2024¶

Exam Details¶


Module	Information Management II
Year	3
Semester	2, 2024
Date	Thursday 25 April 2024
Time	09:30 – 11:30
Venue	Sports Centre
Examiner	Prof Yvette Graham
Programmes	Integrated Computer Science · Business and Computing · Management Science and Information Systems
Materials	None permitted

Instructions¶

Attempt three questions in total.
Question 1 is mandatory. Answer any two from Questions 2, 3 and 4.
All questions carry equal marks — each is scored out of 25 marks.

Question 1 [25 marks]¶

A library loan system contains a relational database that uses the schema below, which has been produced by mapping from an Entity–Relationship diagram.

Provide a possible Entity–Relationship diagram that could have been used to produce the relational database schema above. State any assumptions you make.

Question 2 [25 marks]¶

COMPANY Relational Database Schema¶

┌─────────────────────────────────────────────────────────────────┐
│  EMPLOYEE                                                        │
│  ─────                                                           │
│  FNAME         VARCHAR                                          │
│  MINIT         VARCHAR                                          │
│  LNAME         VARCHAR                                          │
│  SSN           CHAR(9)   ★ PK                                   │
│  BDATE         DATE                                             │
│  ADDRESS       VARCHAR                                          │
│  SEX           CHAR(1)                                          │
│  SALARY        DECIMAL                                          │
│  SUPERSSN      CHAR(9)   FK → EMPLOYEE(SSN)                     │
│  DNO           INT         FK → DEPARTMENT(DNUMBER)              │
└─────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────┐
│  DEPARTMENT                                                      │
│  ──────────                                                      │
│  DNAME         VARCHAR                                          │
│  DNUMBER       INT       ★ PK                                   │
│  MGRSSN        CHAR(9)   FK → EMPLOYEE(SSN)                     │
│  MGRSTARTDATE  DATE                                             │
└─────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────┐
│  DEPT_LOCATIONS                                                  │
│  ────────────────                                                │
│  DNUMBER       INT       ★ PK, FK → DEPARTMENT(DNUMBER)          │
│  DLOCATION     VARCHAR   ★ PK                                   │
└─────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────┐
│  PROJECT                                                         │
│  ────────                                                        │
│  PNAME         VARCHAR                                          │
│  PNUMBER       INT       ★ PK                                   │
│  PLOCATION     VARCHAR                                          │
│  DNUM          INT         FK → DEPARTMENT(DNUMBER)              │
└─────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────┐
│  WORKS_ON                                                        │
│  ─────────                                                       │
│  ESSN          CHAR(9)   ★ PK, FK → EMPLOYEE(SSN)                │
│  PNO           INT       ★ PK, FK → PROJECT(PNUMBER)             │
│  HOURS         DECIMAL(3,1)                                     │
└─────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────┐
│  DEPENDENT                                                       │
│  ──────────                                                      │
│  ESSN          CHAR(9)   ★ PK, FK → EMPLOYEE(SSN)                │
│  DEPENDENT_NAME VARCHAR  ★ PK                                   │
│  SEX           CHAR(1)                                          │
│  BDATE         DATE                                             │
│  RELATIONSHIP  VARCHAR                                          │
└─────────────────────────────────────────────────────────────────┘

Legend: ★ PK = Primary Key · FK = Foreign Key

COMPANY Relational Database State (Sample Data)¶

EMPLOYEE

FNAME	MINIT	LNAME	SSN	BDATE	ADDRESS	SEX	SALARY	SUPERSSN	DNO
John	B	White	123456789	1955-01-09	10 N. Heartland, WA	M	35000	333445555	5
Franklin	T	Wong	333445555	1955-12-08	638 Voss, Houston, TX	M	40000	888665555	5
Alicia	J	Zelaya	999887777	1968-01-19	3321 Castle, Spring Valley, NV	F	28000	987654321	4
Ramesh	K	Narayan	666884444	1962-09-15	975 Fire Oak, Hill Valley, CA	M	38000	333445555	5
James	E	Borg	888665555	1937-11-10	450 Stone, Houston, TX	M	55000	NULL	1

DEPARTMENT

DNAME	DNUMBER	MGRSSN	MGRSTARTDATE
Research	5	333445555	1988-05-22
Administration	4	987654321	1995-01-01
Headquarters	1	888665555	1981-06-19

DEPT_LOCATIONS

DNUMBER	DLOCATION
1	Houston
4	Stafford
5	Houston, Bellaire

PROJECT

PNAME	PNUMBER	PLOCATION	DNUM
ProductX	1	Bellaire	5
ProductZ	4	Stafford	5
Computerization	10	Houston	5
Reorganization	20	Houston	1
Newbenefits	30	Stafford	4

WORKS_ON

ESSN	PNO	HOURS
123456789	1	32.5
123456789	2	7.5
333445555	10	10.0
333445555	20	10.0
333445555	30	30.0
333445555	40	20.0
999887777	10	35.0
999887777	30	5.0
666884444	10	20.0
666884444	20	15.0
888665555	20	15.0

DEPENDENT

ESSN	DEPENDENT_NAME	SEX	BDATE	RELATIONSHIP
123456789	Alice	F	1978-12-31	Daughter
123456789	Elizabeth	F	1957-05-05	Spouse
333445555	Alice	F	1978-12-31	Daughter
333445555	Theofania	F	1932-06-03	Grandmother
999887777	Michael	M	1988-01-03	Son
999887777	Alice	F	1988-07-31	Daughter
999887777	Abner	M	1932-06-03	Spouse

Q2 — Integrity Constraints¶

Suppose that each of the following update operations is applied directly to the COMPANY relational database schema and database state above. Discuss all integrity constraints violated by each operation (a)–(e), if any, and the different ways of enforcing these constraints.

(a) [5 marks]¶

Insert <'Sophia', 'M', 'Wood', '973442298', '1974-05-21', '23 S Lamar Blvd. Rd, Austin, TX', 'F', 62000, '222445555', 5> into EMPLOYEE.

(b) [5 marks]¶

Insert <'Information Technology', 2, '987987987', '2007-10-01'> into DEPARTMENT.

© [5 marks]¶

Insert <'777624972', 15, '40.0'> into WORKS_ON.

(d) [5 marks]¶

Delete the DEPARTMENT tuples with Dnumber = 5.

(e) [5 marks]¶

Modify the Pnumber attribute of the PROJECT tuple with Pnumber = 30 to 40.

Question 3 [25 marks]¶

Consider the COMPANY relational database schema above. Suppose that all the relations were created by (and hence are owned by) user X, who wants to grant the following privileges to user accounts A, B, C, D, and E. Write SQL statements to grant these privileges:

(a) [5 marks]¶

Account A can retrieve or modify any relation except DEPENDENT and can grant any of these privileges to other users.

(b) [5 marks]¶

Account B can retrieve all the attributes of EMPLOYEE and DEPARTMENT except for SALARY, MGRSSN, and MGRSTARTDATE.

© [5 marks]¶

Account C can retrieve or modify WORKS_ON but can only retrieve the FNAME, MINIT, LNAME, SSN attributes of EMPLOYEE and the PNAME, PNUMBER attributes of PROJECT.

(d) [5 marks]¶

Account D can retrieve any attribute of EMPLOYEE or DEPENDENT and can modify DEPENDENT.

(e) [5 marks]¶

Account E can retrieve any attribute of EMPLOYEE but only for EMPLOYEE tuples that have DNO = 3.

Question 4 [25 marks]¶

(i) [8 marks]¶

What is GDPR? Include in your answer:

What jurisdiction GDPR applies to
What legislation it now replaces

(ii) [8 marks]¶

List the GDPR principles.

(iii) [9 marks]¶

What is personal data?