The topic will be as follows:
Suppose there are two entities "building" and "apartment". The entity "apartment" is dependent entity and cannot exist independently and “building” is an independent entity. The "apartment" entity contains the primary key (building_name) of building entity to identify the apartment of that specific building. The "apartment" entity also has its own identifier, i.e. apartment_no but that identifier is not sufficient to identify the particular apartment, so in this case we used both keys building_name and apartment_no to identify the particular apartment.
You have studied different dependencies in the course. You are required to choose the appropriate dependency applicable in the above scenario. Justify your answer with reasons.
Try to provide precise, to the point answer and avoid irrelevant details
For any query: CS403@vu.edu.pk
Types of dependencies: So we could get idea about their functionalities
Trivial Functional Dependencies
A trivial functional dependency occurs when you describe a functional dependency of an attribute on a collection of attributes that includes the original attribute. For example, “{A, B} -> B” is a trivial functional dependency, as is “{name, SSN} -> SSN”. This type of functional dependency is called trivial because it can be derived from common sense. It is obvious that if you already know the value of B, then the value of B can be uniquely determined by that knowledge.
Full Functional Dependencies
A full functional dependency occurs when you already meet the requirements for a functional dependency and the set of attributes on the left side of the functional dependency statement cannot be reduced any farther. For example, “{SSN, age} -> name” is a functional dependency, but it is not a full functional dependency because you can remove age from the left side of the statement without impacting the dependency relationship.
Transitive Dependencies
Transitive dependencies occur when there is an indirect relationship that causes a functional dependency. For example, ”A -> C” is a transitive dependency when it is true only because both “A -> B” and “B -> C” are true.
Multivalued Dependencies
Multivalued dependencies occur when the presence of one or more rows in a table implies the presence of one or more other rows in that same table. For example, imagine a car company that manufactures many models of car, but always makes both red and blue colors of each model. If you have a table that contains the model name, color and year of each car the company manufactures, there is a multivalued dependency in that table. If there is a row for a certain model name and year in blue, there must also be a similar row corresponding to the red version of that same car.
Importance of Dependencies
Database dependencies are important to understand because they provide the basic building blocks used in database normalization. For example
For a table to be in second normal form (2NF), there must be no case of a non-prime attribute in the table that is functionally dependent upon a subset of a candidate key.
For a table to be in third normal form (3NF), every non-prime attribute must have a non-transitive functional dependency on every candidate key.
For a table to be in Boyce-Codd Normal Form (BCNF), every functional dependency (other than trivial dependencies) must be on a super key.
For a table to be in fourth normal form (4NF),it must have no multivalued dependencies.