Part 1 – Create tblJobs Table
Review the page on Entities & Attributes for more information on design.
Start Access, create a new blank database, and create a table named tblJobs
Add an Autonumber field to tblJobs to be used as the Primary Key (PK) uniquely identifying the jobs in your store giving the field an appropriate name.
Add the following fields.
Job Title
Hourly Pay – field must be able to contain fractional dollar amounts: example 10.75
Hours – field contains the weekly number of hours each job is scheduled, must be able to contain fractional hours typically between 20 to 40: example: 23.5
Add at least 2 more fields which are relevant to the job, give them appropriate field names. You should have a minimum of 6 fields in the table.
Set the correct data type / size for each field where the size is the set to the largest expected data attribute and type is set to match the data to be input. Example
Enter a description for each field to explain what it is used for. Example
Part 2 – Input tblJobs Data
Input 5 records into tblJobs using the following job titles; Owner, Manager, Clerk, Accountant, and Trainee.This tblJobs should only have 5 records since all employees with the same job have the same pay and work the same number of hours and so on. Example
Set the hourly pay rate for the Owner as $49.50 and the Trainees at $15.25
Set the number of hours the Owner works as 47.5, Trainees work less than 20 hours, set the values for the other employees to any reasonable value of 40 or less.
Set hourly pay rate for each of the remaining job titles using a different hourly pay rate which is less than the owner pay rate but greater than the Trainees rate.
Part 3 – Create tblEmployees Table
Create a table named tblEmployees
Add a field named EmployeeID as the Primary Key (PK) and set its type as AutoNumber.
Add the fields necessary to store the data listed below in the table.
The design must allow for sorting by these fields: first name, last name, city. You need to determine if 1 or more fields is required to hold each of the data elements listed and use an appropriate name and data type for those fields.
The following data elements must be included in your design:
employee name
address (including all fields necessary for a complete mailing address)
hire date – date hired at company
married – indicates employee is currently married
insurance – indicates if the employee either receives or does not receive health insurance
number of dependents – number of dependents under the age of 26
Set the correct data type and size for each field where the size is the set to the largest expected data attribute and not left at the default size.
Enter a description for each field to explain what it is used for.
Part 4 – Input tblEmployees Data
Input Aiham Asmroo as one of the 12 employees.
Input records for remaining 11 employees using reasonable data which you make up so you have a total of 12 employees. IMPORTANT: Your employees must have EmployeeID numbers from 1 to 12. View Example
Set the records for only 6 employees in the table to indicate that they have insurance.
Part 5 – Create Relationship Between Tables and Assign Employees to Jobs
Create a Foreign Key in one of the tables.
You have to decide which table based on how you are relating the tables.
Create a relationship between tblEmployees and tblJobs such that a job can be assigned to 1 or more employees.
Do not use the Job Title field to create the relationship! Not sure how to create a relationship? Watch these videos Relationships, Normalization or review the Relationships page.
Set the relationship to Enforce Referential Integrity and Cascade Update Related Fields
Assign the Owner job to yourself.
Assign jobs to the remaining 11 employees as follows:2 Managers, 1 Accountant, 6 Clerks, and 2 Trainees.
Save your file as Aiham.Asmroo_Exam1 Do not to use ANY spaces in the file name!
Part 6 – Create 2 Queries
Create a query displaying the following data using: first name and last name fields from tblEmployees and job title and hourly pay from tblJobs
Set the field order in the query from left to right as follows: last name, first name, job title, hourly pay
Sort the query design by hourly pay in descending order.
The query should only produce 12 records. View example
Save the query as qryEmployeePay.
Create a query displaying only employees who have insurance with the following field data in this order: last name, first name, job title, insurance, number of dependents, city.
Sort the query design by last name in ascending order.
Save the query as qryInsurance. View example
Part 7 – Create a Report
Create a report with the Report Wizard with qryInsurance as the report source using the defaults of the Report Wizard.
Change the title of the header caption from qryInsurance to Insurance Report
If necessary adjust the column headings so all column titles are completely visible.
Save the report with the name rptInsurance. View example
Part 8 – Finish and Submit
Set the database file option to compact on close.Not sure how to do this then watch the video on this page: Videos.
Clean up your database deleting any unused fields, tables, forms, and reports.
Upload your Aiham.Asmroo_Exam1 database file using the Exam 1 link.You will not be able to upload your file if the name does not match the name listed in this step. Resubmitted files must use the same file name!Warning: Files can only be accepted via the class drop box. If you email your file to me then you automatically earn 0 for this assignment.
Grading Rubric
Points
Requirements
10
Database follows correct database design principles
Correctly named tables & fields (spelling counts!)
PK & FK fields set, correct field types selected
tblEmployees EmployeeID field is an Autonumber field
tables have correct number of fields
Field sizes set to expected data element size
Field types set appropriately
Design allows for sorting by first name, last name, or city
Design has all fields for complete mailing address
No extra objects (Records, Fields, Tables, Forms, or Reports)
10
tblEmployees has 12 records, with EmployeeID numbers from 1 to 12
tblJobs has at least 6 fields with only 5 records
Jobs assigned to employees according to instructions
Hourly pay values as set according to the assignment instructions
6
Correct relationship exists between tables
Relationship does not use the Job Title
Enforced Referential Integrity and Cascade Update
8
Created qryEmployeePay with correct fields
Created qryInsurance with correct fields
Fields are listed in the correct order Query
design includes sorting according to instructions
4
Created rptInsurance with correct fields
2
Set database to Compact on Close
40
Total possible for uploading Aiham.Asmroo_Exam1
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount! Use Discount Code “Newclient” for a 15% Discount!NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.
The post exam 1 for cis110 class appeared first on Custom Nursing Help.
