#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    77
    Rep Power
    6

    Relational database help


    I am designing an application that uses a database to track students attendance for Saturday school programme for disadvantaged kids. I am using MS Access with the following information:

    1. StudentTable: StudentID, SubjectID, StudentName, Surname, School etc
    2. StudentAttendanceTable: StudentID, 1stTerm, 2ndTerm, 3rdTerm, 4thTerm
    3. TutorTable: TutorID, Name, Surname, IDNumber, SubjectID
    4. TutorAttendanceTable:,TutorID, 1stTerm, 2ndTerm, 3rdTerm, 4thTerm
    5. SubjectTable: SubjectID, TutorID, SubjectName
    6. StudentResultsTable: StudentID, Grade1, Grade2, Grade3, Grade4

    I have set up a relational table where I want the following to happen:

    1. A student can only pick one subject, have one attendance record, have one set of results
    2. A tutor can only teach one subject, have one attendance record

    Not sure how to attach the image properly, hopefully it shows else please tell me how
    Attached Images
    Last edited by chisoko_n; September 20th, 2012 at 07:19 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    1 we don't do homework assignments, but we will help if you have specific questions

    2 you didn't ask a question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    77
    Rep Power
    6
    Originally Posted by r937
    1 we don't do homework assignments, but we will help if you have specific questions

    2 you didn't ask a question
    My problem is linking my tutors with the subjects in a one to one relationship. WHere if I delete my tutor, It will also delete the tutors subject or update. Instead I have a one to many relationships currently between my tutors table and my subjects table. I think it is a lot easier If you look at my ERD attached
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by chisoko_n
    Instead I have a one to many relationships currently between my tutors table and my subjects table.
    um, no

    if subjectid is the pk of subjects, then because tutorid is in that table, a subject can have only one tutor

    and vicey versey

    regardless of the infinity symbols on that diagram

    mind you, getting the subject fk and tutor fk in their respective tables to work properly, that's going to be fun when you load the data...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    If you can have multiple tutors work on multiple subjects than it's pretty easy to create a cross reference table Tutors_Subject

    It has two columns

    TutorID, SubjectID

    each is a foreign key to the primary key in the respective table.

    This way a subject can have multiple tutors and a tutor can tutor multiple subjects.

IMN logo majestic logo threadwatch logo seochat tools logo