Reference no: EM133126275

Database Management System (DBMS) and Presentation

Assignment - Task Overview

Learning Objective 1: apply information systems skills to develop practical solutions to real- world business problems using database management and decision support systems.

Learning Objective 2: communicate practical solutions to issues dealing with implementation of appropriate information systems to both technical and non-technical audiences within a business context.

Task Description

Case Description:
Dr Dan Tooine retired in 2018 after a long career in Psychology. To keep busy and to fulfil a lifelong dream, Dr Dan Tooine started a Memorabilia mail order business, specialising in importing "Star Wars" related products, called "Kashyyyk Collectables". The business specialises in imported Star Wars merchandise such as statues, iPhone skins, giftware, bobbleheads, mugs, clothing, and other assorted Star Wars related paraphernalia to customers via mail order around Australia.
Star Wars merchandise has become very popular, since Disney's acquisition of the franchise and their massive re-start of the movies and television series have brought the phenomenon back into the public's attention such as The Mandalorian; Star Wars: Bad Batch; and the Star Wars: Visions have brought the phenomenon back into the public's attention, as well as the massive spectacle that the 40th Anniversary of A New Hope stirred in 2017; and Dr Dan Tooine's business has experienced a rapid growth in their mail orders. When Dr Dan Tooine first retired, the business bought a personal computer to help manage the books and finances.

Kashyyyk Collectables is located at Shop 9, 63 Carrie-Fisher Boulevard, Toowoomba (about 100 km west of Brisbane). Their ABN is 126 279 410. Dr Dan Tooine can be contacted via phone on
(07) 4632 5683; fax on (07) 4632 5685; or email on [email protected].
Dr Dan Tooine is extremely impressed with our computing firm "First Order Technologies" and the business has contracted our firm to assist them in setting up their various computer-based information systems.
The first computer-based information system that Dr Dan Tooine is interested in is a Database Management System (DBMS) utilising Microsoft Access. The DBMS will store all the business's customer details, information about the stock items that the business sells, the suppliers that the business uses and all postal order transactions for the business. It will allow Dr Dan Tooine to run several queries on the data, which the business has specified below, and they wish to be able to print out an invoice for each customer at the end of each month.
Dr Dan Tooine has provided to us a partial list of the business's customers, stock items, and suppliers and a partial list of the business's postal orders placed first half of the year 2021 (between January and June 2021) to use as test data in four-tab delimited text files, which can be found on your firm's Intranet (File is attached in your email).
Dr Dan Tooine would like the information in the four-tab delimited text files initially imported into the Database Management System (DBMS). The suppliers used by the business, source items from either Montenegro (ME) or Belize (BZ). They allow the business to select from either of these two regions on an ad hoc basic depending on the most favourable exchange rate at the time.
Dr Skye Walker, your supervisor at First Order Technologies, has developed a database structure that has been determined to be suitable to create a database to store Dr Dan Tooine's customer, item, supplier and order data for the business, which has been provided as follows:

• Select No Primary Key option on the fourth page of the wizard - these will be set in Task 2 - then click Next.

• Name the table tblCustomers on the final page of the wizard and click Finish.

Task 2: Modify Table Design

Modify the four imported tables' structures using the given Table Specifications, from the section above, in Microsoft Access using Table Design View:

• Use the above table specifications, checking that you have the correct table names and field names.
• Check that all fields have the appropriate Data Type from the following:
• Short Text, Number, Date/Time, Currency, Yes/No.
• Add appropriate Field Descriptions to ALL data fields with a well-structured description, eg. for CustID an appropriate description would be:
• Customer Identification | Data Type: Short Text: 3 Characters

• Ensure that you also set properties for each field where appropriate. You MUST set the following:
- Field Size - set appropriate logical fields sizes for all Short Text and Number data types (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double).
- Caption - set an appropriate English caption for all fields where the field name is not in English (eg. Customer ID for CustID).
Format - set an appropriate format for all: Yes/No and Date data fields; Text data fields that should be stored in Upper-Case (State); Text data fields that should be stored in lower-case (Email Address); and numbers that contain currency or percentages.
- Decimals - set an appropriate number of decimal places for any single or double numbers
- Default Value - set an appropriate default value for all Yes / No data Fields
- Required - set any data Fields that MUST contain data to Yes and leave any Fields where data is optional as No
- Validation Rule and Validation Text - include appropriate Validation Rules in for each of the following data Fields: Title; State; and Item Type. This ensures that only limited value options can be entered for these data Fields.

Task 3: Set Primary Keys

Set the Primary Keys for the four (4) Tables as per the following table specifications in Microsoft Access using Table Design View:

Task 4: Create Relationships between the Tables
Create the following relationships between the four (4) tables:
See the Beskeen et al Microsoft Office Illustrated textbook Chapter 5, the Forum Postings, Practical Appendixes, the Practical Videos provided in the Assignment 2 Assessment Tab, and the YouTube videos on the StudyDesk for more details on creating relationships.

• tblCustomers to tblOrders
• tblItems to tblOrders
• tblSuppliers to tblItems

- Check that the correct table names and field names are listed in the ‘Edit Relationships' screen.
- Ensure that you ENFORCE referential integrity, but do not set Cascading Updates or Cascading Deletes.

You should check that the computer has identified correctly the 1 to ∞ relationships between the four (4)
tables.

Task 5: Editing Table Data

Edit Customer ID 112 in the Customers Table to your own details.
• Title, Given Names and Family Name.
• Address, City, State and Postcode.
• Date of Birth to your own Date of Birth (note only the course team and markers will have access to this data).

Task 6: Create Data and Update Queries

Create the following Five (5) queries using the Query Design View. Use the names given for each query to avoid confusion for the client (make sure you submit them in the order given below).

Query 1: Unfulfilled Light Clothing and Apparel Orders:
Dr Dan Tooine would like to see how many Clothing and Apparel orders placed in the first quarter of 2021 (between 1st January and 31st March), weighing less than a kilogram (1 kg), and where the items is currently out of stock but on order.
Create a Select Query that will display the full Customer's Name (including Title, Given Names and Family Name), Full Item Details (including Type, Description, Size and Freight Weight), Order Date and Order Qty.

Limit the data to all Clothing and Apparel orders, placed between 1st January and 31st March (using the BETWEEN criteria tool), weighing less than 1 Kg, and that are currently not in stock but it is on order.

Display the results sorted in Descending Order by Order Date, check that have you included all necessary Fields and ensure that you only display the required Fields.

Query 2: Eastern Australian Decal Orders from a Supplier:

Dr Dan Tooine would like to have a query that the business can reuse with a manually entered Supplier Name, to see how many customers living in Eastern Australia (Queensland, New South Wales, the Australian Capital Territory or Victoria) have purchased a Star Wars Decal item obtained from the Supplier as specified.
Create a Select Query that will display the full Customer's Name (including Title, Given Names and Family Name), and Full Customer's Address, and the Item Type, Item Description, Order Date and Order Qty.

Limit the data to customers who live in Queensland, New South Wales, the Australian Capital Territory or Victoria (using the IN criteria tools), who have purchased an Item Description containing the word "decal" (using the LIKE criteria tool), and Supplier Name obtained from a user specified Parameter.

Display the results sorted in Ascending Order by State and Item Description, check that have you included all necessary Fields and ensure that you only display the required Fields and test using Supplier's Name of "Wilhuff Tarkin International".

Query 3: Import Duty Belize (BZ) Cost Price Increase:
A new Australian import duty of 11.5% has been added for all products being imported from Belize (BZ) and Dr Dan Tooine has asked you to develop a query that will update the Cost Price from Belize (BZ) of all items that are currently on order. As it would be considered profiteering, it is important that the query does not update the Cost Price of items already in stock.
Create an Update Query that will raise the Belize (BZ) Cost Price for all items on order that are not in stock by 11.5%. It is always good practice to backup any data before running an update or a delete query. The following steps should be followed to achieve this Query:
• In the List of Tables, copy the table called tblItems and paste it with the new name
tblBackupItems.

• Create an Update Query that will increase the Cost Price (BZ) in tblItems by 11.5% for all items currently not in stock but on order.
• Include rounding to 2 decimal places with the update calculation by placing the following function around the update formula: ROUND(update_formula ,2)

Query 4: Late Dispatched Orders Form Query:

Dr Dan Tooine would like to have a single query that the business can display within a form (Task 7: Form 1) to see within this single query the customers who are either:

• Living in New South Wales, have had items dispatched late (between 15th June and today) and where the item was ordered after the 1st June and the item is in stock
Or
• Living in Queensland, have had items dispatched late (between 20th June and today) and where the item was ordered after the 5th June and the item is in stock
Or
• Living in Victoria, have had items dispatched late (between 25th June and today) and where the item was ordered after the 10th June and the item is in stock
Create a Select Query that will display the CustID, Full Customer's Name (including Title, Given Names, and Family Name), Full Customer's Address, Date of Birth, eMail Address, MailingList, ItemID, Full Item Details (including Type, Description, Size, Freight Weight and Supplier Name), OrderDate, Dispatch Date and OrderQty for those customers.
Limit the data in the single query to either customers (using the OR Criteria):
i) living in New South Wales, who have placed Orders after the 1st June 2021 and where these orders were only dispatched between the 15th June 2021 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock
ii) OR living in Queensland (using the OR criteria row), who have placed Orders after the 5th June 2021 but where these orders were only dispatched between the 20th June 2021 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock
iii) OR living in Victoria (using the OR criteria row), who have placed Orders after the 10th June 2021 but where these orders were only dispatched between the 25th June 2021 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock
Display the results sorted in Ascending Order by State and Order Date, check that have you included all necessary Fields and ensure that you only display the required Fields.

Query 5: Invoice Query with Calculated Fields:
Dr Dan Tooine would like to have a query that the business can use to produce customer invoices (Task
8) to see how many customers on our Mailing List ordered "Props and Replicas" or "Posters, Prints and Standees", but only those items that were not associated with the "Darth Vader" character, and where the Item is in stock. This query should also calculate the selling price (based on goods purchased from the Ireland (IR) and order total, both of which will be needed on the customer invoices.

Create a Select Query that will display the CustID, Full Customer's Name (including Title, Given Names and Family Name), Full Customer Address, ItemID, Full Item Details (including Type, Description, Size, Weight, and Supplier Name), OrderDate, and OrderQty, including two calculated Fields - Selling Price (AU) and Order Total (AU).

Limit the query to only those customers on our Mailing List who ordered "Props and Replicas" or "Posters, Prints and Standees" (using the IN criteria tools), where the Item DOES NOT contain the words "Darth Vader" (using the NOT criteria tools and the LIKE criteria tool), and where the business currently has that item in stock.

Task 7: Create Data Display Forms
Create the following two (2) data entry forms using the Form Wizard and Form Design.

Form 1: Late Orders Form Display with Calculated Fields:
Using all Fields from Task 6 Query 4, create a professional user-friendly Display Form (using either the Columnar or Justified Form Wizard Templates) that will allow users to view order details stored in the Database Management System (DBMS).
Include two calculated Textbox Fields that will calculate:
• Customer's Age in whole years (using the DATEDIFF function - see video Assignment 1 Extra Resources).
• Dispatch Delay in whole days (Dispatch Date - Order Date). The form should demonstrate the following form controls:
• Image -an appropriate logo at the top of the form
• Label -an appropriate Form Title and appropriate Section Headings
• Combo Boxes (using a Value List in the Properties) - demonstrate for Title, State and Item Type Fields only
• Check box - demonstrate for MailingList Field only (this may automatically happen in some versions of Microsoft Access).
• Buttons - demonstrate the following four (4) Navigation Buttons (First Record, Previous Record, Next Record, Last Record).

Form 2: Supplier Items Display Form with Subform

Create a professional user-friendly Display Form (using either the Columnar or Justified Form Wizard Templates) and a sub-form that will allow users to view each supplier record's details stored in the Database Management System (DBMS).
The main form should contain All Supplier details, the sub-form should contain a Datasheet view of the items sold by that particular Supplier (including at least Item Type, Item Description, and Freight Weight) - name the subform: frmItemsSub.
The form should demonstrate the following form controls:
• Image - an appropriate logo at the top of the form
• Label - an appropriate Form Title and SubForm Heading
• List Box (using a Value List in the Properties) - demonstrate for State Fields only.
• Buttons - demonstrate the following four (4) Navigation Buttons (First Record, Previous Record, Next Record, Last Record).

Task 8: Create an Invoice Report

Dr Dan Tooine has provided you with the following sample Invoice. The client requires you to develop a similar invoice for their business.

Task 9: End of Project Presentation to the Client

Dr Skye Walker, your supervisor at First Order Technologies, has asked you to provide a professionally developed business presentation to the client, Dr Dan Tooine, outlining the process that was taken in the development of this Database Management System. Create a PowerPoint Presentation and name it ‘[lastname] [initial] _ [student number] _ [course code] _ [assignment number]'

Make sure that your professionally developed business presentation is prepared so that it conforms to the course StudyDesk presentation resources. These resources include, but are not limited to, the Practical Appendixes found on the Study Schedule and Module Materials and various videos.

Presentation Content - the following are the topics the presentation must answer.

1. Define what a Database Management System is.

2. Define what a Relational Database is.

3. Explain why a Database Management System is the appropriate tool for this project.

4. Explain why this Database Management System was developed as a Relational Database.

5. Describe what a Database Query is and discuss briefly the five queries that were developed for this project.

6. Describe what a Database Form is and discuss briefly the two forms that were developed for this project.

7. Describe what a Database Report is and discuss briefly the report that was developed for this project.

Attachment:- Database Management System.rar