The relational data model

Computer Systems


  • DDL is too low level and not easily understandable by most users

  • We need a data model: A collection of intuitive concepts describing data, their relationships and constraints

  • Relational data model

    • Relations between data are stored in tables

    • Based on the concept of mathematical relations

    • The most widely used data model (for structured data)

  • Intuitively in our data, every entity combines various attributes together

  • The schema of a relation - the description of a particular collection of data in the model

  • Let $A_1,A_2,...,A_n$ be a set of attributes that can be related

  • The $R(A_1,A_2,...,A_n)$ is the scheme of the relation R

  • In a relation schema the ordering of the attributes does not matter

  • A database has many entities, each with its own attributes

  • This information is decomposed into smaller pieces where every relation stores only one piece of the information

  • However there is data duplication where two customers have the same account, and null values are needed where data is not complete

Relation Model Terminology

  • Relation - A table

  • Attribute - A named column of a relation. Every attribute has a unique name

  • Domain - The set of allowable values of an attribute

  • Tuple - A row of a relation - every tuple has a concrete value for every attribute (not left empty, use NULL if no data)

  • Cell - The intersection of a row and a column

  • Degree - The number of attributes (every row stores as many values as the degree of the relation)

  • Cardinality - The number of tuples

  • Normalized - Appropriately structured (every cell has exactly one value, no repetitions of two identical rows)

  • Relational Database - A collection of normalized relations

Instances of branch and staff relations

  • NULL Value

    • a special case of a cell entry

    • It represents an attribute value that is either currently unknown or not applicable

    • Not the same as 0

    • May or may not belong to the domain of the attribute

Properties of relations

  • The relation name is distinct from all other relation names in the relational schema

  • Each attribute within a relation has a distinct name

  • Values of an attribute are all from the same domain

  • Each cell of relation contains exactly one atomic value

  • Each tuple is distinct among the tuples of the relation

  • The ordering of the attributes has no significance

  • The ordering of tuples has no significance

Keys

  • How do we uniquely identify a tuple in a normalized table?

    • attribute names are unique within a table

    • but two tuples may share attribute values

  • Every table must have some attributes, such as:

    • Their value uniquely determines a tuple of the table

    • These attributes are the primary key of the table

  • Candidate key: a minimal set of attributes whose values uniquely identify the tuples

  • Primary key: The candidate key selected to identify rows uniquely with the table

  • Alternate key: Those candidate keys not selected as primary key

  • Simple key: The key consists of only one attribute

  • Composite key: The key consists of only one several attributes

Integrity constraints

  • So far we have seen domain constraints for the attributes

  • Entity integrity - every attribute of a primary key can not be NULL

  • Purpose of entity integrity

    • guarantees that each entity has a unique identifier

    • ensures that foreign key values can reference primary key values

Integrity constraints

  • Referential integrity

    • a foreign key either matches the primary key it refers to or it is null
  • Purpose of referential integrity

    • any reference between tables is valid (or it has not been set yet)

    • Prevents deleting a row in a table B, if the primary key of B has a matching foreign key in another table A

Summary: Characteristics of a relational table

  • A relation is represented by a two dimensional table

  • Each row (tuple) signifies an entity occurrence

  • No two rows can be identical (each row of the table is unique)

  • Each column represents an attribute and has a distinct name

  • The intersection of a row and column has a single value (atomic)

  • All values in a column must be of the same type

  • One (or more) attributes uniquely identify each row (primary key)

  • Two tables can be dependent (the primary key is the foreign key of another table)

  • The ordering of rows and columns does not matter

Views

  • So far all relations we have seen

    • Base relations

    • Its tuples are physically stored in the database

  • A different type of relation: a view

    • a virtual relation

    • it does not exist physically in the database

  • The content of a view

    • is derived from one (or more) base relations

    • is computer upon request by a user, at the time of request

    • changes when the underlying base relations change

  • Main use

    • show customised information to every user

    • computer dynamic quantities

Alternatives to the relational data model

  • Network data model

    • records appear as nodes

    • relationships appear as edges

  • Hierarchical data model

    • Special case of the network data model, where the graph is a tree graph

    • its structure mirrors parent child relationship

    • limitations of the model e.g.

      • deleting a parent

      • adding a record without a parent