Review of the Relational Model:   Relations

  • Relation:

    • Relation = a table/collection/set of tuples

      (So this concept quite different from a relation in Mathematics)

  • Example: a relation 

       +-------+------------+---------------+----------+-----+
   | 12345 | John Doe   | 123 My Ln     | 123-4567 | 3.5 |
   +-------+------------+---------------+----------+-----+
   | 22222 | James Bond | 10 Downing St | 777-7777 | 3.7 | <---"tuple"
   +-------+------------+---------------+----------+-----+
   | 56789 | Jane Doe   | 321 Your Way  | 876-5678 | 3.8 |
   +-------+------------+---------------+----------+-----+

Definitions: (1) relation name, (2) attribute name and (3) tuple

  • Definitions (terminology):

    • Relation name = name (identification) of the relation (= table)

    • Attribute name = name (identification) of a column of the relation (= table)

    • Tuple = a row of values of the relation (table)

    Schematically:

Properties of relations

  • Properties of relations (tables):

    1. Tuples in a relation (= table) are unordered

    2. Attributes (= columns) of a relation (= table) are ordered

      Note:

      • This property makes it very difficult to add or remove attributes from/to relations (= tables) once the relations are populated with data....

    3. Attribute values are atomic

Concepts: (1) Super key and (2) Key

  • Superkey = a set of attributes in a relation R such that:

    • No 2 different tuples will have the same values for that set of attributes

    Mathematically defined:

    • A set of attribute SK is a superkey if and only if:

      • ∀   t1, t2   in   R:     t1[SK] ≠ t2[SK]

  • Key:

    • Key = is a minimal set of attributes in a relation R such that no 2 tuples will have the same values (for that set of attributes)

    I.e., a key is a minimal superkey.

More concepts: (1) Candidate key, (2) Primary key and (3) Foreign "key"

  • Candidate key:

    • Candidate key = any key in the relation

  • Primary key:

    • Primary key = the key that is chosen to be used to identify tuples in a relation.

    Once you have chosen the primary key for some relation, you must use that primary key throughout the database (or else, you database career life will be very miserable)

  • Foreign key:

    • Foreign key = a set of attributes inside some relation R1 that is the primary key of another relation R2

Examples of the 3 different keys on next slide

Examples with all the key concepts

  • Examples of candidate key, primary key and foreign key: