I'm having difficulty linking some tables together. Basically the database is being hosted on MS SQL, but is still in design stage so any alterations can still be made. I need to link the following tables:
Students - This table holds information about the students attending a college. Each student is given a unique identifier.
Staff - Holds information about the staff, contains a unique identifier for each staff member
Assignments - Holds information about assignments, has unique identifier for each assignment.
Courses - Holds information about courses, has unique identifier for each course.
Progress - This holds information about each student's progress of each assignment. This is also used by the staff to mark assignments, and used by the college intranet to display to students which assignments are currently unfinished.
Now, many students can study on many courses. Many staff can teach on many courses. Each course contains many assignments.
Which is easy enough. However, the database is going to be used to create a more intricate information system, as part of mainly an ASP intranet system. So we need to some how add the progress table to the existing design so we can pull the following information from the database easily...
So from the database we need to:
display new assignments for students (if any) that staff submit
find out which course the student is studying, so we can direct the student to appropriate information
display student names for a staff member for individual courses that that they study on, as well as all courses
and also some other minor queries, but these are not majorly important.
If someone could suggest how we would go about relating the tables together to provide a solution that we can easily query and get the information above, we would really appreciate it, because we are stumped
Really your database design should be based on how the data is actually related (I like your text colour change trick, I'll have to remember that!); examples of specific queries to be answered are not relevant at this stage (they may be relevant to adding indexes later).
So: what is the Progress entity about? It records the progress of a Student on an Assignment. This suggests:
Student -= Progress =- Assignment
Is there just ONE Progress record per Student/Assignment combination? If so, the key for Progress is (StudentID,AssignmentID). If not, you will need some additional attribute to distunguish records.
yep you got it, the progress table records information about the the progress of students for each assignment.
And i think there is one record per student/assignment configuration, but it also needs taking into consideration that the staff have input on the progress, as they create the assignments and eventually mark them
Please find attached the origional relationship defined in MS access, so some of the relationships may be incorrect, please feel free to comment, and recommend any suggestions.