🎓 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
- Data Storage: Efficiently stores large volumes of data
- Data Retrieval: Fast search and fetch operations
- Data Manipulation: INSERT, UPDATE, DELETE operations
- Security Management: Authentication and authorization
- Backup & Recovery: Protects against system failures
- Transaction Processing: Ensures reliability of operations
- Concurrency Control: Manages simultaneous user access
- 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
| StudentID (PK) | Name | Address |
|---|---|---|
| S001 | Ali | Colombo |
| SubjectID (PK) | SubjectName |
|---|---|
| ICT01 | Information & Communication Technology |
| 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
✓ 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
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
✗ 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
- Practice drawing ER diagrams daily with different scenarios ✏️
- Memorize normalization rules with transformation examples 🔁
- Write at least 5 SQL queries daily (SELECT, JOIN, GROUP BY) 💻
- Learn key definitions word-for-word (examiners check terminology) 📌
- Use real-world examples in answers (school, bank, hospital) 🌍
- Practice past paper diagram questions under timed conditions ⏱️
- Understand WHY behind rules (not just memorization) 🧠
- Review ACID properties with transaction examples 🔄
- Compare concepts in tables for essay questions 📊
- 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! ✨
