August 1997
DB212: DATABASE MANAGEMENT SYSTEMS

QUESTION 5

Total Marks: 20 Marks

 Click here to access other questions

Click to access
SUGGESTED SOLUTIONS
for Question 5
5. (a) What is an index? Why are indexes used in databases? [2]
(b) Consider the following two relations for a database of student registrations:
STUDENT (STUDENT_ID, STUDENT_NAME, CAMPUS_ADDRESS, GPA)
REGISTRATION (STUDENT_ID, COURSE_ID, GRADE)
A typical query against these relations might be as follows:
SELECT SUTDENT.STUDENT_ID, STUDENT_NAME, COURSE_ID, GRADE
FROM STUDENT, REGISTRATION
WHERE STUDENT.STUDENT_ID = REGISTRATION.STUDENT_ID
AND GPA > 3.0
ORDER BY STUDENT_NAME;
(i) On what attributes should indexes be defined in order to speed up queries like the one above? Justify your answer. [7]
(ii) Write SQL commands to create each of the indexes you identified in (i). [5]
(c) Using examples, explain the two referential integrity rules which are concerned with insertions and deletions. [6]