CS457 Syllabus & Progress

The Relational Model: Definitions and Concepts

The ER model helps us understand the organization information structure
The Relational model will help us realise the information structure

• The Relational Database Model: Overview

• is a medium level model with moderate amount of details on how the data will be stored

 In contrast: the ER-model is a high level model showing very little details on how the data will be stored

• So the Relational Model has enough details to describe the files used to stored the data !!!

• 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 | +-------+------------+---------------+----------+-----+ ```

Comment:

 We can see the file structure here !!! Each row of a table is a data record in a file !!!

• 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

Note:

 The set of attribute values in a domain must be atomic (= singleton values, not composite nor multi-valued !!!)

• 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

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)