1) Here's the diagram:
2) 1. There's always just one primary key for a table in 1NF. It is composed of three fields (or attributes), but it is still just one primary key.
4) There are three of them:
- student # —> student name, major
- course, section —> instructor id, instructor name, instructor office
- course —> title
5) instructor id —> instructor name, instructor office
6) Here's the 3NF diagram:
8) 5. There's always one primary key per table, so the answer to this question has to be the same as the answer to the previous question.
Now, assume that semester information is kept in the database. That is we're going to keep track of the enrollment of students in classes for more than one semester. How will this change the tables in the database? Try this before you read on.
You should add two fields to the primary key of the section table (semester, year) and two fields to the primary key of the enroll table (semester, year). Now we know which instructors teach which section in which semester and which students take which sections in which semester.
The database table descriptions look like the following:
Instructor(id*, name, office)
Section(course*, section*, instructor)
Student(id*, name, major)
Enroll(course*, section*, student*, grade)
The database tables (with data) look like the following:
Back to original problem.