Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources
Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study
Go to the blog

Redundancy and Normalisation-Introduction to Database System-Lecture 12 Slides-Computer Science, Slides of Introduction to Database Management Systems

University of NottinghamIntroduction to Database Management Systems

Redundancy and Normalisation, 'Zeroth' and First Normal Form, 0NF to 1NF, Problems in 1NF, Functional Dependencies, FDs and Normalisation, Properties of FDs, FD Diagrams, Second Normal Form, Partial FDs and 2NF, Removing FDs, 1NF to 2NF, Third Normal Form, Transitive FDs and 3NF, 2NF to 3NF, Normalisation and Design

Typology: Slides

2011/2012

Uploaded on 02/12/2012

jeanette
jeanette 🇬🇧

3.7

(7)

238 documents

1 / 4

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
1
G52DBS – Database Systems
www.cs.nott.ac.uk/~smx/DBS
Redundancy and Normalisation
• Redundant data
• Can be determined
from other data in the
database
• Leads to various
problems
• INSERT an omalies
• UPDATE an omalies
• DELETE anomalies
•Normalisation
• Aims to reduce data
redundancy
• Redundancy is
expressed in terms of
dependencies
•Normal forms are
defined that do not
have certain types of
dependency
G52DBS – Database Systems
www.cs.nott.ac.uk/~smx/DBS
'Zeroth' and First Normal
Form
• In most definitions
of the relational
model
• All data values should
be atomic
• This means that table
entries should be
single values, not
repeating groups or
composite objects
• A relation is said to
be in first normal
form (1NF) if
• All data values are
atomic
• A 'relation' that is not
in 1NF is said to be in
'zeroth' normal form
(0NF), and is
unnormalised
G52DBS – Database Systems
www.cs.nott.ac.uk/~smx/DBS
0NF to 1NF
0NF
Module Dept Lecturer Texts
M1 D1 L1 T1, T2
M2 D1 L1 T1, T3
M3 D1 L2 T4
M4 D2 L3 T1, T5
M5 D2 L4 T6
1NF
Module Dept Lecturer Text
M1 D1 L1 T1
M1 D1 L1 T2
M2 D1 L1 T1
M2 D1 L1 T3
M3 D1 L2 T4
M4 D2 L3 T1
M4 D2 L3 T5
M5 D2 L4 T6
To convert a 0NF ‘relation’ to a 1NF relation,
split up any non-atomic values
G52DBS – Database Systems
www.cs.nott.ac.uk/~smx/DBS
Problems in 1NF
• INSERT anomalies
• Can't add a module
with no texts
• UPDATE anomalies
• To change lecturer for
M1, we have to
change two rows
• DELETE anomalies
• If we remove M3, we
remove L2 as well
1NF
Module Dept Lecturer Text
M1 D1 L1 T1
M1 D1 L1 T2
M2 D1 L1 T1
M2 D1 L1 T3
M3 D1 L2 T4
M4 D2 L3 T1
M4 D2 L3 T5
M5 D2 L4 T6
G52DBS – Database Systems
www.cs.nott.ac.uk/~smx/DBS
Functional Dependencies
• Redundancy can
often be described
as a functional
dependency
• A functional
dependency (FD) is a
link between two sets
of attributes in a
relation
• We can 'normalise' a
relation by removing
undesirable FDs
• A set of attributes,
A, functionally
determines another
set, B, if
• Whenever two rows of
the relation have the
same value for all
attributes in A then
they also have the
same value for all
attributes in B
•A →B
G52DBS – Database Systems
www.cs.nott.ac.uk/~smx/DBS
FDs and Normalisation
• We define a set of
'normal forms'
• Each normal form has
fewer FDs than the
last
• Since FDs represent
redundancy, each
normal form has less
redundancy than the
last
• Not all FDs cause a
problem
• We identify various
sorts of FD that do
• Each normal form
removes a type of FD
that is a problem
• We will also need a
way to remove FDs
pf3
pf4

Partial preview of the text

Download Redundancy and Normalisation-Introduction to Database System-Lecture 12 Slides-Computer Science and more Slides Introduction to Database Management Systems in PDF only on Docsity!

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Redundancy and Normalisation

  • Redundant data
    • Can be determined from other data in the database
    • Leads to various problems - INSERT anomalies - UPDATE anomalies - DELETE anomalies - Normalisation - Aims to reduce data redundancy - Redundancy is expressed in terms of dependencies - Normal forms are defined that do not have certain types of dependency

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

'Zeroth' and First Normal

Form

  • In most definitions

of the relational

model

  • All data values should be atomic
  • This means that table entries should be single values, not repeating groups or composite objects - A relation is said to

be in first normal

form (1NF) if

  • All data values are atomic
  • A 'relation' that is not in 1NF is said to be in 'zeroth' normal form (0NF), and is unnormalised

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

0NF to 1NF

0NF

Module Dept Lecturer Texts

M1 D1 L1 T1, T M2 D1 L1 T1, T M3 D1 L2 T M4 D2 L3 T1, T M5 D2 L4 T

1NF

Module Dept Lecturer Text M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

To convert a 0NF ‘relation’ to a 1NF relation,

split up any non-atomic values

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Problems in 1NF

  • INSERT anomalies
    • Can't add a module with no texts
  • UPDATE anomalies
    • To change lecturer for M1, we have to change two rows
  • DELETE anomalies
    • If we remove M3, we remove L2 as well

1NF

Module Dept Lecturer Text M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Functional Dependencies

  • Redundancy can

often be described

as a functional

dependency

  • A functional dependency (FD) is a link between two sets of attributes in a relation
  • We can 'normalise' a relation by removing undesirable FDs - A set of attributes,

A, functionally

determines another

set, B, if

  • Whenever two rows of the relation have the same value for all attributes in A then they also have the same value for all attributes in B
  • A → B G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

FDs and Normalisation

  • We define a set of

'normal forms'

  • Each normal form has fewer FDs than the last
  • Since FDs represent redundancy, each normal form has less redundancy than the last - Not all FDs cause a

problem

  • We identify various sorts of FD that do
  • Each normal form removes a type of FD that is a problem
  • We will also need a way to remove FDs

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Properties of FDs

  • In any relation
    • The primary key FDs any set of attributes in that relation K → X
  • K is the primary key, X is a set of attributes
    • Any set of attributes is FD on itself X → X - Rules for FDs - Reflexivity: If B is a subset of A then A → B - Augmentation: If A → B then A U C → B U C - Transitivity: If A → B and A → B then A → C

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

FD Example

  • The primary key is

{Module, Text} so

{Module, Text} → {Dept, Lecturer}

  • 'Trivial' FDs, eg: {Text, Dept} → {Text} {Module} → {Module} {Dept, Lecturer} → { }

1NF

Module Dept Lecturer Text M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

FD Example

  • Other FDs are
    • {Module} → {Lecturer}
    • {Module} → {Dept}
    • {Lecturer} → {Dept}
    • These are non-trivial and don't come from the primary key

1NF

Module Dept Lecturer Text M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

FD Diagrams

Module Dept Lecturer Text

{Module, Text} is the primary key, so we put a double box around them

{Module} → {Dept} and {Module} → {Lecturer}, so we have {Module} → {Dept, Lecturer}

{Lecturer} → {Dept}, so we have an arrow from Lecturer to Dept

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Partial FDs and 2NF

  • Partial FDs:
    • A FD, A → B is a partial FD, if some attribute of A can be removed and the FD still holds
    • Formally, there is some proper subset of A , C ⊂ A , such that C → B - Second normal form - A relation is in second normal form (2NF) if it is in 1NF and no non-primary-key attribute is partially dependent on the primary key

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Second Normal Form

  • 1NF is not in 2NF
    • We have the FD {Module, Text} → {Lecturer, Dept}
    • But also {Module} → {Lecturer, Dept}
    • And so Lecturer and Dept are partially dependent on the primary key

1NF

Module Dept Lecturer Text M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

Module Dept Lecturer Text

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

2NF to 3NF – Example

2NFa Module Dept Lecturer M1 D1 L M2 D1 L M3 D1 L M4 D2 L M5 D2 L

Module Dept Lecturer

3NFa Lecturer Dept L1 D L2 D L3 D L4 D

3NFb Module Lecturer M1 L M2 L M3 L M4 L M5 L

Lecturer Dept Module Lecturer

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Problems Resolved in 3NF

  • Problems in 2NF
    • INSERT – Can't add lecturers who teach no modules
    • UPDATE – To change the department for L we must alter two rows
    • DELETE – If we delete M3 we delete L2 as well - In 3NF all of these

are resolved

3NFa Lecturer Dept L1 D L2 D L3 D L4 D

3NFb Module Lecturer M1 L M2 L M3 L M4 L M5 L

G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS

Normalisation and Design

  • Normalisation is

related to DB design

  • A database should normally be in 3NF at least
  • If your design leads to a non-3NF DB, then you might want to revise it - When you find you

have a non-3NF DB

  • Identify the FDs that are causing a problem
  • Think if they will lead to any insert, update, or delete anomalies
  • Try to remove them