Reference no: EM132350182

LEARNING OBJECTIVES

Applicable course objective:
• apply information systems concepts to identify and resolve problems within a business context
• apply information systems skills to develop practical solutions within a business context
• communicate information systems concepts to both technical and non-technical audiences within a business context.
Applicable graduate qualities and skills gained from this assessment instrument:
• Problem Solving
• Communications

PREAMBLE

Dr Drew Little retired in 2012 after a long career in Orchestral Music. To keep busy and to fulfil a lifelong dream, Dr Little started a mail order Pet Supply business, specialising in Pet related products called the "Bonsai Vet".The business specialises in various types of imported pet merchandise and accessoriessuch as outdoor dog supplies, pet memorials, dog gifts, dog travel supplies, dog beds and dog clothing to customers via mail order around Australia.

Dr Little's business has experienced a rapid growth in their mail orders. When Dr Little first retired, the business bought a personal computer to help manage the books and finances.

Bonsai Vet is located at Shop 1, 9Rottweiler Avenue, Toowoomba (about 100 km west of Brisbane). Their ABN is 126 279 410. Dr Little can be contacted via phone on (07) 4632 5683; fax on (07) 4632 5685;

Dr Little is extremely impressed with our computing firm "Dachshund Technologies" and the business has contracted our firm to assist them in setting up their various computer-based information systems.

Part A: The first computer-based information system that Dr Little is interested in is a Database Management System (DBMS) utilising Microsoft Access (any version from Access 2007 to current). The DBMS will to store all of 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 Little to run a number of 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.

Part B: The next computer-based information system that the business is interested in is a Decision Support System (DSS) utilising Microsoft Excel(any version from Excel 2007 to current). The DSS will analyse sales trends for the business to determine future courses of action for the business.

Dr Little would like the information in the Database Management System (DBMS) (Assignment 1 Part A) imported into Microsoft Excel (any version from Excel 2007 to current) so that the information can be summarised as a report and some future analysis of sales trends performed. The suppliers used by the business, source items from either Luxembourg (LU) or Antigua and Barbuda (AG). Then 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 Little has noted that a number of the business's competitors are providing a discount to customers who place large orders. The business would like to see what affect adopting a similar policy would have on the business. The business has also noted a growing number of online businesses are starting to provide free freight delivery as a way of encouraging more online business; the business would like to run a number of scenarios based on potential Mark-up and Freight options to ascertain the best combination for the business if they were to adopt this policy too.

Part C: Dr Little also has a number of questions relating to the two computer-based information systems in Part A & Part B that he would like you to answer, prepare an Essay to answer these questions he has listed.

Assignment 1: Part A (Database Management System)
Your firm's Systems Analyst has developed a database structure that has been determined to be suitable to create a database to store Dr Little's customer, item, supplier and order data for the business, which has been provided as follows:

Dr Little 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 between June and August 2018 to use as test data in four comma delimited text files which can be found on your firm's Intranet (CIS1000 Study Desk).

Dr Lisa Chimes, your manager, has set up the following tasks for you to complete for this phase of the project:
1. Create a Database and import the four (4) Text files into four (4) Tables
2. Modify Table Design
3. Set Primary Keys
4. Create Relationships between the Tables
5. Edit Customers Table
6. Create Data and Update Queries
7. Create an Invoice Report
8. Create an End of Project Presentation to the Client
Task 1: Create and Import

Create a new blank Access Database (using any version from Access 2007 to current) and name it[lastname] [initial] _ [student number] _ [course code] _ assign1.accdb(eg. genrichr_0050051005_cis5100_assign1.accdb).
Import the four sets of data from your firm's Intranet (CIS1000 Study Desk).

The easiest and quickest way to import data into Access is by using the Import Text Wizard. The following steps must be followed carefully to ensure the data is imported correctly:
Importing Tab Delimited Text files into Access (any version from Access 2007 to current) using the Wizard
1) On the External Data tab, in the Import & Link group, click the Text File data source icon.
2) In the Get External Data - Text Filewizard popup, browse to find the ‘Assignment01 Text File Customers.txt' text file and select the Import the source data into a new table in the current database option - then click OK.
3) Select the Delimited - Characters such as commas or tab separate each field option on the first page of the wizard - then click Next.
4) Select the Tab option on the second page of the wizard and turn on the First Row Contains Field Namescheck box - then click Next.
5) Set the following for each field on the third page of the wizard:
• Field Name - keep the field name as it appears when imported
Note: Changing Field Names rather than using Captions will result in loss of marks.
• Data Type - giving each an appropriate data type (students will be marked on their logical choice of data types, based on the sample data provided).
Note: Numbers that will not be used for calculations should be set as Text. Numbers such as Freight Weight and Order Qty must be carefully and logically set to avoid loss of data (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).
See the Beskeen et al textbook Access Chapter 7 for more details on setting appropriate Data Types, particularly Chapter 7: Table 5-3: Common Number field properties for logical Data Types.
• When you are sure that each field has the appropriate data type, click Next.
6) Select No Primary Key option on the forth page of the wizard - these will be set in Task 2 - then click Next.
7) 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 Typefrom the following:
o Text, Number, Date/Time, Currency, Yes/No, Hyperlink.
• Add appropriate Field Descriptionsto 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:
o Field Size- set appropriate logical fields sizes for all 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)
o Caption- set an appropriate English caption for all fields where the field name is not in English (eg. Customer ID for CustID)
o Format- set an appropriate format for all: Yes/No and Date data fields; Text data fields that should be stored in Upper-Case (State); Hyperlinks data fields that should be stored in lower-case (EmailAddress); and numbers that contain currency or percentages
Note:RO is the country code for Romania - should be formatted in Euros; LR is the country code for Liberia- should be formatted in Dollars.
o Decimals- set an appropriate number of decimal places for any single or double numbers
o Default Value- set an appropriate default value for all Yes / No data Fields
o Required- set any data Fields that must contain data to Required and leave any Fields where data is optional as unchecked.
o Validation Ruleand Validation Text- include appropriate Validation Rules 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:
• 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: Edit Customers Table
Edit Customer ID 101 in the Customers Table as per the following instructions in Microsoft Access using Table Datasheet View:
1. Change the Title, Given Names and Family Name to your own details.
2. Change the Address, City, State and Postcode to your own details.
3. Using whereislook up the distance between your current address and Toowoomba QLD 4350 - change the Freight Distance to this new value.
4. Change the Mobile Number to your student ID eg. 0061 099999
» Do not use the u1099999 version.
5. Change the Email Address to your own details.
6. Select that you would like to be on the Business's Mail List.

Task 6: Create Data and Update Queries
Create the following Three (3) 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).
» You must create the Queries using Access Design View, if you create the Queries using SQL this will result in ZERO marks for the Query.
» Query names must conform to the Leszynski Naming Convention (LNC), as per the Naming Convention for Access Database Objects document in the Practical Appendixes found on the Study

Schedule and Module Materials - including the Query Number and Two Meaningful Words to describe the query eg. qry1XxxxxxXxxxxx.
» Not all queries will require the inclusion of all four tables; you must decide which tables are appropriate for the generation of each query's output. Only tables needed should be included in the query design as extraneous tables could cause problems with the query results

• Query 1: Import Duty Liberia (LR) Cost Price Increase:
A new Australian import duty of 1.75% has been added for all products being imported from Africa includingLiberia (LR) and Dr Little has asked you to develop a query that will update the Cost Price from Liberiaof 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 Querythat will raise the Liberia (LR) Cost Price for all items on orderthat are not in stockby 1.75%. 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 (LR) in tblItemsby 1.75%for all items currently not in stockbut 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 2: Late Dispatched Orders Form Query:
Dr Little 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:
i) Living in New South Wales, have had items dispatched late (between 15thAugust and today) and where the item was ordered after the 1stAugust and the item is in stock
Or
ii) Living inVictoria, have had items dispatched late (between 20thAugust and today) and where the item was ordered after the 5thAugust and the item is in stock
Or
iii) Living in Queensland, have had items dispatched late (between 25th August and today) and where the item was ordered after the 10th August 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 Item Type, Item Description, Freight Weight and Supplier Name), OrderDate, Dispatch Date and OrderQty for those customers.

• Query 3: Invoice Query with Calculated Fields:
Dr Little 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 either Cat Toys or Dog Toys, but only those dresses that are not made of Nylon, and where the item was in stock. This query should also calculate the selling price (based on goods purchased from the Romania) 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 Item Type, Item Description, Item Colour, Item Size, Freight Weight and Supplier Name), OrderDate, and OrderQty, including two calculated Fields - Selling Price (AU) and Order Total (AU).

Task 7: Create an Invoice Report
Dr Littlehas provided you with thefollowing sample Invoice. The client requires you to develop a similar invoice for their business.

Assignment: Part B (Decision Support System)

Dr Lisa Chimes, your manager, has set up the following tasks for you to complete for this phase of the project:

1. Create a Spreadsheet and import the four (4) Access Tables into four (4) Worksheets
2. Data Validation Check
3. Create an Index Worksheet
4. Create a Data Input Worksheet Template for later use
5. Create a Calculations Worksheet Template for later use
6. Create Name Ranges for the Customers, Items, and Suppliers data
7. Create a Report Worksheet and set up the column headings
8. Modify the Report Worksheet by Cell Referencing all the Orders Table data
9. Modify the Report Worksheet by using VLOOKUP to get Customer, Item and Supplier data
10. Modify the Data Input Worksheet to include extra data needed for later tasks
11. Modify the Report Worksheet by using a Nested IF to calculate Cost Price (AU)
12. Modify the Report Worksheet by using a Nested IF to calculate Selling Price
13. Modify the Report Worksheet by using a Nested IF to calculate Freight Cost
14. Modify the Report Worksheet by using a AND / IF to calculate Item Discount
15. Modify the Report Worksheet by using simple formulas to find Purchases and Sales, and Modify the Report Worksheet by using a formula to calculate the Order Discount
16. Modify the Data Input Worksheet to include extra data needed for later tasks
17. Modify the Calculations Worksheet by using simple formulas
18. Modify the Data Input Worksheet by Cell Referencing all the Calculations data
19. Create eight (8) Scenarios on the Data Input Worksheet
20. Create a Scenario Summary of the eight (8) Scenarios
21. Create a Documentation Worksheet
22. Create an Analytical Essay to describe the findings made using the Spreadsheet

Attachment:- Database Management System.rar