CSE1ITX IT Fundamentals
This take a look at measures your potential to assemble and use a Microsoft Entry database. You have to be capable of construct many tables, fields, and apply subject validation guidelines to them. It’s best to be capable of fetch knowledge from the database utilizing SQL queries, and show and edit knowledge utilizing kinds.
It is a solo mission. This job can’t be performed in teams.
It is a solo mission. This job can’t be performed in teams. Plagiarism is the submission of one other particular person’s work as one’s personal. Plagiarism is taken severely at La Trobe Penalties are extreme when discovered.
Your task must be typed, not handwritten or sketched.
Submit your task electronically by way of the subject Studying Portal.
A late submission penalty of 5% of the obtainable task mark every day is most at 5 days. Nothing after 5 days shall be accepted. If in case you have issues submitting on time or by no means, it’s best to apply for particular consideration.
Jim owned a pc and electronics retailer. He used to run his firm utilizing spreadsheets. Create an MS Entry database to retailer and handle Jim’s enterprise knowledge.
Jim maintains observe of his purchasers, his objects, their orders, the merchandise bought in every order, and his staff that promote merchandise.
Jim needs to maintain the next knowledge for every entity.
About his prospects (Buyer):
• CusID: a novel buyer ID (e.g., 200000)
• Fname: the client’s first title (e.g., John); (e.g., Kennedy)
Notice the client’s telephone quantity (e.g., 0432 100 000)
Notice the client’s e-mail (e.g., email@example.com).
Notice the client’s handle (e.g., 99 Some Rd, Some suburb, VIC)
Concerning the buyer orders (Order), he needs to maintain:
• OrderID: the distinctive ID that identifies every order (e.g., 1100000)
• CID: the distinctive ID of a buyer that made the acquisition, which is generated within the Buyer desk (e.g., 200000)
• ShippingAddress: the handle to the place the order must be shipped • OrderDate: the date on which the order was positioned (e.g., 20/02/2020)
• StatusDate: the date on which the present standing was set (OrderStatus); every time the order standing is up to date, this date additionally will get up to date • OrderStatus: the standing of the order (e.g., Ordered, Processing, Despatched, Delivered) • EmpID: the ID of the worker who positioned the order • OrderTotal: the overall value of the order (e.g., 256.89)
Concerning the merchandise (Product), he needs to maintain:
• ProductID: the distinctive quantity to tell apart every completely different product (e.g., 100000)
• ProductName: the title of every product (e.g., Moveable Ceramic Fan Heater)
• ProductDescription: the outline of the product
• Value: the default worth of every product (e.g., 750.00)
• Guarantee: the guarantee offered for the Merchandise, in months (e.g., 24)
Concerning the merchandise which can be bought in an order (OrderProduct), he needs to maintain:
• ProductID: the ID quantity used to tell apart every product, which comes from the Product desk (e.g., 1000)
• OrderID: the ID that identifies the every order positioned by the shoppers (e.g., 10000)
• Amount: the quantity/amount of a selected product bought within the order (e.g., 1)
• Value: the worth at which a product was bought in an order (e.g., 20.59)
Concerning the staff (Worker), he needs to maintain:
• EmpID: the distinctive ID that distinguishes every worker (e.g., 12345)
• FName: to document the worker’s first title (e.g., Andrew)
• LName: to document the worker’s final title (e.g., Jackson)
• Deal with: to document the worker’s handle (e.g., 33 Clarke Road, Southbank VIC, 3004)
• Cellphone: to document the worker’s telephone quantity (e.g., 0432 100 000)
• E mail: to document the worker’s e-mail handle (e.g., firstname.lastname@example.org)
• BankAccount: retains the checking account variety of the staff for payroll functions (e.g., 088-888 99-111-000zero)
On this evaluation, you might be required to create a Microsoft Entry database based mostly on the data offered and populate it with the information offered. Then you might be to design completely different SQL queries, stories and kinds to retrieve, show and replace knowledge within the database.
For this job it’s essential to create the database and all of the tables in it, and set up the connection between the tables. The database should be named ‘xxx_CSE1ITX_A4_Database.accdb’, the place ‘xxx’ should be changed along with your pupil quantity.
A. Create a Microsoft Entry database with the database tables as proven in Determine 1.
B. Primarily based on the data and examples offered within the background part above, it’s essential to create the desk fields. You have to: 1. Use the suitable datatypes for every of the fields.
2. Establish and outline the first keys.
C. For the next fields, use a lookup wizard ( ) within the knowledge kind choice subject to create a combo field linking them to the related tables.
Notice: You received’t see the information proven within the figures beneath as you haven’t entered them into the database.
a. OrderProduct: i. Make the PID subject a combo field that exhibits the ProductID, Title and Value fields kind the Product desk.
b. Order: i. Make the CusID subject a combo field that exhibits the CusID, FName and LName from the Buyer desk.
ii. Make the EmpID subject a combo field that exhibits the EmpID, FName and LName fields from the Worker desk.
D. Set up relationships between the tables as proven in Determine 5.
Determine 5 Process 2 Replace the database desk fields to incorporate validation guidelines and enter masks as said beneath.
A. Apply enter masks to the fields listed beneath:
• Buyer o The Cellphone subject should be within the format: (xx) xxxx xxx
• Worker o The Cellphone subject should be within the format: (xx) xxxx xxx o The BankAccount subject should be within the format: xxx-xxx xx-xxx-xxxx.
B. Apply validation guidelines to the next fields:
• Product o The Value subject will solely settle for values between 10 and 10,000 o The Guarantee subject will solely settle for values between zero and 60
• OrderProduct o The Amount subject should solely settle for values between 1 and 99
• Order o The StatusDate subject should all the time be better than or equal to the OrderDate
Insert the spreadsheet knowledge supplied with the evaluation into the suitable database tables that you just created in Process 1.
Process four Design and take a look at the next question on the database. The question must be named ‘Process 4X’. the place ‘X’ is the alphabet denoting every of the subtasks.
A. Checklist the ProductID, Title and Value of merchandise on sale which have the phrase ‘Apple’ of their title.
ProductID Title Value
B. Checklist all particulars of merchandise that value between $1000 and $2000.
C. Get full particulars of all the shoppers whose telephone quantity ends with numbers between 50 and 59 (e.g., 040123456).
Process 5 Design and take a look at the next question on the database. The question must be named ‘Process 5’. Choose all merchandise with a guaranty of 18 months or extra and supply an eight% low cost to their worth. The discounted worth must be displayed in a column named ‘Low cost Value’.
ProductID Title Value Guarantee Low cost DiscountPrice
Process 6 Design and take a look at the next question on the database. The question must be named ‘Process 6’.
Calculate the typical, most and minimal worth of merchandise bought. The end result should be offered in a single row with the fields named and ordered as proven beneath.
Most Value Common Value Minimal Value
Trace: Make use of the mixture features.
Process 7 Design and take a look at the next question on the database. The question must be named ‘Process 7’.
Design a question that prompts the consumer for a date enter (e.g.,12/6/2020) after which show the order particulars of all orders that have been made on or earlier than the date and have been delivered.
Output fields: From Buyer desk From Order desk
CusID FirstName LastName OrderID ShippingAddress OrderDate
Process eight Design and take a look at the next question within the database. The question must be named ‘Process eight’.
• Checklist the small print of orders, together with their whole value and the worker who dealt with the order.
• The output should solely include the orders which have a complete value larger than the typical value of orders. For the reason that common value can change over time, this subject should be calculated on the fly and shouldn’t be a hard and fast worth.
• The sphere ‘Bought By’ should show the total title (first title and final title, e.g., Albert Einstein) of the client who bought the order.
• The sphere ‘Offered By’ should show the second title and the primary letter (preliminary) of the primary title (e.g., Albert Einstein ? Einstein A.) of the worker who dealt with the order.
From Order desk From Worker desk From Order desk
OrderID Bought By CusID OrderDate Offered By OrderTotal
Design and take a look at the next question on the database. The question must be named ‘Process 9’.
For all orders that have been delivered inside per week of putting the order, discover the overall worth of the order in line with the present product worth and the present low cost utilized, and title this subject ‘NewTotal’. Discover the distinction between the unique order whole (OrderTotal) and the brand new order whole (NewTotal), and title the sector ‘Complete Distinction’.
Trace: Mixture features require you to group data.
Output fields: OrderID OrderTotal NewTotal Complete Distinction
Design a question after which a report based mostly on the question to show order particulars. The question and report must be named ‘Process 10’.
Design a report back to show the data from the Order desk, as proven in Determine three.
A. Design a question to retrieve order particulars, together with the merchandise within the order. Title this question ‘Process 10’.
From Order desk Product OrderDetails
CusI D OrderI D OrderDa te OrderStat us StatusDa te OrderTot al Title PID Qua ntity Value
B. Design a report (Determine 6) utilizing the above question (Process 10) and the report should: • be named ‘Process 10’ and titled ‘Order Report’
• use a ‘stepped’ structure in panorama orientation
• have just one worth subject displaying each the order whole and the worth of particular person objects within the order.
Determine 6 Process 11
Design and take a look at a kind to show and replace knowledge within the database. The shape must be named ‘Process 11’.
Design an MS Entry kind to show, replace and place orders. Undergo all of the subtasks earlier than beginning the duty.
A. The shape should be capable of show each document from the Order desk, one document at a time, as proven in Determine 7. The opposite necessities are: • The shape should be titled ‘Order Kind’.
• CusID subject must be a combo field with the listing of Prospects’ info.
• OrderStatus must be a combo field with the listing of all order statuses.
• EmpID subject must be a combo field with the listing of Workers’ info.
B. Embody info of all of the merchandise in every order on the backside of the shape (Order Merchandise) as proven in Determine 7. This could present the listing of merchandise corresponding to every order.
C. Add three buttons to the shape. The buttons must be purposeful and carry out the next features.
• Replace Order– any modifications made to a document or new document entered should be saved when the button is clicked.
• Delete Order – delete the document that’s chosen.
• Cancel Order – undo any modifications made to a document when this button is clicked.
D. Add a brand new combo field to the highest of the shape and label it ‘Choose Order’. This combo field should include all of the order IDs from the Order desk. By choosing an order ID, it should load all particulars of the order related to that ID. This must be one other approach of choosing and viewing the small print of various data.
Determine 7 Submitting your work
When you have got accomplished, submit the options to the above duties by way of the evaluation four exercise hyperlink obtainable in your CSE1ITX topic web page on the Studying Portal. It’s best to submit the next:
• Submit your Microsoft Entry database referred to as xxx_CSE1ITX_A4_Database.accdb (the place xxx is your pupil quantity).
• Submit any further feedback or explanations as a Microsoft Phrase doc referred to as xxx_CSE1ITX_A4_Database.docx (the place xxx is your pupil quantity).
Evaluation four – Marking Standards
Process Response Mark Process 1 2.5 Process 1 A The database and all 5 tables created zero.5 Process 1 B All 5 tables have all of the fields with acceptable knowledge varieties 1 Process 1 C three fields are carried out as combo/listing field with the three fields within the listing when expanded zero.5 Process 1 D Relationship between all tables established correctly zero.5 Process 2 1.5 Process 2 A Enter masks utilized to all related fields zero.75 Process 2 B Discipline validation utilized accurately to all related fields zero.75 Process three 2 Process three Information totally entered in all tables 2 Process four three Process four A Listed the ProductID, Title and Value of merchandise on sale which have the phrase ‘Apple’ of their title. 1 Process four B Checklist all particulars of merchandise that value between $1000 and $2000 1 Process four C Get full particulars of all the shoppers whose telephone quantity ends with numbers between 50 and 59 (e.g., 040123456). 1 Process 5 three Process 5 Lists all merchandise with a minimal of 18 months guarantee, with the right fields and calculations three Process 6 three Process 6 Question end result incorporates all three fields/columns, with the right filed names and end result three Most Value Common Value Minimal Value
Process 7 three Process 7 Question prompts for date enter and supplies the right output (orders positioned on or earlier than the date and was delivered) three Process eight three.5 Process eight Lists the small print of orders, together with its whole value and the worker who dealt with the order, with orders solely with whole value larger than the typical value of orders, and with all output fields named and formatted accurately three.5 Process 9 four Process 9 Right data with all fields named and calculated accurately four Process 10 four.5 Process 10 A Question exhibits all order particulars and related fields, together with that of the merchandise within the order 1.5 Process 10 B Report designed from the question in Process 10 and the report accurately formatted with all fields three Process 11 5 Process 11 A Kind well-presented with appropriate fields and title, and it’s attainable to navigate by means of orders, one after the other 2 Process 11 B The shape incorporates listing of merchandise within the order 1 Process 11 C All three buttons are functioning correctly 2 Complete 35