(a) Normalise the following relation to 3rd normal form, showing all steps and indicate any assumption you have made. The following table stores information about suppliers, the category they are assigned to and the parts they supply. (SuppNO, Supp_Name, Supp_Addr, ,(Part_Num, Part_Desc, Unit_Price, Transfer_Price, CategoryNO, CatName)) Field Definition Field Definition Supp_NO Represents a unique number that is assigned to a supplier CategoryNO Represents a number that uniquely identifies a specific category Supp_Name Supplier Name CatName Category name Supp_Addr Suppliers Address Part_Num Unique number that represents a specific part Transfer_Price Represents the cost of transporting a particular part to a customer Part_Desc Full part description Unit_Price Represents the unit price of a specific part (b) Normalise the following table to 3rd Normal form. INVOICE(Invoice_No, Cust_No, Cust_Name, Cust_Add, Cust_Phone, Inv_Date, (Product_No, Product_Name, CategoryNO, CategoryName, Unit_Price, Sale_Price Qty_Sold )) Cust_No Unique Customer Number Inv_Date Represents the date of the invoice Product_No Unique Product Number Unit_Price Represents the current price of a particular product. Sale_Price Represents the price a particular product is sold for on a given invoice. CategoryNO Each product belongs to one specific category group (5 + 5 = 10 marks) Question 2: Normalise the following user view to 3rd normal form. Show all steps and list any assumptions you have made. Note: The field 'Sale Price' represents the product unit price and the point of sale. Your database design should include the current price ( Unit Cost ) of any product. (5 marks ) Robo's Robot Distributions 16 Nelson Place Invoice NO: 123434 Williamstown 3056 Invoice date: 21.05.2004 CustomerNO: 90876 Customer Name: Joe North Customer Address: 28 Adelaide Ave Sunshine 3020 Product NO Product Description Quantity Sale Price Line Total A1234 Robot Arm 2 2500.00 5000.00 B5677 Robot Head 3 18000.00 54000.00 C1238 Robot Power Pack 2 2000.00 4000.00 Total: 63000.00 Question 3 (a) The following un-normalised table stores information on students, subjects and the results a student obtains when he or she passes a subject. ( StudentNo, FirstName, Surname, CourseNo, Course_Name, (SubjectNo, SubjectDescription, Result)) Field Definition Field Definition StudentNO A unique number representing a student SubjectNO A number that uniquely represents a subject FirstName Represents a student first name SubjectDescription A subjects title Surname Represents a students surname Result Represents a mark a student obtains in a specific subject. CourseNo A student is enrolled in one course Course_Name Full Title of a particular course The following represents three possible solutions. Discuss the merits of each of the solutions and indicate the one you believe is the most appropriate and why