| Lecture notes for the Introduction to Computers by James Tam |
Return to the course web page |
For this assignment you are to use the starting database at the following [link]. There are three main parts to this assignment: 1) Modifying the fields of the table (e.g., to verify the input to be entered or change type of information) as well specifying relationships between tables 2) Creating queries or specific questions that will show the parts of the database of interest 3) Creating a report to display the query results.
Part I: Creating the tables and specifying relationships (you only have to change the properties that are listed below) |
||||||
EMPLOYEES TABLE |
||||||
|
|
Field |
Data type |
Field size |
Input checking |
Required |
Indexed |
|
|
SIN (set as the primary key) |
Text (don't change) |
Change to 9 |
Create new input mask: 000<Space>000<Space>0001 |
Yes |
Yes (duplicates not okay) |
|
|
LastName |
Text (don't change) |
50 (don't change) |
None |
Yes |
No (don't change) |
|
|
FirstName |
Text (don't change) |
50 (don't change) |
None |
Yes |
No (don't change) |
|
|
Address |
Text (don't change) |
50 (don't change) |
None |
Yes |
No (don't change) |
|
|
City |
Text (don't change) |
50 (don't change) |
None |
Yes |
No (don't change) |
|
|
Province |
Text (don't change) |
50 (don't change) |
None |
Yes |
No (don't change) |
|
|
PostalCode |
Text (don't change) |
Change to 7 |
Create new input mask: <ch><num><ch>-<num><ch><num>1 |
Yes |
No (don't change) |
|
|
HomePhone |
Text (don't change) |
Change to 10 |
Use "phone number" input mask |
No (don't change) |
No (don't change) |
|
|
BirthDate |
Change to Date/Time (short date format) |
NA |
Create a validation rule: Date must be after Jan 1 1900 and before Oct 31 1989 (inclusive) |
No (don't change) |
No (don't change) |
|
|
PayRate |
Number (don't change) |
Long Integer (don't change) |
Create a validation rule: Hourly pay rate must be between $10 - $100 per hour (inclusive) |
Yes |
|
TIME BILLED TABLE |
||||||
|
|
Field |
Data type |
Field size |
Input checking |
Required |
Indexed |
|
|
TimeBilledID (set as the primary key) |
Number (don't change) |
Long Integer |
NA |
Yes |
Yes (duplicates not okay) |
|
|
EmployeeID (change to a foreign key that corresponds to the SIN field from the Employees table)2 |
Text (don't change) |
9 (don't change) |
No |
Yes |
No (no change) |
|
|
DepartmentID (change to a foreign key that corresponds to the DepartmentID field from the Departments table)2 |
Number (don't change) |
Long Integer (don't change) |
No |
Yes |
No (no change) |
|
|
StartPayPeriod |
Change to Date/Time (short format) |
NA |
No |
Yes |
No (no change) |
|
|
HoursWorked |
Number (don't change) |
Long Integer (don't change) |
Set the validation rule so that the hours worked must be greater than zero and less than eighty (inclusive) |
Yes |
No |
DEPARTMENTS TABLE |
||||||
|
|
Field |
Data type |
Field size |
Input checking |
Required |
Indexed |
|
|
DepartmentID (set as the primary key) |
Number (don't change) |
Long Integer |
NA |
Yes |
Yes (duplicates not okay) |
|
|
DepartmentName |
Text (don't change) |
50 (don't change) |
No |
Yes |
NA |
Footnote #1 Creating new input masks |
SIN |
Description: Social Insurance Number |
Input mask: 000<Space>000<Space>000 (e.g., 123 456 789) - the exact value that you enter may differ this is the result that you want to get |
Placeholder: _ |
Sample data: 999 999 999 |
Mask/type: Text/Unbound |
POSTAL CODE |
Description: Postal code |
Input mask: character number character - number character number (e.g., T3A-3H1) - the exact value that you enter may differ this is the result that you want to get |
Placeholder: _ |
Sample data: T3A-3H1 |
Mask/type: Text/Unbound |
Footnote #2 Editing the relationships |
|
Time Billed Table & Departments Table |
|
|
|
In the 'relationships' view, edit the relationship to 'Enforce referential integrity' |
Time Billed Table & Employees Table |
|
|
|
In the 'relationships' view, edit the relationship to 'Enforce referential integrity' |
Part II: Creating database queries |
||
FIRST QUERY, NAME: "EMPLOYEE PAY RATES" |
||
|
|
|
Fields of Table Employees |
|
|
|
|
|
|
SECOND QUERY, NAME: "GROSS PAY" |
|
|
|
|
Fields of Table Employees |
|
||
|
|
Fields of Table TimeBilled |
|
|
||
Fields of Table Departments
|
||
|
|
Add a calculated field to the query called "GrossPay" which is determined by multiplying the PayRate by the HoursWorked. The order in which the fields of the tables show up is important. Your query must order the fields as follows: SIN, LastName, FirstName, StartPayPeriod, DepartmentName, PayRate, HoursWorked, GrossPay |
|
THIRD QUERY, NAME: "EMPLOYEES WITH UNUSUAL PAY" |
||
|
|
|
Fields of Table Employees |
|
|
|
|
|
|
|
Fields of Table TimeBilled |
|
||
| Add a calculated field to the query called "GrossPay" which is determined by multiplying the PayRate. The order in which the fields of the tables show up is important. Your query must order the fields as follows: SIN, LastName, FirstName, StartPayPeriod, PayRate, HoursWorked, GrossPay. This query will only display employees with gross pay during that pay period that was less than $300 or greater than $3000 inclusive (this is the 'query criteria'). This query will just take the hours worked for a particular record in the TimeBilled table and multiply it by the PayRate in the Employees Table. It won't take the sum GrossPay for employees that have multiple entries in the TimeBilled table to determine cut offs. | ||
|
|
|
|
Create a report for each of the three queries above. For the first and third query each report should show all the fields that were included in the query. The report of the second query will include all fields from the query except for the DepartmentName. Make sure that each column heading is visible in the report and the data for a column is properly aligned with the appropriate heading. (You will be graded on the appearance and neatness of the report - refer to the principles of Contrast, Repetition, Alignment and Proximity for a guideline).
In addition to the general submission requirements, there are some additional requirements for this assignment: