CIS 336 iLab 1 Normal Forms and Entity Relationship Diagrams CIS 336 iLab 1 Normal Forms and Entity Relationshi | Page 2

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 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