Most of the examples in this book are based on the sample database described in this appendix. The sample database contains data that supports a simple order processing application for a small distribution company. It consists of five tables:
- CUSTOMERS. Contains one row for each of the company’s customers.
- SALESREPS. Contains one row for each of the company’s ten salespeople.
- OFFICES. Contains one row for each of the company’s five sales offices where the salespeople work.
- PRODUCTS. Contains one row for each type of product that is available for sale.
- ORDERS. Contains one row for each order placed by a customer. For simplicity, each order is assumed to be for a single product.
Figure A-1 graphically shows the five tables, the columns that they contain, and the parent/child relationships among them. The primary key of each table is shaded. The five tables in the sample database can be created using the CREATE TABLE statements shown here:
CREATE TABLE CUSTOMERS
(CUST_NUM INTEGER NOT NULL,
COMPANY VARCHAR(20) NOT NULL,
CUST_REP INTEGER,
CREDIT_LIMIT MONEY,
PRIMARY KEY (CUST_NUM),
FOREIGN KEY HASREP (CUST_REP)
REFERENCES SALESREPS
ON DELETE SET NULL)
CREATE TABLE OFFICES
(OFFICE INTEGER NOT NULL,
CITY VARCHAR(15) NOT NULL,
REGION VARCHAR(10) NOT NULL,
MGR INTEGER,
TARGET MONEY,
SALES MONEY NOT NULL,
PRIMARY KEY (OFFICE),
FOREIGN KEY HASMGR (MGR)
REFERENCES SALESREPS
ON DELETE SET NULL)
CREATE TABLE SALESREPS
(EMPL_NUM INTEGER NOT NULL,
NAME VARCHAR(15) NOT NULL,
AGE INTEGER,
REP_OFFICE INTEGER,
TITLE VARCHAR(10),
HIRE_DATE DATE NOT NULL,
MANAGER INTEGER,
QUOTA MONEY,
SALES MONEY NOT NULL,
PRIMARY KEY (EMPL_NUM),
FOREIGN KEY (MANAGER)
REFERENCES SALESREPS
ON DELETE SET NULL,
FOREIGN KEY WORKSIN (REP_OFFICE)
REFERENCES OFFICES
ON DELETE SET NULL)
CREATE TABLE ORDERS
(ORDER_NUM INTEGER NOT NULL,
ORDER_DATE DATE NOT NULL,
CUST INTEGER NOT NULL,
REP INTEGER,
MFR CHAR(3) NOT NULL,
PRODUCT CHAR(5) NOT NULL,
QTY INTEGER NOT NULL,
AMOUNT MONEY NOT NULL,
PRIMARY KEY (ORDER_NUM),
F OREIGN KEY PLACEDBY (CUST)
REFERENCES CUSTOMERS
ON DELETE CASCADE,
FOREIGN KEY TAKENBY (REP)
REFERENCES SALESREPS
ON DELETE SET NULL,
FOREIGN KEY ISFOR (MFR, PRODUCT)
REFERENCES PRODUCTS
ON DELETE RESTRICT
CREATE TABLE PRODUCTS
(MFR_ID CHAR(3) NOT NULL,
PRODUCT_ID CHAR(5) NOT NULL,
DESCRIPTION VARCHAR(20) NOT NULL,
PRICE MONEY NOT NULL,
QTY_ON_HAND INTEGER NOT NULL,
PRIMARY KEY (MFR_ID, PRODUCT_ID))
Figures A-2 through A-6 show the contents of each of the five tables in the sample database. The query results in examples throughout the book are based on the data shown in these figures.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.