6
Task 3 – Database Report
You are now going to prepare some reports for the company. Make sure all currency values are
displayed with no decimal places.
17 • Using a suitable database package, import the file M16EMPLOYEES.CSV
• Use these field names and data types:
Field Name Data Type Description
Employee_ID Text This is a unique identification for each employee
Family_Name Text
First_Name Text
Job_Description Text
Salary Numeric/Currency Amount paid per year
Office_ID Text Three letter code to represent the office
Contractor Boolean/Logical Display in the report as Yes/No or checkbox
• Set the Employee_ID field as a primary key. [5]
18 • Import the file M16OFFICES.CSV as a new table with all fields set as text data types in your
database.
• Set the Office_ID field as a primary key. [2]
EVIDENCE 4
Take screenshot evidence showing the field names and data types used in the two tables.
Place these screenshots into your Evidence Document.
19 • Create a one-to-many relationship as a link between the Office_ID field in the Offices table
and the Office_ID field in the Employees table. [1]
EVIDENCE 5
Take screenshot evidence showing the relationship between the two tables. Place a copy of
this screenshot into your Evidence Document.
20 • Create a data entry form for all fields in the Employees table. [2]
© UCLES 2016 0417/21/F/M/16
7
21 Add the following record to the Employees table using your form:
Employee_ID Family_Name First_Name Job_Description Salary Office_ID Contractor
STRUCTURAL
AHM0221 KAPOOR SHASHI 4416280 AHM No
ENGINEER
Check your data entry for errors. [1]
EVIDENCE 6
Take screenshot evidence of the completed form for this record. Place a copy of this screenshot
into your Evidence Document.
A bonus is paid to employees but not contractors. It is an extra month’s pay.
22 • Produce a report which:
○ contains a new field called Bonus. It is the annual Salary of the employee divided by 12.
This is calculated at run-time
○ displays the contents of this field as currency
○ shows only the records where the Office is Ahmedabad and Contractor is No
○ shows only the fields Office (name in full), Telephone, Employee_ID, Family_Name,
First_Name, Job_Description, Contractor, Salary and Bonus in this order. Make sure all
labels and data are fully visible
○ fits on a single page wide
○ has a page orientation of landscape
○ sorts the data into ascending order of Family_Name
○ calculates the total cost of the bonuses paid in this office and places it at the bottom of
the report
○ displays this value as currency
○ has a label Bonuses paid to the left of this number
○ includes the heading Ahmedabad Office Bonus Report at the top of the page
○ has your name, Centre number and candidate number on the report.
• Save and print your report. [11]
PRINTOUT 2
Make sure that you have entered your name, Centre number and candidate number on
your report.
[Total: 22]
© UCLES 2016 0417/21/F/M/16 [Turn over
