Department of Computer Science

Answers to ER Modelling Exercises

To: Systems Design and Development

Exercise 2.12

1. COURSE_RESULT breaks the rule "depend on the whole key" because the value of CourseTitle only depends on the CourseCode and value of StudentName only depends on the RegistrationNumber.
   
   

2. STUDENT =	{Student}
   Student =	RegistrationNumber + StudentName
   COURSE =	{Course}
   Course =	CourseCode + CourseTitle
   COURSE_RESULT =	{CourseResult}
   CourseResult =	CourseCode* + RegistrationNumber* + OverallResult

   
   

Exercise 2.13

1. (i)
   

   PhysicalOrder =

   OrderNumber + NameAndAddress + DateRequired + { ProductCode + Description + Quantity + UnitPrice + TotalPerProduct }

   (ii)

   PRODUCT =	{Product}
   Product =	ProductCode + Description +  UnitPrice
   ORDER  =	{Order}
   Order =	OrderNumber + NameAndAddress* + DateRequired
   ORDER_LINE =	{OrderLine}
   OrderLine =	OrderNumber* + ProductCode* + Quantity
   CUSTOMER =	{Customer}
   Customer =	NameAndAddress

    

   
   

2. Change the ORDER entity so it records the date the order was placed. Add an extra attribute into the PRODUCT entity to show the date from which the UnitPrice is effective.   This is also part of the key.
   
   

   PRODUCT =	{Product}
   Product =	ProductCode + Description +  UnitPrice + DatePriceEffective
   ORDER  =	{Order}
   Order =	OrderNumber + NameAndAddress* + DateRequired + DateOrderPlaced
   ORDER_LINE =	{OrderLine}
   OrderLine =	OrderNumber* + ProductCode* + DatePriceEffective* + Quantity

    

Exercise 2.14

CUSTOMER =	{Customer}
Customer =	CustNameAndAddress + (TelephoneNum)
CustNameAndAddress =	CustomerName + CustomerAddress
CAR_MODEL =	{CarModel}
CarModel =	CarMakeAndModel + MinorServiceMiles + MinorServiceMonths  + MajorServiceMiles + MajorServiceMonths
CarMakeAndModel =	CarMake + CarModel
CAR =	{Car}
Car =	RegistrationNum + CustNameAndAddress* + CarMakeAndModel* + RegistrationDate
CAR_MOT =	{CarMOT}
CarMOT =	RegistrationNum* + DateOfMOT
CAR_SERVICE =	{CarService}
CarService =	RegistrationNum* + DateOfService + MilometerReading + TypeOfService

How do you check that your diagram corresponds to the data dictionary?

HINT:    The "crows-foot" on a diagram is the "many" end of a relationship, and so the entity at this "many" end must have a foreign key to the other entity.  For example, the CAR entity has a foriegn key CustNameAndAddress and CarMakeAndModel.