The Relational Data Model

Page 1: Title and Lecturers

• Course: The Relational Data Model (SWEN304/SWEN435, Trimester 1, 2026)
• Lecturers: Dr Hui Ma, Dr Felix Yan

Page 2: Outline

• Basic terms: Schema, Attribute, Domain, Relation, Tuple
• Database schemas and instances
• Integrity constraints: Domain, Attribute, Key, Unique, and Interrelation constraints
• Constraint violations during database updates

Page 3: Pre-Relational Database Systems

• Systems: Network and hierarchical (emerged late 1960s).
• Deficiencies: Complex structures, no logical-physical separation (program-data dependency), and navigational programming (low productivity).

Page 4-5: The Relational Model of Data (RDM)

• Introduction: Proposed by E. F. Codd in 1970.
• Concept: Database as a collection of relations (mathematical sets of tuples).
• Benefits: Logical treatment of data, physical data independence (hiding storage/pointers), and declarative language for querying.

Page 6: Relation Schema

• Notation: $N(A_1 : D_1, \dots, A_n : D_n)$
• Components: $N$ (name), $A$ (attributes), $D$ (domains/valid values).
• Degree (Arity): The number of attributes $n$.

Page 7: Attribute

• Represents a property of objects in the Universe of Discourse (UoD).
• Interprets the meaning of data elements.

Page 8: Domain

• Definition: $dom(A) = D$.
• Specified via basic data types (e.g., STRING) or type specifications with constraints (e.g., CourseIdDom).

Page 9: Tuple

• Notation: $t = <v_1, \dots, v_n>$ or $t = {(A_1, v_1), \dots, (A_n, v_n)}$.
• Represents an ordered list of values or (attribute, value) pairs; contains null values ($\omega$) if necessary.

Page 10: Relation

• A relation $r$ over set $R$ is a finite set of $n$-tuples.
• Table notation: Attributes as column headers, tuples as rows (row order is unimportant).

Page 11-12: Relation Schema and Instances

• Instance: $r(N)$ is a state of the schema satisfying all constraints.
• Relational Variable: $\rho(N)$ represents the placeholder for the current instance at any time.

Page 13: Questions & Discussion

• Q: How many relations can be built using subsets of a set of 3 tuples?   - A: $2^3 = 8$.
• Q: How many relations using subsets of 100 tuples?   - A: $2^{100}$.

Page 14-15: Restrictions (Projections)

• Tuple Restriction: $t[A_k, \dots, A_m]$ is a sublist of values from specific attributes.
• Relation Restriction: $r(N)[X] = {t[X] \mid t \in r}$.

Page 16-17: Definition Summary

• Relation state $r(N) \subset dom(A_1) \times dom(A_2) \times \dots \times dom(A_n)$.
• Example: With domains of size 2 and 3, there are $2 \times 3 = 6$ possible combinations in the cross product.

Page 18: Exercises

• 1a: For 4 attributes with 100 values each: $100^4$ records.
• 2: For 2 values per attribute and 4 attributes, there are $2^4 = 16$ unique tuples; therefore, $2^{16} = 65536$ possible instances.

Page 19-20: Constraints

• Essential for ensuring data is meaningful and reflects real-world rules (UoD).
• Types: Domain, Attribute, Key (Entity Integrity), Unique, and Referential Integrity.

Page 21: Domain Constraint

• Format: Domain_Name(Basic data type, Max length, Condition).
• Example: $Age(Integer, 0 < d < 150)$.

Page 22-23: Attribute Constraint

• Defined as $(Dom(N, A), Range(N, A), Null(N, A))$.
• Specifies the associated domain, any further range restrictions, and nullability.

Page 24-26: Keys and Entity Integrity

• Relation Schema Key: A subset of attributes $X$ that is unique, minimal, and contains no nulls.
• Primary Key ($K_p$): The designated key for identification.
• Entity Integrity: No primary key values can be null.
• Superkey: A superset of a minimal key.
• Unique Constraint: Requires unique values but allows nulls.

Page 27-28: Key Examples

• In GRADES table: Key is $(Id + Course_id)$ if one grade per course.
• Key is $(Id + Course_id + Term)$ if multiple terms allowed.

Page 29-31: Database Schema Foundations

• Single relations are inadequate; database schemas contain multiple relations linked via foreign keys to handle multi-valued properties and interactions.

Page 32: Relational Database Schema

• Defined as $N(S, IC)$, where $S$ is a set of relation schemas and $IC$ is a set of interrelation constraints.

Page 33-34: Foreign Key (FK)

• A subset of attributes $Y$ in relation $N_2$ referencing primary key $X$ in $N_1$.
• Requires domain and attribute compatibility.

Page 35-36: Referential Integrity

• Notation: $N_2[Y] \subseteq N_1[X]$.
• Rule: Non-null values in the foreign key must exist in the referenced primary key.
• Either $u[Y] = v[X]$ or at least one attribute in $Y$ is null.

Page 37-40: Referential Integrity Details

• Critical subset of $IC$.
• Pitfall: A composite foreign key $[A, B]$ referencing $[A, B]$ is not the same as two separate constraints on $A$ and $B$.

Page 41: Other Constraints

• Semantic Integrity: Application-specific rules (e.g., enrollment limits).
• Handled via triggers or ASSERTIONS in SQL-99.

Page 42-45: Database Instances

• A database instance is a set of relation instances satisfying all $IC$.
• Example: BOOKSHOP database with SUPPLIER, ARTICLE, and OFFER relations.

Page 46-48: Inferring Key Constraints

• Procedure:   1. Produce power set of attributes.   2. Check subsets starting from lowest cardinality.   3. If a subset satisfies uniqueness, its supersets need not be checked.

Page 49-53: Database Updates and Violations

• Operations: Insert, Delete, Modify.
• Responses to Violations:   - Reject: Disallow the operation.   - Cascade: Propagate changes to maintaining consistency.   - Set Null / Set Default: Adjust values to maintain integrity.

Page 54: Update Question

• Scenario: Updating TEXTBOOK where $PNum = 304$.
• DBMS evaluation depends on whether the update violates foreign key constraints against the COURSE table.

Page 55: Violation Summary Table

Page 56: Final Summary

• RDM relies on domains, attributes, and relations.
• Integrity is maintained through domain, attribute, key, and referential constraints.
• Referential integrity is the primary mechanism for linking relations.
• Update operations must preserve the database's consistent state.