CHAPTER 9 OUTLINE
Relational Database Design Using ER-to-Relational Mapping Mapping EER Model Constructs to Relations
E-R to Relational Mapping
1. Regular entity type - map to a relation
◦ One key of the entity type chosen as primary key for the relation
2. Weak entity type
◦ Include the attribute(s) of the owner relation
◦ Primary key is combination of owner key attributes and partial key of week entity type.
3. Binary M:1 relationship - map to foreign key in relation at M-side referring to a relation at 1-side
4. Binary 1:1 relationship – map to a foreign key from one relation referring to the other relation
ER to Relational Mapping 2
E-R to Relational Mapping
5.
Binary M:N relationship - maps to a relation whose primary key includes the keys of
both participating relations
6.
Multi-valued attributes - map to a relation R that includes the key of the owner relation
7.
Each n -ary relationship - maps to a relation that includes the keys of all participating
relations
ER to Relational Mapping 3
ER-to-Relational Mapping Algorithm
⚫
COMPANY database example
◦ Mapping will create tables with simple single-valued attributes
Where are the Regular entities?
Weak entities?
1:1?
1:M?
M:N?
Multivalued Attributes?
ER-to-Relational Mapping Algorithm
⚫
Step 1: Mapping of Regular Entity Types
◦ For each regular entity type, create a relation R that includes all the simple attributes of E
◦ Called entity relations
• Each tuple represents an entity instance
ER-to-Relational Mapping Algorithm
⚫
Step 2: Mapping of Weak Entity Types
◦ For each weak entity type, create a relation R and include all simple attributes of the entity type as attributes of R
◦ Include primary key attribute of owner as foreign key attributes of R
Foreign Key
⚫
a field (or collection of fields) in one
relation that uniquely identifies a tuple of another relation
⚫
equal to the candidate key in some tuple of the PK relation, or else NULL
⚫
Multiple tuples in the FK relation may
refer to the same tuple in the PK relation
⚫
Same data type and domain as PK
⚫
May have different name
7
ER-to-Relational Mapping Algorithm (cont’d.)
⚫
Step 3: Mapping of Binary 1:N Relationship Types
◦ For each regular binary 1:N relationship type
• Primary key from 1-side becomes foreign key in N-side
ER-to-Relational Mapping Algorithm (cont’d.)
⚫
Step 4: Mapping of Binary 1:1 Relationship Types
◦ For each binary 1:1 relationship type
• Identify relations that correspond to entity types participating in R
◦ Possible approaches:
• Foreign key approach
• Merged relationship approach
• Cross-reference or relationship relation approach
ER-to-Relational Mapping Algorithm
⚫
Step 5: Mapping of Binary M:N Relationship Types
◦ For each binary M:N relationship type
•Create a new relation S
•Include primary key of participating entity types as foreign key
attributes in S
•Include any simple attributes of M:N relationship type
ER-to-Relational Mapping Algorithm (cont’d.)
⚫
Step 6: Mapping of Multivalued Attributes
◦ For each multivalued attribute
• Create a new relation
• Primary key of R is the combination of A and K
• If the multivalued attribute is composite, include its simple components
Result of Mapping (steps 1-6)
ER to Relational Mapping 13
Step 7: n-ary relationships
⚫ Each n-ary (n>2) relationship type maps into a relation.
◦ The primary key of this relation is the combination of the primary keys of the participating entity types.
◦ These are also foreign keys.
◦ Attributes of the relationship type maps to
attributes of the relation, similar to those of regular entity types.
ER to Relational Mapping 14
An ERD Example
PROJECT
SUPPLIER
PART
SPJ SP
STAT US CITY
P#
PNA ME COL WEIGOR
HT CITY
TOT QTY TOT QTY S#
SNA J# ME
JNAM E CITY
TOT QTY
M M
M M
M
QTY
QTY
SPJ (S#, P#, J#, QTY)
Foreign key S# References Supplier Foreign key P# References Part Foreign key J# References Project
Discussion and Summary of Mapping
for ER Model Constructs
MAPPING EER MODEL CONSTRUCTS TO
RELATIONS
Extending ER-to-relational mapping algorithm
Mapping Specialization/ Generalization
⚫ Option 8a – Any specialization
◦ Create multiple relations for all super/sub classes
⚫ Option 8b – subclasses that are total & disjoint
◦ Create multiple relations for all subclasses
⚫ Option 8c – subclasses are disjoint, with a single special attribute
◦ Create a single relation with a type attribute
⚫ Option 8d – subclasses are overlapping, with multiple attributes
◦ Create a single relation with multiple type attributes
17
EER Mapping Example
18
Option 8a – Any specialization
Create multiple relations for all super/sub classes
EER Mapping Example
19
Option 8b – subclasses that are total & disjoint
Create multiple relations for all subclasses
EER Mapping Example
20
Option 8c – subclasses are disjoint, with a single special attribute
Create a single relation with a type attribute
EER Mapping Example
21
Option 8d – subclasses are overlapping, with multiple attributes
Create a single relation with multiple type attributes
Mapping of Categories (Union Types)
⚫
Step 9: Mapping of Union Types (Categories)
◦ Defining superclasses have different keys
◦ Specify a new key attribute
• Surrogate key
Mapping Union types
23
Summary
⚫
Map conceptual schema design in the ER model to a relational database schema
◦ Algorithm for ER-to-relational mapping
◦ Illustrated by examples from the COMPANY database
⚫
Include additional steps in the algorithm
for mapping constructs from EER model
into relational model
RELATIONAL DATABASE
CONSTRAINTS
Chapter 3 – part II
25
Relational Integrity Constraints
⚫
Constraint rules are conditions on the DB that ensure integrity on our data for any
state of the DB:
◦ Single relation
● Domain constraints
● Entity integrity constraints
● Key constraints
◦ Two or more relations
● Referential integrity constraints
26
Domain Constraint
⚫ Every value in a tuple must be either:
◦ An element from the domain of its attribute OR
◦ null, if allowed for that attribute
⚫ For Example,
◦ A name may be defined in a domain of character strings of maximum length 25
◦ A USA phone # is a set of 10 digit phone numbers valid in the U.S.
27
Entity Integrity
⚫ The primary key attributes of a relation schema cannot have null values in any tuple of the
relation.
◦ Primary Key values are used to identify the individual tuples.
◦ t[PK] ≠ null for any tuple t in r(R)
◦ If PK has several attributes, null is not allowed in any of these attributes
⚫ Note: Other attributes of a relation may be
constrained to disallow null values, even though they are not members of the primary key.
28
Database COMPAN Y
29
Does this violate
domain or entity
constraints?
Key Constraint
⚫
Candidate keys:
◦ Specified for each relation schema
◦ Must be unique for every tuple in any relation instance of that schema
30
Referential Integrity
⚫
A constraint defined between two relations
⚫
Specify a relationship among tuples in both relations:
◦ The referencing relation and the referenced relation.
⚫
Pair-wise relationships are used to build relationships across three or more
relations
31
Foreign Key
⚫ Take two relations R1 and R2 where …
◦ FK is a subset of attributes in R1 that “mean” the same as a corresponding Primary Key (PK) in R2.
● I.e., a one-to-one mapping between these two attribute subsets
◦ FK has the same domain(s) of PK, but not necessarily same names!
◦ A value of FK in a tuple t1 of R1 either occurs
● As a value of PK of some tuple t2of R2 ,
● i.e., t
1[FK] = t
2[PK]
OR
● null
32
Referential Integrity Constraint
⚫ A tuple in the referencing relation R1 having the FK references the PK of the referenced relation R2
t1[FK] = t2[PK]
◦ “A tuple t1 in R1 is said to reference a tuple t2 in R2”
33
Referential Integrity Constraints for COMPANY database
34
Constraint Violations
⚫ Basic operations for changing the database:
◦ INSERT a new tuple in a relation
◦ DELETE an existing tuple from a relation
◦ MODIFY an attribute of an existing tuple
35
Possibility of Violations
⚫ INSERT may violate any of the constraints:
◦ Domain constraint:
● If one of the attribute values provided for the new tuple is not of the specified attribute domain
● Ex. An invalid phone number or zip code
◦ Entity integrity:
● If the primary key value is null in the new tuple
● SSN may not be null
◦ Key constraint:
● If the value of a key attribute in the new tuple already exists in another tuple in the relation
● Two employees with the same SSN
◦ Referential integrity:
● If a foreign key value in the new tuple references a primary key value that does not exist in the referenced relation
● Course enrollment for a student that doesn’t exist
36
Possibility of Violations
⚫ UPDATE may violate
◦ Domain constraint
● Updating an ordinary attribute (neither PK nor FK):
◦ Entity constraints
● NOT NULL constraint on an attribute being modified
◦ Key constraint:
● If the new value of a key attribute in the new tuple already exists in another tuple in the relation
◦ Referential Integrity Constraint, depending on the attribute being updated:
● Updating the primary key (PK):
● What about related FKs?
● Updating a foreign key (FK):
● May violate referential integrity
37
Possibility of Violations
⚫
DELETE may violate only referential integrity:
◦ If the primary key of the tuple being deleted is referenced from other tuples in the database referential integrity is violated.
38
Database COMPANY
39
What should
happen if we have a request to
DELETE
department 5 in the
DEPARTMENT relation?
•Integrity violations?
•Illegal state?
Can a Foreign Key have a Null Value?
⚫
May an employee not be assigned to a department?
⚫
May an employee not have a manager?
40
How are constraint violations minimized/ restricted?
Can restrictions be specific?
Deleting Target of a Foreign Key Reference
Restricted - Delete is restricted to case where there are no employees in the department.
Cascades - Delete cascades to remove employees also.
Nullifies - FK is set to null
I.e., Employees are no longer in any
department. Then department is deleted.
Delete department where there are still employees in the department.
41
Updating Target of a Foreign Key Reference.
Restricted - Update is restricted to case where there are no employees in the department.
Cascades - Update cascades to change the
department numbers of all employees in department D50.
Nullifies -FK,
I.e. DNO in EMP is set to null. Department DNO is updated.
Change department number (DNO) 50 to 75.
42
Inserting into Relation With the Foreign Key.
Restricted - Insert is restricted to case where there are already tuples for the corresponding department.
Cascades - If done interactively, user could be asked to enter tuple for department D10.
Nullifies - Cannot be done, since user has specified the department number.
Insert a new employee into department 10, but there is no tuple in the DEPT relation for
department 10.
43
Relational Integrity Constraints
⚫
Not included in Codd’s original definition of relations.
⚫
Not supported in initial commercial relational database systems.
⚫
Now supported in all major relational database systems.
44
Other Types of Constraints
⚫ Semantic Integrity Constraints:
◦ Based on application semantics and cannot be expressed by the model per se
◦ “The max. no. of hours per employee for all projects he or she works on is 56 hrs per week”
◦ “An employee’s salary must be in the range $30,000 to $100,000”
⚫ Express with a constraint specification language
⚫ SQL-99 allows triggers and ASSERTIONS to express for some of these
45
