RECORD THE LECTURE
NORMALISATION
COMP2420/COMP6420 INTRODUCTION TO DATA MANAGEMENT, ANALYSIS AND SECURITY
Copyright By PowCoder代写 加微信 powcoder
WEEK 8 – LECTURE 2 Wednesday 27 April 2022
John (course convenor)
Live Coding by (Head Tutor) School of Computing
College of Engineering and Computer Science
Credit: (previous course convenor)
HOUSEKEEPING
Assignment 2
• To be released tomorrow morning
Outcomes you should be able to:
01 By the end of this lecture,
Describe what normalisation is
Explain the different types of anomalies caused by data redundancy
Explain the different forms of normalisation
Apply normalisation rules to convert tables to different normalisation forms
Database design approaches
• Entity-Relationship modelling
– A graphical technique for understanding and organizing data
• Normalisation
– Design technique to organize data so that there is no redundancy and all related data items are stored together.
Introduction
A poorly designed database causes
• Data redundancy
• Insert anomaly
• Update anomaly
• Delete anomaly
Poorly design relation
redundancy
• In the above example, course, lecturer and school names appear multiple times
• Could be avoided by creating multiple relations
Insert anomaly
• If student B. Jones enrols in a new course, but his name is entered incorrectly as B. Jons. We now have two names for u4924551
• We can’t add a course without students
Update anomaly
• The lecturer for a course changes, but we only update only one tuple
• We now have inconsistent lecturer information for that course
Delete anomaly
• If we delete all the students in a course, then we lose other information about the course (lecturer, school).
A better design
A better relation
Normalisation
• A specific process that can be followed to achieve good design
• Can also be used to check an existing design
• Consists of multiple stages or forms
Definition: Normalisation is a systematic approach of decomposing tables (in a database) to eliminate data redundancy (repetition) and avoid undesirable characteristics like insertion, update and deletion anomalies
Source: Studytonight
Multi-step process–> puts data into tabular form, removing duplicated data from the relation tables
Normalisation: Purpose
Source: Studytonight
• Eliminate redundant data
• Ensure logical data dependencies
Normal Forms
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce- Form (BCNF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)
First Normal Form (1NF): rules
• All attribute domains are atomic
• All attributes can only have a single value
• All attributes are uniquely determined by the primary key
Note: no repeating groups (class1, class2, etc)
Domain: set of original values used to model the data Atomic value: each value in the domain is indivisible as far as relational model is concerned
Example domains:
Marital status: married, single, divorced
First name: set of character strings representing names of people
Source: BCCampus
First Normal Form (1NF): Example
Un-normalised table
Source: Studytonight
What would you do to get table to 1NF?
First Normal Form (1NF): Example
Table in 1NF
Source: Studytonight
First Normal Form (1NF): Example 2
Un-normalised table
Adv-Room Class1 Class2 412 101-07 143-01 216 101-07 143-01
Source: Microsoft
Student# Advisor 1022 Jones 4123 3 159-02 179-04
How about getting this to 1NF?
First Normal Form (1NF): Example
Table in 1NF
Student# Advisor Adv-Room 1022 Jones 412
1022 Jones 412
1022 Jones 412
4123 Smith 216 4123 Smith 216 4123 Smith 216
Class# 101-07 143-01 159-02 101-07 143-01 179-04
Source: Microsoft
Second Normal Form (2NF)
• Itisin1NF
• All non-key attributes are uniquely determined by the whole primary key, not by a part of it.
Remove redundant data.
No partial redundancy.
Partial redundancy: When an attribute depends on only a part of the primary key and not the whole key.
Second Normal Form (2NF): Example
Student# Advisor 1022 Jones 1022 Jones 1022 Jones 4123 Smith 4123 Smith 4123 -Room Class# 412 101-07 412 143-01 412 159-02 216 101-07 216 143-01 216 179-04
Source: Microsoft
What would you do to get table to 2NF?
Second Normal Form (2NF):
Student# Students 1022 4123
2NF tables
Advisor Jones -Room 412
Registration
Student# 1022 1022 1022 4123 4123 4123
Class# 101-07 143-01 159-02 101-07 143-01 179-04
Source: Microsoft
Third Normal Form (3NF)
• It is in 2NF
• All non-key attributes depend on the
key[s] and nothing but the key[s]
• Eg, would updating one attribute independently cause issues (such as updating a person’s postcode independent of city)?
No transitive dependency.
Transitive dependency: when a non-key attribute depends on other non- key attributes rather than on the key attributes
Third Normal Form (3NF):
Student# Students 1022 4123
2NF tables
Advisor Jones -Room 412
Registration
Student# 1022 1022 1022 4123 4123 4123
Class# 101-07 143-01 159-02 101-07 143-01 179-04
Source: Microsoft
Third Normal Form (3NF):
3NF tables
Source: Microsoft
Student# 1022 4123
Advisor Jones Room Jones 412 Smith 216
Dept 42 42
Boyce- Form (BCNF)
• Itisin3NF
• All attributes depend on the key[s] and nothing but the key[s]
Boyce-Codd: Example
student_id
Is this table in BCNF?
Source: -Codd: Example
Student table
student_id
and so on…
and so on…
Professor table
Source: Studytonight
Higher normal forms
4NF and 5NF are not used much in practice
• What normalization is
• Purpose of normalization
• Different types of anomalies caused by redundancy of data
• Normalisation progression
Live coding
Mindika will do the rest of live coding session on databases/SQL next.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com