BITS Corporation Exercises: Relational Algebra

• In the following exercises, you will use the data in the BITS Corporation database shown in Figure 2-1. In each step, indicate how to use relational algebra to obtain the desired results.
• Save the file on your computer with your last name in the file name. (Example: case_1-1 _Jones.doc)
• Click the Choose File button to find and select your saved document.

1. List the number and name of all consultants an also list all information from the Tasks table for item WC19.
2. List the order number, order date, client number, and client name for each order placed by any client represented by the consultant whose last name is Turner then List the order number, order date, client number, and client name for each order.
3. List the number and date of all orders that were placed on 9/10/2018 or that were placed by a client whose consultant number is 19.
4. List the number and date of all orders that were placed on 9/10/2018 by a client whose consultant number is 35.
5. List the number and date of all orders that were placed on 9/10/2018 but not by a client whose consultant number is 35.

Rate $22.50 $22.50 $20.00 $15.00 Consultant ConsitNum LastName FirstName Street City State Zip Code Hours 19 Turner Christopher 554 Brown Dr. "Tri City FL 32889 40 22 Jordan Patrick 2287 Port Rd. Easton FL. 3399840 35 Allen Sarah 82 Elliott St. Lixton FL. 34344 35 51 Shields Tom 373 Lincoln Ln. Sunland FL 39876 10 Client ClientNum ClientName Street City State Zip Code Balance 143 Hershey, Jarrod 135 E. Mill Street Easton FL. 33998 $1,904.55 175 Goduto, Sean 12 Saratoga Parkway Tri City FL. 32889 $2,814.55 299 Two Crafty 9787 NCR 350 Sunland 39876 $8,354.00 Cousins West 322 Prichard's Pizza & 501 Air Parkway Lizton FL. 34344 $7,335.55 Pasta 363 Salazar, Jason 56473 Cherry Tree Dr Easton 33998 $900.75 405 Fisherman's Spot 49 Elwood Ave. Harpersburg FL 31234 $4,113.40 Shop 449 Seymour, 4091 Brentwood Ln Amo 34466 $557.70 CreditLimit ConsitNum $2,500.00 19 $5,000.00 19 $10,000.00 22 $10,000.00 35 $2,500.00 $7,500.00 35 19 $5,000.00 0.0022 Lindsey 22 35 22 19 19 363 458 Bonnie's Beautiful 9565 Ridge Rd. Tri City FL. 32889 $4,053.80 $7,500.00 Boutique 677 Yates, Nick 231 Day Rd. Sunland FL. 39876 $2,523.80 $2,500.00 733 Howler, Laura 1368 E 1000 S. Lizton FL. 34344 $3,658.05 $5,000.00 826 Harpersburg Bank 65 Forrest Blvd. Harpersburg FL. 31234 $6,824.55 $10,000.00 867 MarketPoint Sales 826 Host St. Easton 33998 $3,089.00 $5,000.00 WorkOrders OrderLine Order Num Order Date Client Num Order Num TaskID ScheduledDate QuotedPrice 67101 9/6/2018 733 67101 SI77 9/10/2018 $144.00 67313 9/7/2018 458 67313 LASI 9/12/2018 $104.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 LASI 9/20/2018 $104.00 68252 9/12/2018 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 AC65 9/27/2018 $77.00 68979 DA11 9/27/2018 S970.00 Tasks Tasked Description Category Price AC65 Accessories ACC $80.00 DA11 Data recovery major DRM $175.00 D185 Data recovery minor DRM $50.00 HA63 Hardware major HAM $225.00 HI31 Hardware minor HAM $165.70 LA81 Local area networking (LAN) LAN $104.00 M049 Mobility MOB $65.00 OT99 Other work OTH $99.99 PIS4 Printing issues PRI $50.00 SA44 Software major SOM $200.00 SI77 Software minor SOM $144.00 SI91 Security install/repair SIR $126.00 UP38 Upgrades UPG $185.00 VR39 Virus removal $90.00 WA33 Wide area networking (WAN) WAN $130.00 WC19 Web connectivity WEC $75.00 VIR FIGURE 2-1 Sample data for BITS Rate $22.50 $22.50 $20.00 $15.00 Consultant ConsitNum LastName FirstName Street City State Zip Code Hours 19 Turner Christopher 554 Brown Dr. "Tri City FL 32889 40 22 Jordan Patrick 2287 Port Rd. Easton FL. 3399840 35 Allen Sarah 82 Elliott St. Lixton FL. 34344 35 51 Shields Tom 373 Lincoln Ln. Sunland FL 39876 10 Client ClientNum ClientName Street City State Zip Code Balance 143 Hershey, Jarrod 135 E. Mill Street Easton FL. 33998 $1,904.55 175 Goduto, Sean 12 Saratoga Parkway Tri City FL. 32889 $2,814.55 299 Two Crafty 9787 NCR 350 Sunland 39876 $8,354.00 Cousins West 322 Prichard's Pizza & 501 Air Parkway Lizton FL. 34344 $7,335.55 Pasta 363 Salazar, Jason 56473 Cherry Tree Dr Easton 33998 $900.75 405 Fisherman's Spot 49 Elwood Ave. Harpersburg FL 31234 $4,113.40 Shop 449 Seymour, 4091 Brentwood Ln Amo 34466 $557.70 CreditLimit ConsitNum $2,500.00 19 $5,000.00 19 $10,000.00 22 $10,000.00 35 $2,500.00 $7,500.00 35 19 $5,000.00 0.0022 Lindsey 22 35 22 19 19 363 458 Bonnie's Beautiful 9565 Ridge Rd. Tri City FL. 32889 $4,053.80 $7,500.00 Boutique 677 Yates, Nick 231 Day Rd. Sunland FL. 39876 $2,523.80 $2,500.00 733 Howler, Laura 1368 E 1000 S. Lizton FL. 34344 $3,658.05 $5,000.00 826 Harpersburg Bank 65 Forrest Blvd. Harpersburg FL. 31234 $6,824.55 $10,000.00 867 MarketPoint Sales 826 Host St. Easton 33998 $3,089.00 $5,000.00 WorkOrders OrderLine Order Num Order Date Client Num Order Num TaskID ScheduledDate QuotedPrice 67101 9/6/2018 733 67101 SI77 9/10/2018 $144.00 67313 9/7/2018 458 67313 LASI 9/12/2018 $104.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 LASI 9/20/2018 $104.00 68252 9/12/2018 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 AC65 9/27/2018 $77.00 68979 DA11 9/27/2018 S970.00 Tasks Tasked Description Category Price AC65 Accessories ACC $80.00 DA11 Data recovery major DRM $175.00 D185 Data recovery minor DRM $50.00 HA63 Hardware major HAM $225.00 HI31 Hardware minor HAM $165.70 LA81 Local area networking (LAN) LAN $104.00 M049 Mobility MOB $65.00 OT99 Other work OTH $99.99 PIS4 Printing issues PRI $50.00 SA44 Software major SOM $200.00 SI77 Software minor SOM $144.00 SI91 Security install/repair SIR $126.00 UP38 Upgrades UPG $185.00 VR39 Virus removal $90.00 WA33 Wide area networking (WAN) WAN $130.00 WC19 Web connectivity WEC $75.00 VIR FIGURE 2-1 Sample data for BITS