Hi There,
My assignment questions are pdf and exel file is source files fro activities to use to create database. please reply with best price possible.. it is pretty easy assigment and i dont have time to do as work full time.. you guys done another assignment too which need attentions as well....

Assessment 4
Databases
CSE1ITX Information Technology Fundamentals

Before you begin
Objectives
The objective of this assessment is to assess your ability to create and use a Microsoft (MS) Access database. You must be able to create multiple tables and the different fields in them by selecting the relevant datatypes and implement field validation rules. You should also be able to retrieve data from the database by designing different SQL queries and using reports, and also be able to display and update data using forms.
This is an individual assignment. Students are not permitted to work in a group when writing this assignment.
Copying and Plagiarism
This is an individual assignment. Students are not permitted to work in a group when writing this assignment. Plagiarism is the submission of another person’s work in a manner that gives the impression that the work is their own. La Trobe University treats plagiarism seriously. When detected, penalties are strictly imposed.
Further information can be found on http://www.latrobe.edu.au/students/academicintegrity/explanation/plagiarism
Submission Guidelines
Your assignment submission should be typed, not written/drawn by hand.
Submit the electronic copy of your assignment through the subject Learning Portal.
Submission after the deadline will incur a penalty of 5% of the available assignment mark per day capped at 5 days. No assignment will be accepted after 5 days. If you have encountered difficulties that lead to late submission or no submission, you should apply for special consideration.
Background
Jim had a retail business that sells computers and electronic gadgets. He used to manage his business with the help of spreadsheets. You as an IT student need to help Jim by creating an MS Access database to keep and manage his business data.
Some of the information that Jim keeps is that of his customers, the products the business sells, the orders placed by the customers, the products sold in each order, and the employees that sell products, etc.
Jim wants to keep the following information for each of the entities mentioned earlier.
About his customers (Customer), he wants to keep:
• CusID: customer ID to uniquely identify each customer (e.g., 200000)
• FName: to record the customer’s first name (e.g., John)
• LName: to record the customer’s last name (e.g., Kennedy)
• Phone: to record the customer’s phone number (e.g., 0432 100 000)
• Email: to record the customer’s email address (e.g., username@domain.com)
• Address: to record the customer’s address (e.g., 99 Some Rd, Some suburb, VIC)
About the customer orders (Order), he wants to keep:
• OrderID: the unique ID that identifies each order (e.g., 1100000)
• CID: the unique ID of a customer that made the purchase, which is generated in the Customer table (e.g., 200000)
• ShippingAddress: the address to where the order needs to be shipped
• OrderDate: the date on which the order was placed (e.g., 20/02/2020)
• StatusDate: the date on which the current status was set (OrderStatus); each time the order status is updated, this date also gets updated
• OrderStatus: the status of the order (e.g., Ordered, Processing, Despatched, Delivered)
• EmpID: the ID of the employee who placed the order
• OrderTotal: the total cost of the order (e.g., 256.89)
About the products (Product), he wants to keep:
• ProductID: the unique number to distinguish each different product (e.g., 100000)
• ProductName: the name of each product (e.g., Portable Ceramic Fan Heater)
• ProductDescription: the description of the product
• Price: the default price of each product (e.g., 750.00)
• Warranty: the warranty provided for the Products, in months (e.g., 24)
About the products that are sold in an order (OrderProduct), he wants to keep:
• ProductID: the ID number used to distinguish each product, which comes from the Product table (e.g., 1000)
• OrderID: the ID that identifies the each order placed by the customers (e.g., 10000)
• Quantity: the number/quantity of a particular product purchased in the order (e.g., 1)
• Price: the price at which a product was sold in an order (e.g., 20.59)
About the employees (Employee), he wants to keep:
• EmpID: the unique ID that distinguishes each employee (e.g., 12345)
• FName: to record the employee’s first name (e.g., Andrew)
• LName: to record the employee’s last name (e.g., Jackson)
• Address: to record the employee’s address (e.g., 33 Clarke Street, Southbank VIC, 3004)
• Phone: to record the employee’s phone number (e.g., 0432 100 000)
• Email: to record the employee’s email address (e.g., username@domain.com)
• BankAccount: keeps the bank account number of the employees for payroll purposes (e.g., 088-888 99-111-0000)
Tasks
In this assessment, you are required to create a Microsoft Access database based on the information provided and populate it with the data provided. Then you are to design different SQL queries, reports and forms to retrieve, display and update data in the database.
Task 1
For this task you must create the database and all the tables in it, and establish the relationship between the tables. The database must be named ‘xxx_CSE1ITX_A4_Database.accdb’, where ‘xxx’ must be replaced with your student number.
A. Create a Microsoft Access database with the database tables as shown in Figure 1.
Figure 1
B. Based on the information and examples provided in the background section above, you must create the table fields. You must:
1. Use the appropriate datatypes for each of the fields.
2. Identify and define the primary keys.
C. For the following fields, use a lookup wizard ( ) in the data type selection field to create a combo box linking them to the relevant tables.
Note: You won’t see the data shown in the figures below as you have not entered them into the database.
a. OrderProduct:
i. Make the PID field a combo box that shows the ProductID, Name and Price fields form the Product table.
Figure 2
b. Order:
i. Make the CusID field a combo box that shows the CusID, FName and LName from the Customer table.
Figure 3
ii. Make the EmpID field a combo box that shows the EmpID, FName and LName fields from the Employee table.
Figure 4
D. Establish relationships between the tables as shown in Figure 5.
Figure 5
Task 2
Update the database table fields to include validation rules and input mask as stated below.
A. Apply input masks to the fields listed below:
• Customer
o The Phone field must be in the format: (xx) xxxx xxx
• Employee
o The Phone field must be in the format: (xx) xxxx xxx o The BankAccount field must be in the format: xxx-xxx xx-xxx-xxxx.
B. Apply validation rules to the following fields:
• Product o The Price field will only accept values between 10 and 10,000 o The Warranty field will only accept values between 0 and 60
• OrderProduct o The Quantity field must only accept values between 1 and 99
• Order o The StatusDate field must always be greater than or equal to the OrderDate
Task 3
Insert the spreadsheet data provided with the assessment into the appropriate database tables that you created in Task 1.
Task 4
Design and test the following query on the database. The query should be named ‘Task 4X’.
where ‘X’ is the alphabet denoting each of the subtasks.
A. List the ProductID, Name and Price of products on sale that have the word ‘Apple’ in their name.
ProductID Name Price
B. List all details of products that cost between $1000 and $2000.
C. Get full details of all the customers whose phone number ends with numbers between 50 and 59 (e.g., 040123456).
Task 5
Design and test the following query on the database. The query should be named ‘Task 5’.
Select all products with a warranty of 18 months or more and provide an 8% discount to their price. The discounted price should be displayed in a column named ‘Discount Price’.
Output fields:
ProductID Name Price Warranty Discount DiscountPrice
Task 6
Design and test the following query on the database. The query should be named ‘Task 6’.
Calculate the average, maximum and minimum price of products sold. The result must be provided in a single row with the fields named and ordered as shown below.
Output fields:
Maximum Price Average Price Minimum Price
Hint: Make use of the aggregate functions.
Task 7
Design and test the following query on the database. The query should be named ‘Task 7’.
Design a query that prompts the user for a date input (e.g.,12/6/2020) and then display the order details of all orders that were made on or before the date and were delivered.
Output fields:
From Customer table From Order table
CusID FirstName LastName OrderID ShippingAddress OrderDate
Task 8
Design and test the following query in the database. The query should be named ‘Task 8’.
• List the details of orders, including their total cost and the employee who handled the order.
• The output must only contain the orders that have a total cost higher than the average cost of orders. Since the average cost can change over time, this field must be calculated on the fly and should not be a fixed value.
• The field ‘Purchased By’ must display the full name (first name and last name, e.g., Albert Einstein) of the customer who purchased the order.
• The field ‘Sold By’ must display the second name and the first letter (initial) of the first name (e.g., Albert Einstein ? Einstein A.) of the employee who handled the order.
Output fields:
From Order table From Employee
table From Order table
OrderID Purchased By CusID OrderDate Sold By OrderTotal
Task 9
Design and test the following query on the database. The query should be named ‘Task 9’.
For all orders that were delivered within a week of placing the order, find the total price of the order according to the current product price and the current discount applied, and name this field ‘NewTotal’. Find the difference between the original order total (OrderTotal) and the new order total (NewTotal), and name the field ‘Total Difference’.
Hint: Aggregate functions require you to group records.
Output fields:
OrderID OrderTotal NewTotal Total Difference
Task 10
Design a query and then a report based on the query to display order details. The query and report should be named ‘Task 10’.
Design a report to display the information from the Order table, as shown in Figure 3.
A. Design a query to retrieve order details, including the products in the order. Name this query ‘Task 10’.
From Order table Product OrderDetails
CusI D OrderI D OrderDa
te OrderStat us StatusDa
te OrderTot
al Name PID Qua
ntity Price
B. Design a report (Figure 6) using the above query (Task 10) and the report must:
• be named ‘Task 10’ and titled ‘Order Report’
• use a ‘stepped’ layout in landscape orientation
• have only one price field showing both the order total and the price of individual items in the order.
Figure 6
Task 11
Design and test a form to display and update data in the database. The form should be named ‘Task 11’.
Design an MS Access form to display, update and place orders. Go through all the subtasks before starting the task.
A. The form must be able to display every record from the Order table, one record at a time, as shown in Figure 7. The other requirements are: • The form must be titled ‘Order Form’.
• CusID field should be a combo box with the list of Customers’ information.
• OrderStatus should be a combo box with the list of all order statuses.
• EmpID field should be a combo box with the list of Employees’ information.
B. Include information of all the products in each order at the bottom of the form (Order Products) as shown in Figure 7. This should show the list of products corresponding to each order.
C. Add three buttons to the form. The buttons should be functional and perform the following functions.
• Update Order– any changes made to a record or new record entered must be saved when the button is clicked.
• Delete Order – delete the record that is selected.
• Cancel Order – undo any changes made to a record when this button is clicked.
D. Add a new combo box to the top of the form and label it ‘Select Order’. This combo box must contain all the order IDs from the Order table. By selecting an order ID, it must load all details of the order associated with that ID. This should be another way of selecting and viewing the details of different hardware records.
Figure 7
Submitting your work
When you have completed, submit the solutions to the above tasks via the assessment 4 activity link available in your CSE1ITX subject page on the Learning Portal. You should submit the following:
• Submit your Microsoft Access database called xxx_CSE1ITX_A4_Database.accdb (where xxx is your student number).
• Submit any additional comments or explanations as a Microsoft Word document called
xxx_CSE1ITX_A4_Database.docx (where xxx is your student number).
Assessment 4 – Marking Criteria
Task Response Mark
Task 1 2.5
Task 1 A The database and all 5 tables created 0.5
Task 1 B All 5 tables have all the fields with appropriate data types 1
Task 1 C 3 fields are implemented as combo/list box with the 3 fields in the list when expanded 0.5
Task 1 D Relationship between all tables established properly 0.5
Task 2 1.5
Task 2 A Input mask applied to all relevant fields 0.75
Task 2 B Field validation applied correctly to all relevant fields 0.75
Task 3 2
Task 3 Data fully entered in all tables 2
Task 4 3
Task 4 A Listed the ProductID, Name and Price of products on sale that have the word ‘Apple’ in their name. 1
Task 4 B List all details of products that cost between $1000 and $2000 1
Task 4 C Get full details of all the customers whose phone number ends with numbers between 50 and 59 (e.g., 040123456). 1
Task 5 3
Task 5 Lists all products with a minimum of 18 months warranty, with the correct fields and calculations 3
Task 6 3
Task 6 Query result contains all 3 fields/columns, with the correct filed names and result 3
Maximum Price Average Price Minimum Price
Task 7 3
Task 7 Query prompts for date input and provides the correct output (orders placed on or before the date and was delivered) 3
Task 8 3.5
Task 8 Lists the details of orders, including its total cost and the employee who handled the order, with orders only with total cost higher than the average cost of orders, and with all output fields named and formatted correctly 3.5
Task 9 4
Task 9 Correct records with all fields named and calculated correctly 4
Task 10 4.5
Task 10 A Query shows all order details and relevant fields, including that of the products in the order 1.5
Task 10 B Report designed from the query in Task 10 and the report correctly formatted with all fields 3
Task 11 5
Task 11 A Form well-presented with correct fields and title, and it’s possible to navigate through orders, one at a time 2
Task 11 B The form contains list of products in the order 1
Task 11 C All 3 buttons are functioning properly 2
Total 35