The tables of the superstores_demo database are linked by the primary-foreign key relationships that are identified in this section. This type of relationship is called a referential constraint because a foreign key in one table references the primary key in another table.
The customer table contains a customer_num column that holds a number that identifies a customer. The orders table also contains a customer_num column that stores the number of the customer who placed a particular order. In the orders table, the customer_num column is a foreign key that references the customer_num column in the customer table.
The orders and items tables are linked by an order_num column that contains an identification number for each order. If an order includes several items, the same order number appears in several rows of the items table. In the items table, the order_num column is a foreign key that references the order_num column in the orders table.
The items table and the stock table are joined by three columns: the stock_num column, which stores a stock number for an item, the manu_code column, which stores a code that identifies the manufacturer, and the units column, which identifies the types of unit in which the item can be ordered. You need the stock number, the manufacturer code, and the units to uniquely identify an item. The same stock number and manufacturer code can appear in more than one row of the items table, if the same item belongs to separate orders. In the items table, the stock_num, manu_code, and unit columns are foreign keys that reference the stock_num, manu_code, and unit columns in the stock table.
The stock table and catalog table are joined by three columns: the stock_num column, which stores a stock number for an item, the manu_code column, which stores a code that identifies the manufacturer, and the unit column, which identifies the type of units in which the item can be ordered. You need all three columns to uniquely identify an item. In the catalog table, the stock_num, manu_code, and unit columns are foreign keys that reference the stock_num, manu_code, and unit columns in the stock table.
The stock table and the manufact table are joined by the manu_code column. The same manufacturer code can appear in more than one row of the stock table if the manufacturer produces more than one piece of equipment. In the stock table, the manu_code column is a foreign key that references the manu_code column in the manufact table.
The cust_calls table and the customer table are joined by the customer_num column. The same customer number can appear in more than one row of the cust_calls table if the customer calls the distributor more than once with a problem or question. In the cust_calls table, the customer_num column is a foreign key that references the customer_num column in the customer table.
The call_type and cust_calls tables are joined by the call_code column. The same call code can appear in more than one row of the cust_calls table, because many customers can have the same type of problem. In the cust_calls table, the call_code column is a foreign key that references the call_code column in the call_type table.
The state table and the customer table are joined by a column that contains the state code. This column is called code in the state table and state in the customer table. If several customers live in the same state, the same state code appears in several rows of the table. In the customer table, the state column is a foreign key that references the code column in the state table.
In the customer table, the customer_loc column is a foreign key that references the location_id of the location table. The customer_loc and location_id columns each uniquely identify the customer location.
The manu_loc column in the manufact table is a foreign key that references the location_id column, which is the primary key in the location table. Both manu_loc and location_id uniquely identify the manufacturer location.
The state and location_us tables are joined by the column that contains the state code. The state_code column in the location_us table is a foreign key that references the code column in the state table.
The region_num column is the primary key in the region table. It is a system-generated region number. The region_num column in the sales_rep table is a foreign key that references and joins the region_num column in the region table.
The region_loc column in the region table identifies the regional office location. It is a foreign key that references the location_id column in the location table, which is a unique identifier for location.
The stock table and the stock_discount table are joined by three columns: stock_num, manu_code, and unit. These columns form the primary key for the stock table. The stock_discount table has no primary key and references the stock table.
The unit_name column of the units table is a primary key that identifies the kinds of units that can be ordered, such as case, pair, box, and so on. The unit column of the stock table joins the unit_name column of the units table.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]