ERD modelling/design using MySQL Workbench

Part 1:We are going to model a classroom set up that has the following entities

Subject,Teacher,Student,Dormitory,Student File

We are going to learn One to One,One to Many and Many to Many relationships among the entities

To Create the project,Open MySql Workbench.Click File|New Model.

Subject,Teacher,Student,Dormitory,Student File

In the Model Overview window that pops up,click on the default ‘mydb’ schema under Physical schemas .This opens a window below to edit the schema name and add some comments.Change the schema name to ‘school’ and comments as ‘school schema’.Leave the default collation as ‘utf8-utf_8_-general_ci’.Click Save and choose a preferred location on your machine.

Click on the Add Diagram button as shown above.This opens an EER(Enhanced Entity Relationship) window which enables easy modelling of tables and their relationships .

Create tables by clicking the ‘New Table’ icon as shown by drawn arrow followed by clicking on the design pallete.This places a new table entity from whereby you can rename the table name from the defaults.You should have the below design for a start.

Next we are going to add the various fields to the tables.

Double click on student table to bring up the properties/columns editing view as shown below.

Double clicking on the first row automatically enables the first entry as the PRIMARY KEY for our table.The default naming is ‘id<table_name>’.For our case this is idstudent.We will leave this as is.Note that the PK and NN checkboxes are checked.PK means Primary Key and NN means Not Null as we can not have a null for a primary key.Additionaly check the UN (Unsigned)checkbox- to enable only positive numbers for the primary key and AI to enable Auto Increment for the table keys.One of the fields used,admission_no has to be unique to every student and cannot be null as well,so we tick both UQ for unique and NN for not null.

After adding a few properties,the diagram should look as below.

Creating the relationships

One To One Relationship:

We are going to model this relationship between the student and admissionfile entitities/tables.This is because one student can only have one admissionfile and one admission file can only belong to one student.The admission file will contain details about the student such as their guardian details,Health examination report,Any discipline cases record etc.

To model such click the One-To-One relation identified by 1:1

This brings the relation below

The student entity has a field ‘admissionfile_idadmissionfile’ added onto it.This field will reference the primary key of the admissionfile table.In order to get a student file,we will get a student and then lookup the admissionfile using the value of the ‘admissionfile_idadmissionfile’ foreign key.We will rename this field to ‘file_no’ And also untick/deselect it as a Primary Key(PK)

One to Many relationship

This will be modelled by the student and dormitory entities.A student can belong to one dormitory but a dormitory can have many students.

We model this by clicking on the 1:n Non Identifying Relationship(the dotted one),then clicking on the student table and clicking on the dormitory table.

This adds a field, ‘dormitory_iddormitory’ on the student table .This is a foreign key to the ‘iddormitory’ field in the dormitory table.We can use this field to fetch all students in a particular dormitory.

NB:The difference between Non Identifying and Identifying Relationship is as follows.

A non-identifying relationship means that a child is related to parent but it can be identified by its own. An identifying relationship means that the parent is needed to give identity to child. The child solely exists because of parent.This means that foreign key is a primary key too.For more on this refer here.

We now have the below structure

Many to Many relationship

A student can take many subjects and at the same time a subject can have many students taking it. So this is a Many To Many relationship.

Click on the n: m relationship icon .Click on the student table and then click on the subject table.This creates a third table named ‘student_has_subject’as show below.

The student_has_subject table is made of the 2 columns which are primary keys in the respective tables i.e student_idstudent from student table and subject_idsubject from subject table.

We can now allocate student to many subjects in the relation table student_has_subject by simply entering the primary keys of student and subject as entries.The records will have a composite primary key composed of the student_idstudent and subject_idsubject columns.

Many to Many part 2(subject -teacher)

A subject can be taught by many teachers and a teacher can teach many subjects as well.

Lets click in the n:m relationship key from the pallete .Click on the teacher table and then click the subject table.A third table ‘teacher_has_subject is created’.It has a composite key consisted of namely ‘teacher_idteacher’ and ‘subject_idsubject’.We create relationship between teachers and subjects they teach by inserting a teachers primary key and a subjects primary key as a pair into this table.

Creating the database from our EER design(Forward Engineering)

After designing our database to our satisfaction,we can now create a database for the above.We shall use a feature known as forward engineering to create the database tables automatically.We can save the SQL file and upload it or copy paste it on our database console to create the database as well.

I already have phpmyadmin web interface running on my machine .

On workbench ,click Database then Forward Engineer option.

This pops the below screen whereby you specify you’re the database credentials then click next.

On the next screen,you can customize the script for database creation.The below options are activated for this tutorial

Click Next

The below screen appears to confirm our script set up.

Click Next.The below screen appears whereby we have the option of saving the SQL script to file via the ‘Save to File ’ option.We can then upload the SQL script to our MySQL server to create the database.

Click next to execute to script to the target connection.

The process succeeds and we have below screen.

Click Close to finish.

At this point our database is created as shown below.The tables have no data