LEC2 COMP508: The Relational Model

The Relational Model Overview

• Provides a logical view of data and its relationships.

• Devised by Edgar F. Codd in 1969.

Components of the Relational Model

• Data Structure: Tables (relations), rows (tuples), columns (attributes).

• Data Manipulation: Powerful SQL operations for retrieval and modification.

• Data Integrity: Mechanisms for implementing business rules.

Relations

• A named, two-dimensional table of data.

• Composed of tuples (rows) and attributes (columns).

• Properties/Requirements:

  • Unique name.

  • Every attribute value must be atomic.

  • Every row must be unique.

  • Attributes (columns) must have unique names.

  • Order of columns and rows is irrelevant.

Relational Keys

• Primary Key: Attribute(s) that uniquely identify each row in a relation.

  • A relation has one primary key.

  • A composite key consists of $>1$ attributes.

• Foreign Key: Attribute(s) in a relation that serves as the primary key of another relation.

  • Enables a dependent relation (many side) to refer to its parent relation (one side).

Integrity Constraints

• Domain Constraints: Define allowable values for an attribute.

• Entity Integrity: No primary key attribute may be null; all primary key fields must contain data values.

• Referential Integrity: Rules that maintain consistency between related tables.

  • A foreign key value must match a primary key value in the referenced table OR be null.

  • Delete Rules: Restrict, Cascade, Set-to-Null.

Relational Set Operators (Relational Algebra)

• Defines theoretical ways to manipulate table contents; produces new relations from existing ones.

• Selection (Restriction): $\sigma_{predicate}(R)$

  • Returns tuples (rows) from relation $R$ that satisfy a specified condition.

• Projection: $\Pi<em>{col</em>1, \dots, col_n}(R)$

  • Returns a vertical subset of relation $R$, extracting specified attributes and eliminating duplicates.

• Cartesian Product: $R \times S$

  • Concatenates every tuple of relation $R$ with every tuple of relation $S$.

• Join: Combines Cartesian product and selection; allows information to be combined from two or more tables.