CS2008 (Data Management) - 5 - 2007-2008

CS2008 (Data Management) - 5 - 2007-2008

All the questions in this exam refer to the relational tables listed below. These tables describe data related to a company with several departments located in different cities.

Project /
Pno / Pname / Plocation / Dno /
1 / ProductX / Glasgow / 3
2 / ProductY / Aberdeen / 4
3 / Computerization / Edinburgh / 2
4 / Reorganization / Edinburgh / 1
5 / Newbenefits / Edinburgh / 2
Department /
Dno / Dname / Manager_NIno /
1 / Headquarters / AZ58148
2 / Administration / JW47025
3 / Production / JB36910
4 / Research / RN69258
Dept_Location /
Dno / Location /
1 / Edinburgh
2 / Edinburgh
3 / Glasgow
4 / Aberdeen
Employee /
NIno / Fname / Lname / Address / Sex / Salary / Supervisor_NIno / Dno /
AA70258 / Abrar / Ahmad / 98 Summer Street Glasgow / M / £29,000.00 / JB36910 / 3
AZ58148 / Alicia / Zelaya / 78 Potter Street Edinburgh / F / £50,000.00 / NULL / 1
JB36910 / James / Borg / 12 Station Road Glasgow / M / £39,000.00 / AZ58148 / 3
JS25801 / John / Smith / 23 Grace Street Edinburgh / M / £29,000.00 / JW47025 / 2
JW47025 / Jennifer / Wallace / 54 Parker Road Edinburgh / F / £42,000.00 / AZ58148 / 2
RN69258 / Ramesh / Narayan / 55 Orchard Street Aberdeen / M / £32,000.00 / AZ58148 / 4
Dependent /
NIno / Dependent_Fname / Sex / RelationShip /
JB36910 / Alice / F / Spouse
JB36910 / Mary / F / Daughter
JW47025 / John / M / Spouse
JW47025 / Peter / M / Son

CS2008 (Data Management) - 5 - 2007-2008

Works_on /
NIno / Pno / Hours /
AZ58148 / 4 / 10
JB36910 / 1 / 150
AA70258 / 1 / 350
JW47025 / 4 / 50
JS25801 / 4 / 100
JW47025 / 5 / 50
RN69258 / 2 / 250
JS25801 / 3 / 275

CS2008 (Data Management) - 5 - 2007-2008

1. (a) ‘A database can be defined as a shared collection of logically related data and its metadata’. What do you understand by the term metadata in this definition? (2)

(b) In the context of relational databases,

i) Explain the term ‘entity integrity’ (2)

ii) Explain why you think the ‘Department’ table exhibits entity integrity. (2)

i) Explain the term ‘referential integrity’ (2)

ii) Explain why you think the ‘Employee’ table exhibits referential integrity. (2)

(d) Transform the following EER model fragment representing the relationship between Painter and Painting into a set of relations, explaining in detail the steps you have followed. (5)

(e) Transform the following EER model fragment representing the relationship between Employee and Skill into a set of relations, explaining in detail the steps you have followed. (5)

(f) What is meant by third normal form (3NF)? Examine the following table to check if it is in 3NF. If yes, explain your answer. Otherwise convert the table into 3NF (Assume AlbumID is the Primary Key). (5)

AlbumID / ArtistID / SongTitle / ArtistName / SongLength / AlbumName /
1 / 1 / Dreamy Eyes / Christina Aguilera / 5:28 / Christina Aguilera
2 / 2 / Fever / Kylie Minogue / 3:42 / Fever
3 / 1 / Slow Down Baby / Christina Aguilera / 4:32 / Back to Basics
4 / 2 / Dangerous / Kylie Minogue / 6:12 / Kylie Minogue

CS2008 (Data Management) - 5 - 2007-2008

2. (a) In SQL, give one example of a Data Control Language (DCL) statement (use the Employee table if you need to refer to a table in your answer). (1)

(b) Write an SQL statement to update the salary of employees working in the production department by £500. (2)

(c) Write an SQL statement to change the Employee table by adding a new column representing date of birth of employees. You can set the column (field) data type to be CHAR type. (2)

(d) Using only the Employee table, write an SQL query to show the names of employees who draw more salary than Ramesh Narayan. (5)

(e) Write an SQL query to show the first and last names of those employees who have dependents and also show the number of dependents for each of these employees. Sort the list in the alphabetical order of employee first name and last name. (5)

(f) Write an SQL query to show the names of employees and the names of the projects each of them works on and the total number of hours each of them spends on each of the projects. (5)

(g) Write an SQL query to show the names of employees, the name of department they belong to, the names of the projects they work on and the number of hours they work for the projects carried out in the department located in Aberdeen. (5)

3. (a) Explain the main steps involved in connecting to and querying a database from java. (3)

(b) In the context of transaction management, explain what you understand by the term ‘two-phase locking’. (4)

(d) Briefly explain the major steps involved in relational database query processing and explain how relational algebra trees (RATs) are used in query optimisation. (5)

(e) Consider the following SQL query which refers to the Department and Employee tables given above:

SELECT D.Dname

FROM Employee E, Department D

WHERE E.Dno = D.Dno

AND E.Lname='Smith'

AND S.Fname='John';

Using the relational algebra notation, ∏ for projection, σP for selection with predicate P, × for a Cartesian product, for a natural join, and for conjunction, write down three different but logically equivalent ways of expressing the above query in relational algebra. In your answers, use the given table aliases for brevity, use brackets as necessary to avoid ambiguities, and label your answers A, B and C. (6)

(f) In the database given above, suppose the Department and Employees tables have very many rows but have no indexes. Arrange your solutions (A, B, C) from (e) in order of efficiency and justify your answer. (3)

4. (a) What does the abbreviation BLOB stand for? What are the different data types MySQL supports to store BLOB data? (3)

(b) With the help of a sketch, describe the various subsystems in a MySQL server. (3)

(c) With the aid of a sketch, describe the different firewalls often used in the modern web-based database applications. For each firewall, give one example of a type of network packet that should be permitted to cross that firewall. (4)

(d) In relation to implementing secure database transactions over the internet, briefly explain the purpose of using (a) public key / private key encryption, (b) digital signatures, and (c) digital certificates. (3)

(e) At the logical level (relational level), the company relational database shown above is composed of six tables: Department, Employee, Project, Dept_Location, Dependent and Works_on (each with some associated records). Using UML notation, draw an entity-relationship diagram that represents the same database structure at the conceptual level. For each entity, show its minimum and maximum participatory constraints in the relationship and explain your choice of values. (6)

(f) Consider the following B+ tree

Draw the tree after a new record, whose search key field value of 14, is inserted into the tree. (6)