IB Computer Science Topic A3.2.1: Database Schemas Notes

Database Schemas: The Blueprint Before Any Data Exists

1. A database schema is the structure of a database: the tables it contains, the attributes those tables hold, the relationships between them, and the constraints the data must obey.
2. It is written before any records are entered, so a schema is a design document rather than a store of information.
3. One database has three schemas at once, each describing the same design at a different level of abstraction.

The Three Levels of Abstraction: From Idea to Stored Bytes

1. Conceptual Schema
   1. The highest level, holding only the entities and the relationships between them.
2. Logical Schema
   1. The middle level, which adds every attribute plus the primary keys and foreign keys that implement each relationship.
3. Physical Schema
   1. The lowest level, which adds data types, indexes and storage structures for one specific database management system (DBMS).

Conceptual Schema: Entities and Relationships, Nothing Else

1. The conceptual schema names every entity the database needs and states how those entities are related.
2. It leaves out attributes, keys and data types so that someone who does not write SQL can still check the design.
3. It is normally drawn as an entity relationship diagram at conceptual level.

Logical Schema: Attributes and Keys Make the Design Buildable

1. The logical schema lists the attributes of every entity and picks a primary key for each one.
2. Each relationship named in the conceptual schema becomes a concrete foreign key sitting in one of the tables.
3. It stays independent of any particular DBMS, so the same logical schema can be built in MySQL, PostgreSQL or SQL Server without change.

Physical Schema: The Design as One DBMS Will Store It

1. The physical schema fixes a data type and a size for every attribute.
2. It adds indexes, file organisation and storage settings, which together decide how fast queries actually run.
3. These choices are DBMS-specific, so two physical schemas built from one logical schema can look quite different from each other.

Comparing the Three Levels Side by Side

What the Three Schemas Are Used For

1. Guiding the Build
   1. Each level constrains the next, so a missing entity spotted at conceptual level never reaches the stored data.
2. Protecting Consistency
   1. Declaring keys and constraints in the schema is what lets the DBMS reject a record that breaks the design, instead of relying on every program that writes to the database.
3. Supporting Communication
   1. A head of department can read the conceptual schema and spot a wrong relationship, while a developer works from the physical schema to write the CREATE TABLE statements.