Entity Relationship Diagram Design

Figure 1: The Entity Relationship Diagram of School Database

(Source: Created by Author)

The entity relationship diagram designed in Figure 2 illustrates that many teachers can teach only in one school. Similarly, the students can get admission to a single school only. One or more teachers will be teaching different subjects. This implies that the relation between teacher and subject is many-to-many. As real life database does not support many-to-many, a bridge table has been introduced. This table will hold the time of teaching a subject and the date. The teacher will have one-to-many relation with Teacher_Subject and the subject table will have one-to-many relation with Teacher_Subject. The student and subject also has many-to-many relation among them. At a time one teacher will assigned to manage the school. As many teacher can manage school in long period of time, the manage table has been introduced. This table holds the start and end of managing the school.

Table: School Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper
Attribute	Description	Data Type	Constraint	Example
School_code	This attribute is used for uniquely identifying the rows of the school entity	Varchar (40)	Primary Key	Sch111
Name	This attribute holds the name of the school	Varchar (40)		St. Thomas
Town	The name of the town in which the school is located	Varchar (40)		Brisbane
Street	The street name	Varchar (40)		Camac Strett
Postcode	The postcode of the area where the school is located	Varchar (40)		1234
Phone	The contact number of the school	INT (10)		1234567890

Table: Student
Attribute	Description	Data Type	Constraint	Example
Student_Number	This attribute is used for uniquely identifying the rows of the student entity	Varchar (40)	Primary Key	Std111
School	The id of the school in which the student is going	Varchar (40)	Foreign key references School table (School_code)	Sch111
First_Name	This attribute holds the first name of the student	Varchar (40)		Johnny
Last_Name	This attribute holds the last name of the student	Varchar (40)		Bravo
Gender	The gender of the student (male or female)	Varchar (40)		Male
DOB	The date of birth of the student	Date		12/04/1993

Table: Subject
Attribute	Description	Data Type	Constraint	Example
Subject_Title	Uniquely identify each row of the subject entity	Varchar (40)	Composite Primary Key Foreign Key references to Subject table (Subject_Title)	Mathematics
Type	The category of the subject	Varchar (40)		Science

Table: Teachers
Attribute	Description	Data Type	Constraint	Example
Tax_File_Number	This attribute is used for uniquely identifying the rows of the student entity	Varchar (40)	Primary Key	1Z 05F 16F 24 1272 269 5
School	The id of the school in which the teacher teaches	Varchar (40)	Foreign key references School table (School_code)	Sch111
First_Name	This attribute holds the first name of the school	Varchar (40)		Blossom
Last_Name	This attribute holds the last name of the school	Varchar (40)		Grill
Gender	The gender of the teacher (male or female)	Varchar (40)		Female
Qualifications	The qualification of the teacher is located	Varchar (200)		Bachelor of Science, Master of Science in Mathematics

Table: Teacher_Subject
Attribute	Description	Data Type	Constraint	Example
Teacher	The primary key of the teacher entity	Varchar (40)	Foreign Key references to Teacher table (Tax_File_Number)	1Z 05F 16F 24 1272 269 5
Subject	The name of the subject that the teacher teaches	Varchar (40)	Foreign Key references to Subject table (Subject_Title)	Mathematics
Strat_Time	The time at which the teacher starts teaching subject	Time		10:00:00
End_Time	The time at which the teacher class ends	Time		12:00:00
Date	The date on which the teacher teaches the	Date		23/02/2018

Table: Student_Subject
Attribute	Description	Data Type	Constraint	Example
Student_Number	The primary key of the student entity	Varchar (40)	Composite Primary Key Foreign Key references to Student table (Student_Number)	Std111
Subject_Title	The primary key of the subject entity	Varchar (40)	Composite Primary Key Foreign Key references to Subject table (Subject_Title)	Mathematics

Table: Manage
Attribute	Description	Data Type	Constraint	Example
Teacher	The primary key of the teacher entity	Varchar (40)	Foreign Key references to Teacher table (Tax_File_Number)	1Z 05F 16F 24 1272 269 5
School	The teacher manage the school which has this id	Varchar (40)	Foreign key references School table (School_code)	Sch111
Strat_Date	The date from which the teacher will start managing the school	Date		12/02/2008
End_Date	The date from which the teacher leaves the responsibility of managing the school	Date		12/02/2016

INSERT INTO `school` (`School_code`, `Name`, `Town`, `Street`, `Postcode`, `Phone`) VALUES (‘sch111’, ‘Abbotsford Public School’, ‘Abbotsford’, ‘Segenhoe St’, ‘2336’, ‘65437271’);

INSERT INTO `teachers` (`Tax_File_Number`, `School`, `First_Name`, `Last_Name`, `Gender`, `Qualifications`) VALUES  (‘1Z 8A2 1E5 72 4437 495 6’, ‘sch111’, ‘Jayden’, ‘Rule’, ‘Male’, ‘MSC in Math’);

INSERT INTO `teachers` (`Tax_File_Number`, `School`, `First_Name`, `Last_Name`, `Gender`, `Qualifications`) VALUES  (‘1Z 05F 16F 24 1272 269 5’, ‘sch111’, ‘Angelina’, ‘Julia’, ‘Female’, ‘PHD in Physics’);

INSERT INTO `student` (`Student_Number`, `School`, `First_Name`, `Last_Name`, `Gender`, `DOB`) VALUES (‘STD111’, ‘sch111’, ‘Sophia’, ‘Poltpalingada’, ‘Female’, ‘1997-11-08’);

INSERT INTO `student` (`Student_Number`, `School`, `First_Name`, `Last_Name`, `Gender`, `DOB`) VALUES (‘STD113’, ‘sch111’, ‘Keira’, ‘Rae’, ‘Female’, ‘1995-04-04’);

INSERT INTO `subject` (`Subject_Title`, `Type`) VALUES (‘Mathematics ‘, ‘Science’);

INSERT INTO `subject` (`Subject_Title`, `Type`) VALUES (‘Physics’, ‘Science’);

INSERT INTO `manage` (`Teacher`, `School`, `Strat_Date`, `End_Date`) VALUES (‘1Z 05F 16F 24 1272 269 5’, ‘sch111’, ‘2002-04-11’, ‘2010-04-17’);

INSERT INTO `manage` (`Teacher`, `School`, `Strat_Date`, `End_Date`) VALUES (‘1Z 8A2 1E5 72 4437 495 6’, ‘sch111’, ‘2010-04-18’, ‘2018-04-02’);

INSERT INTO `Student_Subject` (`Student_Number`, `Subject_Title`) VALUES (‘STD111’, ‘Mathematics ‘);

INSERT INTO `Student_Subject` (`Student_Number`, `Subject_Title`) VALUES (‘STD113’, ‘Mathematics ‘);

INSERT INTO `Teacher_Subject` (`Subject`, `Teacher`, `Strat_TIme`, `End_Time`, `Teaching_Date`) VALUES (‘Mathematics ‘, ‘1Z 05F 16F 24 1272 269 5′, ’02:00:00′, ’04:00:00’, ‘2018-03-07’);

INSERT INTO `Teacher_Subject` (`Subject`, `Teacher`, `Strat_TIme`, `End_Time`, `Teaching_Date`) VALUES (‘Physics’, ‘1Z 05F 16F 24 1272 269 5′, ’10:30:00′, ’13:00:00’, ‘2018-03-07’);

Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational database schema. International Journal of Database Theory and Application, 8(3), 15-26.

Veikkolainen, T., Pesonen, L. J., & Evans, D. A. (2014). PALEOMAGIA: A PHP/MYSQL database of the Precambrian paleomagnetic data. Studia Geophysica et Geodaetica, 58(3), 425-441.

Yang, L., & Cao, L. (2016). The Effect of MySQL Workbench in Teaching Entity-Relationship Diagram (ERD) to Relational Schema Mapping. International Journal of Modern Education and Computer Science, 8(7), 1.