Mapping

1
Mapping
2
Chapter 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 11 Relation Types
• Step 4 Mapping of Binary 1N Relationship
  Types.
• Step 5 Mapping of Binary MN Relationship
  Types.
• Step 6 Mapping of Multivalued attributes.
• Step 7 Mapping of N-ary Relationship Types.
• Mapping EER Model Constructs to Relations
• Step 8 Options for Mapping Specialization
  or Generalization.
• Step 9 Mapping of Union Types (Categories).

3
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.
• Example We create the relations EMPLOYEE,
  DEPARTMENT, and PROJECT in the relational schema
  corresponding to the regular entities in the ER
  diagram. SSN, DNUMBER, and PNUMBER are the
  primary keys for the relations EMPLOYEE,
  DEPARTMENT, and PROJECT as shown.

4
FIGURE 7.1The ER conceptual schema diagram for
the COMPANY database.
5
FIGURE 7.2Result of mapping the COMPANY ER
schema into a relational schema.
6
ER-to-Relational Mapping Algorithm (cont)

• 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 and
  include all simple attributes (or simple
  components of composite attributes) of W as
  attributes of R.
• In addition, 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.
• Example Create the relation DEPENDENT
  in this step to correspond to the weak entity
  type DEPENDENT. Include the primary key SSN of
  the EMPLOYEE relation as a foreign key attribute
  of DEPENDENT (renamed to ESSN).
• The primary key of the DEPENDENT relation is
  the combination ESSN, DEPENDENT_NAME because
  DEPENDENT_NAME is the partial key of DEPENDENT.

7
ER-to-Relational Mapping Algorithm (cont)

• Step 3 Mapping of Binary 11 Relation Types
• For each binary 11 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 11 relation MANAGES is mapped by
  choosing the participating entity type DEPARTMENT
  to serve in the role of S, because its
  participation in the MANAGES relationship type is
  total.
• (2) Merged relation option An alternate
  mapping of a 11 relationship type is possible by
  merging the two entity types and the relationship
  into a single relation. This may be appropriate
  when both participations are total.
• (3) Cross-reference or relationship relation
  option The third alternative is to set up a
  third relation R for the purpose of
  cross-referencing the primary keys of the two
  relations S and T representing the entity types.

8
ER-to-Relational Mapping Algorithm (cont)

• Step 4 Mapping of Binary 1N Relationship Types.
• For each regular binary 1N 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 1N relation
  type as attributes of S.
• Example 1N 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.

9
ER-to-Relational Mapping Algorithm (cont)

• Step 5 Mapping of Binary MN Relationship Types.
• For each regular binary MN 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 MN
  relationship type (or simple components of
  composite attributes) as attributes of S.
• Example The MN 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.

10
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.

11
ER-to-Relational Mapping Algorithm (cont)

• Step 7 Mapping of N-ary Relationship Types.
• For each n-ary relationship type R, where n2,
  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

12
FIGURE 4.11Ternary relationship types. (a) The
SUPPLY relationship.
13
FIGURE 7.3Mapping the n-ary relationship type
SUPPLY from Figure 4.11a.
14
Summary of Mapping constructs and constraints

Table 7.1 Correspondence between ER and
Relational Models ER Model Relational
Model Entity type Entity relation 11 or 1N
relationship type Foreign key (or relationship
relation) MN 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 (or secondary) key
15
ER to Relational

• Aggregation Relationship will be mapped to a
  table, extended with p.k. of participating
  entity set(s) relationship its own attributes
  ( if exists)
• Example

N
Interview
M
Company
Job-Applicant
Results-In
Job-Offer
P.K(JOB_OFFER) P.K(INTERVIEW)
RESULTS_IN
16
ER to Relational

Loan
Borrows
M
N
Customer
Loan
Amount
SSN
Name
Loanofficer
Employee
Addr
ESSN
Name
17
ER to Relational
Customer
SSN NAME
Loan
LOAN AMOUNT
ESSN NAME ADDRESS
Employee
SSN LOAN
Borrows
Loan-Officer
SSN LOAN ESSN
18
EER to Relational

• Subclasses are overlapping ( will also work for a
  disjoint specialization), each ti, 1 Boolean attribute indicating whether a tuple
  belongs to subclass, Si.
• Option 1 works on disjoint /overlapping and
  total/partial.
• Option 2 works with both the disjoint and total.
• For overlapping generalization, some values will
  be stored multiple times unnecessarily.

19
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 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 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).

20
FIGURE 4.4EER diagram notation for an
attribute-defined specialization on JobType.
21
FIGURE 7.4Options for mapping specialization or
generalization. (a) Mapping the EER schema in
Figure 4.4 using option 8A.
22
FIGURE 4.3Generalization. (b) Generalizing CAR
and TRUCK into the superclass VEHICLE.
23
FIGURE 7.4Options for mapping specialization or
generalization. (b) Mapping the EER schema in
Figure 4.3b using option 8B.
24
Mapping EER Model Constructs to Relations (cont)

• Option 8C Single relation with one type
  attribute.
• Create a single relation L with attributes
  Attrs(L) k,a1,an U attributes of S1 UU
  attributes of Sm U t and PK(L) k. The
  attribute t is called a type (or discriminating)
  attribute that indicates the subclass to which
  each tuple belongs
• Option 8D Single relation with multiple
  type attributes.
• Create a single relation schema L with
  attributes Attrs(L) k,a1,an U attributes of
  S1 UU attributes of Sm U t1, t2,,tm and
  PK(L) k. Each ti, 1 attribute indicating whether a tuple belongs to
  the subclass Si.

25
FIGURE 4.4EER diagram notation for an
attribute-defined specialization on JobType.
26
FIGURE 7.4Options for mapping specialization or
generalization. (c) Mapping the EER schema in
Figure 4.4 using option 8C.
27
FIGURE 4.5EER diagram notation for an
overlapping (nondisjoint) specialization.
28
FIGURE 7.4Options for mapping specialization or
generalization. (d) Mapping Figure 4.5 using
option 8D with Boolean type fields Mflag and
Pflag.
29
Mapping Exercise

• Exercise 7.4.

FIGURE 7.7An ER schema for a SHIP_TRACKING
database.
30
Example Instances
R1
S1

• We will use these instances of the Sailors and
  Reserves relations in our examples.
• If the key for the Reserves relation contained
  only the attributes sid and bid, how would the
  semantics differ?

S2
31
Example of Conceptual Evaluation
SELECT S.sname FROM Sailors S, Reserves
R WHERE S.sidR.sid AND R.bid103
32
Find sailors whove reserved at least one boat
SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sidR.sid

• Would adding DISTINCT to this query make a
  difference?
• What is the effect of replacing S.sid by S.sname
  in the SELECT clause? Would adding DISTINCT to
  this variant of the query make a difference?

33
Find sids of sailors whove reserved a red or a
green boat
SELECT S.sid FROM Sailors S, Boats B, Reserves
R WHERE S.sidR.sid AND R.bidB.bid AND
(B.colorred OR B.colorgreen)

• UNION Can be used to compute the union of any
  two union-compatible sets of tuples (which are
  themselves the result of SQL queries).
• If we replace OR by AND in the first version,
  what do we get?
• Also available EXCEPT (What do we get if we
  replace UNION by EXCEPT?)

SELECT S.sid FROM Sailors S, Boats B, Reserves
R WHERE S.sidR.sid AND R.bidB.bid
AND B.colorred UNION SELECT S.sid FROM
Sailors S, Boats B, Reserves R WHERE S.sidR.sid
AND R.bidB.bid AND
B.colorgreen
34
Find sids of sailors whove reserved a red and a
green boat
SELECT S.sid FROM Sailors S, Boats B1, Reserves
R1, Boats B2, Reserves R2 WHERE
S.sidR1.sid AND R1.bidB1.bid AND
S.sidR2.sid AND R2.bidB2.bid AND
(B1.colorred AND B2.colorgreen)

• INTERSECT Can be used to compute the
  intersection of any two union-compatible sets of
  tuples.
• Included in the SQL/92 standard, but some systems
  dont support it.
• Contrast symmetry of the UNION and INTERSECT
  queries with how much the other versions differ.

Key field!
SELECT S.sid FROM Sailors S, Boats B, Reserves
R WHERE S.sidR.sid AND R.bidB.bid
AND B.colorred INTERSECT SELECT S.sid FROM
Sailors S, Boats B, Reserves R WHERE
S.sidR.sid AND R.bidB.bid AND
B.colorgreen
35
Nested Queries
Find names of sailors whove reserved boat 103
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid
FROM Reserves R
WHERE R.bid103)

• A very powerful feature of SQL a WHERE clause
  can itself contain an SQL query! (Actually, so
  can FROM and HAVING clauses.)
• To find sailors whove not reserved 103, use NOT
  IN.
• To understand semantics of nested queries, think
  of a nested loops evaluation For each Sailors
  tuple, check the qualification by computing the
  subquery.

36
Nested Queries with Correlation
Find names of sailors whove reserved boat 103
SELECT S.sname FROM Sailors S WHERE EXISTS
(SELECT FROM
Reserves R WHERE
R.bid103 AND S.sidR.sid)

• EXISTS is another set comparison operator, like
  IN.
• If UNIQUE is used, and is replaced by R.bid,
  finds sailors with at most one reservation for
  boat 103. (UNIQUE checks for duplicate tuples
  denotes all attributes. Why do we have to
  replace by R.bid?)
• Illustrates why, in general, subquery must be
  re-computed for each Sailors tuple.

37
More on Set-Comparison Operators

• Weve already seen IN, EXISTS and UNIQUE. Can
  also use NOT IN, NOT EXISTS and NOT UNIQUE.
• Also available op ANY, op ALL, op IN
• Find sailors whose rating is greater than that of
  some sailor called Horatio

SELECT FROM Sailors S WHERE S.rating ANY
(SELECT S2.rating
FROM Sailors S2
WHERE S2.snameHoratio)
38
Division in SQL
Find names of sailors whove reserved all boats.

• SELECT S.sname
• FROM Sailors S
• WHERE
• (SELECT R.bid
• FROM Reserves R
• WHERE R.sidS.sid)
• Contains
• (SELECT B.bid
• FROM Boats B)

(1)
39
Division in SQL
(2)
SELECT S.sname FROM Sailors S WHERE NOT EXISTS
((SELECT B.bid
FROM Boats B) EXCEPT
(SELECT R.bid FROM
Reserves R WHERE R.sidS.sid))
Find sailors whove reserved all boats.

• Lets do it the hard way, without EXCEPT

SELECT S.sname FROM Sailors S WHERE NOT EXISTS
(SELECT B.bid
FROM Boats B
WHERE NOT EXISTS (SELECT R.bid

FROM Reserves R

WHERE R.bidB.bid

AND R.sidS.sid))
(3)
Sailors S such that ...
there is no boat B without ...
a Reserves tuple showing S reserved B
40
Aggregate Operators
COUNT () COUNT ( DISTINCT A) SUM ( DISTINCT
A) AVG ( DISTINCT A) MAX (A) MIN (A)

• Significant extension of relational algebra.

single column
SELECT COUNT () FROM Sailors S
SELECT S.sname FROM Sailors S WHERE S.rating
(SELECT MAX(S2.rating)
FROM Sailors S2)
SELECT AVG (S.age) FROM Sailors S WHERE
S.rating10
SELECT COUNT (DISTINCT S.rating) FROM Sailors
S WHERE S.snameBob
SELECT AVG ( DISTINCT S.age) FROM Sailors
S WHERE S.rating10
41
Find name and age of the oldest sailor(s)

• The first query is illegal! (Well look into the
  reason a bit later, when we discuss GROUP BY.)
• The third query is equivalent to the second
  query, and is allowed in the SQL/92 standard, but
  is not supported in some systems.

SELECT S.sname, MAX (S.age) FROM Sailors S
SELECT S.sname, S.age FROM Sailors S WHERE
S.age (SELECT MAX (S2.age)
FROM Sailors S2)
SELECT S.sname, S.age FROM Sailors S WHERE
(SELECT MAX (S2.age) FROM
Sailors S2) S.age
42
GROUP BY and HAVING

• So far, weve applied aggregate operators to all
  (qualifying) tuples. Sometimes, we want to apply
  them to each of several groups of tuples.
• Consider Find the age of the youngest sailor
  for each rating level.
• In general, we dont know how many rating levels
  exist, and what the rating values for these
  levels are!
• Suppose we know that rating values go from 1 to
  10 we can write 10 queries that look like this
  (!)

SELECT MIN (S.age) FROM Sailors S WHERE
S.rating i
For i 1, 2, ... , 10
43
Queries With GROUP BY and HAVING
SELECT DISTINCT target-list FROM
relation-list WHERE qualification GROUP
BY grouping-list HAVING group-qualification

• The target-list contains (i) attribute names
  (ii) terms with aggregate operations (e.g., MIN
  (S.age)).
• The attribute list (i) must be a subset of
  grouping-list. Intuitively, each answer tuple
  corresponds to a group, and these attributes must
  have a single value per group. (A group is a set
  of tuples that have the same value for all
  attributes in grouping-list.)

44
For each red boat, find the number of
reservations for this boat
SELECT B.bid, COUNT () AS scount FROM Boats
B, Reserves R WHERE R.bidB.bid AND
B.colorred GROUP BY B.bid

• Grouping over a join of two relations.

45
For each red boat, find the number of
reservations for this boat

• What if we add Sailors and the condition
  involving S.sid?
• Redundant

• What do we get if we remove B.colorred from
  the
• WHERE clause and add a HAVING clause with this
• condition?
• SELECT B.bid, COUNT () AS scount
• FROM Boats B, Reserves R
• WHERE R.bidB.bid
• GROUP BY B.bid
• Having B.color red

46
Find the average age of sailors for each rating
level with at least 2 sailors
SELECT S.rating, AVG(S.Age) FROM Sailors
S GROUP BY S.rating HAVING COUNT () 1

• Only S.rating and S.age are mentioned in the
  SELECT, GROUP BY or HAVING clauses other
  attributes unnecessary.
• 2nd column of result is unnamed. (Use AS to name
  it.)

Answer relation
47
Find the average age of sailors who are of voting
age for each rating level with at least 2 such
sailors
SELECT S.rating, AVG(S.Age) FROM Sailors
S WHERE S.age 18 GROUP BY S.rating HAVING
COUNT () 1
Answer relation
48
Find the average age of sailors who are of voting
age for each rating level with at least 2 sailors
(of any age)
SELECT S.rating, avg (S.age) FROM Sailors
S WHERE S.age 18 GROUP BY S.rating HAVING 1
FROM Sailors S2 WHERE
S.ratingS2.rating)

• Shows HAVING clause can also contain a subquery.
  
• Compare this with the query where we considered
  only ratings with 2 sailors 18!
• What if HAVING clause is replaced by
• HAVING COUNT() 1

49
Find the average age of sailors who are of voting
age for each rating level with at least 2 sailors
(of any age)
Answer relation
50
Find the average age of sailors who are of voting
age for each rating level with at least 2 such
sailors
SELECT S.rating, AVG (S.age) FROM Sailors
S WHERE S.age 18 GROUP BY S.rating HAVING 1
FROM Sailors S2 WHERE
S.ratingS2.rating and
S2.age18)