Lab 2 Database Design Part 1 Use the database shown below to work Problems 1 through 7. Note that the database is composed of four tables and reflects these relationships: A CUSTOMER has only one customer#, but a customer can place any number of orders. A BOOK has only one isbn, but can be order as many times as the customer wants. Note also that the M:N relationship has been broken down into two 1:M relationships for which the ORDERITEMS table serves as the composite or bridge entity. 1. Evaluate if the book 'HOW TO MANAGE THE MANAGER' will be entered in to BOOKS table without an error explain why or why not? ISBN 9247381001 Title HOW TO MANAGE THE MANAGER Pubdate 09-MAY-03 Pubid 1 Cost 15.4 Retail 31.95 Category BUSINESS Contents of BOOKS table Title ISBN Pubdate Pubid Cost Retail Category 2. Also evaluate the rows shown under ORDERS & ORDERITEMS tables if they will be entered in the respective tables without error, explain why or why not? Orders table: Shipcity Order # 1010 Customer # 1019 e e Shipstat Shipzi Shipcos P NJ 07960 2 384 Orderdat Shipdat Shipaddre e SS 03-APR- 04-APR- 09 09 WRONG WAY HOME MORRISTOW N Contents of ORDERS Table Orderdat Shipcity Order # Shipdat Shipaddress Customer # Shipzi Shipstat Shipcos e e 3. Also evaluate the rows shown under ORDERITEMS tables if they will be entered in the respective tables without error, explain why or why not? Orderitems table: Item# Order# 1005 1009 1 1 ISBN 1059831198 3437212490 Quantity 1 1 Paideach 39.95 19.95 Contents of ORDERITEMS table Item# 1 1 IN Order 1000 1001 1001 1002 1003 1003 1003 1004 1 ISBN 0401140733 4981341710 8843172113 1059831198 0401140733 4981341710 8843172113 2491748320 Quantity 1 1 1 2 1 1 1 2 Paideach 19.95 31.95 85.45 55.95 55.95 30.95 19.95 85.45 1 2 3 1 4. Evaluate if customer# 1006 AND 1004 will be entered in CUSTOMERS table without an error, explain why or why not? Customer# Lastname Firstname Address City State Zip Region 1006 CRUZ MESHIA 82 DIRT ROAD ALBANY NY 12211 NE 1004 MOLINA JENNIFER P.O. BOX 754 ORLANDO FL 32306 Contents of CUSTOMERS table Customer# 1001 1002 Lastname MORALES THOMPSON Firstname BONITA RYAN Address P.O. BOX 651 P.O. BOX 9835 City EASTPOINT State FL CA Zip 32328 90404 SANTA MONICA TALLAHASSEE SEATTLE 1003 32306 SMITH GIRARD LEILA CINDY P.O. BOX 66 P.O. BOX 851 FL WA 1005 98115 5. For each table in the database, identify the primary key and the foreign key(s). If the table does not have a foreign key, write None in the assigned space. Table Primary key Foreign Key Books Customers Orders Orderitems 6. Do the tables exhibit entity integrity? Answer Yes or No, then explain your answer. Table Entity Integrity Explanation Books Customers Orders Orderitems 7. Do the tables exhibit referential Integrity? Answer Yes or No, then explain your answer. Write NA(not Applicable) if the table does not have a foreign key. Table Referential Explanation Integrity Books Customers Orders Orderitems Lab 2 Database Design Part 1 Use the database shown below to work Problems 1 through 7. Note that the database is composed of four tables and reflects these relationships: A CUSTOMER has only one customer#, but a customer can place any number of orders. A BOOK has only one isbn, but can be order as many times as the customer wants. Note also that the M:N relationship has been broken down into two 1:M relationships for which the ORDERITEMS table serves as the composite or bridge entity. 1. Evaluate if the book 'HOW TO MANAGE THE MANAGER' will be entered in to BOOKS table without an error explain why or why not? ISBN 9247381001 Title HOW TO MANAGE THE MANAGER Pubdate 09-MAY-03 Pubid 1 Cost 15.4 Retail 31.95 Category BUSINESS Contents of BOOKS table Title ISBN Pubdate Pubid Cost Retail Category 2. Also evaluate the rows shown under ORDERS & ORDERITEMS tables if they will be entered in the respective tables without error, explain why or why not? Orders table: Shipcity Order # 1010 Customer # 1019 e e Shipstat Shipzi Shipcos P NJ 07960 2 384 Orderdat Shipdat Shipaddre e SS 03-APR- 04-APR- 09 09 WRONG WAY HOME MORRISTOW N Contents of ORDERS Table Orderdat Shipcity Order # Shipdat Shipaddress Customer # Shipzi Shipstat Shipcos e e 3. Also evaluate the rows shown under ORDERITEMS tables if they will be entered in the respective tables without error, explain why or why not? Orderitems table: Item# Order# 1005 1009 1 1 ISBN 1059831198 3437212490 Quantity 1 1 Paideach 39.95 19.95 Contents of ORDERITEMS table Item# 1 1 IN Order 1000 1001 1001 1002 1003 1003 1003 1004 1 ISBN 0401140733 4981341710 8843172113 1059831198 0401140733 4981341710 8843172113 2491748320 Quantity 1 1 1 2 1 1 1 2 Paideach 19.95 31.95 85.45 55.95 55.95 30.95 19.95 85.45 1 2 3 1 4. Evaluate if customer# 1006 AND 1004 will be entered in CUSTOMERS table without an error, explain why or why not? Customer# Lastname Firstname Address City State Zip Region 1006 CRUZ MESHIA 82 DIRT ROAD ALBANY NY 12211 NE 1004 MOLINA JENNIFER P.O. BOX 754 ORLANDO FL 32306 Contents of CUSTOMERS table Customer# 1001 1002 Lastname MORALES THOMPSON Firstname BONITA RYAN Address P.O. BOX 651 P.O. BOX 9835 City EASTPOINT State FL CA Zip 32328 90404 SANTA MONICA TALLAHASSEE SEATTLE 1003 32306 SMITH GIRARD LEILA CINDY P.O. BOX 66 P.O. BOX 851 FL WA 1005 98115 5. For each table in the database, identify the primary key and the foreign key(s). If the table does not have a foreign key, write None in the assigned space. Table Primary key Foreign Key Books Customers Orders Orderitems 6. Do the tables exhibit entity integrity? Answer Yes or No, then explain your answer. Table Entity Integrity Explanation Books Customers Orders Orderitems 7. Do the tables exhibit referential Integrity? Answer Yes or No, then explain your answer. Write NA(not Applicable) if the table does not have a foreign key. Table Referential Explanation Integrity Books Customers Orders Orderitems