1.5. Database Tables Used in This Book
This section describes a database or library of tables that is used by an imaginary computer hardware and software manufacturer. The library consists of six tables: customer, inventory, invoice, manufacturers, products, and purchases. The examples used throughout this book are based on this library (database) of tables and are described and displayed below. An alphabetical description of each table used throughout this book appears below.
1.5.1. CUSTOMERS Table
The CUSTOMERS table contains data on customers that have purchased computer hardware and software products from a manufacturer. Each customer is uniquely identified with a customer number. A description of each column in the customers table follows.
| CUSTOMERS | |
|---|---|
| CUSTNUM | Unique number identifying the customer |
| CUSTNAME | Name of customer |
| CUSTCITY | City where customer is located |
1.5.2. INVENTORY Table
The INVENTORY table contains customer inventory information consisting of computer hardware and software products. The inventory table contains no historical data. As inventories are replenished, the old quantity is overwritten with the new quantity. A description of each column in the inventory table follows.
| INVENTORY | |
|---|---|
| PRODNUM | Unique number identifying product |
| MANUNUM | Unique number identifying the manufacturer |
| INVENQTY | Number of units of product in stock |
| ORDDATE | Date product was last ordered |
| INVENCST | Cost of inventory in customer’s stock room |
1.5.3. INVOICE Table
The INVOICE table contains information about customer purchases. Each invoice is uniquely identified with an invoice number. A description of each column in the invoice table follows:
| INVOICE | |
|---|---|
| INVNUM | Unique number identifying the invoice |
| MANUNUM | Unique number identifying the manufacturer |
| CUSTNUM | Customer number |
| PRODNUM | Product number |
| INVQTY | Number of units sold |
| INVPRICE | Unit price |
1.5.4. MANUFACTURERS Table
The MANUFACTURERS table contains data about companies that make computer hardware and software products. Two companies cannot have the same name. No historical data is kept in this table. If a company is sold or stops making computer hardware or software, then the manufacturer is dropped from the table. In the event a manufacturer has an address change, the old address is overwritten with the new address. A description of each column in the manufacturers table follows.
| MANUFACTURERS | |
|---|---|
| MANUNUM | Unique number identifying the manufacturer |
| MANUNAME | Name of manufacturer |
| MANUCITY | City where manufacturer is located |
| MANUSTAT | State where manufacturer is located |
1.5.5. PRODUCTS Table
The PRODUCTS table contains data about computer hardware and software products offered for sale by the manufacturer. Each product is uniquely identified with a product number. A description of each column in the products table follows.
| PRODUCTS | |
|---|---|
| PRODNUM | Unique number identifying the product |
| PRODNAME | Name of product |
| MANUNUM | Unique number identifying the manufacturer |
| PRODTYPE | Type of product |
| PRODCOST | Cost of product |
1.5.6. PURCHASES Table
The PURCHASES table contains information about computer hardware and software products purchased by customers. Each product is uniquely identified with a product number. A description of each column in the purchases table follows.
| PURCHASES | |
|---|---|
| CUSTNUM | Unique number identifying the product |
| ITEM | Name of product |
| UNITS | Unique number identifying the manufacturer |
| UNITCOST | Cost of product |