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 |
||||
|
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.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
