We shall represent a relation as a table with columns and rows. Each column of the table has a name, or attribute. Each row is called a tuple.
Domain: a set of atomic values that an attribute can take
Attribute: name of a column in a particular table (all data is stored in tables). Each attribute Ai must have a domain, dom(Ai).
Relational Schema: The design of one table, containing the name of the table (i.e. the name of the relation), and the names of all the columns, or attributes.
Example: STUDENT( Name, SID, Age, GPA)
Degree of a Relation: the number of attributes in the relation's schema.
Tuple, t, of R( A1, A2, A3, …, An): an ORDERED set of values, , where each vi is a value from dom( Ai).
Relation Instance, r( R): a set of tuples; thus, r( R) = { t1, t2, t3, …, tm}
NOTES:
The tuples in an instance of a relation are not considered to be ordered __ putting the rows in a different sequence does not change the table.
Once the schema, R( A1, A2, A3, …, An) is defined, the values, vi, in each tuple, t, must be ordered as t =
Properties of relations
Properties of database relations are:
relation name is distinct from all other relations
each cell of relation contains exactly one atomic (single) value
each attribute has a distinct name
values of an attribute are all from the same domain
order of attributes has no significance
each tuple is distinct; there are no duplicate tuples
order of tuples has no significance, theoretically.
Relational keys
There are two kinds of keys in relations. The first are identifying keys: the primary key is the main concept, while two other keys – super key and candidate key – are related concepts. The second kind is the foreign key
Identity Keys
Super Keys
A super key is a set of attributes whose values can be used to uniquely identify a tuple within a relation. A relation may have more than one super key, but it always has at least one: the set of all attributes that make up the relation.
Candidate Keys
A candidate key is a super key that is minimal; that is, there is no proper subset that is itself a superkey. A relation may have more than one candidate key, and the different candidate keys may have a different number of attributes. In other words, you should not interpret 'minimal' to mean the super key with the fewest attributes.
A candidate key has two properties:
(i) in each tuple of R, the values of K uniquely identify that tuple (uniqueness)
(ii) no proper subset of K has the uniqueness property (irreducibility).
Primary Key
The primary key of a relation is a candidate key especially selected to be the key for the relation. In other words, it is a choice, and there can be only one candidate key designated to be the primary key.
Foreign keys
The attribute(s) within one relation that matches a candidate key of another relation. A relation may have several foreign keys, associated with different target relations.
Foreign keys allow users to link information in one relation to information in another relation. Without FKs, a database would be a collection of unrelated tables.
Example
CAR( State, LicensePlateNo, VehicleID, Model, Year, Manufacturer)
This schema has two keys:
K1 = { State, LicensePlateNo}
K2 = { VehicleID }
Both K1 and K2 are superkeys.
K3 = { VehicleID, Manufacturer} is a superkey, but not a key (Why?).
If a relation has more than one keys, we can select any one (arbitrarily) to be the primary key. Primary Key attributes are underlined in the schema:
CAR (State, LicensePlateNo, VehicleID, Model, Year, Manufacturer)
[Read about Database Management System]