Problem: JMS TechWizards is a local company that provides technical services to several small businesses in the area. The company currently keeps its technicians and clients’ records on papers. The manager requests you to create a database to store the technician and clients’ information.The following table contains the clients’ information.Client NumberClient NameStreetCityStatePostal CodeTelephone NumberBilledPaidTechnician NumberAM53Ashton-Mills216 RivardAndersonTX78077512-555-4070$315.50$255.0022AR76The Artshop722 FisherLiberty CornerTX78080254-555-0200$535.00$565.0023BE29Bert's Supply5752 MaumeeLiberty CornerTX78080254-555-2024$229.50$0.0023DE76D & E Grocery464 LinnellAndersonTX78077512-555-6050$485.70$400.0029GR56Grant Cleaners737 AllardKingstonTX78084512-555-1231$215.00$225.0022GU21Grand Union247 FullerKingstonTX78084512-555-5431$228.00$0.0023JE77Jones Electric57 GiddingsAndersonTX78077512-555-6895$0.00$0.0023ME17Merry Cafe665 WhittierKingstonTX78084512-555-9780$312.60$323.5022SA56Sawyer Ind.31 LafayetteAndersonTX78077512-555-4567$372.25$350.0029GU21Grand Union247 FullerKingstonTX78084512-555-5431$228.00$0.0023JQ87Jones Electric57 GiddingsAndersonTX78077512-555-6895$0.00$0.0023MY76Tops Coffee365 WhittierKingstonTX78084512-555-9780$312.60$323.5022ST45InNetwork318 LafayetteAndersonTX78077512-555-4567$372.25$350.0029SU54Clear Wire256 CadieuxLiberty CornerTX78080254-555-9080$0.00$0.0023TU19Seed’s790 CadieuxLiberty CornerTX78080254-555-9080$0.00$0.0023Table-1 The following table contains the technicians’ information.Technician NumberLast NameFirst NameStreetCityStatePostal CodeHourly RateYTD Earnings22LevinJoe26 CottonAndersonTX78077$25.00$8,245.0023RogersBrad79 MarsdenLiberty CornerTX78080$30.00$9,143.3029RodriguezMaria263 TopperKingstonTX78084$35.00$9,745.5032TorresLee34 Red PoppyLiberty CornerTX78080$23.00$0.0034RossMay26 MainAndersonTX78077$30.00$6,745.5037TorresShin134 RosewoodLiberty CornerTX78080$29.00$4675.00Table-2Instruction Part1:Create a blank database with the file (database) named JMSTechWizardIn this database, create a table in which to store the clients’ information. Use the name Client for the table. The Client table has 10 fields: Client Number, Client Name, Street, City, State, Postal Code, Telephone Number, Billed, Paid, and Technician Number. Client Number is the primary key and is text data type. The Billed and Paid fields are currency data type. The Technician Number field is number data type. All the other fields are text data type.Add the records in the Table-1 to the Client table.In the same database, create another table in which to store the technicians’ information. Use the name Technician for the table. The Technician table has 9 fields: Technician Number, Last Name, First Name, Street, City, State, Postal Code, Hourly Rate, and YTD Earnings. Technician Number is the primary key and is number data type. The Hourly Rate and YTD Earnings are currency data type. All the other fields are text data type.Add the records in the Table-2 to the Technician table. Instruction Part 2:Create a relationship between the common field (Technician Number) of the two tables. Make sure that each client must have 1 and only 1 technician assigned, and each technician can have multiple clients.Create a query to show the Client Number, Client Name, Billed, Paid for clients in Anderson city. Save the query.Create a query to show the Technician Number, Last Name, First Name, YTD Earnings for technicians whose Hourly Rate is greater than or equal to 30. Save the query.Create a query to show Client Number, Client Name, Billed, Paid for clients whose technician number is 22 and whose Billed is over 300. Save the query.Create a query to show the Technician Number, Last Name, First Name, Client Number, Client Name for clients whose technician number 23. Save the query.Create a query to show the Technician Number, Last Name, First Name, Client Number, Client Name for clients whose technician number 23 or 29. Save the query Need answer for instruction part 2

1. SELECT *FROM CLIENT cINNER JOIN TECHNICIAN tON c.TechnicianNumber = t.TechnicianNumber2. SELECT…

Answered: Instruction Part 2: Create a…

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

Question

Problem: JMS TechWizards is a local company that provides technical services to several small businesses in the area. The company currently keeps its technicians and clients’ records on papers. The manager requests you to create a database to store the technician and clients’ information.

The following table contains the clients’ information.

Client Number	Client Name	Street	City	State	Postal Code	Telephone Number	Billed	Paid	Technician Number
AM53	Ashton-Mills	216 Rivard	Anderson	TX	78077	512-555-4070	$315.50	$255.00	22
AR76	The Artshop	722 Fisher	Liberty Corner	TX	78080	254-555-0200	$535.00	$565.00	23
BE29	Bert's Supply	5752 Maumee	Liberty Corner	TX	78080	254-555-2024	$229.50	$0.00	23
DE76	D & E Grocery	464 Linnell	Anderson	TX	78077	512-555-6050	$485.70	$400.00	29
GR56	Grant Cleaners	737 Allard	Kingston	TX	78084	512-555-1231	$215.00	$225.00	22
GU21	Grand Union	247 Fuller	Kingston	TX	78084	512-555-5431	$228.00	$0.00	23
JE77	Jones Electric	57 Giddings	Anderson	TX	78077	512-555-6895	$0.00	$0.00	23
ME17	Merry Cafe	665 Whittier	Kingston	TX	78084	512-555-9780	$312.60	$323.50	22
SA56	Sawyer Ind.	31 Lafayette	Anderson	TX	78077	512-555-4567	$372.25	$350.00	29
GU21	Grand Union	247 Fuller	Kingston	TX	78084	512-555-5431	$228.00	$0.00	23
JQ87	Jones Electric	57 Giddings	Anderson	TX	78077	512-555-6895	$0.00	$0.00	23
MY76	Tops Coffee	365 Whittier	Kingston	TX	78084	512-555-9780	$312.60	$323.50	22
ST45	InNetwork	318 Lafayette	Anderson	TX	78077	512-555-4567	$372.25	$350.00	29
SU54	Clear Wire	256 Cadieux	Liberty Corner	TX	78080	254-555-9080	$0.00	$0.00	23
TU19	Seed’s	790 Cadieux	Liberty Corner	TX	78080	254-555-9080	$0.00	$0.00	23

Table-1

The following table contains the technicians’ information.

Technician Number	Last Name	First Name	Street	City	State	Postal Code	Hourly Rate	YTD Earnings
22	Levin	Joe	26 Cotton	Anderson	TX	78077	$25.00	$8,245.00
23	Rogers	Brad	79 Marsden	Liberty Corner	TX	78080	$30.00	$9,143.30
29	Rodriguez	Maria	263 Topper	Kingston	TX	78084	$35.00	$9,745.50
32	Torres	Lee	34 Red Poppy	Liberty Corner	TX	78080	$23.00	$0.00
34	Ross	May	26 Main	Anderson	TX	78077	$30.00	$6,745.50
37	Torres	Shin	134 Rosewood	Liberty Corner	TX	78080	$29.00	$4675.00

Table-2

Instruction Part1:

Create a blank database with the file (database) named JMSTechWizard
In this database, create a table in which to store the clients’ information. Use the name Client for the table. The Client table has 10 fields: Client Number, Client Name, Street, City, State, Postal Code, Telephone Number, Billed, Paid, and Technician Number. Client Number is the primary key and is text data type. The Billed and Paid fields are currency data type. The Technician Number field is number data type. All the other fields are text data type.
Add the records in the Table-1 to the Client table.
In the same database, create another table in which to store the technicians’ information. Use the name Technician for the table. The Technician table has 9 fields: Technician Number, Last Name, First Name, Street, City, State, Postal Code, Hourly Rate, and YTD Earnings. Technician Number is the primary key and is number data type. The Hourly Rate and YTD Earnings are currency data type. All the other fields are text data type.
Add the records in the Table-2 to the Technician table.

Instruction Part 2:

Create a relationship between the common field (Technician Number) of the two tables. Make sure that each client must have 1 and only 1 technician assigned, and each technician can have multiple clients.
Create a query to show the Client Number, Client Name, Billed, Paid for clients in Anderson city. Save the query.
Create a query to show the Technician Number, Last Name, First Name, YTD Earnings for technicians whose Hourly Rate is greater than or equal to 30. Save the query.
Create a query to show Client Number, Client Name, Billed, Paid for clients whose technician number is 22 and whose Billed is over 300. Save the query.
Create a query to show the Technician Number, Last Name, First Name, Client Number, Client Name for clients whose technician number 23. Save the query.
Create a query to show the Technician Number, Last Name, First Name, Client Number, Client Name for clients whose technician number 23 or 29. Save the query

Need answer for instruction part 2

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 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.