Question

Question 2 – Transaction management and SQL [15] The script shown below is used to add 2 students and their enrolments into two tables STUDENT and ENROLMENT. The new students are "James Bond" and "Bruce Lee". James Bond wants to enrol into CMPG1004 and CMPG1001, whereas Bruce Lee wants to enrol into CMPG1004. -- Start of INSERT script INSERT INTO student VALUES (sno_seq.nextval, 'Bond', 'James', to_date('01-Jan-1994', 'dd-mon-yyyy')); INSERT INTO student VALUES (sno_seq.nextval, 'Lee', 'Bruce', to_date('01-Feb-1994', 'dd-mon-yyyy')); INSERT INTO enrolment VALUES (sno_seq.currval, 'CMPG1004', 2012, 1, 0, 'NA'); INSERT INTO enrolment VALUES (sno_seq.currval, 'CMPG1001', 2012, 1, 0, 'NA'); INSERT INTO enrolment VALUES (sno_seq.currval, 'CMPG1004', 2012, 1, 0, 'NA'); COMMIT; -- Finish of INSERT script The database implementation of the two tables is based on the following ER diagram: UNIT UNIT_CODE CHAR (8) UNIT_NAME VARCHAR (50) ENROLMENT STU_NBR NUMERIC (8) UNIT_CODE CHAR (8) ENROL_YEAR NUMERIC (4) ENROL_SEMESTER CHAR (2) ENROL_MARK NUMERIC (3) ENROL_GRADE CHAR (2) STUDENT STU_NBR NUMERIC (8) STU_LNAME VARCHAR (50) STU_FNAME VARCHAR (50) STU_DOB Date An ORACLE's sequence called sno_seq has been created for auto-generating of the student number in the database. The unit listed in the script (e.g., CMPG1004, CMPG1001) exist in the UNIT table. 2.1. What problems will be associated with the execution of the above script? (3) 2.2. How will you fix the script, so the problems identified in (2.1.) are eliminated? (4) 2.3. Using an example, illustrate and explain what the lost update problem is, where two concurrent transactions are updating the same data element. (8)

          Question 2 – Transaction management and SQL [15]

The script shown below is used to add 2 students and their enrolments into two tables STUDENT and ENROLMENT. The new students are "James Bond" and "Bruce Lee". James Bond wants to enrol into CMPG1004 and CMPG1001, whereas Bruce Lee wants to enrol into CMPG1004.

-- Start of INSERT script
INSERT INTO student VALUES (sno_seq.nextval, 'Bond', 'James', to_date('01-Jan-1994', 'dd-mon-yyyy'));
INSERT INTO student VALUES (sno_seq.nextval, 'Lee', 'Bruce', to_date('01-Feb-1994', 'dd-mon-yyyy'));
INSERT INTO enrolment VALUES (sno_seq.currval, 'CMPG1004', 2012, 1, 0, 'NA');
INSERT INTO enrolment VALUES (sno_seq.currval, 'CMPG1001', 2012, 1, 0, 'NA');
INSERT INTO enrolment VALUES (sno_seq.currval, 'CMPG1004', 2012, 1, 0, 'NA');
COMMIT;
-- Finish of INSERT script

The database implementation of the two tables is based on the following ER diagram:

UNIT
UNIT_CODE CHAR (8)
UNIT_NAME VARCHAR (50)

ENROLMENT
STU_NBR NUMERIC (8)
UNIT_CODE CHAR (8)
ENROL_YEAR NUMERIC (4)
ENROL_SEMESTER CHAR (2)
ENROL_MARK NUMERIC (3)
ENROL_GRADE CHAR (2)

STUDENT
STU_NBR NUMERIC (8)
STU_LNAME VARCHAR (50)
STU_FNAME VARCHAR (50)
STU_DOB Date

An ORACLE's sequence called sno_seq has been created for auto-generating of the student number in the database. The unit listed in the script (e.g., CMPG1004, CMPG1001) exist in the UNIT table.

2.1. What problems will be associated with the execution of the above script? (3)

2.2. How will you fix the script, so the problems identified in (2.1.) are eliminated? (4)

2.3. Using an example, illustrate and explain what the lost update problem is, where two concurrent transactions are updating the same data element. (8)

Question 2 – Transaction management and SQL [15]

The script shown below is used to add 2 students and their enrolments into two tables STUDENT and ENROLMENT. The new students are "James Bond" and "Bruce Lee". James Bond wants to enrol into CMPG1004 and CMPG1001, whereas Bruce Lee wants to enrol into CMPG1004.

– Start of INSERT script
INSERT INTO student VALUES (snoseq.nextval, 'Bond', 'James', todate('01-Jan-1994', 'dd-mon-yyyy'));
INSERT INTO student VALUES (snoseq.nextval, 'Lee', 'Bruce', todate('01-Feb-1994', 'dd-mon-yyyy'));
INSERT INTO enrolment VALUES (snoseq.currval, 'CMPG1004', 2012, 1, 0, 'NA');
INSERT INTO enrolment VALUES (snoseq.currval, 'CMPG1001', 2012, 1, 0, 'NA');
INSERT INTO enrolment VALUES (snoseq.currval, 'CMPG1004', 2012, 1, 0, 'NA');
COMMIT;
– Finish of INSERT script

The database implementation of the two tables is based on the following ER diagram:

UNIT
UNITCODE CHAR (8)
UNITNAME VARCHAR (50)

ENROLMENT
STUNBR NUMERIC (8)
UNITCODE CHAR (8)
ENROLYEAR NUMERIC (4)
ENROLSEMESTER CHAR (2)
ENROLMARK NUMERIC (3)
ENROLGRADE CHAR (2)

STUDENT
STUNBR NUMERIC (8)
STULNAME VARCHAR (50)
STUFNAME VARCHAR (50)
STUDOB Date

An ORACLE's sequence called snoseq has been created for auto-generating of the student number in the database. The unit listed in the script (e.g., CMPG1004, CMPG1001) exist in the UNIT table.

2.1. What problems will be associated with the execution of the above script? (3)

2.2. How will you fix the script, so the problems identified in (2.1.) are eliminated? (4)

2.3. Using an example, illustrate and explain what the lost update problem is, where two concurrent transactions are updating the same data element. (8)

Added by Toni T.

Question

Please give Ace some feedback