Chapter Review 2.3-3 Lone Star Database – Inner Joins

Timesheet

Employee

SSN / FName / LName / Address / City / State / Zip
123456789 / Johnny / Barefoot / 184 N. West St. / San Antonio / TX / 03492-
242242242 / Madeline / Taylor / 664 Main St. / Houston / TX / 28220-
345876282 / Andrew / Montebon / 12 Apollo Ave. / Houston /
TX
/ 28203-
444444444 / Bruce / Collins / 779 Gemini Dr. / Houston / TX / 28392-
987654321 / Kathy / Schwartz / 7382 Park Ln. / San Antonio / TX / 03493-
EmployeeID / Date / HrsWorked
000000000 / 2/1/01 / 7
123456789 / 1/29/01 / 8
123456789 / 1/30/01 / 9
123456789 / 2/2/01 / 8.5
123456789 / 2/3/01 / 10
242242242 / 1/30/01 / 8
242242242 / 2/1/01 / 12
242242242 / 2/2/01 / 9
345876282 / 1/30/01 / 4
345876282 / 2/1/01 / 7.5
444444444 / 1/29/01 / 6
444444444 / 1/30/01 / 5.5
444444444 / 2/2/01 / 8.25
987654321 / 1/29/01 / 10
987654321 / 1/30/01 / 8
987654321 / 1/31/01 / 8
987654321 / 2/1/01 / 9.5
987654321 / 2/2/01 / 9

Job

JobID / JobTitle / HourlyPay
1 / Carpenter / $13.00
2 / Painter / $9.00
3 / Architect / $25.00
4 / Plumber / $14.00
5 / Electrician / $13.50

Assignments

EmployeeID / Hire Date / JobCode
123456789 / 1/1/00 / 1
242242242 / 1/1/00 / 5
345876282 / 5/4/00 / 2
444444444 / 9/19/00 / 1
987654321 / 1/1/00 / 3

The tables above represent the database system for the Lone Star Construction Company. The Employee table lists the names and addresses of all the company’s employees. The employee’s SSN is used as employee identification to track employee information. The Job table lists the hourly wages for each job. The Assignments table lists the hire dates and job assignments for the employees – an employee can have only one job assignment. The Timesheet table lists the number of hours that each employee worked each day. Note that only partial tables are listed above. The database contains much more data.

  1. (2 points) Database Relationships. Set up the relationships of this database. Using the boxes below, fill in the primary key (if any) of each table and draw relationship lines between tables. Label each relationship with the name of the foreign key(s).
  1. Has referential data integrity been violated for any of these relationships (consider only the data shown)? Explain.

Yes, referential data has been violated because EmployeeID 000000000 is on the timesheet table, but does not appear on the Employee table. In other words, EmployeeID 000000000 has no matching primary key on the Employee table.

  1. Using the query design view below, construct a query to list the names of all the Houston residents in alphabetical order by last name, then first name.

Tables Used ___Employee______Join on ____None______

Field / LName / FName / City
Table / Employee / Employee / Employee
Total
Sort / Ascending / Ascending
Show / x / x
Criteria / “Houston”
OR
OR
  1. List the names and social security numbers of all employees hired in the first half of the year 2000 (from January 1 through June 30).

Tables Used ____Employee, Assignments______Join on ______SSN______

Field / FName / LName / SSN / Hire Date
Table / Employee / Employee / Employee / Assignments
Total
Sort
Show / x / x / x
Criteria / >= #1/1/00# and <= #6/30/00#
OR
OR

can also use criteria: between #1/1/00# and #6/30/00#

  1. Create a list of pay rates for all employees who live in Houston and earn more than $10 per hour. Include their last name, SSN, hire date, and hourly pay rate.

Tables Used ___Employee, Assignments, Job______Join on _SSN, JobCode______

Field / LName / SSN / Hire Date / HourlyPay / City
Table / Employee / Employee / Assignments / Job / Employee
Total
Sort
Show / x / x / x / x
Criteria / > 10 / “Houston”
OR
OR

could also use the EmployeeID off the Assignments table instead of SSN

  1. Create a job assignment list. The list should include all employees’ full names and their job titles.

Tables Used ___Employee, Assignments, Job____ Join on __SSN, JobCode______

Field / FName / LName / JobTitle
Table / Employee / Employee / Job
Total
Sort
Show / x / x / x
Criteria
OR
OR
  1. The carpenters’ union has been complaining that you have been working them too many hours each day. Calculate the maximum, minimum, and average number of hours that the carpenters spend working each day.

Tables Used __Timesheet, Assignments, Job______Join on __EmployeeID, JobCode_____

Field / HrsWorked / HrsWorked / HrsWorked / JobTitle
Table / Timesheet / Timesheet / Timesheet / Job
Total / Min / Max / Avg / Where
Sort
Show / x / x / x
Criteria / “Carpenter”
OR
OR
  1. (2 points) Create a pay sheet for the week of January 29, 2001, through February 4, 2001. For each employee, list his or her Employee ID, the employee’s wage rate, the number of days worked that week, the total number of hours worked, and the total amount to be paid on that week’s paycheck.

Tables Used __Timesheet, Assignments, Job______Join on __EmployeeID, JobID_

Field / EmployeeID / Date / HrsWorked / AmtPaid: / Date
Table / Timesheet / Timesheet / Timesheet / Timesheet
Total / Group by / Count / Sum / Expression / Where
Sort
Show / x / x / x / x / x
Criteria / >= #1/29/01# and <= #2/4/01#
OR
OR

Additional room for calculations if needed:

AmtPaid: [SumOfHrsWorked]*[Job]![HourlyPay]

**could also use criteria: between #1/29/01# and #2/4/01#

1