A Book Author Business Case After studying a few publishing companies, for a book information database system, you have documented the following business rules, and author, book and publisher information table listed on page 2 Business Rules: 1. Each book number (bno) is a 4 digit unique code for the book 2. Each book name (name) is the full title of the book 3. An author identification number (aid) is a unique identifier for each author whose name (alname and afname) is on the book. Each book may have multiple authors who may write many books 4. The city (pubcity) indicates the publisher location of origin 5. Each publisher name (bpublisher) is the name of the publisher of the book 6. Each price is the price of the book (bprice) 7. Each author will receive an author royalty (auroyalty) for each of his her book(s) to be sold Requirements: Analyze the author and book information below, and normalize the unnormalized data in the next page into the 1", 20. and 3rl normal forms. For each normal form, describe all pertinent relations (or tables) with attributes (or fields) and show tables with relevant data as a proof of your work (20 points) 2. Draw an ERD and business rules from your 3 normal form using the tables and data (10 points) A Book Author Business Case After studying a few publishing companies, for a book information database system, you have documented the following business rules, and author, book and publisher information table listed on page 2 Business Rules: 1. Each book number (bno) is a 4 digit unique code for the book 2. Each book name (name) is the full title of the book 3. An author identification number (and) is a unique identifier for each author whose name (alname and afname) is on the book. Each book may have multiple authors who may write many books 4. The city (pubcity) indicates the publisher location of origin 5. Each publisher name (bpublisher) is the name of the publisher of the book 6. Each price is the price of the book (bprice) 7. Each author will receive an author royalty (auroyalty) for cach of his her book(s) to be sold Requirements: 1. Analyze the author and book information below, and normalize the unnormalized data in the next page into the 1.200 and 3r normal forms. For each normal form, describe all pertinent relations (or tables) with attributes (or fields) and show tables with relevant data as a proof of your work (20 points). 2. Draw an ERD and business rules from your 3 normal form using the tables and data (10 points). AUTHOR BOOK INFORMATION TABLE with DATA book_data (aid, alname, afname, ainst, bno, bname, ppublisher, pubcity, bprice,auroyalty) AIDAL Name AFName Alnst BNO BName BPublisher PubCity 10 Gold Josh Brown Univ 1106 Java Intro Wall & Vintage Chicago 10 Gold Josh Brown Univ 1102 Quick Mobile Apps Gary Brothers Boston 24 Shippen Mary Green Univ 1 104 Data Management Smith & Sons Dallas 24 Shippen Mary Green Univ 1 106 Java Intro Wall & Vintage Chicago 32 Oswald Jan Middlle College ( 1126 Networks & Comm Cheap Publisher Scattle 32 Oswald Jan Middlle College 1180 Server Architecture Gary Brothers Boston 32 Oswald Jan Middlle College 1102 Quick Mobile Apps Gary Brothers Boston BPrice 562.75 $49.95 Si50.85 562.75 $210.00 $122.85 S45.95 AuRoyalty 56.28 $2.50 S15.87 56,00 $12.50 $12.30 $2.25 A Book Author Business Case After studying a few publishing companies, for a book information database system, you have documented the following business rules, and author, book and publisher information table listed on page 2 Business Rules: 1. Each book number (bno) is a 4 digit unique code for the book 2. Each book name (name) is the full title of the book 3. An author identification number (aid) is a unique identifier for each author whose name (alname and afname) is on the book. Each book may have multiple authors who may write many books 4. The city (pubcity) indicates the publisher location of origin 5. Each publisher name (bpublisher) is the name of the publisher of the book 6. Each price is the price of the book (bprice) 7. Each author will receive an author royalty (auroyalty) for each of his her book(s) to be sold Requirements: Analyze the author and book information below, and normalize the unnormalized data in the next page into the 1", 20. and 3rl normal forms. For each normal form, describe all pertinent relations (or tables) with attributes (or fields) and show tables with relevant data as a proof of your work (20 points) 2. Draw an ERD and business rules from your 3 normal form using the tables and data (10 points) A Book Author Business Case After studying a few publishing companies, for a book information database system, you have documented the following business rules, and author, book and publisher information table listed on page 2 Business Rules: 1. Each book number (bno) is a 4 digit unique code for the book 2. Each book name (name) is the full title of the book 3. An author identification number (and) is a unique identifier for each author whose name (alname and afname) is on the book. Each book may have multiple authors who may write many books 4. The city (pubcity) indicates the publisher location of origin 5. Each publisher name (bpublisher) is the name of the publisher of the book 6. Each price is the price of the book (bprice) 7. Each author will receive an author royalty (auroyalty) for cach of his her book(s) to be sold Requirements: 1. Analyze the author and book information below, and normalize the unnormalized data in the next page into the 1.200 and 3r normal forms. For each normal form, describe all pertinent relations (or tables) with attributes (or fields) and show tables with relevant data as a proof of your work (20 points). 2. Draw an ERD and business rules from your 3 normal form using the tables and data (10 points). AUTHOR BOOK INFORMATION TABLE with DATA book_data (aid, alname, afname, ainst, bno, bname, ppublisher, pubcity, bprice,auroyalty) AIDAL Name AFName Alnst BNO BName BPublisher PubCity 10 Gold Josh Brown Univ 1106 Java Intro Wall & Vintage Chicago 10 Gold Josh Brown Univ 1102 Quick Mobile Apps Gary Brothers Boston 24 Shippen Mary Green Univ 1 104 Data Management Smith & Sons Dallas 24 Shippen Mary Green Univ 1 106 Java Intro Wall & Vintage Chicago 32 Oswald Jan Middlle College ( 1126 Networks & Comm Cheap Publisher Scattle 32 Oswald Jan Middlle College 1180 Server Architecture Gary Brothers Boston 32 Oswald Jan Middlle College 1102 Quick Mobile Apps Gary Brothers Boston BPrice 562.75 $49.95 Si50.85 562.75 $210.00 $122.85 S45.95 AuRoyalty 56.28 $2.50 S15.87 56,00 $12.50 $12.30 $2.25