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


EditText of this page (last edited February 20, 2009) or FindPage with title or text search