Normalization Questions
NORMALIZATION
TUTORIAL (EXTRA)
1. Given the dependency diagram, answer items a-c:
[pic]
a. Identify and discuss each of the indicated dependencies.
C1 ( C2 represents a partial dependency, because C2 depends only on C1, rather than on the entire primary key composed of C1 and C3.
C4 ( C5 represents a transitive dependency, because C5 depends on an attribute (C4) that is not part of a primary key.
C1, C3 ( C2, C4, C5 represents a set of proper functional dependencies, because C2, C4, and C5 depend on the primary key composed of C1 and C3.
b. Create a database whose tables are at least in 2NF, showing the dependency diagrams for each table.
[pic] c. Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table.
[pic]
Transitive 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.
Figure Q5.9 shows an example of a transitive dependency removal.
Figure Q5.9 Transitive Dependency Removal
[pic]
1. Using the INVOICE table structure shown in Table P5.1, write the relational schema, draw its dependency diagram and identify all dependencies (including all partial and transitive dependencies). You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.)
2. Using the initial dependency diagram drawn in Problem 1, remove all partial dependencies, draw the new dependency diagrams,
TUTORIAL (EXTRA)
1. Given the dependency diagram, answer items a-c:
[pic]
a. Identify and discuss each of the indicated dependencies.
C1 ( C2 represents a partial dependency, because C2 depends only on C1, rather than on the entire primary key composed of C1 and C3.
C4 ( C5 represents a transitive dependency, because C5 depends on an attribute (C4) that is not part of a primary key.
C1, C3 ( C2, C4, C5 represents a set of proper functional dependencies, because C2, C4, and C5 depend on the primary key composed of C1 and C3.
b. Create a database whose tables are at least in 2NF, showing the dependency diagrams for each table.
[pic] c. Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table.
[pic]
Transitive 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.
Figure Q5.9 shows an example of a transitive dependency removal.
Figure Q5.9 Transitive Dependency Removal
[pic]
1. Using the INVOICE table structure shown in Table P5.1, write the relational schema, draw its dependency diagram and identify all dependencies (including all partial and transitive dependencies). You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.)
2. Using the initial dependency diagram drawn in Problem 1, remove all partial dependencies, draw the new dependency diagrams,