Database Design

Hi Daniel!

I fail to see what is difficult about this. Of course you know that a many-many relationship means a new table, so in reality you have seven tables: Students, staff, assignments, courses, studentcourses, staffcourses and progress (which you could also think of as a many-many relationship between students and assignments - or will "progress" contain several records for each student for each assignment?)

I've been so lucky that I've never had to work with MS SQL, but here is how I would have joined your tables to answer your specific questions in Firebird:



Assuming 'new assignments' to be assignments dated within the last week, the SQL could be as follows:

SELECT whatever
FROM STUDENTS S
JOIN STUDENTCOURSES SC ON S.ID = SC.STUDENTSID
JOIN PROGRESS P ON P.COURSEID = SC.COURSEID
JOIN ASSIGNMENTS A ON A.ID = P.ASSIGNMENTSID
WHERE A.ASSIGNMENTSDATE >= CURRENT_DATE - 7

then you could add whatever you want to the where clause, e.g. if you only want information for a particular student. One thing that your model lacks, is if you want to link assignments to a particular lecturer. If you only want one lecturer as responsible for an assignment, you can of course add this to the assignments table, or if you want all that is involved in teaching the course to be responsible, you could just leave your model as is. However, if you want one or more specific lecturers to be responsible, you would have to add yet another table ASSIGNMENTSTAFF.



This one is even simpler, just do

SELECT whatever
FROM STUDENTS S
JOIN STUDENTCOURSES SC ON S.ID = SC.STUDENTSID
JOIN COURSES C ON C.ID = CS.COURSEID
WHERE S.STUDENTNAME = :NAME



Here, the main problem is to understand what you're after. I'll interpret this as one lecturer wondering who will attend his courses.

SELECT C.COURSENAME, S.STUDENTNAME
FROM STUDENTS S
JOIN STUDENTCOURSES SC ON SC.ID = S.STUDENTSID
JOIN STAFFCOURSES SFC ON SFC.COURSEID = SC.COURSEID
JOIN COURSES C ON C.ID = SC.COURSEID
JOIN STAFF SF ON SF.ID = SFC.STAFFID
WHERE SF.NAME = :STAFFNAME
ORDER BY C.COURSENAME, S.STUDENTNAME

Did I understand your questions correctly, or did I just add to your confusion?

Set

In my first SQL part, I messed it up. Here's how I meant it to be:

SELECT whatever
FROM STUDENTS S
JOIN STUDENTCOURSES SC ON S.ID = SC.STUDENTSID
JOIN COURSE C ON C.ID = SC.COURSEID
JOIN ASSIGNMENTS A ON A.COURSEID = C.ID
WHERE A.ASSIGNMENTSDATE >= CURRENT_DATE - 7

Right i think i see where you are coming from. In my first design i had it so that:

Staff---TeachesOn---Courses
Students---StudysOn---Courses

(Staff and Students)---Progress=---Assignments

...progress would contain StaffID and StudentID to link staff to a particular Assignment, and therefore a student who is undertaking this assignment.

So do i have the same idea as yours here? If so i think that would work, it just all seems so complicated to me.

It's part of an assignment at college believe it or not, to create a database structure and then a VB and ASP front end. Got a trial copy of MS SQL to make it easier, seen as tho i'm using VB and ASP. Plus the VB front end will be used on a PC that i can't install a new ODBC to, so that's why i chose MS SQL over MySQL or another.

I think i understand now. Would it be easier to execute the SQL in VB/ASP or would it make more sense to create views and extract values from there into VB/ASP?

missed a bit. Must explain that in my design i had Assignments linked to courses through the tables 'TeachesOn' and 'StudysOn'

thought i would also attach this rather messy relationship diagram. This is my initial design, but i wasn't sure about it. Are there any recommendations anyone could make for it?

-- note this jpg is around 97k

I thought each assignment would be part of a course and that you simply had a courseid field in your assignments table.

As for the progress table, I can understand that it is a many to many relationship between assignments and students, but I don't understand why you want to add staff into the equation. Surely, it is the progress of the student you are interested in, not the progress of the staff in creating the assignment?

Sounds like a fair college assignment. When I studied at UMIST (Manchester) donkeysyears ago, we had a group project where we had to design and partially implement a flight information system covering everything from booking a flight to assigning seats on the plane.

I don't know anything about VB/ASP and cannot help you out on that bit. I don't see this as all that complicated, but in Firebird I guess I would have chosen between

-SQL directly on the tables
-stored procedures
-views

If it was something that seems error prone and that possibly have to be written several times, I guess a stored procedure would have been the best way to go. Using views may also hide some complexity, just make sure that it doesn't decrease performance too much.

Set

Well the college assignment is my final big project to create a scenario and analyise the subject to develop a suitable solution. In this case i chose the college system, as the one at college is totally unorganised. Mind you, i don't think mine will be much better!

Well basically, the origional specification is to design a datastructure that will house information about the whole college. The staff table is needed, because the staff will create (and mark) the assignments, and therefore need input and output on the progress table.

Can you explain the confussion with the many to many errors? i created the relationship in Microsoft Access origionally, so it automatically created what it thought was best, what should it be?

thanks again,

Danny

Ah, I just discovered your attachment...

A bit confusing in that some places do say whether it is one or many, whereas it is left out elsewhere, but I guess this is just some additional things to complain about for us that try to avoid Microsoft except for Windows (I'm not yet on Linux).

The question is what the progress table really is. Let's see... Assignments is related to TeachesOn through M:1, hence each assignment is associated with one particular lecturer. Each assignment can have several progress records, but it is not evident whether this is one record for each student or if one student can have several records for each assignment. From the fields mentioned, I would say that progress probably only contains one record for each student per assignment, i.e. more of a StudentAssignmentsResult table than a Progress table (I assumed 'progress' would contain assessment or reports at various stages of the assignment)? And then the staff that grades the assignment may or may not be the same person as the one who made the assignment (if it is always the same person as wrote the assignment, then there is no need for StaffID in the progress table)? It makes sense! Though looking closer I do find a couple of things I do not understand:

1) I do not understand why you have CourseID in your 'progress' table. You already know that through your one-to-many relationships from Courses->TeachesOn->Assignments->Progress.

2) Is the relationship between StudysOn and Assignments really 1:M? Sorry, but I think it is M:M requiring another table... (or, probably preferrably, just drop this one link - it definitely does not make any sense to link StudysOn and TeachesOn to the same field).

3) I assume the relationship between StaffNames and Progress and between StudentDe and Progress to be 1:M. Basically, I expect the Progress table to be a M:M relationship between StudentsDe and Assignments with only the added fields as attributes.

Make sure you document those things that your model doesn't explicitly say. E.g. your model does not say that you can only have a record in 'progress' if you 'studyon' a 'course' for which the 'assignment' was made or whether a 'staff' person in 'progress' has to be associated with the 'course' the 'assignment' is part of.

I never intended to suggest you drop your staff table, just that with my erroneous impression of what 'progress' contained it seemed more sensible to have two tables - one for studentsprogress and one for staffprogress.

Assuming this will end up as a system which several people and machines will update simultaneously, I recommend you to stay away from desktop databases like Access or Paradox and rather go for some proper client/server-databases. I don't know MS SQL Server beyond having heard negative things about it, but it is at least a client/server database so it ought to manage. Alternatively, Firebird or PostgreSQL are good alternatives that are free (price have nothing to do with quality, Firebird is better than it's licensed alternative InterBase), and I think Oracle have some decent licensing options for studying (though Oracle does require quite a bit of database administration). Don't choose MySQL, it doesn't handle transactions properly.

Good luck in creating a great system for the college in Wakefield (or do you study somewhere else, I've never even heard of Wakefield before)!

Now I have to go and buy myself some groceries before the shop closes!