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
