Definition
Database Schema
A blueprint that outlines how data is organised within a database.
- To be more specific, a database schema defines the structure of the database including:
- Tables
- Fields
- Relationships
- Constraints
Note
- The schema acts as a logical framework that guides how data is stored, accessed, and managed.
Types of Schema
Conceptual Schema
- Purpose: Shows entities and which are linked.
- Includes: High-level entities (e.g., Student, Course) and their relationships (e.g., enrolls in).
- Use Case: Helps understand the overall structure of the database without focusing on implementation details.
Logical Schema
- Purpose: Conceptual + field descriptions, table descriptions, and foreign keys.
- Includes: Detailed table structures, field descriptions, and how tables relate via foreign keys.
- Use Case: Prepares the design for implementation in a DBMS while remaining system-agnostic.
Physical Schema
- Purpose: Logical + field names, data types, and table names, basically a working database schema
- Includes: Actual table and column names, data types, indexing, and storage-specific settings
- Use Case: Guides how data is stored and accessed on hardware, used for database implementation and optimization.
