Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    1

    Unanswered: Can someone help me make sure my DB is in 3NF?

    Course (DNum, Code (FK), Description)

    Courses_Taken (DNum, Code, SID (FK) Grade)

    Student (SID, SName, ANum (FK))

    Advisor (ANum, AName, DNum (FK))

    Department (DNum (FK), DName)

    I think it is.. just seeing if anyone can double check since I am now wholly sure.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you are part way there....
    but having a brief look at your model I think there are some questions you need to ask yourself.....
    can a student study on more than one course in any one academic period
    can a student have more than one advisor
    does a course comprise modules
    can a student study a module in another department
    presumably in Courses_Taken DNum and Code are also FK's
    what are your PK's
    do you allow students to change courses part way through the course duration, and if so how do you propose to handle credits earned on previous years

    Personally I think you have some work to do on your column names
    code? wtf do you mean CourseCode?
    keep up the good work.. I think you are close, but not there yet.....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is DNum really a foreign key in department?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You know this notation..?
    TableName(PrimaryKey, Field1, Field2 ...)
    How do you show a foreign key?
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Here's my two cents worth:
    Course (DNum, Code (FK), Description)
    tblCourse (CourseCode(PK), DepartmentID, Description)

    Courses_Taken (DNum, Code, SID (FK) Grade)
    tblCoursesTaken (CourseCode(FK), StudentID(FK), DateTaken, Grade)

    Student (SID, SName, ANum (FK))
    tblStudent (StudentID(PK), StudentFirstName, StudentLastName, AdvisorID(FK))

    Advisor (ANum, AName, DNum (FK))
    tblAdvisor (AdvisorID(PK), AdvisorFirstName, AdvisorLastName, DepartmentID(FK))

    Department (DNum (FK), DName)
    tblDepartment (DepartmentID(PK), DepartmentName)

    Ideas show above from experience: CamelBack names. Capitalize first letter of each word, no underlines, spaces, or other special characters. Also, stay away from numbers, they usually don't help either; Use "tbl" (or something) to show this is the name of a table. And "qry" for Queries, "frm" for forms, "rpt" for Reports. Macros and Modules ("mac" and "bas") are not used like the other objects are, so they are not near as important to know. Generally, if they ever show up, it would be obvious. But for consistency, best to use them too.

    For more really great guildlines, follow the link in my signature for Access Design Tips.

    HTH,

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    vic, i gotta disagree with you on the use of "tbl" prefixes

    totally superfluous, unnecessary, redundant, extraneous, useless, and bothersome

    have you ever accidentally used a column name where you should've used a table name? have you ever accidentally used a table name where you should've used a column name? SQL syntax won't let you!!

    granted, as far as the syntax goes, you can use tables and queries interchangeably in access, just like you can use tables and views interchangeably in other databases, but there is a good reason why you don't want to name them accordingly -- one day you'll need to write a query (view) to produce what is now stored in a table, and then you'll have to go and change all your other stored queries which reference that table, and then you're sunk

    also, please don't hand students any answers, make them come up with their own answers by guiding them along the thought process
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Rudy,
    Did not know this was a student. I just saw your other post and thought about what I did with this one and said, "Ops".
    In my 13 years with Access, I have never had to change from a table to a query, except when that table was used as the RecordSource for a form or table. At which point I did not write a QueryDef, but just the SQL for that particular Form or Report.

    But I did have many times when I could not remember if this object was a table or query. And there are tools to go through all the code and objects to do a Find and Replace. I know some people can remember a whole lot more than I can. For example, it took me over two years to finally be able to remember the command RunCommand. Not proud of it, but I do watch how I code so it does not hurt me later on.
    But I never have had a problem between a column name and a table or query name. Well, at least not that I remember.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •