Transcript Module 4: Part 1

Database Management
System
Module 4: Schema Refinement
Pitfalls in relational database
design

Relational database design requires that
we find a “good” collection of relation
schemas. A bad design may lead to

Repetition of Information.

Inability to represent certain information.
Repetition of Information
(Redundancy)

Storing the same information
redundantly, that is in more than one
place within a database, can lead to
several problems:


Redundant storage: Some information
stored repeatedly.
Update anomalies: If one copy of such
repeated data is updated inconsistency is
created unless all copies are similarly
updated.
Redundancy Problems


Insertion anomalies: It may not be
possible to store some informstion
unless some other infromation is stored
as well.
Deletion anomalies: it may not be
possible to delete some information
without losing some other information
as well.
Example of Redundancy
Problem


Consider a relation instance of Hourly_Emps
below
The key for Hourly_Emps is ssn. In addition,
suppose the hourly_wages attribute is
determined by the rating attribute.
Example of Redundancy
Problem



That is for given rating value, there is
oly one permissible hourly_wages value.
This IC is an example of a functional
dependency.
This lead to redundancy in the relation
Hourly_Emps as illustrated above.
Consequence

Some information is stored multiple
times. E.g. the rating value 8 with
hourly_wage 10 repeated three times.


Wasting space
Inconsistency; a hourly_wages in the first
tuple could be updated without making a
similar change in the second tuple (Update
Anomaly)
Example 2

Consider the relation schema:
Lending-schema = (branch-name, branch-city,
assets, customer-name, loan-number, amount)

.
Example 2

Redundancy:




Data for branch-name, branch-city, assets are
repeated for each loan that a branch makes
Wastes space
Complicates updating, introducing possibility of
inconsistency of assets value
Null values


Cannot store information about a branch if no
loans exist
Can use null values, but they are difficult to
handle
Problem Solution

Design Goals:

Avoid redundant data



Can be easily identified with Functional
Dependency
Ensure that relationships among attributes
are represented
Facilitate the checking of updates for
violation of database integrity constraints.
Problem Solution

There are various ways of solving
redundancy problem in your
design:
Use Decomposition
 Normalisation with the aid of
Functional Dependencies

Use Decomposition



Problems arising from redundancy can
be addressed by replacing a relation
with collection of smaller relations.
Each of the smaller relations contains a
subset of the attributes of the original
relation.
This process is called Decomposition of
the larger relation to smaller relation.
Decomposition
Example we can deal with the
redundancy in Hourly_Emps by
decomposing it into two relations.
Hourly_Emps2 (ssn, name, lot, rating,

hours,_worked)
Wages (rating, hourly_wages)
Decomposition

The Instances Hourly_Emps2 and
Wages are shown below:
Decomposition

Decompose the relation schema Lendingschema into:
Branch-schema = (branch-name, branchcity,assets)
Loan-info-schema = (customer-name, loannumber, branch-name, amount)
 All attributes of an original schema (R) must
appear in the decomposition (R1, R2):
R = R 1  R2
Lossless-Join Decomposition

Let R be a relation schema and let F be a set
of FDs over R. A decomposition of R into two
schemas with attribute set X and Y is said to
be lossless-join decomposition with
respect to F if for every instance r of R that
satisfies the dependencies in F,
x(r )  Y (r )  r
Example


Below are instances illustrating Lossy
Decomposition
All decomposition used to eliminate
redundancy must be lossless which it is not in
our case above.
Functional Dependence:
Revisited

We use functional dependencies to:

test relations to see if they are legal under
a given set of functional dependencies.

If a relation r is legal under a set F of
functional dependencies, we say that r satisfies
F.

specify constraints on the set of legal
relations

We say that F holds on R if all legal relations on
R satisfy the set of functional dependencies F.
Functional Dependence:
Revisited

Note: A specific instance of a relation
schema may satisfy a functional
dependency even if the functional
dependency does not hold on all legal
instances.

For example, a specific instance of Loanschema may, by chance, satisfy
loan-number  customer-name.
Closure of a Set of Functional
Dependencies

Given a set F set of functional
dependencies, there are certain other
functional dependencies that are
logically implied by F.



E.g. If A  B and B  C, then we can
infer that A  C
The set of all functional dependencies
logically implied by F is the closure of F.
We denote the closure of F by F+.
Closure of a Set of Functional
Dependencies

We can find all of F+ by applying Armstrong’s
Axioms:




if   , then   
(reflexivity)
if   , then     
(augmentation)
if   , and   , then    (transitivity)
These rules are


sound (generate only functional dependencies that
actually hold) and
complete (generate all functional dependencies
that hold).
Goal — Devise a Theory for
the Following


Decide whether a particular relation R is in
“good” form.
In the case that a relation R is not in “good”
form, decompose it into a set of relations {R1,
R2, ..., Rn} such that



each relation is in good form
the decomposition is a lossless-join decomposition
Our theory is based on:

functional dependencies
Normal Forms and
Normalisation



In a given relation schema, we need to
decide whether it is a good design or
whether it requires a decomposition
into smaller relations.
Such a decision must be guided by an
understanding of what problems from
current schema.
Normal forms are used for such
guidance.
Normal Forms

Normal forms based on FDs are:





First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)
Boyce-Codd normal form (BCNF)
The process of using normal forms to
improve database design by breaking
up relations into smaller relation is
called Normalization
Normal Forms

These forms have increasingly resrictive
requirements:



Every relation in BCNF is also in 3NF
Every relation in 3NF is also in 2NF
Every relation in 2NF is also in 1NF
Unnormalised Data-Set to First
normal form (1NF)


A relation is in first normal forms
(1NF ) if every field contains only
atomic values, that is, not lists or sets.
Consider the data set below
Unnormalised Data-Set to First
normal form (1NF)


In the dataset above suppose we
choose the data item moduleName as
the key of this data-set.
There are some cells in the table such
as studentNo, studentName, assGrade
and assType contains multiple values.

All repeat with respect to moduleName
First Normal Form



The attribute studentNo, studentName,
assGrade and assType are not
functionally dependent on our chosen
primary key moduleName.
The attributes staffNo and staffName
are dependent to key.
Form two tables; one for the
functionally dependent attributes and
other for non-functionally dependent.
1NF Tables
1NF to 2NF


Remove part key dependencies
Involves examining those tables that
have a compound key and for each
non-key data item in the table asking
the question: can the data-item be
uniquely identified by part of the
compound key?
1NF to 2NF


A relation is in second normal form if
and only if it is in first normal form and
every non-key attribute is fully
functionally dependent on the primary
key.
E.g table Assessments have three-part
compound key moduleName, studentNo
and assType
1NF to 2NF




We observed that ModuleName has no
influence on the studentName.
StudentNo alone determine
studentName.
Hence we have to break out the
determinant and dependent data-items
into their own table.
This lead to decomposition of the tables
as follows:
2NF Tables
2NF to 3NF



To move from second normal form to third
normal form we remove inter-data
dependencies.
To do this examine every table and ask of
each pair of non-key data-items; is the value
of data item A dependent on the value of
data-item B, or vice versa?
If the answer is yes, split off the relevant
data-items into a separate table
2NF to 3NF



A relation is in third normal form if and only if
it is in second normal form and every-key
attribute is non-transitively dependent on the
primary key.
The only place where this is relevant is in the
table called Modules.
Here staffNo determines staffName.
staffName hence is transitively dependent on
moduleName
2NF to 3NF


StaffNo is therefore asking to be a
primary key.
Hence, we create a separate table to be
called Lecturers with staffNo as the
primary key as illustrated in the below
tables:
3NF Tables