Newbie trying to create a Dependency Diagram and determine relationships

[dbnewbie77]

Hello : )

I am currently taking a database design class using Access 2003 and am looking for some help. I am trying to draw a dependency diagram before I create the tables based on the following attributes / fields in one table named Student:

StudentNumber, StudentName, StudentMajor, student's AdvisorNumber, AdvisorName, AdvisorOfficeNumber, AdvisorPhone, student's NumberCredits and student's Class (freshman, sophomore, etc.)

So far I have identified the Primary Keys as StudentNumber and AdvisorNumber and this is what I have:

Table #1 - Student = Student Number functionally determines Student Name, StudentMajor (?) NumberCredits and Class
Table #2 - Advisor = Advisor number functionally determines Advisor name, office number and phone

The problem I run into is with the the StudentMajor / Advisor / StudentNumber relationship. Since one student can have many majors and therefore many advisors since there is only one advisor per major, and each advisor has many students I am assuming it is a many-to-many relationship.

The problem I am having is determining the third table (and fourth if there will be one) and the relationship between StudentMajor / Advisor / StudentNumber.

Should StudentMajor be included as functionally dependent on StudentNumber? If it is there would be redundancy due to multiple entries in that field for each row so I guess I can't include it actually.

Wouldn't there have to be another attribute named AdvisorDept for this to work properly....that way there is a relationship between Advisor and their department they work in so I can link the student's major to the advisor

Any pointers and suggestions would be greatly appreciated

Thanks

 

[Kraj]

There are several ways to do this and it all depends on how much freedom you are given in your assignment. The simplest and most restrictive is to have your third table be Majors - StudentNumber, StudentMajor, AdvisorNumber. You are correct that StudentMajor is dependant on StudentNumber, and also correct that it doesn't belong in the table because of redundancy. Since the relationship is many-to-many you should break it out into a seperate table. Adding AdvisorNumber links the student to multiple advisors.

However, setting up the third table this way introduces an additional violation of normal form, but fixing it requires more fields and information beyond what you provided. You need to know what relationship advisors have with majors. Are there multiple possible advisors per major or only one? Can one advisor oversee a variety of majors or only one? Without knowing this there's no way to determine the proper relationship and table structure.

 

[dbnewbie77]

Many thanks

Kraj, thank you very much for the reply. This is the conclusion I was headed to as well with regard to the inability to make a relationship between StudentMajor and AdvisorNumber without additional information. You have reassured my confidence in my own reasoning abilities.

Thank you kindly