Databases Reference

In-Depth Information

Parts:
Relvar P denotes parts (more accurately, kinds of parts). Each kind of part has one part number

(PNO), which is unique; one name (PNAME), not necessarily unique; one color (COLOR); one weight

(WEIGHT); and one location where parts of that kind are stored (CITY).

Shipments:
Relvar SP denotes shipments (it shows which parts are supplied, or shipped, by which suppliers).

Each shipment has one supplier number (SNO), one part number (PNO), and one quantity (QTY). Also, I

assume for the sake of the example that there's at most one shipment at any one time for a given supplier and

a given part, and so each shipment has a supplier-number/part-number combination that's unique.

S SP

┌─────┬───────┬────────┬────────┐ ┌─────┬─────┬─────┐

│ SNO │ SNAME │ STATUS │ CITY │ │ SNO │ PNO │ QTY │

├═════┼───────┼────────┼────────┤ ├═════┼═════┼─────┤

│ S1 │ Smith │ 20 │ London │ │ S1 │ P1 │ 300 │

│ S2 │ Jones │ 30 │ Paris │ │ S1 │ P2 │ 200 │

│ S3 │ Blake │ 30 │ Paris │ │ S1 │ P3 │ 400 │

│ S4 │ Clark │ 20 │ London │ │ S1 │ P4 │ 200 │

│ S5 │ Adams │ 30 │ Athens │ │ S1 │ P5 │ 100 │

└─────┴───────┴────────┴────────┘ │ S1 │ P6 │ 100 │

P │ S2 │ P1 │ 300 │

┌─────┬───────┬───────┬────────┬────────┐ │ S2 │ P2 │ 400 │

│ PNO │ PNAME │ COLOR │ WEIGHT │ CITY │ │ S3 │ P2 │ 200 │

├═════┼───────┼───────┼────────┼────────┤ │ S4 │ P2 │ 200 │

│ P1 │ Nut │ Red │ 12.0 │ London │ │ S4 │ P4 │ 300 │

│ P2 │ Bolt │ Green │ 17.0 │ Paris │ │ S4 │ P5 │ 400 │

│ P3 │ Screw │ Blue │ 17.0 │ Paris..│ └─────┴─────┴─────┘

│ P4 │ Screw │ Red │ 14.0 │ London │

│ P5 │ Cam │ Blue │ 12.0 │ Paris │

│ P6 │ Cog │ Red │ 19.0 │ London │

└─────┴───────┴───────┴────────┴────────┘

Fig. 1.1: The suppliers-and-parts database—sample values

KEYS

Before going any further, I need to review the familiar concept of
keys
, in the relational sense of that term. First of

all, as I'm sure you know, every relvar has at least one
candidate
key. A candidate key is basically just a unique

identifier; in other words, it's a combination of attributes—often but not always a “combination” consisting of just a

single attribute—such that every tuple in the relvar has a unique value for the combination in question. For

example, with respect to the database of Fig. 1.1:

Every supplier has a unique supplier number and every part has a unique part number, so {SNO} is a

candidate key for S and {PNO} is a candidate key for P.

As for shipments, given the assumption that there's at most one shipment at any one time for a given supplier

and a given part, {SNO,PNO} is a candidate key for SP.

Note the braces, by the way; to repeat, candidate keys are always combinations, or
sets
, of attributes (even

when the set in question contains just one attribute), and the conventional representation of a set on paper is as a

commalist of elements enclosed in braces.
Note:
The useful term
commalist
can be defined as follows: Let
xyz
be

some syntactic construct (for example, “attribute name”). Then the term
xyz commalist
denotes a sequence of zero