1. What is the purpose of normalisation? What problems does normali- sation gets rid of? When is it used?

2. Consider the relational schema R with key (a, b, c) and no alternative keys. Below all its full functional dependencies are listed.

R(a,b,c,d,e,f,g,h) {a,c} e {a,b} d b {g,h}

{a,b,c} f gh

(a) Explain in which normal form the schema is.

(b) Normalise schema R above to 3rd Normal Form. Make sure you declare primary and foreign keys correctly whenever you intro- duce a new schema. Also state explicitly which schema(s) are intermediate and which are part of the result.

3. What is a transaction in databases?

4. Explain ONE of the problems that can occur in a database table when

transactions are carried out concurrently.

5. Explain TWO techniques a database programmer can use to avoid running into the problems mentioned in the answer to Question 4.

6. Let the following database schema be given.

Person(idnumber, name, dob, mother, father) primary key idnumber foreign key father references Person(idnumber) foreign key mother references Person(idnumber)

Consider the following query:

For the person with idnumber 42 produce a list of all their ancestors.

Note that ancestors include grandparents, great-grandparents and so on.

1. (a) Outline how you would solve this task programmatically, ad- dressing which particular SQL shortcoming you have to over- come.

2. (b) What other kind of database might be more appropriate for the above query and why?

1. What is the purpose of normalisation? What problems does normali- sation gets rid of? When is it used? [4 marks] 2. Consider the relational schema R with key (a, b, c) and no alternative keys. Below all its full functional dependencies are listed. R(a, b, c, d, e, f, g, h) {a,c} e {a,b} d b + {9, h} {a,b,c} + f 9th (a) Explain in which normal form the schema is. [3 marks] (b) Normalise schema R above to 3rd Normal Form. Make sure you declare primary and foreign keys correctly whenever you intro- duce a new schema. Also state explicitly which schema(s) are intermediate and which are part of the result. [8 marks] 3. What is a transaction in databases? [2 marks] 4. Explain ONE of the problems that can occur in a database table when transactions are carried out concurrently. [2 marks] 5. Explain TWO techniques a database programmer can use to avoid running into the problems mentioned in the answer to Question 4. [4 marks] 6. Let the following database schema be given. Person(idnumber, name, dob, mother, father) primary key idnumber foreign key father references Person(idnumber) foreign key mother references Person(idnumber) Consider the following query: For the person with idnumber 42 produce a list of all their ancestors. Note that ancestors include grandparents, great-grandparents and so on. (a) Outline how you would solve this task programmatically, ad- dressing which particular SQL shortcoming you have to over- come. [5 marks] (b) What other kind of database might be more appropriate for the above query and why? [2 marks] 1. What is the purpose of normalisation? What problems does normali- sation gets rid of? When is it used? [4 marks] 2. Consider the relational schema R with key (a, b, c) and no alternative keys. Below all its full functional dependencies are listed. R(a, b, c, d, e, f, g, h) {a,c} e {a,b} d b + {9, h} {a,b,c} + f 9th (a) Explain in which normal form the schema is. [3 marks] (b) Normalise schema R above to 3rd Normal Form. Make sure you declare primary and foreign keys correctly whenever you intro- duce a new schema. Also state explicitly which schema(s) are intermediate and which are part of the result. [8 marks] 3. What is a transaction in databases? [2 marks] 4. Explain ONE of the problems that can occur in a database table when transactions are carried out concurrently. [2 marks] 5. Explain TWO techniques a database programmer can use to avoid running into the problems mentioned in the answer to Question 4. [4 marks] 6. Let the following database schema be given. Person(idnumber, name, dob, mother, father) primary key idnumber foreign key father references Person(idnumber) foreign key mother references Person(idnumber) Consider the following query: For the person with idnumber 42 produce a list of all their ancestors. Note that ancestors include grandparents, great-grandparents and so on. (a) Outline how you would solve this task programmatically, ad- dressing which particular SQL shortcoming you have to over- come. [5 marks] (b) What other kind of database might be more appropriate for the above query and why? [2 marks]