FIT2094SampleExamSolnS22017.pdf

Page 13 of 15 q4 answer i ii create table enrolment

Info icon This preview shows pages 13–15. Sign up to view the full content.

View Full Document Right Arrow Icon
Page 13 of 15 Q4 ANSWER (i) (ii) CREATE TABLE ENROLMENT ( student_id NUMBER (8) NOT NULL, unit_code VARCHAR2 (8) NOT NULL, semester VARCHAR2 (1) NOT NULL, year DATE NOT NULL, mark NUMBER (3) CHECK (mark BETWEEN 0 and 100) CONSTRAINT ENROLMENT_PK PRIMARY KEY (student_id, unit_code, semester, year); ALTER TABLE ENROLMENT ADD CONSTRAINT student_enrolment_fk FOREIGN KEY (student_id) REFERENCES STUDENT (student_id); ALTER TABLE ENROLMENT ADD CONSTRAINT unit_enrolment_fk FOREIGN KEY (unit_code ) REFERENCES UNIT (unit_code);
Image of page 13

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Page 14 of 15 5. Transaction Management (20 marks) (i) 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 FIT1004 and FIT1001, whereas Bruce Lee wants to enrol into FIT1004. -- Start of INSERT script INSERT INTO student VALUES (sno_seq.nextval,’Bond’,’James’,to_date(’01-Jan- 1994’,’yyyy-mon-dd’)); INSERT INTO student VALUES (sno_seq.nextval,’Lee’,’Bruce’,to_date(’01-Feb- 1994’,’yyyy-mon-dd’)); INSERT INTO enrolment VALUES (sno_seq.currval,1,2012,’FIT1004’,0,’NA’); INSERT INTO enrolment VALUES (sno_seq.currval,1,2012,’FIT1001’,0,’NA’); INSERT INTO enrolment VALUES (sno_seq.currval,1,2012,’FIT1004’,0,’NA’); COMMIT; -- Finish of INSERT script The database implementation of the two tables is based on the following ER diagram. An ORACLE’s sequence called sno_seq has been created for auto-generating of the student number in the database. The units listed in the script (e.g., FIT1004, FIT1001) exist in the UNIT table. a) What problems will be associated with the execution of the above script? (5 marks) b) Fix the script so the problems identified in (a) are eliminated. (5 marks) (ii) Using an example, illustrate and explain what the lost update problem is where two concurrent transactions are updating the same data element. (10 marks)
Image of page 14
Page 15 of 15 Q5 ANSWER (i) a) An error message will be generated when the fifth INSERT statement is executed by the DBMS. The error will state there is a duplicate PK identified. Bruce Lee will be enrolled into FIT1004 twice for S1, 2015 offering. James Bond will not be enrolled in FIT1004. Bruce Lee will be enrolled in both FIT1004 and FIT1001. b) The order of the statements in the script needs to be changed. The order should be: insert James Bond details into STUDENT table. Insert James Bond’s enrolment details into ENROLMENT table. Insert Bruce Lee’s details into STUDENT table Inert Bruce Lee’s enrolment details into ENROLMENT table. (ii) Explanation based around
Image of page 15
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern