Week 2
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.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. |