ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.
-
Upload
jason-sullivan -
Category
Documents
-
view
222 -
download
4
Transcript of ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.
ER-to-Relational Mapping
1
ER-to-Relational Mapping
Relational Database Design : ER- Mapping
ER-to-Relational Mapping
Outline
• ER-to-Relational Mapping Algorithm – Step 1: Mapping of Regular Entity Types– Step 2: Mapping of Weak Entity Types– Step 3: Mapping of Binary 1:1 Relation Types– Step 4: Mapping of Binary 1:N Relationship Types.– Step 5: Mapping of Binary M:N Relationship Types.– Step 6: Mapping of Multivalued attributes.– Step 7: Mapping of N-ary Relationship Types.
– Specialization/Generalization
ER-to-Relational Mapping
The ER conceptual schema diagram for the COMPANY database.
ER-to-Relational Mapping
Result of mapping the COMPANY ER schema into a relational schema.
ER-to-Relational Mapping
ER-to-Relational Mapping Algorithm• 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.
– Choose one of the key attributes of E as the primary key for R.– If the chosen key of E is composite, the set of simple attributes that
form it will together form the primary key of R.
ER-to-Relational Mapping
ER-to-Relational Mapping Algorithm (contd.)
• 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 & include all simple attributes (or simple components of composite attributes) of W as attributes of R.
– Also, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s).
– 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.
ER-to-Relational Mapping
ER-to-Relational Mapping Algorithm (contd.)
• Step 3: Mapping of Binary 1:1 Relationship Types– For each 1:1 relationship type R, identify the relations S and T that
participating in R.
• Foreign key approach– Choose one relation S. Include the primary key of T as a foreign key
in S.• It is better to choose an entity type with total participation in R in the role
of S.
ER-to-Relational Mapping
ER-to-Relational Mapping Algorithm (contd.)
• Step 4: Mapping of Binary 1:N Relationship Types
– For each binary 1:N relationship type R, identify the relation S that represents the participating entity type at the N-side of the relationship. Let T denotes the other participating entity.
– Include the primary key of T as a foreign key in S. – Include any simple attributes of the relationship as attributes of S.
ER-to-Relational Mapping
ER-to-Relational Mapping Algorithm (contd.)
• Step 5: Mapping of Binary M:N Relationship Types
– For each binary M:N relationship type R, create a new relation S.
– Include the primary keys of both participating entities as foreign keys.
– Their combination will form the primary key of S.
– Also include any simple attributes of R.
ER-to-Relational Mapping
ER-to-Relational Mapping Algorithm (contd.)
• Step 6: Mapping of Multivalued attributes.
– For each multivalued attribute A, create a new relation R. – This relation R will include an attribute corresponding to A, plus the
primary key attribute K (as a foreign key in R) of the relation that represents the entity type of relationship type that has A as an attribute.
– The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components.
ER-to-Relational Mapping
ER-to-Relational Mapping Algorithm (contd.)
• Step 7: Mapping of N-ary Relationship Types.
– For each n-ary relationship type R, where n>2, create a new relation S to represent R.
– Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types.
– Also include any simple attributes of the n-ary relationship type (or simple components of composite attributes) as attributes of S.
ER-to-Relational Mapping
Ternary relationship types. (a) The SUPPLY relationship.
ER-to-Relational Mapping
Mapping the n-ary relationship type SUPPLY from Figure 3.17(a).
ER-to-Relational Mapping
15
EXAMPLES
ER-to-Relational Mapping
16
1. Create a relation for each strong entity type
• include all simple attributes
• choose a primary key
Suppose we have:
course 1 Noffered in
Section noterm
meeting
course noname
credit hoursdescription
section
ER-to-Relational Mapping
17
We create a relation for Course - four attributes, course_no is the PK.
course 1 Noffered in
Section noterm
meeting
course noname
credit hoursdescription
section
CourseCourse_no name credit_hours description
ER-to-Relational Mapping
18
2. Create a relation for each weak entity type• include primary key of owner (an FK)• Owner’s PK + partial key become the PK
Suppose we have:
course 1 Noffered in
Section noterm
meeting
course noname
credit hoursdescription
section
ER-to-Relational Mapping
19
We create a relation for Section
course 1 Noffered in
Section noterm
meeting
course noname
credit hoursdescription
section
Section
Course_no Section_no Term
•PK is {course_no, section_no}.
•course_no is an FK.
•meeting is not a simple attribute, so it’s not included.
ER-to-Relational Mapping
20
3. For each binary 1:1 relationship choose an entity and include the other’s PK in it as an FK.
department instructorchair1 1
dept_no dname instr_no iname
There are two choices here
• choose department, or
• choose instructor
Which is the better choice?
ER-to-Relational Mapping
21
Department is the better choice since it must participate in the relationship.
department instructorchair1 1
dept_no dname instr_no iname
Department
chairdept_no dname
If we choose department then instr_no is included as, of course, an FK. Note that instr_no must have a value.
ER-to-Relational Mapping
22
4. For each binary 1:n relationship, choose the n-side entity and include an FK w.r.t the other entity.
department instructoremploys1 N
dept_no dname instr_no iname
We must choose instructor
We end up with:
instructorinstr_no iname dept_no
•PK is instr_no
•dept_no is an FK
Note that Step 1 would lead to the instructor relation - we have now augmented instructor with the dept_no attribute.
ER-to-Relational Mapping
Jan. 2011 ACS-3902 Yangjun Chen 23
5. For each binary M:N relationship, create a relation for the relationship
• include PKs of both participating entities and any attributes of the relationship
• PK is the catenation of the participating entities’ PKs
student courseenrollm ngrade
Enroll
student_no Course_no grade
•PK is {student_no, course_no}
•student_no is a FK
•course_no is a FK
•grade is an attribute of Enroll
course_nostudent_no
ER-to-Relational Mapping
24
6. For each multi-valued attribute create a new relation• include the PK attributes of the entity type• PK is the PK of the entity type and the multi-valued
attribute
course 1 Noffered in
Section noterm
meeting
course noname
credit hoursdescription
section
Meeting is a multi-valued attribute
ER-to-Relational Mapping
25
Create a relation for meeting
Section was created because of Step 2 - its PK is {course_no, section_no}
meeting
Meeting
course_no section_no meeting•PK is {course_no, section_no, meeting}.
•Meeting is an all-key relation.
ER-to-Relational Mapping
26
7. For each n-ary relationship, create a relation for the relationship
• include PKs of all participating entities and any attributes of the relationship
• PK may be the catenation of the participating entity PKs (depends on cardinalities)
semester courseoffersm nroom course_nosemester_no
instructor
instr_no
p
ER-to-Relational Mapping
27
We need one relation, offers, with PK of {semester_no, course_no, instr_no}
semester courseoffersm nroom no course_nosemester_no
instructor
instr_no
p
Offers
course_no instr_no semester_no Room_no
ER-to-Relational Mapping
28
student
graduate undergraduate
Return to Entity-Relationship Modeling
Consider Section 4.2 on Specialization and Generalization
• Specialization is the process of defining a set of sub-entities of some entity type. Generalization is the opposite approach/process of determining a supertype based on certain entities having common characteristics.
• e.g. employees may be paid by the hour or a salary (part vs full-time)• e.g. students may be part-time or full-time; graduate or undergraduate
• these are similar to 1:1 relationships, but they always involve entities of one (super)type
• these are ‘is-a’ relationships
d
ER-to-Relational Mapping
29
student
graduate undergraduate
d
The arc implies graduate and undergraduate are subtypes of student
The bubble and the d imply disjoint subtypes(o - overlap subtypes)
A student must be a graduate or undergraduate
• Participation of supertype may be mandatory or optional
• Subtypes may be disjoint or overlapping
• a predicate (on an attribute) determines the subtype: e.g. attribute Student_class
Student_class = ‘graduate’; Student_class = ‘undergraduate’
Student_class
Subtype is determined by the student_class attribute
ER-to-Relational Mapping
30
Mapping to a relational database -
4 choices:
1. Create separate relations for the supertype and each of the subtypes.
2. Create relations for the subtypes only - each contains attributes from the supertype.
3. (disjoint subtypes) Create only one relation - includes all of the attributes for the supertype and all for the subtypes, and one discriminator attribute.
4. (overlapping subtypes) Create only one relation - includes all of the attributes for the supertype and all for the subtypes, and one logical discriminator attribute per subtype.
PK is always the same - determined from the supertype
ER-to-Relational Mapping
31
SECRETARY ENGINEER
d
Example for super- & sub-types: choice 1
TECHNICIAN
name
lnameminitfname
Ssn bDates Address JobType
TypingSpeed
TGradeEngType
fname, minit, lname, ssn, bdate, address, JobType
EMPLOYEE
Essn, TypingSpeed
SECRETARY
Essn, TGrade
TECHNICIAN
Essn, EngType
ENGINEER
EMPLOYEE
ER-to-Relational Mapping
32
CAR TRUCK
d
Example for super- & sub-types: choice 2
VehicleId Price LicensePlate
TNoOfPassengersNoOfAxles
VehicleId, LicensePlate, Price, MaxSpeed, NoOfPassenger
CAR
VehicleId, LicensePlate, Price, NoOfAxles, Tonnage
TRUCK
MaxSpeed Tonnage
Vehicle
ER-to-Relational Mapping
33
SECRETARY ENGINEER
d
Example for super- & sub-types: choice 3
TECHNICIAN
name
lnameminitfname
Ssn bDates Address JobType
TypingSpeed
TGradeEngType
fname, minit, lname, ssn, bdate, address, JobType, TypingSpeed, Tgrade, EngType
EMPLOYEE
EMPLOYEE
ER-to-Relational Mapping
34
Part
Manufacture_Part Purchased_Part
o
Example for super- & sub-types: choice 4
PartNo Description
manufactureDate
Supplier
PartNo, Desription, MFlag, Drawing, ManufactureDate, BatchNo, Pflag, Supplier, ListPrice
Part
BatchNo
DrawingNoListPrice
ER-to-Relational MappingMapping Exercise
Slide 7- 35
An ER schema for a SHIP_TRACKING database.