CDBM Mod02 Answers
CDBM Mod02 Answers
CDBM Mod02 Answers
Solutions
REVIEW QUESTIONS
b. Each intersection of a row and column in a table may contain more than one value.
c. All values in a column are values of the same attribute (that is, all entries must match the column
name).
2. Which of the following sentences explains how entities, attributes, and records work together?
a. A record contains a single attribute with many entities.
3. The _____ contains values that uniquely identify each record in a table and serves as the linking
field in the table on the “one” (parent) side of a one-to-many relationship.
a. primary key field
c. calculated field
d. natural field
c. It is the field in the table on the “many” (child) side of a one-to-many relationship.
6. With _____, only one criterion must evaluate true in order for a record to be selected and with
_____, all criteria must be evaluate true in order for a record to be selected.
a. parameter criteria, double criteria
c. MonthlyRate: [AnnualRate] / 12
d. MonthlyRate: “AnnualRate” / 12
b. An aggregate function determines all rows that are in table A but not in table B.
c. An aggregate function sums, averages, or counts, for example, the records in a group.
d. An aggregate function makes a specified change to all records satisfying the criteria in a query.
b. Enter the sort order on the Criteria row of the query design grid.
c. Select a grouping field, and then use the Sort row to select a sort order.
d. In the Sort row, select “major sort key” or “minor sort key” for each field you want to sort.
b. You may not create orphan records by entering a phony foreign key field value in the “many” table.
c. You may not delete records in the “one” table that have no related records in the “many” table.
11. Which of the following types of queries does not change data in Access?
a. update
b. delete
c. select
d. make-table
12. _____ forms the foundational knowledge for SQL, Structured Query Language, which is the most
popular way developers select, edit, and add data to a relational database.
a. Query algebra
b. Referential integrity
c. Entity diagramming
d. Relational algebra
Critical Thinking
1. Using the data for the JC Consulting database shown in Figure 2-1, identify the one-to-many
relationships as well as the primary key fields and foreign key fields for each of the five tables.
Clients (ClientID, ClientName, Street, Zip, Government)
2. Using the data for the JC Consulting database shown in Figure 2-1 and your answers from the
previous question, which tables would need to be involved if you wanted to create a query that
showed the EmployeeID and LastName fields from the Employees table as well as all TaskID and
Description fields from the TaskMasterList table that were related to that employee?
You would need the Employees, TaskMasterList, Projects, and ProjectListItem tables.
Note: The following answers indicate how to perform the specified task in Microsoft Access. The process for other database
management systems would be different, although it should be similar. Data and solution files are available at the Cengage
website. Data files consist of copies of the JC Consulting, Pitt Fitness, and Sports Physical Therapy databases that are usable
in Access 2010, Access 2013, Access 2016, and Access 2019, and script files to create the tables and data in these databases
in other systems, such as MySQL.
b. 17
c. 26
d. 4
Review1
LastName ProjectID ClientName
Novak 1 Tri-Lakes Realtors
Novak 3 Midstates Auto Auction
Novak 4 Bretz & Hanna Law Firm
Novak 8 Midstates Auto Auction
Novak 16 Harper State Bank
Novak 17 MarketPoint Sales
Novak 19 SecureCom Wireless
Review1
LastName ProjectID ClientName
Novak 25 Wu Electric
2. Select the ProjectID, ProjectStartDate, ClientName, and Government fields for all records where the
Government field value is True. Which of the following clients are included in the results?
a. Project Lead The Way and The HELPCard
Review2
ProjectID ProjectStartDate ClientName Government
7 9/2/2019 Project Lead The Way Yes
15 12/21/2020 The HELPCard Yes
3. Select the ProjectID, ProjectStartDate, ClientName, TaskID, and Description fields for TaskID
TEST01 and a ProjectStartDate after 1/1/2020. Sort the records in ascending order by
ProjectStartDate. What are the ProjectStartDate and ClientName values in the first record of the
results?
a. 1/6/2020, Midstates Auto Auction
d. 1/14/2022, SkyFactor
Review3
ProjectID ProjectStartDate ClientName TaskID Description
4 4/10/2020 Bretz & Hanna Law Firm TEST01 Test technology
13 11/30/2020 Jobot Developers TEST01 Test technology
14 12/9/2020 Jillian Henry & Associates TEST01 Test technology
15 12/21/2020 The HELPCard TEST01 Test technology
17 2/15/2021 MarketPoint Sales TEST01 Test technology
22 9/30/2021 Juxly Engineering TEST01 Test technology
24 1/14/2022 SkyFactor TEST01 Test technology
4. Select the ProjectID, ClientName, TaskID, and Description fields for TaskIDs TEST01 or TEST02.
Sort the records in ascending order by ProjectID and then by TaskID. What are the ProjectID and
TaskID values of the first two records in the results?
a. 2, TEST01 and 4, TEST01
Review4
ProjectID ClientName TaskID Description
2 Jobot Developers TEST01 Test technology
2 Jobot Developers TEST02 Test performance
4 Bretz & Hanna Law Firm TEST02 Test performance
4 Bretz & Hanna Law Firm TEST01 Test technology
7 Project Lead The Way TEST01 Test technology
7 Project Lead The Way TEST02 Test performance
13 Jobot Developers TEST01 Test technology
14 Jillian Henry & Associates TEST01 Test technology
14 Jillian Henry & Associates TEST02 Test performance
15 The HELPCard TEST02 Test performance
15 The HELPCard TEST01 Test technology
17 MarketPoint Sales TEST01 Test technology
17 MarketPoint Sales TEST02 Test performance
22 Juxly Engineering TEST02 Test performance
22 Juxly Engineering TEST01 Test technology
24 SkyFactor TEST02 Test performance
24 SkyFactor TEST01 Test technology
5. Select the EmployeeID, LastName, HireDate, and Salary fields for employees hired on or before
1/1/2020. Create a calculated field named Bonus that is calculated as 50 percent of their monthly
salary, which is stored in the Salary field. What is the bonus amount for Lopez?
a. 2500
b. 12,400
c. 6200
d. 3100
Review5
EmployeeID LastName HireDate Salary Bonus
19 Kohn 1/1/2020 $5,000.00 2500
52 Novak 1/1/2019 $8,000.00 4000
53 Anad 1/1/2019 $5,300.00 2650
Review5
EmployeeID LastName HireDate Salary Bonus
54 Allen 1/1/2019 $7,000.00 3500
56 Reddy 9/1/2019 $6,200.00 3100
58 Young 1/1/2019 $5,500.00 2750
59 Santana 1/1/2019 $4,800.00 2400
60 Lu 3/1/2019 $7,900.00 3950
61 Smirnov 10/1/2019 $6,000.00 3000
63 Geller 1/1/2019 $8,100.00 4050
64 Lopez 1/1/2019 $6,200.00 3100
68 Patel 4/1/2019 $6,500.00 3250
73 Safar 2/1/2019 $4,100.00 2050
6. Find the total, average, and count of the Salary field for all employees grouped by title. Sort the
records in ascending order by title. What is the average of the salaries for the first title in the results?
a. $6,650.00
b. $6,000.00
c. $6,375.00
d. $4,366.67
Review6
Title SumOfSalary AvgOfSalary CountOfSalary
Customer Support Specialist $13,100.00 $4,366.67 3
Database Developer $15,700.00 $7,850.00 2
Front End Developer $24,600.00 $4,920.00 5
Network Specialist $14,000.00 $7,000.00 2
Programmer $48,400.00 $6,050.00 8
Project Manager $21,100.00 $7,033.33 3
Quality Assurance Engineer $12,750.00 $6,375.00 2
UI Designer $13,300.00 $6,650.00 2
7. Select the records in the TaskMasterList table with a CategoryID field value of Database, and then
create an update query to update their Estimate field values by 10 percent. How many records are
updated, and what is the highest updated Estimate field value?
a. 5 and $1,100
b. 5 and $1,000
c. 40 and $1,100
d. 40 and $1,000
After running the update query, 5 records are updated as shown below:
Review7
CategoryID Estimate
Database $1,100.00
Database $137.50
Database $550.00
Database $550.00
Database $440.00
8. Delete all of the records in the ProjectLineItems table with a ProjectID field value of 11. How many
records were deleted?
a. 1
b. 0
c. 7
d. 145
These 7 records should be deleted from the ProjectLineItems table:
Review8
ProjectLineItemID ProjectID TaskID TaskDate Quantity Factor ProjectLineItemNotes
17 11 MEET00 30-Apr-20 1 1.00
18 11 PLAN01 30-Apr-20 1 1.00
201 11 DB04 13-May-20 1 1.00
202 11 CODE04 20-May-20 40 1.00
203 11 SUPP03 27-May-20 8 1.00
204 11 SUPP02 01-Jun-20 4 1.00
205 11 SUPP04 08-Jun-20 12 1.00 12-month minimum
9. Make a table with the ClientName, ProjectID, ProjectStartDate, and TaskID fields for all projects
with a TaskID field value of MEET00. Sort the records in ascending order by the ProjectStartDate
field. Twenty-four records should be selected. Name the table InitialMeetings. What is the
ClientName value of the first record in the InitialMeetings table?
a. Bounteous
b. Tri-Lakes Realtors
c. Morales Group
d. Jobot Developers
These 24 records should be present in a new InitialMeetings table:
Review9
ClientName ProjectID ProjectStartDate TaskID
Tri-Lakes Realtors 1 2/6/2019 MEET00
Jobot Developers 2 2/7/2019 MEET00
Midstates Auto Auction 3 3/11/2019 MEET00
Project Lead The Way 7 9/2/2019 MEET00
Midstates Auto Auction 8 1/6/2020 MEET00
Bounteous 9 2/10/2020 MEET00
Tri-Lakes Realtors 31 3/1/2020 MEET00
Bretz & Hanna Law Firm 4 4/10/2020 MEET00
Jobot Developers 13 11/30/2020 MEET00
Jillian Henry & Associates 14 12/9/2020 MEET00
The HELPCard 15 12/21/2020 MEET00
Harper State Bank 16 1/4/2021 MEET00
MarketPoint Sales 17 2/15/2021 MEET00
Bounteous 18 4/14/2021 MEET00
SecureCom Wireless 19 6/4/2021 MEET00
Pediatric Group 21 8/31/2021 MEET00
Juxly Engineering 22 9/30/2021 MEET00
NuCamp 23 11/12/2021 MEET00
SkyFactor 24 1/14/2022 MEET00
Wu Electric 25 2/17/2022 MEET00
Revature Motors 27 5/3/2022 MEET00
Talent Sensations 28 5/10/2022 MEET00
Carta Training 29 6/2/2022 MEET00
Morales Group 30 6/3/2022 MEET00
Critical Thinking
1. Select the ClientName, ProjectStartDate, and TaskID fields for all records that have a project
start date in the year 2020 and have a task ID of MEET00 or MEET01. Sort the records by client
This question requires students to use both AND and OR criteria in one query. The ProjectStartDate
must be >1/1/2020 and < 1/1/2021 for TaskID MEET00 or MEET01.
Review10
ClientName ProjectStartDate TaskID
Bounteous 2/10/2020 MEET00
Review10
ClientName ProjectStartDate TaskID
Bretz & Hanna Law Firm 4/10/2020 MEET00
Jillian Henry & Associates 12/9/2020 MEET00
Jillian Henry & Associates 12/9/2020 MEET01
Jobot Developers 11/30/2020 MEET00
Midstates Auto Auction 1/6/2020 MEET00
The HELPCard 12/21/2020 MEET00
The HELPCard 12/21/2020 MEET01
Tri-Lakes Realtors 3/1/2020 MEET00
Tri-Lakes Realtors 3/1/2020 MEET01
Tri-Lakes Realtors 3/1/2020 MEET01
2. An employee of JC Consulting created the query shown in Figure 2-48. He wants to list the
client name, project ID, and task description for each task assigned to the projects for that client.
Will this query be successful? If not, what needs to change in order for this query to work
correctly?
This query will not result in the correct answer because the relationship between the TaskMasterList
table and the other two tables in the query is not established. When relationships are not clear, they
result in a Cartesian join in which every related record from Clients and Projects will join with every
record in the TaskMasterList table. To fix this problem, the ProjectLineItems table needs to be added to
Query Design View to clarify how the records in the TaskMasterList table relate to the rest of the
relational database.
Answers to JC Consulting Case Exercises: Relational Algebra
In the following exercises, you will use the data in the JC Consulting database shown in Figure 2-1. In
each step, indicate how to use relational algebra to obtain the desired results.
1. Complete the following statement to list the employee ID, first name, and last name of all
employees.
a. PROJECT
b. JOIN
c. INTERSECTION
d. SELECT
2. Complete the following statement to list all information from the TaskMasterList table for task ID
CODE05.
a. TaskID = CODE05
b. TaskID = 'CODE05'
c. CODE05 = 'TaskID'
d. (TaskID, CODE05)
3. Complete the following statements to list the project ID, project start date, client ID, and client name
Answer
a. ClientID.Projects = ClientID.Clients
b. Projects.ProjectID = Clients.ProjectID
c. Projects.ClientID = Clients.ClientID
d. Projects.ProjectID = Clients.ClientID
4. Complete the following statements to list the project ID, project start date, client ID, and client name
for each project created for the employee with the last name of Winter.
Answer
a. SELECT
b. PROJECT
c. UNION
d. JOIN
5. Complete the following statements to list the project ID and project start date of all projects that
a. WHERE ClientID = 5
b. OVER ClientID = 5
d. AND ClientID = 5
6. Complete the following statements to list the project ID and project start date of all projects that
a. JOIN
b. UNION
c. OR
d. INTERSECTION
7. Complete the following statements to list the project ID and project start date of all projects with a
a. UNION
b. PRODUCT
c. SUBTRACT
d. DELETE
The owner of Pitt Fitness knows that the power of the company’s database is in running queries to find
out important information for making good business decisions. In the following exercises, you use the
data in the Pitt Fitness database shown in Figures 1-15 through 1-19 in Module 1. When you use
Microsoft Access to respond to these exercises, make a copy of the original database to create the
a. Gregor
b. Aboud
c. Agnew
d. Sanchez
a. 5
b. 3
c. 10
d. 2
c. Yoga
a. 10
b. 7
c. 6
d. 5
a. 5
b. 2
c. 1
d. 0
a. 0
b. 1
c. 3
d. 10
a. Raymond Stein
b. Memo Said
c. Michael Nguyen
d. Maria D’Angelo
a. Neda Tahan
b. Juan Varlano
c. Megan Kobinski
d. Robert Sisto
12. Of all reservations for classes containing the word Cycle, which customer does not owe another fee
besides the class price? (Note: this field is called “OtherFees.”) Create a calculated field to total both
costs.
a. Gene Shaffer
b. Philip Benavides
c. Tony Waldron
d. Juan Barry
13. According to the reservations so far, how much money will the Combination classes generate?
a. $9
b. $18
c. $36
d. $0
14. Instructor Michael Nguyen is injured and has to cancel his class on Wednesday. Delete that record
b. 2
c. 5
d. 10
15. Which instructor will customer Margo Patterson get for her HIIT class?
a. Raymond Stein
b. Vicki Pegues
c. Megan Kobinski
d. Neda Tahan
16. How many classes are offered on Tuesday at 6 am and last 45 minutes?
a. 3
b. 2
c. 1
d. 0
a. Monday
b. Tuesday
c. Wednesday
d. Thursday
Critical Thinking
1. Suppose you want to list information on all the classes that Pitt Fitness offers, including the day of
the week, time, location, and length of class. To do this, you could create a query. What table(s)
should you add to the query? If you use only the Classes table instead of all the tables together, does
it make a difference to the output? What if someone had never reserved a specific class?
Answer: To answer the question correctly, you would need only the Classes table. If you used all the
tables in the query and some classes had not been reserved, you might be missing some of that
output.
2. The owner of Pitt Fitness is considering whether to consolidate his clubs and offer classes in only
one location to ensure profitability. To explore his question, what query or queries would you create
Answer: First, create a query that counts the number of classes offered at each location. Next, create
a query that counts the number of reservations at each location. Finally, to refine the answer, you
could create a query that counts the number of reservations for each specific class at the low-
In the following exercises, you use data in the Sports Physical Therapy database shown in Figures 1-21
through 1-24 in Module 1. When you use Microsoft Access to answer these questions, make a copy of
the original database to create the queries to answer these questions. In each step, use QBE to obtain the
desired results.
a. Latisha Culling
b. Andre Marino
c. Tammy Wilson
d. Isaiah Venable
b. Three therapists
c. Two patients
d. No one lives in Georgetown, TX.
a. Robbie Koehler
b. Tobey Short
c. Ben Odepaul
d. Tierra Falls
a. Saritha Nair
b. Anthony Shields
c. Bridgette McClain
d. Jonathan Risk
a. Brianna Waggoner
b. Tammy Wilson
c. Andre Marino
d. Joseph Baptist
a. Wilder, Shields
b. Risk, Nair
c. McClain, Shields
d. Wilder, Nair
a. Culling, Marino
b. Venable, Koehler
c. King, Falls
d. Baptist, Short
8. How many patients are receiving ultrasound therapy?
a. 1
b. 2
c. 3
d. 0
a. Bridgette McClain
b. Saritha Nair
c. Steven Wilder
d. Anthony Shields
10. Create a calculated field that figures the amount each therapist earns assuming they charge $100 per
hour and the length of the session is the correct amount of time each therapy takes. How much
a. $100
b. $150
c. $300
d. $225
a. Jonathan Risk
b. Anthony Shields
c. Saritha Nair
d. Steven Wilder
a. 3
b. 1
c. 4
d. 0
a. 5
b. 10
c. 15
d. 30
a. Latisha Culling
b. Brianna Waggoner
c. Joseph Baptist
d. Robbie Koehler
a. McClain, Shields
b. Risk, Shields
c. Nair, Shields
d. Wilder, Shields
a. Jonathan Risk
b. Steven Wilder
c. Bridgette McClain
d. Anthony Shields
17. A surcharge is being placed on any patient whose balance is greater than $1,000. That surcharge is 5
percent addition to a patient’s current balance. Create an update query to perform the calculation.
b. $2,053.17
c. $2,116.07
d. $2,087.93
Critical Thinking
1. If you want to find out which therapist is meeting a particular patient on a specific day, what tables
do you need for your query? If you also want to know the description of the therapy, does that
Answer: The first question needs the Patient, Session, and Therapist tables. The second question
2. If you made a mistake on the update query in question 17 to add a 5 percent surcharge to accounts
with balances greater than $1,000, how would you undo the change?
Answer: Modify the update query to divide by 1.05 and run the query.