Transcript CS186: Introduction to Database Systems

EECS 647: Introduction to
Database Systems
Instructor: Luke Huan
Spring 2007
Review

Relational algebra




Use temporary variable
Use foreign key to join relations
A comparison is to identify a relationship
Use minus in non-monotonic operations
7/17/2015
Luke Huan Univ. of Kansas
2
Exercises of R. A.
Reserves
sid bid
22
58
bname
Interlake
Interlake
Clipper
Marine
101
103
10/10/96
11/12/96
Sailors
Boats
bid
101
102
103
104
day
color
Blue
Red
Green
Red
sid sname rating age
22
31
58
dustin
lubber
rusty
7
8
10
45.0
55.5
35.0
Problem 1 Find names of sailors who’ve
reserved boat #103

Solution:
 sname((
bid 103
Who reserved boat
#103?
Boat #103
¾bid
Reserves)*Sailors)
¼sname

Sailors
= “103”Reserves
Problem 2: Find names of sailors who’ve
reserved a red boat

Information about boat color only available in Boats; so
need an extra join:
Names of sailors who
reserved red boat ¼sname

¼SID
Who reserved red
boats?

Red boats
¾color
Sailors
= “red”
Boat
Reserve
Problem 3: Find names of sailors who’ve
reserved a red boat or a green boat

Can identify all red or green boats, then find
sailors who’ve reserved one of these boats:
Names of sailors who
reserved red boat ¼sname

Who reserved red
boats?
Red boats
¾color
= “red”

color = “green”
¼SID
Sailors

Reserve
Boat
Problem 4: Find names of sailors who’ve
reserved only one boat
Today’s Topic

Logical database design: Translation ER to relation
schema
7/17/2015
Luke Huan Univ. of Kansas
8
Database Design
7/17/2015
Luke Huan Univ. of Kansas
9
Database design steps: review





Understand the mini-world
Specify it using a database design model (e.g., E/R)
Translate specification to the data model of DBMS (e.g.,
relational)
Create DBMS schema
Next: translating E/R design to relational schema
7/17/2015
Luke Huan Univ. of Kansas
10
E/R model: review

Entity sets



Keys
Weak entity sets
Relationship sets




Attributes on relationships
Multiplicity
Roles
Binary versus N-ary relationships

7/17/2015
Modeling N-ary relationships with weak entity sets and
binary relationships
Luke Huan Univ. of Kansas
11
Translating entity sets

An entity set translates directly to a table


Attributes ! columns
Key attributes ! key columns
SID
CID
Students
Enroll
name
Courses
title
grade
Student (SID, name)
7/17/2015
Course (CID, title)
Luke Huan Univ. of Kansas
12
Translating entity sets (cont.)


No multi-valued attributes (called first Normal Form)
Composite attributes is broken down into a list of atomic
attributes
7/17/2015
Luke Huan Univ. of Kansas
13
Translating weak entity sets


Remember the “borrowed” key attributes
Watch out for attribute name conflicts
number
name
Rooms
In
capacity
Buildings
year
In
number
Seats
Building (building_name, year)
Rooms (building_name, room_number, capacity)
Seats (building_name, room_number, seat_number, left_or_right)
L/R?
7/17/2015
Luke Huan Univ. of Kansas
14
Translating 1:1 Relation Types

For each binary 1:1 relationship type R in the ER
schema, identify the relations S and T that correspond to
the entity types participating in R. There are three
possible approaches:

(1) Foreign Key approach: Choose one of the
relations-S, say-and include a foreign key in S the
primary key of T. It is better to choose an entity type
with total participation in R in the role of S.
Example: 1:1 relation MANAGES is mapped by
choosing the participating entity type DEPARTMENT to
serve in the role of S, because its participation in the

7/17/2015
Luke Huan Univ. of Kansas
15
ER-to-Relational Mapping Algorithm (cont)

Step 4: Mapping of Binary 1:N Relationship Types.



For each regular binary 1:N relationship type R, identify the relation S
that represent the participating entity type at the N-side of the
relationship type.
Include as foreign key in S the primary key of the relation T that
represents the other entity type participating in R.
Include any simple attributes of the 1:N relation type as attributes of S.
Example: 1:N relationship types WORKS_FOR, CONTROLS, and
SUPERVISION in the figure. For WORKS_FOR we include the
primary key DNUMBER of the DEPARTMENT relation as foreign key
in the EMPLOYEE relation and call it DNO.
7/17/2015
Luke Huan Univ. of Kansas
16
ER-to-Relational Mapping Algorithm (cont)

Step 5: Mapping of Binary M:N Relationship Types.
For each regular binary M:N relationship type R, 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; their combination will form the
primary key of S.
Also include any simple attributes of the M:N relationship type (or simple
components of composite attributes) as attributes of S.



Example: The M:N relationship type WORKS_ON from the ER diagram is
mapped by creating a relation WORKS_ON in the relational database
schema. The primary keys of the PROJECT and EMPLOYEE relations are
included as foreign keys in WORKS_ON and renamed PNO and ESSN,
respectively.
Attribute HOURS in WORKS_ON represents the HOURS attribute of the
relation type. The primary key of the WORKS_ON relation is the
combination of the foreign key attributes {ESSN, PNO}.
7/17/2015
Luke Huan Univ. of Kansas
17
ER-to-Relational Mapping Algorithm (cont)

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.
Example: The relation DEPT_LOCATIONS is created. The attribute
DLOCATION represents the multivalued attribute LOCATIONS of
DEPARTMENT, while DNUMBER-as foreign key-represents the primary
key of the DEPARTMENT relation. The primary key of R is the
combination of {DNUMBER, DLOCATION}.
7/17/2015
Luke Huan Univ. of Kansas
18
ER-to-Relational Mapping Algorithm (cont)

Step 7: Mapping of N-ary Relationship Types.



For each n-ary relationship type R, where n>2, create a new
relationship 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.
Example: The relationship type SUPPY in the ER below. This can be
mapped to the relation SUPPLY shown in the relational schema, whose
primary key is the combination of the three foreign keys {SNAME,
PARTNO, PROJNAME}
7/17/2015
Luke Huan Univ. of Kansas
19
FIGURE 4.11
Ternary relationship types. (a) The SUPPLY relationship.
7/17/2015
Luke Huan Univ. of Kansas
20
FIGURE 7.3
Mapping the n-ary relationship type SUPPLY from Figure 4.11a.
7/17/2015
Luke Huan Univ. of Kansas
21
Summary of Mapping constructs and constraints
Table 7.1 Correspondence between ER and Relational Models
ER Model
Entity type
1:1 or 1:N relationship type
M:N relationship type
n-ary relationship type
Simple attribute
Composite attribute
Multivalued attribute
Value set
Key attribute
7/17/2015
Relational Model
“Entity” relation
Foreign key (or “relationship” relation)
“Relationship” relation and two foreign keys
“Relationship” relation and n foreign keys
Attribute
Set of simple component attributes
Relation and foreign key
Domain
Primary (or secondary) key
Luke Huan Univ. of Kansas
22
Mapping EER Model Constructs to Relations

Step8: Options for Mapping Specialization or Generalization.
Convert each specialization with m subclasses {S1, S2,….,Sm} and generalized
superclass C, where the attributes of C are {k,a1,…an} and k is the (primary) key, into
relational schemas using one of the four following options:
Option 8A: Multiple relations-Superclass and subclasses.
Create a relation L for C with attributes Attrs(L) = {k,a1,…an} and PK(L) = k. Create a relation Li
for each subclass Si, 1 < i < m, with the attributesAttrs(Li) = {k} U {attributes of Si} and
PK(Li)=k. This option works for any specialization (total or partial, disjoint of over-lapping).
Option 8B: Multiple relations-Subclass relations only
Create a relation Li for each subclass Si, 1 < i < m, with the attributes Attr(Li) = {attributes of Si}
U {k,a1…,an} and PK(Li) = k. This option only works for a specialization whose subclasses are
total (every entity in the superclass must belong to (at least) one of the subclasses).
7/17/2015
Luke Huan Univ. of Kansas
23
FIGURE 4.4
EER diagram
notation for an
attribute-defined
specialization on
JobType.
7/17/2015
Luke Huan Univ. of Kansas
24
FIGURE 7.4
Options for mapping specialization or generalization.
(a) Mapping the EER schema in Figure 4.4 using option 8A.
7/17/2015
Luke Huan Univ. of Kansas
25
FIGURE 4.3
Generalization. (b) Generalizing CAR and TRUCK into the superclass VEHICLE.
7/17/2015
Luke Huan Univ. of Kansas
26
Translating relationship sets

A relationship set translates to a table



Keys of connected entity sets ! columns
Attributes of the relationship set (if any) ! columns
Multiplicity of the relationship set determines the key of
the table
SID
CID
Students
Enroll
Courses
name
title
grade
Enroll (SID, CID, grade)
7/17/2015
Luke Huan Univ. of Kansas
27
More examples
SID
CID
Students
name
Enroll
Courses
title
TID
TA’s
name
Enroll (SID, CID, TID)
husband
SSN
Persons
Marry
wife
Marry (husband_SSN, wife_SSN)
Marry (husband_SSN, wife_SSN)
7/17/2015
Luke Huan Univ. of Kansas
28
Translating double diamonds


Recall that a double-diamond relationship set connects a weak
entity set to another entity set
No need to translate because the relationship is implicit in the
weak entity set’s translation
number
name
Rooms
In
capacity
year
In
number
Seats
L/R?
7/17/2015
Buildings
RoomInBuilding
(room_building_name, room_numb
building_name)
is subsumed by
Rooms (building_name, room_number, cap
Luke Huan Univ. of Kansas
29
Design principles

KISS


Avoid redundancy



Keep It Simple, Stupid
Redundancy wastes space, complicates updates and
deletes, 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
7/17/2015
Luke Huan Univ. of Kansas
30
Review

How to map ER to relational schemas

Next class meeting:

How to design “good” relational schema
7/17/2015
Luke Huan Univ. of Kansas
31