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
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
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

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
  • SEE MORE 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