Exercise 4.1

Consider the student and section entities from the grading database shown, in slightly modified form, here.

  1. Is it possible to combine these entities/relationships into a single relation. If so, specify the relation schema. If not, explain why not.

  2. Is your resulting schema well-designed? Explain why or why not.

Exercise 4.2

Use normalization to specify an appropriate schema for the student & section entities.

  1. Determine the normal form of your schema.

  2. If it isn’t in BCNF, decompose it so that it is and demonstrate that:
    1. all the data and relations in the original relation(s) are still recoverable.
    2. no spurious tuples are created when joining the sub-relations.

Exercise 4.3

Consider the following relation (cf. MVD) in which the department independently assigns the instructor(s) and text(s) for each course.

CourseAssignment(courseId, instructorId, textId)

  1. What problems, if any, do you see with this relation?

  2. Prove (or disprove) that the section/text relation is in:
    • BCNF.
    • 4NF.

  3. If it isn’t in both BCNF and 4NF, try decompose it so that it is, and then demonstrate either that you’ve succeeded or that it’s impossible.