Today, l’m discussing the relation schema. This document comes directly from the Entity Relationship Diagram. However, l don’t think you need an entity relationship diagram to create the relation schema but l think its the approved way of going about the database design.
I’m going to use a sample Entity Relation schema l drew for a fake business to continue discussion of the Relation schema. Relation schema is just writing the entities and attributes. For example, if we have an entity person and it has the attributes ssn, name,address and ssn is the primary key. In creating the relation schema for this entity, it will be written as shown below.
person(ssn,name,address). The ssn will be underlined to indicate it’s the primary key.
To make the discussion a little easier let’s look at the fake business l was talking about and the ER diagram for it, then we can create a relation schema.
———————————————————————————————————————————————————————
The following is a business description of a mythical college and its data tracking requirements. Produce an ER-Model which would describe the following data and a Relation schema for the college.
DoyleCollege has several schools. Each school is composed of several departments. Each department offers several courses. One or more sections of a course may be taught during any given term. Each course has a value of college credits a student may earn for passing the course. Professors are associated with a department. A professor may teach up to three courses per term. Students may take up to 16 college credits per term.
The College needs to track professors and what course sections they have taught, because professor pay will depend on the course load. Student records must include tuition payments, courses taken, and grades received. Each student’s department, major, and advisor must also be included in the student records. Since each course is given on the Doyle campus, the course section’s room, building, and time must be recorded.
The College also needs to track its maintenance expenses. Expenses are to be tracked by maintenance category. Maintenance categories include: groundskeeping, daily janitorial activity, interior room painting, exterior building painting, roof repair, roof replacement, and utility expenses. All of these maintenance activities are obtained through contract from other companies, rather than performed byDoyleCollege employees.
——————————————————————————————————————————————————————-
So given this system, I came up with this ER diagram below.
This ER Diagram is not the best ever, anyone can come up with something better than this but this is all l can do at this time. All l want to explain is how to draw the Relatioin schema at this time so l’ll not go into details explaining the creation of the ER diagram. It will be good to read the previous tutorial l wrote on ER diagram and try to make sense of this diagram. So let’s go ahead and create the Relation schema for the entities. We are going to make up some attributes where necessary just for fun and understanding. So we take each entity and create a schema for it. If there is a relationship between entities, we take care of it using either foreign key or conjunction table. For a many to many relationship, we use conjunction table. The conjunction table will be made up the primary keys of the tables involved in the relationship and if there is any relationship attribute, it’s added to the conjunction table.
For one to many and one to one relationship, a foreign key is used to link up the entities. For one to many relationship, the foreign key is on the many side and references the primary key in the one side. For one to one relationships, the foreign key can be on any side.
So let’s start with our entities.
DoyleCollege(collegeid,year_established,chancellor).
Collegeid can be underlined and used as the primary key for this entity.
school(collegeid,schoolid,head,address).
Here, we link up the school and college because they have a relationship. collegeid and schoolid should be underlined as the primary key for this table. The foreign key is collegeid and references the column collegeid in the DoyleCollege entity. The school entity has the foreign key because it’s the entity on the many side of the relationship. That means, the college can have many schools. For example, school of arts, school of engineering etc, but each school belongs to only one college. So that’s a one to many relationship. Also, we can make the school a week entity, that will clear things even better. Which means the school depends on the college to exist. Meaning, no college, no school.
Let’s take the ternary relationship of Doyle college, maintenance company and maintenance expense and see how to build the table.
In this type of association, we’ll break up the relationship as follows, first create table for the entities, then create a conjunction table to connect all the entities. for example, we can say college_company_maintenance(collegeid,maintenance_id,company_id,—–). This table will have collegeid,maintenance_id,company_id as the primary key and any relationship attribute based on the ternary relationship will be included in this table.
This procedure should be continued until all entities are taken care off.
The next topic I’ll be discussing is Normalisation, keep in touch, questions, corrections and feedback welcome.
