Supplier Parts Database

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);