辅导案例-INST0001
1 Assignment Title Coursework Component/Module INST0001 Database systems Assignment Code AS01-INST0001 Set by D. Romano Moderated by L. Dickens Learning outcomes to be assessed: • Describe in brief how a business operates • Utilise standard graphical notations for communicating the form of the database to the customer and the programmer in a standard manner • Analyse the requirements for business database, create and query it utilising the SQL language in a database management system (this year XAMMP has been explained and MySQL) • Elicit the database specifications from brief documents • Perform the database conceptual requirements analysis • Describe the logical diagrammatic model of the data and how to normalise it Submission requirements: Brief Assignment Description This assignment requires students to work in pairs towards describing a business case study, and its database needs considering both the conceptual and normalised logical model of the data. The student must also demonstrate the ability to query the database and the data resulting from such queries. The complete coursework will be produced in the form of a report that represents 40% of the final mark. Conditions Assignment Submission must be completed through a designated Moodle Area: https://moodle.ucl.ac.uk/mod/turnitintooltwo/view.php?id=1633863 Make sure the course code (INST0001) and your name and student number, and the name and student number of the other person you have worked with, are clearly marked below the report tile. Only one student will submit the coursework for the pair including his/her individual reflection, the other student will submit only his/her individual refection Marking Criteria and Weighting Rubric These are described in Appendix A below. The assignment is worth 40 % of the overall assessment for this course This assignment must be completed: Report (in pairs) Date work set (provisional): 07/02/2020 Date and time due in (provisional): 26th March 2020. Extended now due on 9th of April 2020 Standard lateness penalty will apply. The target date for the return of marked work and full feedback (provisional): Marked work is returned on: 24/04/2019 Extended 16th of May 2020 (tentatively within 4 weeks, according to DIS policy) A detailed description of the assignment is available at: https://moodle-1819.ucl.ac.uk/course/view.php?id=5253 INST0001 Coursework Assignment Brief UCL DEPARTMENT OF INFORMATION STUDIES 2 Detailed Assignment Description You are designing and implementing a payroll system for a company of your choice. The employees in the company are paid on hourly base, overtime is paid 1.5 of the basic rates. The database must have the followings elements: • A manner for the payroll assistant to login to the online system. • The list of employees and their attributes (e.g. designation, name, surname, job title, years in the company, hourly pay, national insurance number, contact details, etc.). • Payment history (e.g. payment received in the first week of June) REQUIREMENTS: The database should contain at least 15 employees. There should be 3 different job types with a different hourly pay. The database should have at least 6 weeks history, the hours worked in a day for each day worked). Some of the employees must do overtime. For each job title, there might be several grades, which lead to a different hourly rate. For example, an assistant Grade 6.1 has an hourly are of £14.50, while an assistant Grade 6.2 has an hourly rate of £16.10. In pairs you must to produce a Written Report (1500 words max) the minimum font size to be used in the report is ARIAL point 11. All screen shoots must be clearly legible. • The report’s 1st page should contain the report title, the authors (name, surname & student ID, of both students contributing). This is not counted in the word count. • The 2nd page should have content list (including list of figures). This is not counted in the word count. • On the 3rd page please include a table showing each authors breakdown of effort (as percentage) and list the nature of their participation (e.g. what each author has contributed to the coursework considering each). This is not counted in the word count. An example of the effort table is shown below: Name and Surname A. B. C. D. E. Signature Mary Rossi 50% 50% 50% 70% 50% Mary Rossi John Smith 50% 50% 50% 30% 50% JohnSmith In addition, each student must write an individual report (see point E. below). A, B, C, D, E, elements of the Group Report are as follows (the word count starts now): A. (10% of mark) Customer brief – this is an essential part of both the project development and the contract with the client as it helps guarantee that the work for a project will be done according to specific guidelines and expectations of the client. If you cite any source to write this section, please provide it in the reference list utilizing APA style. Your brief should include: o An Overview of what the payroll system is expected to do in order to produce the data required by the mySQL queries below (a short paragraph of max 500 words) o A Concept Table (who, when, where, why, how) o A Conceptual Model of the whole payroll system and its attributes (e.g. main tables and columns needed in the database) displayed visually utilizing two different techniques: § Axis Technique § Conceptual ER model o A Dictionary of all the key-works utilised by the system and their meanings (e.g. hourly pay is the amount an employee gets paid per hour). B. (25% of mark) Normalization - show with tables, and explain in writing (step by step), how the Conceptual ER (CER) model you have produced is being normalised. In particular in the report you need to present: o The unnormalized model (this is the CER model, or one long list of attributes, where you have ensured it has all the columns shown in the conceptual model, but it has no keys) o Logical ER model (1NF) and explain how from CER you moved to 1NF o Logical ER model (2NF) and explain how from 1NF you moved to 2NF o Logical ER model (3NF) and explain how from 2NF you moved to 3NF 3 C. (2% of mark) The database - Build the database in XAMMP (in the report you should provide both the screenshots of the database creation and the code - see note 2 below) o (1%) Create the database o (1%) Create all the tables, with columns and keys D. (60% of mark) The queries - Code the following queries in XAMMPS. In the report provide i) the code as text in the report, ii) the screen-shots of both the query’ code and its iii) results/print out – see note 2 below): o Produce a list of employees that have worked more than 35 hours in the current week (note the DB should still have some that have worked less than 25 hors) o Produce a list of employees that live in specific UK city that has a particular job designation (note there should be still employees in the DB that live at a different city and have a different job title) o List the employees of the company that on average have been paid the most in the last 4 weeks. (note the DB should contain more than 4 weeks data). o Produce a payslip for the past 4 weeks for one of the employees that has done overtime in at least one week (see template provided, see figure 1. The payslip number to be constructed as a unique key using date and job and grade. Figure 1. Sample payslip, note payslip number is not shown as we want you to work it out. E. (3% of mark) Composition of report & Individual reflection o Include a table of content and figures o All screen-shot and graphics must be readable, of good image quality and appropriate for the size of the printed page o Ensure spelling and grammar is correct throughout the report o The individual reflection (suggested 300 – not in the word count) should highlight i) what was your contribution to the group project, and ii) How the collaboration was conducted. Only one student will submit the coursework for the pair including his/her individual reflection, the other student will submit only his/her individual refection NOTE: We MUST provide the following information from XAMMP as a proof that the database has been built. This information I. and II. should be included as an Appendix to the report. Information III. and IV. should be included in the solution of D. The information that MUST be included is the following: I. Data dictionary – saved as .pdf file and include it in the report appendix. This information it can be accessed from the main page of XAMMPS. After the database is built, a link will appear called Data dictionary, which needs to be clicked. II. The Designer – save the screen-shot as a .pdf file or add it as picture within the report. 4 The Designer can be found under the top navigation bar (or under the More tab in some versions). III. The SQL code – save the screen-shot as a .pdf file or add it as picture within the report. This is the code produced in XAMMP, which can be found under the SQL tab in the top navigation bar. Simple code is automatically generated when using the interfaces. However, we required that you show the code that has been created by yourself, such as for example the code to generate the invoice. Please remember to open up the SQL windows to type the code and enlarge the window in a manner that includes the whole code clearly before screen shoot it. For example, any table containing a composite key should be created with code, and the code screen-shot. IV. A screen shot of the output created by the code - save the screen-shot as a .pdf file or add it as picture within the report. This will be generated when running the code pressing the button ‘Go’ at the bottom right of the SQL tab screen. The code run is shown at the top pf the screen and should also be included. NOTE 2: All the images used on the report should be of sufficient quality for the text depicted to be read clearly in the printed report. Utilise the same style throughout, label the figures and provide a brief explanation of what the picture shows. FURTHER NOTES: v A penalty reduction by 10 marks will be applied for every additional 500 words used. Penalties are applied for late submissions (refer to UCL guidelines at https://www.ucl.ac.uk/academic- manual/chapters/chapter-4-assessment-framework-taught-programmes/section-3-module-assessment#3.12). v The Marking Scheme in Appendix A provides further details on how your work will be marked and can be used to guide your work. Please study the marking scheme carefully and use it as a checklist before submitting the work. v Students that do not work in pairs as directed will be penalised unless a special exemption has been granted. v You may consult the lecturer or ask for further advice about your coursework by posting a query on the Moodle 5 INST001- Database Systems APPENDIX A – MARKING SCHEME REPORT Criteria Inadequate (up to 39) Poor/Missing (40-49) Adequate (50-59) Good (60-69) Very Good (70-79) Excellent (80+) COMPOSITION OF REPORT: Is the report readable, well-structured with no grammatical errors? There is little or no evidence of thought about structure, and the document's readability and grammar are severely impaired There is a structure; however, the document is still hard to read and/or has severe grammatical errors There is some evidence of thought towards structure, but poor choices impair readability and grammar The report is structured, perhaps using sub-headings or paragraphs to direct the reader through the content, has good use of language, and figures are readable The report is well- structured, probably using sub-headings or paragraphs to lead the reader through the material, has good use of language, and the figures are legible. The content is well explained Excellent writing and style, topics excellently explained visualised and structured COMPLETENESS and CORRECTNESS OF EACH ELEMENT: Does the report contain all the items required in the coursework description? Have these items been well executed? (e.g. A,B,C,D, E) Most items are missing or are not well executed. The majority of items requested are missing or incomplete, or they have a poor execution The majority or all items are present; however, they might not be well executed or described All items requested are present and well executed and described All items are present, very well executed, very well described The report goes well above the task and guidelines provided PAIRS WORK: Does the report explain how the work was apportioned? (e.g. collaboration table) The report does not describe how the work was allocated The report explains adequately how the work was distributed, however, the collaboration declaration is missing or both members of the pair have not signed the table The report describes how work was apportioned. The collaboration declaration is present and signed by all group members The student has explained how work was distributed. The student has evaluated the way in which work was apportioned. 6 Student Name: ____________________________________________________________________ First Marker initials: ____________ Second Marker initials ____________ Elements assessed Comments Ratings Composition of report 0 1 2 3 4 5 Completeness & Correctness of A 0 1 2 3 4 5 Completeness & Correctness of B 0 1 2 3 4 5 Completeness & Correctness of C 0 1 2 3 4 5 Completeness & Correctness of D 0 1 2 3 4 5 Completeness & Correctness of E 0 1 2 3 4 5 Pairs work 0 1 3 5