Databases Reference
In-Depth Information
Chapter 1
A M o t i v a t i n g E x a m p l e
Example is always more efficacious than precept
—Samuel Johnson:
Rasselas
(1759)
Examples throughout this topic are based for the most part on the familiar (not to say hackneyed)
suppliers-and-parts database. I apologize for dragging out this old warhorse yet one more time,
but as I've said elsewhere, I believe using the same example in a variety of different publications
can be a help, not a hindrance, in learning. In SQL terms,
1
the database contains three tables—
more specifically, three base tables—called S (“suppliers”), P (“parts”), and SP (“shipments”),
respectively. Sample values are shown in Fig. 1.1.
S SP
┌─────┬───────┬────────┬────────┐ ┌─────┬─────┬─────┐
│ SNO │ SNAME │ STATUS │ CITY │ │ SNO │ PNO │ QTY │
├═════┼───────┼────────┼────────┤ ├═════┼═════┼─────┤
│ S1 │ Smith │ 20 │ London │ │ S1 │ P1 │ 300 │
│ S2 │ Jones │ 10 │ 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 │ Oslo │ └─────┴─────┴─────┘
│ 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
The semantics (in outline) are as follows:
1
I use SQL and SQL-style syntax in this introductory chapter for reasons of familiarity, despite the fact that it's not really to my
taste, and (more to the point, perhaps) despite the fact that it actually makes the motivating example harder to explain properly.
ﾠ
