Appendix A. Sample Table Scripts

Writing SQL statements requires a good understanding of the underlying database design. If you do not know what information is stored in what table, how tables are related to each other, and the actual breakup of data within a row, it is impossible to write effective SQL.

You are strongly advised to actually try every example in every lesson in this book. All the lessons use a common set of data files. To assist you in better understanding the examples, and to enable you to follow along with the lessons, this appendix describes the tables used, their relationships, and how to build (or obtain) them.

Understanding the Sample Tables

The tables used throughout this book are part of an order entry system used by an imaginary distributor of toys. The tables are used to perform several tasks:

Manage vendors

Manage product catalogs

Manage customer lists

Enter customer orders

Making this all work requires five tables (that are closely interconnected as part of a relational database design). A description of each of the tables appears in the following sections.

Note: Simplified Examples

The tables used here are by no means complete. A real-world order entry system would have to keep track of lots of other data that has not been included here (for example, payment and accounting information, shipment tracking, and more). However, these tables do demonstrate the kinds of data organization and relationships that you will encounter in most real installations. You can apply these techniques and technologies to your own databases.

Table Descriptions

What follows is a description of each of the five tables, along with the name of the columns within each table and their descriptions.

The Vendors Table

The Vendors table stores the vendors whose products are sold. Every vendor has a record in this table, and that vendor ID (the vend_id) column is used to match products with vendors.

Table A.1 Vendors Table Columns

Column	Description
vend_id	Unique vendor ID
vend_name	Vendor name
vend_address	Vendor address
vend_city	Vendor city
vend_state	Vendor state
vend_zip	Vendor ZIP code
vend_country	Vendor country

All tables should have primary keys defined. This table should use vend_id as its primary key.

The Products Table

The Products table contains the product catalog, one product per row. Each product has a unique ID (the prod_id column) and is related to its vendor by vend_id (the vendor’s unique ID).

Table A.2 Products Table Columns

Column	Description
prod_id	Unique product ID
vend_id	Product vendor ID (relates to vend_id in Vendors table)
prod_name	Product name
prod_price	Product price
prod_desc	Product description

All tables should have primary keys defined. This table should use prod_id as its primary key.

To enforce referential integrity, a foreign key should be defined on vend_id relating it to vend_id in VENDORS.

The Customers Table

The Customers table stores all customer information. Each customer has a unique ID (the cust_id column).

Table A.3 Customers Table Columns

Column	Description
cust_id	Unique customer ID
cust_name	Customer name
cust_address	Customer address
cust_city	Customer city
cust_state	Customer state
cust_zip	Customer ZIP code
cust_country	Customer country
cust_contact	Customer contact name
cust_email	Customer contact email address

All tables should have primary keys defined. This table should use cust_id as its primary key.

The Orders Table

The Orders table stores customer orders (but not order details). Each order is uniquely numbered (the order_num column). Orders are associated with the appropriate customers by the cust_id column (which relates to the customer’s unique ID in the Customers table).

Table A.4 Orders Table Columns

Column	Description
order_num	Unique order number
order_date	Order date
cust_id	Order customer ID (relates to cust_id in Customers table)

All tables should have primary keys defined. This table should use order_num as its primary key.

To enforce referential integrity, a foreign key should be defined on cust_id relating it to cust_id in CUSTOMERS.

The OrderItems Table

The OrderItems table stores the actual items in each order, one row per item per order. For every row in Orders there are one or more rows in OrderItems. Each order item is uniquely identified by the order number plus the order item (first item in order, second item in order, and so on). Order items are associated with their appropriate order by the order_num column (which relates to the order’s unique ID in Orders). In addition, each order item contains the product ID of the item orders (which relates the item back to the Products table).

Table A.5 OrderItems Table Columns

Column	Description
order_num	Order number (relates to order_num in Orders table)
order_item	Order item number (sequential within an order)
prod_id	Product ID (relates to prod_id in Products table)
quantity	Item quantity
item_price	Item price

All tables should have primary keys defined. This table should use order_num and order_item as its primary keys.

To enforce referential integrity, foreign keys should be defined on order_num relating it to order_num in Orders and prod_id relating it to prod_id in Products.

Database administrators often use relationship diagrams to help demonstrate how database tables are connected. Remember, it is foreign keys that define those relationships as noted in the table descriptions above. Figure A.1 is the relationship diagram for the five tables described in this appendix.

Figure A.1 Sample tables relationship diagram

Obtaining the Sample Tables

In order to follow along with the examples, you need a set of populated tables. Everything you need to get up and running can be found on this book’s web page at http://forta.com/books/0135182794/.

On that page you’ll find links to download SQL scripts for your DBMS. There are two files for each:

create.txt contains the SQL statements to create the five database tables (including defining all primary keys and foreign key constraints).

populate.txt contains the SQL INSERT statements used to populate these tables.

The SQL statements in these files are very DBMS specific, so be sure to execute the one for your own DBMS. These scripts are provided as a convenience to readers, and no liability is assumed for problems that may arise from their use.

At the time that this book went to press, scripts were available for

IBM DB2 (including Db2 on Cloud)

Microsoft SQL Server (including Microsoft SQL Server Express)

MariaDB

MySQL

Oracle (include Oracle Express)

PostgreSQL

SQLite

Tip: SQLite Data File

SQLite stores its data in a single file. You can use the creation and population scripts to create your own SQLite data file. Or, to make things easier, you can download a ready-to-use file from the URL above.

Other DBMSs may be added as needed or requested.

Note: Create, Then Populate

You must run the table creation scripts before the table population scripts. Be sure to check for any error messages returned by these scripts. If the creation scripts fail, you will need to remedy whatever problem may exist before continuing with table population.

Note: Specific DBMS Setup Instructions

The specific steps used to set up your DBMS vary greatly based on the DBMS used. When you download the scripts or databases from the book’s web page, you’ll find a README file that provides specific setup and installation steps for specific DBMSs.