Database Schema

Levels of Database Schema

Conceptual Schema

The conceptual schema represents a high-level view of the data. It defines entities, relationships, and constraints without any concern for how data is implemented in tables or stored on disk.

Example: An ER diagram showing entities like Student, Course, and Instructor, with relationships such as "enrols in" or "teaches".

Logical Schema

The logical schema translates the conceptual design into a set of normalised tables, fields, and relationships. It provides a blueprint of how data is logically organised for query and manipulation.

Example: A table Students with fields like StudentID, Name, and DateOfBirth, linked via foreign keys to other tables like Enrolments.

Physical Schema

The physical schema describes how data is actually stored on disk. It includes data types, indexes, partitions, and file structures that affect performance and storage efficiency.

Example: A university database stores student records in physical data files on the server. Indexes are created on StudentID and LastName to speed up search queries. The physical schema also defines how data might be compressed.

Why Are Database Schemas Important?

• Data Organisation: Provides a clear and consistent structure for managing data.
• Scalability: A well-designed schema supports database growth without performance loss.
• Consistency and Integrity: Ensures data rules are enforced through keys and constraints.
• Security and Performance: Enables optimised access control and query efficiency.