Databases
04. A. ER to Relations (ch 4.5 and 4.6)
2018. Fall Instructor: Joonho Kwon [email protected] Data Science Lab @ PNU
These slides are mainly based on the chapter 9 of Fundamentals of Database Systems and Ch 4.6 of the text book.
datalab
data science laboratory
From ER diagrams to Relational Designs (4.5)
Converting Subclass Structures to Relations (4.6)
Outline
2
Entity sets
Attributes
Keys
Weak entity sets
Relationship sets
Attributes on relationships
Multiplicity
Roles
Binary versus 𝑛-ary relationships
Modeling 𝑛-ary relationships with weak entity sets and binary relationships
ISA relationships
E/R model: review
3
ER/EER to RDB schema
ER-diagram Relational database schema4
Basic cases
Entity set E relation
Attribute of an entity set attributes of relation
Relationship R relation whose attributes are
attributes of R +
Key attributes of the connected entity sets
Special cases
weak entity sets
Combining relations (especially M-to-1 relationships)
is-a relationships and subclasses
From ER/EER to Relations
5
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.
Mapping EER Model Constructs to Relations
Entity-in-all-superclasses (E/R style)
Entity-in-most-specific-class (OO style)
All-entities-in-one-table (Null style)
Guidelines
6
STEP1: Strong Entity Types (1/2)
7
ER-diagram Relational database schema Regular (Strong ) entity type Relation
Simple attribute Attribute
One of key attributes Primary key
Fname
EMPLOYEE Minit Lname Name
Bdate Ssn
Address
Salary Sex
Fname Minit Lames Ssn Bdate Address Sex Salary EMPLOYEE
STEP1: Strong Entity Types (2/2)
8
DEPARTMENT Locations
Number Name
PROJECT
Name
Location Number
Dname Dnumber DEPARTMENT
Pname Pnumber Plocation PROJECT
STEP2: Weak Entity Types
9
ER-diagram Relational database schema
Weak entity type Relation
Simple attribute Attribute
One of key attributes of owner entity type + partial key attributes
Primary key
Dependent_name DEPENDENT
Q) Owner entity type? Q) Identifying relationship type?
Sex Birth_date Relationship
EMPLOYEE
DEPENDENT
HAS_
DEPENDENTS
Name Sex Birth_date Relationship Ssn
Essn
Identify 1:1 relationship type, and find an entity type with total participation (Manages – Department)
Add a foreign key
a primary key in related entity type
Add an attribute of relationship type
STEP3: 1:1 Relationship Types
10
Dname Dnumber Mgr_ssn Mgr_start_date DEPARTMENT
EMPLOYEE
SSN
DEPARTMENT
MANAGES
Locations
Number Start_date
Name
1 1
Identify 1:N relationship type
Add a foreign key in N-side
a primary key in related entity type
STEP4: 1:N Relationship Types (1/2)
11
EMPLOYEE
Fname Minit Lames Ssn Bdate Address Sex Salary
N 1
EMPLOYEE
SSN
DEPARTMENT
WORKS_FOR
Locations
Number Name
Dno
STEP4: 1:N Relationship Types (2/2)
12
EMPLOYEE
PROJECT
Pname Pnumber Plocation Dnum
DEPARTMENT
PROJECT
Name Location
Locations
Number Name
Number CONTROLS
N 1
EMPLOYEE
Ssn
SUPERVISION
1 N
Supervisor Supervisee
Fname Minit Lames Ssn Bdate Address Sex Salary Dno Super_ssn
Identify M:N relationship type
Create a new relation
Add foreign keys in both side
a primary key in related entity type
The combination of two foreign keys become a primary key
Add attributes of the relationship type
STEP5: M:N Relationship Types (1/2)
13
STEP5: M:N Relationship Types (2/2)
14
PROJECT
WORKS_ON
Name
Location Hours
Number
M N
EMPLOYEE Ssn
Essn Pno Hours
WORKS_ON
Create a new relation
Add two attributes
a multi-valued attribute
a key attribute of entity type
Combination of the attributes becomes a primary key
STEP6: Multivalued Attributes
15
Dlocation Dnumber DEPT_LOCATION
DEPARTMENT Locations
Number Name
{Dlocation, Dnumber}: primary key
Relationship type connected to more than 2 entity types
Create a new relation
Add foreign keys in all side
a primary key in related entity type
Add attributes of the relationship type
STEP7: N-ary Relationship Types (1/2)
16
STEP7: N-ary Relationship Types (2/2)
17
From Company ERD to RDB Schema
18
Correspondence
between ER and Relational Models
19
ER Model Relational Model
Entity type Entity relation
Weak Entity type Entity relation and foreign key
1:1 or 1:N relationship type Foreign key (or relationship relation)
M:N relationship type Relationship relation and two foreign keys n-ary relationship type Relationship relation and n foreign keys Simple attribute Attribute
Composite attribute Set of simple component attributes Multivalued attribute Relation and foreign key
Value set Domain
Key attribute Primary key
Translate the following ER Diagram into a relational database schema.
Exercise
20
Three approaches
Entity-in-all-superclasses approach (“E/R style”)
Entity-in-most-specific-class approach (“OO style”)
All-entities-in-one-table approach (“NULL style”)
Translating subclasses & ISA
21
Users Name
uid
Groups
Name
WORKS_ gid
ON
fromDate
PaidUsers Avatar
ISA
Approach
An entity is represented in the table for each subclass to which it belongs
A table includes only the attributes directly attached to the corresponding entity set, plus the inherited key
Entity-in-all-superclasses approach (“E/R style”)
22
Users Name
uid
Groups
Name
IsMember gid
Of
fromDate
PaidUsers Avatar
ISA
Group (gid, name) User (uid, name)
Member (uid, gid, from_date)
〈142, Bart〉
〈456, Ralph〉
〈456, 〉
∈
∈ PaidUser (uid, avatar)
Approach
An entity is only represented in one table (the most specific entity set to which the entity belongs)
A table includes the attributes attached to the
corresponding entity set, plus all inherited attributes
Translating subclasses & ISA: OO style
23
Users Name
uid
Groups
Name
IsMember gid
Of
fromDate
PaidUsers Avatar
ISA
Group (gid, name) User (uid, name)
Member (uid, gid, from_date)
〈142, Bart〉
〈456, Ralph, 〉
∈
∈ PaidUser (uid, name, avatar)
Approach
One relation for the root entity set, with all attributes found in the network of subclasses (plus a “type” attribute when needed)
Use a special NULL value in columns that are not relevant for a particular entity
All-entities-in-one-table approach (“NULL style”)
24
Users Name
uid
Groups
Name
IsMember gid
Of
fromDate
PaidUsers Avatar
ISA
Group (gid, name)
User (uid, name, avatar)
Member (uid, gid, from_date)
〈142, Bart, NULL〉
〈456, Ralph, 〉 ∈
Entity-in-all-superclasses (ER)
User (uid, name), PaidUser (uid, avatar)
Pro: All users are found in one table
Con: Attributes of paid users are scattered in different tables
Entity-in-most-specific-class (OO)
User (uid, name), PaidUser (uid, name, avatar)
Pro: All attributes of paid users are found in one table
Con: Users are scattered in different tables
Comparison of three approaches (1/2)
25
All-entities-in-one-table (NULL)
User (uid, [type, ]name, avatar)
Pro: Everything is in one table
Con: Lots of NULL’s; complicated if class hierarchy is complex
Comparison of three approaches (2/2)
26
Example
27
Trains engineer
number
ISA
LocalTrains ExpressTrain
ISA
ExpressStations LocalStations
Stations
address name
ExpressTrainStops LocalTrainStops
time time
Train (number, engineer) LocalTrain (number)
ExpressTrain (number)
Station (name, address) LocalStation (name) ExpressStation (name)
LocalTrainStop (local_train_number, time)
LocalTrainStopsAtStation (local_train_number, time, station_name) ExpressTrainStop (express_train_number, time)
ExpressTrainStopsAtStation (express_train_number, time, express_station_name)
merge
merge
Eliminate LocalTrain table
Redundant: can be computed as 𝜋𝑛𝑢𝑚𝑏𝑒𝑟(𝑇𝑟𝑎𝑖𝑛) − 𝐸𝑥𝑝𝑟𝑒𝑠𝑠𝑇𝑟𝑎𝑖𝑛
Slightly harder to check that local_train_number is indeed a local train number
Eliminate LocalStation table
It can be computed as 𝜋𝑛𝑢𝑚𝑏𝑒𝑟(𝑆𝑡𝑎𝑡𝑖𝑜𝑛) − 𝐸𝑥𝑝𝑟𝑒𝑠𝑠𝑆𝑡𝑎𝑡𝑖𝑜𝑛
Simplifications and refinements
28
Train (number, engineer) LocalTrain (number)
ExpressTrain (number)
Station (name, address) LocalStation (name) ExpressStation (name)
LocalTrainStop (local_train_number, station_name, time)
ExpressTrainStop (express_train_number, express_station_name , time)
Encode the type of train/station as a column rather than creating subclasses
What about the following constraints?
Type must be either “local” or “express”
Express trains only stop at express stations
They can be expressed/declared explicitly as database constraints in SQL (as we will see later in course)
Arguably a better design because it is simpler
An alternative design
29
Train (number, engineer, type) Station (name, address, type)
TrainStop (train_number, station_name, time)
KISS
Keep It Simple, Stupid
Avoid redundancy
Redundancy wastes space, complicates modifications, promotes inconsistency
Capture essential constraints, but don’t introduce unnecessary restrictions
Use your common sense
Warning: mechanical translation procedures given in this lecture are no substitute for your own judgment
Design principles
30
Q&A
31
