A
Sample Database with Sample Data
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. (Column
attribute type: INTEGER) |
|
sname |
The last name of the
salesperson. (Column attribute type: CHAR(15) ) |
|
city |
The location of the
salesperson. This indicates one of a set of company offices. (Column attribute
type: CHAR(15) ) |
|
comm |
The salesperson's commission
on orders in decimal form. (Column attribute type: NUMERIC(3,2) ) |
The Salespeople table
|
snum |
sname |
city |
comm |
|
1001 |
Peel |
|
0.12 |
|
1002 |
Serres |
|
0.13 |
|
1004 |
Motika |
|
0.11 |
|
1007 |
Rifkin |
|
0.15 |
|
1003 |
Axelrod |
|
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. (Column attribute
type: INTEGER) |
|
cname |
The last name of the
customer. (Column attribute type: CHAR(15) ) |
|
city |
The location of the
customer. This actually indicates a company office, rather than the city
where the customer resides. (Column attribute type: CHAR(15) ) |
|
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. (Column attribute type: INTEGER) |
|
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.
(Column attribute type: INTEGER) |
The columns of the Customers table
|
cnum |
cname |
city |
rating |
snum |
|
2001 |
Hoffman |
|
100 |
1001 |
|
2002 |
Giovanni |
|
200 |
1003 |
|
2003 |
Liu |
|
200 |
1002 |
|
2004 |
Grass |
|
300 |
1002 |
|
2006 |
Clemens |
|
NULL |
1001 |
|
2008 |
Cisneros |
|
300 |
1007 |
|
2007 |
|
|
100 |
1004 |
The structure of the Orders table
|
COLUMN |
CONTENT |
|
onum |
A unique number given
to each purchase. This is the primary
key of the table. (Column attribute
type: INTEGER) |
|
amt |
The amount of the
purchase. (Column attribute type: NUMERIC(8,2) ) |
|
odate |
The date of the
purchase. (Column attribute type: DATE ) |
|
cnum |
The number of the
customer making the purchase. This is foreign
key referencing Customers (cnum). (Column attribute type: INTEGER) |
|
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 in the beginning of the document. (Column attribute type:
INTEGER) |
The Orders table
|
onum |
amt |
odate |
cnum |
snum |
|
3001 |
18.69 |
|
2008 |
1007 |
|
3003 |
767.19 |
|
2001 |
1001 |
|
3002 |
1900.10 |
|
2007 |
1004 |
|
3005 |
5160.45 |
|
2003 |
1002 |
|
3006 |
1098.16 |
|
2008 |
1007 |
|
3009 |
1713.23 |
|
2002 |
1003 |
|
3007 |
75.75 |
|
2004 |
1002 |
|
3008 |
4723.00 |
|
2006 |
1001 |
|
3000 |
1309.95 |
|
2004 |
1002 |
|
3011 |
9891.88 |
|
2006 |
1001 |