Lesson overview
Teach operational database design for transactional systems, including entities, relationships, functional dependency, 1NF, 2NF and 3NF.
Starter
Write down the user need, the data being stored or searched, and the decision the system has to support. Then predict which representation, index, query method or governance control will matter most in this lesson.
Learning objectives
- Explain OLTP and why transactional systems value integrity.
- Apply 1NF, 2NF and 3NF ideas to reduce update anomalies.
- Use functional dependency to justify normalisation decisions.
Learning outcomes
- Students can explain OLTP and why transactional systems value integrity.
- Students can apply 1NF, 2NF and 3NF ideas to reduce update anomalies.
- Students can use functional dependency to justify normalisation decisions.
Key vocabulary, acronyms and terminology
- OLTP
- Online Transaction Processing: systems handling day-to-day operational transactions.
- normalisation
- Structuring relational data to reduce redundancy and anomalies.
- 1NF
- First normal form: values are atomic and repeating groups are removed.
- 2NF
- Second normal form: non-key attributes depend on the whole key.
- 3NF
- Third normal form: non-key attributes do not depend on other non-key attributes.
- functional dependency
- A relationship where one attribute value determines another.
Detailed teaching notes
Core concept
OLTP systems prioritise correct, current operational data. Normalisation reduces duplication so inserts, updates and deletes do not create contradictory facts.
Representation choice
A single wide order table containing customer address, product name and order lines repeats facts. Normalisation separates customers, products, orders and order lines according to dependencies.
Method and reasoning
Identify entities, keys and functional dependencies. Remove repeating groups for 1NF, remove partial dependency for 2NF and remove transitive dependency for 3NF.
Risk and limitation
Normalisation is not an abstract ritual. The point is to prevent anomalies, such as changing a customer's address in one order row but not another.
Degree-level deep dive
Normalisation reduces update anomalies
First, second and third normal form are not decorative rules. They reduce duplicate facts and prevent insert, update and delete anomalies in systems where operational correctness matters.
Functional dependency is the reasoning tool
A functional dependency states that one attribute or set of attributes determines another. Normalisation decisions should be justified using these dependencies, not by intuition about table size.
What excellent work shows
A strong answer identifies keys, dependencies and repeating groups, then decomposes tables while preserving meaning. It should connect normalisation to OLTP reliability and also recognise when analytics may later need a different shape.
Concrete example to study
Unnormalised enrolment record
StudentModule(student_id, student_name, module_code, module_title, lecturer_name)
Problem: module_title depends on module_code, not on the whole enrolment row.
Decompose:
Student(student_id, student_name)
Module(module_code, module_title, lecturer_name)
Enrolment(student_id, module_code)Reasoning
- The original table repeats module facts for every enrolled student.
- Updating a module title in one row but not another creates an anomaly.
- The decomposed model stores each fact at the level where it belongs.
Reveal takeaway
Normalisation is a correctness argument based on dependencies, not just tidying tables, and it protects OLTP systems from repeated facts drifting out of sync.
Worked example
Scenario
An Orders spreadsheet repeats customer names and addresses on every order line.
Worked solution
- Identify order id, customer id and product id as separate concepts.
- Move customer facts to Customer(customer_id, name, address).
- Move product facts to Product(product_id, name, price).
- Keep transaction facts in Orders and OrderLine tables.
Reveal model result
The normalised design reduces update anomalies. Changing a customer address happens once in Customer rather than many times across order rows.
Applied retrieval task
Normalise a small booking spreadsheet.
Deliverables
- Mark repeating groups and non-atomic values.
- List functional dependencies.
- Propose tables up to 3NF.
Success checks
- The tables follow dependencies.
- The explanation names at least one anomaly avoided.
Common misconception
Normalisation does not mean splitting tables randomly. It follows dependencies so each fact is stored in the right place.
Quick checks
1. In this lesson, why does OLTP matter?
2. Which answer best shows degree-level understanding of data modelling and normalisation for oltp?
Digital exam practice
Example exam task
A sales spreadsheet stores order id, customer name, customer address, product id, product name, quantity and product price in every row. In your answer, define the relevant objects or data structures, use course-specific vocabulary, show the method rather than only the result, and finish with a decision about the storage or retrieval system.
Notation and technical toolkit
What a good answer is expected to show
A strong answer for Data Modelling and Normalisation for OLTP the answer should identify OLTP needs, functional dependency and normalisation to 1NF, 2NF and 3NF, then explain update anomalies. It should connect the formal or technical representation to the user's information need instead of listing terms without using them.
How to solve it
- Identify keys and functional dependencies such as product_id -> product_name.
- Remove repeating or non-atomic data for 1NF.
- Separate attributes depending only on part of a composite key for 2NF.
- Remove transitive dependencies for 3NF and name the anomaly avoided.
Model answer
Reveal model answer
For OLTP, the design should protect current operational facts. Product_id determines product_name and product_price, while customer_id should determine customer name and address. A normalised design separates Customer, Product, Order and OrderLine so that line quantity remains with the transaction, while product and customer facts are stored once. This reaches the intent of 1NF, 2NF and 3NF and avoids update anomalies such as changing a product name in some rows but not others.
Practise next
- Find one functional dependency in a university enrolment table.
- Explain why normalisation usually helps OLTP more than ad hoc reporting speed.
Self-marking criteria
- Defines the scenario objects, data forms or system components before solving.
- Uses the lesson vocabulary accurately and in context.
- Shows a clear method with enough working for a marker to follow.
- Connects the result back to retrieval, storage, analytics or governance.
- States a limitation, trade-off or quality risk rather than presenting the answer as absolute.
Extension
Compare a 3NF order schema with a reporting table built from it. Identify which facts are duplicated in the reporting table and explain why that duplication is risky in OLTP but useful for controlled analytics.
Study route
Save a short worked answer from this lesson using this pattern: define the need or data, choose the representation, show the method, state the result and interpret the implication for the system.
Next lesson: Transactions, Integrity and Operational Storage.