For the following exercises, you will answer problems and questions from management at TAL Distributors. You do not use the TAL Distributors database for any of these exercises.
1. Fragment the Customer table so that customers of rep 15 form a fragment named CustomerRep15, customers of rep 30 form a fragment named CustomerRep30, customers of rep 45 form a fragment named Customer- Rep45, and customers of rep 60 form a fragment named CustomerRep60. (Include all fields from the Customer table in each fragment.) In addition, you need to fragment the Orders table so that orders are distributed and stored with the customers that placed the orders. For example, fragment OrdersRep15 consists of those orders placed by customers of rep 15. Write the SQL-type statements to create these fragments.
2. Create a class diagram for the TAL Distributors database, as shown in Figure 1-5 in Chapter 1. If you need to make any assumptions when preparing the class diagram, document those assumptions.
3. A user queries the Item table in the TAL Distributors database over the company intranet. Assume the Item table contains 5,000 rows, each row is 1,000 bits long, the access delay is 2.5 seconds, the transmission rate is 50,000 bits per second, and only 20 of the 5,000 rows in the Item table satisfy the query conditions. Calculate the total communication time required for this query based on retrieving all table rows one row at a time, and then calculate the total communication time required based on retrieving the 20 rows that satisfy the query conditions in a single message.

Figure 1-5

Transcribed Image Text:

Rep RepNum LastName FirstName Street Campos Gradey Tian State PostalCode Commission Rate City Rafael 724 Vinca Dr. Grove CA $23,457.50 $41,317.00 $27,789.25 $0.00 15 90092 0.06 Megan Hui 632 Liatris St. Fullton CA 30 90085 0.08 1785 Tyler Ave. 267 Oakley St. Northfield Congaree CA 90098 0.06 45 Sefton Janet CA 60 90097 0.06 Customer CustomerNum CustomerName Street Toys Galore State PostalCode Balance $1,210.25 CreditLimit RepNum $7,500.00 15 City 28 Laketon St. Fullton CA 126 90085 260 Brookings Direct 452 Columbus Dr. Grove CA 90092 $575.00 $10,000.00 30 342 Magee St. $7,500.00 45 The Everything Shop Johnson's Department Store Congaree CA $2,345.75 334 90097 386 124 Main St. Northfield CA $879.25 $7,500.00 30 90098 Grove 3456 Central Ave. Fullton CA $345.00 $5,000.00 45 440 90085 Historical Museum Store Cards and More 167 Hale St. Mesa CA $5,025.75 $5,000.00 15 502 90104 Almondton 3345 Devon Ave. Almondton CA $3,456.75 $15,000.00 45 586 90125 General Store Cricket Gift Shop 372 Oxford St. Grove CA 665 90092 $678.90 $4,234.60 $7,500.00 30 $10,000.00 15 12 Rising Sun Ave. Congaree 786 Passmore St. Northfield CA Cress Store 713 90097 Unique Gifts CA $124.75 $7,500.00 45 796 90098 Kline's 945 Gilham St. Mesa CA 90104 $2,475.99 $15,000.00 30 824 All Season Gifts 382 Wildwood Ave. Fullton CA $935.75 $7,500.00 15 893 90085 Orders OrderLine OrderNum ItemNum NumOrdered QuotedPrice OrderNum OrderDate CustomerNum 10/12/2015 51608 CD33 $86.99 51608 126 10/12/2015 KL78 $10.95 334 51610 51610 25 $13.99 51613 10/13/2015 386 TR40 51610 10 $104.95 10/13/2015 DL51 51614 260 51613 $124.95 10/15/2015 FD11 51617 586 51614 NL89 $115.99 10/15/2015 51617 51619 126 10/15/2015 $116.95 TW35 51623 586 51617 FD11 $121.95 10/16/2015 51625 796 51619 $29.95 DR67 51623 $36.95 FH24 51623 12 $13.10 KD34 51623 10 $45.79 51625 МТоз Item ItemNum Description AH74 OnHand Category Storehouse Price Patience 9 GME $22.99 BR23 Skittles GME $29.99 21 Wood Block Set (48 piece) CD33 36 TOY $89.49 Classic Railway Set 12ΤΟΥ DL51 $107.95 DR67 Giant Star Brain Teaser 24 PZL $31.95 40 GME DW23 Mancala $50.00 3. Rocking Horse 8 TOY FD11 $124.95 FH24 Puzzle Gift Set 65 PZL $38.95 56 GME 60 PZL 110 GME $75.00 KA12 Cribbage Set KD34 Pentominoes Brain Teaser $14.95 KL78 Pick Up Sticks $10.95 $45.79 МТоз Zauberkasten Brain Teaser 45 PZL. Wood Block Set (62 piece) Tic Tac Toe Fire Engine NL89 32 ΤΟΥ $119.75 TR40 GME $13.99 75 30 ΤΟΥ TW35 $118.95