CSE1ITX PC DB

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.

Background

Jack has a custom gaming PCs building business. He started the business as a hobby and it has steadily grown ever since. He now has many employees to help him in the business. As a result, he needs to manage his business information more effectively. The use of spreadsheets, which he initially used, seems to be insufficient to meet Jack's needs.

You, being Jack's technical guru, proposed to him the use of a database. As you are now familiar with MS Access database and as its available on Jacks office computers, you have proposed using MS Access to store all of his business information.

Some of the information that Jack keeps is that of his customers: their orders, the information on the computers they order, the parts that make up the computers, and the employees that make and sell these computers, etc.

Jack 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., 100000)
• FirstName: to record the customer's first name (e.g., John)
• LastName: 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)

About the customer orders (Order), he wants to keep:

• OrderID: the unique ID that identifies each order (e.g., 2000000)
• CusID: the unique ID of a customer that made the purchase, which is generated in the Customer table (e.g., 100000)
• TotalPrice: the details about the order (e.g., 250.00)
• Shipping: the cost of shipping (e.g., 50.00)
• Address: the address to where the order needs to be shipped
• SaleDate: the date on which the order was placed (e.g., 20/02/2020)
• Emp: the ID of the employee who placed the order

About the customer order details (OrderDetail), he wants to keep:

• ODID: order details ID is the ID for each item in an order
• OrderID: the unique ID that identifies each order; it links each item in the order with the Order table (e.g., 2000000)
• ComID: the product (computer) in each order item; it links each order item with the Computer table (e.g., 100)
• Price: the price at which the actual product was sold in an order (e.g., $650.00)

About the computer (Computer), he wants to keep:

• ComID: the unique number to distinguish each different model of PC (e.g., 10000)
• ComName: the name provided to each model of the PC (e.g., Gaming turbo Max X32)
• Price: the default price of each PC (e.g., 750.00)
• Warranty: the warranty provided for the PC, in months (e.g., 24)
• Date: the date the model was released (e.g., 20/02/2020)

About the computer parts (ComputerParts), he wants to keep:

• ComID: the ID number used to distinguish each PC, which comes from the Computer table (e.g., 1000)
• HWID: the ID that identifies the specific brand of hardware used to build the PC (e.g., 10000)
• Quantity: the number of the particular hardware component used in the PC (e.g., 1)
• The combination of ComID and HWID will be unique in the table. That is, every hardware used in a particular computer will be recorded only once and if multiple number of the same hardware (e.g. RAM) is used then its recorded in the Quantity field.

About the computer hardware (Hardware), he wants to keep:

• HWID: the unique ID that distinguishes each brand and type of hardware from each other (e.g., 10000)
• HWName: the name of the hardware
• HWCode: the manufacturer code of the hardware
• Manufacturer: the name of the manufacturer (e.g., IBM)
• Price: the price of the product as priced by the manufacturer (e.g., 220.00)
• ManDate: the manufactured date of a hardware component (e.g., 20/02/2020)
• Warranty: the warranty provided for the hardware by the manufacturer, in months (e.g., 12)

About the employees (Employee), he wants to keep:

• EID: the unique ID that distinguishes each employee (e.g., 12345)
• FirstName: to record the employee's first name (e.g., Andrew)
• LastName: 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. see image.

B. Based on the information and examples provided in the background section above, you must create the table fields. You must:

• Use the appropriate datatypes for each of the fields.
• Identify and define the primary keys.

C. Establish relationships between the tables as shown in Figure 2. see image.

Task 2

Update the database table fields to include validation rules and/or input mask as stated below.

A. In the Customer and Employee tables, apply the input mask so that the phone numbers entered would look like: (xx) xxxx xxxx. E.g., (04) 3210 000

B. In the Computer table apply the following validation rules:

• The Price field will only accept values between 10 and 10,000.
• The Warranty field will only accept values between 0 and 60.
• The date field must not accept any future dates. That is, it should only accept current or past dates.

Task 3

Insert the spreadsheet data provided with the assessment into the appropriate database tables in the proper formats.

• Insert data and only data related to the customers, into the Customer table.
• Insert data and only data related to the orders, into the Order table.
• Insert data and only data related to the employees, into the Employee table.
• Insert data and only data about the different computers of the business, into the Computer table.
• Insert data and only data about the different hardware components that are used for building the computers, into the Hardware table.
• Insert data about the different hardware components that make up each of the computers sold by the business, into the ComputerParts table.
• Insert data about the different products (computers) that make up each order, into the OrderDetails table.

Task 4

Design and test the following query on the database. The query should be named 'Task 4 X', where X is the alphabet denoting each of the sub-tasks.

A. List the ID, name, price and manufacturer of hardware that has 'DDR' in its name.

B. Get full details of all the customers whose phone number starts with '04'.

C. List all details of computers that cost between $800 and $1,200.

Task 5

Design and test the following query on the database. The query should be named 'Task 5'.

Select all computers with a warranty less than or equal to 18 months and provide a 5% discount to their price. The discounted price should be displayed in a column named 'Discount Price'.

Output fields:

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 hardware belonging to 'Intel'.

Output fields:

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 two date inputs (e.g.,12/6/2019 and 6/12/2019) and then display the order details of all orders that were made within the provided date range.

Output fields:

Task 8

Design and test the following query in the database. The query should be named 'Task 8'.

List the details of orders, including its total cost and the employee who handled the order. The output must only contain the orders that have a total cost higher than the price of the most expensive computer sold by the business. Since the highest priced can change, this field must be calculated on the fly and should not be a fixed value.

The field 'Sold By' must display the full name (first name and last name) of the employee who handled the order. The Order Total is the sum of the price of all computers in each order.

Hint: Aggregate functions require you to group records.

Output fields:

Task 9

Design and test the following query on the database. The query should be named 'Task 9'.

Find the profit that the company makes from the sale of the computers 'Extreme XTX 9099'. The profit is the difference between the price of the computer, as sold by the business and the sum of the individual price of the computer hardware.

The 'Price' is the cost of the computer, SumHW is the sum of the price of all the parts that make up the computer, and the profit is the difference between the Price and SumHW.

Hint: Aggregate functions require you to group records.

Output fields:

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 computers in the order. Name this query 'Task 10'.

B. Design a report (Figure 3) 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 the records grouped by the Order tables fields.

C. Add a new column/field to the end of the report to display the total cost of each order. The column must be labelled 'Order Total'. This column value must appear on the same level as the Order table records, as shown in Figure 3.

D. Add a formula to the new 'Order Total' field to calculate and display the sum of the price of all computers in each order and the shipping cost.

Figure 3: see image.

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 and update every record in the Hardware table.

A. The form must be able to display every record from the Hardware table, one record at a time, as shown in Figure 4. The field labels should be descriptive. The form must be titled 'Hardware Record'.

B. Add three buttons to the form. The buttons should be functional and perform the following functions.

• Update Hardware- Any changes made to a record or new record entered must be saved when the button is clicked.
• Delete Hardware - Deletes the record that is selected.
• Cancel Update - Undo any changes made to a record when this button is clicked.

C. Convert the textbox gorm control used to display/enter the manufacturer information into a combo box. List the combo box with the name of the manufacturers provided here:

AMD, Asus, Gigabyte, Intel, Kingston, Transcend, Nvidia, Samsung, Western Digital, Xeon

D. Add a new combo box to the top of the form and label it 'Select record by HW ID'. This combo box must contain all the different hardware IDs from the Hardware table. By selecting a hardware ID, it must load all details of the hardware associated with that ID. This should be another way of selecting and viewing the details of different hardware records.

• The form control to enter the hardware ID (HWID) should be a combo box (drop-down) with the list of HWIDs available in the Hardware table.
• Selecting the HWID must load the corresponding records of the HWID.

Figure 4: see image.