#3 DB Optimisation / Normalisation - v1.54

[Ap Muthu]

Attached is an ERD for the v1.54 SchoolMate.sql. You
will find that there is a lot of redundancy. It should make
sense to move FName, LName and MI from students,
teachers, parents and adminstaff into the users table.
The teacherid in the courses table should be in green
(non-identifying).

If we wish to do away with the parent_student_match
table, then an extra field in the users table - reportto -
can be used to indicate parent's userid or managing
staff's userid - for hierarchial organisation - retrieval
being done using a self-join.

All Identifying relationships (in blue in the gif attached)
can be done away with if appropriately redesigned.
eg., the courses table can be split up into courselist
table and regcourses table.
The courselist table would have:-
courselistid
coursename
q1points
q2points
totalpoints
a, b, c, d perc
secondcourselistid (using self-join)

and the regcourses table would have:-
courseid (named so as not to disturb all other related
tables)
semesterid (which already has a link to it's parent having
the termid)
teacherid,
sectionnum,
roomnum
periodnum
dotw
substituteid

Other tables such as registrations can then merely have
courseid to replace
courseid
semesterid
termid

Ap.Muthu

[Ap Muthu]

ERD for Schoolmate v1.54