December 1998
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

 
(a) What is an index, and why are indexes often used in large database systems? [2]
Index: a table or data structure used to determine the location of rows in a table (or tables) (1 mark). They are used to improve performance (1 mark).

 

 [2]
(b) There are several guidelines to help in choosing what indexes to define for a relational database. For each of the following guidelines, explain the reasons why it is often given:
(i) Specify an unique index for the primary key attribute of each table. [3]
To ensure uniqueness of primary key (1 mark) and to speed up retrievals (1 mark).

 
(ii) Specify an index for foreign keys that are used in joining tables. [2]
To speed up multiple-table queries (1 mark).

 

  

[3]

 
(c) For which non-key attributes might it be useful to specify an index? [2]
Those which are referred to in sorting (1 mark) or grouping (1 mark) commands.

 

 [2]
(d) Under what circumstances would the designer of a large database decide not to use any indexes? [2]
Where there are many updates to the database (1 mark), since this may involve decreased performance (1 mark).

 

 [2]
(e) What is meant by 'referential integrity' in a database?

Referential integrity: database must not contain any unmatched foreign key values. (1 mark), i.e., all foreign key values must occur as primary keys in the relevant target relation (1 mark).


Give a rule which ensures that referential integrity is maintained for:

 [2]

 

[2]
  • an insertion
    Insertion rule: a row should not be inserted in the referencing table unless there is already a matching entry in the referenced table.

 

 [2]

 

[2]
  • a deletion
    Deletion rule: a row should not be deleted from the referenced table if there is a matching row in the referencing table.

 

 [2]

 

[2]
With reference to the tables below, give an example of an insertion and  a deletion which are forbidden by these rules

Course

Course-code Course-title Tutor-code
DB100 Databases 45
SA200 Systems analysis 63
SD300 Systems design 21
CP400 Programming in C 63

Tutor

Tutor-code Tutor-name Date-of-birth
21 Brown 4/5/63
45 Jones 11/4/61
61 Peters 12/4/55
63 Smith 25/12/23

Insertion example: any example insertion in Course table, where Tutor-code does not appear in Tutor table.

Deletion example: can't delete 21, 45 or 63 rows in Tutor table.

 

  

 

 

 

 

 

 

 

 

 

 

 

 

 

[1]

[1]
(f) How might referential integrity constraints be enforced in a database? [3]
There are two possible methods of enforcement:
  • Each application program might contain logic to enforce the integrity constraints, independent of all other application (1 mark); or
  • The contraints might be declared when the relational table is defined, and the DBMS could then be responsible for enforcement (1 mark).

The second option is more reliable and therefore preferred (1 mark).

 

  

 

 

 

[3]