Week 2

Site:	Newgate University Minna - Elearning Platform
Course:	Data Management
Book:	Week 2

Printed by:	Guest user
Date:	Sunday, 14 June 2026, 4:01 AM

Description

Teaching and Learning Methods to be Employed

1. Illustrated lecture on DBMS architecture with layered diagrams

2. Lab session: Navigating MySQL Workbench schemas, tables, users

3. Demonstration: Data independence in practice

Learning Outcomes / Objectives

By the end of this week, students should be able to:

1. Identify and describe the components of a database system.

2. Explain the functions of a DBMS.

3. Describe the three-schema (ANSI/SPARC) architecture.

4. Distinguish between physical and logical data independence.

5. Explain the roles of DBA, database designer, and end-users.

2.1 Components of a Database System
2.2 The Three-Schema (ANSI/SPARC) Architecture
2.3 Data Independence
2.4 Database Languages

2.1 Components of a Database System

A complete database system consists of the following components:

Component	Description
Hardware	Physical computers, servers, storage disks, network infrastructure. E.g., the servers in a bank's data centre.
Software	The DBMS software itself (e.g., MySQL, Oracle), operating system, and application programs.
Data	The actual data stored (operational data) and the metadata (schema/catalogue describing the data).
Procedures	Rules and instructions for using and running the database (backup procedures, security policies).
People	Database Administrator (DBA), database designers, application developers, and end-users.

Functions of DBMS

A DBMS performs the following essential functions:

DBMS Function	Explanation
Data Definition	Allows users to define the database structure (schemas, tables, constraints) using a Data Definition Language (DDL).
Data Manipulation	Allows insertion, updating, deletion, and retrieval of data using a Data Manipulation Language (DML).
Data Security	Controls who can access what data. Implements authentication and authorisation.
Data Integrity	Enforces rules (constraints) to ensure data accuracy and consistency.
Concurrency Control	Manages simultaneous access by multiple users to prevent data corruption.
Backup and Recovery	Creates backups and restores the database to a consistent state after failures.
Data Dictionary Management	Maintains metadata (the catalogue) describing the structure of the database.
Transaction Management	Ensures that groups of operations either all succeed or all fail (atomicity).

2.2 The Three-Schema (ANSI/SPARC) Architecture

The ANSI/SPARC three-schema architecture provides a framework that separates the user's view of data from the physical storage of data. This separation is called data independence.

The three schemas (levels) are:

Schema Level	Also Called	Description
External Schema	View Level / User View	What each group of users sees. Different users may see different views of the same data. E.g., a bank teller sees customer balances; the auditor sees transaction logs.
Conceptual Schema	Logical Level	The complete logical structure of the database all tables, relationships, and constraints. Independent of physical storage. E.g., the complete bank database structure with all tables defined.
Internal Schema	Physical Level / Storage Level	How data is physically stored on disk, file organisations, indexes, storage structures. E.g., data stored in B-tree indexed files on SSDs in the data centre.

Three-Schema Analogy: Think of a Nigerian government office building. The INTERNAL level is the building's structure, beams, pipes, electrical wiring (users never see this). The CONCEPTUAL level is the official floor plan where each department is located. The EXTERNAL level is what each visitor sees the reception desk, their specific meeting room, each visitor has their own 'view' of the building.

2.3 Data Independence

Data independence is the ability to change the schema at one level without affecting the schema at the next higher level. There are two types:

Type	Definition and Example
Physical Data Independence	The ability to change the physical (internal) schema without affecting the conceptual schema. Example: Moving the bank's database from HDD to SSD storage, or changing the indexing strategy, without any change to the table structures or applications.
Logical Data Independence	The ability to change the conceptual (logical) schema without affecting the external schema or applications. Example: Adding a new column to a Customer table without breaking existing applications that do not use that column.

2.4 Database Languages

DBMS provides several languages for interacting with the database:

1. Data Definition Language (DDL) — Defines the database structure. Commands: CREATE, ALTER, DROP, TRUNCATE

2. Data Manipulation Language (DML) — Manipulates data. Commands: SELECT, INSERT, UPDATE, DELETE

3. Data Control Language (DCL) — Controls access. Commands: GRANT, REVOKE

4. Transaction Control Language (TCL) — Manages transactions. Commands: COMMIT, ROLLBACK, SAVEPOINT

SQL (Structured Query Language) is the industry-standard language that combines DDL, DML, DCL, and TCL into one integrated language. It is used by virtually all relational DBMS products.

Database Roles and People

Role	Responsibilities
Database Administrator (DBA)	Responsible for the overall health of the database — installation, configuration, performance tuning, security, backup/recovery, user management.
Database Designer	Designs the structure of the database — identifies entities, relationships, attributes, and constraints.
Application Developer	Builds applications that use the database. Writes SQL queries and application code.
Naive End User	Uses applications (like a bank teller's terminal) without knowing SQL or the database structure.
Sophisticated End User	Directly queries the database using SQL (e.g., a data analyst at Access Bank).

Reading List / References

Connolly, T. & Begg, C. (2015). Database Systems, Chapters 2 & 3: Database Architecture. Pearson.

Silberschatz, A. et al. (2020). Database System Concepts, Chapter 1: Overview. McGraw-Hill.

ANSI/X3/SPARC Study Group on DBMS (1975). Interim Report. FDT Bulletin, 7(2).

Activities

Self-Assessment Quiz: 1. Name the FIVE components of a database system. 2. What is the difference between the external and conceptual schema in the three-schema architecture? 3. Define physical data independence and give an example. 4. What is the difference between DDL and DML? Give ONE command from each.

Lab Exercise: Install MySQL Workbench (or use an online SQL tool like SQLFiddle). Create a new schema called 'university_db'. Create a simple STUDENT table with columns: student_id (INT, PRIMARY KEY), first_name (VARCHAR 50), last_name (VARCHAR 50), department (VARCHAR 50), level (INT). Insert 5 sample student records. Screenshot your work for submission.