The relational model and Codd's rules
Clear, exam-oriented notes on the relational model (relations, schemas, tuples, domains, keys) and Codd's rules with exact definitions, formal conditions, worked model answers, common exam mistakes and quick checks.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
Two or three sentences: Database engineers use the relational model to design, implement and reason about structured data stored in RDBMSs (Relational Database Management Systems). Understanding Codd's rules helps you judge whether a DBMS truly follows the relational principles and why relational design (keys, domains, constraints) ensures data integrity and predictable query behaviour.
Key ideas
Start from first principles and simple language. Define each term before use.
Domain — a set of allowable atomic values for an attribute (for example, the domain of Age might be the integers 0..150). Use domains to control type and allowed values.
Attribute — a named property (column) of an entity, with an associated domain. Define an attribute before you use it; e.g., attribute
EmpIDhas domain integers.Tuple — a collection of attribute–value pairs (one per attribute) that conforms to attribute domains. In table language, a tuple is a row; the order of attributes and rows has no significance.
Relation schema — notation
R(A1, A2, ..., An)whereRis the relation name andA1..Anare attributes with specified domains. Example:Student(USN: integer, Name: string, Dept: string).Relation instance — a finite set of tuples that conform to a relation schema. It is the actual table contents at a point in time.
Relation (mathematical) — a relation instance is a subset of the Cartesian product of the domains:
R ⊆ D1 × D2 × ... × Dn. State this when asked for formal definition.Degree — the number of attributes in a relation schema (n above). Also called arity.
Cardinality — the number of tuples in a relation instance (|R|).
Superkey — a set of attributes
K ⊆ {A1..An}such that in every legal relation instancerof schemaR, no two distinct tuples agree on all attributes ofK. Formally: for every instancer, ∀t1,t2 ∈ r, if t1[K] = t2[K] then t1 = t2.Candidate key — a superkey that is minimal (no proper subset of it is a superkey).
Primary key — one chosen candidate key to be the main identifier for the relation; the DB designer selects it.
Foreign key — an attribute (or set of attributes) in relation
R1that references a candidate key in relationR2to express a referential constraint. State referential integrity: every non-null foreign-key value must appear as a candidate-key value in the referenced relation instance.
Exact statements universities commonly ask for
Formal definition of a relation instance: Draw and label a diagram: draw a rectangle labelled
Rwith columnsA1, A2, ..., Anand show sample rows. Under it write the set notationR ⊆ D1 × D2 × ... × Dnand explain eachDiis the domain ofAi.Formal uniqueness (key) condition derivation: Start from definition of superkey and show minimality gives candidate key. Step-by-step: (a) define function projection πK(t) that picks K attributes from tuple t; (b) show uniqueness means πK is injective on r; (c) minimality means removing any attribute from K makes projection non-injective for at least one legal instance.
Codd's rules (principles) — present exact short statements and intent. Codd proposed a set of rules describing what a DBMS must provide to be called truly relational. These rules are commonly given together; list and explain each briefly (see below in exam model answers). When writing them, give both the short rule name and a one-line explanation of intended behaviour.
Codd's rules (principle list and intent) you should memorise and be able to explain and give examples for:
- Foundation rule (Rule 0): A DBMS that is relational must be able to manage databases entirely through its relational capabilities. (Often presented as rule 0 or the Foundation Rule.)
- Rule 1: Information rule: All information in the database must be represented explicitly at the logical level and in exactly one way — as values in relations.
- Rule 2: Guaranteed access rule: Every single data element (atomic) is guaranteed to be logically accessible by using a combination of relation name, primary key value, and attribute name.
- Rule 3: Systematic treatment of null values: The DBMS must support NULL markers (missing, unknown, and inapplicable) and treat them consistently for data types and operations, with a well-defined semantics.
- Rule 4: Active online catalog based on the relational model: The DBMS should provide its catalog (metadata) as relations so that authorized users can query metadata using the same relational language.
- Rule 5: Comprehensive data sublanguage rule: The system must support at least one language that has a linear syntax and can express data definition, data manipulation (both interactive and programmatic), integrity constraints, and transaction control.
- Rule 6: View updating rule: All views that are theoretically updatable should be updatable by the system.
- Rule 7: High-level insert, update, delete: The DBMS must support set-oriented insert, update, delete operations (not just row-by-row) through the relational language.
- Rule 8: Physical data independence: Changes in physical storage structures or access methods must not require changes to application programs.
- Rule 9: Logical data independence: Changes to the logical schema (e.g., adding a new attribute) should not require application changes that do not use the changed structures.
- Rule 10: Integrity independence: Integrity constraints must be definable in the relational language and stored in the catalog, not embedded in application code.
- Rule 11: Distribution independence: The user should not have to know whether data is distributed across multiple locations.
- Rule 12: Non-subversion rule: If the system provides a low-level (procedural) interface, it must not be able to bypass the integrity rules expressed in the relational language.
(When writing these in an answer, give examples: e.g., Rule 4 means system tables like INFORMATION_SCHEMA in SQL; Rule 5 refers to SQL; Rule 3 discuss three-valued logic in SQL and its caveats.)
Assumptions to state in derivations: assume finite relation instances, attributes have atomic domains (no nested relations unless using extended models), and tuples are unordered (order of rows doesn't matter) and attributes are named and unordered.
Formulas
Each formula on its own line; explain symbols.
r ⊆ D1 × D2 × ... × Dn — r is a relation instance, Di is the domain of attribute Ai. Applies always to relational instances.
|r| = number_of_tuples_in_r — cardinality: the count of tuples.
degree(R) = n — number of attributes (arity) of relation schema R.
K ⊆ {A1,..,An} is a superkey iff for every legal instance r of R, ∀t1,t2 ∈ r, t1[K] = t2[K] ⇒ t1 = t2 — uniqueness condition.
K is candidate key ⇔ K is a superkey ∧ ∀K' ⊂ K, K' is not a superkey — minimality condition.
FK ⊆ Attr(R1) references CK ⊆ Attr(R2) (a candidate key) ⇒ ∀t ∈ r1, if t[FK] is not null then ∃s ∈ r2 with s[CK] = t[FK] — referential integrity condition.
Note: there are no SI units for these symbols; they are relational definitions.
Worked examples
(Provide model-exam answers; be precise and formal.)
Example 1 — Keys and foreign keys (exam-style answer)
Question: Given relation schemas Student(USN, Name, Dept, Phone) and Enroll(USN, CourseID, Semester, Grade). State domains, identify keys and foreign keys, and write the formal referential integrity condition.
Answer (model):
- Domains (state explicitly):
USN: string (unique student number),Name: string,Dept: string,Phone: string or integer as chosen,CourseID: string,Semester: string,Grade: string. - Keys: For
Student, candidate key is{USN}(assuming USN uniquely identifies students). ForEnroll, candidate key could be{USN, CourseID, Semester}because the same student can enroll in many courses and semesters; this combination uniquely identifies an enrollment tuple. - Foreign key: In
Enroll, attributeUSNis a foreign key referencingStudent(USN). - Referential integrity formal statement: For every legal instance rStudent and rEnroll, ∀t ∈ rEnroll, if t[USN] ≠ NULL then ∃s ∈ rStudent such that s[USN] = t[USN].
Example 2 — Short model answer: Explain Codd's Rule 4 and give an example Question: Explain the meaning of Codd's Rule 4 (catalog as relations) and give an example from SQL.
Answer (model):
- Rule statement: The DBMS must present its catalog (metadata) as relations so that users can query metadata using the same relational language used for data.
- Meaning: Metadata such as relation names, attribute names, types, constraints must be available as relational tables, not hidden.
- Example in SQL:
INFORMATION_SCHEMA.TABLESandINFORMATION_SCHEMA.COLUMNSare relational views that hold metadata. You can run SQL queries such asSELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'public'to list tables. This satisfies Rule 4 because the catalog is queryable via the same language.
Example 3 — Formal uniqueness proof sketch (short exam proof) Question: Prove that if K is a candidate key, then projection πK is injective on any instance r.
Answer (model): By definition, K is a superkey, so for any t1,t2 ∈ r, if t1[K] = t2[K] then t1 = t2. The projection πK maps each tuple to its K-values. Injectivity of πK means that πK(t1) = πK(t2) ⇒ t1 = t2, which is exactly the superkey uniqueness condition. Since K is minimal, no subset of K has this property, proving minimality too.
Common mistakes
- Calling every superkey a key: must check minimality to call it a candidate key.
- Misstating Codd's rules count or exact wording — some sources add a Foundation Rule (Rule 0); state which convention you use.
- Assuming SQL implements all of Codd's rules fully — SQL implements many but has known departures (e.g., SQL's three-valued logic for NULLs has practical differences from pure relational semantics, and some view update situations are not automatically handled).
- Forgetting to state domains when defining relations; examiners look for domains to avoid multivalued or nested attributes.
For your exam
Kinds of questions and what full-marks answers include:
- Definitions: define relation, tuple, attribute, domain, schema, instance, degree, cardinality, superkey, candidate key, primary key. Give formal uniqueness condition for keys and minimality.
- Short notes: explain Codd's rules — give rule statement and one-sentence explanation and an example per rule.
- Derivations: prove uniqueness implies injective projection; show minimality argument for candidate keys.
- Diagram questions: draw a table for a relation instance (label columns with attribute names and domains) and show how to identify keys; describe the diagram as a rectangle with column headings and sample rows.
- Programs/SQL: you may be asked to write SQL to create tables with primary and foreign keys and to query the catalog (e.g., query INFORMATION_SCHEMA). Provide exact SQL statements.
- Evaluation/essay: given a DBMS feature list, argue whether it meets particular Codd rules and give reasons.
Full-marks answers must: use precise definitions, show formal conditions when required, give examples, and state assumptions (atomic domains, finite instances). If asked about Codd's rules, list the rule number, short name, one-line intent and a real example.
Quick check
- What is the formal mathematical definition of a relation instance for schema
R(A,B)? - State the uniqueness condition that defines a superkey K.
- Give one reason why SQL's handling of NULL can violate a pure relational rule.
Answers: 1) R ⊆ D(A) × D(B) where D(A), D(B) are domains; 2) ∀t1,t2 ∈ r, t1[K] = t2[K] ⇒ t1 = t2; 3) Because SQL uses three-valued logic for NULL which makes equality and predicate evaluation different from pure relational (complete) semantics and complicates Rule 3 (systematic treatment of nulls).
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?