Answer to Class List problem

1) Here's the diagram:
classlistdd1nf.png
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.
3) 3
4) There are three of them:

  1. student # —> student name, major
  2. course, section —> instructor id, instructor name, instructor office
  3. course —> title

5) instructor id —> instructor name, instructor office
6) Here's the 3NF diagram:
classlistdd3nf.png
7) 5
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)
Course(id*, title)
Student(id*, name, major)
Enroll(course*, section*, student*, grade)

The database tables (with data) look like the following:

Instructor
id name office
1253 Norma L. Form E2616
Section
course section instr
BIT330 3 1253
Course
id title
BIT330 Web based Info Res.
Student
id name major
38214 Bright BIT
40875 Cortez FIN
51893 Edwards BIT
Enroll
course section student grade
BIT330 3 38214 A
BIT330 3 40875 B
BIT330 3 51893 A

Back to original problem.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License