QUESTIONS

 

Part A: Relational Database Design (30 Marks) Consider the following ER diagram, which shows aspects of the business of a Food Saver company. As a part of the Assignment 1 activities, we have learned how to map entities and relationships in a E-R model Contact type {PK) detail O.N PK has 1..1 Store storeNo {PK} address street suburb postcode 0..N stock qty O..N Product productNo {PK} name description salePrice startDate 11..1 workAt 0..1 manage qty supervise > 0..1 Staff 0..N staffNo (PK} name address 1..1 position salary Order 0..N orderNo {PK} 1..N contains date {PK} O..N creates 1..1 Customer CreditCard email {PK} creditCardNo {PK} expiry Date securityCode 1..1 name address 1..1 paymentDetails street suburb O..N postcode into a relational database schema. We have used the 7-step mapping process. As a result of incorrect application of 7-step mapping process, it was mapped into following relations. Contact(type, StoreNo*, details, S_Postcode*) Store (StoreNo, S_Street, S_Suburb, S_Postcode, staffNo*, startDate, ManagerStaffNo*, S_Name*) Staff((StaffNo, S_Name, S_Address, Position, Salary, SupervisorStaff No*, SupervisorStaffName*) Product(ProductNo, P_Name, P_Description, P_salePrice) Order (OrderNo, OrderDate, C_Email*, C_name*) Customer(C Email, C_Name, C_Street, C_Suburb,C_Postcode, CreditCard No*, expiryDate*, securityCode*) Contains(productNo*, OrderNo*, OrderDate*, Contains_qty) Stock (StoreNo*, ProductNo*, P_Name*, P_salePrice*, stock_qty)