Database Schema

Definition

A database schema is the complete structural design of an entire database: the set of ALL relation schemas it contains, together with the constraints that hold across them (primary keys, foreign keys, domain constraints, etc.). It is the intensional description of the whole database, independent of whatever data currently happens to be stored.

Example

A college database schema might include:

• Students(id, name, department, gpa)
• Courses(course_id, title, credits)
• Enrollments(student_id → Students.id, course_id → Courses.id, grade)

The database schema is all three relation schemas plus the two foreign-key relationships that link Enrollments to Students and Courses.

How this differs from Relation Schema / Database Instance

• A Relation Schema describes one relation; a Database Schema aggregates every relation schema in the system plus cross-relation constraints (foreign keys) that no single relation schema can express alone.
• Database Schema is "the design" (rarely changes); Database Instance is "the current data" (changes constantly with every transaction). This mirrors the Relation Schema vs. Relation distinction, just scaled up to the whole database.

Edge Cases

• A database schema is valid and complete even when every relation in it is empty (no instance data yet) — e.g., immediately after running all the CREATE TABLE statements.
• A migration (adding/dropping a table, adding a column, adding a foreign key) changes the database schema. Such changes are typically versioned and infrequent, unlike everyday CRUD operations, which only affect the instance.
• Circular foreign keys between two relation schemas (e.g., Departments.headid → Employees.id and Employees.deptid → Departments.id) are part of the database schema design and must be validated together, not per relation schema in isolation.

Key Takeaways / Q&A

Q: Does inserting 1,000 new student rows change the database schema? A: No — that changes the database instance; the schema (structure + constraints) stays exactly the same.

Q: Where do foreign key constraints conceptually "live" — in one relation schema, or the database schema? A: They are declared syntactically on a relation schema, but they express a relationship BETWEEN two relation schemas, so conceptually they belong to the overall database schema.