Structure
of the Bookstore Database
The
Bookstore database consists of the slspers, sales, sales1, titles, titles1, customers, obsolete_titles, obsolete_titles1, potential_customers, potential_customers1 and titles2 tables. The following
tables describe the columns contained in each of the database tables.
Table A-1: Structure of the slspers Table
| Column Name |
Datatype |
Description |
| repid |
nvarchar (3) NULL |
The representative ID is an alphanumeric ID that uniquely identifies
each sales executive.
|
| fname |
nvarchar
(10) NULL
|
The first name of a sales executive. |
| lname |
nvarchar (20) NULL |
The last
name of a sales executive.
|
| commrate |
float
NULL
|
The commission rate earned by each executive. |
|
Table A-2: Structure of the sales Table
| Column Name |
Datatype |
Description |
| ordnum |
nvarchar (5) NULL |
The order number which uniquely identifies each sale. |
| sldate |
smalldatetime NULL |
The date
of sale.
|
| qty |
int NULL |
The
quantity of units sold for an order.
|
| custnum |
nvarchar (5) NULL |
The customer number. |
| partnum |
nvarchar (5) NULL |
The part
number which uniquely identifies the book sold.
|
| repid |
nvarchar (3) NULL |
The representative ID which uniquely
identifies the sales executive who contributed to the sale.
|
|
Table A-3: Structure of the sales1 Table
| Column Name |
Datatype |
Description |
| ordnum |
nvarchar (5) NULL |
The order number which uniquely identifies each sale. |
| sldate |
smalldatetime NULL |
The date
of sale.
|
| qty |
int NULL |
The
quantity of units sold for an order.
|
| custnum |
nvarchar (5) NULL |
The customer number. |
| partnum |
nvarchar (5) NULL |
The part
number which uniquely identifies the book sold.
|
| repid |
nvarchar (3) NULL |
The representative ID which uniquely
identifies the sales executive who contributed to the sale.
|
|
Table A-4: Structure of the titles Table
| Column Name |
Datatype |
Description |
| partnum |
nvarchar (5) NULL |
The part number of a book that uniquely identifies the book. |
| bktitle |
nvarchar (40) NULL |
The
title of a book.
|
| devcost |
money NULL |
The cost of developing the book. |
| slprice |
money NULL |
The sale price of a book. |
| pubdate |
smalldatetime NULL |
The publishing
date of a book.
|
|
Table A-5: Structure of the titles1 Table
| Column Name |
Datatype |
Description |
| partnum |
nvarchar (5) NULL |
The part number of a book that uniquely identifies the book. |
| bktitle |
nvarchar (40) NULL |
The
title of a book.
|
| devcost |
money NULL |
The cost of developing the book. |
| slprice |
money NULL |
The sale price of a book. |
| pubdate |
smalldatetime NULL |
The publishing
date of a book.
|
|
Table A-6: Structure of the customers Table
| Column Name |
Datatype |
Description |
| custnum |
nvarchar (5) NULL |
The customer number that uniquely identifies a customer. |
| referredby |
nvarchar (5) NULL |
The customer
number that represents a customer who referred the bookstore.
|
| custname |
nvarchar (30) NULL |
The name
of the customer.
|
| address |
nvarchar (25)
NULL
|
The address of the customer. |
| city |
nvarchar (20) NULL |
The city where the customer resides. |
| state |
nvarchar (2) NULL |
The
state where the customer's city is located.
|
| zipcode |
nvarchar (12) NULL |
The zip code of the customer's state. |
| repid |
nvarchar (3) NULL |
The
representative ID that identifies the sales executive who had sold books to
the customer.
|
|
Table A-7: Structure of the obsolete_titles Table
| Column Name |
Datatype |
Description |
| partnum |
varchar (10) NOT NULL |
The part number of a book that uniquely identifies the book. |
| bktitle |
varchar (40) NOT NULL |
The
title of a book.
|
| devcost |
money |
The cost of developing the book. |
| slprice |
money |
The sale price of a book. |
| pubdate |
smalldatetime |
The publishing date of a book. |
|
Table A-8: Structure of the obsolete_titles1 Table
| Column Name |
Datatype |
Description |
| partnum |
varchar (10) NOT NULL |
The part number of a book that uniquely identifies the book. |
| bktitle |
varchar (40) NOT NULL |
The
title of a book.
|
| devcost |
money |
The cost of developing the book. |
| slprice |
money |
The sale price of a book. |
| pubdate |
smalldatetime |
The publishing date of a book. |
|
Table A-9: Structure of the potential_customers Table
| Column Name |
Datatype |
Description |
| custnum |
varchar (5) NULL |
The customer number that uniquely identifies a customer. |
| referredby |
varchar (5) NULL |
The customer
number that represents a customer who referred the bookstore.
|
| custname |
varchar (30) NULL |
The name
of the customer.
|
| address |
varchar (50)
NULL
|
The address of the customer. |
| mobileno |
varchar (10) NOT NULL |
The customer's mobile number. |
| repid |
varchar (3) NULL |
The representative
ID that identifies the sales executive who had sold books to the customer.
|
|
Table A-10: Structure of the potential_customers1 Table
| Column Name |
Datatype |
Description |
| custnum |
varchar (5) NULL |
The customer number that uniquely identifies a customer. |
| referredby |
varchar (5) NULL |
The customer
number that represents a customer who referred the bookstore.
|
| custname |
varchar (30) NULL |
The name
of the customer.
|
| address |
varchar (50)
NULL
|
The address of the customer. |
| mobileno |
varchar (10) NOT NULL |
The customer's mobile number. |
| repid |
varchar (3) NULL |
The representative
ID that identifies the sales executive who had sold books to the customer.
|
|
Table A-11: Structure of the titles2 Table
| Column Name |
Datatype |
Description |
| partnum |
nvarchar (5) NULL |
The part number of a book that uniquely identifies the book. |
| bktitle |
nvarchar (40) NULL |
The
title of a book.
|
| devcost |
money NULL |
The cost of developing the book. |
| slprice |
money NULL |
The sales price of a book. |
| pubdate |
smalldatetime NULL |
The publishing
date of a book.
|
|