Based on 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. 2. Indicate the changes you need to make to the design of the BITS database to support the following situation. There is no relationship between clients and consultants. When a client places an order (service request), it may be performed by any consultant. On the order, identify both the client placing the order and the consultant responsible for the order.
Based on 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. 2. Indicate the changes you need to make to the design of the BITS database to support the following situation. There is no relationship between clients and consultants. When a client places an order (service request), it may be performed by any consultant. On the order, identify both the client placing the order and the consultant responsible for the order.
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
Related questions
Question
100%
Based on BITS
2. Indicate the changes you need to make to the design of the BITS database to support the following situation. There is no relationship between clients and consultants. When a client places an order (service request), it may be performed by any consultant. On the order, identify both the client placing the order and the consultant responsible for the order.
![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.](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F5fb0902d-19fc-4913-9f11-cf5e6943390f%2Fcc45a6e9-296a-4a02-8e94-62cfcbb4f00b%2Fqd7z3ub_processed.png&w=3840&q=75)
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.
![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](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F5fb0902d-19fc-4913-9f11-cf5e6943390f%2Fcc45a6e9-296a-4a02-8e94-62cfcbb4f00b%2Fgtxou4_processed.png&w=3840&q=75)
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
Expert Solution
![](/static/compass_v2/shared-icons/check-mark.png)
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 3 images
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
Knowledge Booster
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.Recommended textbooks for you
![Database System Concepts](https://www.bartleby.com/isbn_cover_images/9780078022159/9780078022159_smallCoverImage.jpg)
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)](https://www.bartleby.com/isbn_cover_images/9780134444321/9780134444321_smallCoverImage.gif)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
![Digital Fundamentals (11th Edition)](https://www.bartleby.com/isbn_cover_images/9780132737968/9780132737968_smallCoverImage.gif)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
![Database System Concepts](https://www.bartleby.com/isbn_cover_images/9780078022159/9780078022159_smallCoverImage.jpg)
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)](https://www.bartleby.com/isbn_cover_images/9780134444321/9780134444321_smallCoverImage.gif)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
![Digital Fundamentals (11th Edition)](https://www.bartleby.com/isbn_cover_images/9780132737968/9780132737968_smallCoverImage.gif)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
![C How to Program (8th Edition)](https://www.bartleby.com/isbn_cover_images/9780133976892/9780133976892_smallCoverImage.gif)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
![Database Systems: Design, Implementation, & Manag…](https://www.bartleby.com/isbn_cover_images/9781337627900/9781337627900_smallCoverImage.gif)
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
![Programmable Logic Controllers](https://www.bartleby.com/isbn_cover_images/9780073373843/9780073373843_smallCoverImage.gif)
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education