• 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.
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.
• 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.
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.
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.
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.
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.
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.
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.
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?
(iii) Explain why such a projection could be used to optimise a query whose results do not include
data for the attribute street.
(b) Consider the SQL query shown in Table 1.
(i) Draw a near-optimal query tree for the SQL query.
(ii) Identify the root and leaf nodes in the tree that you drew for part (i).
(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.
FROM factory, order
AND order.quantity >20
Table 1: An SQL query about orders placed with a factory.
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.
(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.
(c) What are the benefits to the company of using a database management system to support concurrent execution of its database transactions?
(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)
Holding a PhD degree in Finance, Dr. John Adams is experienced in assisting students who are in dire need...
55 - Completed Orders
Canada, Toronto I have acquired my degree from Campion College at the University of Regina Occuption/Desi...
52 - Completed Orders
Even since I was a student in Italy I had a passion for languages, in fact I love teaching Italian, and I...
102 - Completed Orders
To work with an organization where I can optimally utilize my knowledge and skills for meeting challenges...
109 - Completed Orders