From Messy Tables to 1NF
From Messy Tables to 1NF
Open with the idea that a database should store data so it stays accurate when values are added, changed or deleted. This sets up normalisation as a practical design tool rather than a rule to memorise.
Learning goal
I can explain why database normalisation matters and apply first normal form (1NF) to simple tables.
Deze slide heeft geen instructies
What words do you associate with a well-structured database table?
Use this to surface prior ideas such as order, accuracy, rows, columns, keys, duplication or errors. Briefly connect student responses to the lesson focus on normalisation and 1NF.
Why normalisation exists
In a relational database, table design affects data quality.
Poorly structured tables often repeat the same facts many times.
Repeated facts increase the risk of inconsistency.
Normalisation is a method for organising tables more cleanly.
Its aim is to reduce redundancy and avoid update problems.
Link this to everyday intuition: if the same fact appears in several places, changing it everywhere becomes error-prone.
Redundancy in a table
Redundancy means storing the same information more than once.
Example: a customer's name appears in many order rows.
Repetition is not always wrong, but unnecessary repetition is risky.
The more copies a fact has, the easier it is for them to disagree.
You can briefly contrast 'same value appears naturally in multiple records' with 'same fact copied because the table is badly structured'.
Update anomalies
Poor table structure can create update anomalies:
Update anomaly: one fact must be changed in several rows
Insert anomaly: a fact cannot be added without unrelated data
Delete anomaly: deleting one row accidentally removes another fact
Normalisation helps prevent these problems.
Keep the examples simple and relational: changing one supplier address in many rows, being unable to add a new course before a student enrols, or deleting the last order and losing customer details.
Normalisation as a design principle
A normalised design aims to:
store each fact in an appropriate place
make each row represent one record
make updates predictable and consistent
support reliable searching, sorting and joining
First normal form is the starting point of this process.
Deze slide heeft geen instructies
What makes a table well structured?
A table should model one clear type of record.
Each column should hold one kind of value.
Each row should describe one instance of that record.
Rows should be identifiable, usually by a primary key.
The structure should make errors less likely, not easier.
Emphasise that 'well structured' is about how data is organised, not about how neat it looks on screen.
What is the main purpose of normalisation?
Reducing redundancy
Adding more columns
Hiding duplicate rows
Speeding up typing
The nearby slides stress reducing redundancy and avoiding update, insert and delete anomalies. Distractors reflect superficial but incorrect ideas about table design.
Complete the summary
In a relational database, poor table design can create redundancy and update problems. Normalisation aims to organise tables more cleanly so that each row represents one record and facts are stored in an appropriate place.
This echoes slides 2 to 6: redundancy, cleaner organisation and one row per record are the load-bearing ideas.
Why is 'one row, one record' important?
Expected elements: a row should represent one record only; mixing several items in one row makes meaning unclear; it complicates querying and updating; a stable identity for each row supports reliable database operations.
One row, one record
A row should represent one record only.
If one row mixes several items, the meaning becomes unclear.
Queries and updates work best when each row has a single, stable identity.
Useful contrast: one student per row is clear; one row containing several students or several courses is not.
Columns should hold one value type
Each field should have a clear purpose.
A column such as `PhoneNumbers` or `Modules` often hides several values together.
A well-structured table makes each value easy to access directly.
This prepares the table for first normal form.
Deze slide heeft geen instructies
Primary keys matter
A primary key identifies each row uniquely.
It helps distinguish one record from another.
Without a unique identifier, updates may affect the wrong row.
A primary key is one requirement of a table in 1NF.
You do not need to go deep into candidate keys here; the lesson only needs the idea of a dependable unique identifier.
Defining first normal form
A table is in first normal form (1NF) when:
each field contains a single, atomic value
there are no repeating groups
there are no multi-valued attributes
each row represents one record
each row can be identified by a primary key
Present 1NF as the first checkpoint for table quality: simple values, clear rows, clear identity.
Atomic values
An atomic value is a single value in one field.
In 1NF, a field should not contain a list such as `red, blue, green` or `0123 / 0456`. If a field stores several separate values together, the table is not in 1NF.
Students may ask whether a full name is atomic. Keep it practical: atomic means 'treated as one value for this design'. The lesson focus is on obvious lists and grouped values.
Repeating groups
A repeating group appears when the same kind of data repeats within one row.
Examples: `Module1`, `Module2`, `Module3` or several phone number columns.
This breaks the idea that one column should hold one consistent attribute.
Explain that repeating groups can appear either as multiple similar columns or as lists inside one field; both signal weak structure.
Multi-valued attributes
A multi-valued attribute stores more than one value for the same attribute in one row.
Example: one student record with several email addresses in the same field.
In 1NF, those values must not be bundled together inside a single field.
This is close to atomicity, but keep the distinction: the problem is not just formatting, but the fact that one attribute holds several values for one record.
What 1NF improves
1NF makes data easier to:
search with precise conditions
sort and filter consistently
update without hidden duplicates in one field
process with relational operations
It does not solve every design issue, but it removes common structural problems.
Good moment to note that 1NF is a foundation, not the end of normalisation.
Which field breaks the idea of an atomic value?
StudentID
OrderDate
PhoneNumbers
Surname
PhoneNumbers suggests several values bundled in one field, matching the warning on slide 8 and the 1NF definition on slides 10 to 13.
Complete the 1NF definition
A table is in 1NF when each field contains a single, atomic value, there are no repeating groups and each row can be identified by a primary key.
All four blanks come directly from the defining criteria on slides 10 to 12 and 9.
Which 1NF issue do students usually miss first?
Lists in one field
Repeating columns
Missing primary key
Several items per row
Deze slide heeft geen instructies
Spotting a 1NF violation
Typical signs that a table is not in 1NF:
a field contains a list of values
columns repeat the same attribute pattern
one row describes several items at once
rows cannot be uniquely identified
values are grouped in one field for convenience
Frame this as a checklist students can apply quickly before attempting a conversion.
Example: list in one field
`StudentID | Name | Modules`
`102 | Amina | SQL, Python, Networks`
The `Modules` field contains several values, so the table is not in 1NF.
Read the row aloud and ask students to notice that one student record contains multiple module values in one field.
Converting the example to 1NF
To convert the table:
keep one module value per row
repeat the student identifier where needed
let each row represent one student–module record
`102 | Amina | SQL`
`102 | Amina | Python`
`102 | Amina | Networks`
Point out that repeating the student data across rows is acceptable here because the immediate goal is 1NF. Later normal forms deal with further refinement.
Example: repeating columns
`OrderID | Customer | Item1 | Item2 | Item3`
This structure is not in 1NF because:
the same attribute repeats across columns
some rows may leave item columns empty
the number of items is limited by the design
This is a strong example of why repeating groups are both inflexible and hard to query cleanly.
Converting repeating columns
A 1NF version stores one item per row:
`OrderID | Customer | Item`
`501 | Noor | Keyboard`
`501 | Noor | Mouse`
`501 | Noor | Headset`
Now each field is atomic and the repeating group is removed.
If useful, mention that the row identity may then depend on more than one column, but keep the focus on 1NF compliance rather than key design theory.
A simple method for applying 1NF
When checking a table for 1NF:
identify what one row is meant to represent
check whether each field holds one atomic value
look for lists or repeating columns
ensure rows can be identified by a primary key
split repeated values into separate rows or tables where needed
This slide consolidates the lesson into a procedure students can use on straightforward exam-style examples.
How should `SQL, Python, Networks` be converted for 1NF?
Keep one row
Add more module columns
Split into rows
Merge all fields
The correct move is to store one module per row, as shown on slides 16 and 17. A common misconception is to add Module1, Module2, Module3 columns, which still violates 1NF.
Complete the method
To apply 1NF, first identify what one row is meant to represent. Then check whether each field holds one atomic value, look for lists or repeating columns, and ensure rows can be identified by a primary key.
This is a direct application of the step-by-step method on slide 20, using the exact procedural language students need.
Why is `Item1 | Item2 | Item3` not in 1NF?
Expected elements: Item1, Item2 and Item3 repeat the same attribute across columns; the design limits the number of items; empty cells may appear; one item per row removes the repeating group and restores atomic values.
What idea about 1NF became clearest today?
Invite short reflective responses about understanding, examples, or misconceptions corrected during the lesson.
What part of applying 1NF still feels hardest?
Use answers to identify where students still need support: atomic values, repeating groups, row meaning, or primary keys.
Glossary
database normalisation - organising tables to reduce redundancy and prevent structural problems
redundancy - unnecessary repetition of the same fact in a database
update anomaly - a problem caused when a fact must be changed in multiple places
insert anomaly - a problem where adding one fact requires unrelated data
delete anomaly - a problem where deleting a row removes useful information as well
well-structured table - a table whose rows, columns and identifiers are organised clearly
primary key - a field, or combination of fields, that uniquely identifies each row
Deze slide heeft geen instructies
Glossary
first normal form (1NF) - a table structure in which each field is atomic, repeating groups are removed and each row represents one record
atomic value - a single value stored in one field
repeating group - repeated columns or grouped values representing the same attribute
multi-valued attribute - an attribute that stores several values for one record
record - one complete row of data about a single item in the table
field - a column that stores one type of value
Deze slide heeft geen instructies