Summary: In this tutorial, you will learn about dependencies and its types in context to relational databases (DBMS) with the help of the examples.
Dependencies in DBMS
In a database management system (DBMS), dependencies refer to the relationships between objects in a database. These objects can include tables, columns, views, and stored procedures, among others.
There are several types of dependencies that can exist in a database:
- Functional dependencies: This type of dependency occurs when the value of one attribute determines the value of another attribute. For example, a “Student” table might have a functional dependency between the “Student ID” and “Student Name” attributes, since the name of a student is uniquely determined by their ID.
- Referential dependencies: This type of dependency occurs when one table has a foreign key that references a primary key in another table. For example, a “Course Enrollment” table might have a referential dependency on a “Student” table, since each row in the “Course Enrollment” table references a specific student by their ID.
- Join dependencies: This type of dependency occurs when two or more tables can be combined through a join operation to reconstruct the original table.
- Multivalued dependencies: This type of dependency occurs when an attribute in a table is dependent on a combination of two or more other attributes in the same table.
Functional Dependency
Consider a “Student” table with the following attributes:
- Student ID (primary key)
- Student Name
- Student Age
- Student Gender
There is a functional dependency between the “Student ID” and “Student Name” attributes, because the name of a student is uniquely determined by their ID. This means that if we know the ID of a student, we can determine their name, but not vice versa.
Referential Dependency
Consider a “Course Enrollment” table with the following attributes:
- Course Enrollment ID (primary key)
- Student ID (foreign key referencing the “Student” table)
- Course ID (foreign key referencing the “Course” table)
- Grade
There is a referential dependency between the “Course Enrollment” table and the “Student” table, since each row in the “Course Enrollment” table references a specific student by their ID.
This means that if a student’s ID is deleted from the “Student” table, any rows in the “Course Enrollment” table that reference that student’s ID would become orphaned and would need to be deleted or updated.
Join Dependency
Consider a “Person” table with the following attributes:
- Person ID (primary key)
- First Name
- Last Name
And a “Phone Number” table with the following attributes:
- Phone Number ID (primary key)
- Person ID (foreign key referencing the “Person” table)
- Phone Number
- Phone Type (e.g. “home”, “work”, “mobile”)
The “Person” and “Phone Number” tables have a join dependency, because they can be combined through a join operation to reconstruct the original “Person” table.
For example, we could use the following SQL query to join the two tables and retrieve all of the phone numbers for a person with a given ID:
SELECT p.FirstName, p.LastName, ph.PhoneNumber, ph.PhoneType
FROM Person p
JOIN PhoneNumber ph ON p.PersonID = ph.PersonID
WHERE p.PersonID = 123
Multivalued Dependency
Consider a “Product” table with the following attributes:
- Product ID (primary key)
- Product Name
- Product Category
- Product Subcategory
There is a multivalued dependency between the “Product Category” and “Product Subcategory” attributes, because a product can belong to multiple categories and subcategories.
For example, a product might belong to both the “Clothing” category and the “Shoes” category, and it might also belong to both the “Men’s” subcategory and the “Women’s” subcategory. This means that the values in the “Product Subcategory” attribute are dependent on both the “Product ID” and “Product Category” attributes.
Why Dependency?
Dependencies are important in a database because they can help ensure the integrity and consistency of the data.
For example, functional dependencies can help ensure that if the value of one attribute is known, the value of another attribute can be uniquely determined. This can help prevent errors and inconsistencies in the data.
Referential dependencies are important because they help enforce relationships between tables in the database. For example, if a table has a foreign key that references a primary key in another table, it ensures that there is a corresponding row in the referenced table for every row in the dependent table. This helps prevent orphaned rows and ensures the data is consistent.
Join dependencies can help ensure that data is not unnecessarily duplicated in the database, which can save space and improve performance.
Multivalued dependencies can help ensure that all possible combinations of values for multiple attributes are properly represented in the data.
Overall, dependencies help ensure that the data in a database is structured and organized in a logical and consistent way, which is important for the integrity and reliability of the database.
Too Many Dependencies is Bad
It’s important to understand dependencies in a database because they can affect the performance and integrity of the database.
For example, if a table with a lot of functional dependencies is frequently updated, it can lead to performance issues.
Similarly, if a table has a referential dependency on another table and a row in the referenced table is deleted, it could cause data integrity issues if the dependent rows are not also deleted.
