Question

Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas. For each one of the relational schemas identify its highest normal form. Remember that the identification of a normal form requires analysis of the valid functional dependencies and the minimal keys. Provide justification of each answer. A solution with no comprehensive justification scores no marks.

(iv) When a staff is required to undertake an inspection of properties, the staff is allocated a company car for the day. A staff may inspect several properties on a given date, and a property is only inspected once on a given date. These information of the inspection are stored in the following relational table:
PropertyInspection(PropertyNum, IDate, ITime, Comments, StaffNum, CarRegNum)

(vi) Each student is assigned with a counselor who will act as an advisor to the students. The student is given the counselor email address so that the student can contact the counselor for advice. The information on the student and counselor are stored in the following relational table.
STUDENTCOUNSELOR (StdNum, StdName, CounselorName, CounselorEmail)

Answer 1

iv) IDate and Property has a one-to-many relationship between them. Which means that there will be an unique value of IDate for each PropertyNum, but there can be more than one PropertyNum for each IDate.
Property and Staff has many-to-many relationship. One staff may inspect more than one property, whereas one property may be inspeced more than one staff, but on different dates.
Thus,
PropertyNum->IDate,StaffNum, ITime,Comments, CarRegNum
StaffNum-> PropertyNum

Candidate Keys-> (StaffNum, PropertyNum)

The relation is is in BCNF as both StaffNum and PropertyNum are Superkeys.

(vi) STUDENTCOUNSELOR (StdNum, StdName, CounselorName, CounselorEmail)

The relation between Student and Counsellor is many-to-one i.e. more than student can be asigned to one counselor but one student can have one counselor only and hence one email address.

StdNum->StdName, CounselorName, CounselorEmail
CounselorName->CounselorEmail

Candidate Key-{StdNum}
Considering the table to be INF generically. There is no partial dependency since no non-primary key attribute is dependent upon a proper subset of the candidate key, hence the table is in 2NF.

CounselorEmail is dependent on a non-primary key CounselorName transitively, which indicates a transitive dependency. Hence, it is not in 3NF.
Thus, the table is in 2NF.