Database schema
A schema is the structure or blueprint of a database.
Defines how data is organised and how relationships are managed.
Types of Schema
Physical Schema – How data is stored on disk.
Logical Schema – structure of the database (tables, relationships)
View Schema – customised views for users.
Data Model
A data model is a framework for organizing data.
Helps represent data, relationships, and constraints.
Three main components:
Structural – defines organization of data
Manipulative – rules for accessing/updating data.
Constraints – restrictions on valid data.
Function of a Multi-user DBMS
Provides services to manage concurrent users.
Key function:
- Concurrency control
- Backup and recovery
- Security and access control
- Data integrity and consistency
- Transaction management
Standalone PC DBMS ( Function Not required)
Used by a single user, simpler than multi-user DBMS.
Not required:
Concurrency control
complex security/user accounts
multi-user transaction locking
focus: Simple data storage, retrieval and updates
System Catalogue
A system catalogue is a repository of metadata (data about data).
Functions:
- Stores table definitions, columns, indexes, constraints
- Supports query optimization
- Helps enforce data integrity and security
Benefits:
improves efficiency, consistency and management of DB
DDL VS DML
DDL (Data Definition Language): defines database structure.
Examples: CREATE, ALTER, DROP.
DML (Data Manipulation Language): Manipulates the data.
Examples: INSERT, UPDATE, DELETE, SELECT.
Key difference: DDL changes schema; DML changes data.
Record-based Data Models
Three main record-based models:
Hierarchical – data organised in tree structure
Network – data organised in graph structure
Relational – data organised in tables with rows/columns
Differences:
flexibility, complexity, and ease of use vary
relational is most widely used due to simplicity.