Structure and Data of the Sample Database

This document describes a database of three tables, including the structures of the tables and the data records in the individual tables. Throughout the semester, we’ll use the tables in this sample database to illustrate the syntax and semantics of SQL. These tables themselves are intended to resemble a real-life business situation, where you would use SQL to keep track of the salespeople, their customers, and the customers' orders. There are a number of things to note about the overall design.

Simplification: To ease the exploration, they are much simplified. For example, the detailed information about salespeople and customers, such as full names and contact information, is not included. A realistic database would obviously include such information. We have omitted it here to keep the tables as simple as possible, so that the complexity of the tables would not get in the way of understanding examples

Referential integrity from the Customers table to the Salespeople table: The snum field of the Customers table indicates to which salesperson a customer is assigned. The snum number relates to the Salespeople table, which gives information about these salespeople. Obviously, the salespeople to whom the customers are assigned should exist—that is to say, the snum values in the Customers table should also be present in the Salespeople table. If such is the case, we say that the system is in a state of referential integrity.

Flexibility of assigning salespersons to customers: Customers are assigned to salespeople, and then each order is associated with both a salesperson and a customer. Does each match of salesperson and customer in the Orders table have to be the same as the matches in the Customers table? In other words, does the salesperson assigned to a customer invariably get credit for each order of that customer? We have assumed not. If we had assumed so, the snum column in the Orders table would be superfluous, since we could use the Customers table to determine which salesperson would get credit for the sale. Doing it this way is more flexible. If the salesperson assigned to a customer is unavailable, another could handle the sale, and the two could split the commission. Such complexity may be introduced into a database by the business rules, and we thought it best to deal with it straight on. For the sake of simplicity, however, we have not introduced any such anomalies into the sample data.

Periodic recalculation of salespeople’s commission rate: the commission in the Salespeople table need not be static. It may be something that is periodically calculated on, for example, year-to-date sales. Suppose that calculating the commission is complex or involves accessing several databases. In such cases, it makes sense to occasionally (say, monthly) derive the commission and store it here.

The structure of the Salespeople table

COLUMN	CONTENT
snum	A unique number assigned to each salesperson as an employee number. This is the primary key of the table.
sname	The last name of the salesperson.
city	The location of the salesperson. This indicates one of a set of company offices.
comm	The salesperson's commission on orders in decimal form.

The Salespeople table

snum	sname	city	comm
1001	Peel	London	0.12
1002	Serres	San Jose	0.13
1004	Motika	London	0.11
1007	Rifkin	Barcelona	0.15
1003	Axelrod	New York	0.10

The structure of the Customers table

COLUMN	CONTENT
cnum	A unique number assigned to each customer. This is the primary key of the table.
cname	The last name of the customer.
city	The location of the customer. This actually indicates a company office, rather than the city where the customer resides.
rating	A numeric code indicating the level of preference assigned to this customer. Higher numbers indicate greater preference. NULL indicates a customer who has not yet been assigned a rating.
snum	The number of the salesperson assigned to this customer. This is a foreign key referencing Salespeople (snum). Since we don't want a customer to languish if his assigned salesperson is not available, we have enabled a mechanism for other salespeople to handle this customer when the one normally assigned is not available. We explain this mechanism below.

The columns of the Customers table

cnum	cname	city	rating	snum
2001	Hoffman	London	100	1001
2002	Giovanni	Rome	200	1003
2003	Liu	San Jose	200	1002
2004	Grass	Berlin	300	1002
2006	Clemens	London	NULL	1001
2008	Cisneros	San Jose	300	1007
2007	Pereira	Rome	100	1004

The structure of the Orders table

COLUMN	CONTENT
onum	A unique number given to each purchase.
amt	The amount of the purchase.
odate	The date of the purchase.
cnum	The number of the customer making the purchase. This is foreign key referencing Customers (cnum).
snum	The number of the salesperson credited with the sale. This is a foreign key referencing Salespeople (snum). It normally would be the salesperson assigned to the customer in the Customers table, but may not be, as explained below.

The Orders table

onum	amt	odate	cnum	snum
3001	18.69	10/03/2000	2008	1007
3003	767.19	10/03/2000	2001	1001
3002	1900.10	10/03/2000	2007	1004
3005	5160.45	10/03/2000	2003	1002
3006	1098.16	10/03/2000	2008	1007
3009	1713.23	10/04/2000	2002	1003
3007	75.75	10/04/2000	2004	1002
3008	4723.00	10/05/2000	2006	1001
3000	1309.95	10/06/2000	2004	1002
3011	9891.88	10/06/2000	2006	1001