FNDS015 Information Technology Assessment 4: Project
FNDS015 – Information Technology Assessment 4 – Project
Part 2: Database
Weighting:
15% of Course Total Grade
Due Date:
Week 12
Required Software:
Microsoft Access (Windows OS)
Submission:
Online via SIBT Learning
Assessment Learning Outcomes:
• Examine database concepts and explore the Microsoft Access application environment
• Design and create the elements of a database table
• Relate the tables using primary keys and referential integrity
• Build simple and effective queries
• Generate meaningful reports from tables and queries
AN OVERVIEW OF THE DATA
GenZ has a partially created database for sales records in Microsoft Access. The database currently has 6 tables: Orders, OrderItems, Customers, Categories, Subcategories and Products.
In the second part of this project, you are required to complete the database by designing additional tables. You will build queries to analyse sales records from Queensland. Based on the queries, you will generate a report on Access so that GenZ management can make company decisions along with the report you created in Part 1 (Spreadsheet) of this project.
SETUP
1. Download the Access database file GenZ_Sales_Records.accdb from Project
Resources folder under the ‘Assessment’ tile on SIBT Learning.
2. Rename it to your preferred Name and Student Number followed by ‘Project Database’.
o For example: John 40009999 Project Database.
2020 Session 2 1
FNDS015 Information Technology Assessment 4: Project ASSESSMENT TASKS
Task 2.1: Table Design (10 Marks)
Open the Access database file that you downloaded.
Importing data
1. The GenZ_Staff.xlsx Excel file – in Project Resources Folder under the ‘Assessments’ tile on Moodle – contains staff information of the company. Download this Excel file on your computer and import it into a new table in your Access database. (1)
2. A unique Employee ID for each staff member is included in this spreadsheet. Select this as the primary key rather than letting Access add its own. Name this new table Staff. (1)
3. Open the newly imported Staff table in Design view. The field names all contain spaces which are normally best avoided on databases. Rename the fields as below and update data type and field properties: (3)
Field Name
Data Type
Field properties
staffID
Short Text
Primary key, field size 7
staffName
Short Text
field size 50
dateOfBirth
Date/Time
format as short date
position
Short Text
field size 50
deptID
Short Text
field size 3
4. As you have recently started working at GenZ, your information has not been added. Switch to Datasheet view and enter your details. (1)
Your staffID should be your first initial and last initial followed by “-1001”. Your position is Systems Analyst and your deptID is ICT.
• Example: If your name is John Smith, your staffID should be JS-1001. 5. Save and close the table.
2020 Session 2 2
FNDS015 Information Technology Assessment 4: Project
Creating new table
1. Create a new table in Design view called Departments. Add the following fields: (3)
Field Name
Data Type
Field properties
deptID
Short Text
Primary key, field size 3
deptName
Short Text
field size 50
deptHead
Short Text
field size 50
2. Switch to Datasheet view and enter the five following records. (1)
deptID
deptName
deptHead
FIN
Finance
Tiffany Campbell
ICT
Information Technology
Maddie Pham
LOG
Logistics
Bruno Carlson
MGM
Management
Steve Norris
MKT
Marketing
Peter Chambers
3. Save and close the table.
Task 2.2: Relationships (8 Marks)
1. Open the Relationships window from the Database Tools tab. This is not currently
showing all tables in the database. Add the Departments and Staff tables. (1)
2. Create a relationship between staffID (Staff table) and salesStaffID (Orders table)
and enforce referential integrity. (1)
3. Create a relationship between deptID (Departments table) and deptID (Staff table)
and enforce referential integrity. (1)
4. Save the changes and close the Relationship window.
2020 Session 2 3
FNDS015 Information Technology Assessment 4: Project
Adding and updating new records
Refer to GenZ’s new product line that you created in Assessment 3 – Group Presentation. Your product may have different variations such as different colours, sizes, design, etc. Each variation has to be entered as a separate record in the database.
1. Add all product variations that your group created in the Products table. Make sure all fields are filled for each product. (1)
2. If your products are in a different category or subcategory, add new records in Category and SubCategory tables. (1)
3. Create a relevant unique ID for each record and make sure all fields are filled in accordingly. (1)
4. Save and close all the tables.
The company decided to change the subcategory name (subCatName) “Monitor” to
“Screen”. The subCatID has to be changed to COM-SC.
5. Open the subCategory table and attempt to make changes. You will receive an
error to make changes. Close the table.
6. Open the Relationships window. Modify the relationships between subCategory
table and related tables by changing the Cascade setting. (1)
7. Save the Relationships and close it.
8. Open the subCategory table again and make changes to the subCatID and subCatName of the Monitor. (1)
9. Save and close the table.
2020 Session 2 4
FNDS015 Information Technology Assessment 4: Project
Task 2.3: Essential Queries (12 Marks) Creating a query
We want to display only products from the Phone category on the Datasheet.
1. Create a new query in Design view. Add the Products table. (1)
2. Add productID, categoryID, productName, productPrice and productDetail in the grid and show all fields except categoryID. (2)
3. Add a criterion to display only products from the Phone category. (1) Note that Category ID for Phone is PHO.
4. Apply a Sort to order product names in an ascending order. (1)
5. Save the query as qryPhones and close the query.
Multi-table queries
We want to view order items purchased by Queensland customers in March. This will require data from several related tables.
1. Create a new query in Design view. Add these tables – Customers, Orders, OrderItems and Products. (1)
2. Add the following fields to the grid. (2)
a. orderID and orderDate from the Orders table
b. customerName,segmentandstatefromtheCustomerstable c. productName from the Products table
d. priceandquantityfromtheOrderItemstable.
3. Modify the design to display products ordered by Queensland customers in March. You will have to consider modifying the below criteria. (3)
a. March in orderDate field (i.e. between 01/03/2020 and 31/03/2020), AND
b. Queensland in state field (i.e. QLD)
4. Switch to the Datasheet view. You should have 38 results. (1)
5. Save the query as qryMarchQld.
2020 Session 2 5
FNDS015 Information Technology Assessment 4: Project
Task 2.4: Creative Queries (12 Marks) Calculated fields in queries
Continue using the qryMarchQld query in Design view. We want to calculate the total sales price including the GST value for each record in the query.
1. Add a calculated field called totalSalesAfterGST. (1)
2. The GST amount is 10%. Express a mathematical expression [price]*[quantity]*1.1
after the field name followed by a colon. (3)
3. Open Property Sheet. Change the format to display as a Currency value with 2
decimal places. (2)
4. Check the results in Datasheet view. Save and close the query.
Parameter queries
We want to be able to view information about a particular customer.
1. Create a new query from the Customers table including all fields from the table. (2)
2. Create a parameter query that requests a customer name when run with a text “Enter customer name”. (1)
3. We want to display all the records containing the letters that are entered as a parameter value. You will have to use wildcards (Like, * and &) in the criteria of customerName field. (3)
For example, if we type in Lee after running the query, the Datasheet should display all records that contain the word “lee”. It should display names such as Lee Smith, Johnny Lee, Leela Thompson, Ashlee Wilson, Susan Gleem etc.
4. Save the query as qryEnterCustomerName.
2020 Session 2 6
FNDS015 Information Technology Assessment 4: Project
Task 2.5: Grouped Report (8 Marks) Creating a report based on query
We want to produce a printable report that lists all customers from Queensland who placed orders in March.
1. Open the query qryMarchQld that you created in Task 2.3.
2. Use the report wizard to create a report. (0.5)
3. Include these fields in the report: customerName, orderDate, orderID, productName, price, quantity and totalSalesAfterGST. (1)
4. Group the report by Customers. The grouping level should be as follow: (0.5)
5. Save the report as rptMarchQldOrders.
Report design
We want to make the report presentable for the management to read. We want a report layout with a similar format of the following design view.
2020 Session 2 7
FNDS015 Information Technology Assessment 4: Project Continue this part of the task by referencing the above report design.
1. In the Report Header tab, change the title of the report to “March Queensland Sales Report”. Make it this title bold, with the font of your choice. (0.5)
2. Delete all the labels in the Page Header section. (0.5)
3. In the customerName Header section, increase the font size of customerName
field to 14. Make this bold, with a dark blue colour. (0.5)
4. In the orderID Header section, bring the orderID and orderDate fields to the left.
Increase the font size to 12. Make this bold, with a dark blue colour. (0.5)
6. In the Detail section, adjust the field size and position of productName, price,
quantity and totalSalesAfterGST fields. (0.5)
7. Select the totalSalesAfterGST field and calculate the total sales amount for each group by using the Total SUM from Grouping & Totals group in the Design tab in the ribbon. Make the font within these fields bold so it stands out. (1)
8. Expand the orderID Header section and add a second line to create labels for each field of the Detail tab. Make the labels bold, with a dark blue colour. (1)
9. Add a label on the left side of each Totals field that were created in Step 7. Name them as “Order Total:”, “March Total:”, “Total Sales:”. Change the colour of the font to dark blue and bold them. (0.5)
10. Add a horizontal line in the customerName Footer tab so that there is a line after each customer in the Report view. (0.5)
11. Switch to the Report view and see if your report looks similar to the report below. Make changes accordingly referencing the Design view image on Page 6. (0.5)
2020 Session 2 8
FNDS015 Information Technology Assessment 4: Project
12. Save your report and print it as a PDF file from the Print Preview view.
Rename it to your preferred Name and Student Number followed by “Project Part 2 Report”. (2)
For example: John 40009999 Project Part 2 Report.
This is the end of the Project.
Please refer to the following page for details on how to submit your completed assessment.
2020 Session 2
9
FNDS015 Information Technology Assessment 4: Project
SUBMISSION DETAILS You are required to submit:
1. thePDFfileofyourreportfromTask2.4,and
2. the Access Database file of your work from Task 2.1 to Task 2.4.
Submit both files via the Assessment 4.2 – Database submission box located in the Assessment Tile on the FNDS015 SIBT Learning course page.
There will be no extensions except for approved special circumstance. If seeking an extension due to any extenuating circumstances, you should apply for Special Consideration Application.
The Penalty for Late Submission of assignments is 10% of the total marks for each day (or part thereof, including weekends) the assignment is late.
For assignments received more than 10 days after the due date, the assignment will be assessed, but a zero mark will be awarded.
MARKING GUIDE
Student Name: Student ID:
Score
Task 2.1: Table Design
/ 10
Task 2.2: Relationships
/8
Task 2.3: Essential Queries
/ 12
Task 2.4: Creative Queries
/ 12
Task 2.5: Grouped Report
/8
Total Score:
/ 50
Comment:
2020 Session 2 10