A relational schema is the implementation-focused diagram of a Relational database. It comes one step after the higher-level Entity-relationship diagram (ER diagram) and one step before the actual SQL CREATE TABLE statements.
Each entity becomes a box listing its columns. The Primary key is underlined (it uniquely identifies a row). Foreign keys are marked (they point to rows in other tables). Lines between boxes show relationships, and the decorations on the lines tell us what kind of relationship.
Take a small academic example. Three entities: students, majors, supervisors.
- A student has an
id, a first name, a family name, a phone number, a major, and a supervisor. - A major has a code, a name, a length in years, and a credit count.
- A supervisor has a code, a name, a degree, an affiliation, and an address.
The schema has three boxes. student_id is underlined in Students (its primary key); major_code is underlined in Majors; supervisor_code is underlined in Supervisors. The Students box also contains major_code and supervisor_code as foreign keys — they aren’t unique in Students; they’re references into the other tables.
Two pieces of vocabulary describe how a relationship between two tables behaves.
Direction. Read from Majors to Students: one major can have many students — a one-to-many relationship. Read from Students to Majors: each student has exactly one major — a many-to-one relationship. It’s the same arrow drawn between the same boxes; what changes is which side we read from.
Participation. Whether a row in one table must have a corresponding row in the other.
- Mandatory participation: an entity must be associated with at least one record in the related table — every major must have at least one student, say.
- Optional participation: it doesn’t have to — a student might exist without a declared major, in which case
major_codefor that student would beNULL.
These two are independent. It’s perfectly possible to have one major can have many students (mandatory) in one direction and each student has at most one major (optional) in the other.