SOMAIYA
VIDYAVIHAR
K J Somaiya Institute of Engineering and Information Technology
An Autonomous Institute Permanently Affiliated to the University of Mumbai
DEPARTMENT OF INFORMATION TECHNOLOGY
Experiment No.-02
Aim: Mapping ER/EER to Relational schema model.
Objective: To convert ER/EER model into Relational model.
Outcome: Able to create relational database form EER diagram.
Theory:
Entity sets and relationship sets can be expressed uniformly as relation schemas that represent
the contents of the database.
A database which conforms to an E-R diagram can be represented by a collection of schemas.
For each entity set and relationship set there is a unique schema that is assigned the name of the
corresponding entity set or relationship set.
Each schema has a number of columns (generally corresponding to attributes), which have
unique names.
A strong entity set reduces to a schema with the same attributes
student(ID, name, tot_cred)
A weak entity set becomes a table that includes a column for the primary key of the identifying
strong entity set
section ( course_id, sec_id, sem, year )
A many-to-many relationship set is represented as a schema with attributes for the primary keys
of the two participating entity sets, and any descriptive attributes of the relationship set.
Example: schema for relationship set advisor
Composite attributes are flattened out by creating a separate attribute for each component
attribute
Example: given entity set instructor with composite attribute name with component attributes
first_name and last_name the schema corresponding to the entity set has two attributes
name_first_name and name_last_name
Prefix omitted if there is no ambiguity (name_first_name could be first_name)
Ignoring multivalued attributes, extended instructor schema is
instructor(ID, first_name, middle_initial, last_name, street_number, street_name, apt_number,
city, state, zip_code, date_of_birth)
A multivalued attribute M of an entity E is represented by a separate schema EM
Schema EM has attributes corresponding to the primary key of E and an attribute corresponding
to multivalued attribute M
Example: Multivalued attribute phone_number of instructor is represented by a schema:
inst_phone= ( ID, phone_number)
Each value of the multivalued attribute maps to a separate tuple of the relation on schema EM
For example, an instructor entity with primary key 22222 and phone numbers 456-7890
and 123-4567 maps to two tuples: (22222, 456-7890) and (22222, 123-4567)
Many-to-one and one-to-many relationship sets that are total on the many-side can be
represented by adding an extra attribute to the “many” side, containing the primary key of the
“one” side
SOMAIYA
VIDYAVIHAR
K J Somaiya Institute of Engineering and Information Technology
An Autonomous Institute Permanently Affiliated to the University of Mumbai
DEPARTMENT OF INFORMATION TECHNOLOGY
Example: Instead of creating a schema for relationship set inst_dept, add an attribute dept_name
to the schema arising from entity set instructor
For one-to-one relationship sets, either side can be chosen to act as the “many” side
That is, an extra attribute can be added to either of the tables corresponding to the two
entity sets
If participation is partial on the “many” side, replacing a schema by an extra attribute in the
schema corresponding to the “many” side could result in null values
The schema corresponding to a relationship set linking a weak entity set to its identifying strong
entity set is redundant.
Example: The section schema already contains the attributes that would appear in the
sec_course schema
Conclusion: Mapping EER diagram to relational model gives actual database.
Output:
Post-lab Questionnaire:
1. How to map strong and weak entity sets to relations?
2. How to map multivalued attribute to a relation?
3. How to store composite attribute in relation?
4. How to map relationship to a relation?
