---

The Relational Model

---

• The Relational Database Model: Overview

  is a implementation model In contrast: the ER-model is a conceptual model !!! So the Relational Model has enough details to describe how the data is actually stored The Relational Model uses only one concept in the description: A relation           BUT: The relation concept in the Relational Model is not the relation concept used in Mathematics (which is a fixed set (collection) of elements)

  ---

  ---

  ---

  ---

• Some concepts in the Relational Database Model
  • Relation:

    Relation = a table of values (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 | +-------+------------+---------------+----------+-----+ | 56789 | Jane Doe | 321 Your Way | 876-5678 | 3.8 | +-------+------------+---------------+----------+-----+

    ---

  • 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:

    ---

    ---

  • More definitions:

    Domain = set of atomic values that are assigned to an attribute Relation schema consists of: Relation name and All its attribute names Example: Relation Schema of Student student(ID, Name, Address, Phone, GPA) Degree of a relation = the number of attributes in the relation schema Example: student(ID, Name, Address, Phone, GPA) degree(Student) = 5 (there are 5 attributes) Note: degree of a relation is very different from the degree of a ER-relationship (Degree of a ER-relationship = the number of entities participating in the relationship)

  ---

  ---

  ---

  ---

  ---

  ---

• Characteristics of the Relational Database
  • Properties of relations (tables):

    Tuples in a relation (= table) are unordered Note: This may not sound like a big deal to you now, but you have to realise that before the Relational Database Model was introduced, the existing database models (the hierarchical and the network database models) uses file pointers (= disk locations) to access data in different files. These file pointers make it impossible to move records within a file.... 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.... Attribute values are atomic (This is a natural result of the property that the domains only have atomic values - see above).

  ---

  ---

  ---

  ---

• Some Notations
  • Notations:

    A relation schema is denoted as R(A1, A2, ..., An) A tuple of a relation is denoted as t(a1, a2, ..., an) t[Ai] = (denotes) the value of the attribute Ai in the tuple t            ( The value is equal to ai ) t[Ai, Aj, Ak] = (denotes) the value of attributes Ai, Aj, Ak in tuple t             ( The valus is equal to (ai, aj, ak) )

  • Examples:

    Relation Schema: Student(StuID, Name, Address, Level, GPA) Tuples: t1(1234, John, 123 South Ln, 3, 3.5) t2(2222, Jake, 456 North Ln, 2, 3.8) t1(SSN) = 1234 t2(SSN) = 2222 t1(SSN, Name) = (1234, John) t2(SSN, Name) = (2222, Jake)

  ---

  ---

  ---

  ---

• "Key" concepts in Relational Database
  • You already know what "key" attributes are from the ER-model: click here

    The key concept in the relational model is defined in an "unusually" difficult manner (well, in a Mathematical manner)...

    ---

  • Super 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 speaking:

    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.

    ---

  • Candidate key:

    Candidate key = any key

    ---

  • 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

    Example:

    ID in GradeReport is a foreign key:

    The attribute ID in the relation GradeReport (= R1) is the primary key in relation Student (R2)

    CID in GradeReport is also a foreign key:

    The attribute CID in the relation GradeReport (= R1) is the primary key in relation Course (R2)

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• Integrity Constraints in the Relational Model
  • Integrity constraints are constraints that tuples in a relational database must satisfy.

    ---

  • Types of constraints:

    Key constraint Entity integrity constraint Referential integrity constraint

    ---

    ---

  • Key constraint:

    Every relation schema must have a primary key

    NOTE:

    A key may consist of all attributes in the relation !!! (Often, the key is a subset of the attributes).

    ---

  • Entity integrity constraint:

    The attribute values of the primary key cannot have NULL values

    Makes perfect sense:

    • The primary key is used to identify a tuple

    • NULL value means Not Applicablex or Not Available

    • In either case, the primary key would be crippled (useless) in its ability to identify a tuple.

    ---

  • Referential integrity constraint:

    Suppose relation schema R1 contains a foreign key (which is the primary key) of the relation schema R2 (in other words: R1 "points to" R2). Then the following constraint must hold: if a tuple t1 in R1 that references the tuple t2 in R2, then the tuple t2 must exist

    Pictorially explanation of Referential integrity constraint:

    (This also makes perfect sense because you must point to something that exists !!!)