CIS5100 ASSIGNMENT 1 QUESTION FILE SEMESTER 2, 2019 Database Management System (DBMS) & Decision Support System (DSS) Description Marks out of Wtg(%) Due date Assignment 1 100.00 20.00 11 September...

assessment


CIS5100 ASSIGNMENT 1 QUESTION FILESEMESTER 2, 2019 Database Management System (DBMS) & Decision Support System (DSS) Description Marks out of Wtg(%) Due date Assignment 1 100.00 20.00 11 September 2019 IMPORTANT INFORMATION Academic Integrity · This assignment must be all your own work. It is acceptable to discuss course content with others to improve your understanding and clarify requirements, but solutions to assignment questions must be done on your own. · You must not copy from anyone, including tutors and fellow students, nor allow others to copy your work. · All Assignments will be checked using collusion monitoring tools to ensure that each assignment is the original work of the submitting student. Assignments that do not adhere to this requirement will be deemed as being the result of collusion or plagiarism. This may lead to severe academic penalties as outlined in USQ Policy Library: Academic Integrity Policy and Procedure. It is your own responsibility to ensure the integrity of your work. Refer to the USQ Policy Library for more details: · Academic Integrity Policy · Academic Integrity Procedure Course Expectations · This assignment is quite complex, and exposes you to many different components in Microsoft Access (any version from Access 2007 to current), some or all of which might be very unfamiliar to you. The assignment description provides some explanation of how to use these components, but you are also expected to use available resources such as Online Help, the Beskeen et al text, the Practical Skills sections at the end of each Module in the Study Materials, the SAM online tutorial activities, the course discussion forums, appendices and practical related videos, as well as exploring and experimenting on your own. · It is important to make sure you have backups of all assignments you create. This is especially true if you store documents on a flash (thumb) drive. Flash drives can become corrupted or lost very easily. There are many options available (Dropbox, or Google Drive) and USQ students already have access to Google Drive via uConnect - UDrive. Another good idea is to Email your assignment file to yourself every-time you finish working on it, that way you have multiple copies of your progress in case you need to revert to a previous stage or are looking for a piece of information you have mistakenly deleted. · Note: Lost or corrupted assignment files is not grounds for an extension. It is your responsibility to ensure you have backup copies of your files. Late submission policy · USQ’s Assessment – Assignment (Late Submission) and Compassionate and Compelling Circumstances procedures relate to Extensions and Late Assignments. They can be found under the following links: · Assessment – Assignment (Late Submission) Procedure · Assessment of Compassionate and Compelling Circumstances Procedures · An Assignment submitted after the due date without an approved extension of time will be penalised. The penalty for late submission is a reduction by five percent (5%) of the maximum mark applicable for the assignment, for each University Business Day or part Business Day that the assignment is late. An assignment submitted more than ten (10) University Business Days after the due date will have a mark of zero recorded for that Assignment. Referencing requirements · References are required and the Harvard AGPS standard of referencing must be used, including in-text referencing. Ensure that your essay is fully referenced, including any references to the text book. Your essay should include a List of References using the Harvard AGPS standards. A useful link on referencing is from USQ’s Library site: · Any direct and paraphrased quotes must be referenced using Harvard AGPS style and listed in the List of References. Do not repeat verbatim large portions of information from other sources such as the text or video – you must put the ideas/information in your own words. Only use small direct quotes where possible. ASSIGNMENT 1 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 Shirley “Shirl” Lock-Holmes retired in 2015 after a long career in Investigative Journalism. To keep busy and to fulfil a lifelong dream, Dr Lock-Holmes started a “Steampunk” mail order business, specialising in importing “steam-punk” related products, called the “The Steam Age Emporium”. The business specialises in imported steam-punk merchandise such as ladies’ and gentlemen’s clothing, ladies’ and gentlemen’s boots, headwear, goggles and other assorted Steampunk related paraphernalia to customers via mail order around Australia. Steampunk merchandise has become very popular, especially with the release of various steampunk games (e.g. Bioshock II), graphic novels (e.g. League of Extraordinary Gentlemen), movies (e.g. Sherlock Holmes, Hugo, the Golden Compass, Sucker Punch and the Three Musketeers), and TV shows (e.g. Warehouse 13); and Dr Lock-Holmes’s business has experienced a rapid growth in their mail orders. When Dr Lock-Holmes first retired, the business bought a personal computer to help manage the books and finances. The Steam Age Emporium is located at Shop 7, 1 Edison Boulevard, Toowoomba (about 100 km west of Brisbane). Their ABN is 137 168 521. Dr Lock-Holmes can be contacted via phone on (07) 4633 4572; fax on (07) 4633 4574; or email on [email protected]. Dr Lock-Holmes is extremely impressed with our computing firm “Moriarty 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 Lock-Holmes is interested in is a Database Management System (DBMS) utilising Microsoft Access (any version from Access 2007 to current). 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 Lock-Holmes 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 Lock-Holmes 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 Estonia (EE) or Singapore (SG). 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 Lock-Holmes 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 Lock-Holmes 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. https://videohive.net/item/steam-age-trailer, Themilamax. 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 Lock-Holmes’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, Colour, Size, Freight Weight (Kg), Supplier ID, In Stock, On Order, Cost Price (EE), Cost Price (SG). · EE is the country code for Estonia (their currency is the Euro) · SG is the country code for Singapore (their currency is the Dollar) 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 Lock-Holmes 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 January and June 2019 to use as test data in four comma delimited text files, which can be found on your firm’s Intranet (CIS5100 Study Desk). Dr Hach G Wells, 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 All phases in this project must be developed with professionalism and user-friendliness in mind. 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_(part_a).accdb eg. genrichr_0050051005_cis5100_assign1_(part_a).accdb. Import the four sets of data from your firm’s Intranet (CIS5100 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 File wizard 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