CIS 336 STUDY Future Starts Here/cis336study.com CIS 336 STUDY Future Starts Here/cis336study.com | Page 40

1. Using the BookSales table structure shown above, draw the
dependency diagram, and show all dependencies, full, partial, and
transitive.
Figure 1 -solution 1
Discussion: A composite primary key consisting of InvNum + ISBN13
can be constructed, ensuring that all rows are unique. All remaining
attributes are shown to be dependent (at least partially) on this
composite key, so the table is 1NF. Further analysis shows that
InvDate is dependent on only part of the key (InvNum), and that
unitPrice, PubCode, and BookTitle also depend on part of the key
(ISBN13). A transitive relationship is also revealed: PubName truly
depends on PubCode, which in turn depends on ISBN13.
2. Using the answer to exercise 1, remove all partial dependencies and
draw the new dependency diagrams. For each new table created,
specify its normal form (1NF, 2NF, 3NF).
Figure 2- solution 2
Discussion: To achieve 2NF, we must remove partial dependencies.
This is done by decomposing into three tables, and three dependency
diagrams at this stage. Notice that the Transitive relationship has not
yet been addressed.
3. Using the answer to exercise 3, remove all transitive dependencies,
and draw the new dependency diagrams. For each new or revised
table, specify its normal form.
Figure 3 – Solution 3
Discussion: The transitive relationship has been removed by creating
the Publishers table, in which PubName is fully dependent upon