Database Management System (DBMS) & Decision Support System (DSS) Assignment
ASSIGNMENT REQUIREMENTS -
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 Rikki Wagner retired in 2012 after a long career in Orchestral Music. To keep busy and to fulfil a lifelong dream, Dr Wagner started a Memorabilia mail order business, specialising in Wedding related products called the "Queen of Sheba Wedding Emporium". The business specialises in various types of imported wedding merchandise and bridal accessories such as bonbonnieres & favours, table centrepieces, men's suits, bride's wedding dresses and bridesmaid's dresses to customers via mail order around Australia.
Dr Wagner's business has experienced a rapid growth in their mail orders. When Dr Wagner first retired, the business bought a personal computer to help manage the books and finances.
Queen of Sheba Wedding Emporium is located at Shop 19, 13 Aphrodite Avenue, Toowoomba (about 100 km west of Brisbane). Their ABN is 126 279 410. Dr Wagner can be contacted via phone on (07) 4632 5683; fax on (07) 4632 5685; or email on [email protected].
Dr Wagner is extremely impressed with our computing firm "Mendelssohn 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 Wagner 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 Wagner 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 Wagner 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 Wagner 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 Wagner 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.
All phases in this project must be developed with professionalism and user-friendliness in mind.
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 Wagner's customer, item, supplier and order data for the business, which has been provided as follows:
Table Specifications
|
Table
|
tblCustomers
|
|
Fields:
|
Cust ID (Primary Key), Title, Family Name, Given Names, Date of Birth, Address, City, State, Postcode, Freight Distance (Km), Mobile Number, eMail Address, Mailing List.
|
|
Table:
|
tblItems
|
|
Fields:
|
Item ID (Primary Key), Type, Description, Size, Freight Weight (Kg), Supplier ID, In Stock, On Order, Cost Price (LU), Cost Price (AG)
|
|
Table:
|
tblSuppliers
|
|
Fields:
|
Supplier ID (Primary Key), Supplier Name, Address, City, State, Postcode, Phone Number, Recommended MarkUp
|
|
Table:
|
tblOrders
|
|
Fields:
|
Cust ID, Item ID, Order Date (Primary Key), Dispatch Date, Order Qty
Note: The primary key of the Orders table is a combined multiple-field key. It has three fields in one key.
|
Dr Wagner has provided to us a partial list of the business's customers, stock items, and suppliers and a partial list of her postal orders for the third quarter 2017 (Jul - Sept) to use as test data in four comma delimited text files which can be found on your firm's Intranet (CIS1000 Study Desk).
Mrs Georgie Handel, 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
Part B - Decision Support System
Mrs Georgie Handel, 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
Part C - Analytical Essay
Mrs Georgie Handel has asked you to develop an analytical essay (between 750 and 1000 words in length) to the client, Dr Wagner outlining the major issues that the Decision Support System's results have highlighted. Create a Word Document (using Word 2007 to current) and name it '[lastname] [initial] _ [student number] _ [course code] _ [assignment number'(eg. genrichr_0050051005_cis1000_assign2.docx).
Essays have a particular structure - An introduction, a body (where you write your answer in a number of paragraphs, usually one for each idea or topic) and a conclusion. The conclusion is where you sum up your 'argument'. Essays normally do not have headings.
The body of your essay should consider the following issues:
From Part A (Approx. 250 - 350 words):
- Define what a Database Management System is.
- Explain why a Database Management System is the appropriate tool for this project.
From Part B (Approx. 500 - 650 words):
Define what a Decision Support System is.
Explain why a Decision Support System is the appropriate tool for this project.
From the results of the Decision Support System results discuss the following (keeping in mind the information provided in the preamble on page 2):
- Which Mark-up Type would be most appropriate for the business? Why?
- What would be the impact on the business's profit if the plan to provide a discount to large orders was implemented? Why?
- Which Recommended Freight Type would be most appropriate for the business? Why?
- Which country would be most appropriate for the business to import from at the moment? Why?
The essay should be using a proportional font (eg. Arial, Times New Roman etc.), with a font size of 11 or 12, and be laid out using 1 ½ line spacing.
Attachment:- Assignment File.rar