Database design progresses through three stages:

Conceptual Data Model: High-level overview focusing on business concepts and rules without technical details. Independent of hardware and software specifications.

Logical Data Model: Defines data structures (entities, tables, relationships) independently of specific DBMS implementation. Includes precise attribute specifications and follows normalization up to Third Normal Form (3NF).

Physical Data Model: Details actual data storage within a specific DBMS. Includes table names, column names, data types, keys, indexes, and access profiles.

Worked Example: Bus Company Data Model

Conceptual Stage: Entities = Route, Bus Company, Schedule

Logical Stage: Attributes include route_number, route_name, origin, destination for Route; company_id, route_number, owner_name for Bus Company. Primary keys: route_number, company_id, schedule_number.

Physical Stage: Table definitions with data types:

• route (route_number INT, route_name VARCHAR(50), origin VARCHAR(50), destination VARCHAR(50))
• bus_company (company_id INT, route_number INT, owner_name VARCHAR(50), post_code INT)
• schedule (schedule_number INT, route_number INT, departure_time TIME, arrival_time TIME)

• Attribute/Field: A named column describing a characteristic (e.g., first_name, date_of_birth)
• Tuple/Record: A collection of related attributes representing a single item; a row in a table
• Relation: A set of tuples with the same attributes; also called a table
• Domain: The set of allowable values for an attribute (e.g., Grade: 0-100)
• Primary Key (PK): A field that uniquely identifies each record; cannot be null
• Candidate Key: A minimal set of attributes that uniquely identifies a row; multiple may exist
• Alternate Key: A candidate key not chosen as primary key
• Foreign Key (FK): An attribute referencing the primary key of another table, creating relationships
• Composite Key: A combination of two or more columns that uniquely identify each row

Worked Example: Student Database

In a student registration table:

• Primary Key: StudentID
• Candidate Keys: StudentID, Roll_Number, Email
• Alternate Keys: Roll_Number, Email
• Foreign Key: DepartmentID (referencing Department table)

The ER model represents database structure through:

• Entities: Real-world objects stored in the database (e.g., Student, Course)
• Attributes: Properties of entities (e.g., StudentName, CourseName)
• Relationships: How entities interact (enrols, teaches)

Cardinality Types:

• One-to-One (1:1): One entity associates with exactly one other
• One-to-Many (1:M): One entity associates with multiple entities
• Many-to-One (M:1): Multiple entities associate with one entity
• Many-to-Many (M:N): Multiple entities associate with multiple entities

A constraint where one attribute determines another. Types include:

• Trivial: X → Y where Y is a subset of X
• Non-trivial: X → Y where Y is not a subset of X
• Transitive: X → Y → Z (indirect dependency through intermediate attribute)
• Multivalued: One attribute determines multiple independent values

Normalization organises data to minimise redundancy and prevent anomalies. The main normal forms are:

First Normal Form (1NF): Remove repeating groups; each attribute contains atomic (single) values only.

Second Normal Form (2NF): Must be in 1NF; all non-key attributes fully depend on the entire primary key.

Third Normal Form (3NF): Must be in 2NF; remove transitive dependencies (non-key attributes should depend only on the primary key).

Worked Example: Normalisation

UNF Table (not normalised):

1NF: Split multiple values into separate rows

2NF: Ensure full dependency on primary key (decompose if needed)

3NF: Remove transitive dependencies (e.g., dept_name depending on dept_id, which depends on emp_id)

SQL is a standard language for managing relational databases. Categories include:

Data Definition Language (DDL): CREATE (new database/table), ALTER (modify structure), DROP (delete structure), TRUNCATE (delete all rows)

Data Manipulation Language (DML): INSERT (add records), UPDATE (modify records), DELETE (remove records)

Data Query Language (DQL): SELECT (retrieve data)

Data Control Language (DCL): GRANT (give privileges), REVOKE (remove privileges)

Transaction Control Language (TCL): COMMIT (save changes), ROLLBACK (undo changes), SAVEPOINT (partial rollback)

Worked Example: SQL Commands

-- Create table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT
);

-- Insert data
INSERT INTO Students VALUES (1, 'Juma', 'Mwakidudu', 18);

-- Query data
SELECT * FROM Students WHERE Age > 17;

-- Update data
UPDATE Students SET Age = 19 WHERE StudentID = 1;

-- Delete data
DELETE FROM Students WHERE StudentID = 1;

NoSQL (Not Only SQL) databases handle unstructured and semi-structured data without fixed schemas. Types include:

• Document Databases: Store JSON-like documents (e.g., MongoDB)
• Key-Value Stores: Simple key-value pairs (e.g., Redis)
• Wide-Column Stores: Large datasets with variable columns (e.g., Cassandra)
• Graph Databases: Store relationships between entities (e.g., Neo4j)

NoSQL advantages: horizontal scaling, flexible schemas, handling large volumes of unstructured data. Limitations: weaker consistency guarantees, less mature ecosystem.

Big Data refers to extremely large, complex datasets that traditional databases cannot handle efficiently. Characteristics (the 5 Vs):

• Volume: Large quantities of data
• Velocity: High speed of data generation
• Variety: Different data types (structured, semi-structured, unstructured)
• Veracity: Data quality and accuracy
• Value: Extracting useful insights

Big Data technologies use distributed processing and storage to analyse massive datasets for patterns and insights.

A data warehouse is a specialised system for analytical processing and reporting. Unlike operational databases focused on transactions, data warehouses:

• Store historical data from multiple sources
• Are subject-oriented (organised around business subjects)
• Integrate and cleanse data for consistency
• Support complex queries and data mining
• Enable trend analysis and decision-making

In Tanzania, a supermarket chain like Shoprite or local markets in Kariakoo uses relational databases to track inventory, sales, and supplier information. For example, when a customer buys items worth TZS 150,000, the point-of-sale system updates the inventory table, records the transaction in the sales table, and generates a receipt—demonstrating how databases manage real-world transactions efficiently.