Suppliers and Parts database


The Suppliers and Parts database is an example relational database that is referred to extensively in the literature and described in detail in C. J. Date's An Introduction to Database Systems, 8th ed. It is a simple database comprising three tables: Supplier, Part and Shipment, and is often used as a minimal exemplar of the interrelationships found in a database.
  1. The Supplier relation holds information about suppliers. The SID attribute identifies the supplier, while the other attributes each hold one piece of information about the supplier.
  2. The Part relation holds information about parts. Likewise, the PID attribute identifies the part, while the other attributes hold information about the part.
  3. The Shipment relation holds information about shipments. The SID and PID attributes identify the supplier of the shipment and the part shipped, respectively. The remaining attribute indicates how many parts where shipped.

    SQL

The following SQL schema is one possible expression of the Suppliers-and-Parts database.

CREATE TABLE Supplier NOT NULL,
Status int NOT NULL,
City varchar NOT NULL
CREATE TABLE Part NOT NULL,
Color int NOT NULL,
Weight real NOT NULL,
City varchar NOT NULL
CREATE TABLE Shipment,
PID int NOT NULL FOREIGN KEY REFERENCES Part,
Qty int NOT NULL,
PRIMARY KEY

Notes:
  1. The ID attributes are simple integers, but they could be UUIDs or a system-defined identifier type that holds system-generated values.
  2. The choice of VARCHAR is arbitrary and would be too small for real-world use.
  3. The application of the NOT NULL constraint to all attributes is a design decision based on the view that NULLs are to be avoided. It is not, strictly speaking, a requirement of the schema.