The following exercises are based on the BITS database user views as designed in Your Turn 6-1 in this chapter. In each exercise, represent your answer in DBDL and with an Entity Relationship Diagram.
5. Indicate the changes you need to make to the BITS database design to support the following situation. The client address may or may not be the same as the Bill To address on the client's invoice.
Extracted text: YOUR TURN 6-1 Complete an information-level design for a database that satisfies the following constraints and user view requirements for a company that stores information about consultants, clients, tasks, and work orders. User View 1 Requirements: For a consultant, store the consultant's number, name, address, hours, and rate. User View 2 Requirements: For a client, store the client's number, name, address, balance, and credit limit. In addition, store the number and name of the consultant who represents this client. A consultant can represent many elients, but a client must have exactly one consultant. (A client must have a consultant and cannot have more than one consultant.) User View 3 Requirements: For cach service task, store the task's ID number, description, category, and <. price.="" user="" view="" 4="" requirements:="" for="" an="" order,="" store="" the="" order="" number;="" order="" date;="" number,="" name,="" and="" address="" of="" the="" client="" who="" placed="" the="" order;="" and="" number="" of="" the="" eonsultant="" who="" services="" that="" elient.="" in="" addi-="" tion,="" for="" each="" line="" item="" within="" the="" order,="" store="" the="" task="" id="" number,="" description,="" scheduled="" date,="" and="" quoted="" price.="" the="" user="" also="" has="" supplied="" the="" following="" constraints:="" each="" order="" must="" be="" placed="" by="" a="" client="" who="" is="" already="" in="" the="" client="" table.="" b.="" there="" is="" only="" one="" client="" per="" order.="" c.="" on="" a="" given="" order,="" there="" is,="" at="" most,="" one="" line="" item="" for="" a="" given="" task.="" for="" example,="" task="" las1="" cannot="" appear="" in="" several="" lines="" within="" the="" same="" order.="" d.="" the="" quoted="" price="" might="" not="" match="" the="" current="" price="" in="" the="" tasks="" table,="" allowing="" the="" company="" to="" provide="" services="" to="" different="" clients="" at="" different="" prices.="" the="" user="" wants="" to="" be="" able="" to="" change="" the="" price="" for="" a="" task="" without="" affecting="" orders="" that="" are="" currently="" on="" file.="">
Extracted text: Consultant ConsltNum LastName FirstName Street City Tri City Easton Lizton Sunland State ZipCode Hours FL. Rate Turner Jordan Allen Christopher Patrick Sarah 19 554 Brown Dr. 32889 40 $22.50 22 2287 Port Rd. FL. 33998 40 $22.50 FL. FL. 35 82 Elliott St. 373 Lincoln Ln. 34344 35 $20.00 $15.00 51 Shields Тom 39876 10 Client ClientNum ClientName Street City Easton State ZipCode Balance FL. CreditLimit ConsitNum Hershey, Jarrod Goduto, Scan 135 E. Mill Street 12 Saratoga Parkway Tri City 143 33998 $1,904.55 $2,500.00 19 175 FL. 32889 $2,814.55 $5,000.00 19 Two Crafty Cousins 299 9787 NCR 350 Sunland FL. 39876 $8,354.00 $10,000.00 22 West 322 Prichard's Pizza & 501 Air Parkway Lizton FL. 34344 $7,335.55 $10,000.00 35 Pasta 363 Salazar, Jason 56473 Cherry Tree Dr Easton FL 33998 $900.75 $2,500.00 35 Harpersburg FL. Fisherman's Spot Shop 405 49 Elwood Ave. 31234 $4,113.40 $7,500.00 19 449 FL Seymour, Lindsey 4091 Brentwood Ln Amo 34466 $557.70 $5,000.00 22 Tri City Bonnie's Beautiful 9565 Ridge Rd. Boutique Yates, Nick Howler, Laura 458 FL 32889 $4,053.80 $7,500.00 22 677 231 Day Rd. Sunland FL 39876 $2,523.80 $2,500.00 35 733 1368 E. 1000 S. Lizton FL. 34344 $3,658.05 $5,000.00 22 826 Harpersburg Bank 65 Forrest Blvd. Harpersburg FL. 31234 $6,824.55 $10,000.00 19 867 MarketPoint Sales 826 Host St. Easton FL. 33998 $3,089.00 $5,000.00 19 WorkOrders OrderLine OrderNum OrderDate ClientNum OrderNum TaskID ScheduledDate QuotedPrice 67101 9/6/2018 733 67101 S177 9/10/2018 $144.00 67313 9/7/2018 458 67313 LASI 9/12/2018 S104.00 67424 9/10/2018 322 67424 MO49 9/14/2018 $65.00 67838 9/10/2018 867 67424 UP38 9/14/2018 $185.00 67949 9/10/2018 322 67838 LAS1 9/20/2018 $104.00 68252 9/12/2018 363 67949 PIS4 9/21/2018 $50.00 68868 9/14/2018 867 67949 VR39 9/21/2018 $88.00 68979 9/17/2018 826 67949 WA33 9/21/2018 $126.00 68252 DISS 9/24/2018 $50.00 68868 SA44 9/24/2018 $200.00 68979 АC65 9/27/2018 $77.00 68979 DA11 9/27/2018 $970.00 Tasks TaskID Description Category Price AC65 Accessories ACC $80.00 DA11 DRM Data recovery major Data recovery minor Hardware major $175.00 DI85 DRM $50.00 HA63 НАМ $225.00 HI31 Hardware minor НАМ $165.70 LAS1 Local area networking (LAN) LAN Mobility $104.00 MO49 MOB $65.00 OT99 Other work OTH $99.99 PIS4 Printing issues Software major PRI $50.00 SA44 SOM $200.00 S177 Software minor SOM $144.00 S191 Security installfrepair Upgrades SIR $126.00 UP38 UPG $185.00 VR39 Virus removal VIR $90.00 Wide area networking (WAN) WAN Web connectivity WA33 $130.00 WC19 WEC $75.00 FIGURE 2-1| Sample data for BITS