Relational Model


A database model is a conceptual framework for database systems with 3 parts:

  • data structures
    • prescribe how data is organized
  • operations
    • manipulate data structures
  • rules
    • govern valid data

The relational model is a database model based on tabular data structure.

  • published in 1970 by E.F. Codd of IBM
    • released commercially in 1980
  • data structure, operations, and rules are standardized in SQL

Relational Data Structure

  • relational data structure is based on set theory
  • a set is an unordered collection of elements enclosed in braces
    • e.g., {a, b, c} = {c, b, a}
  • a tuple is an ordered collection of elements enclosed in parentheses
    • e.g., (a, b, c) ≠ (c, b, a)
  • organizes data in tables:
    • table
      • has a name, fixed tuple of columns, and varying set of rows
    • column
      • has a name and a data type
    • row
      • is unnamed tuple of values
        • each value corresponds to a column and belongs to the column’s data type
      • rows in a table have no inherent order
    • data type
      • is a named set of values, from which column values are drawn

Relational Operations

  • based on set theory
  • each operation generates a result table from one or two input tables
    • SELECT selects a subset of (or all) rows of a table
    • PROJECT selects one or more columns of a table
    • PRODUCT lists all combinations of rows of two tables
    • JOIN combines two tables by comparing related columns
    • UNION selects rows of two tables
    • INTERSECT selects rows common to two tables
    • DIFFERENCE selects rows that appear in one table but not another
    • RENAME changes a table name
    • AGGREGATE computes functions over multiple table rows, such as sum and count
  • these operations are called relational algebra
  • result of a SQL query is a table

Relational Rules

Rules are logical constraints that ensure data is valid.

Relational rules are part of the relational model and govern data in every relational database.

  • e.g.,
    • unique primary key
    • unique column names
    • no duplicate rows
  • relational rules are implemented as SQL constraints
    • enforced by the database system

Business rules are based on business policy and specific to a particular database.

  • discovered during database design
  • often implemented as SQL constraints
    • but may need to be enforced by applications running on the database