Database design -- does it respect 3rd NF?

Question

Hi

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?

Answer 1

I think it's 3NF normalized (the key, the whole key, and nothing but the key) as far as what we know, but it might not solve your problem domain issues.

Your primary keys would be the _id columns - except for attendees. where it would be both _id columns - but this would not accommodate attending the same lecture on different dates (or would that technically be a different lecture_id?) In a class scheduling system I built, we had sections in a class, but the problem domain for this could be much larger than what you have given.

The problem with students and teachers has already been raised - both will be attendees, that's about all you will know - all the teachers could be teaching or only some of them could be teaching, or they could be peers (seminar, say)

I think this is more of a domain modeling issue first, then normalization...

Answer 2

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

Answer 3

"Do these tables respect the 3rd normal form?"

That question is only answerable if you tell us what the keys are and what the full set of functional dependencies is.

Without that, any answer anyone gives can only be the result of a nonzero amount of guesswork on the part of the answerer, and you have no guarantee that that guesswork corresponds to your business reality.

That said, you might look into the Attendees. It's more than likely that there's something rotten there.

(Hey, you claimed yourself you didn't want to be spoonfed.)

Answer 4

It's definitely not 3NF. A simple, normalised design for the above problem (assuming that person_id uniquely identifies both teachers and students, and that there is one teacher per lecture) would be as follows:

Person
  person_id (PK), first_name, last_name, address, Student_matr_nr, Teacher_salary

Lecture
  lecture_id (PK), teacher_person_id (FK), lect_name, lect_description

Attendees
  lecture_id (PK), student_person_id (PK), date

Relations that have the same key as each other are the same relation.