ld Hoon Hoon 5578Jumpers 5578 Jumpers CT Pirates of the Mr Clash of the Titans F
ID: 3592610 • Letter: L
Question
ld Hoon Hoon 5578Jumpers 5578 Jumpers CT Pirates of the Mr Clash of the Titans Forgetting Sarah Marshal 34 34 5th Avenue Mr of the Titans (1) Draw the dependency diagram and identify all dependencies, including all partial and transitive dependencies (2) Using the initial dependency diagram drawn in question (1), remove all partial dependencies draw the new dependency diagrams, and identify the normal forms for each table structure you created (3) Using the table structures you created in question (2), remove all transitive dependencies, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created. (4) Using the results of question (3), draw the Crow's Foot ERDExplanation / Answer
Normalization
Definition:
Normalization is the process for assigning attributes to entities. Properly executed, the normalization process eliminates uncontrolled data redundancies, thus eliminating the data anomalies and the data integrity problems that are produced by such redundancies. Normalization does not eliminate data redundancy; instead, it produces the carefully controlled redundancy that lets us properly link database tables.
First Normal Form (1NF)
A table is in 1NF when all the key attributes are defined (no repeating groups in the table) and when all remaining attributes are dependent on the primary key. However, a table in 1NF still may contain partial dependencies, i.e., dependencies based on only part of the primary key and/or transitive dependencies that are based on a non-key attribute.
Second Normal Form (2NF)
A table is in 2NF when it is in 1NF and it includes no partial dependencies. However, a table in 2NF may still have transitive dependencies, i.e., dependencies based on attributes that are not part of the primary key.
Third Normal Form(3NF)
A table is in 3NF when it is in 2NF and it contains no transitive dependencies.
BCNF
A table is in Boyce- Codd Normal Form (BCNF) when it is in 3NF and every determinant in the table is a candidate key. For example, if the table is in 3NF and it contains a nonprime attribute that determines a prime attribute , the BCNF requirements are not met. (Reference the text's Figure 5.8 to support this discussion.)This description clearly yields the following conclusions:
If a table is in 3NF and it contains only one candidate key, 3NF and BCNF are equivalent.
BCNF can be violated only if the table contains more than one candidate key. Putting it another way, there is no way that the BCNF requirement can be violated if there is only one candidate key.
Partial Dependency
A partial dependency exists when an attribute is dependent on only a portion of the primary key.This type of dependency is associated with 1NF.
Three Data Anomalies
The most common anomalies considered when data redundancy exists are: update anomalies, addition anomalies, and deletion anomalies. All these can easily be avoided through data normalization. Data redundancy produces data integrity problems, caused by the fact that data entry failed to conform to the rule that all copies of redundant data must be identical.
Transitive Dependency
ransitive dependency is a condition in which an attribute is dependent on another attribute that is not part of the primary key. This kind of dependency usually requires the decomposition of the table containing the transitive dependency.
To remove a transitive dependency, the designer must perform the following actions:
Place the attributes that create the transitive dependency in a separate table.
Make sure that the new table's primary
key attribute is the foreign key in the original table
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.