32741 4 Assessment Information/Brief for Semester Two Module title CRN Level Database Systems Assessment title DBS Semester Two Coursework Weighting within module This assessment is worth 50% of the overall module mark. • How to Submit • You must submit a single document, in PDF format. Zero (0) marks will be awarded for supplementary files or files in other formats. Do not submit a zip file. • The name of your PDF file must include your student roll number and your User ID. Please do not include your name because the University will mark assessments anonymously where this is possible. Student roll numbers usually comprise the @ symbol followed by eight digits. User IDs usually comprise three characters followed by three digits. You must submit through Blackboard. It is your responsibility to ensure that your work is submitted successfully to the correct folder and that the correct version has been submitted. If you find that you have submitted the wrong version, you can contact Digital IT Service Desk before the submission deadline. They will be able to withdraw an incorrect submission, enabling you to make a new submission. Once the submission deadline has passed, no further action can be taken and work which has been submitted cannot be withdrawn. You can find the contact details of the Digital IT Service Desk in Chapter 2 of the module's exercise booklet. Database Systems (CRN 32741, UMC G400 10045) Semester Two Coursework 1 Assessment Task Details and Instructions • This is an individual assessment. You must not work in a team. The whole of your submission must be your own work. • You are reminded that penalties will be applied to late submissions, in accordance with the regulations and policies. • At the start of your document, you must include your student roll number, your User ID, the assessment's title, and the module's title and the CRN. Please do not include your name because the University will mark assessments anonymously where this is possible. • You must answer all the questions. • Do not include the questions in your submission. • The order of your answers must correspond to the order of the questions in this briefing. • Your submission must be typed (rather than hand-written) in, at least, font size 12pt. Do not use the colours red or green anywhere in your submission. • Do not include diagrams in your submission that are illegible, blurred or require the reader to use a magnifying glass. This briefing does not place any constraints on how you prepare your diagrams, but you are encouraged to learn how to use a digital drawing tool because it is a useful skill that may be useful during the remainder of your studies and career. There is a wide range of drawing software available. The university provides you with a wide range of support for developing your digital skills, see: http://www.salford.ac.uk/skills/ • Citations and references must conform to the APA 7 th (Harvard) style. Assessed Intended Learning Outcomes This coursework assesses the student's ability to: 1. describe the process of database query processing and evaluation; 2. explain the concepts of transaction management and concurrency control; 3. discuss database security and recovery. Employability Skills to be Developed/Demonstrated Communication? Yes Critical Thinking and Problem Solving? Yes • • • Data Literacy? Yes • Digital Literacy? Yes • Industry Awareness? No • Innovation and Creativity? Yes • Proactive Leadership? No • Reflection and Life-Long Learning? Yes • Self-management and Organisation? Yes • Team Working? No Word Count There are four questions. Your answer for each question must not exceed seven hundred and fifty (750) words. Zero marks will be awarded for parts of your submission beyond the word limits. Feedback Arrangements Database Systems (CRN 32741, UMC G400 10045) Semester Two Coursework 2 Your mark will be made available via Blackboard. An announcement will be made on Blackboard (and emailed to you) when the mark has been released. Academic Integrity and Referencing Students are expected to learn and demonstrate skills associated with good academic conduct (academic integrity). Good academic conduct includes the use of clear and correct referencing of source materials. You can find out more about the skills which students need at • • https://www.salford.ac.uk/skills/academic-integrity-referencing ; https://www.salford.ac.uk/skills/referencing. Academic misconduct is an action which may give you an unfair advantage in your academic work. This includes plagiarism, asking someone else to write your assessment for you or taking notes into an exam. The University takes all forms of academic misconduct seriously. Support arrangements There will be a verbal briefing on the contents of this document and some question and answer sessions during scheduled class times. You can obtain further support for this assessment by attending Dr Bryant's surgery. If you send Dr Bryant a query about this assessment via e-mail then he may respond by sending a message (via Blackboard) to all students taking this module so that all students can benefit from his answer. Tips You can find more information about understanding your assessment brief and assessment tips for success at: https://www.salford.ac.uk/skills/university- assessments . Assessment Rules and Processes You can find information about assessment rules and processes in the Assessment Support module in Blackboard. Develop your Academic and Digital Skills Find resources to help you develop your skills at http://www.salford.ac.uk/skills/ . Concerns about Studies or Progress If you have any concerns about your studies, contact your Academic Progress Review Tutor/Personal Tutor or your Student Progression Administrator (SPA). AskUS Services The University offers a range of support services for students through AskUS, including Disability and Learner Support, Wellbeing and Counselling Services (see https://www.salford.ac.uk/askus ). Personal Mitigating Circumstances If personal mitigating circumstances (e.g. illness or other personal circumstances) may have affected your ability to complete this assessment, you can find more information about personal mitigating circumstances procedure at: https://www.salford.ac.uk/askus/admin-essentials/personal-mitigating- circumstances Independent advice about this process is available from the Students' Union Advice Centre (see https://www.salfordstudents.com/advice/centre ). Database Systems (CRN 32741, UMC G400 10045) Semester Two Coursework 3 In Year Retrieval Scheme This assessment is not eligible for in year retrieval. Reassessment If you fail this assessment, and are eligible for reassessment, you will be required to do the same assessment again. The provisional re-sit submission deadline is 26th July 2024. For students with accepted personal mitigating circumstances for absence/non submission, this will be your replacement assessment attempt. We know that having to undergo a reassessment can be challenging however support is available. Have a look at all the sources of support outlined earlier in this brief and refer to the Personal Effectiveness resources at https://www.salford.ac.uk/skills/personal-effectiveness. Assessment Criteria Each part of each question is followed by the number of marks it is worth. Percentage Mark Level of Performance Outstanding 90-100 80-89 Excellent 70-79 Very Good 60-69 Good 50-59 Fair 40-49 30-39 Adequate Unsatisfactory 20-29r Poor 10-19r 0-9 Very Poor Extremely Poor Database Systems (CRN 32741, UMC G400 10045) Semester Two Coursework 4 Questions 1) (a) Suppose that a relation factory(ID, name, street, postcode) contains 800 tuples. Each tuple is comprised of a header (24 bytes) and four attributes: ID (5 bytes), name (10 bytes), street (20 bytes) and postcode (6 bytes). The size of each disc block is 1024 bytes and the size of the header of each disc block size 24 bytes. (i) How many blocks would be required to store the whole of this relation? (3 marks) (ii) How many blocks would be required to store a projection that does not include the attribute street? (3 marks) (iii) Explain why such a projection could be used to optimise a query whose results do not include data for the attribute street. (2 marks) (b) Consider the SQL query shown in Table 1. (i) Draw a near-optimal query tree for the SQL query. (8 marks) (ii) Identify the root and leaf nodes in the tree that you drew for part (i). (1 mark) (iii) Write down a justification for the position of the root node and each of the internal nodes in the tree that you drew for part (i) in terms of the heuristic steps that optimisers apply to reduce the cost of optimisation. Explain why the position of the nodes should make the query efficient. (8 marks) SELECT factory.name FROM factory, order WHERE order.factory_ID=factory.ID AND order.quantity >20 AND order.priority='urgent'; Table 1: An SQL query about orders placed with a factory. Database Systems (CRN 32741, UMC G400 10045) Semester Two Coursework 5