Unit 8 Detailed Notes GCE A/L ICT Database Management Systems (DBMS) Questions and Answers

🎓 GCE A/L ICT – Unit 8 Detailed Notes

Database Management Systems (DBMS)

Comprehensive exam-focused notes for Sri Lankan GCE Advanced Level ICT

---

📌 8.1–8.3 Introduction: Data, Information & File Systems

🔹 What is Data?

Data are raw facts and figures without meaning.

Examples: 95, Ahmed, Colombo, 2026

🔹 What is Information?

Information is processed and organized data that has meaning.

Example: "Ahmed scored 95 marks in ICT in 2026."

📁 File Processing Systems (Pre-Database Era)

Characteristics:

• Data stored in separate files per department
• Difficult to share data across systems
• High redundancy and maintenance complexity

❌ Problems of File Processing Systems

Problem	Description	Example
Data Redundancy	Same data stored multiple times	Student name in exam file AND library file
Data Inconsistency	Different versions of same data	Old address in one file, new in another
Data Isolation	Data scattered across files	Hard to get complete student profile
Security Problems	Difficult access control	No centralized permission system
Backup Issues	Recovery difficult after failure	Multiple files = multiple backup points
Lack of Integrity	No validation rules	Invalid data can be entered freely

✅ Database Approach Advantages

• Reduced Redundancy: Duplicate data minimized
• Better Consistency: Single updated version of data
• Data Sharing: Multiple users access same data
• Improved Security: Controlled access privileges
• Backup & Recovery: Centralized restoration
• Data Integrity: Validation rules enforce correctness
• Concurrent Access: Multiple users work simultaneously

💡 Exam Tip: "Compare file processing vs DBMS" is a frequent 10-mark question. Always structure answer as: Definition → Problems of file system → Advantages of DBMS → Conclusion.

⚙️ 8.4–8.6 DBMS Fundamentals: Functions, Components & Architecture

🔹 What is a DBMS?

A DBMS is software used to create, manage, manipulate, and maintain databases.

Common Examples: MySQL, Oracle, Microsoft Access, PostgreSQL, MongoDB, SQLite

⚙️ Functions of a DBMS

1. Data Storage: Efficiently stores large volumes of data
2. Data Retrieval: Fast search and fetch operations
3. Data Manipulation: INSERT, UPDATE, DELETE operations
4. Security Management: Authentication and authorization
5. Backup & Recovery: Protects against system failures
6. Transaction Processing: Ensures reliability of operations
7. Concurrency Control: Manages simultaneous user access
8. Data Integrity: Enforces accuracy and consistency rules

🧩 Components of DBMS Environment

Component	Description	Examples
Hardware	Physical devices	Servers, Hard disks, Network devices
Software	DBMS + Operating System	MySQL, Windows, Linux
Data	Actual stored information	Student records, Product lists
Procedures	Rules and instructions	Backup schedules, Access policies
People	Users interacting with system	DBA, Developers, End users

👥 Types of Database Users

• Database Administrator (DBA): Security, backup, performance tuning, user management
• Database Designers: Design logical and physical database structures
• Application Programmers: Develop software that interacts with database
• End Users: Cashiers, Students, Teachers who use the system daily

🏗️ Three-Level Database Architecture

Level	Purpose	Example
External Level	User-specific views of data	Teacher sees marks; Cashier sees payments
Conceptual Level	Logical structure of entire database	ER diagram showing all entities & relationships
Internal Level	Physical storage details	File structures, indexing methods, disk allocation

🗂️ 8.7–8.10 Data Models, Relational Concepts & Keys

📊 Types of Data Models

Model	Structure	Pros	Cons	Example
Hierarchical	Tree (parent-child)	Simple, Fast access	Complex relationships difficult	School → Classes → Students
Network	Graph (many-to-many)	Flexible relationships	Complex design	Students ↔ Subjects ↔ Teachers
Relational ⭐	Tables (rows & columns)	Simple, Easy querying, Flexible	Complex joins may slow performance	Student table with ID, Name, Grade
Object-Oriented	Objects with methods	Good for complex data	Steep learning curve	Multimedia systems, CAD

🔑 Relational Database Concepts

• Relation: A table
• Tuple: A row in a table
• Attribute: A column in a table
• Domain: Allowed values for an attribute (e.g., Gender = Male/Female)
• Degree: Number of attributes in a relation
• Cardinality: Number of tuples (rows) in a relation

🗝️ Types of Keys in Databases

Key Type	Description	Characteristics	Example
Primary Key	Uniquely identifies each record	Unique, Cannot be NULL	StudentID
Candidate Key	Possible choices for primary key	Unique, Minimal	NIC or StudentID
Alternate Key	Candidate key not selected as primary	Unique but unused as PK	Email (if StudentID is PK)
Composite Key	Combination of multiple attributes	Used when single attribute isn't unique	StudentID + SubjectID
Foreign Key	Attribute referencing another table's PK	Creates relationships between tables	StudentID in Marks table

🔒 Integrity Constraints

• Entity Integrity: Primary key cannot be NULL
• Referential Integrity: Foreign key values must exist in referenced table
  Example: Cannot enter invalid StudentID in Marks table
• Domain Integrity: Values must belong to valid domain
  Example: Age cannot be negative; Grade must be A/B/C/D/F

🔷 8.11–8.16 ER Modeling: Entities, Attributes & Relationships

🔷 Components of ER Diagram

Component	Represents	Symbol	Example
Entity	Real-world object	▭ Rectangle	Student, Teacher, Course
Attribute	Property of entity	⭕ Oval	StudentName, Age, Address
Relationship	Association between entities	◇ Diamond	Student enrolls in Course

📋 Types of Attributes

• Simple: Cannot be divided (Age, Gender)
• Composite: Can be divided (Address → City, Street, ZIP)
• Single-Valued: One value only (NIC, StudentID)
• Multi-Valued: Multiple values (Phone Numbers, Email addresses)
• Derived: Calculated from others (Age from Date of Birth, Total from Unit Price × Quantity)

🔗 Types of Relationships

Type	Notation	Description	Example
One-to-One (1:1)	1 ↔ 1	One entity instance relates to one other instance	Person ↔ Passport
One-to-Many (1:M)	1 → ∞	One entity instance relates to many others	Teacher → Students
Many-to-Many (M:N)	∞ ↔ ∞	Many instances relate to many others	Students ↔ Subjects

📐 Degree of Relationships

• Unary: Relationship within same entity
  Example: Employee supervises Employee
• Binary: Relationship between two entities
  Example: Student enrolls in Course
• Ternary: Relationship among three entities
  Example: Student takes Subject taught by Teacher

⚠️ Weak Entity

An entity that cannot exist independently and depends on another entity.

• Has a partial key (discriminator)
• Identified via relationship with strong entity
• Example: Dependent linked to Employee; OrderItem linked to Order

🗂️ Mapping ER Diagram to Relations

Example: Student-Subject Enrollment

Student Table

StudentID (PK)	Name	Address
S001	Ali	Colombo

Subject Table

SubjectID (PK)	SubjectName
ICT01	Information & Communication Technology

Enrollment Table (Relationship)

StudentID (FK)	SubjectID (FK)	EnrollDate
S001	ICT01	2026-01-15

🎯 ER Diagram Exam Tips:
✓ Use correct symbols (rectangle/oval/diamond)
✓ Label relationships with verbs (enrolls, teaches, manages)
✓ Show cardinality clearly (1, M, N)
✓ Underline primary keys in attribute lists
✓ For M:N relationships, create a separate junction table

🔄 8.17–8.21 Normalization: 1NF, 2NF, 3NF

🎯 Purpose of Normalization

Normalization is the process of organizing data to:

• ✓ Eliminate duplicate/redundant data
• ✓ Reduce insertion, update, and deletion anomalies
• ✓ Improve data integrity and consistency
• ✓ Simplify database maintenance

⚠️ Types of Anomalies

Anomaly	Description	Example
Insertion	Cannot add data without other unrelated data	Cannot add new subject without assigning a student
Update	Need to update same data in multiple places	Changing teacher name requires updating many records
Deletion	Deleting data unintentionally removes other data	Deleting last student also deletes subject info

✅ First Normal Form (1NF)

Requirements:

• All attribute values must be atomic (indivisible)
• No repeating groups or multi-valued attributes in a single cell

Before 1NF:

StudentID	Subjects
S01	ICT, Maths, Science

After 1NF:

StudentID	Subject
S01	ICT
S01	Maths
S01	Science

✅ Second Normal Form (2NF)

Requirements:

• Must already be in 1NF
• No partial dependency: Non-key attributes must depend on the entire primary key

Example: If PK is (StudentID + SubjectID), then Grade must depend on BOTH, not just StudentID.

✅ Third Normal Form (3NF)

Requirements:

• Must already be in 2NF
• No transitive dependency: Non-key attributes must not depend on other non-key attributes

Example: If StudentID → Department → DeptHead, then DeptHead depends transitively on StudentID. Move DeptHead to Department table.

🎯 Normalization Exam Strategy:
1. Identify the primary key first
2. Check for repeating groups → Apply 1NF
3. Check if non-key attributes depend on full PK → Apply 2NF
4. Check for non-key → non-key dependencies → Apply 3NF
5. Always show "Before" and "After" tables for full marks

💻 8.22–8.26 SQL Essentials: Commands, Clauses & Functions

📦 SQL Command Categories

Category	Full Name	Purpose	Key Commands
DDL	Data Definition Language	Define/modify database structure	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language	Insert/update/delete data	INSERT, UPDATE, DELETE
DQL	Data Query Language	Retrieve data from database	SELECT
DCL	Data Control Language	Control access permissions	GRANT, REVOKE
TCL	Transaction Control Language	Manage transaction boundaries	COMMIT, ROLLBACK, SAVEPOINT

💡 Essential SQL Examples

-- CREATE TABLE
CREATE TABLE Student (
  StudentID CHAR(5) PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Grade CHAR(1),
  EnrollmentDate DATE
);

-- INSERT DATA
INSERT INTO Student VALUES ('S001', 'Ali', 'A', '2026-01-15');

-- SELECT WITH CONDITIONS
SELECT Name, Grade 
FROM Student 
WHERE Grade IN ('A', 'B') 
ORDER BY Name;

-- AGGREGATE FUNCTIONS
SELECT Grade, COUNT(*) AS StudentCount, AVG(Marks) AS AvgMark
FROM Results
GROUP BY Grade
HAVING COUNT(*) > 5;

-- JOIN EXAMPLE
SELECT s.Name, m.Subject, m.Marks
FROM Student s
INNER JOIN Marks m ON s.StudentID = m.StudentID
WHERE m.Marks >= 75;
    

🔍 SQL Clauses & Operators

• WHERE: Filters rows before grouping
• ORDER BY: Sorts results (ASC/DESC)
• GROUP BY: Groups rows for aggregate functions
• HAVING: Filters groups after aggregation
• Comparison Operators: =, >, <, >=, <=, <>
• Logical Operators: AND, OR, NOT
• Special Operators: BETWEEN, IN, LIKE, IS NULL

📊 Aggregate Functions

Function	Purpose	Example
COUNT()	Count rows	COUNT(*) , COUNT(StudentID)
SUM()	Total of numeric values	SUM(Marks)
AVG()	Average value	AVG(Marks)
MAX()	Highest value	MAX(Marks)
MIN()	Lowest value	MIN(Marks)

🔗 SQL JOIN Types

• INNER JOIN: Returns matching rows from both tables
• LEFT JOIN: All rows from left table + matches from right
• RIGHT JOIN: All rows from right table + matches from left
• FULL JOIN: All rows from both tables (matches + non-matches)

⚠️ Common SQL Exam Mistakes:
✗ Forgetting WHERE clause in UPDATE/DELETE (affects ALL rows!)
✗ Using HAVING without GROUP BY
✗ Confusing WHERE (pre-aggregation) vs HAVING (post-aggregation)
✗ Missing JOIN condition (creates Cartesian product)
✓ Always test queries mentally with sample data before writing

🛡️ 8.27–8.32 Advanced DBMS: Views, Indexing, Transactions & Security

👁️ Views (Virtual Tables)

A view is a saved SQL query that appears as a table but doesn't store data physically.

Advantages:

• ✓ Security: Restrict access to sensitive columns
• ✓ Simplicity: Hide complex JOINs behind simple view name
• ✓ Consistency: Centralize business logic

CREATE VIEW TopStudents AS
SELECT Name, Grade, Marks
FROM Student
WHERE Marks >= 75;
    

⚡ Indexing

Indexes improve search speed by creating a lookup structure on columns.

• Advantages: Faster SELECT, WHERE, JOIN, ORDER BY operations
• Disadvantages: Slower INSERT/UPDATE/DELETE; Extra storage space
• Best for: Columns frequently used in WHERE clauses or as JOIN keys

🔄 Transactions & ACID Properties

A transaction is a logical unit of work that must be completed entirely or not at all.

Property	Meaning	Example
Atomicity	All or nothing execution	Bank transfer: debit AND credit must both succeed
Consistency	Database moves from one valid state to another	Account balance never goes negative if rule exists
Isolation	Concurrent transactions don't interfere	Two users booking same seat: only one succeeds
Durability	Committed changes survive system failure	After COMMIT, data remains even if power fails

🔐 Database Security Measures

• Authentication: Verify user identity (username/password, biometrics)
• Authorization: Control what authenticated users can do (GRANT/REVOKE)
• Encryption: Convert data to unreadable form during storage/transit
• Audit Trails: Log all database activities for monitoring
• Backup Strategies: Regular copies for disaster recovery

⚙️ Concurrency Control

Manages simultaneous access to prevent conflicts:

• Problems: Lost updates, Dirty reads, Unrepeatable reads, Phantom reads
• Solutions: Locking (pessimistic), Timestamp ordering, Optimistic concurrency

🌐 8.33–8.39 Modern Topics + Exam Focus + Revision

💾 Backup Types

Type	What It Copies	Restore Speed	Storage Used
Full Backup	Entire database	Fastest restore	Highest
Incremental	Changes since last backup (any type)	Slowest restore (needs chain)	Lowest
Differential	Changes since last FULL backup	Moderate restore speed	Moderate

🌍 Modern Database Trends

• Distributed Databases: Data across multiple locations → Better reliability & local access
• Data Warehousing: Central repository for analysis → Business intelligence, Decision support
• Data Mining: Discover patterns in large data → Fraud detection, Recommendations
• Big Data (5Vs): Volume, Velocity, Variety, Veracity, Value
• NoSQL Databases: MongoDB, Cassandra → Flexible schema, horizontal scaling for unstructured data
• Cloud Databases: Hosted on cloud platforms → Scalability, accessibility, reduced maintenance

🏫 Real-World Applications

• 🏦 Banking: Account management, ATM transactions, Fraud detection
• 🏥 Hospitals: Patient records, Appointment scheduling, Billing
• 🏫 Schools: Student management, Attendance, Examination systems
• 🛒 E-Commerce: Product catalogs, Order processing, Customer profiles

🎯 Examination Focus Areas

📝 Theory Questions (Frequent):

• Advantages of DBMS over file processing
• Types of keys with examples
• Normalization steps (1NF→2NF→3NF) with table transformations
• ER diagram components and relationship types
• SQL command categories and use cases
• ACID properties with real-world examples

💻 Practical/Diagram Questions (High Marks):

• Draw ER diagram for given scenario (school, library, hospital)
• Convert ER diagram to relational tables
• Normalize a given unnormalized table to 3NF
• Write SQL queries: SELECT with JOIN, GROUP BY, aggregate functions
• Identify and correct DFD/ER diagram errors

⚡ Quick Revision Checklist

Topic	Key Point to Remember
Primary Key	Unique + NOT NULL; identifies each row
Foreign Key	References PK in another table; creates relationship
1NF	Atomic values only; no repeating groups
2NF	1NF + no partial dependency on composite PK
3NF	2NF + no transitive dependency (non-key → non-key)
SQL SELECT	SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
ACID	Atomicity, Consistency, Isolation, Durability
ER Symbols	Entity=▭, Attribute=⭕, Relationship=◇

💡 Student Success Tips for Unit 8

1. Practice drawing ER diagrams daily with different scenarios ✏️
2. Memorize normalization rules with transformation examples 🔁
3. Write at least 5 SQL queries daily (SELECT, JOIN, GROUP BY) 💻
4. Learn key definitions word-for-word (examiners check terminology) 📌
5. Use real-world examples in answers (school, bank, hospital) 🌍
6. Practice past paper diagram questions under timed conditions ⏱️
7. Understand WHY behind rules (not just memorization) 🧠
8. Review ACID properties with transaction examples 🔄
9. Compare concepts in tables for essay questions 📊
10. Leave 5 minutes at exam end to check diagram labels & SQL syntax ✅

🎯 FINAL EXAM REMINDERS

✓ For ER diagrams: Label EVERY element, show cardinality

✓ For SQL: Write syntax exactly; missing semicolon can cost marks

✓ For normalization: Show each step with "Before/After" tables

✓ For essays: Definition → Explanation → Example → Conclusion

You've mastered the concepts. Now execute with confidence. All the best! ✨