7. Consider the relation: COURSE (course#,date,level,cost) STUDENT (sID, univers

Question

7. Consider the relation: COURSE (course#,date,level,cost) STUDENT (sID, university, address, course#, date, level)

The following assumptions hold:

• a course always has the same cost, regardless of date or level.

• each course is available on one or more dates and one or more levels, and each course is available in all combinations of dates and levels for that course.

• each student attends one university.

• each university has one address; each address maps to one university.

• each tuple of Course and Student is unique (no duplicate rows).

(a) List all the FDs.

(b) Specify all keys for the relations COURSE and STUDENT.

(c) Specify all MVDs.

(d) Are the relations in 4NF? If not, give 4NF decompositions.

Explanation / Answer

a) Functional dependencies

course# -> cost

course# -> date

course# -> level

sID -> university

university -> address

sID -> course#

b)

COURSE(course#)

STUDENT(sID)

UNIVERSITY(university)

c)

MVDs

course# -> date

course# -> level

d) As there are attributes which have Multivalue dependencies , the relations are not in 4NF.

4NF decompositions

COURSEDATE(course#,date)

COURSELEVEL(course#,level)

Relations after 4NF

COURSECOST(course#,cost)

COURSEDATE(course#,date)

COURSELEVEL(course#,level)

STUDENT(sID,university)

UNIVERSITY(university,address)

Related Questions

Navigate

• Browse (All)
• Browse 7
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.