Advice with ERD & Tables - School System
Posted on 2006-07-12
I am currently learning to take practical solutions and formulate an ERD Diagram, I have most of it done but a few concepts still elude me.
I have entities for: Student, Class, Department, Teacher, Grade, Room, Degree, and the ERD diagram made.
School, Person(Super Class), Department_Head are entities I deemed unneccesary unless someone can say why I might need them.
Eventually I'm going to have to make a mock database probably using Access to go from ERD to actual database, so I want the ERD as spot on as possible.
So I have the relationships between all of these fine what I am finding difficult is determining a way of tracking student progress.
Each student takes X classes, Y are required for the degree Z are not, so do I have an entity progress as well?
Department - Teacher (1:N)
Department - Module (1:N)
Teacher - Module (1:N)
Student - Class (N:M)
Student - Grade (1:N)
Degree - Student (1:N)
Grade - Class (1:N)
Class - Class (1:N)
Class - Degree (N:M)
Class - Room (1:N)
So this takes care of the relationships for everything except tracking a students progress. So would it be wise to add in a entity say "Transcript" and have the following relationships?
Transcript - Degree (1:1) the reverse of this makes no sense tho unless its optional
Transcript - Course (1:N)
Transcript - Grade (1:N)
Transcript - Student (1:1)
A lot of this seems implied, for example; A course has a grade as shown earlier so showing a transcript has both many courses and many grades seems sliightly silly... So if anyone has any advice here or to just stop.
Im not looking for a really complicated ERD or table structure when im ready to progress, this is just for self learning as I follow a book.
Im assuming once im ready to go to tables the following tables are what Ill need:
Class(ID, Year, Period, Teacher, Time, Room)
Teacher (TID, Name, Contact_Info, DeptID)
Student (SID, Name, Contact_Info)
Only problems im having now is formulating ways to record grades, as I wouldnt want them to be in the Class table as if there are 100 students then thats a lot of repeated information etc
So I would appreciate any help if someone can help clear some of this up for me ;) Maybe I should have picked an easier subject to learn for my first time lol.