Course: COMP1631 Information Analysis Contribution: 100% of course PDF file required ZIP file also required Greenwich Course Leader: Dr Mohammed Hassouna Due date: 25thApril 2016 This coursework will be marked anonymouslyYOU MUST NOT PUT ANY INDICATION OF YOUR IDENTITY IN YOUR SUBMISSION This coursework will be marked anonymouslyYOU MUST NOT PUT ANY INDICATION OF YOUR IDENTITY IN YOUR SUBMISSION This coursework should take an average student who is up-to-date with tutorial work approximately 50 hours This coursework should take an average student who is up-to-date with tutorial work approximately 50 hours Learning Outcomes:A. Explain and discuss the different types of information required by business functions.B. Explain and discuss the requirements of quality business information.C. Develop and produce a quality business information system. Learning Outcomes:A. Explain and discuss the different types of information required by business functions.B. Explain and discuss the requirements of quality business information.C. Develop and produce a quality business information system. Plagiarism is presenting somebody else s work as your own. It includes: copying information directly from the Web or books without referencing the material; submitting joint coursework as an individual effort; copying another student s coursework; stealing or buying coursework from someone else and submitting it as your own work. Suspected plagiarism will be investigated and if found to have occurred will be dealt with according to the procedures set down by the University. All material copied or amended from any source (e.g. internet, books) must be referenced correctly according to the reference style you are using. Your work will be submitted for electronic plagiarism checking. Any attempt to bypass our plagiarism detection systems will be treated as a severe Assessment Offence. Coursework Submission Requirements An electronic copy of your work for this coursework should be fully uploaded by midnight (local time) on the Deadline Date. The last version you upload will be the one that is marked. For this coursework you must submit a single Acrobat PDF document. In general, any text in the document must not be an image (ie must not be scanned) and would normally be generated from other documents (eg MS Office using Save As .. PDF ). There are limits on the file size. The current limits are displayed on the coursework submission page on the Intranet Make sure that any files you upload are virus-free and not protected by a password or corrupted otherwise they will be treated as null submissions. Comments on your work will be available from the Coursework page on the Intranet. The grade will be made available in the portal. You must NOT submit a paper copy of this coursework. Detailed Specification This is an individual coursework. You are required to develop an information system based on a computer sales and maintenance store of your choice. The store has a number of branches and there are a number of personnel working in each branch. Personnel can be reassigned to other branches to work. The store s services include computer sales, repair computer parts and installing and upgrading software and hardware. There are multiple owners of the store and they wish to have an information system that gives them up to date information about the current status of the business. The owners are often in different locations and therefore the system needs to be available from multiple remote locations. Furthermore, the owners have very little technical skill and therefore, the information presented to the owners must be in visual form (e.g. a dashboard). The store would like to use the system for analysing sales, cost and customer trends. They expect it to be constructed in a way such that a range of queries are available to them. The owner s most urgent need is to have information concerning the volume of sales of different products and services, the volume of sales in each location, and the performance of their sales personnel. You may assume the following for simplification purposes: There are only 6 months of trading data available. The store has 8 personnel. The store has 3 branches in the city. The store provides only 12 different products and services. Requirements: You must store the data in Database management systems (DBMS) (e.g. MySQL, MS SQL). You can assume that this database is automatically updated when a sales event occurs. Therefore, you only need to populate the database with static data. Populate the database with a small amount of data. The data should be meaningful but does not need to be extensive. The following sites may be useful for quickly generating data: http://www.databasetestdata.com/ http://www.generatedata.com/ Use pivot tables in Excel to visualise the data in the system. The connectivity will be handled using ODBC (Open Database Connectivity). You must supply at least five visualisations for your system. An example of a useful interactive visualisation might be the following: A chart that shows the overall sales per month in the six months of trading. Allow the owner to further select a specific product or services to see its sales over the six months. A chart that shows the overall sales per employee. State any assumptions that you make in modelling the business. You must produce a report with some design documents. Detailed report requirements are described under report section. Report Deliverables: Your final report must contain the following sections: A short introduction to explain the store requirements and assumptions Dimensional model schema of the conceptual design of the data warehouse (e.g. Star Schema, and Snow Flake Schema). Logical and Physical data models of the data warehouse The SQL code used to create your database tables The SQL code for your queries A screenshot of the data returned from one query that will be used for a visualisation A short description of the purpose of each pivot table and visualisation Screenshots of your visualisations A paragraph on what you would do differently if you were to do the coursework again A paragraph to answer the question: What do you understand by the term quality information ? In answering this refer to your system which you have developed? A self-assessment form and references Self-Assessment: You will find a self-assessment sheet attached to this coursework. You are to complete this sheet and submit it with your coursework. The grade that you award yourself is NOT the final grade that you will be awarded. Your coursework will still be graded by an academic member of staff. There are 5 marks allocated for accurate self-assessment. These marks are available for accurately assessing how well you completed the coursework, so be as objective as possible when completing the form. Use the following descriptions to guide your self-assessment Grading: Your grade will depend on how well you meet these criteria. A portion of the available marks will be given for above minimal specification features of your implementation. You will be graded according to the following criteria: 70-100% All requirements completed to an excellent standard 60-69% All requirements completed. However, there are a number of minor deficiencies in significant areas. 50-59% All requirements completed. However, significant improvements could be made in many areas. 40-49% All requirements completed. However, significant improvements could be made in all areas. 30-39% All requirements attempted but the overall level of understanding and performance is poor. 0-29% There are requirements missing or completed to a very inadequate standard which indicates a very poor or non-existent level of understanding. Demonstration: The demonstrations will be conducted after the end of term (within two weeks of the submission date). Details regarding the schedule for demonstrations will be provided later in the term. The demonstration is very important and should be treated as an important part of your assessment. Your self-assessment sheet will be annotated during the demonstration. Self-AssessmentSheet: (Placeatickinthebox thatyoudeem to be mostindicativeofthequalityofthework) % No Attempt to Very Poor Poor Fair Good Very Good Excellent Conceptual Database Design 10 Database Implementation 10 Queries 20 PivotTables 10 Visualisation 20 Report> report layout and organization 5 Report> Report srequirement 9 7 Report> Report srequirement 10 8 Data 5 Assessment 5 Total 100 Note:You must submit this self-assessment as part of the final report. The boxes in bold are for examiner use only. Grading Criteria (Marking Schema) Conceptual Database (10%) The schema should be a star schema (or snowflake schema if the student is very confident in terms of data modelling). If the model is NOT a star or snowflake schema deduct 5 marks. The data model should at least model the following: Time or some aspect of time, at minimum an attribute in the fact table, but for full marks there will be a separate dimension with a number of different ways of grouping time. Locations and some further attributes associated with locations. Personnel which are modelled so that one can find who provided services including useful attributes of personnel, in terms of services provided. Services, as a separate dimension which has useful attributes for each service in terms of reporting. For exemplary work the schema should have at least one further dimension that is correctly modelled. Data Model Implementation (10 %) Check to ensure that the data model is implemented as per the conceptual model. Check the data types of one or two of the tables to see if they are as per the student s specification. Check the table names to see if the naming is consistent. Deduct two marks if the naming of tables or attributes is not consistent. Excellent marks should be awarded only if there are no problems in the implementation. Queries (20%) Given that there are to be at least five visualisations, the student should have five queries that retrieve data for the visualisations. Check that the queries are correctly written and check that they do NOT use aggregate functions. Are the joins correct? There should be at least one query that joins three tables. The student should be able to explain what each query will be used in terms of visualisation later. Mark the quality of each query out of 4 marks. One query can be used in two visualisations. However, the student should have five visualisations that are populated from different queries. Treat any visualisations that are populated from the same query as extra visualisations. Pivot Tables (10%) They should be at least five pivot tables and they must summarise the data that is retrieved by each of the five queries. These must all work correctly to get 5 marks. Check that if an extra services gets added to the database that this is reflected in the pivot table when it is refreshed (1 mark). Check that at least one pivot table uses a filter (2 marks). Check that the pivot tables are controlled by slicers. Check that at least two of the pivot tables can be controlled by the same slicer (2 marks). Visualisations (20%) Did the student use at least two different types of visualisation in the four developed? Do the visualisations form a dashboard in a different sheet? For each visualisation check the following: Is there a title to indicate what the chart is for? Are the important axis clearly labelled? Can one dimension of the visualisation be controlled by a slicer (i.e. is it interactive)? Are they intuitive (i.e. do they make sense from a decision making point-of-view)? Is there enough data to properly show the visualisation? The visualisations must be exemplary to get excellent marks (5 Visualisations * 4 marks). Report> report layout and organization (5%) The report should be complete, accurate, and easy to read and logically structured to be awarded the full mark. Report> A paragraph on what you would do differently if you were to do the coursework again (7%) Write between 200 and 300 on what you would do differently if you were to do the coursework again. Be specific and justify any statements you make. This section should be easy to read and logically structured to be awarded the full mark. Report> A paragraph on what quality information and the quality of the developed system (8%) Write between 200 and 300 on what quality information is and the quality of the developed system. Be specific and justify any statements you make. Just saying things like my visualization is well designed without justifying the statement will not gain you full mark. This section should be easy to read and logically structured to be awarded the full mark. Data (5%) Is there the required number of personnel, branches, and products and services (2 marks)? Is there a substantial amount of sales data (more than 200 sales in the fact table that span 6 months) (3 marks)?