- The relational database schema for The Suppliers and Parts database is shown below:
S (Sno, Sname, Status City)
P (Pno, Pname, Color, Weight, City)
SP (Sno, Pno, Qty) - A superkey is an attribute (or set of attributes) that uniquely identifies a tuple in a relation For example the combination Sno,Sname uniquely identifies a tuple in S.
- A candidate key is also a unique identifier for a tuple in a relation, but in addition it is minimal (i.e. it is as small as possible without losing its uniqueness). For example: Sno is a candidate key in S because we do not need both Sno and Sname to identify a tuple, simply using Sno is enough (we know that Sno in itself is unique).
- The primary key is the candidate key that is selected (from all the available candidates keys) to identify tuples uniquely within a relation. A relation, by definition, has ONE primary key (which in some cases can be a compound key). In S there is only one candidate key (Sno) and therefore this attribute is the primary key. If there is more than one candidate key, the database designer must decide which of the candidate keys is made primary key.
- A foreign key is an attribute, or set of attributes, within one relation that matches the candidate key of some (possibly the same) relation. For example, Sno in relation SP is a foreign key that matches the candidate key Sno in relation S.
Two rules are entity integrity and referential integrity.
Consider the tables S and SP from the Suppliers and Parts database (see Unit Resources).
The entity integrity rule states that in a base relation no attribute of a primary key can be null.
The primary key of S is Sno. If Sno were null then it would no longer be possible to select a single tuple from S. After all (theoretically) there could be more than one supplier with the same name, the same status, and based in the same city.
The primary key of SP is Sno, Pno. Neither Sno nor Pno in itself are unique in SP, so if part of the key is null, the key loses its uniqueness and it will no longer be possible to select one single tuple.
The referential integrity rule states that if a foreign key exists in a relation, either the foreign key value must match a candidate key value in its home relation, or the foreign key value must be wholly null.
So if S4 exists in SP then S4 must also exist in S (the home relation). The rationale is that only a supplier who exists (in S) can ship parts (in SP). A foreign key can also be wholly null, for example supplier S5 currently has no shipments and therefore there is no value S5 in SP.