Chapter 1: Normalisation
Terminology Description Alternative names
Rational Database Database consists of many tables
Table Many records Entity, Relation
Record Many elds Row, Entity instance
Field Store single data items in it Attribute, Column, Property
Rational Database
Databases consist of many tables that contain data, that is related in some way and are managed
by the Database Management Systems (DBMS).
Relation- a table represented as many records/rows/entity instance.
Primary Key Field: a eld in a table that uniquely identi es each record in the rational database
table/ A minimal candidate key that uniquely identi es a record.
- no data values in the primary key eld(s) are repeated.
PK REQUIREMENTS:
• A primary cannot cannot have no value (null)
• if the primary keys are a composite key- all elds contributing to the primary key must have a
value.
CREATING PRIMARY KEYS:
• Use auto-numbered eld to make each record unique.
• Combine elds to create a combination of values that will produce a unique key.
NAMING KEYS:
Example: tblStudents
Primary key: studentID
Super keys- any number of elds in a table which uniquely identify a record/row.
Example: (studentID, Name, Surname)
Candidate key- a speci c type of eld(s) in a a rational database that can identify each unique
record independent of any other record.
Example: (studentID, numID, email, phone_number)
- minimal super key with no repeated attributes.
- Usually choose primary key from candidate key.
- no redundant elds.
Composite/ Compound/ Concatenated Keys- the combination of 2 or more elds to form a
primary key.
Example: (clientID, carID: client might own more more than one car or more than one of the same
car)
Foreign Key- a eld in a table that is a primary key in another table in the database. Links tables
together if they are not linked via the primary key.
fi fi fi fi fifi fi fi fi fi fi fi fi
, Normalisation
The process of organising data in a database by dividing the set of data into tables using a
de ned set of rules to prevent a poor data structure.
It is important that data is normalised to minimise redundancy and to ensure only related data is
stored in each table. In a normalised database all elds should have a single value.
AVOIDS/PREVENTS:
• Repeating groups
• Data redundancy
• Anomalies (update, insert, delete)
• Any issues occurring in the database
1. REPEATING GROUPS (TYPE 1 & TYPE 2)
A series of elds that repeat multiple times throughout a rational database table.
2. DATA REDUNDANCY
A data organisation issue that allows the unnecessary duplication of data within a relational
database. (Fields that are repeated in 2 or more tables).
A change to redundant data requires a change to multiple elds in the database.
Takes up more storage over time, as more data is added.
• Expanding/Adding primary key(s) produces data redundancy.
• When repeating groups are removed: results in data redundancy.
Example: studentID in tblStundents and in tblSubject
3. ANOMALIES
Problems that occur in a poorly-planned database where all the data is stored in one table ( at
database).
Removed by normalisation by splitting/joining tables.
Too much data redundancy in database’s information.
Update: when data needs to be updated in more than 1 place.
Delete: when deleting causes unnecessary loss of data.
Insert: when records are added that do not satisfy the primary key requirements.
Process of Normalisation
3 Forms Characteristics
1st Normal Form Eliminate repeating groups
Choose a primary key- introduces data redundancy
However, 1NF doesn’t eliminate data redundancy, and doesn’t generate
new relations.
2nd Normal Form Relation in 1NF
No partial dependancies
3rd Normal Form Reaction in 2NF
No transitive dependencies
fi
fi fi fi fl
Terminology Description Alternative names
Rational Database Database consists of many tables
Table Many records Entity, Relation
Record Many elds Row, Entity instance
Field Store single data items in it Attribute, Column, Property
Rational Database
Databases consist of many tables that contain data, that is related in some way and are managed
by the Database Management Systems (DBMS).
Relation- a table represented as many records/rows/entity instance.
Primary Key Field: a eld in a table that uniquely identi es each record in the rational database
table/ A minimal candidate key that uniquely identi es a record.
- no data values in the primary key eld(s) are repeated.
PK REQUIREMENTS:
• A primary cannot cannot have no value (null)
• if the primary keys are a composite key- all elds contributing to the primary key must have a
value.
CREATING PRIMARY KEYS:
• Use auto-numbered eld to make each record unique.
• Combine elds to create a combination of values that will produce a unique key.
NAMING KEYS:
Example: tblStudents
Primary key: studentID
Super keys- any number of elds in a table which uniquely identify a record/row.
Example: (studentID, Name, Surname)
Candidate key- a speci c type of eld(s) in a a rational database that can identify each unique
record independent of any other record.
Example: (studentID, numID, email, phone_number)
- minimal super key with no repeated attributes.
- Usually choose primary key from candidate key.
- no redundant elds.
Composite/ Compound/ Concatenated Keys- the combination of 2 or more elds to form a
primary key.
Example: (clientID, carID: client might own more more than one car or more than one of the same
car)
Foreign Key- a eld in a table that is a primary key in another table in the database. Links tables
together if they are not linked via the primary key.
fi fi fi fi fifi fi fi fi fi fi fi fi
, Normalisation
The process of organising data in a database by dividing the set of data into tables using a
de ned set of rules to prevent a poor data structure.
It is important that data is normalised to minimise redundancy and to ensure only related data is
stored in each table. In a normalised database all elds should have a single value.
AVOIDS/PREVENTS:
• Repeating groups
• Data redundancy
• Anomalies (update, insert, delete)
• Any issues occurring in the database
1. REPEATING GROUPS (TYPE 1 & TYPE 2)
A series of elds that repeat multiple times throughout a rational database table.
2. DATA REDUNDANCY
A data organisation issue that allows the unnecessary duplication of data within a relational
database. (Fields that are repeated in 2 or more tables).
A change to redundant data requires a change to multiple elds in the database.
Takes up more storage over time, as more data is added.
• Expanding/Adding primary key(s) produces data redundancy.
• When repeating groups are removed: results in data redundancy.
Example: studentID in tblStundents and in tblSubject
3. ANOMALIES
Problems that occur in a poorly-planned database where all the data is stored in one table ( at
database).
Removed by normalisation by splitting/joining tables.
Too much data redundancy in database’s information.
Update: when data needs to be updated in more than 1 place.
Delete: when deleting causes unnecessary loss of data.
Insert: when records are added that do not satisfy the primary key requirements.
Process of Normalisation
3 Forms Characteristics
1st Normal Form Eliminate repeating groups
Choose a primary key- introduces data redundancy
However, 1NF doesn’t eliminate data redundancy, and doesn’t generate
new relations.
2nd Normal Form Relation in 1NF
No partial dependancies
3rd Normal Form Reaction in 2NF
No transitive dependencies
fi
fi fi fi fl
