$($i$)$ What does integrity in the database context mean? Explain how integrity is

defined and what types of integrity are part of the relational model?

$($ii$)$ Describe how entity integrity can be specified in SQL$.$

$($iii$)$ Discuss how triggers can be used to monitor integrity. Use an SQL statement

for one of the tables in question $1$ of this paper to help illustrate your answer.

$[5$ Marks$]$

$($iv$)$ Suppose a university wishes to manage staff, lecturers and modules taught $($in

the university$).$ Suppose the university $($UNI$)$ is sub$-$divided into the following

responsibilities $($roles$)$: departments $($DEPARTMENT$),$ subject areas

$($SUBJECT$\_$AREAS$)$ and university staff $($UNIVERSITY$\_$STAFF$).$ The university

database contains, among others, the relations: modules taught $($MODULE$),$

staff $($STAFF$)$ and lecturer $($LECTURER$).$

To improve the university$-$wide cooperation, departments are awarded further

rights to the UNI$-$database $($read$,$ insertion and modification privileges on

relations MODULE, STAFF, LECTURER$).$

GRANT SELECT, INSERT, UPDATE ON MODULE TO DEPARTMENT WITH GRANT

OPTION

GRANT SELECT, INSERT, UPDATE ON LECTURER TO DEPARTMENT WITH GRANT

OPTION

GRANT SELECT, INSERT, UPDATE ON STAFF TO DEPARTMENT WITH GRANT

OPTION

MODULE should now be passed on to individual subject areas

$($SUBJECT$\_$AREAS$).$ The subject area offices should be able to create new

modules and lecturer allocations. Subject areas should get read, insertion and

modification privileges on MODULE and LECTURER. University staff

$($UNIVERSITY$\_$STAFF$)$ of the subject areas should get read privileges to all three

relations, as well as modification privileges to MODULE.

a$.$ What SQL command$($s$)$ are needed to assign the appropriate

privileges?

b$.$ What happens, when the university decides to revoke the insertion

and modification privileges to the relations MODULE and LECTURER

from the subject areas $($SUBJECT$\_$AREAS$)?$ Write down the

corresponding SQL command$($s$).$

What are the consequences for the staff privileges?

$($v$)$ Assume you work as database administrator for a European company which

stores information about its customers. Does this data need to be protected?

Explain why and what you, as database administrator, must do$.$

$[7$ Marks$]$