Primary and foreign keys - Assignments

1. Courses and registrations

We need to set up a data model for a school.

• The school offers a range of courses.
• Students can enroll in each course.

The initial design of the data model looks as follows:

Consider the following: * Which columns are primary key(s)? * Which columns are foreign key(s)?

---

2. Table with students

The initial design of the data model was not optimal.

The data of a student who enrolls in multiple courses is repeated in the subscriptions table.

Therefore, it is useful to:

• Add an extra table students.
• Make adjustments to the subscriptions table.

The following new design has been made for this:

Consider the following: * Which columns are primary key(s)? * Which columns are foreign key(s)?

---

3. (Extra) Expansion of the model

Courses can be given multiple times.

This cannot be processed in the current data model.

An adjustment is needed to make this possible.

You need to be able to process the following data:

• A course covers one subject.
  • A subject has a name and a description.
• A course can be given multiple times.
  • For each time a course is given, a start and end date is known.

Create a new data model for this, paying attention to: * The lowest possible required storage capacity. * A clear overview. * Primary and foreign keys.