We describe a mechanical procedure for converting an E-R diagram to a relational schema. We will use the student-section-course database as an example:
1. (non-weak) Entity sets.
Create a relation for each entity set. It can use the same name and same set of attributes as the entity set. The key of the entity set is used as the primary key of the relation. For example,
Student(ssn, last, first, streetaddress, city, state,
Course(dept, number, title, description, credits)
Section(crn, days, time, room)
Faculty(ssn, last, first, title, dept, office, phone)
a. Binary many-to-many relationships
For each such relationship, create a relation. The attributes of this relation are the attributes of the relationship, together with the primary keys of the two related entity sets. The primary key of the relation combines the primary keys of the related entity sets.
In the example database, the "is taking" relationship would be converted into
Enrolled(ssn, crn, gradeOption, credits)
In the case of a recursive many-to-many relationship, it is necessary to rename the primary keys borrowed from the related entity sets. For example, if we want to implement the "is a prerequisite of" relationship between courses, we would create a relation as follows:
Prerequisite(prereqDept, prereqNumber, successorDept,
b. Binary one-to-many relationships
There are two ways to handle this type of relationship. One is to follow essentially the same approach as with the many-to-many relationship, by creating a new relation to represent the relationship. For example, for the "is teaching" relationship, we would create a relation
Note that the primary key of the relation is just the primary key of the section, not the combination of both primary keys.
An alternative is to simply include the primary key of the "one" entity set in the relation representing the "many" entity set. In this case, we would simply add the instructor ssn to the section relation:
Section(crn, days, time, room, instructorSsn)
What if the one-to-many relationship has attributes?
Which method is preferable?
c. Binary one-to-one relationships
As with the one-to-many relationships, they can be implemented by
a new relation or by including the primary key of one entity set
relation representing the other one.
d. n-ary relationships
Create a relation representing the relationship. Include the primary key of each participating entity set. Any attributes of the relationship become attributes of the relation. For example, if students, majors, and faculty advisors are related by a ternary "advises in" relationship, we would create a relation:
Advises(studentSsn, advisorSsn, major)
What is the primary key of the resulting relation?
3. Multivalued attributes
Suppose we are using a version of the E-R model which permits multivalued attributes. For example, we might model "major" as a multivalued attribute of "student". The relational model does not permit nonatomic attributes, so to implement this attribute, we need to create a new relation to represent it. A tuple of the relation contains a value of the attribute and the primary key of the owning entity. For example,
What is the primary key of this relation?
In some cases, particularly where it is known that there are at most two values of the attribute, it may be preferable to define two attributes in the owning entity, rather than creating a separate relation. For example, a student might have a local address and a home address.
Q: How would the student-major example be handled if we
multivalued attributes even from our E-R diagrams?
4. Weak entity sets
The procedure described above for representing entity sets does not work for weak entity sets, because they do not contain their own primary keys as attributes. Some allowance must be made to handle this situation.
The solution is simply to copy the needed key attributes from
entities into the relation representing the weak entity. For
if departments are modeled as entities, and the department code is
of the key of courses, then course becomes a weak entity.
code must be included as an attribute of course, so it can be used
of the primary key.
Suppose we have an entity with three subentities; that is, there are three entities, with an "is-a" relationship to the first entity.
There are several ways in which this E-R diagram can be implemented in the relational model.
a. Use one relation for the superclass and one for each subclass. The superclass has its own key and its own attributes. Each subclass has its own attributes and the key attribute(s) of the superclass.
Problem: To find all the attributes of a single entity, it is necessary to find tuples in two different relations.
b. Subclass relations contain all subclass attributes and all superclass attributes. An entity which is a member of a sublcass does not have a tuple in the superclass relation.
This method works best if every entity is a member of exactly one subclass. Then there is no need for the superclass relation at all.
Problem: To find an entity given its key, it is necessary to search several relations.
Problem: To display only the superclass information for each entity, it is necessary to traverse several relations.
c. Just use one big relation. Include all superclass and subclass attributes as attributes of this relation. In this case, some attributes will not be applicable to certain entities; use the NULL value for those attributes. It is also necessary to indicate somehow the subtype of a given tuple; that is, which subclass(es) does it belong to. How could this be done?
Problem: Lots of NULL values.