Module 4 EntityRelationship to Relational Mapping

1
Module 4Entity-Relationship toRelational Mapping

• Teknik Informatika Fakultas Teknik
• Universitas Dr. Soetomo Surabaya

2
DATABASE Modules
Fundamentals
Module 1 Database Systems
Module 2 Relational Model Module 3 Entity
Relationship Model
Data Modeling
Module 4 ER to Relational Mapping Module 5 FDs
and Normalization
Data Design
Module 6 Relational Algebra Module 7 SQL
Data Access
Module 8 Database Systems Architecture
Architecture
3
Module 4 - Motivation
Storage perspective
4
Module 4 - Contents

• Entity-Relationship to Relational Mapping
• Steps for mapping a basic ER diagram to a
  relational schema
• Uses the Company database example to
• illustrate the concepts
• Design choices in the ER Model and their impact
  on the resulting relational schema

5
Mapping Method

• Method for mapping a conceptual schema developed
  using the ER model to a relational database
  schema comprises 7 steps
• CASE tools also exist for this task

6
Steps for Mapping

• 1. Entity Mapping
• 2. Weak Entity Mapping
• 3. Binary 11 Relationship Mapping
• 4. Binary 1 N Relationship Mapping
• 5. Binary MN Relationship Mapping
• 6. Multi-valued Attribute Mapping
• 7. N-ary Relationship Mapping

7
(No Transcript)
8
(No Transcript)
9
Step 1 Entity Mapping

• For each regular (non-weak) entity type E, create
  a relation R that includes all simple attributes
  of E
• Include only simple component attributes of a
• composite attribute
• Choose one key attribute of E as primary key
• for R. If key of E is composite, the set of
• simple attributes together should form the
  key
• Add following attributes in subsequent steps
• Foreign key, Relationship, Multi-valued

10
Step 1 Example

• Entity Types in the Company Database
• EMPLOYEE, DEPARTMENT, PROJECT

EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
Address, Sex, Salary
11
Step 1 Example

• Entity Types in the Company Database
• EMPLOYEE, DEPARTMENT, PROJECT

DEPARTMENT Dnumber, Dname
12
Step 1 Example

• Entity Types in the Company Database
• EMPLOYEE, DEPARTMENT, PROJECT

PROJECT PNo, PName, Plocation
13
Schema (in progress)

• EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
  Address, Sex, Salary
• DEPARTMENT Dnumber, Dname
• PROJECT Pno, PName, Plocation

14
Step 2 Weak Entity Mapping

• For each weak entity type W with owner entity
  type E create a relation R that includes all
  simple attributes of W
• Include as foreign key attributes in R the
• primary key attributes of the relation(s)
  that
• correspond to the owner entity types. (This
• maps 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
  primary
• key of the weak entity type W (if any)

15
Step 2 Example

• Weak Entity Types in the Company Database
  DEPENDENT

DEPENDENT ESSN, DepName, Sex, Birthdate,
Relationship where Primary Key ESSN,
DepName includes
SSN, the primary key of the EMPLOYEE
relation, which is the owner entity type, as
a foreign key attribute of DEPENDENT
(renamed ESSN) DepName, the partial key of
DEPENDENT
16
Schema (in progress)

• EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
  Address, Sex, Salary
• DEPARTMENT Dnumber, Dname
• PROJECT Pno, PName, Plocation
• DEPENDENT ESSN,DepName, Sex, BirthDate,
  Relationship

17
Step 3 Binary 11 Relationship

• For each binary 11 relationship type (RT),
  identify relations S T that correspond to the
  entity types participating in RT
• Choose one relation (say S) and include as
  foreign key
• in S the primary key of T
• It is better to choose as S, the entity type
  with
• total participation in RT
• Include all the simple attributes (or simple
  components
• of composite attributes) of the 11
  relationship type RT
• as attributes of S

18
Step 3 Example

• Binary 11 relationship type in the Company
  Database MANAGES

DEPARTMENT Dnumber, Dname , MGRSSN, MgrStart

• DEPARTMENT serves in the role of S because its
  participation in the
• MANAGES relationship type is total (every
  department has a manager)
• Include the primary key of the EMPLOYEE relation
  as a foreign key in the
• DEPARTMENT relation (renamed MGRSSN)
• Include the simple attribute StartDate of the
  MANAGES relation (renamed
• MGRSTART)

19
Schema (in progress)

• EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
  Address, Sex, Salary
• DEPARTMENT Dnumber, Dname MGRSSN, MgrStart
• PROJECT Pno, PName, Plocation
• DEPENDENT ESSN,DepName, Sex, BirthDate,
  Relationship

20
Step 4 Binary 1N Relationship

• For each (non-weak) binary 1N relationship type
  (RT), identify relation S that represents the
  participating entity type at the N-side of the
  relationship type
• Include as foreign key of S the primary key of
• relation T that represents the other entity
  type
• participating in RT
• Include any simple attributes (or simple
• components of composite attributes) of the
• 1N relationship type as attributes of S

21
Step 4 Example

• Binary IN relationship types in the Company
  Database WORKS_FOR, CONTROLS and SUPERVISION

EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
Address, Sex, Salary , Dno
Where primary key of the DEPARTMENT relation is
included as a foreign key in the EMPLOYEE
relation (renamed Dno)
22
Step 4 Example

• Binary IN relationship types in the Company
  Database WORKS_FOR, CONTROLS and SUPERVISION

PROJECT Pno, PName, Plocation, Dnum Where
primary key of the DEPARTMENT relation is
included as a foreign key in the PROJECT relation
(renamed Dnum)
23
Step 4 Example

• Binary IN relationship types in the Company
  Database WORKS_FOR, CONTROLS and SUPERVISION

EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
Address, Sex, Salary , SuperSsn Where primary
key of the EMPLOYEE relation is included as a
foreign key within the EMPLOYEE relation (called
SuperSsn) Note the recursive relationship!
24
Schema (in progress)

• EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
  Address, Sex, Salary , SuperSsn
• DEPARTMENT Dnumber, Dname MGRSSN, MgrStart
• PROJECT Pno, PName, Plocation, Dnum
• DEPENDENT ESSN,DepName, Sex, BirthDate,
  Relationship

25
Step 5 Binary MN Relationship

• For each binary MN relationship type (RT),
  create a new relation S to represent RT
• Include as foreign key of S the primary keys
  of the
• relations that represent the participating
  entity types in
• RT
• The combination of foreign keys will form the
  primary key
• of S (Note cannot represent the MN using a
  single
• foreign key in one relation because of the
  MN
• cardinality ratio)
• Include any simple attributes (or simple
  components of
• composite attributes) of the MN relationship
  type as
• attributes of S.

26
Step 5 Example

• Binary MN relationship type in the Company
  Database WORKS_ON

WORKS_ON ESSN, PNo, Hours Where WORKS_ON
includes the primary keys of the PROJECT and
EMPLOYEE relations as foreign keys The primary
key of WORKS_ON is the combination of the foreign
key attributes (renamed to PNO and ESSN
respectively) HOURS in WORKS_ON represents
the attribute of the relationship type
27
Schema (in progress)

• EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
  Address, Sex, Salary , SuperSsn
• DEPARTMENT Dnumber, Dname MGRSSN, MgrStart
• PROJECT Pno, PName, Plocation, Dnum
• DEPENDENT ESSN,DepName, Sex, BirthDate,
  Relationship
• WORKS_ON ESSN, PNo, Hours

28
More on MN Mapping

• Note that 11 and 1N relationships can be mapped
  in the same way as MN
• Advantageous when few relationship instances
  exist (Sparse 11 Relationship) as it reduces the
  number of nulls that appear as foreign key
  values

29
Sparse 11 Relationship
No Nulls as Foreign Keys
Standard Implementation
MN Implementation
30
Step 6 Multivalued Attributes

• For each multi-valued attribute A, create a new
  relation R that includes an attribute
  corresponding to A plus the primary key K (as a
  foreign key of R) of the relation that represents
  the entity type or relationship type that has A
  as an attribute
• The primary key of R is the combination of
• attributes A K
• If the multi-valued attribute is composite,
• include its simple components

31
Step 6 Example

• Multi-valued attributes in the Company Database
  Locations

DEPT_LOCS DNumber, Dlocation Where primary
key of DEPT_LOCS is the combination of DNumber,
DLocation Attribute DLocation will represent
the multivalued attributes Locations of
DEPARTMENT Attribute DNumber (as foreign key)
represents the primary key of the
DEPARTMENT relation
32
Final Schema

• EMPLOYEE Ssn, Fname, Minit, Lname, Bdate,
  Address, Sex, Salary , SuperSsn
• DEPARTMENT Dnumber, Dname MGRSSN, MgrStart
• PROJECT Pno, PName, Plocation, Dnum
• DEPENDENT ESSN,DepName, Sex, BirthDate,
  Relationship
• WORKS_ON ESSN, PNo, Hours
• DEPT_LOCS DNumber, Dlocation

33
Step 7 N-ary Relationship Type

• For each n-ary relationship type (RT) , create
  a new relation S to represent RT.
• Include as foreign key attributes of S the
• primary keys of the relations that represent
• the participating entity types in RT
• Include any simple attributes of the n-ary
• relationship type
• The combination of foreign keys referencing
• the relations representing the participating
• entity types is used to form primary key of S

34
Special caseN-ary Relationship

• If the participation constraint (min,max) of one
  of the entity types E participating in RT has max
  1, then the primary key of S need not include
  the foreign key attribute that references the
  relation E

35
N-ary Mapping Examples

• Ternary relationship
• Ternary relationship with participation
  constraint of one entity type having max1
• Weak entity with three owners
• Semantically different representation of
  relationship between 3 entities

36
Ternary relationship
SUPPLIER Sname, ... PROJECT ProjName, ...
PART PartNo, ... SUPPLY SName, ProjName ,
PartNo, Quantity
37
Ternary relationship (max1)
Participation constraint with max 1 (Only one
Supplier for each Project/Part)
SUPPLIER Sname, ... PROJECT ProjName, ...
PART PartNo, ... SUPPLY ProjName, PartNo,
Sname, Quantity
38
Weak entity with three owners
Same as ternary relationship

• SUPPLIER Sname, ...
• PROJECT ProjName, ...
• PART PartNo, ...
• SUPPLY SName, ProjName , PartNo, Quantity

39
Relationships between 3 Entities
Semantically different from ternary relationship
SUPPLIER Sname, ... PROJECT ProjName, ...
PART PartNo, ...
SUPPLIES SName, ProjName CANSUPPLY SName,
PartNo USES PartNo, ProjName
40
Step 8 Super Sub-classes

• Option 8A
• We create a relational table for the
  superclass and create a
• relational table for each subclass.
• The primary key of each of the subclass is the
  primary key of the
• superclass.

Vehicle(Vid, Lic) Truck(Vid, Weight,
Axles) Car(Vid, Seats, Engsize)
Works for all constraints Disjoint/Overlapping To
tal/Partial
41
Step 8 (cont)

• Option 8B
• We create a relational table for each
  subclass. The attributes of
• the superclass are merged into each of the
  subclasses.
• The primary key of the subclass table is the
  primary key of the
• superclass.

Truck(Vid, Lic, Weight, Axles) Car(Vid, Lic,
Seats, Engsize)
Disjoint Total only
Overlapping redundancy Partial may lose
superclass entities not in any subclass
42
Step 8 (cont)

• Option 8C
• We create a single relational table for all
  subclasses and the
• superclass.
• The attributes of the table is the union of
  all attributes plus the
• attribute T to indicate the subclass to which
  each tuple
• belongs. T is NULL in tuples that do not
  belong to any subclass
• (for partial constraints)

Vehicle(Vname, Vtype, Mload, Seats, T)
Disjoint only
In the case attribute Vtype is used to
discriminate subclasses, the attribute T can be
omitted.
43
Step 8 (cont)

• Option 8D
• We create a single relational table for all
  subclasses and the
• superclass.
• The attributes of the table is the union of
  all attributes plus m
• extra boolean attributes for each subclass to
  indicate whether or
• not the tuple belongs to this subclass.

Employee( E, Dob, T1, Posn, Years, T2, Hours )
Overlapping only
44
Module 4 - Contents

• Entity-Relationship to Relational Mapping
• Steps for mapping a basic ER diagram to
• a relational schema
• Uses the Company database example to
• illustrate the concepts
• Design choices in the ER Model and their
• impact on the resulting relational schema

45
Expressibility of ER

• Constraints play an important role in determining
• the best database design for an enterprise.
• Several kinds of integrity constraints can be
  expressed
• in the ER model, e.g., key constraints,
  participation
• constraints.
• Some foreign key constraints are also implicit
  in the
• definition of a relationship set.
• Some constraints (notably, functional
  dependencies)
• cannot be expressed in the ER model.
• Some additional constructs have not been
  discussed
• ISA hierarchies, and aggregation.
• There are many variations on ER model

46
Subjectivity of ER Design

• ER model is a means of capturing users data
  requirements. However, different designers may
  interpret the semantics of the users
  requirements differently
• This may result in the same UoD being represented
  by different ER diagrams because of different
  Design Choices
• These design choices in the ER Model
• impact on the resulting relational schema

47
What are Design Choices ?

• Should a concept be modeled as an entity or an
  attribute?
• Should a concept be modeled as an entity or a
  relationship?
• Should a concept be modeled as a weak entity or a
  complex (composite, multivalued) attribute
• Is a relationship binary or ternary?
• ?

48
Entity vs. Attribute

• enr-dept gives the
• enrolling department
• for a Student
• ofr-dept gives the
• offering department
• for a Course
• A designer may choose
• to create an entity
• type Department with
• a single attribute
• dname. Other
• attributes for
• Department (Hod,
• dbudget) may be
• discovered later

49
Entity vs. Relationship

• Works_In2 does not allow an employee to work in a
  project more than once.
• Works_In3 allows an employee to work in the same
  project more than once.
• Can an employee work in the same project, for the
  same period under two different positions?

50
Weak Entity vs. Complex Attrib

• If a weak entity participates in other
  relationship types, besides the identifying
  relationship, then it has to be modeled as a weak
  entity
• If the weak entity has only one attribute, then
  it may be modeled as a multivalued attribute of
  the owner entity

51
Binary vs. Ternary Relationships

• If a project is controlled by, and an employee
  works in only one department, the ternary
  relationship is inappropriate
• EPD ssn, dname, projid
• (90, CSEE, WF99)
• (90, CSEE, Hydro88)
• (87, CSEE, Hydro88)
• (87, CSEE, Spark4)
• (32, Biology,Gen2000)

52
From UoD to Database

• Correctness (How can we be sure?)
• semantic non-ambiguity (unique name and
• elementary value)
• minimum representation (no redundancy and
• no derivables)
• Completeness (Is it good for all applications?)
• Everything in UoD is represented
• Everything expressed by the model is true in
• UoD

53
Example Exercise

• Extract the conceptual model (ER DIAGRAM) from a
  given user specification
• Map the conceptual model to a RELATIONAL SCHEMA
• Refine the relational schema using functional
  dependencies and normalization (Next Module)

54
Specifications

• A bank, given by its code, name and head office
  address, can have several branches. Each branch
  within a given bank has a branch number and
  address
• One branch can have several accounts, each
  identified by an AC number. Every account has a
  type, current balance, and one or more account
  holders
• One branch can have several loans, each given by
  a
• unique loan number, type, amount and one or more
  loan holders
• The name, address, phones and id of all customers
• (account and loan) of the bank are recorded and
• maintained

55
ER Diagram
56
ER Diagram
57
ER Diagram
58
ER Diagram
59
Relational Schema

• BANK Code, Name, HOAddr
• BRANCH BankCode, BranchNo, Addr
• ACCOUNT ACNo, Type, Balance, BankCode, BranchNo
• LOAN LoanNo, Type, Amount, BankCode, BranchNo
• CUSTOMER SSN, Name, Address
• CUSTPHONE SSN, Phone
• ACCOUNT-HOLDER ACNo, SSN
• LOAN-HOLDER LoanNo, SSN

60
Module 4 - Review

• Seven mapping steps address basic constructs that
• appear on ER diagrams
• The result is a relational database schema
  that exhibits
• good design characteristics
• There are other ER constructs that we have not
  
• addressed
• ER design is subjective. There are often several
  ways to model a given scenario! Analyzing
  alternatives can be tricky, especially for a
  large enterprise.
• ER to Relational Mapping is a starting point.
  Resulting
• relational schema has to be analyzed and refined
  further
• to achieve optimal design, using functional
  dependency
• information and techniques such as normalization
  (Next
• Module)

61
Recommended Readings

• Elmasri Navathe
• Chapter 9

62
Next ...

• Module 5
• Functional
• Dependencies and
• Normalization