data base class quiz in class - due by 25 minutes

1. A cosmetic product retailer needs to create a database to keep track of the information for its business operations. The company has a web site that posts all its products. The product information includes product ID, product name, description, and unit price. The company also needs to keep track of customers’ information, including customer names, their shipping addresses, and the email address. The company creates an account for each customer for identification and tracking purpose. A customer can purchase multiple products with different quantities in one order. The company’s products have many prospective customers. The company needs to keep track of information for all orders it received, including the order date, invoice number, and information about products purchased in an order such as IDs of products, and quantities, etc. Company’s products are stocked in several warehouses. So company also needs to keep track of the information about each warehouse such as its name, address, manager, telephone, etc.

a. Create an E/R model for this scenario. In your E/R model you need to show the names of entities, names of attributes, keys and the name(s) of relationship(s). Also indicate maximum and minimum cardinalities You may hand-draw the ER model and insert its image here.

b. Convert/Express this E/R model into a database design model (a set of tables). (Using table notation, table should be at least in 3NF, label/indicate keys and foreign keys).

2. Given the followin ER model

ID

_ _ _ _ __

DName

PhoneNo.

Director

Name

Address

PhoneNo. (MV)

Salary

a. Given the following E/R model, how to implement it into a logical database design (a set of tables), show tables. Note the PhoneNo in Employee is a multivalued attribute (an employee may have multiple phone numbers).

b. Describe the meaning of oth maximum andminimum cardinalities of above model

c. Given the minimum cardinality indicated in above model

State how does database system enforce the data integrity when

(c1) an employee record is ADDED into Employee table?

(c2) a division record is DELETED from Division table.

3. Given a table Inventory (ProductID, ProductName, Quantity-in-Stock, WarehourseID, StaffName, Jobtitle, Manufacture).

Given FDs:

ProductID, WarehouseID → Quantity-in-Stock

ProdcutID → Manufacture, ProductName

StaffName Jobtitle

WarehouseID >> StaffName (multivalued dependence, namely, a warehouse has several staffs)

a. What is the KEY of the table Inventory, given above table and FDs?

b. What Normal Form this table is in? Why?

c. Describe one type of anomaly (delete, insert or update)?

d. How to normalize above table, show your result (table name(s), key(s) and FK(s), attributes).

(clearly label your answers for a,b,c,d)