Transcript 6 ER2RDM_Mapping

ER to RDM Mapping
Database Principles
Mapping from ER to Relational Data Model
the next phase
Real World
Relational Data Model
Chen’s Proposal
E1
E2
database
Straight-forward
Mapping
Straight-forward
Mapping
E3
Exercise:
Give me some suggestions on what this mapping would look like.
Database Principles
Relational Data Model
• Models all information stored in a database as a collection
of related mathematical structures called relations.
remember this word
• Suppose
– Students = {s1, s2, s3, …, sn} and
– Courses = {c1, c2, c3, …, cm}
• The Cartesian Product of these two sets is:
– Students x Courses =
{ (si, cj) | si ε Students, cj ε Courses }
Database Principles
.
.
.
.
.(s ,c ) .
.
.
.
.
.
.
.
.(s ,c ) .
.
.
.
.
.
.
.
.
Student x Course Grid:
cm
.
.(s ,c )
n
...
.
c2
.
c1
.
Courses
c3
2
1
s1
3
1
.
s2
s3
Students
Database Principles
...
.
sn
m
Relational Data Model
• A relation is a subset of a Cartesian Product.
• Enrollment = {(s1, c2), (s2, c3), (s2, c2) }
• Enrollment is a subset of Students x Courses
Exercise: Suppose Student is a set of patients and Professor is a set of doctors.
Give several examples of relations that are subsets of Student x Professor.
Exercise: Go through the exercise of asking a student you do not know for
examples of useful relations and identity their corresponding Cartesian Products.
Database Principles
Enrollment
.
.
.
.
.
.
c
.
. (s , c ) .
.
.
c
.(s , c ) . (s , c ) .
.
.
.
.
.
.
.
.
.
.
.
NOTE: Points inside the
ellipse correspond to realworld information. Points
outside the ellipse do not
correspond to any realworld event and we call
them noise.
.
Enrollment
cm
.
...
.
Courses
3
1
2
2
3
2
2
.
c1
noise
points
2
s1
s2
Students
Database Principles
s3
...
.
sn
Exercise:
• A tournament of local soccer teams is run as a double
round-robin tournament. What does this mean?
• Describe the difference between such a tournament
and one we could describe as a cartesian product
tournament.
• Work in pairs. If you know what double round-robin
means find someone who doesn’t and explain it to
them. Then answer the question.
• Draw a simple ER for this
home (1,n)
plays
example
Team
Name
away (1,n)
Database Principles
home_score
away_score
Relational Data Model
• A table containing a list of all suppliers - an ID, name and
address for each – is called Supplier.
Supplier
ID
•
SName
SAddress
This table is a subset of the Cartesian Product
SupplierIDs x SupplierNames x SupplierAddresses
so mathematically it too is a relation.
• Of course, the only rows in the table are the useful tuples
of the Cartesian Product, those that really correspond to
some supplier.
Database Principles
Exercise:
• Consider the previous round-robin tournament.
Plays
home (1,n)
HomeName
plays
Team
Name
away (1,n)
home_score
away_score
noise
A
B
A
C
B
C
A
AwayName
B
A
C
A
C
B
C
HomeScore
2
3
0
2
1
1
2
AwayScore
1
3
1
0
1
1
1
• What happens if we have an extra line in the Plays table?
• This line is noise!! It makes the entire table useless. Why?
Rule to Follow: Database tables should contain no noise.
Database Principles
Table Parts
• A table has two parts – intension and extension
• Intension: what the table is “intended” to model. In
essence, the table name and the name of each of its
columns; otherwise known as the table schema.
• Extension: A list of all possible rows of the table; both
presently existing and what might exist in the future or
has existed in the past.
Table Schema
Supplier
ID
SName
SAddress
extension
Database Principles
Notational Correspondence:
ER
•
•
•
•
•
Entity
Relationship
Attribute
Key Attributes
Instance
•
•
•
•
•
RDM
Table/Relation Schema
Table/Relation Schema
Column/Attribute
Primary Key
Row/Tuple
Exercise: The title of this lecture is “ER-to-RDM Mapping“. The above
notational correspondence leads one to think that this mapping is quite
straight forward.
What is the one thing that is not straight forward about this implied mapping?
Database Principles
ER-to-RDM Mapping, Rule 1:
• Each entity is mapped to a table schema with the same
columns as the entity has attributes.
CARDHOLDER
borrowerid
b_name
b_addr
b_status
maps to
loan_limit
CARDHOLDER
borrowerid b_name b_addr
pk
b_status loan_limit
Database Principles
Rule 1 (cont)
• The actual mapping is to syntax in SQL which can be
used to create the table described by our schematic.
Create table CARDHOLDER (
borrowerid int not null primary key,
b_name
varchar(10),
b_addr
varchar(10),
b_status
char(6),
loan_limit int
)
its an integer
can never be null
is the primary key of the table
CARDHOLDER
borrowerid b_name b_addr
pk
b_status loan_limit
Database Principles
ER-to-RDM Mapping, Rule 2a:
• If a relationship has no (1,1) participation number pair
then it too is mapped to a table schema. The table
schema consists of:
– The key attributes of all entities participating in the
relationship
– All attributes of the relationship.
• The primary key of the new table consists of all the key
attributes of the entities participating in the relationship
and any relationship attribute identifies as part of the key.
Database Principles
ER-to-RDM Mapping, Rule 2a:
Reserves
Book
Borrower
Borrowerid
B_name
B_addr
B_status
Loan_limit
(0,n)
(0,n)
r_date
maps to
reserves
borrowerid
pk
ISBN
r_date
Database Principles
ISBN
Author
Title
Pub_name
Pub_date
C_price
ER-to-RDM Mapping, Rule 2b:
• If a relationship has a (1,1) participation number pair
then it is not mapped to a table schema.
• Instead, the table schema corresponding to the entity
with the (1,1) pair takes on additional columns:
– The keys of all other entities participating in the
relationship are migrated to the entity table schema
– The attributes of the relationship itself are migrated to
the entity able schema.
• The primary key of the entity table schema does not
change.
Database Principles
ER-to-RDM Mapping, Rule 2b:
Book
Publisher
ISBN
Author
Title
Pub_date
C_price
Pub_name
(1,n)
(1,1)
pub_date
maps to
BOOK
ISBN Author
Title
Pub_date
C_price
Pub_name
pk
stays the same
Database Principles
Pub_date
Review of Mapping Complexity:
• Relationships model real-world associations.
• In a database, these associations can be modeled in one
of two ways:
– By a table of their own; the table key is a combination
of entity keys
– Within one of the participating entity tables.
Database Principles
Weak Entity ER-to-RDM Mapping, Rule 2b:
• Relationships that attach
to a weak entity always
follow Rule 2b. This is
because the weak entity
always has a (1,1) pair.
However, the key to the
table schema
corresponding to the
weak entity consists of
the attributes in the weak
entity key.
enrolls
Student
StudentNum
Course
(1,n)
(1,n)
(1,1)
Enrollment
Grade
Enrollment
StudentNum CourseNum
pk
Database Principles
CourseNum
Grade
IS_A ER-to-RDM Mapping, Rule 2b:
• IS_A relationships always follow Rule 2b because the
sub-entity always has a (1,1) pair.
Person
ID
Name
DOB
is_a
(0,1)
(0,1)
is_a
(1,1)
(1,1)
Professor
Student
OfficeNumber
GPA
maps to
Professor
ID
OfficeNumber
Student
ID
Database Principles
GPA
Considering (0,1) as (1,1)
• What if a relationship with a (0,1) pair were mapped to
a RDM under the assumption that the (0,1) pair is
really (1,1)?
becomes
(1,1)
disappears
Database Principles
Are These Models Equivalent?
• Suppose two copies exist – one on loan and one not.
Copy
Copy
accession_no
ISBN
p_price
qt-23.4-c1
qs-44.7_c1
1-23
4-76
20.00
12.00
accession_no
qt-23.4-c1
qs-44.7_c1
borrowerid
1234
null
ISBN
p_price
l_date
1-23
4-76
20.00
12.00
nov 3
null
Borrows
borrowerid
1234
accession_no
qt-23.4-c1
since this book is not
on loan there is no
borrowerid or l_date
l_date
nov 3
There can only be one row
in Borrows that contains the
value “qt-23.4_c1”. Why?
Database Principles
So there can only be one
possible value that can go
into the borrowerid column
of the row in Copy that
contains “qt-2.34_c1”
Considering (1,1) as (1,n):
• Suppose you are a database designer and are told that
every project is managed by precisely one employee.
managed by
Employee
(0,n)
(1,1)
EmployeeID
E_name
Project
ProjectID
Title
start_date
• But you ask, “Might it be possible in the future to have
more than one manager for a project?”, and the answer
is “Yes”.
managed by
Employee
(1,n) Project
(0,n)
EmployeeID
E_name
ProjectID
Title
start_date
Database Principles
Considering (1,1) as (1,n):
• x
managed by
Employee
(0,n)
(1,1)
Project
EmployeeID
E_name
ProjectID
Title
start_date
Employee
EmployeeID
E_Name
Project
ProjectID
5
mary
1
Title
A
EmployeeID
5
start_date
apr 4
managed by
Employee1
(1,n) Project1
(0,n)
EmployeeID
E_name
ProjectID
Title
start_date
Employee
EmployeeID
5
E_Name
mary
Managed_by
ProjectID EmployeeID
1
5
start_date
apr 4
Database Principles
Project
ProjectID
1
Title
A
Considering (1,1) as (1,n):
• The advantage of the second model is that the relationship
<managed by> will be mapped to its own table. This is not
true in the first case.
• If you create the ManagedBy table from the very beginning
it will be there when you need it.
• A little more work up front saves a lot of work later on.
• You can show off later on by saying “It will only take a
second”, come back in 15 minutes and say “the database
can now handle multiple project managers” because it
always could even when it didn’t need to.
• If you are working as a consultant you can bill for 8 hours
even though it didn’t take any time at all.
Database Principles
Library to Relational Data Model
• In the next series of slides we map the Library ER
Model to a Relational Data Model
Database Principles
Cardholder to RDM
not (1,1)
NOTE: As an entity,
Cardholder maps to a
table with all its attributes
as table columns.
The key to Cardholder
becomes the primary key
to the table Cardholder.
Since Cardholder has no
(1,1) participation number
pairs, no new columns
are added to the table.
Database Principles
Book to RDM
not (1,1)
NOTE: As an entity,
Book maps to a table
with all its attributes
as table columns.
The key to Book
becomes the primary key
to the table Book.
Since Book has no (1,1)
participation number
pairs, no new columns
are added to the table.
Database Principles
Reserves to RDM
NOTE: As a relationship
with no (1,1) pair, reserves
is mapped to a table whose
columns include the keys
to the entities participating
in the reserves relationship
together with the attributes
of reserves.
Database Principles
Borrows to RDM
NOTE: As a relationship
with no (1,1) pair, borrows
is mapped to a table whose
columns include the keys
to the entities participating
in the borrows relationship
together with the attributes
of borrows.
Database Principles
Copy to RDM
NOTE: As an entity with
a (1,1) participation number
pair, Copy is mapped to a
table that contains all the
Copy attributes and the key
attributes of the other entities
(Book) in the relationship
where Copy has a (1,1) pair.
The relationship with the
(1,1) pair is not mapped to
a table.
Database Principles
Putting it all Together:
Database Principles
Exercise: ER 2 RDM
Person
ID Fname Lname DOB Addr
Person
pk
Professor
(1,n)
is_a
PK ID
(0,1)
(1,1)
FName
LName
DOB
Addr
has_taught
PhoneNum
Rank
(1,1)
(0,1)
semester
(1,n)
Office
Course
is_a
PK OfficeNum
PK CrsNum
(1,n)
Location
(1,1)
Student
NumCredits
OnProbation
enrolled_in
(1,n)
(0,n)
grade
CrsName
NumCredits
SemLastTaught
CrsSection
(1,n)
PK SecNum
PK SemOffered
Location
TimeTableSlot
(1,1)
Professor
PersonID OfficeNum PhoneNum Rank
pk
fk
Student
PersonID NumCredits OnProbation
pk
Course
CrsNum CrsName
Has_taught
PersonID CrsNum Semester
fk
fk
pk
Office
OfficeNum Location
pk
pk
CrsSection
CrsNum SecNum SemOffered Location TimeTableSlot
fk
pk
Enrolled_in
PersonID CrsNum SecNum SemOffered Grade
fk
fk
fk
fk
pk
Person, Office, Professor
Student, Course, Has_taught
CrsSection, Enrolled_in
Database Principles
NumCredits SemLastTaught