6: Relational Data Model
(part two)
views
a ‘virtual’ relation schema constructed
! from one or more relation schemas
a view does not store data, a window
! used to view selected data from relations
‘derived’ relation state resulting from the
! completion of a relational expression
(e.g. join, project) at that time.
views
advantages
same data can be seen by different users
! in different ways at the same time
provide security by restricting user access
! to predetermined tuples and attributes
! hide data complexity from the user
er model → logical schema
fine-granular design-specific er model → relational schema
D
preserve the information of the source
data model (ER model) in the target
data model (relational data model)
mapping entity types
W create a relation schema for each entity type
5 create an attribute for every stored attribute
no derived or multi-valued attributes, constituents of composite
K choose and underline a primary key
from the candidate keys
for weak entity type, add the parent’s
primary key
parent primary key and partial key = primary key for relation
example er diagram
figure 6.2, page 262
relation schemas for figure 6.2
EMPLOYEE Emp_e#a Emp_e#n Emp_fname Emp_minit Emp_lname Emp_nametag Emp_gender Emp_address Emp_salary Emp_datehired
PLANT Pl_name Pl_p# Pl_budget
BUILDING Bld_building Bld_pl_p#
- no derived attributes
- only constituent attributes for emp# and name of employee are recorded
mapping relationship types
1:1 K
use foreign key
1:n constraints
participation constraints are not mapped
1:n relationship
1 child
referencing relation schema
n parent
referenced relation schema
which is the child and parent in the
EMPLOYEE-PLANT RELATIONSHIP?
1:n relationship
EMPLOYEE Emp_e#a Emp_e#n Emp_fname Emp_minit Emp_lname Emp_nametag Emp_gender Emp_address Emp_salary Emp_datehired Emp_pl_name
notice that this is referencing a candidate key
PLANT Pl_name Pl_p# Pl_budget
DIRECTED
ARC
BUILDING Bld_building Bld_pl_p# METHOD
alternative notation
inclusion dependency method
EMPLOYEE (Emp_e#a, Emp_e#n, Emp_fname, Emp_minit, Emp_lname,
Emp_nametag, Emp_address, Emp_salary, Emp_datehired, Emp_pl_name)
# EMPLOYEE.{Emp_pl_name} C PLANT.{Pl_name}
PLANT (Pl_name, Pl_p#, Pl_budget)
BUILDING (Bld_building, Bld_pl_p#)
# BUILDING.{Bld_pl_p#} C PLANT.{Pl_p#}
what about participation?
some employees don’t work in plants
EMPLOYEE is the child, will have PLANT foreign key
null foreign key values
null foreign keys are possible, but are considered bad practice
option 1: foreign key design
(1,1): foreign key must not have null values
Pl_# Pl_name Emp_n Emp_a Emp_name Emp_pl_#
10 Aberdeen 7 XY Biggio 13
11 Bellaire 5 YW Bagwell 13
12 Edloe 12 FX Kent 11
13 Underwood 22 CD Clemens 11
44 XP Oswalt 10
PLANT
EMPLOYEE
(0,1): foreign key can have null values
Pl_# Pl_name Emp_n Emp_a Emp_name Emp_pl_#
10 Aberdeen 7 XY Biggio 13
11 Bellaire 5 YW Bagwell NULL
12 Edloe 12 FX Kent 11
13 Underwood 22 CD Clemens NULL
44 XP Oswalt 10
PLANT
EMPLOYEE
option 2: cross-referencing design
W create a separate relation schema
representing the relationship type
3 WORKS_IN
tuples in the relation represent only
employees who actually work in a plant
all WORKS_IN tuples have a corresponding
employee tuple in the EMPLOYEE relation
cross-referencing design
directed arc method
EMPLOYEE Emp_e#a Emp_e#n Emp_fname Emp_minit Emp_lname Emp_nametag Emp_gender Emp_address Emp_salary Emp_datehired
WORKS_IN Wrk_emp_e#a Wrk_emp_e#n Wrk_pl_p#
PLANT Pl_name Pl_p# Pl_budget
additional relation schema,
but no null values
BUILDING Bld_building Bld_pl_p#
cross-referencing design
inclusion dependency method
EMPLOYEE (Emp_e#a, Emp_e#n, Emp_fname, Emp_minit, Emp_lname,
Emp_nametag, Emp_address, Emp_salary, Emp_datehired)
WORKS_IN (Wrk_emp_e#a, Wrk_emp_e#n, Wrk_pl_p#)
# WORKS_IN.{Wrk_pl_p#} C PLANT.{Pl_p#}
# WORKS_IN.{Wrk_emp_e#a, Wrk_emp_e#n} C EMPLOYEE.{Emp_e#a,
Emp_e#n}
PLANT (Pl_name, Pl_p#, Pl_budget)
BUILDING (Bld_building, Bld_pl_p#)
# BUILDING.{Bld_pl_p#} C PLANT.{Pl_p#}
two designs demonstrated
foreign key design
Pl_# Pl_name Emp_n Emp_a Emp_name Emp_pl_#
10 Aberdeen 7 XY Biggio 13
11 Bellaire 5 YW Bagwell NULL
12 Edloe 12 FX Kent 11
13 Underwood 22 CD Clemens NULL
44 XP Oswalt 10
PLANT
EMPLOYEE
cross-referencing design
Pl_# Pl_name Emp_n Emp_a Emp_name Emp_n Emp_a Pl_pl#
10 Aberdeen 7 XY Biggio 7 XY 13
11 Bellaire 5 YW Bagwell 12 FX 11
12 Edloe 12 FX Kent 44 XP 10
13 Underwood 22 CD Clemens WORKS_IN
PLANT 44 XP Oswalt (column names
abbreviated)
EMPLOYEE
1:1 relationship
participation of one of the
entity types is total
3 participation of both of the
cases entity types is partial
participation of both of the
entity types is total
one entity type total participation
≈
one entity type total participation
" choose the entity type with total
participation as the child
" add the foreign key to the child
relation schema
specify constraints:
" foreign key must not have a null value (total)
foreign key must be unique (1:1)
one entity type total participation
EMPLOYEE Emp_e#a Emp_e#n Emp_fname Emp_minit Emp_lname Emp_nametag Emp_gender Emp_address Emp_salary Emp_datehired
PLANT Pl_name Pl_p# Pl_budget Pl_emp_e#a Pl_emp_e#n Pl_mgrsdate
Pl_mgrsdate comes from the PLANT-EMPLOYEE relationship
Pl_# Pl_name Pl_emp_n Pl_emp_a Pl_mgrstartdt
10 Aberdeen 81 FC 10/11/2004
11 Bellaire 82 FD 7/12/2002
12 Edloe 83 IU 10/1/2000
13 Underwood 84 FD 2/10/2001
PLANT
both entity types partial
≈
both entity types partial
" choose either entity type to be
the child
" add the foreign key to the child
relation schema
" foreign key is allowed to have a null value
not ideal. there is a better option.
both entity types partial
Emp_mgrsdate comes from the PLANT-EMPLOYEE relationship
EMPLOYEE Emp_e#a Emp_e#n Emp_fname Emp_minit Emp_lname Emp_nametag Emp_gender Emp_address Emp_salary Emp_datehired Emp_pl_p# Emp_mgrsdate
PLANT Pl_name Pl_p# Pl_budget
Emp_e#n Emp_e#a Emp_name Emp_pl_#
Pl_# Pl_name 7 XY Biggio 13
10 Aberdeen 5 YW Bagwell NULL
11 Bellaire 12 FX Kent 11
12 Edloe 22 CD Clements NULL
13 Underwood 44 XP Oswalt 10
PLANT EMPLOYEE
alternative solution
cross-referencing design
EMPLOYEE Emp_e#a Emp_e#n Emp_fname Emp_minit Emp_lname Emp_nametag Emp_gender Emp_address Emp_salary Emp_datehired Emp_pl_p# Emp_mgrsdate
MANAGED_BY Man_pl__p# Man_emp_e#a Man_emp_e#n Man_mgrsdte
PLANT Pl_name Pl_p# Pl_budget
EMPLOYEE (Emp_e#a, Emp_e#n, Emp_fname, Emp_minit, Emp_lname, Emp_nametag,
Emp_address, Emp_salary, Emp_datehired)
PLANT (Pl_name, Pl_p#, Pl_budget)
MANAGED_BY (Man_pl_p#, Man_emp_e#a, Man_emp_e#n, Man_mgrsdate)
# MANAGED_BY.{Man_pl_p#} C PLANT.{Pl_p#}
# MANAGED_BY.{Man_emp_e#a, Man_emp_e#n} C EMPLOYEE.{Emp_e#a, Emp_e#n}
mutual referencing design
can be used for both of the cases so far
" make both entity types a child
" add foreign key to both schemas
# unnecessary relationship
V creates a cycle that requires runtime
enforcement
# requires specification of additional
constraints for consistency
both entity types total
modified from figure 6.17
[A, 30]
Mgr_name
both entity types total
" use mutual referencing design
# requires procedural intervention
V creates a cycle that requires runtime
enforcement
alternative: use a single schema design
W (collapse the two relation schemas into
one)
mutual referencing design
MANAGER Mgr_name Mgr_exp Mgr_pl_p#
PLANT Pl_name Pl_p# Pl_budget Pl_mgr_name Pl_mgrsdte
MANAGER (Mgr_name, Mgr_exp, Mgr_pl_p#)
# MANAGER.{Mgr_pl_p#} C PLANT.{Pl_p#}
PLANT (Pl_name, Pl_p#, Pl_budget, Pl_mgr_name, Pl_mgrsdte)
# PLANT.{Pl_p#} C MANAGER.{Mgr_name}