- 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 | +-------+------------+---------------+----------+-----+
- Relation =
a table/collection/set
of tuples
- 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:
- Relation name =
name
(identification) of the
relation (= table)
- 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....
- 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.
- 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).
- Tuples
in a relation (= table) are
unordered
- 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: all the key concepts
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)