Consultant ConsitNum LastName FirstName Street City State ZipCode Hours Rate Christopher 554 Brown Dr. Tri City FL 2287 Port Rd. 19 Turner 32889 40 $22.50 Jordan Allen 22 Patrick Easton FL 33998 40 $22.50 35 Sarah 82 Elliott St. Lizton FL 34344 35 $20.00 51 Shields Tom 373 Lincoln Ln. Sunland FL 39876 10 $15.00 Client ClientNum ClientName Street City State ZipCode Balance CreditLimit ConsltNum 143 Hershey, Jarrod 135 E. Mill Street Easton FL 33998 $1,904.55 $2,500.00 19 175 Goduto, Sean 12 Saratoga Parkway |Tri City FL $2,814.55 32889 $5,000.00 19 299 Two Crafty 9787 NCR 350 West Sunland FL 39876 $8,354.00 $10,000.00 22 Cousins 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 405 Fisherman's 49 Elwood Ave. Harpersburg FL 31234 $4,113.40 $7,500.00 19 Spot Shop 449 Seymour, 4091 Brentwood Ln Amo FL 34466 $557.70 $5,000.00 22 Lindsey Bonnie's Tri City FL 458 9565 Ridge Rd. 32889 $4,053.80 $7,500.00 22 Beautiful Boutique 677 Yates, Nick FL 231 Day Rd. Howler, Laura 1368 E. 1000 S. Sunland 39876 $2,523.80 $2,500.00 35 733 Lizton FL 34344 $3,658.05 $5,000.00 22 826 Harpersburg 65 Forrest Blvd. Harpersburg FL 31234 $6,824.55 $10,000.00 19 Bank 867 MarketPoint 826 Host St. Easton FL 33998 $3,089.00 $5,000.00 19 Sales Tasks OrderLine TaskID Description Category Price OrderNum TaskID ScheduledDate QuotedPrice AC65 Accessories ACC $80.00 67101 S177 9/10/2018 $144.00 DA11 Data recovery major DRM $175.00 67313 LA81 9/12/2018 $104.00 DI85 Data recovery minor DRM $50.00 67424 MO49 9/14/2018 $65.00 НА63 67424 UP38 9/14/2018 $185.00 Hardware major Hardware minor НАМ $225.00 НI31 НАМ $165.70 67838 LAS1 9/20/2018 $104.00 LA81 Local area networking (LAN) LAN $104 00 67949 PI54 9/21/2018 $50.00 MO49 Mobility Мов $65.00 67949 VR39 9/21/2018 $88.00 OT99 Other work ΟΓΗ $99.99 67949 WA33 9/21/2018 $126.00 PI54 Printing issues 68252 DI85 9/24/2018 $50.00 PRI $50.00 SA44 Software major SOM $200.00 68868 SA44 9/24/2018 $200.00 S177 Software minor SOM $144.00 68979 AC65 9/27/2018 $77.00 68979 DA11 9/27/2018 $970.00 S191 Security install/repair SIR $126.00 UP38 Upgrades UPG $185.00 WorkOrders VR39 Virus removal VIR $90.00 WA33 Wide area networking (WAN) WAN $130.00 OrderNum OrderDate ClientNum WC19 Web connectivity WEC $75.00 67101 9/6/2018 733 67313 9/7/2018 458 67424 9/10/2018 322 67838 9/10/2018 867 67949 9/10/2018 322 68252 9/12/2018 363 68868 9/14/2018 867 68979 9/17/2018 826

Consultant ConsitNum LastName FirstName Street City State ZipCode Hours Rate Christopher 554 Brown Dr. Tri City FL 2287 Port Rd. 19 Turner 32889 40 $22.50 Jordan Allen 22 Patrick Easton FL 33998 40 $22.50 35 Sarah 82 Elliott St. Lizton FL 34344 35 $20.00 51 Shields Tom 373 Lincoln Ln. Sunland FL 39876 10 $15.00 Client ClientNum ClientName Street City State ZipCode Balance CreditLimit ConsltNum 143 Hershey, Jarrod 135 E. Mill Street Easton FL 33998 $1,904.55 $2,500.00 19 175 Goduto, Sean 12 Saratoga Parkway |Tri City FL $2,814.55 32889 $5,000.00 19 299 Two Crafty 9787 NCR 350 West Sunland FL 39876 $8,354.00 $10,000.00 22 Cousins 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 405 Fisherman's 49 Elwood Ave. Harpersburg FL 31234 $4,113.40 $7,500.00 19 Spot Shop 449 Seymour, 4091 Brentwood Ln Amo FL 34466 $557.70 $5,000.00 22 Lindsey Bonnie's Tri City FL 458 9565 Ridge Rd. 32889 $4,053.80 $7,500.00 22 Beautiful Boutique 677 Yates, Nick FL 231 Day Rd. Howler, Laura 1368 E. 1000 S. Sunland 39876 $2,523.80 $2,500.00 35 733 Lizton FL 34344 $3,658.05 $5,000.00 22 826 Harpersburg 65 Forrest Blvd. Harpersburg FL 31234 $6,824.55 $10,000.00 19 Bank 867 MarketPoint 826 Host St. Easton FL 33998 $3,089.00 $5,000.00 19 Sales Tasks OrderLine TaskID Description Category Price OrderNum TaskID ScheduledDate QuotedPrice AC65 Accessories ACC $80.00 67101 S177 9/10/2018 $144.00 DA11 Data recovery major DRM $175.00 67313 LA81 9/12/2018 $104.00 DI85 Data recovery minor DRM $50.00 67424 MO49 9/14/2018 $65.00 НА63 67424 UP38 9/14/2018 $185.00 Hardware major Hardware minor НАМ $225.00 НI31 НАМ $165.70 67838 LAS1 9/20/2018 $104.00 LA81 Local area networking (LAN) LAN $104 00 67949 PI54 9/21/2018 $50.00 MO49 Mobility Мов $65.00 67949 VR39 9/21/2018 $88.00 OT99 Other work ΟΓΗ $99.99 67949 WA33 9/21/2018 $126.00 PI54 Printing issues 68252 DI85 9/24/2018 $50.00 PRI $50.00 SA44 Software major SOM $200.00 68868 SA44 9/24/2018 $200.00 S177 Software minor SOM $144.00 68979 AC65 9/27/2018 $77.00 68979 DA11 9/27/2018 $970.00 S191 Security install/repair SIR $126.00 UP38 Upgrades UPG $185.00 WorkOrders VR39 Virus removal VIR $90.00 WA33 Wide area networking (WAN) WAN $130.00 OrderNum OrderDate ClientNum WC19 Web connectivity WEC $75.00 67101 9/6/2018 733 67313 9/7/2018 458 67424 9/10/2018 322 67838 9/10/2018 867 67949 9/10/2018 322 68252 9/12/2018 363 68868 9/14/2018 867 68979 9/17/2018 826

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

Related questions

Concept explainers

Question

1. Using your knowledge of BITS, determine the functional dependencies that exist in the following table. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form:

Tasks (TaskID, Description, Category, Price, (OrderNum, OrderDate, ClientNum, ClientName, ConsltNum, LastName, FirstName, ScheduledDate, QuotedPrice))

2. List the functional dependencies in the following table that concern invoicing (an application BITS is considering adding to its database), subject to the specified conditions. For a given invoice (identified by the InvoiceNum), there will be a single client. The client’s number, name, and complete address appear on the invoice, as does the date. Also, there may be several different tasks appearing on the invoice. For each task that appears display the TaskID, description, category, and price. Assume that each client that requests a particular service task pays the same price. Convert this table to an equivalent collection of tables that are in third normal form:

Invoice (InvoiceNum, ClientNum, LastName, FirstName, Street, City, State, ZipCode, Date, (TaskID, Description, Category, Price))

3. BITS wants to store information about the supervisors, including their supervisor number and the relationship to consultants. Supervisors can work with multiple consultants, but consultants only have one supervisor. In addition, supervisors specialize in working with clients in specific task categories. Using this information, convert the following unnormalized relation to fourth normal form:

Consultant (ConsltNum, LastName, FirstName, Street, City, ZipCode, Hours, Rate, (SupervisorNum, SupervisorName), (Tasks, Description, Category, Price, SupervisorNum))

4. BITS is considering changing its business model so that many consultants can service one client and one consultant can represent many clients. Using this information, convert the following unnormalized relation to fourth normal form:

Client (ClientNum, ClientName, Street, City, State, ZipCode, Balance, CreditLimit, (ConsltNum, LastName, FirstName, Street, City, State, ZipCode, Hours, Rate))

5. Convert the following unnormalized relation to fourth normal form using the same requirements as in Question 4 (many consultants can represent one client, and one consultant can represent many clients):

Consultant (ConsltNum, LastName, FirstName, Street, City, State, ZipCode, Rate, (ClientNum, ClientName, Street, City, State, ZipCode, Balance, CreditLimit))

6. Is there any difference between the tables you created in Questions 4 and 5? Why or why not?

Consultant
ConsitNum
LastName FirstName Street
City
State ZipCode
Hours Rate
Christopher 554 Brown Dr. Tri City FL
2287 Port Rd.
19
Turner
32889
40
$22.50
Jordan
Allen
22
Patrick
Easton FL
33998
40
$22.50
35
Sarah
82 Elliott St.
Lizton FL
34344
35
$20.00
51
Shields
Tom
373 Lincoln Ln. Sunland FL
39876
10
$15.00
Client
ClientNum ClientName
Street
City
State ZipCode
Balance
CreditLimit ConsltNum
143
Hershey, Jarrod 135 E. Mill Street
Easton
FL
33998
$1,904.55
$2,500.00 19
175
Goduto, Sean 12 Saratoga Parkway
|Tri City
FL
$2,814.55
32889
$5,000.00 19
299
Two Crafty
9787 NCR 350 West
Sunland
FL
39876
$8,354.00 $10,000.00 22
Cousins
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
405
Fisherman's
49 Elwood Ave.
Harpersburg FL
31234
$4,113.40 $7,500.00 19
Spot Shop
449
Seymour,
4091 Brentwood Ln
Amo
FL
34466
$557.70
$5,000.00 22
Lindsey
Bonnie's
Tri City
FL
458
9565 Ridge Rd.
32889
$4,053.80 $7,500.00 22
Beautiful
Boutique
677
Yates, Nick
FL
231 Day Rd.
Howler, Laura 1368 E. 1000 S.
Sunland
39876
$2,523.80
$2,500.00 35
733
Lizton
FL
34344
$3,658.05
$5,000.00 22
826
Harpersburg
65 Forrest Blvd.
Harpersburg FL
31234
$6,824.55 $10,000.00 19
Bank
867
MarketPoint
826 Host St.
Easton
FL
33998
$3,089.00
$5,000.00 19
Sales

Tasks
OrderLine
TaskID
Description
Category
Price
OrderNum TaskID ScheduledDate QuotedPrice
AC65
Accessories
ACC
$80.00
67101
S177
9/10/2018
$144.00
DA11
Data recovery major
DRM
$175.00
67313
LA81
9/12/2018
$104.00
DI85
Data recovery minor
DRM
$50.00
67424
MO49
9/14/2018
$65.00
НА63
67424
UP38
9/14/2018
$185.00
Hardware major
Hardware minor
НАМ
$225.00
НI31
НАМ
$165.70
67838
LAS1
9/20/2018
$104.00
LA81
Local area networking (LAN)
LAN
$104 00
67949
PI54
9/21/2018
$50.00
MO49
Mobility
Мов
$65.00
67949
VR39
9/21/2018
$88.00
OT99
Other work
ΟΓΗ
$99.99
67949
WA33
9/21/2018
$126.00
PI54
Printing issues
68252
DI85
9/24/2018
$50.00
PRI
$50.00
SA44
Software major
SOM
$200.00
68868
SA44
9/24/2018
$200.00
S177
Software minor
SOM
$144.00
68979
AC65
9/27/2018
$77.00
68979
DA11
9/27/2018
$970.00
S191
Security install/repair
SIR
$126.00
UP38
Upgrades
UPG
$185.00
WorkOrders
VR39
Virus removal
VIR
$90.00
WA33
Wide area networking (WAN)
WAN
$130.00
OrderNum OrderDate
ClientNum
WC19
Web connectivity
WEC
$75.00
67101
9/6/2018
733
67313
9/7/2018
458
67424
9/10/2018
322
67838
9/10/2018
867
67949
9/10/2018
322
68252
9/12/2018
363
68868
9/14/2018
867
68979
9/17/2018
826

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 4 steps

SEE SOLUTION Check out a sample Q&A here

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.