Primary and foreign keys - Assignments
1. Courses and enrollments
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 first 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 first 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
subscriptionstable.
For this, the following new design has been made:
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.
