Logical Database Design and the

Relational Model

M ODERN DATABASE M ANAGEMENT

1 1 TH ED ITION
JEFFREY A. HOFFER, V. RAMESH,
HEIKKI TOPI

© 2013 PEARSON EDUCATION, INC. PUBLISHING AS PRENTICE HALL

2
Learning objectives
❑ Define terms
❑ List five properties of relations
❑ State two properties of candidate keys
❑ Transform E-R and EER diagrams to relations
❑ Create tables with entity and relational integrity constraints

3
Components of relational model
Data structure
◦ Tables (relations), rows, columns
Data manipulation
◦ Powerful SQL operations for retrieving and modifying data
Data integrity
◦ Mechanisms for implementing business rules that
maintain integrity of manipulated data

4
Relation
• A relation is a named, two-dimensional table of data.
• A table consists of rows (records) and columns (attribute or field).
• Requirements for a table to qualify as a relation:
➢ It must have a unique name.
➢ Every attribute value must be atomic (not multivalued, not composite).
➢ Every row must be unique (can’t have two rows with exactly the same values
for all their fields).
➢ Attributes (columns) in tables must have unique names.
➢ The order of the columns must be irrelevant.
➢ The order of the rows must be irrelevant.
NOTE: All relations are in 1st Normal form.

5
Correspondence with E-R Model
▪ Relations (tables) correspond with entity types and with many-to-many
relationship types.
▪ Rows correspond with entity instances and with many-to-many relationship
instances.
▪ Columns correspond with attributes.

relation (in relational database) is NOT the same as the

NOTE: The word
word relationship (in E-R model).

6
Key Fields
▪ Keys are special fields that serve two main purposes:
▪ Primary keys are unique identifiers of the relation. Examples include employee
numbers, social security numbers, etc. This guarantees that all rows are unique.
▪ Foreign keys are identifiers that enable a dependent relation (on the many side of a
relationship) to refer to its parent relation (on the one side of the relationship).

▪ Keys can be simple (a single field) or composite

(more than one field).
▪ Keys usually are used as indexes to speed up the
response to user queries.

7
Figure 4-3 Schema for four relations (Pine Valley Furniture Company)

Primary Key
Foreign Key
(implements 1:N relationship
between customer and order)

Combined, these are a composite

primary key (uniquely identifies the
order line)…individually they are
foreign keys (implement M:N
relationship between order and product)

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

8 8
Integrity Constraints
▪ Domain Constraints
▪ Allowable values for an attribute

▪ Entity Integrity
▪ No primary key attribute may be null. All primary key fields MUST have data.

9
Domain definitions enforce domain integrity constraints.

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

10 10
Integrity Constraints
Referential Integrity–rule states that any foreign key value (on the relation of the many
side) MUST match a primary key value in the relation of the one side. (Or the foreign
key can be null)
◦ For example: Delete Rules
◦ Restrict–don’t allow delete of “parent” side if related rows exist in “dependent” side
◦ Cascade–automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted
◦ Set-to-Null–set the foreign key in the dependent side to null if deleting from the parent side → not allowed for weak
entities

11
Figure 4-5 - Referential integrity constraints (Pine Valley Furniture)

Referential
integrity
constraints are
drawn via arrows
from dependent to
parent table

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

12 12
 Figure 4-6 SQL table definitions

Referential integrity
constraints are
implemented with
foreign key to primary
key references.

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

13
Transforming EER Diagrams into Relations
Mapping Regular Entities to Relations
❑ Simple attributes: E-R attributes map
directly onto the relation
❑ Composite attributes: Use only their
simple, component attributes
❑ Multivalued Attribute: Becomes a separate
relation with a foreign key taken from the
superior entity

14
Figure 4-8 Mapping a regular entity

(a) CUSTOMER
entity type with
simple
attributes

(b) CUSTOMER relation

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

15
Figure 4-9 Mapping a composite attribute
(a) CUSTOMER
entity type with
composite
attribute

(b) CUSTOMER relation with address detail

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

16 16
 Figure 4-10 Mapping an entity with a multivalued attribute

(a)

Multivalued attribute becomes a separate relation with foreign key

(b)

One–to–many relationship between original entity and new relation

17
Transforming EER Diagrams into Relations (cont.)
Mapping Weak Entities
▪Becomes a separate relation with a foreign
key taken from the superior entity
▪Primary key composed of:
✓Partial identifier of weak entity
✓Primary key of identifying relation (strong
entity)

18
Figure 4-11 Example of mapping a weak entity
a) Weak entity DEPENDENT

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

19 19
Figure 4-11 Example of mapping a weak entity (cont.)

b) Relations resulting from weak entity

NOTE: the domain constraint
for the foreign key should
NOT allow null value if
DEPENDENT is a weak
entity
Foreign key

Composite primary key

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

20 20
Transforming EER Diagrams into Relations (cont.)

Mapping Binary Relationships

➢ One-to-Many–Primary key on the one side
becomes a foreign key on the many side
➢ Many-to-Many–Create a new relation with the
primary keys of the two entities as its primary key
➢ One-to-One–Primary key on mandatory side
becomes a foreign key on optional side

21
Figure 4-12 Example of mapping a 1:M relationship
a) Relationship between customers and orders

Note the mandatory one

b) Mapping the relationship

Again, no null value in the

foreign key…this is because
of the mandatory minimum
cardinality.
Foreign key

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

22 22
Figure 4-13 Example of mapping an M:N relationship

a) Completes relationship (M:N)

The Completes relationship will need to become a separate relation.

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

23 23
Figure 4-13 Example of mapping an M:N relationship (cont.)

b) Three resulting relations

Composite primary key

Foreign key
new
Foreign key
intersection
relation

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

24 24
Figure 4-14 Example of mapping a binary 1:1 relationship
a) In charge relationship (1:1)

Often in 1:1 relationships, one direction is optional

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

25 25
Figure 4-14 Example of mapping a binary 1:1 relationship (cont.)
b) Resulting relations

Foreign key goes in the relation on the optional side,

matching the primary key on the mandatory side

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

26 26
Transforming EER Diagrams into Relations (cont.)
Mapping Associative Entities
▪ Identifier Not Assigned
▪ Default primary key for the association relation is
composed of the primary keys of the two entities
(as in M:N relationship)
▪ Identifier Assigned
▪ It is natural and familiar to end-users
▪Default identifier may not be unique

27
Figure 4-15 Example of mapping an associative entity
a) An associative entity

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

28 28
Figure 4-15 Example of mapping an associative entity (cont.)
b) Three resulting relations

Composite primary key formed from the two foreign keys

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

29 29
Figure 4-16 Example of mapping an associative entity with
an identifier

a) SHIPMENT associative entity

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

30 30
Figure 4-16 Example of mapping an associative entity with
an identifier (cont.)
b) Three resulting relations

Primary key differs from foreign keys

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

31 31
Transforming EER Diagrams into Relations (cont.)
Mapping Unary Relationships
▪One-to-Many–Recursive foreign key in the same
relation
▪Many-to-Many–Two relations:
▪ One for the entity type
▪ One for an associative relation in which the primary
key has two attributes, both taken from the primary
key of the entity

32
 Figure 4-17 Mapping a unary 1:N relationship

(a) EMPLOYEE
entity with unary
relationship

(b)
EMPLOYEE
relation with
recursive
foreign key

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

33 33
Figure 4-18 Mapping a unary M:N relationship

(a) Bill-of-materials
relationships (M:N)

(b) ITEM and

COMPONENT
relations

34
Transforming EER Diagrams into Relations (cont.)
Mapping Ternary (and n-ary) Relationships
➢ One relation for each entity and one for the
associative entity
➢ Associative entity has foreign keys to each
entity in the relationship

35
 Figure 4-19 Mapping a ternary relationship
a) PATIENT TREATMENT Ternary relationship with associative entity

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

36 36
 Figure 4-19 Mapping a ternary relationship (cont.)
b) Mapping the ternary relationship PATIENT TREATMENT

Remember This is why But this makes a It would be

that the treatment date very better to create a
primary key and time are cumbersome surrogate key
MUST be included in the key… like Treatment#.
unique. composite
primary key.

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

37 37
Transforming EER Diagrams into Relations (cont.)
Mapping Supertype/Subtype Relationships
➢ One relation for supertype and for each subtype
➢ Supertype attributes (including identifier and subtype
discriminator) go into supertype relation
➢ Subtype attributes go into each subtype; primary key of
supertype relation also becomes primary key of subtype
relation
➢ 1:1 relationship established between supertype and each
subtype, with supertype as primary table

38
 Figure 4-20 Supertype/subtype relationships

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

39 39
Figure 4-21 - Mapping supertype/subtype relationships to relations

These are implemented as one-to-one

relationships.

Chapter 4 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

40 40