Unit 08
🗄️ Data & Databases
Database
Management
Learn how relational databases are designed and built from scratch — drawing ER diagrams, converting them to logical schema, writing SQL DDL and DML commands, normalising tables to remove anomalies, and querying data with SELECT, JOIN and WHERE. Covers competency levels 8.1–8.7 from the NIE ICT syllabus.
🗂️ Database Models
📋 Relational Model
🔑 Keys & Constraints
🔷 ER Diagrams
📐 Entities & Attributes
🔗 Relationships & Cardinality
🧩 Extended ER (EER)
📊 Logical Schema
🔄 ER → Logical Schema
📝 SQL — DDL Commands
🔍 SQL — SELECT & JOIN
➕ INSERT / UPDATE / DELETE
⚠️ Redundancy & Anomalies
📏 Functional Dependencies
1️⃣ 1NF
2️⃣ 2NF
3️⃣ 3NF
📚 Competency Levels — Unit 08
COMPETENCY 8.1 · 2 PERIODS
Database Basics & Models
Data vs. information, structured vs. unstructured. Database definition. Flat file, hierarchical, network, relational and object-relational models — features comparison.
COMPETENCY 8.2 · 4 PERIODS
Relational Database Model
Relations/tables, attributes/columns, tuples/rows, relationships. Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK (table check).
COMPETENCY 8.3 · 14 PERIODS
SQL — DDL & DML
Introduction to SQL and its classification. DDL: CREATE, ALTER (add/delete columns, add/delete PK/FK), DROP TABLE, DROP DATABASE. DML: SELECT (single & multi-table with INNER JOIN), INSERT, UPDATE, DELETE.
COMPETENCY 8.4 · 12 PERIODS
ER Diagram Design
Entities, attributes and entity identifiers. Relationship types and cardinality (1:1, 1:N, M:N). Drawing ER diagrams for given scenarios. Introduction to Extended ER (EER) diagrams.
COMPETENCY 8.5 · 6 PERIODS
Logical Schema Design
Relational schema and instances. Candidate key, primary key, alternate key, foreign key. Domain definition. Transforming ER conceptual design into a relational logical schema.
COMPETENCY 8.6 · 6 PERIODS
ER → Logical Schema Transformation
Entity transformation, attribute transformation and relationship transformation methods. Converting all ER elements into complete logical relational schema tables.
COMPETENCY 8.7 · 6 PERIODS
Normalisation (0NF → 3NF)
Need for normalisation — redundancies and anomalies (insert, update, delete). Functional dependencies: full, partial, transitive. Zero NF → 1NF → 2NF → 3NF with anomaly reduction at each stage.
⚡ SQL Quick Reference — DDL vs DML
DDL — Data Definition Language
CREATECreate a new database or table with columns & constraints
ALTERAdd/remove columns, add/drop PK or FK from existing table
DROP TABLEPermanently delete a table and all its data
DROP DBPermanently delete an entire database
DML — Data Manipulation Language
SELECTRetrieve rows/columns — use WHERE to filter, JOIN for multiple tables
INSERTAdd new rows of data into a table
UPDATEModify existing records — always use WHERE clause
DELETERemove rows from a table — use WHERE to avoid deleting all rows
🗂️ Database Models Overview
Flat File
Single table. Simple but causes redundancy and anomalies.
Hierarchical
Tree — parent/child. Fast reads but inflexible relationships.
Network
Complex many-to-many links. Powerful but hard to manage.
Relational ⭐
Tables with keys & constraints. SQL-based. Widely used in industry.
Object-Relational
Extends relational with OOP concepts — objects & inheritance.
📏 Normalisation — 0NF → 3NF
0NF
Zero Normal Form
Unnormalised data. Repeating groups and multi-valued attributes present.
❌ All anomalies present
1NF
First Normal Form
All attributes atomic. No repeating groups. Primary key identified.
✅ Removes repeating groups
2NF
Second Normal Form
In 1NF + every non-key attribute fully depends on the whole PK (no partial dependencies).
✅ Removes partial dependencies
3NF
Third Normal Form
In 2NF + no transitive dependencies (non-key attributes must not depend on other non-key attributes).
✅ Removes transitive dependencies
📥 Unit 08 — Available Downloads
📄
Full Notes — Database Management
PDF · 5.1 MB · 74 pages
📄
ER Diagram Guide & Exercises
PDF · 767 kB · 6 pages
📄
SQL Reference Cheat Sheet
PDF · 0.8 MB · 4 pages
📄
Entity Relationship Diagrams (ER Diagrams)
PDF · 1.1 MB · 14 pages
📄
Relational Model & Logical Schema
PDF · 1.6 MB · 16 pages
📗
Sample Database — School
PDF · 0.4 MB · Practice data
📝
SQL Practice Q&A
PDF · 1.3 MB · 26 pages
📊
Slide Deck — Database Management
PPTX · 7.3 MB · 52 slides
📄
Revision Summary Card
PDF · 0.6 MB · 2 pages
📄
Past Paper Questions
PDF · 1.8 MB · 18 pages
🗜️
Complete Bundle — All Files
ZIP · 21.3 MB · All resources