| Informix Guide to SQL: Reference The stores_demo Database |
|
|
The tables of the stores_demo database are linked by the primary-foreign key relationships that Figure A-10 on page A-11 shows and 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. Figure A-11 through Figure A-18 show the relationships among tables and how information stored in one table supplements information stored in others.
The customer table contains a customer_num column that holds a number that identifies a customer and columns for the customer name, company, address, and telephone number. For example, the row with information about Anthony Higgins contains the number 104 in the customer_num column. 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. Figure A-11 shows this relationship.
According to Figure A-11, customer 104 (Anthony Higgins) has placed two orders, as his customer number appears in two rows of the orders table. Because the customer number is a foreign key in the orders table, you can retrieve Anthony Higgins' name, address, and information about his orders at the same time.
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. Figure A-12 shows this relationship.
The items table and the stock table are joined by two columns: the stock_num column, which stores a stock number for an item, and the manu_code column, which stores a code that identifies the manufacturer. You need both the stock number and the manufacturer code to uniquely identify an item. For example, the item with the stock number 1 and the manufacturer code HRO is a Hero baseball glove; the item with the stock number 1 and the manufacturer code HSK is a Husky baseball glove. 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 and manu_code columns are foreign keys that reference the stock_num and manu_code columns in the stock table. Figure A-13 shows this relationship.
The stock table and catalog table are joined by two columns: the stock_num column, which stores a stock number for an item, and the manu_code column, which stores a code that identifies the manufacturer. You need both columns to uniquely identify an item. In the catalog table, the stock_num and manu_code columns are foreign keys that reference the stock_num and manu_code columns in the stock table. Figure A-14 shows this relationship.
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. Figure A-15 shows this relationship.
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. Figure A-16 shows this relationship.
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. Figure A-17 shows this relationship.
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. Figure A-18 shows this relationship.
