SHIPMENT_LINE SHIP_ID INV_ID SL_QUANTITY SL_DATE_RECEIVED Number Number Number Date/ Time 25 09/10/2006 2 25 09/10/2006 2 25 5 200 6 200 7 200 12 100 08/15/2006 13 100 08/25/2006 23 50 08/15/2006 24 100 08/15/2006 25 100 08/15/2006 COLOR COLOR String Sky Blue Light Grey Khak Navy Royal Eggplant Blue Red Spruce Turquoise Bright Pink White Figure 1-24 Clearwater Traders database (continued) The ORDER_SOURCE table has OS_ID as the primary key and contains a column named OS_DESC, which describes the order source as a specific catalog or the com- pany Web site. The ORDERS table shows six customer orders. The table includes the O_ID column, which is a surrogate key that is the table's primary key; the O_DATE column, showing the date the customer places the order; the O_METHPMT column, indicating the payment method: CC (credit card) or CHECK; the C_ID column, a for- eignkoy that creates a relationship to the CUSTOMER table; and the OS_ID column, a foreign key that creates a relationship to the ORDER_SOURCE table. The first record shows information for O_ID 1, dated 5/29/2006, method of payment CC (credit card), and ordered by customer 1, Neal Graham. The OS_ID foreign key indicates that the Spring 2006 catalog was the source for the order. The CATEGORY table displays different product categories: Women's Clothing, Children's Clothing, Men's Clothing, and Outdoor Gear. The CAT_ID column is this table's primary key. The ITEM table contains seven different items. ITEM_ID is the table's primary key. The CAT_ID column is a foreign key that creates a relationship with the CATEGORY table. Item 1, Men's Expedition Parka, is in the Men's Clothing cate- gory. The ITEM_IMAGE column contains a text string that represents the name of the JPEG image file that stores an image of each item.Chapter 1 Client/Server Databases and the Oracle 10g Relational Database transaction volume generated by sales representatives processing incoming order. Managers are also concerned that the current database does not have the failure-handi;'s and recovery capabilities needed for an ordering system that cannot tolerate failures downtime. When a customer orders an item, the system must confirm that the ordered item is in stock. If the item is in stock, the system must update the available quantity on hand to reflect that the item has been sold. If the item is not in stock, the system needs to advise the customer when the item will be available. The customer must log onto the system using a username and password, and then complete the ordering process. Customers should also be able to log onto the system and view information about the status of their orders. When Clearwater Traders receives new inventory shipments, a receiving clerk at Clearwater Traders must update the inventory to show the new quantities on hand. The system must produce invoices that can be included with customer shipments and must print reports showing inventory levels. Marketing managers want to be able to track each order's source in terms of the catalog number or whether it originates on the Web site. These processes require the following data items: Customer name, address, daytime and evening telephone numbers, usernames, and passwords Order date, payment method (check or credit card), order source (catalog description or Web site), and associated item numbers, sizes, colors, and quan- tities ordered Item descriptions and photo images, as well as item categories (women's clothing, outdoor gear, and so on), prices, and quantities on hand. Many clothing items are available in multiple sizes and colors. Sometimes the same item has different prices depending on the item size. Information about incoming product shipments Figure 1-24 shows sample data for Clearwater Traders. Each database table displays the table name, column names, and the type of data (Number, String, or Date/Time) the col- umn stores. In Figure 1-24 and later figures, the primary key or keys will be positioned as the first columns in the figure. The CUSTOMER table displays six customer records Customer 1 is Neal Graham, who lives at 9815 Circle Dr., Tallahassee, FL, and his Zip code is 32308. His daytime telephone number is 904-555-1897, and his evening tele phone number is 904-555-8599. C_ID has been designated as the table's primary keyCUSTOMER C_ID C_LAST C_FIRST C_MI C_DOB C_ADDRESS C_CITY C_STATE C_ZIP Number String String String Date/Time String String String String Graham Neal R 12/10/1967 9815 Circle Dr Tallahassee FL 32308 Sanchez Myra 8/14/1958 172 Alto Park Seattle WA 42180 3 Smith Lisa M 4/12/1960 850 East Main Santa Ana CA 51875 Phelp Paul 1/18/ 1981 994 Kirkman Rd Northpoint NY 11795 Lewis Sheila 8/30/1978 195 College Blvd Newton GA 37812 6 James Thomas E 6/01/1973 348 Rice Lane Radcliff WY 87195 CUSTOMER (continued) C_DPHONE C_EPHONE C_USERID C_PASSWORD String String String String 9045551897 9045558599 grahamn barbiecar 418555179 4185556643 sanchezmt qwert5 3075557841 3075559852 smithlm joshua5 4825554788 4825558219 phelpp hold98er 3525554972 3525551811 lewissa 125pass 7615553485 7615553319 jamest nok$tell ORDER_SOURCE OS_ID OS_DESC Number String Winter 2005 2 Spring 2006 3 Summer 2006 14 Outdoor 2006 5 Children's 2006 Web Site ORDERS O_ID O_DATE O_METHPMT | C_ID OS_ID Number Date/ Time String Number Number 5/29/2006 CC 1 5 2 5/29/2006 CC 6 5/31/2006 CHECK 2 2 5/31/2006 CC 6/01/2006 CC 4 6/01/2006 CC 4 3 CATEGORY CAT_ID CAT_DESC Number | String Women's Clothing Children's Clothing Men's Clothing Outdoor Gear ITEM ITEM_ID ITEM DESC CAT_ID ITEM_IMAGE Number String Number String Men's Expedition Parka parka.jpg 3-Season Tent 4 tents.jpg Women's Hiking Shorts shorts.jpg Women's Fleece Pullover fleece.jpg Children's Beachcomber Sandals Boy's Surf Shorts Girl's Soccer Tee N N N - - sandals.jpg surfshorts.jpg girlstee.jpg Figure 1-24 Clearwater Traders databaseORDER_LINE O_ID INV_ID OL_QUANTITY Number Number Number N - 1 14 19 24 26 -- 12 1 8 2 13 1 w / -T SHIPMENT SHIP_ID SHIP_DATE_EXPECTED Number Date/ Time 09/15/2006 2 3 11/15/2006 4 06/25/2006 06/25/2006 08/15/2006 INVENTORY INV_ID ITEM_ID COLOR INV SIZE INV_PRICE INV_QOH Number Number String String Number Number 1 2 Sky Blue 259.99 16 2 W Light Grey 259.99 12 Khaki 29.95 150 4 3 S Khaki M 29.95 147 Khaki L 29.95 0 3 Navy 139 7 S 29.95 3 Navy M 29.95 8 3 137 Navy L 29.95 115 9 Eggplant VIA A A A A S 59.95 135 10 4 Eggplant M 59.95 Eggplant L 168 59.95 12 Roya S 187 59.95 0 13 Roya M 59.95 124 14 Roya L 59.95 15 Turquoise 10 112 15.9 16 15 Turquoise 11 121 15.99 111 17 15 Turquoise 12 15.99 18 15 Turquoise 113 15.9 5 Bright Pink 121 19 10 15.99 148 20 5 Bright Pink 11 15.99 21 5 Bright Pink 12 137 15.99 134 22 15 Bright Pink 15.99 1 1 Spruce 123 23 S M 199.95 Spruce 114 24 199.95 25 Spruce L 209.95 17 1 1 0 26 Spruce XL 209.95 27 6 Blue S 12 6 Blue M 15.95 15.95 50 28 29 6 Blue L 15.95 100 30 17 White 100 White M 19.99 19.99 100 31 32 7 White 19.99 100 100 -24 Clearwater Traders databaThe Database Cases 29 be INV_ID, because the same inventory item might be in several shipments. The pri- mary key must be a composite key comprising SHIP_ID and INV_ID, because each shipment might consist of multiple inventory items, but each inventory item is listed only once per shipment. Therefore, each SHIP_ID/INV_ID combination is unique for each record. SHIP_ID and INV_ID are also foreign keys in this table, because they ref- erence records in the SHIPMENT and INVENTORY tables. Figure 1-25 shows a visual representation of the Clearwater Traders database tables. In this representation, the primary key for each table appears in boldface, and foreign key columns appear in italics. Relationships between tables are represented by join lines. For example, a join line connects the C_ID column in the CUSTOMER table to the C_ID column in the ORDERS table. Note that for tables that contain a composite primary key, the columns that compose the primary key appear in boldface italic type. The ital- ics are added because the composite columns are also foreign keys. For example, in the ORDER_LINE table in Figure 1-25, the columns that make up the table's composite primary key (O_ID and INV_ID) appear in boldface italics. This indicates that the columns are part of a primary key and that the columns are also foreign keys. CUSTOMER T I'M ORDERS T M ORDER SOURCE T OC_ID 789 DO_ID 789 JOS_ID 789 OC_LAST A DO_DATE 31 DOS_DESC A OC_FIRST A DO_METHPMT |A OC_MI A OC_ID 789 OC_DOB 31 DOS_ID 789 SHIPMENT T OC_ADDRESS A O SHIP_ID 789 DC_CITY A OSHIP_DATE_EXPECTED | 31 OC_STATE A OC_ZIP A OC_DPHONE A M ORDER LINE T DC_EPHONE A DO_ID 789 M SHIPMENT LINE T DC_USERID A O INV_ID 789 O SHIP_ID 789 C_PASSWORD A OOL_QUANTITY 789 O INV_ID 789 OSL_QUANTITY 789 SL_DATE_RECEIVED 31 ITEM T ITEM_ID 789 ITEM_DESC A O CAT_ID 789 INVENTORY T DITEM_IMAGE A INV_ID 789 789 M COLOR T ITEM_ID COLOR A COLOR A CATEGORY INV_SIZE A CAT_ID T 7894 INV_PRICE 789 O CAT_DESC O INV_QOH 789 A Figure 1-25 Visual representation of the Clearwater Traders databaseManager-Used for performing database administration tasks such as creating new user accounts and configuring how the DBMS stores 1 and manages data HE DATABASE CASES In this book, you will encounter tutorial exercises and end-of-chapter cases that illus- trate how to use Oracle 10g using databases developed for Clearwater Traders and Northwoods University, two fictional organizations. As previously mentioned, the focus of this book is on database development rather than on database design, so the rationale behind the design of all the database tablesis not described. Nevertheless, it is important that you remember a few general database design principles. You should follow these principles when creating database tables: . Unless instructed otherwise, convert all tables to third normal form. To link tables, make certain you include the primary key as the foreign key in the table on the "many" side of the relationship. . When creating a database and inserting data values, you must specify the data type for each column. Different database management systems have specific names for their data types, but in general, data types include numbers, text strings, date/time values, time interval values, or binary data such as images or sounds. In general, you should use only a number data type for columns that store numerical values that are involved in calculations. For example, you would store inventory quantity-on-hand values in number columns, but you would not store telephone numbers in number columns. You store data values that contain numeric characters that are not used in calculations, such as tele- phone numbers and postal codes, as text strings. Client/server DBMSs are appropriate for the case study databases because these systems have many simultaneous users accessing the system from different locations. Each data- base has data examples to illustrate database development tasks such as creating and maintaining database tables, retrieving data, and developing data entry and maintenance forms and output reports. The Clearwater Traders Sales Order Database Clearwater Traders markets a line of clothing and sporting goods through mail-order cat- alogs. Clearwater Traders currently accepts customer orders through telephone, mail, and fax, and wants to begin accepting orders using its Web site. The company recently exper rienced substantial growth and, as a result, has decided to offer 24-hour customer order service. The existing microcomputer-based database system cannot handle the currentChapter 1 Client/ Server The INVENTORY table contains specific inventory numbers for specific merchandise item sizes and colors. It also shows the price and quantity on hand (QOH) for each item Items that are not available in different sizes contain NULL, or undefined, values in their INV_SIZE columns. Notice that some items have different prices for different sizes. For example, for ITEM_ID 1 (Men's Expedition Parka), the small (S) and medium (M) inventory items are priced at $199.95, whereas the large (L) and extra large (XL) items are priced at $209.95. INV_ID is the primary key of this table, and ITEM_ID is a for- eignkoy that creates a relationship with the ITEM table. The ORDER_LINE table represents the individual inventory items in a customer order. The first line of O_ID 1 specifies one Sky Blue 3-Season Tent, and the second line of this order specifies two large Royal-colored Women's Fleece Pullovers. This infor- mation is used to create the printed customer order invoice and to calculate sales rev- enues. Note that the primary key of this table is not O_ID, because more than one record might have the same O_ID. The primary key is a composite key made up of the com- bination of O_ID and INV_ID. An order might have several different inventory items, but it will never have the same inventory item listed more than once. Along with being part of the primary key, O_ID and INV_ID are also foreign keys because they create relationships to the ORDERS and INVENTORY tables. The COLOR column of the INVENTORY table is also a foreign key, which references the COLOR table. The COLOR table is a lookup table. A lookup table is also some- times called a pick list. It contains a list of legal values for a column in another table. Notice the variety of colors shown in the INVENTORY table (Sky Blue, Light Grey, Khaki, Navy, Royal, and so on). If users are allowed to type these colors each time an inventory item is added to the table, data entry errors might occur. For example, a query looking for sales of items with the Light Grey color will not find instances if Light Grey is spelled Light Gray, or is specified with a different combination of upper- and lower- case letters, such as Light grey or LIGHT GREY. Typically, when a Clearwater Traders employee user enters a new inventory item, he or she selects a color from a pick list that displays values from the COLOR table. Thus the user need not type the color directly, which reduces errors. Small lists that are unlikely to change over time might be coded directly into an application, but large lists to which items might be added over time are usually stored in a separate lookup table. The SHIPMENT table contains a schedule of expected shipments and the date each shipment is expected. The primary key of the SHIPMENT table is SHIP_ID. A ship ment can include multiple inventory items, so the SHIPMENT LINE table records the corresponding shipment ID, the inventory ID, the quantity of each item, and the da each item was received. If a shipment line has not been received, the date value is NU The first SHIPMENT_LINE record shows that the first line of SHIP ID 1 is for 25 5 Blue 3-Season Tents. The second line of SHIP_ID 1 is for 25 Light Grey 3-Season Tem Both items were received on 09/10/2006. Notice that the primary key of this table not be SHIP_ID, because some shipments have multiple lines. The primary key camPROBLEM-SOLVING CASES 1. The manager for the Clearwater Traders wants to collect the following data for each order placed by a customer: customer's name and address, item(s) ordered, each item. quantity of each item, item's size or color-if applicable-and the retail price of a. Create an entity-relationship model representing the data that the manager wants to store in the database, based on the following assumptions: o Each customer can place multiple orders. 0 Each order can only belong to a single customer. o Different items can be ordered on the same order. Each item on an order can have a different size and/or color, and some items may not have a size or color. b. Based on the data elements the Clearwater Traders' manager wants you to include in the database, assign an appropriate field name to each of the data elements. Make certain the name of each field is descriptive so it will be easily recognizable in the final version of the database. c. Take the named data elements from the previous step and convert the data to first normal form.40 Chapter 1 Client/Server Databases and the Oracle 10g Relational Database d. Convert the first normal form of the data elements to second normal form. Use the shorthand method to specify which elements belong to each entity and underline the primary key for each element. e. Convert each of the previously identified entities to third normal form. Make certain that the necessary foreign keys have been added to the final tables to support the relationships shown in your initial ER model. 2. Identify the tables and fields that must be referenced in the Clearwater Traders database to answer the following questions: a. How many 3-Season Tents are left in stock? b. Which customer(s) lives in Georgia? c. How many customers placed an order in May 2006? d. Who was the last customer to order a Men's Expedition Parka? e. Which items of women's clothing were sold between May 29 and June 1? 3. Using the Clearwater Traders data set presented in Figure 1-24, determine the answer for each of the five questions asked in Problem 2. 4. Based on the Clearwater Traders data set presented in Figure 1-24, draft an ER model representing the relationships in the database. 5. Using the ER model created in Problem 4, create a list of each table using the shorthand method shown in Figure 1-19. Underline the primary key of each table once and underline any foreign keys the table may contain twice