School database Design DiagramPosted: asked May 18 at 22:08 - Source : stackoverflow
From your comments,
Personrepresents the generic information about any person tied to this system; students, teachers, other school employees. Information that's in
Personwouldn't need to be repeated in
Student(name, address, DOB).
I would add the student's current grade/class (kindergarten through high school) to
Student. Let's call that
StudentClass; we'll use it later.
Also - you may need an intermediary table to go with
Mark. I would think you'd need something to indicate the total available points for each item (as shown in the headers of your output).
MarkTypemakes sense as is. I would have
Markas a table indicating a particular exam, quiz, homework assignment (etc.). So, it would need:
MarkValue- the points this specific item is worth (for example 10 points).
Then, I would have
Mark- the points earned on this specific item, by this student (for example one student might get 9 points out of the possible 10, other might only get 5).
I would also be inclined to decouple
Subject- there may be subjects that are only offered for one term. Similarly, different instructors may provide a different mix of assignments, and assign different values to a given assignment than another instructor. I would create two new tables:
InstructorId(primary key) -
PersonID(foreign key to
Person, where name, address, DOB would be) - ... (other information specific to a given instructor, regardless of the subject they're teaching, or the term/year they're teaching it in)
CourseId(primary key) -
TermIdwould be removed from
Another note: it might make sense to specifically tie a student to a course. that could be called....
StudentCourseId(primary key) -
StudentCurrentClass(the value of
StudentClassat the time this student was in this class).
In that case, replace
This would even let you identify if there were exams/quizzes/assignments that a particular student in a particular course had no mark for.
UPDATE: Based on your comments, I added some fields to track the student's current "class" (K through high school), and their class when they took a specific course. consists of hard-coded values (in the US, there's kindergarten plus 12 years of education before university, so "K" and "1" - "12" would work here), the
Classtable shouldn't actually be needed.If you need to establish explicit classes for some reason, then the
Classtable would still be needed. I would reverse it, though - one class has multiple students, so put
Studenttable. Note: you may want to consider tying each
Classto a specific
Term, as well.
- user3423060 @user3423060May 19 at 13:41
Thank you so much for your answer :) ,, Person Table is for students + Teachers + employees as well as employers. Class Table : this system is supposed to handle a student data from KG to High school as our education system in the school we have a 15 class . another issue that I will have if we have more than one same class I mean class 2 (A) , class 2 (B) , what should I do !
- user3423060 @user3423060May 19 at 13:46
What the different between Mark value in Mark table and Mark in StudentMarks Table !!
- RDFozz @rdfozzMay 19 at 14:44
Mark_Value is the maximum points available for this test/quiz/assignment. Mark is what this particular student got out of the total available.
- RDFozz @rdfozzMay 19 at 15:07
Updated - should handle Class now (you've still got some options there).
- user3423060 @user3423060May 19 at 20:38
The eeducation year is 2 terms first one and second one So I will put the TermId in Subject table. did you mean the mark is of MarkValue then where should I create the TotalMarks Column ? As you see I have Person Id and StudentId that means the student will have 2 IDs , Is it good to delete the StudentId and use the PersonId as a F.K.? Please explain that to me Is it necessary to have a primary key in any Table created ? I know that by practicing anyone will be better but I want to be good in designing database can you give some title of books to read .