Concepts of Database Management (9th Edition) See this solution in the app Chapter 5, Problem 2BCE 1 Bookmark Show all steps: ON Problem List the functional dependencies in the following table that concern invoicing (an application BITS is considering adding to its database), subject to the specified conditions. For a given invoice (identified by the InvoiceNum), there will be a single client. The client's number, name, and complete address appear on the invoice, as does the date. Also, there may be several different tasks appearing on the invoice. For each task that appears, display the Tasked, description, category, and price. Assume that each client that requests a particular service task pays the same price. Convert this table to an equivalent collection of tables that are in third normal form: Invoice (InvoiceNum, Client Num, Last Name, FirstName, Street, City. State, ZipCode, Date, (TaskID, Description, Category, Price)) Chapter 5 Many of the examples in this chapter use data from the BITS database, which is shown in Figure 5.1. Rate 822.50 $22.50 $20.00 $15.00 164 CreditLimit ConskNum $2,500.00 19 $5,000.00 19 Consultant Hours State Zip Code City First Name Street LastName Conslt Num 40 32889 Tri City FL Turner Christopher 554 Brown Dr. 19 33998 40 Easton Patrick 2287 Port Rd. Jordan 22 FL Lizton 34344 35 Sarah 82 Elliott St. Allen 35 39876 10 Tom Shields 373 Lincoln Ln. Sunland FL 51 Client City Balance State Zip Code Street ClientNum ClientName Easton FL 33998 $1,904.55 143 Hershey, Jarrod 135 E. Mill Street FL Tri City 32889 $2,814.55 12 Saratoga Parkway 175 Goduto, Sean Sunland FL 9787 NCR 350 West 39876 Two Crafty 299 $8,354.00 $10,000.00 22 Cousins Lizton FL 501 Air Parkway Prichard's 34344 322 $7,335.55 $10,000.00 35 Pizza & Pasta 363 Salazar, Jason 56473 Cherry Tree Dr. Easton FL 33998 $900.75 405 Fisherman's 49 Elwood Ave. Harpersburg FL 31234 $4,113.40 Spot Shop 449 Seymour, 4091 Brentwood Ln. Amo FL 34466 $557.70 Lindsey 458 Bonnie's 9565 Ridge Rd. Tri City FL 32889 $4,053.80 Beautiful Boutique Yates, Nick 231 Day Rd. Sunland FL 39876 $2,523.80 $2,500.00 35 733 Howler, Laura 1368 E. 1000 S. Lizton FL 34344 $3,658.05 $5,000.00 22 826 Harpersburg 65 Forrest Blvd. Harpersburg FL 31234 $6,824.55 $10,000.00 19 Bank 867 MarketPoint 826 Host St. Easton FL 33998 $3,089.00 $5,000.00 19 Sales $2,500.00 35 $7,500.00 19 $5,000.00 22 $7,500.00 22 677 Tasks TaskID Price AC65 DA11 D185 HA63 HI31 LA81 MO49 OT99 P154 SA44 SI77 SI91 Description Category Accessories ACC Data recovery major DRM Data recovery minor DRM Hardware major HAM Hardware minor HAM Local area networking (LAN) LAN Mobility MOB Other work OTH Printing issues PRI Software major SOM Software minor SOM Security install/repair SIR Upgrades UPG Virus removal VIR Wide area networking (WAN) WAN Web connectivity WEC $80.00 $175.00 $50.00 $225.00 $165.70 $104 00 $65.00 $99.99 $50.00 $200.00 $144.00 $126.00 $185.00 $90,00 $130,00 $75,00 OrderLine Order Num TaskID ScheduledDate QuotedPrice 67101 SI77 9/10/2018 $144,00 67313 LA81 9/12/2018 $104,00 67424 MO49 9/14/2018 $65.00 67424 UP38 9/14/2018 $185.00 67838 LA81 9/20/2018 $104.00 67949 PI54 9/21/2018 $50.00 67949 VR39 9/21/2018 $88.00 67949 WA33 9/21/2018 $126.00 68252 D185 9/24/2018 $50.00 68868 SA44 9/24/2018 $200.00 68979 AC65 9/27/2018 $77.00 68979 DA11 9/27/2018 $970.00 Work Orders Order Num OrderDate Client Num 67101 9/6/2018 733 67313 9/7/2018 458 67424 9/10/2018 322 67838 9/10/2018 867 67949 9/10/2018 322 68252 9/12/2018 363 68868 9/14/2018 867 68979 9/17/2018 826 UP38 VR39 WA33 WC19 FIGURE 5-1 Sample data for BITS
