Group: Individual: Graded out of: 20 Weight: 20
Title: ERD MS Access
Due date:
Week 11
Sunday 26th of January 2020 11:55 PM
Submission: Instructions:
Summary: Key Criteria
Online: Hardcopy:
Students submit using the submission link located under the resources and Assessment section of Moodle.
NB: Late submissions will incur 5 penalty each day for up to 5 working days and then the total assignment score will be zero.
The intention of this assignment is to help you develop skills in using Microsoft SWAY.
As listed below
Relevant Content Weeks ULOs
Week 1 Week 7
Week 2 Week 8
Week 3 Week 9
Week 4 Week 10
Week 5 Week 11
ULO3
Week 6 Week 12
ULO1
Apply and demonstrate an
understanding of the nature and function of information technologies to a specific practical situation
ULO2
Develop and explain solutions
for an identified need which address all of the information processes
Proficiently use the main functions of Word Processing and Spreadsheet applications.
Please read the full assignment details that follow.
FNDS015 Assessment 4 ERD MS SWAY
Page 1 of 11
Assessment Summary
WARNING: Acceptable Required Software for development of this assignment:
Assignments created in software other than the listed software will not be accepted for marking!
Microsoft Access available on terminals at Deakin College or Office 2016 can be downloaded from www.deakin.edu.ausoftware Note: Office 365 does not provide Access you will need to download Office 2016 for a small price if you do not have it yourself. To be used to create the database
Microsoft Word: available on terminals at Deakin College or Office 365 can be downloaded from www.deakin.edu.ausoftware or can be used for free via www.deakin.edu.auonedrive To be used to submit the ERD
MicrosoftVisio:availableonterminalsatDeakinCollegeTobeused tosubmittheERD
Gliffy Drawing Software: available at www.gliffy.com To be used to create the ERD. This is an online applicationfordrawingflowchartsandotherdiagrams,suchasERDs.Thisprogramallowsa14daytrial use. After 14 days, all of your data will be lost. You can save your work if you use GOOGLE DRIVE
FNDS015 Assessment 4 ERD MS SWAY Page 2 of 11
Instructions:
This assignment, in two parts, is based on learning materials covered between Week 9 Week 11 from the Hunt and ClemensText,andisrelatedtotheWeek10and11topic
Part A:
1. Download the Word document titled Assignment 3 ERD Template and fill in your details.
2. Save this document as your student ID eg: ZHAND1701
3. Prepare an Entity Relationship Diagram ERD in MS Visio use Crows Foot Database Notation tool or
Gliffy for the case below: The diagram must include:
All entities from the business case. You MUST use this case below.
Correct relationships between the entities based on the business case
Entity labels
Primary keys
Attributes based on the business case. Be sure to use the correct symbols!.
Business Case for your ERD:
ZenaTech is an automotive service and repair company that receives a range of customer requests which include: regular vehicle servicing, repairs, performance modifications and Road Worthy Certificate inspections.
ZenaTech employs mechanics called Technicians who are able to carry out a broad range of automotive activities for the company. Depending on the type of a customer request, it can be completed by either one or several of the technicians at a time, however each technician can only be assigned to one request at a time.
A customer may make one or more requests Job when they call ZenaTech, and details about the customer and the type of requests Jobs need to be tracked. Requests are identified by a RequestID field.
Also ZenaTech needs to know the details of the technician who completed a request. Upon completion of a request, the customer receives an invoice that details the work done and the amount that must be paid. ZenaTech needs to track all the invoices sent to customers and ensure payment for completed requests is received.
4. You will need to copy the diagram using a screen shot PrintScrn or Snipping Tool into the Word document student ID you completed earlier in point 2. Make sure the screen shot is complete, large andclearenoughtobe viewed.
FNDS015 Assessment 4 ERD MS SWAY Page 3 of 11
Part B:
Open Microsoft Access and do the following:
1. Create a new database and name it with your student ID eg: ZHAND1701 and save it into your student folder.
2. Design a table called Customer as below via the Table Design button
3. Enter these records into the Customer table:
FNDS015 Assessment 4 ERD MS SWAY Page 4 of 11
4. Design another table called Invoice as below:
5. Enter these records into the Invoice table:
FNDS015 Assessment 4 ERD MS SWAY Page 5 of 11
6. Relationship: Create an appropriate relationship between the two tables and enforce referential integrity. This can be done in the following way:
The Edit Relationships dialog box appears:
In the Relationships window, click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.
Doubleclick the relationship line.
Select the Enforce Referential Integrity check box.
Make any additional changes to the relationship, and then click OK
7. Adding Fields to the Invoice Table:
Add Discount Amount and Amount paid fields to the Invoice table as below; DiscountedAmount fieldtocalculatea10discountfromtheInvoiceAmount. Amount paid field to calculate the remaining balance after 10 discount.
The final Invoice table should look like the following table
FNDS015 Assessment 4 ERD MS SWAY
Page 6 of 11
8. Create a Form:
Create a new form, Columnar style based on the Invoice table and include all the fields from the Invoice table. Name the form Invoice
Modify the look of the form and place a SIMPLE and CLEAR image of a car being repaired find this image fromanonlinesearchintheheadersectionoftheform.Addthecompanyname,ZenaTech, to your form. Look up examples of invoices to get an idea for layoutdesign.
Add your full name and student ID in the footer section of the form. Dont forget to type it in like this: FirstNameLastNameYAPAC1702
Change the background colour of all sections in the Property Sheet section of the form. Choose colours carefully.
FNDS015 Assessment 4 ERD MS SWAY Page 7 of 11
9. Create Queries:
Create these queries to answer the questions below:
List all the customers who live in area 3013 and include all the fields. Save the query as Customer Area.
The total value of all discounted invoices for each customer, showing the field CustomerID, LName in
the Customer table, and the fields Discounted Amount in the Invoice table. Save the query as
InvoiceTotal. Hint: you will need to use the in the ShowHide toolbox in the Design Tab, along with a SUM Total.
List all the invoices showing all the fields that have an Amount of 3,700 or less. Save the query as InvoiceUnderorEqualTo3700.
FNDS015 Assessment 4 ERD MS SWAY Page 8 of 11
10. Create a Report
Create a Tabular report based on the query InvoiceTotal and sort in ascending order by, and
order by SumOfDiscountedAmount
Save as JobTotal.
FNDS015 Assessment 4 ERD MS SWAY Page 9 of 11
Submission Instructions:
ThisassignmentistobesubmittedthroughtheAssignment3submissionlinkinweek11ofthe portal. You will be shown this process in Week 11.
Students must submit a zipped folder containing the Access Database file and the ERD template document. The file must be zipped using Winzip or 7ZIP as a .zipfile.
Name each file with your Name and ID number, then name the FOLDER as shown.
The folder and both files must be named as your Deakin College student number. The folder and bothfile names must have no other information included. The folder must be zipped using WINZIP or 7ZIP. This process is described in the Assignment 1 instructions.
Anycomponentnotnamedcorrectlywillincura10scorelosspercomponent e.g.
if the folder, word file or Access file is named incorrectly 10 loss from total
assignment score
if 2 files named incorrectly 20 loss from total assignment score
if all 3 components are named incorrectly 30 loss from total assignment score
Marking Guide:
Refer to Resources and Assessment in the Portal for the marking guide.
FNDS015 Assessment 4 ERD MS SWAY
Page 10 of 11
FNDS015 Assignment 3 Marking Student name ID:
Guide
Total Achievable Marks
6
6
6
6
Part A ERD
Marks Received
Correct entities displayed
ERD displays correct entity relationships:
Primary Keys and Labels applied to Entities:
Correct Attributes applied per entity based on business rules:
SubTotal: 24
Part B Database Modules
Tables
Customer Table design: Provide 1 point for each correct field name, property and format
Invoice Table design: Provide 1 point for each correct field name, property and format
Relationship created between the tables
20
12
2
Referential integrity applied 1
SubTotal:
Form
Columnar Form created based on Invoice table
35
1
Image of a computer is inserted in header section 2
Student name ID placed in footer section 2
Background colour changed 1
SubTotal: Queries
6
Query 1 CustomerArea 3
Query 2 InvoiceTotal 3
Query 3 InvoiceOver4000 3
SubTotal: 9 Add Calculated Field
Tax field calculated 10 tax of amount 3
SubTotal:
Report
RepTotal report Ascending sorted SubTotal:
3
2
1
3
Total maximum marks out of 80 80 Penalties 0 Scaled total out of 20 20
FNDS015 Assessment 4 ERD MS SWAY
Page 11 of 11