CIS 336 STUDY Extraordinary Success /cis336study.com CIS 336 STUDY Extraordinary Success /cis336study.c | Page 39

1022 9781890774820 2015-03-09 Murach‘s MySQL, 2nd Edition
1032 Murach, Mike & Associates, Inc 2 48.95
1249 9781449374020 2015-02-22 MySQL Cookbook: Solutions for
Database Developers and Administrators 1118 O‘Reilly Media,
Incorporated 9 50.59
1249 9781449325572 2015-02-22 PHP & MySQL: The Missing
Manual 1118 O‘Reilly Media, Incorporated 6 29.95
1249 9781890774790 2015-02-22 Murach‘s PHP and MySQL, 2nd
Edition 1032 Murach, Mike & Associates, Inc 1 48.95
BookSales
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