August 1997
DB212: DATABASE MANAGEMENT SYSTEMS

QUESTION 5

Total Marks: 20 Marks

 Click here to access other questions

SUGGESTED SOLUTIONS
Solutions and allocated marks are indicated in green.
Return to Question 5
5. (a) What is an index? Why are indexes used in databases? [2]
An index is a table or other data structure that is used to determine the location of rows in a table that satisfy some condition.
[1 mark]
Indexes are used to improve database performance
[1 mark]
(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]
STUDENT_ID in STUDENT, because it is a primary key in both tables, and the index would force uniqueness of the key.
[3 marks]
GPA in STUDENT, because it is an attribute used to qualify record retrieval.
[2 marks]
STUDENT_NAME in STUDENT, because it is a non-key attribute used to sort records.
[2 marks]
For each index, 1 mark for identifying the attribute. Remaining marks for justification
(ii) Write SQL commands to create each of the indexes you identified in (i). [5]
CREATE UNIQUE INDEX STUINDX ON STUDENT(STUDENT_ID);
[2 marks]
1 mark for UNIQUE
CREATE INDEX CLUST_INDX
ON STUDENT(GPA)
CLUSTER;
[2 marks]
1 mark for CLUSTER
CREATE INDEX NAMEINDX ON STUDENT(STUDENT_ID);
[1 mark]
(c) Using examples, explain the two referential integrity rules which are concerned with insertions and deletions. [6]
Insertion rule: a row should not be inserted in the referencing table unless there is already a matching entry in the referenced table. [1]
Example [2]
Deletion rule: a row should not be deleted from the referenced table if there is a matching row in the referencing table. [1]
Example [2]
The examples should give clear definitions of tables and an instance of a row which should not be inserted / deleted to illustrate the rules. Otherwise, no credit.
[6 marks]