Normalize data to third normal form (3NF)
Topic 10 – Database & File Concepts (AS Level)
Scope of this lesson
- 10.1 Creating a database – data types, field sizes, key fields, referential integrity, flat vs relational files, file‑access methods, DBMS categories, MIS overview.
- 10.2 Entity‑Relationship Diagrams (ERDs) – conceptual, logical, physical levels.
- 10.3 Normalisation to Third Normal Form (3NF) – UNF → 1NF → 2NF → 3NF, functional dependencies, advantages/disadvantages, when to de‑normalise.
- 10.4 Data dictionary – required columns and examples.
Cross‑Reference to Cambridge AS Syllabus (9626)
| Notes Section | Syllabus Bullet(s) |
|---|---|
| Creating a Database – Data types & field sizes | 10.1.1 Data types and field sizes |
| Creating a Database – Key fields (PK, FK, composite, compound) | 10.1.2 Primary, foreign, composite and compound keys |
| Creating a Database – Referential integrity | 10.1.3 Referential integrity |
| Creating a Database – Flat vs relational files | 10.1.4 Flat‑file and relational‑file concepts |
| Creating a Database – File‑access methods | 10.1.5 Indexed‑sequential, direct and random access files |
| Creating a Database – DBMS categories & MIS | 10.1.6 Types of DBMS; role of MIS |
| ERDs – Conceptual, logical, physical | 10.2.1 Levels of ER diagrams |
| Functional Dependencies | 10.3.1 Functional dependency notation and analysis |
| Normalisation – Definitions & check‑lists | 10.3.2 Definitions of UNF, 1NF, 2NF, 3NF |
| Normalisation – Step‑by‑step procedure | 10.3.3 Process of normalising to 3NF |
| Normalisation – Advantages / disadvantages | 10.3.4 Benefits and limits of normalisation; when to de‑normalise |
| Data Dictionary – Required columns | 10.4.1 Data‑dictionary structure and contents |
1. Creating a Database
1.1 Data Types & Field Sizes
- Numeric:
INT,SMALLINT,BIGINT,DECIMAL(p,s),FLOAT - Character:
CHAR(n)(fixed length),VARCHAR(n)(variable length) - Date/Time:
DATE,TIME,DATETIME,TIMESTAMP - Boolean:
BIT/BOOLEAN - Binary:
BLOB,VARBINARY
Choose the smallest type that can safely hold the required data – this minimises storage and improves performance.
1.2 Key Fields
- Primary Key (PK) – uniquely identifies each record; never NULL.
- Foreign Key (FK) – references a PK in another table; enforces referential integrity.
- Composite Key – PK formed from two or more attributes (e.g.,
(StudentID, CourseCode)). - Compound Key – a key that may combine single‑field and multi‑field parts (used rarely in the Cambridge syllabus).
1.3 Referential Integrity
The DBMS must prevent:
- Insertion of a FK value that does not exist in the referenced PK.
- Deletion or update of a PK value while related FK rows still exist (unless cascade or set null rules are defined).
1.4 Flat vs. Relational Files
- Flat file: a single table (often a CSV or text file). May contain repeating groups, leading to redundancy and update anomalies.
- Relational file: a collection of related tables linked by PK/FK pairs. Eliminates most redundancy and supports integrity checks.
1.5 File‑Access Methods
| Method | How it works | Typical use |
|---|---|---|
| Sequential Access | Records are read/written in order from the start of the file. | Batch processing, log files. |
| Indexed‑Sequential Access | An index (often a B‑tree) points to the location of each record, allowing fast search while preserving sequential order. | Most DBMS tables, file‑based systems. |
| Direct (Random) Access | Records can be retrieved directly via a physical address or hash value. | High‑performance transaction systems. |
1.6 Types of DBMS & MIS Overview
- File‑based DBMS – stores data in flat or indexed files; limited multi‑user support.
- Relational DBMS (RDBMS) – uses tables, PK/FK, SQL; supports ACID properties.
- Object‑oriented DBMS – stores objects; useful for CAD/CAM, multimedia.
- Distributed DBMS – data spread across multiple sites; provides transparency.
A Management Information System (MIS) extracts, processes and presents data from one or more databases to support decision‑making.
2. Entity‑Relationship Diagrams (ERDs)
- Conceptual ERD – only entities and relationships; no attributes, PK/FK symbols.
- Logical ERD – adds attributes, underlines primary keys, marks foreign keys (FK).
- Physical ERD – includes data types, field sizes, indexes, and any integrity rules.
3. Functional Dependencies (FDs)
An FD X → Y means that whenever two rows have the same value(s) for the attribute set X, they must also have the same value(s) for every attribute in Y.
| FD | Interpretation |
|---|---|
| StudentID → StudentName, DateOfBirth | Student name and DOB are determined solely by the student identifier. |
| CourseCode → CourseTitle, LecturerID | Each course code uniquely determines its title and the lecturer teaching it. |
| (StudentID, CourseCode) → Semester, Grade | The combination of student and course determines the semester taken and the grade obtained. |
| LecturerID → LecturerName, Department | Lecturer details depend only on the lecturer identifier. |
Why FDs matter
- They reveal partial dependencies (an attribute depends on part of a composite key).
- They expose transitive dependencies** (non‑key attribute depends on another non‑key attribute).
- Identifying all FDs is the first step in systematic normalisation.
4. Normalisation to Third Normal Form (3NF)
4.1 Definitions & Check‑lists
| Normal Form | Definition | Checklist |
|---|---|---|
| UNF (Unnormalised Form) | Data stored without any logical structure; may contain repeating groups or multi‑valued fields. | Identify repeating groups → move to 1NF. |
| 1NF | All attribute values are atomic; each field holds a single, indivisible value; no repeating groups. |
|
| 2NF | Relation is in 1NF and every non‑key attribute is fully functionally dependent on the whole of every candidate key (no partial dependencies). |
|
| 3NF | Relation is in 2NF and has no transitive dependencies (no non‑key attribute depends on another non‑key attribute). |
|
4.2 Advantages of Normalising to 3NF
- Eliminates most update, insert and delete anomalies.
- Reduces data redundancy → saves storage and improves consistency.
- Improves data integrity; queries become more predictable.
- Facilitates easier maintenance and future extensions.
4.3 Disadvantages / When to De‑Normalise
- More tables mean more
JOINoperations, which can affect performance on very large data sets. - In data‑warehousing or reporting systems, controlled redundancy may be introduced deliberately for speed.
- Real‑time systems sometimes keep a denormalised copy for rapid lookup.
4.4 Step‑by‑Step Procedure to Reach 3NF
- Verify the relation is in 1NF (all values atomic, no repeating groups).
- List **all candidate keys** and **all functional dependencies**.
- Identify and remove **partial dependencies** → decompose into 2NF tables.
- Examine each 2NF table for **transitive dependencies** → decompose further into 3NF tables.
- Assign primary keys to the new tables, add foreign keys to preserve relationships, and enforce referential integrity.
5. Worked Example – Student‑Course Registration System
5.1 Unnormalised Form (UNF)
| StudentID | StudentName | CourseCode | CourseTitle | Lecturer | Semester | Grade |
|---|---|---|---|---|---|---|
| 1001 | Alice Brown | C101 | Database Systems | Dr. Smith | Fall 2024 | A |
| 1001 | Alice Brown | C102 | Web Development | Prof. Lee | Fall 2024 | B+ |
| 1002 | Bob Carter | C101 | Database Systems | Dr. Smith | Fall 2024 | B |
5.2 1NF Check
All fields contain single, indivisible values; there are no repeating groups. The table already satisfies 1NF.
5.3 Functional Dependencies (complete set)
| FD | Explanation |
|---|---|
| StudentID → StudentName | Student name is determined by the student identifier. |
| CourseCode → CourseTitle, Lecturer | Each course code uniquely defines its title and the lecturer teaching it. |
| (StudentID, CourseCode) → Semester, Grade | The combination of student and course determines the semester and the grade. |
5.4 Decompose to 2NF (remove partial dependencies)
Composite key = (StudentID, CourseCode). The dependencies on StudentID alone and CourseCode alone are partial.
| Student (2NF) | ||
|---|---|---|
| StudentID (PK) | StudentName | OtherStudentDetails |
| 1001 | Alice Brown | … |
| 1002 | Bob Carter | … |
| Course (2NF) | ||
|---|---|---|
| CourseCode (PK) | CourseTitle | Lecturer |
| C101 | Database Systems | Dr. Smith |
| C102 | Web Development | Prof. Lee |
| Registration (2NF) | |||
|---|---|---|---|
| StudentID (FK) | CourseCode (FK) | Semester | Grade |
| 1001 | C101 | Fall 2024 | A |
| 1001 | C102 | Fall 2024 | B+ |
| 1002 | C101 | Fall 2024 | B |
All three tables are now in 2NF – every non‑key attribute depends on the whole primary key of its table.
5.5 Identify Transitive Dependencies (move to 3NF)
Assume the Course table also stores the lecturer’s department, giving:
CourseCode → LecturerLecturer → Department
Since Department depends on Lecturer (a non‑key attribute), we have a transitive dependency.
5.6 Decompose to 3NF
| Lecturer (3NF) | ||
|---|---|---|
| LecturerID (PK) | LecturerName | Department |
| L01 | Dr. Smith | Computer Science |
| L02 | Prof. Lee | Information Systems |
| Course (3NF) | |||
|---|---|---|---|
| CourseCode (PK) | CourseTitle | LecturerID (FK) | OtherCourseDetails |
| C101 | Database Systems | L01 | … |
| C102 | Web Development | L02 | … |
The Student and Registration tables remain unchanged (they already satisfy 3NF). All tables now meet the 3NF criteria: every non‑key attribute is directly dependent on the primary key, and no transitive dependencies exist.
6. Expanded Data Dictionary
A Cambridge‑style data dictionary must contain at least the columns shown below. Additional columns such as Default and Validation Rules are useful for practical design.
| Field Name | Data Type | Size | Description | Allowed Values / Format | Default | Validation Rules | Key Type |
|---|---|---|---|---|---|---|---|
| StudentID | INT | 5 | Unique identifier for each student | 00001‑99999 | Auto‑increment | Not NULL, unique | PK |
| StudentName | VARCHAR | 50 | Full name of the student | Alphabetic, spaces, hyphens | — | Not NULL | |
| CourseCode | CHAR | 4 | Code assigned to a course | Pattern Cnnn (e.g., C101) | — | Not NULL, unique in Course | PK (Course) / FK (Registration) |
| CourseTitle | VARCHAR | 100 | Descriptive title of the course | Alphanumeric, spaces | — | Not NULL | |
| LecturerID | CHAR | 3 | Identifier for a lecturer | Pattern Lnn (e.g., L01) | — | Not NULL, unique in Lecturer | PK (Lecturer) / FK (Course) |
| Department | VARCHAR | 50 | Academic department of the lecturer | Alphabetic | — | Not NULL | |
| Semester | VARCHAR | 10 | Semester in which the course was taken | Fall YYYY, Spring YYYY | — | Check against list of valid semesters | |
| Grade | CHAR | 2 | Result achieved for the course | A, A‑, B+, B, …, F | — | Must match grade list |
7. Summary Checklist – Is the Design in 3NF?
- ✅ All tables are in 2NF (no partial dependencies).
- ✅ No transitive dependencies remain – every non‑key attribute depends only on its table’s primary key.
- ✅ Primary keys are defined for every table; foreign keys correctly reference the related primary keys.
- ✅ Referential integrity rules (ON UPDATE/DELETE) are documented.
- ✅ Data dictionary fully describes each field, including allowed values and validation.
Create an account or Login to take a Quiz
46 views
0 improvement suggestions
Log in to suggest improvements to this note.