Relational Data Model – Key Points

Relational Model Basics

• Proposed by Ted Codd (1970); based on set theory & first-order predicate logic.
• Data represented as a collection of relations (tables).
• Formal terms:
  • Row $\rightarrow$ tuple
  • Column $\rightarrow$ attribute
  • Table $\rightarrow$ relation
  • Attribute’s allowed values $\rightarrow$ domain

Structural Elements

• Domain: Named set of atomic values with a defined data type (e.g., \text{USA_phone_numbers}).
• Attribute $A<em>i$: Named role played by a domain in relation schema $R$; denoted $\text{dom}(A</em>i)$.
• Tuple: Mapping from attributes to domain values; describes an entity/relationship.
• Relation: Set of tuples with identical attribute set.
• Relation schema: $R(A<em>1,A</em>2,\dots ,A_n)$
  • Degree/arity $= n$ (number of attributes).
  • Example: $\text{STUDENT}(\text{Name},\text{Ssn},\dots,\text{Gpa})$.
• Relation state $r(R) = {t<em>1,t</em>2,\dots,t_m}$ at a given time.

Relation Characteristics

• No ordering among tuples (set semantics).
• Attribute order irrelevant if attribute–value pairing retained.
• Values are atomic; NULL indicates unknown / not applicable / undefined.
• Schema is an assertion (or predicate); each tuple is an instance satisfying it.

Relational Model Notation

• Relation names: uppercase $Q,R,S$; relation states: lowercase $q,r,s$.
• Tuple symbols: $t,u,v$.
• Dot notation: $R.A$ (e.g., $\text{STUDENT.Name}$).
• Tuple component: $t[A<em>i]$ or $t.A</em>i$ $

Constraints

• Classified as: inherent (model-based), schema-based (explicit), application-based (semantic).

Domain Constraint

• Each attribute value must belong to $\text{dom}(A)$ and be of correct data type.

Key Constraints

• Tuples must be unique.
• Superkey $SK$: attribute set with unique combination property.
• Key $K$: minimal superkey (no attribute removable).
• Candidate keys: all minimal keys of relation.
• Primary key: chosen candidate key; underlined in schema; $\text{NOT NULL}$ enforced.

Entity Integrity

• Primary key values $\neq$ NULL.

Referential Integrity

• Foreign key $FK$ in $R<em>1$ references primary key $PK$ of $R</em>2$ if:
  1. $\text{dom}(FK)=\text{dom}(PK)$
  2. Each $FK$ value is either NULL or exists as some $PK$ in $R_2$.
• $R<em>1$: referencing; $R</em>2$: referenced relation.

Other Constraints

• Semantic/business rules (e.g., salary $\le$ supervisor’s salary).
• Functional dependency: $X \rightarrow Y$ (value of $X$ determines unique $Y$).
• State constraints (static) vs. transition constraints (dynamic).

Relational Database Schema & State

• Database schema $S = {R<em>1,R</em>2,\dots,R_m}+IC$ (integrity constraints set).
• Database state $DB = {r<em>1,r</em>2,\dots,r_m}$ satisfies $IC$ (valid) or not (invalid).

Update Operations & Constraint Violations

• Insert, Delete, Update (Modify).

Insert

• May violate: domain, key, entity integrity, referential integrity.
• Default on violation: reject; DBMS may report cause or attempt correction.

Delete

• Can violate referential integrity (other tuples reference deleted tuple).
• Options: restrict (reject), cascade (propagate), set NULL / set default.

Update

• Change attribute values of selected tuples.
• Possible violations: domain, key (if changing PK), referential integrity (if PK/FK affected).

Transaction Concept

• Transaction: sequence of DB operations (retrievals + updates) forming an atomic unit.
• Must leave DB in a consistent (valid) state satisfying all constraints.