4th Normal Form of table not met

Question

Below is a graph of a database to be used to manage university student enrolment and grades across multiple years. Below are the listed requirements for the database

• Students must be able to be a part of a class
• A class must teach a subject
• Each class may have 0 or more courseworks
• Each class will have one exam
• Each class can be taught by more than one lecturer
• Coursework can only be set by one lecturer
• Coursework and exams can be marked by different staff than who set them, and the staff member marking it must be able to be identified and recorded.
• It is necessary to specify whether an exam taken is being taken for the first time or is a resit

I think the database is now in 4th normal form, and is represented in the table below.

The key represents the primary key for that table, and a green arrow means it is a foreign key.

Can anyone spot any errors or suggest ways to improve it?

Answer 1

Obviously the cardinality between coursework and courseworktaken cannot be 1:1.

(Why are some lines dotted and others not ?)

Answer 2

Not enough information here to tell whether you are satisfying any Normal Form or not. We can only guess at some dependencies.

For example, "Each class will have one exam" seems to be saying that class→exam. Your Exam table on the other hand satisfies the dependency examID→classID, which is not one of your requirements. I can't tell from your diagram if classID is a candidate key in the Exam table. It also looks like examTaken would not be in 4NF if the classID→examID is one of the dependencies to be satisfied.

From a practical data modelling point of view 4NF is not very important. 5NF is more important. Is this homework? If so I'd suggest you write down the attributes and dependencies before you start drawing a diagram. You seem to have created far more attributes than are suggested by the statement of requirements.