# Database: Relational Schema Problem

Name: _________________________________

Answers are worth one point unless otherwise indicated.

1. (2 points) Class Database - You're building a database of courses and the sections that offered. Each row of the `Course` table is a description of a single course, with a primary key (PK) of `course_code`. A second table, `Section`, has information about each section that is offered. There may be more than one section offered for a course. Draw a line connecting the two tables indicating the cardinality/multiplicity of the relationship.

   Course

   course_code | PK
   course_title |
   course_description |

   Section

   section_id | PK
   course_code | FK
   section_year |
   section_term |
   section_classroom |
   section_time |

2. ________ (T/F) In the `Course` table, the `course_code` field must be unique (only one row with a given value).
3. ________ (T/F) In the `Section` table, the `section_classroom` field must be unique.
4. ________ (`Course`/`Section`) If a `location` field is added (eg, Lakenheath, Ramstein, ...), which table should it be added to?
5. If we add an `Instructor` table with a primary key of `instructor_id` to record who's teaching what, how should it be connected to the `Course` and/or `Section` tables? Assume an instructor can teach more than one section, and only one instructor teaches a given section.
   1. Add a foreign key to the `Course` table pointing to the instructor who is teaching the course.
   2. Add a foreign key to the `Section` table pointing to the instructor who is teaching the section.
   3. Add a field to the `Instructor` table with the `section_id` of the course that he/she is teaching.
   4. Add a field to the `Instructor` table with the `course_code` of the course that he/she is teaching.
   5. Because the `Instructor` - `Section` relationship is many-to-many, an intermediate table must be built to relate these two.
   6. Because the `Instructor` - `Course` relationship is many-to-many, an intermediate table must be built to relate these two.
6. (2 points) Students are missing from the database. Show how a `Student` table should be connected to the others to show who's enrolled in what. How would this relationship be implemented?
   - If you think the relationship can be created by adding fields, show the additional fields (marked in the diagram below with "?") along with the line showing the cardinality/multiplicity.
   - If you think the relationship needs an additional intermediate table to implement a many-to-many relationship, show that table and lines indicating the cardinality/multiplicity.
   There is no need to show the relationship between `Course` and `Section` since that was already given above.

   Course

   course_code (PK)
   course_title
   course_description
   ?

   Section

   section_id (PK)
   course_code (FK)
   section_year
   section_term
   section_classroom
   section_time
   ?

   Student

   student_id (PK)
   student_name
   student_email
   ?