Advantages of database management systems

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:

1. 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.
2. 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)