代写 Management Information Systems (115.107)

IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 1 of 12 IT Skills Assignment The IT Skills Assignment for 115.107 is marked out of 100 marks, distributed as noted below, and is worth 25% of your final grade. This assignment is due no later than 12.00pm on Friday, 22 April 2016. Deliverables Submit 2 files via the assignment upload link on Stream 1. An assignment report (documentation) using Microsoft Word The assignment report is a “paper trail” of what you have done while working on the assignment. It will include a contents page and many screen shots of your progress. A suggested template (IT_Skills_Assignment_Report_format guide_MIS_S1 2016.pdf) is available on Stream. Make sure that the report is well presented. To fully appreciate your work, markers need to be able to find your answers, and to understand everything you did. How to style and format your report is explained in Part 6 of this document. Marks are awarded for the correct use of word processing features and overall presentation. 2. A database file (the “TravelAgent” database) for parts 3 and 4 using Microsoft Access You are given a partially completed database, a Microsoft Access file (Library.accdb), which you will download from Stream. In parts 3 and 4 of this assignment you will add database tables, add data and create queries / reports on data. This takes place within this database. How to submit these two assignment files via Stream is covered in Module 1 exercises and further information is available at the end of this assignment. When assigning marks, markers will rely on both your report, and on the database. If a feature that should be both in the database and in the report is present in only one of them (for example, a database query present in the database, but not documented in the report), you will lose marks for that feature. Overview of this assignment Part 1 – Modelling a business scenario with an entity relationship (ER) model (30 marks) Part 2 – Designing a database with a relational model (20 marks) Part 3 – Database implementation (10 marks) Part 4 – Querying and reporting within the database (20 marks) Part 5 – Analysing and presenting information (13 marks) Part 6 – Finalizing the assignment report (7 marks) IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 2 of 12 How to approach this assignment The main aim of this assignment is to test the knowledge and skills you gain by completing software exercises in modules 1 to 6 of this paper (over weeks 1 to 6). In the exercises you will learn new concepts and develop hands-on technology skills. You are expected to be working on the software exercises and on the assignment more or less simultaneously: acquire skills by doing software exercises and immediately apply them by completing the relevant parts of the assignment. In addition, the assignment involves a small number of tasks not covered by the exercises. This offers you an experience in resolving IT problems by relying on using software help systems and the Internet as sources of technical information. You should read this entire assignment and understand what is required of you before beginning work on the deliverables. Also, as will become obvious, it is impossible to complete the assignment unless you carefully complete the exercises first. Each part of the assignment begins by telling you what exercises in each module you need to complete first. Take sufficient time (about 4 weeks) Skills acquisition takes time and practice. You need about 4 weeks to complete the exercises and the assignment. Needless to say, attempts to cram 4 weeks of work into one week are unlikely to result in a good outcome. Expect to do and re-do most of the deliverables It is highly likely that you will revisit most of the assignment deliverables more than once. For example, as you work on the rest of the assignment, you are likely to gain a better understanding of both the meaning of ER notation and of the exact requirements for the ER diagram you complete in Part 1. Therefore, you would go back to Part 1 and adjust the ER diagram to reflect your better level of understanding. Expect to go over several versions of each task you are asked to complete. Do not expect to "get everything right" on the first attempt. IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 3 of 12 Part 1: Modelling a business scenario with an Entity Relationship (ER) model (30 marks) Complete Modules 2 and 3 exercises before you start working on this part of the assignment. Real Time Shopping – An online shop database Real Time Shopping is an online store to sell mobile phones. The shop does not allow pick-up and delivery is by post only. Currently Real Time Shopping I operating within New Zealand but has a plan to extend its operation globally. To purchase an item, the customers are required to register on the website and provide their name, address, contact number and email address. However, a customer does not need to supply their credit card details until they make the first purchase. Every customer needs to register and become a verified user for the online store. Every customer is verified by sending a letter to their postal address. The letter contains a secret code that the recipient user need to enter into the real Time Shop website for the respective user to be verified. Real Time Shopping purchases mobile phones from different vendors (suppliers) and thus is required to keep a record of the suppliers including purchase history with each supplier it has relationship with. The online shop has three warehouses in Auckland, Palmerston North and Wellington. When a customer orders an item, the item is supplied from the nearest warehouse to customer’s address. Apart from maintaining records for mobile phones, suppliers and customers, Real Time Shopping also keeps record of its employees. A manager for each warehouse is in charge of the warehouse. An IT technician looks after the IT infrastructure and IT support personnel deals with the online orders. All transactions are online and no cash is handled at any of the sites of real Time Shopping. Tasks Create a document file using MS Word as your assignment report (a reminder: IT_Skills_Assignment_Report_format guide_MIS_S1 2016.pdf is a template of this file that is available on Stream). 1. For Part 1 of this assignment, design the ER diagram describing entities and relationships in an information system supporting the operation of Real Time Shopping database. Draw this diagram within the report, using the appropriate features as defined in part 6, “Finalizing the assignment report” and as practiced in the exercises in Module 2. 2. Create the table of entities and attributes that help to define your ER diagram. Notes   When naming entity types, use singular, rather than plural. For example, in a customer relationship management (CRM) system there can be any number of customers, but the name of the entity type would be “Customer”, rather than “Customers”.   Feel free to make assumptions, in particular, to add attributes. But, do not make it too complex. Read the scenario requirements to identify the minimum number of attributes. Use your understanding of “how” the attributes may be used in the system to identify their structure. For example, “address” is rarely a well-structured attribute. To be of practical use, it is often deconstructed into at least three attributes: Street, Suburb, and City. IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 4 of 12   Different students may make different assumptions, and arrive at quite different solutions. There is no single "correct answer". Please document your assumptions, if you made any.   Reading ahead to Part 3 of this assignment, especially figure 1 and table 1, will also help you to understand how to document your relational model. Part 1 deliverables Include the following items in your assignment report:   ER model for Real Time Shopping   Table of entities, attributes and key attributes. Part 2: Designing a database with a relational model (20 marks) Complete Module 4 exercises before you start working on this part of the assignment. In this part of the assignment you design the database for Real Time Shopping database. Document the database design in your assignment report including tables, columns, primary keys, and foreign keys, presented in tabular form in the assignment report, as in Module 4 exercises. When naming the tables and their columns, you must follow the naming convention rules (see below). Tasks Convert your ER model into a relational data model. Consider which tables and table columns you need and document them in your assignment report. You may use graphical notation for relational database design (like the one you may fine in part 3 of this assignment), or list the tables and keys in a table in text format. Note that sometimes you may need more tables than there are entity types in the ER diagram – for example, you need to consider any many-to-many relationships in your ER diagram. Notes   Add information about primary and foreign keys in your report: o In the report, for each relation / table, list its columns and indicate which column (or columns) forms its primary key. o For each of the foreign keys (if there are any) state the table to which it points. Use Module 4 exercises as a guide how to word this.   In this assignment, we expect you to comply with the following naming conventions: o A table representing an entity type should be named the same as the entity type (with the omission of any spaces) + Tbl suffix. For example, a table representing a Customer entity type in a customer relationship management system should be named CustomerTbl. o A table representing a many-to-many relationship type should be named the same as the relationship type (with the omission of any spaces) + Tbl suffix. For example, a table representing an “is located at” many-to-many relationship type should be named IsLocatedAtTbl. o Table columns corresponding to attributes of an entity type should be named the same as the attributes they correspond to (with the omission of any spaces). o Table columns added to represent relationship types become components of foreign keys. Whenever possible, such a column, or foreign key, should be named exactly the same as the primary key column it matches. IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 5 of 12   You may find that reading ahead to Part 3 of this assignment, especially table 2, will also help you to understand how to document your database design. Part 2 deliverable: The relational database design presented in tabular form in your assignment report: include tables, columns, primary keys, and foreign keys. Part 3: Database implementation (10 marks) Complete Module 4 exercises before you start working on this part of the assignment. Consider the following description of a business domain. UniTravel – An online travel agent UniTravel is an online travel agent operating in Auckland, New Zealand. Through its website, customers can search different flight options for their chosen destination(s) and subsequently can book flights. UniTravel requires its customers to be registered on the system. For any chosen destination, there may be several airliners offering flight and a customer may choose their preferred airline. There is option for the customers to cancel a purchased ticket. There is a $25 penalty for each cancellation. With the aim of designing a database to manage the operations of the library, a business analyst created the following ER model: Figure 1: Entity Relationship Model for UniTravel Based on the ER model a relational data model has been created as the design of the UniTravel database. This is presented in Figure 2 below: IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 6 of 12 Figure 2: Relational Database Design for UniTravel By carefully examining the relational model above, you should be able to find the table names and their fields including primary and foreign key. For example, in Customer table, customerID is the primary key. This table has a one-to-many relationship with Booking table. In Booking table, you may find a field customerID which is a foreign key. In the same way, as another example, bookingID is a foreign key in Cancellation table. Open the “TravelAgent” database provided with the assignment on Stream (TravelAgent.accdb). Most of the tables and corresponding data have been implemented in this file. Complete the database implementation according to the provided database design (relational model) in the desktop database management system MS Access. Tasks 1. Create one new table within the given database – FlightTbl, and set up its columns or fields and primary keys. 2. Add more test data to the database. IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 7 of 12 a. Populate some data into FlightTbl. You are required to input the following data into the table: FlightTbl flightID flightNumber originPort destinationPort airlineID FL001 CZ121 Auckland Dunedin NZ001 FL002 CZ141 Dunedin Christchurch NZ002 FL003 CZ442 Wellington Auckland NZ001 FL004 CZ114 Stewart Island Cape Reinga NZ003 b. In the table FlightTbl, Add a new flight information with ‘Palmerston North’ as originPort and ‘Otago’ as destinationPort. Choose value for other fields in consistent with other existing records in the table. c. Add yourself to the database as a registered customer, who o Has booked 3 flights (you may choose your preferred date). o Has cancelled 3 flights (you may choose your preferred date). o You MUST use your real name, but do NOT provide your actual phone number. Put name of the city only in Address field. Do NOT make any other changes on the database data apart from those required as above. Choose primary key that is consistent with the existing ones in terms of convention. 3. Within your database, create a relationships diagram based on the given database design (see table 2). Ensure the correct use of foreign and primary keys and enforce referential integrity. Include (paste) a screenshot of the relationships diagram in your assignment report. Notes   Setting up columns or fields in database tables When selecting data types for columns, you can use “Text” in most cases. You will note that in the database provided with the assignment “date/time” is used for due dates and “Currency” for dollar amounts, while the rest of the columns are “Text” data types.   Setting up primary and foreign keys for database tables Make sure that you set up primary and foreign keys in the database, as shown in the exercises. To set up the foreign keys, you need to create a relationships diagram. To set up a primary key consisting of more than one column, in the table design view hold down the Ctrl key as you click on each column / field to include it.   Adding test data Fill the table columns you created with realistic test data, but do not change the data that are provided. When adding new data, make sure that any foreign key value corresponds to the appropriate primary key value. For example, a booking can be done only by a registered customer and a cancellation can be done for a booked flight only. If you set up foreign keys in the database correctly, the software will refuse to accept values that do not comply with this constraint. The above rules are general guidelines for database implementation; you may not need to use all of them when completing the tasks in this part of the assignment. IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 8 of 12 Part 3 deliverables:   Complete the database implementation tasks within the TravelAgent database.   In your assignment report, include (paste) the following screen shots from the database you implemented o The design view of FlightTbl o The datasheet view of FlightTbl o The relationships diagram you created within the database IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 9 of 12 Part 4: Querying and reporting within the database (20 marks) Complete Module 5 exercises before you start working on this part of the assignment. Tasks Query 1:  CustomersWhoCancelled Create a QBE (query by example – using Query Designer to create) or an SQL (structured query language) query to list all customers who cancelled at least one flight booking. Only include customers’ first names, last name and cancellation date  in  your  query  output.  This  query  should  be  saved  as “CustomersWhoCancelled” within the database. Query 2:  MyBooking Create a QBE or an SQL query to list the bookings made by you. Only include your first name, last name, booking date and seat number in your query output. This query should be saved as “MyBooking” within the database. Query 3:  MyAirlines Create a QBE or an SQL query to list all the airlines your booked flights are associated with. Only include your first name, last name, flight number and the airline’s name for that flight in your query output. This query should be saved as “MyAirlines” within the database. Query 4: BookingInFebruary Create a QBE or an SQL query to list all the bookings that are made in the month of February. Only include customers’ first name, last name, booking date and flight number in your query output. This query should be saved as “BookingInFebruary” within the database. Query 5: CancelledBookingUntilFebruary Create a QBE or an SQL query to list all the bookings that are cancelled until the end of the month of February. Only include customers’ first name, last name, booking date, cancellation date, penalty and flight number in your query output. This query should be saved as “CancelledBookingUntilFebruary” within the database. Database

代写 Management Information Systems (115.107)  
  Report: CancelledBookingUntilFebruaryReport Create a database report (Access or OO Base) displaying the results of Query 5 (CancelledBookingUntilFebruary) above. The report title (“Bookings Cancelled by February”) and your name should appear in the report header as the author of the report. This report should be saved as “CancelledBookingUntilFebruaryReport” within the database. Part 4 deliverables:   Complete the queries and the database report within the “TravelAgent” database.   Document these queries and the database report in your assignment report. For each query include (paste) before and after screenshots, that is, screenshots of the query design (QBE or SQL), and another of the output from running the query. Also, include (paste) a screenshot of the database report (‘Database report’ task above). IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 10 of 12 Part 5: Analysing and presenting information (13 marks) Complete Module 6 exercises before you start working on this part of the assignment. Tasks 1. Copy or export data from Query 5: “CancelledBookingUntilFebruary” that you created, in Part 4, into a spreadsheet in MS Excel. Each task below (2, 3, and 4) is independent and based on the exported data in task 1 above. The starting point for each is this original copied data. It may be helpful to copy the original data into different worksheets. The exception is task 5 (chart) which required data from task 4 (pivot table). 2. Apply a Filter and customise it to show only cancellation done in February. 3. Use Conditional Formatting to show booking cancelled after 15 th February as bold, underlined, and in yellow background. 4. Create a Pivot Table to show total cancellation penalty for each customer who have cancelled their booking. 5. Plot the data in the pivot table as a bar chart. The bar chart should show total penalty incurred by each customer. Part 5 deliverable:   Although you must use spreadsheet software to complete this part, you are not required to submit the file.   Include the screenshots of the copied spreadsheet data, the filtered data, the formatted data, the pivot table and the chart in your assignment report. IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 11 of 12 Part 6: Finalizing the assignment report (7 marks) and submitting Complete Module 1 and 2 exercises before you start working on this part of the assignment. Tasks 1. Consistently style your assignment report using the style features of the software, which is introduced in Module 2 exercises. 2. Assign numbers and captions to all tables and figures in your report (e.g., “Figure 1: Entity relationship model for UniTravel System”). Place table captions immediately above tables, and figure captions immediately below figures. 3. At the beginning of the report, create a table of contents, a list of all tables and a list of all figures by using the relevant software features introduced in the Module 2 exercises. Submit the assignment Submit the two files – assignment report and database file – to the IT Skills Assignment Submission link on Stream. Do this no later than 12:00pm on Friday, 22 April 2016. 115.107 does not use the "submit for marking" button. This allows students to change and update their assignment files up to the due date. If both files are visible on your screen as being in your assignment folder, then they are considered submitted and ready for marking. If for some reason you cannot submit your assignment using Stream, please contact the paper coordinator before the due date to arrange an alternative way to submit your assignment. Your inability to submit using Stream does not give you an automatic extension. Plan ahead so if there is a problem you can still submit the assignment on time. Otherwise, late penalties will apply. If you submit your assignment files incorrectly, so that the paper coordinator may need to ask you to resubmit, the re-submission date will be counted as the actual submission date, and apply the late submission penalty. It is recommended that when using the assignment upload system you employ the feedback facilities provided by the system. In particular, you should:   Download from the online submission system the files you submitted, and verify that they are correct and not corrupted.   Before submission, and when verifying the submitted files, make use of the checklist provided with the assignment. Retain a copy of the assignment as a back-up. Late submission and extensions Extensions without penalty are granted in exceptional circumstances such as a personal illness or family bereavement. It is understand that unforeseen circumstances might occur and we try to accommodate these. In almost all cases, documentary evidence of your circumstances (e.g. doctor’s or counsellor’s certificate) will be required. Please contact the paper coordinator as soon as possible to negotiate a revised due date. All extensions will take into consideration the fact that you should have been working on the assignment during the previous 4 weeks; so most extensions will be for a very limited time. Assignments submitted after the due date will go to the end of the marking queue. The later your assignment is submitted, the longer it will take to return it to you. Assignments arriving after the due date (unless an extension has been negotiated) are subject to a penalty. IT Skills Assignment  Management Information Systems (115.107)  S1 2016 Page 12 of 12   1-3 days late: deduction of 5 marks from your final mark in this paper (which corresponds to 20% of credits available for the assignment).   4-7 days late: deduction of 10 marks from your final mark in this paper (which corresponds to 40% of credits available for the assignment).   More than 7 days late: 0 marks for this assignment A high quality assignment that would have received the full score of 25 marks if submitted by the deadline would receive a score of 20 if submitted 2 days late, and it would receive only 15 marks if submitted 6 days late. If your assignment arrives after the cut-off date -- exactly one week after the submission deadline -- the assignment will not be marked and a score of zero marks will be recorded.

代写 Management Information Systems (115.107)