Help In Database:

sakila-reduced.pdf:

CMPSC 321 Relational Database Theory and Design Lab 3: Reverse Engineering Task 1: Relational schema 1. Consider a LIBRARY database which is used to keep track of books, borrowers, and book loans. The relational schema is depicted below AUTHOR Title Publisher name (FKO PUBLISHER COPIES Name Address Phone Book id(K) Branch id FK) No of LIBRARY BRANCH Address BORROWER Branch id (F) (FK) FK) Name Address Phone Book id 2. Try to convert this into a conceptual schema (ER diagram) a. Recall that each table may have come from an entity, a relationship, a weak entity or a multivalued attribute. Try to figure out which is which Write down any assumptions you make. Draw the ER diagram using ERDPlus. b. 3. This process is called "reverse engineering" because you are taking an existing database and trying to figure out the thought process behind it. Compare your answer with a neighbor. Discuss the assumptions you made. 4. Task 2: Data 5. Download the "sakila-reduced.pdf database from Canvas. 6. Reverse engineer this database Figure out what the primary keys are. Figure out what the foreign keys are. Construct the relational schema for this database. For each table, list the attributes. Indicate all primary keys (underlined) and all foreign keys (arrows). You can do this in ERDPlus if you want. Draw an ER diagram of a conceptual schema for this database a. b. c. 7. 8. d. Show the result to the instructor Export an image of the ER diagram, and upload it to Canvas under Lab 3 Task 1 is adapted from exercises in Elmasri and Navathe's Fundamentals of Database Systems, 6th edition, Addison Wesley, 2011, pp. 188-189 Task 2 is adapted from the MySQL documentation, http://dev.mysql.com/doc/sakila/en/ CMPSC 321 Relational Database Theory and Design Lab 3: Reverse Engineering Task 1: Relational schema 1. Consider a LIBRARY database which is used to keep track of books, borrowers, and book loans. The relational schema is depicted below AUTHOR Title Publisher name (FKO PUBLISHER COPIES Name Address Phone Book id(K) Branch id FK) No of LIBRARY BRANCH Address BORROWER Branch id (F) (FK) FK) Name Address Phone Book id 2. Try to convert this into a conceptual schema (ER diagram) a. Recall that each table may have come from an entity, a relationship, a weak entity or a multivalued attribute. Try to figure out which is which Write down any assumptions you make. Draw the ER diagram using ERDPlus. b. 3. This process is called "reverse engineering" because you are taking an existing database and trying to figure out the thought process behind it. Compare your answer with a neighbor. Discuss the assumptions you made. 4. Task 2: Data 5. Download the "sakila-reduced.pdf database from Canvas. 6. Reverse engineer this database Figure out what the primary keys are. Figure out what the foreign keys are. Construct the relational schema for this database. For each table, list the attributes. Indicate all primary keys (underlined) and all foreign keys (arrows). You can do this in ERDPlus if you want. Draw an ER diagram of a conceptual schema for this database a. b. c. 7. 8. d. Show the result to the instructor Export an image of the ER diagram, and upload it to Canvas under Lab 3 Task 1 is adapted from exercises in Elmasri and Navathe's Fundamentals of Database Systems, 6th edition, Addison Wesley, 2011, pp. 188-189 Task 2 is adapted from the MySQL documentation, http://dev.mysql.com/doc/sakila/en/