(b) (4 marks)

(i) Identify the functional dependencies based on your understanding of the My University domain. Explain how the data in Table 1 satisfy those dependencies. State any assumptions you have made.

(ii) Did you identify UnitNo Offering as one of the functional dependencies above? Justify why you did, or did not, do so.

(c) (3 marks)

Using the functional dependencies identified in part ( b) above, describe and illustrate the process of normalization by converting the Enrolment relation to Third Normal Form (3NF). Identify the primary and foreign keys in these relations in 3NF that you obtained.

QUESTION A2 (12 marks) Consider the relation schema: Enrolment (Dept, UnitNo, Offering, SID, SName, EnDate, CName, CLoc) intended to contain student enrolment data for My University, a multi-campus university with different campuses around Sydney. The campus name is represented as CName and campus location as CLoc. A unit is offered by a particular department, is taught at a particular campus, and a student must enrol in it for a particular offering by a given date. The table below is a snapshot of the data held at some particular time eptUnitNo Offering SIDSName EnDate CNameCLoc 1/6/2015 Main 1/1/2015 1/6/2015 Cit Computing Isys2242015S2 12345Johrn Computing Isys1142015S1 12345 Johin Economics Econ223 2015S2 21324 Jack Computing Isys2242015S2 21325Jill Economics Econ123 2015S121325 Jill Economics Econ213 |2015S2 12345 John NorthRyde NorthRyde CBD NorthRyde CBD