Relational Data Model

1
Lecture 8 (09/19/2005)

• Relational Data Model

2
Some comments

• For assignment 1, please include attributes for
  entity types with unclear descriptions
• Customer
• EMPLOYEE manages a DEPARTMENT
• Only EMPLOYEES with degrees manage DEPARTMENTS
• Maintain all their degree dates
• Assume PhDs have M.S. (or MA or ME) and BS (or BA
  or BE )
• Assume only employees with PHD degrees can serve
  on employee evaluation committees

3
Mapping of N-ary Relationship Types

• For each n-ary relationship type R, where ngt2,
  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

4
(No Transcript)
5
Mapping of N-ary Relationship Types

• The relationship type SUPPLY
• 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

6
Summary of Mapping Constructs and Constraints

• ER Model?Relational Model
• Entity type ? Entity relation
• 11 relationship type ? Foreign key (or lookup or
  merging depending on participation)
• 1N relationship type ? Foreign key (or lookup or
  merging)
• 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
  

7
Options for Mapping Specializations or
Generalizations

• Convert each specialization
• m subclasses S1, S2,.,Sm
• generalized superclass C (k,a1,an)
• 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 lt i
  lt m, with the attributesAttrs(Li) k U
  attributes of Si and PK(Li)k
• Works for any specialization
• Does it really work for overlap?
• Make it work!
• Null for missing!
• Every tuple in a subclass must also in superclass

8
Recommended
9
Options for Mapping Specialization or
Generalization

• Multiple relations-Subclass relations only
• Create a relation Li for each subclass Si, 1 lt i
  lt m, with the attributes Attr(Li) attributes
  of Si U k,a1,an and PK(Li) k
• This option only works for a specialization with
  total superclass participation (every entity in
  the superclass must belong to (at least) one of
  the subclasses)
• Only disjoint?

10
d
11
Options for Mapping Specialization or
Generalization

• 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
• Works only for disjoint specializations
• Why?
• Compare with model 1?
• Less joins
• Has potential for generating a lot of nulls
• All attributes from other subclasses

12
d
13
Options for Mapping Specialization or
Generalization

• 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 lt I lt m, is a Boolean type attribute
  indicating whether a tuple belongs to the
  subclass Si
• Works well for specializations whose subclasses
  are overlapping (but will also work for disjoint
  specializations)
• Lots of zeros and nulls
• Compare with model 1?
• Less joins

14
O
15
Alternative (min, max) Notation for Relationship
Structural Constraints

• Specified on each participation of an entity type
  E in a relationship type R
• Specifies that each entity e in E participates in
  at least min and at most max relationship
  instances in R
• Default (no constraint) min0, maxn
• Must have min?max, min?0, max ?1
• Derived from the knowledge of mini-world
  constraints
• Works for any degree

16
Alternative (min, max) Notation for Relationship
Structural Constraints

• A department has exactly one manager and an
  employee can manage at most one department
• Specify (0,1) for participation of EMPLOYEE in
  MANAGES
• Specify (1,1) for participation of DEPARTMENT in
  MANAGES

17
The (min,max) Notation Relationship Constraints

• An employee can work for exactly one department
  but a department must have at least 4 employees
• Specify (1,1) for participation of EMPLOYEE in
  WORKS_FOR
• Specify (4,N) for participation of DEPARTMENT in
  WORKS_FOR

18
COMPANY ER Schema Diagram using (min, max)
notation

• No need for total/partial
• Min 0 ? partial
• Min 1 ? total
• Works for any degree
• More accurate
• Might not have info

19
0..N
0..N
0..N
Multi-owner weak entity type
20
0..N
0..N
1..1
21
Mapping ExerciseFigure below shows an ER schema
for a database that may be used to keep track of
transport ships and their locations for maritime
authorities. Map this schema into a relational
schema, and specify all primary keys and foreign
keys.
A ship can visit the same port once during a
single day