Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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. 4. Indicate the changes you need to make to the BITS database design to support the following situation. The region where clients are located is divided into territories. For each territory, store the territory number (a unique identifier) and territory name. Each consultant is assigned to a single territory. Each client also is assigned to a single territory, which may not be the same as the territory to which the client's consultant is assigned.

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. 4. Indicate the changes you need to make to the BITS database design to support the following situation. The region where clients are located is divided into territories. For each territory, store the territory number (a unique identifier) and territory name. Each consultant is assigned to a single territory. Each client also is assigned to a single territory, which may not be the same as the territory to which the client's consultant is assigned.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question

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.

4. Indicate the changes you need to make to the BITS database design to support the following situation. The region where clients are located is divided into territories. For each territory, store the territory number (a unique identifier) and territory name. Each consultant is assigned to a single territory. Each client also is assigned to a single territory, which may not be the same as the territory to which the client's consultant is assigned.

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
Transcribed Image 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
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. a.
Transcribed Image 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. a.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 5 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Enhanced ER Model
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS