2

I'm trying to design a database for the information shown in the table below:

enter image description here

In this regard, let's make the following assumptions:

  1. Each Student may take many Courses
  2. Each Course may have many Students
  3. Student Numbers, Course Numbers and Advisor Names are unique
  4. Student Names and Course Descriptions are not unique
  5. Each Student has one Advisor
  6. Each Advisor may advise many Students
  7. Each Advisor has one Office
  8. A Student can visit with his Advisor many times

My current approach

So far, I have come up with the following diagramatic representation:

enter image description here

The question

Being that this contains a many-to-many (M:N) relationship, I am confused as to what the association class (or associative entity type) should be.

What would the correct way to represent this relationship be?

  • for a many to many relation, you need an extra table which matches studentnumber with coursenumber – XtremeBaumer Mar 9 '17 at 8:35
  • What happens if an advisor has a name change? For example, gets married, gender reassignment, or perhaps just a legal name change. What happens if a course changes it description? – Edmund Mar 9 '17 at 10:31
1

You could have a Course table which would just contain the courses and descriptions.

As stated in the comments, you could then have a StudentCourse table which had either a surrogate PK, with foreign keys to Student and Course or a composite PK (StudentNumber and CourseNumber) with the Grade for the student held in it.

Similarly, you could have an Office table and link the Office back to the Advisor. This would allow multiple advisors to have the same office without having to hold the office name multiple times (what if the office name changed for example?).

As also mentioned using the Advisor name as the primary key might not be best, their name could change; you might be better to use a Surrogate key (integer perhaps?) for that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.