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: 1. Create a blank database with the file (database) named JMSTechWizard 2. 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. 3. Add the records in the Table-1 to the Client table. 4. 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. 5. Add the records in the Table-2 to the Technician table.

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: 1. Create a blank database with the file (database) named JMSTechWizard 2. 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. 3. Add the records in the Table-1 to the Client table. 4. 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. 5. Add the records in the Table-2 to the Technician table.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter7: Introduction To Structured Query Language (sql)

Section: Chapter Questions

Problem 97C: The following tables provide a very small portion of the data that will be kept in the database....

See similar textbooks

Related questions

Question

Access Assignment

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:

1. Create a blank database with the file (database) named JMSTechWizard

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

3. Add the records in the Table-1 to the Client table.

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

5. Add the records in the Table-2 to the Technician table.

Instruction Part 2:

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

2. Create a query to show the Client Number, Client Name, Billed, Paid for clients in Anderson city. Save the query.

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

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

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

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

Submission:

1. Where to submit: Canvas only. Attach your database file in the submission

2. Email or private message submission will not be accepted

3. Print copy is not accepted

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 9 images

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.