Map the ERD, from the sample solution, into a set of relations in at least Third n...

Map the ERD, from the sample solution, into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. There is no need to show your working. b) Select any two (2) of your relations from the previous step, 1a), and perform the following for each of those two relations: • List all the functional dependencies exist in the relation. • Demonstrate that the relation meets Third Normal Form (3NF). For your help, an example for a Student relation’s 3NF justification has been provided below: STUDENT (StudentID, Name, Address, DateOfBirth) i. The primary key is StudentID which identifies a student. There will be one student name per Student ID because only one name is recorded for each student. Even though a student may have multiple addresses (e.g. a home address and a work address), the case study states that there is a need to record the home address only and hence there will be only one address for each student. There will be only one date of birth for each student. Thus, there are no repeating groups. Hence, this relation is in 1NF. ii. The primary key is StudentID. This is the only candidate key since Name is not guaranteed to be unique (two different students may have the same name). Address is also not guaranteed to be unique. This means that Student ID functionally determines every other attribute in the table. There is a single valued simple candidate key (StudentId) and therefore no partial dependencies are possible. Hence, the relation is in

Document Preview:

Assignment 1 Sample Solution ER model – ASS Database System Client Car _______________________ _______________________ ClientID RegoNbr Full Name owns Make,Model,Colour Address(Stree,City,State,etc) ProducedYear buys Phone Price,WarrantyDueDate IsSenior PurchasedFromASS PartType _____________________ PartID Salesperson PartDescription gets _______________________ sells PricePerUnit NbrOfExperienceYear “S” included Service Employee LabourWork _______________________ PartUsed _______________________ ______________________ JobNbr _____________________ EmployeeID EmployeeID Mechanic JobDescription JobNbr Full Name performs JobNbr related uses _______________________ ServiceDate PartID Address(Street,City,State, StartTime d “M” CertificateLevel TotalCost QuantityUsed PostCode) FinishTime PartCost Phone LabourCost EmployeeType related to “A” Complaint _______________________ ComplaintID Admin processes Reason _______________________ LodgedDate Qualification Outcome makes (see next page on business rules)Assumptions and Business rules 1. The E-R model is not concerning the record that the cars from this dealer are sold to the clients who don’t bring car for service. It mainly concerns the services and relevant data. If a client car was purchased from this dealer, the database records The price and warranty due date; otherwise the values in these two fields could leave as empty. 2. The dealer may have multiple cars with the same type. The database doesn’t record the details that the car type and client relationship can be many to many. It assumes that the dealer sells new cars, not considering trade of used car. 3. A client may own more than one cars that are under service. 4. A client may purchase a car from the dealer and may not. 5. The salesperson may sell zero car. 6. A client may make a complaint about his/her service and may not. 7. It is possible for a service to be carried out using no parts at all. 8. A service could be performed by one or many…

Attachments:

Ass1-sol.pdf