Search code examples
sqldatabase-designdatabaserelational

Database design -- does it respect 3rd NF?

I have the following relations (tables) in a relational model

Person
  person_id, first_name, last_name, address

Student
  person_id, matr_nr

Teacher
  person_id, salary

Lecture
  lecture_id, lect_name, lect_description

Attendees
  lecture_id, person_id, date

I'm wondering about the functional dependencies of Student and Teacher.

Do these tables respect the 3rd normal form? Which should be the primary keys of these tables?

Solution

  • Using concepts like "table inheritance" (loosely) and join tables I would set things up in this manner:

     Person
  person_id, first_name, last_name, address

Student
  student_id, person_id, matr_nr

Teacher
  teacher_id, person_id, salary

Lecture
  lecture_id, teacher_id, lect_name, lect_description, date

Attendees
  lecture_id, student_id

    Where Student and Teacher tables "inherit" from Person and the Attendees table is a Join table between Lecture and Student (teacher_id is used in the Lecture table to specify who's teaching the class. And by Join table best practice the table ought actually be named Lecture_Student or similar)

    Alternate Design: (allows for multiple teachers of a class)

    Person
person_id, first_name, last_name, address

Student
student_id, person_id, matr_nr

Teacher
teacher_id, person_id, salary

Lecture
lecture_id, lect_name, lect_description, date

Lecture_Student
lecture_id, student_id

Lecture_Teacher
lecture_id, teacher_id