Link to home
Start Free TrialLog in
Avatar of Afterlife
AfterlifeFlag for United States of America

asked on

Advice with ERD & Tables - School System

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:
DegreeList (DegreeID)
DepartmentList(DeptID, Name)
ClassList(ClassID, Name)
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.

Thanks!
 
ASKER CERTIFIED SOLUTION
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Afterlife

ASKER

Thanks for the link, I think it helped.

If I posted the ERD I made think you could give me some critisism?