### Title

• 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 | <--- 1 "tuple" +-------+------------+---------------+----------+-----+ | 56789 | Jane Doe | 321 Your Way | 876-5678 | 3.8 | +-------+------------+---------------+----------+-----+

• Terminology

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

• 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).

• "Key" concepts in Relational Database

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