๐ 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
• 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)
- 1NF → Atomic values, no repeating groups
- 2NF → No partial dependency
- 3NF → No transitive dependency
- 4NF → No multi-valued dependency
- 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.
๐ต 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.
๐ต QUESTION 3 – 3NF
EmpID | EmpName | DeptID | DeptName | DeptLocation
Questions: 1. Identify transitive dependency. 2. Why not 3NF? 3. Normalize to 3NF.
๐ต 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.
๐ต 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
Given: | OrderID | ProductID | ProductName | Qty | (PK: OrderID, ProductID)
Answer
Order Table: | OrderID | ProductID | Qty |
Product Table: | ProductID | ProductName |
Explanation: ProductName depends only on ProductID (Partial dependency removed).
Question 3: Convert to 3NF
Given: | StudentID | StudentName | ClassID | ClassName |
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
→ 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