# Database: Relational Schema Problems (2a)

Name: _________________________________

Answers are worth one point unless otherwise indicated.

## Part 1 - Relationship between Course and Section

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_id`. 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. Use crow's foot notation, indicating whether something is option or mandatory.

   Section

   section_id (PK)
   course_id (FK)
   section_year
   section_term
   section_classroom
   section_time

   Course

   course_id (PK)
   course_title
   course_description

2. ________ (T/F) In the `Course` table, the `course_id` 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.

## Part 2 - Adding a Location table

So that we know where each individual class is being taught (assume a particular class is being taught at only one location), a `Location` table is added to the database with a primary key of `location_id`. This table will contain records for UMUC offices at Lakenheath, Ramstein, Heidelberg, etc. Where should a `location_id` be added to link this table into existing tables?

1. `Course` table.
2. `Section` table.
3. A bridging table between `Location` and `Course`.
4. A bridging table between `Location` and `Section`.

## Part 3 - Add an Instructor table

(4 points) Add an `Instructor` table with a primary key of `instructor_id` to indicate 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.

Show where a foreign key should be added and/or draw an intermediate bridging table to show how the `Instructor` table should be integrated into the database. Show all foreign-primary key links using crow's foot notation, indicating optional or mandatory.

Section

section_id (PK)
course_id (FK)
section_year
section_term
section_classroom
section_time
? __________

Course

course_id (PK)
course_title
course_description
? __________

Instructor

instructor_id (PK)
inst_fname
inst_lname
? __________