Newbie Trying To Create A Dependency Diagram And Determine Relationships

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. :confused:

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 :confused:

Any pointers and suggestions would be greatly appreciated

Thanks