Relational Data Model

Syllabus tag: Paper-II, Section-B, Topic IV.2 — Relational Data Model & Relational Database Constraints

Definition

The Relational Model (E. F. Codd, 1970) represents data as a collection of relations (tables). Each relation has a fixed set of attributes (columns) and a variable set of tuples (rows). It is grounded in set theory and first-order predicate logic.

Core Terminology

• Relation: a table with a unique name
• Tuple: a row — represents one entity instance
• Attribute: a column — a named property with a domain (set of allowed values)
• Degree: number of attributes in a relation
• Cardinality: number of tuples in a relation (changes as rows are added/removed)
• Relation Schema: the structure — name + attribute list, e.g. Student(RollNo, Name, Dept)
• Relation Instance: the actual set of tuples in the relation at a point in time

Keys

• Super Key: any attribute set that uniquely identifies a tuple (may contain extra, non-essential attributes)
• Candidate Key: a minimal super key — no attribute can be removed without losing uniqueness
• Primary Key: the candidate key chosen by the designer to uniquely identify tuples; cannot be NULL
• Alternate Key: a candidate key not chosen as primary key
• Foreign Key: an attribute (or set) in one relation that references the primary key of another (or the same) relation
• Composite Key: a key made of two or more attributes together

Relational Database Constraints

• Domain constraint: every attribute value must belong to its declared domain (type/range)
• Key constraint: no two tuples may have identical values for a candidate key
• Entity Integrity constraint: no primary key attribute may be NULL
• Referential Integrity constraint: a foreign key value must either match an existing primary key value in the referenced relation, or be entirely NULL
• General/semantic constraints: business rules beyond the above, enforced via CHECK, NOT NULL, UNIQUE, triggers (e.g., "salary must be positive")

Codd's 12 Rules (DBMS Codd's Rules)

E. F. Codd published these in 1985 as the test of whether a DBMS is truly relational rather than merely storing data in tables. They are numbered 1–12 plus a Rule 0 foundation rule.

• Reality check worth stating in an answer: no commercial DBMS satisfies all 12 fully. Rule 6 (view updating) and Rule 11 (distribution independence) are the most commonly violated; Oracle and SQL Server satisfy roughly 8–10 in practice. A product meeting most of them is called fully relational; one meeting only a few is semi-relational (or, like early products, merely tabular).

Numerical — Set Operations on Relations (FPSC 2018, Paper-II, Q5)

Given relations R with M tuples and S with N tuples, same degree/compatible schema, M > N > 0:

• CSS angle: this exact reasoning-under-constraints style question (not a fixed formula, but min/max bounds) is a favorite FPSC trap — always justify why, not just state the bound.

MCQ Trap

• A Primary Key cannot be NULL (Entity Integrity); a Foreign Key CAN be NULL (unless separately constrained NOT NULL).
• Every Candidate Key is a Super Key, but not every Super Key is a Candidate Key (super keys need not be minimal).
• Cartesian Product size is always exactly M×N — it is the only operation in the table above with no min/max range.

CSS Exam Angle

• Super/Candidate/Foreign key distinctions (2018) and constraint definitions are frequent 6-mark short-answer material.
• Relational algebra set-operation sizing (2018) tests whether you understand when union/intersection/difference degenerate to their bounds, not just their formulas.

Likely Questions

1. Define relation, tuple, attribute, degree, and cardinality with a small example table.
2. Differentiate between super key, candidate key, primary key, and foreign key.
3. Explain the four types of relational integrity constraints with an example violating each.
4. Given |R| = M and |S| = N tuples (M > N > 0), find the minimum and maximum possible sizes of R∪S, R∩S, R−S, and R×S.

---

📘 Enrichment — Worked Examples

Worked Example 1 — Identify Every Kind of Key

Student(RollNo, CNIC, Email, Name, DeptID) — RollNo, CNIC and Email are each unique; DeptID references Department(DeptID).

• Counting super keys (numerical trick): if a relation has n attributes and a single candidate key K of one attribute, every set containing K is a super key → 2^(n−1) super keys. For R(A,B,C,D) with key A: 2³ = 8 super keys.
• With two single-attribute candidate keys A and B in R(A,B,C,D): super keys = sets containing A or B = 2³ + 2³ − 2² = 8 + 8 − 4 = 12 (inclusion–exclusion).

Worked Example 2 — One Violation for Each Constraint

Tables: Department(DeptID PK, DName) = {(10, CS), (20, EE)}; Student(RollNo PK, Name, Age CHECK Age ≥ 16, DeptID FK).

Enforcing Constraints in SQL (write this in answers — examiners like code)

CREATE TABLE Department (
    DeptID  INT PRIMARY KEY,
    DName   VARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE Student (
    RollNo  INT PRIMARY KEY,                    -- key + entity integrity
    CNIC    CHAR(13) UNIQUE,                    -- alternate key
    Name    VARCHAR(50) NOT NULL,
    Age     INT CHECK (Age >= 16),              -- domain / semantic
    DeptID  INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
        ON DELETE SET NULL                      -- referential action
        ON UPDATE CASCADE
);

Referential Actions (what happens when a referenced row changes)

• RESTRICT / NO ACTION: reject the delete/update of the parent row (the default).
• CASCADE: delete/update the matching child rows too (e.g., deleting an Order deletes its OrderLines).
• SET NULL: set the child's FK to NULL (e.g., a student survives after their department is closed).
• SET DEFAULT: set the child's FK to a declared default value.

Worked Numerical — Set-Operation Sizes with Numbers

• R ∪ S: minimum 8, maximum 13
• R ∩ S: minimum 0, maximum 5
• R − S: minimum 3, maximum 8
• R × S: exactly 40 tuples; if R has degree 3 and S degree 4, R × S has degree 7.

---

✍️ Model Answers — Likely Questions

Q1. Define relation, tuple, attribute, degree and cardinality with an example.

Use one small table and label it:

• Relation: the whole table Student.
• Tuple: one row, e.g. (101, Ali, Lahore).
• Attribute: one column, e.g. City, with domain = set of Pakistani city names.
• Degree = number of attributes = 3; Cardinality = number of tuples = 2.
• Properties of a relation: no duplicate tuples, tuple order irrelevant, attribute order irrelevant, every cell atomic.

Q2. Differentiate super, candidate, primary and foreign keys.

• Super key: any attribute set that uniquely identifies a tuple — may carry extra attributes ({RollNo, Name}).
• Candidate key: a minimal super key ({RollNo}, {CNIC}).
• Primary key: the one candidate key chosen; unique and NOT NULL (RollNo).
• Foreign key: attribute(s) referencing another relation's PK, linking tables (Student.DeptID → Department.DeptID); may be NULL and may repeat.
• Relationship: every candidate key is a super key; every primary key is a candidate key; the reverse is not true.

Conclusion: keys enforce uniqueness (super/candidate/primary) and relationships (foreign).

Q3. Explain the relational integrity constraints with a violation of each.

Answer with the four headings — Domain, Key, Entity integrity, Referential integrity (plus semantic/CHECK) — and use the violation table from Worked Example 2 above. Close with the SQL DDL block showing how each is declared, and mention the four referential actions (RESTRICT, CASCADE, SET NULL, SET DEFAULT).

Q4. Given |R| = M and |S| = N (M greater than N greater than 0), find min/max sizes of R∪S, R∩S, R−S, R×S.

• R ∪ S: min M (when S ⊆ R), max M + N (disjoint).
• R ∩ S: min 0 (disjoint), max N (S ⊆ R).
• R − S: min M − N (S ⊆ R), max M (disjoint).
• R × S: always M × N; degree adds.

Always state the condition producing each bound — the marks are for the reasoning. Then illustrate with numbers (M = 8, N = 5 → 8/13, 0/5, 3/8, 40).

---

⚡ 60-Second Revision

• Degree = columns (fixed); Cardinality = rows (changes).
• Super ⊇ Candidate ⊇ Primary; Alternate = Candidate − Primary.
• PK never NULL; FK can be NULL.
• Four constraints: Domain, Key, Entity integrity, Referential integrity.
• Codd: Rule 0 + 12 rules; no product satisfies all.

📜 Past-Paper Questions & MCQs (CSS 2016–2026)

2018 · Paper-II · Q5(B) · 6 marks

Q. Elaborate the concepts of super key, candidate key and foreign key with examples.

Ans. (Example table: Student(RegNo, CNIC, Name, DeptID).)

• Super key: any set of attributes that uniquely identifies a row. {RegNo}, {CNIC}, {RegNo, Name} and {RegNo, CNIC, Name} are all super keys.
• Candidate key: a minimal super key (no unnecessary attribute). {RegNo} and {CNIC} are candidate keys, and {RegNo, Name} is not (Name is extra).
• Primary key: the candidate key chosen to identify rows (for example RegNo). It cannot be NULL.
• Foreign key: an attribute (or set) in one table that refers to the primary key of another table, enforcing referential integrity. DeptID in Student references Department(DeptID), so a student cannot belong to a non-existent department.
• Relationship: every candidate key is a super key, but not every super key is a candidate key.