# Classic Models data dictionary

Sample database `classicmodels` on learnsql.cpetoday.com (SQL Server login: CPE Today course). Each table lists its columns with the type, keys, what the column means, and, for codes and statuses, the values seen in the sample copy (with row counts when the dictionary was written).
8 of 9 tables are described. Undocumented tables are listed at the end.

## customers

Customers of the model-car company: contact, address and credit limit, with the sales rep who looks after them.

Grain: one row per customer

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `customerNumber` | int | PK | Unique customer number. |  |
| `customerName` | varchar(50) |  | Company name. |  |
| `contactLastName` | varchar(50) |  | Contact person's last name. |  |
| `contactFirstName` | varchar(50) |  | Contact person's first name. |  |
| `phone` | varchar(50) |  | Phone number. |  |
| `addressLine1` | varchar(50) |  |  |  |
| `addressLine2` | varchar(50) |  |  |  |
| `city` | varchar(50) |  | City. |  |
| `state` | varchar(50) |  |  |  |
| `postalCode` | varchar(15) |  |  |  |
| `country` | varchar(50) |  | Country. |  |
| `salesRepEmployeeNumber` | int | FK to employees | Sales rep for the account (employees.employeeNumber). |  |
| `creditLimit` | decimal |  | Maximum credit allowed. |  |

## employees

Staff, with job title, office and manager.

Grain: one row per employee

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `employeeNumber` | int | PK | Unique employee number. |  |
| `lastName` | varchar(50) |  | Last name. |  |
| `firstName` | varchar(50) |  | First name. |  |
| `extension` | varchar(10) |  |  |  |
| `email` | varchar(100) |  | Email. |  |
| `officeCode` | varchar(10) | FK to offices | Office (offices). | 1 (6), 4 (5), 6 (4), 2 (2), 3 (2), 5 (2), 7 (2) |
| `reportsTo` | int | FK to employees | Manager's employee number (employees). | 1102 (6), 1143 (6), 1056 (4), 1088 (3), 1002 (2), (blank) (1), 1621 (1) |
| `jobTitle` | varchar(50) |  | Job title (for example Sales Rep). | Sales Rep (17), President (1), Sale Manager (EMEA) (1), Sales Manager (APAC) (1), Sales Manager (NA) (1), VP Marketing (1), VP Sales (1) |

## offices

Sales offices and their territories.

Grain: one row per office

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `officeCode` | varchar(10) | PK | Unique office code. | 1 (1), 2 (1), 3 (1), 4 (1), 5 (1), 6 (1), 7 (1) |
| `city` | varchar(50) |  | City. | Boston (1), London (1), NYC (1), Paris (1), San Francisco (1), Sydney (1), Tokyo (1) |
| `phone` | varchar(50) |  |  |  |
| `addressLine1` | varchar(50) |  |  |  |
| `addressLine2` | varchar(50) |  |  |  |
| `state` | varchar(50) |  |  |  |
| `country` | varchar(50) |  | Country. | USA (3), Australia (1), France (1), Japan (1), UK (1) |
| `postalCode` | varchar(15) |  |  |  |
| `territory` | varchar(10) |  | Sales territory. | NA (3), EMEA (2), APAC (1), Japan (1) |

## orderdetails

The products on each order: quantity and price per unit. Revenue is quantityOrdered times priceEach.

Grain: one row per product per order

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `orderNumber` | int | PK FK to orders | Order (orders). |  |
| `productCode` | varchar(15) | PK FK to products | Product (products). |  |
| `quantityOrdered` | int |  | Units ordered. |  |
| `priceEach` | decimal |  | Price per unit on this order. |  |
| `orderLineNumber` | smallint |  | Line number within the order. |  |

## orders

Sales orders: one row per order with its dates and status. Line items are in orderdetails.

Grain: one row per order

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `orderNumber` | int | PK | Unique order number. |  |
| `orderDate` | date |  | Date the order was placed. |  |
| `requiredDate` | date |  | Date the customer needs the order by. |  |
| `shippedDate` | date |  | Date shipped (blank if not yet shipped). |  |
| `status` | varchar(15) |  | Order status (see the values). | Shipped (303), Cancelled (6), In Process (6), On Hold (4), Resolved (4), Disputed (3) |
| `comments` | varchar |  | Notes on the order. |  |
| `customerNumber` | int | FK to customers | Customer who placed the order (customers). |  |

## payments

Payments customers have made, one row per check.

Grain: one row per payment

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `customerNumber` | int | PK FK to customers | Customer who paid (customers). |  |
| `checkNumber` | varchar(50) | PK | Check number. |  |
| `paymentDate` | date |  | Date received. |  |
| `amount` | decimal |  | Amount paid. |  |

## productlines

Product lines, such as Classic Cars or Trains.

Grain: one row per product line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `productLine` | varchar(50) | PK | Product line name. | Classic Cars (1), Motorcycles (1), Planes (1), Ships (1), Trains (1), Trucks and Buses (1), Vintage Cars (1) |
| `textDescription` | varchar(4000) |  | Short description. | Attention car enthusiasts: Make your wildest car ownership dreams come true. Whether you are looking for classic muscle cars, dream sports cars or movie-inspired miniatures, you will find great choices in this category. These replicas feature superb attention to detail and craftsmanship and offer features such as working steering system, opening forward compartment, opening rear trunk with removable spare wheel, 4-wheel independent spring suspension, and so on. The models range in size from 1:10 to 1:24 scale and include numerous limited edition and several out-of-production vehicles. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office. (1), Model trains are a rewarding hobby for enthusiasts of all ages. Whether you're looking for collectible wooden trains, electric streetcars or locomotives, you'll find a number of great choices for any budget within this category. The interactive aspect of trains makes toy trains perfect for young children. The wooden train sets are ideal for children under the age of 5. (1), Our motorcycles are state of the art replicas of classic as well as contemporary motorcycle legends such as Harley Davidson, Ducati and Vespa. Models contain stunning details such as official logos, rotating wheels, working kickstand, front suspension, gear-shift lever, footbrake lever, and drive chain. Materials used include diecast and plastic. The models range in size from 1:10 to 1:50 scale and include numerous limited edition and several out-of-production vehicles. All models come fully assembled and ready for display in the home or office. Most include a certificate of authenticity. (1), Our Vintage Car models realistically portray automobiles produced from the early 1900s through the 1940s. Materials used include Bakelite, diecast, plastic and wood. Most of the replicas are in the 1:18 and 1:24 scale sizes, which provide the optimum in detail and accuracy. Prices range from $30.00 up to $180.00 for some special limited edition replicas. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office. (1), The perfect holiday or anniversary gift for executives, clients, friends, and family. These handcrafted model ships are unique, stunning works of art that will be treasured for generations! They come fully assembled and ready for display in the home or office. We guarantee the highest quality, and best value. (1), The Truck and Bus models are realistic replicas of buses and specialized trucks produced from the early 1920s to present. The models range in size from 1:12 to 1:50 scale and include numerous limited edition and several out-of-production vehicles. Materials used include tin, diecast and plastic. All models include a certificate of authenticity from their manufacturers and are a perfect ornament for the home and office. (1), Unique, diecast airplane and helicopter replicas suitable for collections, as well as home, office or classroom decorations. Models contain stunning details such as official logos and insignias, rotating jet engines and propellers, retractable wheels, and so on. Most come fully assembled and with a certificate of authenticity from their manufacturers. (1) |
| `htmlDescription` | varchar |  |  |  |
| `image` | varbinary |  |  |  |

## products

The model-car catalog: name, scale, vendor, stock and prices.

Grain: one row per product

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `productCode` | varchar(15) | PK | Unique product code. |  |
| `productName` | varchar(70) |  | Product name. |  |
| `productLine` | varchar(50) | FK to productlines | Product line (productlines). | Classic Cars (38), Vintage Cars (24), Motorcycles (13), Planes (12), Trucks and Buses (11), Ships (9), Trains (3) |
| `productScale` | varchar(10) |  | Scale, such as 1:18. | 1:18 (42), 1:24 (27), 1:700 (10), 1:12 (9), 1:32 (8), 1:10 (6), 1:50 (4), 1:72 (4) |
| `productVendor` | varchar(50) |  | Manufacturer of the model. |  |
| `productDescription` | varchar |  |  |  |
| `quantityInStock` | smallint |  | Units in stock. |  |
| `buyPrice` | decimal |  | What the company pays for the item. |  |
| `MSRP` | decimal |  | Suggested retail price. |  |

## Undocumented tables

`sysdiagrams`
