2020 International Higher Diploma in Software Development Assignment Brief PDF

INTERNATIONAL HIGHER DIPLOMA IN SOFTWARE DEVELOPMENT ASSIGNMENT BRIEF EdHat Registration Number Candidate Name Module Code / Module Title ITC 0202 / DATABASE DESIGN CONCEPTS Assignment title LOWSON SUMMER HOUSES Summ...

INTERNATIONAL HIGHER DIPLOMA IN SOFTWARE DEVELOPMENT ASSIGNMENT BRIEF EdHat Registration Number Candidate Name Module Code / Module Title ITC 0202 / DATABASE DESIGN CONCEPTS Assignment title LOWSON SUMMER HOUSES Summary of Learning outcomes covered  LO1 - Understand Database Environment  LO2 - Use and manipulate appropriate database software  LO3 - Design a database  LO4 - Demonstrate the use of database Assignment Type Issued Date Due Date Date Submitted Assessor Internal Verifier PLAGIARISM: While research and discussion are an essential part of an assignment, the deliberate copying of someone else’s work or unacknowledged copying from printed or electronic sources is NOT permitted. You may be subject to disciplinary procedure if you do this. You should sign this sheet to show that you comply with these regulations. Student’s Signature: Date: _____/_____/ __________ EdHat International Higher Diploma in Software Development Assignment Brief - Cover Sheet Version: 2020.1.1  EdHat International Higher Diploma - Assignment Grading Sheet Mark Range Grade Point Grading Representation 0 - 29 0.0 E 30 - 39 1.0 D 40 - 49 1.7 C- 50 - 54 2.0 C 55 - 59 2.3 B- 60 - 64 2.7 B 65 - 69 3.0 B+ 70 - 74 3.3 A- 75 - 79 3.7 A 80 and above 4.0 A+ Task 01 – Marks Task 02 – Marks Task 03 – Marks Total Marks Grade Point Final Grade Total Marks (Average above / overall grading) Assessor Comments: Assessor: Signature: Date:_____/_____/ ___________ Internal Verifier Comments: IV Name: Signature: Date:_____/_____/ ___________ Introduction This assignment is in four parts, design, data and queries, derived data and an evaluation. All parts of the assessment relate to the Lawson Summer Houses scenario below. Scenario Lawson Summer Houses are a construction company based in the UK. The company specialize in building summer houses and outbuildings such as sheds. They want you to design and implement a database that meets the requirements for their data. These requirements are specified in this scenario and the examples of paper documents kept by the company that are shown below. Lawson Summer Houses organise their data around the concept of a ‘job’. A job is specified as being for a particular property; but note that a property might have more than one job over time. A job is also categorized by job type which is linked to a particular design. The detailed architectural plans for each design are NOT to be stored on this database. Instead a design would be linked to a number of parts and there should be a reference to a detailed plan kept as a separate document. Moreover, a particular design will specify the parts to be used for that design. A separate record is kept of the actual parts used on that job, which will include any additional parts used beyond those specified in the design. This is shown in the sample documents below. Finally, there should also be a record kept of the members of staff who work on a job. Please Note: The data shown in the assignment is not necessarily normalised, and that it is the candidate’s task to organize the data in the most optimal way possible. For example, the paper records shown below will not necessarily map directly to data base tables. The candidate is expected to use these tables as a starting point for their own normalisation and optimisation of the Lawson Summer Houses data. Please note also that all SQL scripts should be shown along with their results. Page 1 of 5 Below is a sample of the paper records currently kept by Lawson Summer Houses Document 1 – Initial Job Sheet at outset of a job Parts Job ID Property Id Address Job Type Design specified in Design 2 x Long side wall fittings 23 The Summer 1 x Back wall Elms, Summer 2 234 House Type fittings Andover, House 2 1 x front wall Hants. fittings 8 x window fittings 2 x short side wall fittings Rapid Summer 1 x Back wall House Summer 6 343 House Type fittings Gardens, House 8 1 x front wall Essex fittings 6 x window fittings 99 Neat Standard Shed Type 7 600 Street, Utility Shed Shed 1 Chesire. pack Document 2 – Staff on Job Record Staff Job ID Name Number 2 S1 Isla St Cloud 2 S8 Zak Dodd 2 S2 Marco Yad 6 S11 Sally Lam 6 S1 Isla St Cloud 7 S8 Zak Dodd 7 S1 Isla St Cloud Page 2 of 5 Document 3 – Final Job Record Parts used as Property Additional Parts Job ID Design specified Id used on job in Design 2 x Long side wall fittings 1 x Back 1 x Base Board wall 12 x filters for Summer House fittings 2 234 water pump Type 2 1 x front 1 x additional wall window frame fittings 8x window fittings 2 x short side wall fittings 1 x Back wall Summer House fittings 1 x additional 6 343 Type 8 1 x front window frame wall fittings 6x window fittings Standard 7 600 Shed Type 1 Shed 1 x door pelmet pack Page 3 of 5 Task 01: Initial Design (LO1) Allocated Given Marks Marks Create an entity relationship model for the proposed database 75 system for Lawson Summer Houses. This should be a fully normalised model to 3rd normal form. You do not have to show the steps you took in the normalisation process. Create a data dictionary for the entity relationship model 25 Task 02: Data and Queries (LO2) Allocated Given Marks Marks Create all the tables using SQL. Show your Create scripts and 30 the finished tables.  Input data on all the jobs and the properties for those jobs.  Provide a listing of this Input data for the members of staff including those listed in the scenario. Enter yourself as a member of staff and assign staff to jobs.  Provide a listing of this Input data on the parts that belong to a particular design and the parts that are actually used on a job. Provide a listing of this.  Write a query that selects all the jobs and which staff 70 have worked on them.  Write a query that selects the parts that are actually used on a job including those specified as standard in the design for that particular job.  Write a query that selects all the properties and the jobs that have taken place at those properties.  Write a query that shows all jobs that have taken place for a particular design.  Write a query that counts the number of jobs that have used a back wall fitting.  Write a query that shows all the summer house jobs that a particular member of staff has worked on Page 4 of 5 Task 03: Derived Data and Evaluation (LO3 and LO4) Allocated Given Marks Marks The company have said they would like to extend the database 60 to include costing information. All parts will be allocated a price and all members of staff a flat rate for working on a job. Discuss how you would derive data for a report that showed all the relevant data for a job including the costs. This should also include the property, job type, design, parts and staff. You should include an outline of how you would derive it using SQL and specify the method of implementing it Give an evaluation of how the work you have done has met the 40 requirements of the firm. This should include data storage and applications. You should also discuss how you have standardised the company’s data. Submission Requirements  Your submission should be in the form of a single word-processed document that includes any necessary diagrams.  The word count for the document is 1750 words (excluding text in any diagrams). You should explain any assumptions you have made.  A digital version must be submitted on a CD, USB flash drive or other similarly acceptable medium, along with a copy of the developed database. Page 5 of 5 Deliverable: Hard & Soft copy of word-processed report NOTE:  You should include the COVER PAGE of this assignment including the Declaration of Academic Honesty when you submit your final report.  More work is required to achieve a pass. If you submit your assignment after the deadline set above your assignment will be only marked for PASS provided it falls within the extended deadline.  If submitted after the extended deadline, the assignment will not be accepted whereas you shall be asked to go for a NEW assignment.  Plagiarism is strictly prohibited and will lead for a “Fail” Grade. Instructions to students: 1. All assignments should comprise of the standard Front Cover given. No other front page will be accepted. 2. All assignments should be bound with BLACK hard board cover as the last sheet. 3. Clearly label the CD’S with your Name, Batch no and Student no and attach it to the Back cover of your assignment. 4. Report Writing Guidelines: a. Every Assignment should have an Introduction and Conclusion. b. The Standard Table of Contents should be generated. c. All the Figures, Table Diagrams, etc. should be numbered. d. Main Heading Font: Arial – size 16 e. Sub Heading Font: Arial – size 14 f. Body text Font: Arial- size 11 g. Paragraph: Single line h. Margins: Top – 1” Bottom – 1” Left – 1” Right – 1” i. Header – include the Module name on the Right-hand side j. Footer – include the Page number on the Right-hand side k. All sections should have continuity and pages should be clearly labeled. l. References – clear references for all the materials, books, articles, website, etc should be given in the following format:  Books – title, Author, ISBN No, Publisher & Edition, Chapter & Page Nos.  URL- Complete address e.g. http://www.abs.com/index/1234/xyz.asp.... and date  Article, Journals: Name of Published material, date, author EdHat International Higher Diploma Assignment - Deliverable guide Version : 1.1 (2020) Declaration of Academic Honesty Instructions 1. You must submit a completed copy of this form every time you submit an assignment. 2. You must submit this declaration electronically, either within your assignment, file, or as a separate file. 3. If you do not submit this declaration, your work will not be assessed. 4. If any student is found to have been dishonest, then the student may face disciplinary action as stated in the Nations Campus Plagiarism Policy. Student’s Full Name: Date: Assignment Title: Declaration (Insert YES or NO here): 1. I confirm that this assignment is my own work: 2. I confirm that where ever I have used someone else’s words, concepts, graphics or data in my work, I have clearly indicated exactly where I have done so, using the Oscola referencing system. (insert yes or no here): 3. I confirm that I have indicated by referencing, where I have used someone else’s words, concepts, graphics or data, irrespective of whether I have quoted verbatim or I have paraphrased in my own words. 4. I have clearly indicated in my assignment any work that has been contributed by another student. 5. I have clearly indicated in my assignment any work that has been carried out collaboratively with another student. …………………………………………….. Student Signature EdHat International Higher Diploma Assignment Student Academic Honesty Statement Version: 1.1(2020)