1
LOGICAL DATABASE DESIGN
AND THE RELATIONAL MODEL
Professor Tahir Sabtain Syed
Relation
2
Definition: A relation is a named, two-dimensional table of
data
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
Chapter 5
Key Fields
3
Keys are special fields that serve two main purposes:
Primary keys are unique identifiers of the relation in question.
Examples include employee numbers, social security numbers, etc.
This is how we can guarantee 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)
Chapter 5
Figure 5-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)
4
Integrity Constraints
5
Domain Constraints
Allowable values for an attribute. See Table 5-1
Entity Integrity
No primary key attribute may be null. All primary
key fields MUST have data
Referential integrity
Foreign key can contain only those values of
primary key
Chapter 5
Domain definitions enforce domain integrity constraints
6
Integrity Constraints
7
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
Chapter 5
Figure 5-5
Referential integrity constraints (Pine Valley Furniture)
Referential
integrity
constraints are
drawn via
arrows from
dependent to
parent table
8
Data Normalization
9
Primarily a tool to validate and improve a
logical design so that it satisfies certain
constraints that avoid unnecessary
duplication of data
The process of decomposing relations with
anomalies to produce smaller, well-
structured relations
Chapter 5
Well-Structured Relations
10
A relation that contains minimal data redundancy and
allows users to insert, delete, and update rows
without causing data inconsistencies
Goal is to avoid anomalies
Insertion Anomaly–adding new rows forces user to create
duplicate data
Deletion Anomaly–deleting rows may cause a loss of data that
would be needed for other future rows
Modification Anomaly–changing data in a row forces changes
to other rows because of duplication
General rule of thumb: A table should not pertain to
more than one entity type
Chapter 5
Example–Figure 5-2b
11
Question–Is this a relation? Answer–Yes: Unique rows and no
multivalued attributes
Question–What’s the primary key? Answer–Composite: Emp_ID, Course_Title
Chapter 5
Anomalies in this Table
12
Insertion–can’t enter a new employee without
having the employee take a class
Deletion–if we remove employee 140, we lose
information about the existence of a Tax Acc class
Modification–giving a salary increase to employee
100 forces us to update multiple records
Why do these anomalies exist?
Because there are two themes (entity types) in this
one relation. This results in data duplication and an
unnecessary dependency between the entities
Chapter 5
Functional Dependencies and Keys
13
Functional Dependency: The value of one
attribute (the determinant) determines the
value of another attribute
Candidate Key:
A unique identifier. One of the candidate keys will
become the primary key
E.g. perhaps there is both credit card number and SS#
in a table…in this case both are candidate keys
Each non-key field is functionally dependent on
every candidate key
Chapter 5
Figure 5.22 Steps in normalization
14
First Normal Form
No multivalued attributes
15
Every attribute value is atomic
Fig. 5-25 is not in 1 Normal Form
st
(multivalued attributes) it is not a
relation
Fig. 5-26 is in 1 Normal form
st
All relations are in 1 Normal Form
st
Chapter 5
Table with multivalued attributes, not in 1st normal form
16
Note: this is NOT a relation
Table with no multivalued attributes and unique rows, in 1st
normal form
17
Note: this is relation, but not a well-structured one
Anomalies in this Table
18
Insertion–if new product is ordered for order 1007 of
existing customer, customer data must be re-
entered, causing duplication
Deletion–if we delete the Dining Table from Order
1006, we lose information concerning this item's
finish and price
Update–changing the price of product ID 4 requires
update in several records
Why do these anomalies exist?
Because there are multiple themes (entity types) in
one relation. This results in duplication and an
unnecessary dependency between the entities
Chapter 5
Second Normal Form
1NF PLUS every non-key attribute is
19
fully functionally dependent on the
ENTIRE primary key
Every non-key attribute must be defined by the
entire key, not by only part of the key
No partial functional dependencies
Chapter 5
Figure 5-27 Functional dependency diagram for INVOICE
20
Order_ID Order_Date, Customer_ID, Customer_Name, Customer_Address
Customer_ID Customer_Name, Customer_Address
Product_ID Product_Description, Product_Finish, Unit_Price
Order_ID, Product_ID Order_Quantity
Therefore, NOT in 2nd Normal Form
Chapter 5
Figure 5-28 Removing partial dependencies
21
Getting it into
Second Normal
Form
Partial dependencies are removed, but there
are still transitive dependencies
Chapter 5
Third Normal Form
22
2NF PLUS no transitive dependencies
(functional dependencies on non-primary-key
attributes)
Note: This is called transitive, because the
primary key is a determinant for another
attribute, which in turn is a determinant for a third
Solution: Non-key determinant with transitive
dependencies go into a new table; non-key
determinant becomes primary key in the new
table and stays as foreign key in the old table
Chapter 5
Figure 5-28 Removing partial dependencies
23
Getting it into
Third Normal
Form
Transitive dependencies are removed
Chapter 5
