can you complete this written assignment
Assessment Brief Form Assessment Brief Form 1 Assessment Information/Brief 2019-20 Module title Database Systems CRN 32741 Level 4 Assessment title Database Systems Coursework: Part 1 of 1 Weighting within module This assessment is worth 50% of the overall module mark. Submission deadline date and time 4pm on Wednesday 6th May 2020 Module Leader/Assessment set by Dr Bryant Dr Bryant’s on-line contact details are available from the staff section of this module on Blackboard. How to submit • You must submit a single document, in PDF format, via Blackboard. 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 family name, your student ID and your User name. Assessment task details and instructions • This is an individual coursework. You must not work in a team. The whole of your submission should be your own work. Please follow the regulations and policies. • You are reminded that penalties will be applied to late submissions, in accordance with the regulations and policies, as amended by the university in response to the pandemic. • At the start of your document, you must include your full name, your student ID, your User name, the assessment title, the module title and the CRN. • You must answer all the questions. • The order of your answers must correspond to the order of the questions in this briefing. • Citations and references must conform to the APA 6th (Harvard) style. • You must use a black font of, at least, size 12pt. Do not use colour for emphasis. Assessed intended learning outcomes On successful completion of this assessment, you will be able to: 1. describe the basic concepts of Relational Database Design; 2. describe the process of Database Query processing and evaluation; 3. explain the concepts of transaction management and concurrency control; 4. discuss database security and recovery; 5. explain advanced data types and new applications. Assessment Brief Form 2 Module Aims • To provide students with fundamental knowledge of database management systems, their design, implementation and their applications. • To develop students' knowledge and understanding of the underlying principles of Relational Database Management System. • To demonstrate database trilingualism in: the basic algebraic operations, a standard query language and English. • To build up students' ability to learn DBMS advanced features. • To build up students' ability to implement and maintain an efficient database system using emerging trends. 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 which exceed the word limits. Feedback arrangements Your mark will be made available via Grade Centre in Blackboard. An announcement will be made on Blackboard (and emailed to you) when the mark has been released. Support arrangements askUS The University offers a range of support services for students through askUS. Good Academic Conduct and Academic Misconduct 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. Here is a link to where you can find out more about the skills which students require http://www.salford.ac.uk/skills-for-learning. 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. You can find out how to avoid academic misconduct here https://www.salford.ac.uk/skills-for-learning. Assessment Information If you have any questions about assessment rules, you can find out more here. Personal Mitigating Circumstances If personal mitigating circumstances may have affected your ability to complete this assessment, you can find more information about personal mitigating circumstances procedure here. Personal Tutor/Student Progression Administrator If you have any concerns about your studies, contact your Personal Tutor or your Student Progression Administrator. Assessment Criteria Each part of each question is followed by the number of marks it is worth. In Year Retrieval Scheme Your 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 resit submission deadlines will be published as soon as they are available. http://www.askus.salford.ac.uk/ http://www.salford.ac.uk/skills-for-learning https://www.salford.ac.uk/skills-for-learning https://www.salford.ac.uk/askus/academic-support/student-handbook/your-studies/course-support/assessment-and-feedback https://sss.salford.ac.uk/ Assessment Brief Form 3 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) Table 1: An SQL query about orders placed with a factory. SELECT factory.name FROM factory, order WHERE order.factory_ID=factory.ID AND order.quantity >20 AND order.priority=’urgent’; Assessment Brief Form 4 2) Suppose that a company stores goods it has manufactured in crates and has a fleet of vehicles that transport crates between warehouses. At any one time, the company needs to operate multiple vehicles, which may need to travel between different pairs of warehouses. The company uses a relational database to store data on which crates are in which warehouses. (a) Describe the properties required of transactions involving the company’s database. (8 marks) (b) Discuss the extent to which a database management system alone can ensure that the company’s database remains in a consistent state. Illustrate your answer using a transaction that is intended to transfer crates from one warehouse to another. (3 marks) (c) What are the benefits to the company of using a database management system to support concurrent execution of its database transactions? (3 marks) (d) Consider the schedule of two transactions involving three warehouses shown in Table 2. (i) How do the two transactions interfere with each other? State what the resulting error is and explain why it occurs. (3 marks) (ii) What phrase is usually used to describe this type of interference? (1 mark) (e) Consider the schedule of three transactions shown in Table 3. (i) Draw the precedence graph for this schedule. (5 marks) (ii) Is the schedule conflict-serializable? Use your graph to justify your answer. (2 marks) Assessment Brief Form 5 Time Transaction 1 Transaction 2 Warehouse sum W1 W2 W3 t1 begin_transaction 100 75 25 t2 begin_transaction sum=0 100 75 25 0 t3 read(quantityW1) read(quantityW1) 100 75 25 0 t4 quantityW1=quantityW1+10 sum=sum+quantityW1 100 75 25 100 t5 write(quantityW1) read(quantityW2) 110 75 25 100 t6 read(quantityW3) sum=sum+quantityW2 110 75 25 175 t7 quantityW3=quantityW3-10 110 75 25 175 t8 write(quantityW3) 110 75 15 175 t9 commit read(quantityW3) 110 75 15 175 t10 sum=sum+quantityW3 110 75 15 190 t11 commit 110 75 15 190 Table 2: Schedule involving two transactions and three warehouses (W1, W2 and W3). The columns W1,W2, and W3 show the quantity of crates at the warehouses as recorded on secondary storage. The sum column shows the value of a variable that is local to Transaction 2. Transaction 1 Transaction 2 Transaction 3 Read(B) Read(B) Write(A) Write(B) Read(A) Read(B) Write(B) Table 3: Schedule involving three transactions. Assessment Brief Form 6 3) Suppose that an airline has a computer system for reserving seats on its planes, which stores data on customer transactions in a relational database. (a) Under what circumstances must an exclusive lock be granted by the database management system (DBMS)? (2 marks) (b) Describe a protocol that the company could use to ensure that schedules are conflict serializable. (3 marks) (c) The locking information for five transactions is shown in Table 4. (i) Produce a wait-for-graph for the transactions. (7 marks) (ii) Does deadlock occur? Justify your answer. (1 mark) (d) Table 5 shows the airline’s transaction log file. Suppose that part of the airline’s computer system fails at 15:06. Assume that the DBMS is using the Immediate Update protocol. (i) Describe the actions that the recovery manager would take to recover from the failure. Explain why each action is