ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

35
ER-to-Relational Mapping 1

Transcript of ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

Page 1: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

ER-to-Relational Mapping

1

Page 2: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

ER-to-Relational Mapping

Relational Database Design : ER- Mapping

Page 3: ER-to-Relational Mapping 1. 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

Page 4: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

ER-to-Relational Mapping

The ER conceptual schema diagram for the COMPANY database.

Page 5: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

ER-to-Relational Mapping

Result of mapping the COMPANY ER schema into a relational schema.

Page 6: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 7: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 8: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 9: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 10: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 11: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 12: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 13: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

ER-to-Relational Mapping

Ternary relationship types. (a) The SUPPLY relationship.

Page 14: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

ER-to-Relational Mapping

Mapping the n-ary relationship type SUPPLY from Figure 3.17(a).

Page 15: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

ER-to-Relational Mapping

15

EXAMPLES

Page 16: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 17: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 18: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 19: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 20: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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?

Page 21: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 22: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 23: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 24: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 25: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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.

Page 26: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 27: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 28: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 29: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 30: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 31: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 32: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 33: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 34: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

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

Page 35: ER-to-Relational Mapping 1. Relational Database Design : ER- Mapping.

ER-to-Relational MappingMapping Exercise

Slide 7- 35

An ER schema for a SHIP_TRACKING database.