This is a very simple database that is used by ChrisDate & HughDarwen throughout their books (e.g., AnIntroductionToDatabaseSystems and TheThirdManifesto) for examples. It's handy to have this as a reference for discussing database software. They also use the SupplierPartsProjectsDatabase to illustrate some more complex situations.
Small and simple, the schema and data are carefully chosen to allow uncluttered illustration of many common query operations. There are only three tables:
Table name Purpose Key
------------------------------------------------------
S Suppliers (S#)
P Parts (P#)
SP Parts supplied by Suppliers (S#, P#)
The full column set of each table, and the data they typically contain for example uses, are shown below. This version was taken from the eighth edition of AnIntroductionToDatabaseSystems.
S
S# SNAME STATUS CITY
----------------------------
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
P
P# PNAME COLOR WEIGHT CITY
----------------------------------
P1 Nut Red 12.0 London
P2 Bolt Green 17.0 Paris
P3 Screw Blue 17.0 Oslo
P4 Screw Red 14.0 London
P5 Cam Blue 12.0 Paris
P6 Cog Red 19.0 London
SP
S# P# QTY
------------
S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S4 P5 400
The above in TutorialDee syntax:
VAR S REAL RELATION {S# CHAR, SNAME CHAR, STATUS INTEGER, CITY CHAR}, KEY{S#},;
S := RELATION {
TUPLE {S# "S1", SNAME "Smith", STATUS 20, CITY "London"},,
TUPLE {S# "S2", SNAME "Jones", STATUS 10, CITY "Paris"},,
TUPLE {S# "S3", SNAME "Blake", STATUS 30, CITY "Paris"},,
TUPLE {S# "S4", SNAME "Clark", STATUS 20, CITY "London"},,
TUPLE {S# "S5", SNAME "Adams", STATUS 30, CITY "Athens"},},;
VAR P REAL RELATION {P# CHAR, PNAME CHAR, COLOR CHAR, WEIGHT RATIONAL, CITY CHAR}, KEY{P#},;
P := RELATION {
TUPLE {P# "P1", PNAME "Nut", COLOR "Red", WEIGHT 12.0, CITY "London"},,
TUPLE {P# "P2", PNAME "Bolt", COLOR "Green", WEIGHT 17.0, CITY "Paris"},,
TUPLE {P# "P3", PNAME "Screw", COLOR "Blue", WEIGHT 17.0, CITY "Oslo"},,
TUPLE {P# "P4", PNAME "Screw", COLOR "Red", WEIGHT 14.0, CITY "London"},,
TUPLE {P# "P5", PNAME "Cam", COLOR "Blue", WEIGHT 12.0, CITY "Paris"},,
TUPLE {P# "P6", PNAME "Cog", COLOR "Red", WEIGHT 19.0, CITY "London"},},;
VAR SP REAL RELATION {S# CHAR, P# CHAR, QTY INTEGER}, KEY{S#, P#},;
SP := RELATION {
TUPLE {S# "S1", P# "P1", QTY 300},,
TUPLE {S# "S1", P# "P2", QTY 200},,
TUPLE {S# "S1", P# "P3", QTY 400},,
TUPLE {S# "S1", P# "P4", QTY 200},,
TUPLE {S# "S1", P# "P5", QTY 100},,
TUPLE {S# "S1", P# "P6", QTY 100},,
TUPLE {S# "S2", P# "P1", QTY 300},,
TUPLE {S# "S2", P# "P2", QTY 400},,
TUPLE {S# "S3", P# "P2", QTY 200},,
TUPLE {S# "S4", P# "P2", QTY 200},,
TUPLE {S# "S4", P# "P4", QTY 300},,
TUPLE {S# "S4", P# "P5", QTY 400},},;
Here is standard SQL to create and fill these tables. This should work with most dialects, even older ones. The exact column type choices made here are, in some cases, fairly arbitrary, and the SNUM and PNUM columns are defined as integers rather than strings. This can be a handy starting point for your own experimentation with your DBMS of choice.
CREATE TABLE S
(
SNUM int NOT NULL PRIMARY KEY,
SNAME varchar(16) NOT NULL UNIQUE,
STATUS int NOT NULL,
CITY varchar(20) NOT NULL
);
CREATE TABLE P
(
PNUM int NOT NULL PRIMARY KEY,
PNAME varchar(18) NOT NULL,
COLOR varchar(10) NOT NULL,
WEIGHT decimal(4,1) NOT NULL,
CITY varchar(20) NOT NULL,
UNIQUE (PNAME, COLOR, CITY)
);
CREATE TABLE SP
(
SNUM int NOT NULL REFERENCES S,
PNUM int NOT NULL REFERENCES P,
QTY int NOT NULL,
PRIMARY KEY (SNUM, PNUM)
);
INSERT INTO S VALUES (1, 'Smith', 20, 'London');
INSERT INTO S VALUES (2, 'Jones', 10, 'Paris');
INSERT INTO S VALUES (3, 'Blake', 30, 'Paris');
INSERT INTO S VALUES (4, 'Clark', 20, 'London');
INSERT INTO S VALUES (5, 'Adams', 30, 'Athens');
INSERT INTO P VALUES (1, 'Nut', 'Red', 12, 'London');
INSERT INTO P VALUES (2, 'Bolt', 'Green', 17, 'Paris');
INSERT INTO P VALUES (3, 'Screw', 'Blue', 17, 'Oslo');
INSERT INTO P VALUES (4, 'Screw', 'Red', 14, 'London');
INSERT INTO P VALUES (5, 'Cam', 'Blue', 12, 'Paris');
INSERT INTO P VALUES (6, 'Cog', 'Red', 19, 'London');
INSERT INTO SP VALUES (1, 1, 300);
INSERT INTO SP VALUES (1, 2, 200);
INSERT INTO SP VALUES (1, 3, 400);
INSERT INTO SP VALUES (1, 4, 200);
INSERT INTO SP VALUES (1, 5, 100);
INSERT INTO SP VALUES (1, 6, 100);
INSERT INTO SP VALUES (2, 1, 300);
INSERT INTO SP VALUES (2, 2, 400);
INSERT INTO SP VALUES (3, 2, 200);
INSERT INTO SP VALUES (4, 2, 200);
INSERT INTO SP VALUES (4, 4, 300);
INSERT INTO SP VALUES (4, 5, 400);