📖 Database Normalization for Beginners

Database normalization is a process used to organize data in a database properly.

Goals of normalization:

Reduce data redundancy (duplicate data)
Avoid anomalies (problems when inserting/updating/deleting)
Improve consistency
Make database easier to manage

ICT Online Class 0729622034

🔑 Important Terms

1. Redundancy

Redundancy means duplicate or repeated data.

Example

StudentID	StudentName	Course	Lecturer
1	Ali	DBMS	Silva
1	Ali	Web	Perera

Here, student name Ali is repeated many times. This wastes space and can create errors.

2. Anomaly

Anomaly means problems caused by bad database design.

a) Insert Anomaly

Cannot insert data properly.

Cannot add a new course unless a student joins it.

b) Update Anomaly

Need to update same data in many places.

Lecturer Silva changes to Fernando.
Must update every row.
If one row is missed → inconsistent data.

c) Delete Anomaly

Deleting one row removes important information.

If last student leaves a course, course information is lost too.

ICT Online Class 0729622034

🟢 What is 1NF (First Normal Form)?

A table is in 1NF if:

Each column has atomic values
No repeating groups
Each row is unique

Atomic Value

Atomic means:

One cell should contain only ONE value
Cannot store multiple values in one field

Wrong Example

StudentID	Name	PhoneNumbers
1	Ali	0777,0711

PhoneNumbers contains multiple values. This is NOT atomic.

Correct Example

StudentID	Name	PhoneNumber
1	Ali	0777
1	Ali	0711

Now each cell has only one value. This is atomic.

Repeating Group

Repeating group means multiple similar columns storing same type of data.

Wrong Example

StudentID	Subject1	Subject2	Subject3
1	DBMS	Web	AI

This repeats subject columns. NOT 1NF.

Correct Example

StudentID	Subject
1	DBMS
1	Web
1	AI

Now no repeating groups.

Things That Should NOT Exist in 1NF

❌ Multiple values in one cell
❌ Repeating columns
❌ Array/list inside a field
❌ Duplicate rows
❌ Non-atomic values

Steps to Convert to 1NF

Find repeating groups (e.g., Phone1, Phone2, Phone3)
Remove multiple values from one cell
Create separate rows for repeated data
Ensure each row is unique using primary key

Example of Converting to 1NF

Before 1NF

OrderID	Customer	Products
101	Ahmed	Pen,Book

Problems: Multiple values in Products, Not atomic

After 1NF

OrderID	Customer	Product
101	Ahmed	Pen
101	Ahmed	Book

Now: ✔ Atomic ✔ No repeating groups ✔ 1NF achieved

ICT Online Class 0729622034

🟡 2NF (Second Normal Form)

A table is in 2NF if:

Already in 1NF
No partial dependency

Partial Dependency

Occurs when:

Table has composite primary key
Non-key attribute depends on only PART of key

Example

StudentID	CourseID	StudentName	CourseName
1	C1	Ali	DBMS

Primary Key = (StudentID, CourseID)

Problems:

StudentName depends only on StudentID
CourseName depends only on CourseID

This is partial dependency. NOT 2NF.

How to Remove Partial Dependency

Split table.

Student Table

StudentID	StudentName
1	Ali

Course Table

CourseID	CourseName
C1	DBMS

Enrollment Table

StudentID	CourseID
1	C1

Now: ✔ No partial dependency ✔ 2NF achieved

Rules of 2NF

✔ Must be in 1NF
✔ No partial dependency
✔ Non-key columns depend on full primary key

Steps to Convert to 2NF

Ensure table is in 1NF
Find composite key
Check if any column depends on only part of key
Separate into smaller tables

ICT Online Class 0729622034

🟠 3NF (Third Normal Form)

A table is in 3NF if:

Already in 2NF
No transitive dependency

Transitive Dependency

Occurs when a non-key column depends on another non-key column.

Example

StudentID	StudentName	DeptID	DeptName
1	Ali	D1	Computing

Problem: DeptName depends on DeptID, DeptID depends on StudentID
So: StudentID → DeptID → DeptName (Transitive Dependency)

This is NOT 3NF.

How to Remove Transitive Dependency

Split table.

Student Table

StudentID	StudentName	DeptID
1	Ali	D1

Department Table

DeptID	DeptName
D1	Computing

Now: ✔ No transitive dependency ✔ 3NF achieved

Rules of 3NF

✔ Must be in 2NF
✔ No transitive dependency
✔ Non-key attributes depend only on primary key

Steps to Convert to 3NF

Ensure table is in 2NF
Find non-key to non-key dependency
Create separate tables

ICT Online Class 0729622034

🔵 4NF (Fourth Normal Form)

A table is in 4NF if:

Already in 3NF
No multi-valued dependency

Multi-Valued Dependency

Occurs when one entity has multiple independent values.

Example

Student	Hobby	Language
Ali	Cricket	English
Ali	Cricket	Tamil
Ali	Football	English
Ali	Football	Tamil

Problem: Hobbies and languages are independent. Data repeats unnecessarily. NOT 4NF.

How to Remove Multi-Valued Dependency

Split into separate tables.

StudentHobby Table

Student	Hobby
Ali	Cricket
Ali	Football

StudentLanguage Table

Student	Language
Ali	English
Ali	Tamil

Now: ✔ No unnecessary repetition ✔ 4NF achieved

ICT Online Class 0729622034

📊 Quick Summary & Memory Tricks

Normal Form	Removes
1NF	Repeating groups & non-atomic values
2NF	Partial dependency
3NF	Transitive dependency
4NF	Multi-valued dependency

Easy Way to Identify

1NF Check: Any multiple values in one cell? Any repeating columns? If YES → not 1NF
2NF Check: Composite key exists? Any column depends on part of key only? If YES → not 2NF
3NF Check: Any non-key column depends on another non-key column? If YES → not 3NF
4NF Check: Any independent multiple values causing repetition? If YES → not 4NF

🧠 Final Simple Memory Trick

• 1NF → One value per cell
• 2NF → Full key dependency
• 3NF → Only key dependency
• 4NF → No multiple independent lists

ICT Online Class 0729622034

📘 UNIT 8 – Databases (GCE A/L ICT) Exam Notes

🧠 SECTION A: THEORY RECAP (Quick Revision)

Normalization Key Points

1NF → Atomic values, no repeating groups
2NF → No partial dependency
3NF → No transitive dependency
4NF → No multi-valued dependency

ER Diagram Key Points

Entity → real-world object (Student, Course)
Attribute → property (Name, ID)
Relationship → connection (Enrolls)
Primary Key → unique identifier
Cardinality → 1:1, 1:M, M:M

ICT Online Class 0729622034

🧾 Past Paper & Unit Test Questions

🔵 QUESTION 1 – 1NF

OrderID	CustomerName	Products
O01	Kamal	Pen, Book
O02	Nimal	Pencil, Eraser

Questions: 1. State TWO problems. 2. Convert to 1NF. 3. Define atomic value.

✔ Examiner Focus: Detect repeating group, Split into rows

🔵 QUESTION 2 – 2NF

StudentID	CourseID	StudentName	CourseName	Lecturer

PK = (StudentID, CourseID)

Questions: 1. Identify dependency type. 2. Explain why NOT 2NF. 3. Convert into 2NF tables.

✔ Expected: Partial dependency exists (StudentName→StudentID, CourseName→CourseID)

🔵 QUESTION 3 – 3NF

EmpID | EmpName | DeptID | DeptName | DeptLocation

Questions: 1. Identify transitive dependency. 2. Why not 3NF? 3. Normalize to 3NF.

✔ Examiner Focus: DeptName depends on DeptID (not EmpID)

🔵 QUESTION 4 – 4NF

Student	Skill	Language
S1	Cricket	English
S1	Football	Tamil

Questions: 1. Explain redundancy. 2. Convert to 4NF. 3. Define multi-valued dependency.

🔵 QUESTION 5 – ER Diagram Design

System stores: Student (ID, Name, Address), Teacher (ID, Name, Subject). Each student enrolls in multiple subjects. Each subject taught by one teacher.

Tasks: 1. Identify entities/attributes. 2. Draw ER diagram. 3. State cardinality Student–Subject. 4. Identify PKs.

✔ Examiner expects: M:N (Student–Subject), 1:M (Teacher–Subject)

🔵 QUESTION 6 – ER to Table Conversion

Given: Student, Course, Relationship: Enrolls (M:N)

Tasks: 1. Convert to relational tables. 2. Identify PKs & FKs. 3. Explain why junction table is needed.

🔵 QUESTION 7 – Relationship Types

One student has one ID card → 1:1
One teacher teaches many students → 1:M
Many students enroll in many courses → M:N

🔹 SECTION D: Unit Test Short Questions

Define redundancy. 2. What is an anomaly? 3. State TWO anomaly types. 4. What is atomic value? 5. What is a repeating group?
State TWO rules of 1NF. 6. What is partial dependency? 7. What is transitive dependency? 8. Why is 2NF important? 9. Goal of normalization?
Define entity/attribute/relationship. 10. What is cardinality? 11. Draw PK symbol. 12. Diff between 1:M & M:N. 13. What is composite key?

ICT Online Class 0729622034

💡 Exam Tips & Quick Revision

🧠 EXAM TIPS (VERY IMPORTANT)

Always show steps (not only final answer)
Use arrows in dependency explanation
Clearly mark primary key (PK) and foreign key (FK)
ER diagrams: Rectangle = Entity, Oval = Attribute, Diamond = Relationship

📌 QUICK REVISION SUMMARY

Topic	Exam Focus
1NF	Atomic values, remove repeating groups
2NF	Remove partial dependency
3NF	Remove transitive dependency
4NF	Remove multi-valued dependency
ERD	Entities, relationships, cardinality

ICT Online Class 0729622034

📚 Unit 8 DBMS Syllabus Coverage

Database Fundamentals

Data vs Information
Database, DBMS, RDBMS

Relational Database Concepts

Table (Relation), Record (Tuple), Field (Attribute)
Domain, Primary Key, Foreign Key, Candidate Key, Composite Key

Database Design

Entity, Attribute, Relationship, ER Diagram (ERD)

Normalization

UNF, 1NF, 2NF, 3NF

SQL Commands

CREATE, ALTER, DROP
INSERT, UPDATE, DELETE
SELECT, WHERE, ORDER BY, GROUP BY

Database Security

Access Rights, Data Integrity, Backup, Recovery

ICT Online Class 0729622034

📝 Question Types & Most Repeated Topics

A. MCQ Questions

Usually 5–12 MCQs directly or indirectly related to DBMS appear in Paper I.

Database Basics: Primary key, foreign key, DBMS model, data redundancy.
SQL MCQ: Purpose of SELECT * FROM Student;
ERD MCQ: Identify cardinality, One-to-many relationship.
Normalization MCQ: Which table is in 1NF? Identify partial dependency.

B. Structured Questions

Convert ER Diagram to Relations: e.g., Student, Course, Registration → Relational schema.
SQL Writing: CREATE TABLE, INSERT INTO, UPDATE, DELETE.
Normalization: Given a table, convert to 1NF, 2NF, 3NF.

C. Essay Questions

Advantages of DBMS over file systems.
Explain normalization with examples.
Explain ERD and relational mapping.
Explain SQL commands with examples.
Database security and integrity.

Most Repeated Topics (2011–2024 Analysis)

Topic	Frequency
Primary Key	Very High
Foreign Key	Very High
ER Diagram	Very High
ERD → Relation Mapping	Very High
Normalization	Very High
SQL SELECT	Very High
SQL INSERT / UPDATE	High
DDL vs DML	High
Database Security / Integrity	Medium

ICT Online Class 0729622034

✍️ Fill in the Blanks, Matching & True/False

Fill in the Blanks

A Primary Key uniquely identifies a record.
SQL stands for Structured Query Language.
A table row is called a Tuple.
A table column is called an Attribute.
Repetition of data is called Data Redundancy.

Matching Questions

Column A	Column B
Primary Key	Unique Identifier
Foreign Key	Reference Field
SQL	Query Language
ERD	Database Design
1NF	Atomic Values

True / False Questions

Every table must have a primary key. True ✔
Foreign key ensures relationship between tables. True ✔
SQL is a programming language. False ✘
3NF reduces redundancy. True ✔
ERD is used before implementation. True ✔

ICT Online Class 0729622034

💻 Practical SQL Questions

Common Exam Tasks

Create Table

CREATE TABLE Student (
  StudentID INT,
  Name VARCHAR(30)
);

Insert Record

INSERT INTO Student VALUES(1,'Ali');

Select Records

SELECT * FROM Student;

Update Record

UPDATE Student SET Name='Ahmed' WHERE StudentID=1;

Delete Record

DELETE FROM Student WHERE StudentID=1;

ICT Online Class 0729622034

🟢 PART A – MCQ Questions & Answers

Which key uniquely identifies a record in a table?
Answer: C. Primary Key
Explanation: A primary key uniquely identifies every record in a table.
A row in a relational table is called a:
Answer: D. Tuple
Explanation: A row is called a tuple, while a column is called an attribute.
Which SQL command retrieves data?
Answer: C. SELECT
Explanation: SELECT is used to retrieve records from a table.
Which normal form removes repeating groups?
Answer: A. 1NF
Explanation: First Normal Form requires atomic values and no repeating groups.
A foreign key is used to:
Answer: C. Link tables
Explanation: Foreign keys establish relationships between tables.
Which SQL command adds new records?
Answer: B. INSERT INTO
Explanation: INSERT INTO adds records to a table.
Data duplication is called:
Answer: B. Redundancy
Explanation: Redundancy means storing the same data multiple times.
Which key can contain multiple attributes?
Answer: A. Composite Key
Explanation: Composite keys consist of more than one attribute.
Which SQL clause filters records?
Answer: C. WHERE
Explanation: WHERE specifies conditions.
Which command changes existing data?
Answer: A. UPDATE
Explanation: UPDATE modifies existing records.

ICT Online Class 0729622034

🟡 PART B, C & D – Blanks, True/False & Short Qs

PART B – Fill in the Blanks

A Primary Key uniquely identifies a record.
A column in a table is called an Attribute.
SQL stands for Structured Query Language.
A row in a table is called a Tuple.
The process of reducing redundancy is called Normalization.
The set of allowed values for an attribute is called a Domain.
A database containing tables is known as a Relational database.
The Foreign key references a primary key in another table.
Referential integrity ensures foreign key validity.
CREATE TABLE is used to create a table.

PART C – True / False

Every table should have a primary key. True
A foreign key must always be unique. False (Multiple records can share the same foreign key.)
Normalization reduces redundancy. True
SELECT is a DDL command. False (Belongs to DML.)
ERD is used during database design. True
2NF comes before 1NF. False
UPDATE modifies existing records. True
DELETE removes records. True
Primary keys may contain NULL values. False
SQL is used to communicate with databases. True

PART D – Short Questions

What is a DBMS?
Software used to create, manage and retrieve data from databases (e.g., MySQL, Oracle).
What is a Primary Key?
An attribute that uniquely identifies each record (e.g., StudentID).
What is a Foreign Key?
An attribute referencing the primary key of another table.
What is an ER Diagram?
A graphical representation of entities and relationships.
What is Normalization?
The process of organizing data to reduce redundancy.

ICT Online Class 0729622034

🔵 PART E – Normalization Questions & Answers

Question 1: Convert to 1NF

StudentID	StudentName	Subject1	Subject2
S001	Amal	ICT	Maths

Answer

StudentID	StudentName	Subject
S001	Amal	ICT
S001	Amal	Maths

Explanation: Repeating groups removed.

Question 2: Convert to 2NF

Answer

Order Table: | OrderID | ProductID | Qty |

Product Table: | ProductID | ProductName |

Explanation: ProductName depends only on ProductID (Partial dependency removed).

Question 3: Convert to 3NF

Answer

Student Table: | StudentID | StudentName | ClassID |

Class Table: | ClassID | ClassName |

Explanation: Removed transitive dependency (ClassName depends on ClassID, not StudentID).

ICT Online Class 0729622034

🟠 PART F & G – SQL & Essay Questions

PART F – SQL Questions

Create Student table:
CREATE TABLE Student(StudentID INT PRIMARY KEY, Name VARCHAR(50), City VARCHAR(30));
Insert student:
INSERT INTO Student VALUES(1,'Amal','Colombo');
Display all students:
SELECT * FROM Student;
Display only Colombo students:
SELECT * FROM Student WHERE City='Colombo';
Update city:
UPDATE Student SET City='Kandy' WHERE StudentID=1;
Delete student:
DELETE FROM Student WHERE StudentID=1;

PART G – Essay Questions

Essay 1: Advantages of DBMS over file systems

Reduced redundancy, Better security, Data sharing, Data integrity, Faster retrieval, Backup/recovery, Multiple user access.

Essay 2: Explain normalization

1NF (Atomic values, no repeating groups), 2NF (Remove partial dependency), 3NF (Remove transitive dependency). Benefits: Reduced redundancy, improved consistency, easier maintenance.

Essay 3: Primary Key vs Foreign Key

PK: Unique, Not NULL. FK: References another table, maintains relationships.

Essay 4: DDL and DML

DDL: CREATE, ALTER, DROP. DML: SELECT, INSERT, UPDATE, DELETE.

Essay 5: Database Security

User authentication, Password protection, Access control, Backup, Recovery, Encryption, Audit logs.

ICT Online Class 0729622034

🔷 ER DIAGRAM (ERD) Concepts (Q1-Q8)

What is an Entity?
A real-world object, person, place, event, or thing about which data is stored (e.g., Student, Teacher). Entities become tables.
What is an Attribute?
A property or characteristic of an entity (e.g., StudentID, Name). Attributes become columns.
What is a Relationship?
Describes how two entities are associated (e.g., Student → Enrolls → Course).
Draw ERD for Students and Courses (M:N)
Student M -------- M Course (Many-to-Many relationship).
Identify Relationship: One teacher teaches many students.
Teacher 1 -------- M Student (One-to-Many).
Identify Relationship: One customer can place many orders.
Customer 1 -------- M Order.
Identify Relationship: One employee manages one department.
Employee 1 -------- 1 Department (One-to-One).
What is Cardinality?
Specifies the number of entity instances participating in a relationship. Types: 1:1, 1:M, M:N.

ICT Online Class 0729622034

📐 ER DIAGRAM (ERD) Drawing & Conversion (Q9-Q15)

Question 9: School ERD

One class has many students. Answer: Class 1 ------- M Student

Question 10: Library ERD

Members can borrow many books. Books can be borrowed by many members.
Answer: Member M ------- M Book. Needs an associative entity: Borrow(BorrowID, Date) between Member(1) and Book(M).

Question 11: Company ERD

One department employs many employees.
Answer: Department 1 -------- M Employee

Question 12: Hospital ERD

One doctor treats many patients.
Answer: Doctor 1 -------- M Patient

Question 13: Convert ERD to Relations (1:M)

Student 1 ------ M Exam
Answer:
Student(StudentID PK, Name)
Exam(ExamID PK, Subject, StudentID FK)
Foreign key goes to the "many" side.

Question 14: Convert ERD to Relations (M:N)

Customer M ------ M Product
Answer:
Customer(CustomerID PK, Name)
Product(ProductID PK, ProductName)
Order(CustomerID FK, ProductID FK)
Many-to-Many requires a junction table.

Question 15: Steps in designing an ER Diagram

Identify entities.
Identify attributes.
Select primary keys.
Identify relationships.
Determine cardinality.
Draw entities and attributes.
Connect relationships.
Validate design.

ICT Online Class 0729622034

🏆 Frequently Asked ERD Scenarios & Golden Rules

Frequently Asked A/L ERD Scenarios

Scenario	Relationship
Student – Course	M:N
Customer – Order	1:M
Department – Employee	1:M
Teacher – Class	1:M
Doctor – Patient	1:M
Library Member – Book	M:N
Supplier – Product	M:N
Hotel – Room	1:M
Passenger – Ticket	1:M
Employee – Project	M:N

🌟 Golden Rule for Exams

1:1 Relationship
→ Foreign key in either table.

1:M Relationship
→ Foreign key on the MANY side.

M:N Relationship
→ Create a new associative (junction) table containing both foreign keys.

Mastering these three cases will help solve almost every ERD question in GCE A/L ICT Unit 8.

ICT Online Class 0729622034

🎓 Expert ICT, Coding, School Classes, Digital Marketing & University Project Guidance

Struggling with your university final year project? Want to master coding, upscale your business with expert digital marketing, or learn absolute computer basics from scratch? We offer high-quality individual and group online classes conducted in English, Sinhala, or Tamil mediums. Get guaranteed academic success and professional growth with tailored guidance.

🎓 University Final Year Project Guidance & AI

Get specialized, end-to-end mentoring and technical support to pass your degree or master's program with flying colors:

🏫 Targeted Institutes: Expert guidance tailored for BIT UCSC, UoM, SLIIT, NIBM, and other leading universities.
🔬 Postgraduate Support: Comprehensive assistance for MSc Software Final Year Projects.
🤖 AI & Smart Applications: Step-by-step implementation of AI, Machine Learning (ML), and automation modules.
✅ Guaranteed Success: Help with documentation, system architecture, coding, and viva preparation.

🏫 School ICT & Corporate Beginner Classes

💻 Non-IT Staff Computer Basics: Absolute beginner-friendly online classes covering essential computer skills, office tools, and internet operations.
🎒 Primary & Secondary (Grades 1-10): Interactive online ICT classes tailored to build strong foundations from early ages.
📝 Exam Prep: Dedicated training packages for GCE O/L, GCE A/L ICT, and GIT exams.
🌍 Global Syllabuses: Complete curriculum coverage for Local, Edexcel, and Cambridge in English & Tamil Mediums.

📢 Software Development & Digital Marketing Services

⚙️ Software & Web Development: Professional custom software application and website development built using PHP & MySQL.
🎯 Social Media Management: Content creation, publishing, and channel management for Facebook, Instagram, TikTok, and YouTube.
📈 Ad Boosting: Highly targeted paid advertising campaigns to drive leads, traffic, and sales to your business.

📞 Connect With Us Instantly

Book your slot for online classes or get a premium tech service quote today!

💬 WhatsApp: +94 729622034

📧 Email: ITClassSL@gmail.com

🌐 Explore Our Resources & Communities

Stay updated with our latest tutorials, project ideas, and student guides across all our official platforms:

📺 YouTube Tutorials: Subscribe to our Channel
💼 Professional Network: Connect on LinkedIn
✍️ Tech Blog: Visit our WordPress Site
❓ Project Q&A: Follow our Quora Guide Profile
📰 Monthly Updates: Read Our Newsletter
🌐 Official Portfolios: Wix Site | Google Business | Strikingly Portfolio
🗣️ Student Forum: Join our ElaKiri Discussion Thread

Main Menu

Monday, June 8, 2026

Database Normalization for Beginners GCE A/L ICT Unit 8 Exam Notes and Question ICT Classes English Tamil Medium

1. Redundancy

Example

2. Anomaly

a) Insert Anomaly

b) Update Anomaly

c) Delete Anomaly

Atomic Value

Wrong Example

Correct Example

Repeating Group

Wrong Example

Correct Example

Things That Should NOT Exist in 1NF

Steps to Convert to 1NF

Example of Converting to 1NF

Before 1NF

After 1NF

Partial Dependency

Example

How to Remove Partial Dependency

Student Table

Course Table

Enrollment Table

Rules of 2NF

Steps to Convert to 2NF

Transitive Dependency

Example

How to Remove Transitive Dependency

Student Table

Department Table

Rules of 3NF

Steps to Convert to 3NF

Multi-Valued Dependency

Example

How to Remove Multi-Valued Dependency

StudentHobby Table

StudentLanguage Table

Easy Way to Identify

🧠 Final Simple Memory Trick

🧠 SECTION A: THEORY RECAP (Quick Revision)

🔵 QUESTION 1 – 1NF

🔵 QUESTION 2 – 2NF

🔵 QUESTION 3 – 3NF

🔵 QUESTION 4 – 4NF

🔵 QUESTION 5 – ER Diagram Design

🔵 QUESTION 6 – ER to Table Conversion

🔵 QUESTION 7 – Relationship Types

🔹 SECTION D: Unit Test Short Questions

🧠 EXAM TIPS (VERY IMPORTANT)

📌 QUICK REVISION SUMMARY

Database Fundamentals

Relational Database Concepts

Database Design

Normalization

SQL Commands

Database Security

A. MCQ Questions

B. Structured Questions

C. Essay Questions

Most Repeated Topics (2011–2024 Analysis)

Fill in the Blanks

Matching Questions

True / False Questions

Common Exam Tasks

Create Table

Insert Record

Select Records

Update Record

Delete Record

PART B – Fill in the Blanks

PART C – True / False

PART D – Short Questions

Question 1: Convert to 1NF

Answer

Question 2: Convert to 2NF

Answer

Question 3: Convert to 3NF