Relational Database
Design by ER- and
EER-to-Relational Mapping
By,
Prof. Anisha Cotta
ECS Dept.
DBCE- Fatorda
How to design a relational database schema based on a
conceptual schema design ?
Relational Database Design Using ER-to-Relational Mapping
Step 1: Mapping of Regular Entity Types
For each regular (strong) entity type E in the ER schema, create a relation R
that includes all the simple attributes of E.
Include only the simple component attributes of a composite attribute.
Choose one of the key attributes of E as the primary key for R. If the chosen
key of E is a composite, then the set of simple attributes that form it will
together form the primary key of R.
If multiple keys were identified for E during the conceptual design, the
information describing the attributes that form each additional key is kept in
order to specify additional (unique) keys of relation R.
Knowledge about keys is also kept for indexing purposes and other types of
analyses.
In our example, we create the relations EMPLOYEE, DEPARTMENT, and
PROJECT in to correspond to the regular entity types EMPLOYEE,
DEPARTMENT, and
PROJECT. The foreign key and relationship attributes, if any, are not included
yet; they will be added during subsequent steps.
These include the attributes Super_ssn and Dno of EMPLOYEE, Mgr_ssn and
Mgr_start_date of DEPARTMENT, and Dnum of PROJECT.
In our example, we choose Ssn, Dnumber, and Pnumber as primary keys for
the relations EMPLOYEE, DEPARTMENT, and PROJECT, respectively.
Knowledge that Dname of DEPARTMENT and Pname of PROJECT are unique
keys is kept for possible use later in the design.
The relations that are created from the mapping of entity types are sometimes
called entity relations because each tuple represents an entity instance.
Step 2: Mapping of Weak Entity Types.
For each weak entity type W in the ER schema with owner entity type E, create a
relation R and include all simple attributes (or simple components of composite
attributes) of W as attributes of R.
In addition, include as foreign key attributes of R, the primary key attribute(s) of the
relation(s) that correspond to the owner entity type(s); this takes care of mapping
the identifying relationship type of W.
The primary key of R is the combination of the primary key(s) of the owner(s) and
the partial key of the weak entity type W, if any. If there is a weak entity type E2
whose owner is also a weak entity type E1, then E1 should be mapped before E2 to
determine its primary key first.
In our example, we create the relation DEPENDENT in this step to correspond to the
weak entity type DEPENDENT. We include the primary key Ssn of the EMPLOYEE
relation—which corresponds to the owner entity type— as a foreign key attribute of
DEPENDENT; we rename it Essn, although this is not necessary. The primary key of
the DEPENDENT relation is the combination {Essn, Dependent_name}, because
Dependent_name (also renamed from Name in Figure) is the partial key of
DEPENDENT.
Step 2: Mapping of Weak Entity Types.
It is common to choose the propagate (CASCADE) option for the referential
triggered action on the foreign key in the relation corresponding to the weak
entity type, since a weak entity has an existence dependency on its owner
entity. This can be used for both ON UPDATE and ON DELETE.
Rules for converting ER diagram into table
Rules for converting ER diagram into table
Rules for converting ER diagram into table
Rules for converting ER diagram into table
Rules for converting ER diagram into table
Rules for converting ER diagram into table
Example
Entity type becomes a table.
In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms
individual tables.
•All single-valued attribute becomes a column for the table.
In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column of
STUDENT table. Similarly, COURSE_NAME and COURSE_ID form the column of
COURSE table and so on.
•A key attribute of the entity type represented by the primary key.
In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and
LECTURE_ID are the key attribute of the entity.
•The multivalued attribute is represented by a separate table.
In the student table, a hobby is a multivalued attribute. So it is not possible to represent
multiple values in a single column of STUDENT table. Hence we create a table
STUD_HOBBY with column name STUDENT_ID and HOBBY. Using both the
column, we create a composite key.
•Composite attribute represented by components.
In the given ER diagram, student address is a composite attribute. It contains
CITY, PIN, DOOR#, STREET, and STATE. In the STUDENT table, these
attributes can merge as an individual column.
•Derived attributes are not considered in the table.
In the STUDENT table, Age is the derived attribute. It can be calculated at
any point of time by calculating the difference between current date and Date
of Birth.
1) Construct an E-R diagram for a hospital with a set of patients and a set
of medical doctors. Associate with each patient a log of the various tests
and examinations conducted. Construct appropriate tables for each of the
E-R diagram.
2) A university registrar.s ofce maintains data about the following entities: (a) courses, including
number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number,
year, semester, section number, instructor(s), timings, and classroom; (c) students, including
student-id, name, and program; and (d) instructors, including identication number, name,
department, and title. Further, the enrollment of students in courses and grades awarded to
students in each course they are enrolled for must be appropriately modeled. Construct an E-R
diagram for the registrar.s [Link] all assumptions that you make about the mapping
constraints.
3) Construct an E-R diagram for a car-insurance company whose
customers own one or more cars each. Each car has associated with it zero
to any number of recorded accidents.