Understanding Database Schemas
- A database schema is a blueprint of a database, defining its structure, organization, and constraints.
- It serves as a model that guides the design and implementation of a database, ensuring that data is stored efficiently and consistently.
A database schema is a model detailing how the database has been set up, but the schema does not contain any actual data.
The Three Levels of Database Schemas
- Conceptual Schema
- Logical Schema
- Physical Schema
These schemas correspond to different levels of abstraction, each serving specific roles in database design and management.
Conceptual Schema
The conceptual schema provides a high-level view of the database, focusing on the entities and their relationships.
Key Features
- Entities and Relationships
- Identifies all entities (e.g., tables) and the relationships between them.
- Does not include attributes or constraints.
- Abstraction Level
- Used by users, administrators, and developers to understand the overall structure.
- Often represented using Entity-Relationship Diagrams (ERDs).
- Think of the conceptual schema as a blueprint for a building.
- It shows the rooms (entities) and hallways (relationships) but doesn't specify the furniture (attributes) or materials (data types).
Conceptual Schema for a School Database
- Entities: Student, Enrolment, Class, Teacher
- Relationships:
- One Student can have multiple enrolments
- One Enrolment can have one class, but a class can have multiple enrolments
- One teacher, teaches multiple classes
Logical Schema
The logical schema adds more detail to the conceptual schema by defining the attributes and keys for each entity.
Key Features
- Attributes and Keys
- Specifies attributes for each entity (e.g., FirstName, ClassName, EnrolmentID).
