Database Management Systems, computer science homework help

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.

2.Database: Normalization

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.

"Order a similar paper and get 100% plagiarism free, professional written paper now!"

Order Now