Demonstrate the basic techniques of concurrency control and their application. Suppose the table Course in Q3...

   

Transcribed Image Text:

Demonstrate the basic techniques of concurrency control and their application. Suppose the table Course in Q3 includes an additional column capacity. A student may enrol for a course if the capacity for the course is not exceeded. Assume the following stored procedures may run concurrently: • Store procedure A allows the school administrator to update the capacity of courses. The capacity may be increased or decreased if students who have successfully enrolled in courses prior to the update are not impacted. Store procedure B allows students to enrol for courses if the capacity for the courses is not exceeded. Discuss what problem(s) if stored procedures A and B run without locks, and then recommend the lowest transaction isolations for running stored procedures A and B to avoid the problem(s). The transaction isolation levels for running stored procedures A and B need not be the same. Note that your answer must be specific to the specifications of the stored procedures. (8 marks) Describe ONE (1) permission that should be given to students to allow them to enrol for courses and submit assignment. Describe ONE (1) permission that should not be given to students. Limit your answers to permissions on the tables in Q3. (2 marks) Given Figure Q4(c), explain what the database recovery procedure will do for the transactions T1 to T5. Explain also how the transaction log help ensure the ACID properties of T1 to T5. (a) Record Pointer 1 2 3 4 5 6 7 8 9 10 11 12 Transaction ID 17 T1 T1 T1 T2 T2 T3 T3 T2 T4 T4 T4 13 T3 14 T4 15 T3 16 T5 T5 T1 Reverse Pointer 0 2 3 2 11 0 4 1 0 6 5 10 7 Forward Pointer 12 13 0 0 9 12 3 16 8 12:01:36 Insert 12:01:55 Insert 5 12:03:01 Start 10 Time 0 12:01:32 14 15 12:03:24 7 12:04:41 Start 13 12:05:23 Insert 0 12:06:45 Rollback 12:07:00 Start Start Type of Operation 12:07:12 Update Table3 Update 12:09:25 Commit 12:25:00 Update 12:25:02 Delete 0 12:25:25 Commit 0 12:25:42 Rollback 17 12:26:23 Start 0 12:26:43 Insert Table1 Table2 Table4 Table3 Table1 Table2 Table1 Table Checkpoint Crash! Figure 4(c) 22 10 15 11 7 9 7 23 Row ID -- - End of Assignment 15, 7, ... 7, 3, ... 9, 8, ... 20, ... -- Before Image 22,... 10,... 15, 4, ... 11,... 7,7.... 9, 7, ... 23.... After Image (10 marks) Question 3 (35 marks) (a) (i) (ii) Transform the ERD in Figure Q3(a)(i) into a logical model. ● ● ● ● ● ● Assignment ● courseCode assignment Number H ● weight due Date has Course courseCode courseName Figure Q3(a)(i) Refer to the description of the columns and relationships and then code the appropriate SQL statements to create the tables, derived from the ERD in Figure Q3(a)(i). You must use the same table names and the same column names as given in Figure Q3(a)(i) or your statements will be marked as incorrect. List your SQL statements in the order that they should be executed. isFor Description for columns: studentId is a whole number starting from 1000000 with step increment 1 and is auto-generated. program is at most 50 characters and is one of these values: Accountancy, Finance, Information and Communication Technology, Business Administration. courseCode is exactly 5 characters starting with two letters (A-Z) followed by 3 digits (0-9). courseName is at most 50 characters long. assignment Number is a running number for each course and can be generated with a trigger. weight is an integer that is greater than 0, dueDate is a date at least one week after today's date. dateSubmitted is a date with default value today's date, ● score is a decimal number between 0 and 100. All columns are required except for dateSubmitted and score enrols Submission student Id courseCode assignment Number date Submitted score submits Student 4 student Id program Constraints Refer to Figure Q3(a)(i) for the minimum and maximum cardinalities. Demonstrate the basic techniques of concurrency control and their application. Suppose the table Course in Q3 includes an additional column capacity. A student may enrol for a course if the capacity for the course is not exceeded. Assume the following stored procedures may run concurrently: • Store procedure A allows the school administrator to update the capacity of courses. The capacity may be increased or decreased if students who have successfully enrolled in courses prior to the update are not impacted. Store procedure B allows students to enrol for courses if the capacity for the courses is not exceeded. Discuss what problem(s) if stored procedures A and B run without locks, and then recommend the lowest transaction isolations for running stored procedures A and B to avoid the problem(s). The transaction isolation levels for running stored procedures A and B need not be the same. Note that your answer must be specific to the specifications of the stored procedures. (8 marks) Describe ONE (1) permission that should be given to students to allow them to enrol for courses and submit assignment. Describe ONE (1) permission that should not be given to students. Limit your answers to permissions on the tables in Q3. (2 marks) Given Figure Q4(c), explain what the database recovery procedure will do for the transactions T1 to T5. Explain also how the transaction log help ensure the ACID properties of T1 to T5. (a) Record Pointer 1 2 3 4 5 6 7 8 9 10 11 12 Transaction ID 17 T1 T1 T1 T2 T2 T3 T3 T2 T4 T4 T4 13 T3 14 T4 15 T3 16 T5 T5 T1 Reverse Pointer 0 2 3 2 11 0 4 1 0 6 5 10 7 Forward Pointer 12 13 0 0 9 12 3 16 8 12:01:36 Insert 12:01:55 Insert 5 12:03:01 Start 10 Time 0 12:01:32 14 15 12:03:24 7 12:04:41 Start 13 12:05:23 Insert 0 12:06:45 Rollback 12:07:00 Start Start Type of Operation 12:07:12 Update Table3 Update 12:09:25 Commit 12:25:00 Update 12:25:02 Delete 0 12:25:25 Commit 0 12:25:42 Rollback 17 12:26:23 Start 0 12:26:43 Insert Table1 Table2 Table4 Table3 Table1 Table2 Table1 Table Checkpoint Crash! Figure 4(c) 22 10 15 11 7 9 7 23 Row ID -- - End of Assignment 15, 7, ... 7,3,... 9,8,... 20, ... -- Before Image 22,... 10,... 15, 4, ... 11,... 7,7.... 9, 7, ... 23.... After Image (10 marks) Question 3 (35 marks) (a) (i) (ii) Transform the ERD in Figure Q3(a)(i) into a logical model. ● ● ● ● ● ● Assignment ● courseCode assignment Number H ● weight due Date has Course courseCode courseName Figure Q3(a)(i) Refer to the description of the columns and relationships and then code the appropriate SQL statements to create the tables, derived from the ERD in Figure Q3(a)(i). You must use the same table names and the same column names as given in Figure Q3(a)(i) or your statements will be marked as incorrect. List your SQL statements in the order that they should be executed. isFor Description for columns: studentId is a whole number starting from 1000000 with step increment 1 and is auto-generated. program is at most 50 characters and is one of these values: Accountancy, Finance, Information and Communication Technology, Business Administration. courseCode is exactly 5 characters starting with two letters (A-Z) followed by 3 digits (0-9). courseName is at most 50 characters long. assignment Number is a running number for each course and can be generated with a trigger. weight is an integer that is greater than 0, dueDate is a date at least one week after today's date. dateSubmitted is a date with default value today's date, ● score is a decimal number between 0 and 100. All columns are required except for dateSubmitted and score enrols Submission student Id courseCode assignment Number date Submitted score submits Student 4 student Id program Constraints Refer to Figure Q3(a)(i) for the minimum and maximum cardinalities. Demonstrate the basic techniques of concurrency control and their application. Suppose the table Course in Q3 includes an additional column capacity. A student may enrol for a course if the capacity for the course is not exceeded. Assume the following stored procedures may run concurrently: • Store procedure A allows the school administrator to update the capacity of courses. The capacity may be increased or decreased if students who have successfully enrolled in courses prior to the update are not impacted. Store procedure B allows students to enrol for courses if the capacity for the courses is not exceeded. Discuss what problem(s) if stored procedures A and B run without locks, and then recommend the lowest transaction isolations for running stored procedures A and B to avoid the problem(s). The transaction isolation levels for running stored procedures A and B need not be the same. Note that your answer must be specific to the specifications of the stored procedures. (8 marks) Describe ONE (1) permission that should be given to students to allow them to enrol for courses and submit assignment. Describe ONE (1) permission that should not be given to students. Limit your answers to permissions on the tables in Q3. (2 marks) Given Figure Q4(c), explain what the database recovery procedure will do for the transactions T1 to T5. Explain also how the transaction log help ensure the ACID properties of T1 to T5. (a) Record Pointer 1 2 3 4 5 6 7 8 9 10 11 12 Transaction ID 17 T1 T1 T1 T2 T2 T3 T3 T2 T4 T4 T4 13 T3 14 T4 15 T3 16 T5 T5 T1 Reverse Pointer 0 2 3 2 11 0 4 1 0 6 5 10 7 Forward Pointer 12 13 0 0 9 12 3 16 8 12:01:36 Insert 12:01:55 Insert 5 12:03:01 Start 10 Time 0 12:01:32 14 15 12:03:24 7 12:04:41 Start 13 12:05:23 Insert 0 12:06:45 Rollback 12:07:00 Start Start Type of Operation 12:07:12 Update Table3 Update 12:09:25 Commit 12:25:00 Update 12:25:02 Delete 0 12:25:25 Commit 0 12:25:42 Rollback 17 12:26:23 Start 0 12:26:43 Insert Table1 Table2 Table4 Table3 Table1 Table2 Table1 Table Checkpoint Crash! Figure 4(c) 22 10 15 11 7 9 7 23 Row ID -- - End of Assignment 15, 7, ... 7,3,... 9,8,... 20, ... -- Before Image 22,... 10,... 15, 4, ... 11,... 7,7.... 9, 7, ... 23.... After Image (10 marks) Question 3 (35 marks) (a) (i) (ii) Transform the ERD in Figure Q3(a)(i) into a logical model. ● ● ● ● ● ● Assignment ● courseCode assignment Number H ● weight due Date has Course courseCode courseName Figure Q3(a)(i) Refer to the description of the columns and relationships and then code the appropriate SQL statements to create the tables, derived from the ERD in Figure Q3(a)(i). You must use the same table names and the same column names as given in Figure Q3(a)(i) or your statements will be marked as incorrect. List your SQL statements in the order that they should be executed. isFor Description for columns: studentId is a whole number starting from 1000000 with step increment 1 and is auto-generated. program is at most 50 characters and is one of these values: Accountancy, Finance, Information and Communication Technology, Business Administration. courseCode is exactly 5 characters starting with two letters (A-Z) followed by 3 digits (0-9). courseName is at most 50 characters long. assignment Number is a running number for each course and can be generated with a trigger. weight is an integer that is greater than 0, dueDate is a date at least one week after today's date. dateSubmitted is a date with default value today's date, ● score is a decimal number between 0 and 100. All columns are required except for dateSubmitted and score enrols Submission student Id courseCode assignment Number date Submitted score submits Student 4 student Id program Constraints Refer to Figure Q3(a)(i) for the minimum and maximum cardinalities. Demonstrate the basic techniques of concurrency control and their application. Suppose the table Course in Q3 includes an additional column capacity. A student may enrol for a course if the capacity for the course is not exceeded. Assume the following stored procedures may run concurrently: • Store procedure A allows the school administrator to update the capacity of courses. The capacity may be increased or decreased if students who have successfully enrolled in courses prior to the update are not impacted. Store procedure B allows students to enrol for courses if the capacity for the courses is not exceeded. Discuss what problem(s) if stored procedures A and B run without locks, and then recommend the lowest transaction isolations for running stored procedures A and B to avoid the problem(s). The transaction isolation levels for running stored procedures A and B need not be the same. Note that your answer must be specific to the specifications of the stored procedures. (8 marks) Describe ONE (1) permission that should be given to students to allow them to enrol for courses and submit assignment. Describe ONE (1) permission that should not be given to students. Limit your answers to permissions on the tables in Q3. (2 marks) Given Figure Q4(c), explain what the database recovery procedure will do for the transactions T1 to T5. Explain also how the transaction log help ensure the ACID properties of T1 to T5. (a) Record Pointer 1 2 3 4 5 6 7 8 9 10 11 12 Transaction ID 17 T1 T1 T1 T2 T2 T3 T3 T2 T4 T4 T4 13 T3 14 T4 15 T3 16 T5 T5 T1 Reverse Pointer 0 2 3 2 11 0 4 1 0 6 5 10 7 Forward Pointer 12 13 0 0 9 12 3 16 8 12:01:36 Insert 12:01:55 Insert 5 12:03:01 Start 10 Time 0 12:01:32 14 15 12:03:24 7 12:04:41 Start 13 12:05:23 Insert 0 12:06:45 Rollback 12:07:00 Start Start Type of Operation 12:07:12 Update Table3 Update 12:09:25 Commit 12:25:00 Update 12:25:02 Delete 0 12:25:25 Commit 0 12:25:42 Rollback 17 12:26:23 Start 0 12:26:43 Insert Table1 Table2 Table4 Table3 Table1 Table2 Table1 Table Checkpoint Crash! Figure 4(c) 22 10 15 11 7 9 7 23 Row ID -- - End of Assignment 15, 7, ... 7, 3, ... 9, 8, ... 20, ... -- Before Image 22,... 10,... 15, 4, ... 11,... 7,7.... 9, 7, ... 23.... After Image (10 marks) Question 3 (35 marks) (a) (i) (ii) Transform the ERD in Figure Q3(a)(i) into a logical model. ● ● ● ● ● ● Assignment ● courseCode assignment Number H ● weight due Date has Course courseCode courseName Figure Q3(a)(i) Refer to the description of the columns and relationships and then code the appropriate SQL statements to create the tables, derived from the ERD in Figure Q3(a)(i). You must use the same table names and the same column names as given in Figure Q3(a)(i) or your statements will be marked as incorrect. List your SQL statements in the order that they should be executed. isFor Description for columns: studentId is a whole number starting from 1000000 with step increment 1 and is auto-generated. program is at most 50 characters and is one of these values: Accountancy, Finance, Information and Communication Technology, Business Administration. courseCode is exactly 5 characters starting with two letters (A-Z) followed by 3 digits (0-9). courseName is at most 50 characters long. assignment Number is a running number for each course and can be generated with a trigger. weight is an integer that is greater than 0, dueDate is a date at least one week after today's date. dateSubmitted is a date with default value today's date, ● score is a decimal number between 0 and 100. All columns are required except for dateSubmitted and score enrols Submission student Id courseCode assignment Number date Submitted score submits Student 4 student Id program Constraints Refer to Figure Q3(a)(i) for the minimum and maximum cardinalities. Demonstrate the basic techniques of concurrency control and their application. Suppose the table Course in Q3 includes an additional column capacity. A student may enrol for a course if the capacity for the course is not exceeded. Assume the following stored procedures may run concurrently: • Store procedure A allows the school administrator to update the capacity of courses. The capacity may be increased or decreased if students who have successfully enrolled in courses prior to the update are not impacted. Store procedure B allows students to enrol for courses if the capacity for the courses is not exceeded. Discuss what problem(s) if stored procedures A and B run without locks, and then recommend the lowest transaction isolations for running stored procedures A and B to avoid the problem(s). The transaction isolation levels for running stored procedures A and B need not be the same. Note that your answer must be specific to the specifications of the stored procedures. (8 marks) Describe ONE (1) permission that should be given to students to allow them to enrol for courses and submit assignment. Describe ONE (1) permission that should not be given to students. Limit your answers to permissions on the tables in Q3. (2 marks) Given Figure Q4(c), explain what the database recovery procedure will do for the transactions T1 to T5. Explain also how the transaction log help ensure the ACID properties of T1 to T5. (a) Record Pointer 1 2 3 4 5 6 7 8 9 10 11 12 Transaction ID 17 T1 T1 T1 T2 T2 T3 T3 T2 T4 T4 T4 13 T3 14 T4 15 T3 16 T5 T5 T1 Reverse Pointer 0 2 3 2 11 0 4 1 0 6 5 10 7 Forward Pointer 12 13 0 0 9 12 3 16 8 12:01:36 Insert 12:01:55 Insert 5 12:03:01 Start 10 Time 0 12:01:32 14 15 12:03:24 7 12:04:41 Start 13 12:05:23 Insert 0 12:06:45 Rollback 12:07:00 Start Start Type of Operation 12:07:12 Update Table3 Update 12:09:25 Commit 12:25:00 Update 12:25:02 Delete 0 12:25:25 Commit 0 12:25:42 Rollback 17 12:26:23 Start 0 12:26:43 Insert Table1 Table2 Table4 Table3 Table1 Table2 Table1 Table Checkpoint Crash! Figure 4(c) 22 10 15 11 7 9 7 23 Row ID -- - End of Assignment 15, 7, ... 7,3,... 9,8,... 20, ... -- Before Image 22,... 10,... 15, 4, ... 11,... 7,7.... 9, 7, ... 23.... After Image (10 marks) Question 3 (35 marks) (a) (i) (ii) Transform the ERD in Figure Q3(a)(i) into a logical model. ● ● ● ● ● ● Assignment ● courseCode assignment Number H ● weight due Date has Course courseCode courseName Figure Q3(a)(i) Refer to the description of the columns and relationships and then code the appropriate SQL statements to create the tables, derived from the ERD in Figure Q3(a)(i). You must use the same table names and the same column names as given in Figure Q3(a)(i) or your statements will be marked as incorrect. List your SQL statements in the order that they should be executed. isFor Description for columns: studentId is a whole number starting from 1000000 with step increment 1 and is auto-generated. program is at most 50 characters and is one of these values: Accountancy, Finance, Information and Communication Technology, Business Administration. courseCode is exactly 5 characters starting with two letters (A-Z) followed by 3 digits (0-9). courseName is at most 50 characters long. assignment Number is a running number for each course and can be generated with a trigger. weight is an integer that is greater than 0, dueDate is a date at least one week after today's date. dateSubmitted is a date with default value today's date, ● score is a decimal number between 0 and 100. All columns are required except for dateSubmitted and score enrols Submission student Id courseCode assignment Number date Submitted score submits Student 4 student Id program Constraints Refer to Figure Q3(a)(i) for the minimum and maximum cardinalities. Demonstrate the basic techniques of concurrency control and their application. Suppose the table Course in Q3 includes an additional column capacity. A student may enrol for a course if the capacity for the course is not exceeded. Assume the following stored procedures may run concurrently: • Store procedure A allows the school administrator to update the capacity of courses. The capacity may be increased or decreased if students who have successfully enrolled in courses prior to the update are not impacted. Store procedure B allows students to enrol for courses if the capacity for the courses is not exceeded. Discuss what problem(s) if stored procedures A and B run without locks, and then recommend the lowest transaction isolations for running stored procedures A and B to avoid the problem(s). The transaction isolation levels for running stored procedures A and B need not be the same. Note that your answer must be specific to the specifications of the stored procedures. (8 marks) Describe ONE (1) permission that should be given to students to allow them to enrol for courses and submit assignment. Describe ONE (1) permission that should not be given to students. Limit your answers to permissions on the tables in Q3. (2 marks) Given Figure Q4(c), explain what the database recovery procedure will do for the transactions T1 to T5. Explain also how the transaction log help ensure the ACID properties of T1 to T5. (a) Record Pointer 1 2 3 4 5 6 7 8 9 10 11 12 Transaction ID 17 T1 T1 T1 T2 T2 T3 T3 T2 T4 T4 T4 13 T3 14 T4 15 T3 16 T5 T5 T1 Reverse Pointer 0 2 3 2 11 0 4 1 0 6 5 10 7 Forward Pointer 12 13 0 0 9 12 3 16 8 12:01:36 Insert 12:01:55 Insert 5 12:03:01 Start 10 Time 0 12:01:32 14 15 12:03:24 7 12:04:41 Start 13 12:05:23 Insert 0 12:06:45 Rollback 12:07:00 Start Start Type of Operation 12:07:12 Update Table3 Update 12:09:25 Commit 12:25:00 Update 12:25:02 Delete 0 12:25:25 Commit 0 12:25:42 Rollback 17 12:26:23 Start 0 12:26:43 Insert Table1 Table2 Table4 Table3 Table1 Table2 Table1 Table Checkpoint Crash! Figure 4(c) 22 10 15 11 7 9 7 23 Row ID -- - End of Assignment 15, 7, ... 7,3,... 9,8,... 20, ... -- Before Image 22,... 10,... 15, 4, ... 11,... 7,7.... 9, 7, ... 23.... After Image (10 marks) Question 3 (35 marks) (a) (i) (ii) Transform the ERD in Figure Q3(a)(i) into a logical model. ● ● ● ● ● ● Assignment ● courseCode assignment Number H ● weight due Date has Course courseCode courseName Figure Q3(a)(i) Refer to the description of the columns and relationships and then code the appropriate SQL statements to create the tables, derived from the ERD in Figure Q3(a)(i). You must use the same table names and the same column names as given in Figure Q3(a)(i) or your statements will be marked as incorrect. List your SQL statements in the order that they should be executed. isFor Description for columns: studentId is a whole number starting from 1000000 with step increment 1 and is auto-generated. program is at most 50 characters and is one of these values: Accountancy, Finance, Information and Communication Technology, Business Administration. courseCode is exactly 5 characters starting with two letters (A-Z) followed by 3 digits (0-9). courseName is at most 50 characters long. assignment Number is a running number for each course and can be generated with a trigger. weight is an integer that is greater than 0, dueDate is a date at least one week after today's date. dateSubmitted is a date with default value today's date, ● score is a decimal number between 0 and 100. All columns are required except for dateSubmitted and score enrols Submission student Id courseCode assignment Number date Submitted score submits Student 4 student Id program Constraints Refer to Figure Q3(a)(i) for the minimum and maximum cardinalities.

Answer rating: 100% (QA)

It seems youve presented a series of questions related to database design transaction management isolation levels recovery procedures and permissions Due to the complexity and length of these questions I will provide a structured approach to answer each part Since its not possible to address all questions in one go accurately Ill start by addressing the concurrency control and its related transaction isolation issue of stored procedures A and B When stored procedures A and B run without locks they can lead to several problems related to concurrency such as 1 Lost Updates If both procedures read and update course capacity at the same time one update can be lost 2 Dirty Reads Procedure B could read uncommitted data if A has decreased capacity but not yet committed leading B to make decisions based on incorrect data 3 Nonrepeatable Reads Inconsistent Analysis If Procedure B reads the same data more than once and Procedure A updates it between these reads B can see different values for capacity leading to confusion or incorrect decisionmaking 4 Phantom Reads Similar to nonrepeatable reads if Procedure B retrieves a list of courses and their capacities and Procedure A inserts or deletes a row changes capacity in the middle of Bs operation B could get inconsistent results in a subsequent read operation The lowest transaction isolation level that prevents all these issues is Serializable However this level can be too restrictive and impact performance Therefore we aim for the lowest isolation level that still maintains data integrity If we only want to prevent lost updates Read Committed might suffice as each read is committed and updates are based on this fresh data To prevent dirty reads as well Read Committed still suffices To prevent nonrepeatable reads we can use Repeatable Read but this does not protect against phantom reads To fully prevent phantom reads as well we must use Serializable Hence For Procedure A updating course capacity Read Committed could be sufficient if we only need to prevent dirty reads or Repeatable ReadSerializable if nonrepeatable reads or phantom reads are a concern For Procedure B enrolling students based on capacity Serializable would be safest to ensure that the course capacity remains consistent throughout the transaction and to prevent violations of capacity constraints Its important to remember that the higher the isolation level the less concurrency the system allows which can impact performance These recommendations assume a balance between concurrency and data integrity must be found and the exact level chosen may depend on the specifics of the system and its requirements Regarding the second part of the question ... View the full answer