ICT322_SEPwSolution - ICT322 Database Systems...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
ICT322 Database Systems: Implementation & Administration (Specimen Exam Paper)
Background image of page 1

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

View Full DocumentRight Arrow Icon
1 PART I Answer ALL SIX questions from this part. Each question is worth 10 marks. QUESTION 1 Consider a database at a university with contains the following information : students (name, IC number, course schedule, grades, etc.) classes (number, name, class list) faculty (name, IC number, course schedule, salary, etc.) 1.1 Indicate for each user which items that the user should be able to access and which items the user should be able to access and change. a. Faculty member b. Clerk in registrar’s office c. Student d. Payroll clerk [8 marks] 1.2 Would their database likely be centralized or distributed? What unknown factors might make a difference in the choice? [2 mark] QUESTION 2 2.1 Define an atomic transaction. Why is it important to make a transaction atomic? [4 marks] 2.2 Explain the benefits of marking transaction boundaries, declaring lock characteristics, and letting the DBMS place locks [3 marks] 2.3 Explain the read uncommitted isolation level. Give an example of its use. [3 mark] QUESTION 3 3.1 Explain the difference between optimistic and pessimistic locking. [4 marks] 3.2 With the advancement of the Internet and distributed databases, what are some of the new problems presented for database security? [3 marks]
Background image of page 2
2 3.3 What mechanism can be used to allow a user to see only a part of the database? How is this mechanism withdrawn when it is no longer required? [3 marks] QUESTION 4 4.1 Explain how a database could be recovered via reprocessing. [3 marks] 4.2 Why is this generally not feasible? [4 marks] 4.3 What is the advantage of taking frequent checkpoints of a database during processing? [3 marks] QUESTION 5 5.1 Consider the tables: CUSTOMER (Phone , FirstName, LastName) ORDER (OrderNumber , DateIn, DateOut, Phone ) State an appropriate referential integrity constraint. [3 marks] 5.2 Consider the tables: CUSTOMER (Phone, FirstName , LastName ) ORDER (OrderNumber , DateIn, DateOut, FirstName , LastName ) What does the following referential integrity constraint mean? ORDER. (FirstName, LastName) must be in CUSTOMER. (FirstName, LastName) [2 marks]
Background image of page 3

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

View Full DocumentRight Arrow Icon
3 5.3 Use a SQL statement to create the Treatment table with the constraints given below: Column Name Datatype Length Constraints TreatmentNo Numeric 4 PatientID Character 5 FOREIGN KEY to Patient TreatmentDate Date Not Null DoctorID Character 4 FOREIGN KEY to Doctor FollowupDate Date > TreatmentDate PatientType Character 12 Takes only values ‘Subsidised’ or ‘Non Subsidised’ The primary key is (TreatmentNo, PatientID) and the alternate key is (PatientID, DoctorID) [5 marks] QUESTION 6 One use of triggers is to enforce data constraints. You are given the following tables: Customer (CustNo
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 09/06/2010 for the course IT ICT322 taught by Professor Tan during the Spring '10 term at Singapore Institute of Management.

Page1 / 10

ICT322_SEPwSolution - ICT322 Database Systems...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online