Database Management Systems: Test 2
1.Database: E-R Diagram
Consider a MAIL_ORDER database in which employees take orders for parts from customers. The data requirements are summarized as follows:
The mail order company has employees, each identified by a unique employee number, first name, last name, and Zip code.
Each customer of the company is identified by a unique customer number, first name, last name, and Zip code.
Each part sold by the company is identified by a unique part number, a part name, unit price, and quantity in stock.
Each order placed by a customer is taken by an employee and is given a unique order number. Each order contains specified quantities of one or more parts. Each order has a date of receipt as well as an expected ship date. The actual ship date is also recorded.
Design an Entity-Relationship diagram for the MAIL_ORDER database and build the design using a data modeling tool such as Microsoft Visio; it is also OK to use Microsoft Word or Excel.
Consider the following relation for published books:
BOOK (Book_Title, Author_Name, Book_Type, List_Price, Author_Affiliation, Publisher)
Suppose the following functional dependencies exist:
Book_Title -> Publisher, Book_Type
Book_Type -> List_Price
Author_Name -> Author_Affiliation
Your tasks are as follows:
(a)Draw (use Microsoft Visio or Word or Excel) a relational schema and diagram the functional dependencies in the relation BOOK.
(b)What is the primary key in BOOK? Explain your answer.
(c)In what normal form is this relation? Explain your answer.
(d)Decompose BOOK into a set of 3NF relations. Show the details and state the reasons behind each decomposition.
(e)Draw a relational schema for your 3NF relations and show the referential integrity constraints.