Question

Question 3 (a) Draw an ER diagram in term of entity, attribute and relationship for a SMART FOOD VENTURE proposal. [14 marks] (b) Identify the relational keys are Superkey, candidate key, primary key, alternate key and foreign key. Describe each of them with reference to the sample database tables that you had created. [6 marks]

Answer 1

(a)

Based on the given business rules and requirements ERD has been created using online free tool

Customer Biz_Cat PK Customerld 1,1 + PK Biz_Cat_ID Biz_Cat_Name FName LName Price Phone Volume Item Price 1,1 PK Item_Code input_Price Retail_Price Biz_Cat_Approved_Price Volume_Approved_Price Term_Approved_Price Special_Discount Offered_Price FK1 Biz_Cat_ID FK2 Volume_ID 1.M FK3 Term_ID FK4 Price_changed_By 1,1 HPK Volume_ID Volume Order 1,1 PK Order_ID HE Order_Date Is_Csutomer_agreed 1.M -OFK1 Customer_ID Order_status 1,M Price 1.M 1.M Term 1.1 #PK Term_ID 1,M Term Order_item Ordered_kg FK1 Item_Code FK2 Order_ld offered_price Authorised Person PK Employee_ID FName Price 1,1 1.M LName SSN Phone Desgination

(b) The entities identified as below after normalization upto 3NF to avoid multivalued attributes(1NF violation), removing partial dependencies of non prime attributes on the primary key (2NF) and removing transitive dependencies (3NF) Customer Item Price • Authorized Person (Marketing Manager or owner) • Biz_Cat Volume • Term Order • Order_item

The identified keys in the tables are below Customer Item_Code Primary key Candidate Key Phone Biz_Cat Biz_Cat_ID Primary key Biz_Cat_Name Candidate Key Volume Volume_ID Primary key Volume Candidate Key Term Term_ID Primary key Term Candidate Key

Authorized Person Employee ID Primary key Candidate Key SSN Phone Candidate Key Item Price Item_Code Primary key Foreign Key Biz_Cat_ID Volume_ID Foreign Key Term_ID Foreign Key Price_Changed_By Foreign Key

Order Order ID Primary key Customer_ID Foreign Key Order_Item Item_Code Foreign key Order_ID Foreign Key