本次数据库代写的主要内容是根据一个汽车维修公司案例,编写ERD模型和Access数据库设计.

FNDS015 INFORMATION TECHNOLOGY
Assessment 3 – ERD & MS Access Database
Due: End of Week 11- Sunday 26 May 2019, 11:55 PM
This is an individual assignment (20% of total subject grade)
Late submissions will incur a 5% penalty each day for up to 5 calendar days and then the total assignment score will be 0.
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.au/software – 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.au/software or can be used for free via www.deakin.edu.au/onedrive (To be used to submit the ERD)
MicrosoftVisio:availableonterminalsatDeakinCollege(Tobeused tosubmittheERD)
Gliffy Drawing Software: available at www.gliffy.com (To be used to create the ERD). This is an on-line applicationfordrawingflowchartsandotherdiagrams,suchasERDs.Thisprogramallowsa14-daytrial use. After 14 days, all of your data will be lost. You can save your work if you use GOOGLE DRIVE
FNDS015 Assessment 4 Page 1 of 9

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 Crow’s 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 Page 2 of 9

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 Page 3 of 9

4. Design another table called ‘Invoice’ as below:
5. Enter these records into the ‘Invoice’ table:
FNDS015 Assessment 4 Page 4 of 9

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.
 Double-click 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 fieldtocalculatea10%discountfromtheInvoiceAmount. Amount paid field to calculate the remaining balance after 10% discount.
The final Invoice table should look like the following table
FNDS015 Assessment 4
Page 5 of 9

8. Create a Form:
 Create a new form, ‘Columnar style’ based on the ‘Invoice’ table and include all the fields from the Invoice table.NametheformInvoice
 Modify the look of the form and place a SIMPLE and CLEAR image of a car being repaired (find this image fromanonlinesearch)intheheadersectionoftheform.Addthecompanyname,ZenaTech, to your form. Look up examples of invoices to get an idea for layout/design.
 Add your full name and student ID in the footer section of the form. Don’t 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 Page 6 of 9

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
‘Invoice_Total’. Hint: you will need to use the in the Show/Hide 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 ‘Invoice_Under_or_Equal_To_3700’.
FNDS015 Assessment 4 Page 7 of 9

10. Create a Report
 Create a Tabular report based on the query ‘Invoice_Total’ and sort in ascending order by, and
order by SumOfDiscountedAmount
 Save as ‘Job_Total’.
FNDS015 Assessment 4 Page 8 of 9

Submission Instructions:
Thisassignmentistobesubmittedthroughthe‘Assignment3submissionlink’inweek11ofthe 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 .zip file).
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 7-ZIP. This process is described in the Assignment 1 instructions.
Anycomponentnotnamedcorrectlywillincura10%scorelosspercomponent 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
Page 9 of 9