September 20th, 2012, 04:00 AM
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
Last edited by chisoko_n; September 20th, 2012 at 08:19 AM.
September 20th, 2012, 05:34 AM
1 we don't do homework assignments, but we will help if you have specific questions
2 you didn't ask a question
September 20th, 2012, 08:13 AM
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
Originally Posted by r937
September 20th, 2012, 09:13 AM
Originally Posted by chisoko_n
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...
September 20th, 2012, 11:39 AM
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
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.