| 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/12/ |
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#*@ SubjectId* 1590593324 1 1590593324 2 1590593324 3 Subject table SubjectId* SubjectName^ 1 MySQL 2 Database 3 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 23.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 23.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 Author table AuthorId* AuthorName Nationality 1 Chad Russell American Format table FormatId Format 1 E-book 2 Hardcover Book_Format ISBN*@ FormatId*@ Price 1590593324 1 23.99 1590593324 2 49.99 From 3/29/2022 Book table ISBN* Title AuthorId@ Pages Thickness Publisher Publisher Country Genre ID 1590593324 Beginning MySQL 1 520 Thick Apress USA 1 Author table AuthorId* AuthorName Nationality 1 Chad Russell American Genre table GenreID* GenreName 1 Tutorial 2 Popular science What if a book can have many Genres Book table ISBN* Title AuthorId@ Pages Thickness Publisher Publisher Country 1590593324 Beginning MySQL 1 520 Thick Apress USA Author table AuthorId* AuthorName Nationality 1 Chad Russell American Genre table GenreID* GenreName 1 Tutorial 2 Popular science Book_Genre table ISBN*@ GenreID*@ 1590593324 1 1590593324 2 The Publisher Country does not depend on the Book. So create a table for Publishers. Book table ISBN* Title AuthorId@ Pages Thickness PublisherID@ 1590593324 Beginning MySQL 1 520 Thick 1 Book_Subject table ISBN#*@ SubjectId* 1590593324 1 1590593324 2 1590593324 3 Subject table SubjectId* SubjectName^ 1 MySQL 2 Database 3 Design Author table AuthorId* AuthorName Nationality 1 Chad Russell American Genre table GenreID* GenreName 1 Tutorial 2 Popular science Book_Genre table ISBN*@ GenreID*@ 1590593324 1 1590593324 2 Publisher table PublisherID* PublisherName PublisherCountry 1 Apress USA