Database代写:FNDS015 A-TECH

根据提供的E-R关系图,在数据库中构建表结构。

Software

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 or can be used for free (To be used to submit the ERD)

Gliffy Drawing Software: available at www.gliffy.com (To be used to create the ERD). This is an on-line application for drawing flow charts and other diagrams, such as ERDs. This program allows a 14-day trial use. After 14 days, all of your data will be lost. You can save your work if you use GOOGLE DRIVE - this would be the smart thing to do! If you have a Google account, then you can login to Giffy with this.

Instructions

This assignment is based on learning materials covered between Week 9 - Week 11 from the Hunt and Clemens Text.

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 Relation Diagram (ERD) in 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
  4. You will need to copy the diagram using a screen shot (PrintScrn) into the Word document ‘student ID’ you completed earlier in point 2. Make sure the screen shot is large and clear enough to be viewed. Or download and install a program called EasyCapture (for Windows) at https://easycapture.en.softonic.com/.

Business Case for your ERD

A-TECH 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.

A-TECH 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 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 A-TECH, and details about the customer and the type of requests (‘Jobs’) need to be tracked. Requests are identified by a RequestID field.

Also A-TECH 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. A-TECH needs to track all the invoices sent to customers and ensure payment for completed requests is received.

Part B

Open Microsoft Access and do the following:

  1. Create a new database named ‘your student ID’ (eg: ZHAND1701) and save it into your studentfolder.
  2. Design a table called ‘Customer’ as below via the ‘Table Design’ button:
  3. Design another table called ‘Invoice’ as below:
  4. Enter these records into the ‘Customer’ table:
  5. Enter these records into the ‘Invoice’ table:
  6. Create an appropriate relationship between the 2 tables and enforce referential integrity.

Create a Form

  1. Create a new form, ‘Columnar style’ based on the ‘Invoice’ table and include all the fields from the Invoice table. Name the form Invoice
  2. Modify the look of the form and place a SIMPLE and CLEAR image of a car being repaired (find this image from an online search) in the header section of the form. Add a company name to your form. Add your full name and student ID in the footer section of the form.
  3. Change the background colour of all sections in the ‘Property Sheet section of the form.

Add field

  1. Create a calculated field in the Invoice table in a new column with the name ‘Discounted Invoice Amount’ with a data type of currency (in the Field Properties window). Use this field to work out the 10% discount that is removed from the total of the Invoice amount.

Create Queries

Create these queries to answer the questions below:

  1. List all the customers who live in area ‘4216’ and include all the fields. Save the query as ‘Customer Area’.
  2. The total value of all discounted invoices for each customer, showing the field CustomerID, LName in the Customer table, and the fields Discounted Invoice Amount in the Invoice table. Save the query as ‘Invoice_Total’. Hint: you will need to use the a SUM Total.
  3. List all the invoices (showing all the fields) that have an Amount of $3,999 or less. Save the query as ‘Invoice_Under_or_Equal_To_3999’.

Create a Report

  1. Create a Tabular report based on the query ‘Invoice_Total’ and sort in ascending order by, and order by SumOfDiscountedAmount
  2. Save as ‘Job_Total’.

Submission Instructions

This assignment is to be submitted through the ‘Assignment 3 submission link’ in week 12 of the 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 student number. The folder and both file names must have no other information included. The folder must be zipped using WINZIP or 7ZIP. This is available by right-clicking your mouse key then choosing ‘Send to’ then ‘Compressed (zipped) file’. Other compression software cannot be used. Assignments submitted using other software will not be marked.

Any component not named correctly will incur a 10% score loss per component 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.