Informationfor questions 1, 2 and 3:

The  given  EER  diagram  represents  the  database  for  the  earthquake  monitoring  service.  The database records the base stations, which are located in towns threatened by some known fault lines. Each base station has some seismometers located nearby, which report their measurements every 10 seconds to the base station by radio. The base station forwards the measurements to the central database. There are two types of seismometers  (transverse and reflective) which take different measurements. Reflective seismometers are planted at a certain depth.

Question 1 [12 marks] Map the given EER diagram into a relational database schema. Specify the primary keys and any foreign keys.

Question 2 [7 marks] State the highest normal form your relations are in, and explain why.

Question 3  [4 marks] The CONTROLS and MONITORED_BY relationship types in the EER diagram require total participation of one or both of the participating entity types. Explain how your relational schema enforces those constraints, if it does. Otherwise,  suggest how the constraints could be enforced.

Informationfor questions 4, 5 and 6:

Relation R(A, B, C, D, E, F, H) is given, as well as a set of functional dependencies: F = {A → D, AE → H, DF → BC, E → C, H → E}.

Question 4 [5 marks] List all candidate keys in R (with justification).

Question 5 [10 marks] For each of the following functional dependencies, show whether they are implied by the functional dependencies in F.

A → AD

A → DH

AED → C

DH → C

ADF → E

Question 6 [5 marks] Consider the decomposition of R into three relations:

R1(A, D), R2(E, C) and R3(A, B, E, F, H).

Find (and explain) the highest normal form these relations are in.

Question 7 [3 marks] Under what condition can an attribute of a binary relationship type be moved to one of the participating entity types in an EER schema?

Question 8 [3 marks] How is the three-schema architecture supported in Oracle?

Question 9 [4 marks ] Given two relations, R(A,B,C) and S(C,D,E), specify each of the following relational algebra operations in SQL:

(a) σA=C (R)

(b)  πA,B (R)

(c) R * S

(d)  CℱCOUNT A (R)

Informationfor questions 10, 11 and 12:

Refer to the index (key, pointer) and table (flight number, airline name, departure time, airport code, aircraft) below.

Question 10 [1 mark] Enter the index entry that refers to the record for Aer Lingus flight 140 -- one of a), b), c), …, l) None of the above.

Question 11 [1 mark] Lufthansa flight 144, departing from Munich at 23:05, is inserted into the table. The code for Munich airport is MUC and the aircraft is an Airbus 320. Where does the new index entry go?

a)  At the end of the index.

b)  At the first available free space in the index.

c)  Between the LAX and ORD index entries.

d)  Between the ATL and DME index entries.

Question 12 [6 marks] You need to create an index on a particular field. Given block size of 4 kilobytes, index size of 12 bytes, and pointer size of 8 bytes. How many index records can be stored in one index block?

Informationfor questions 13, 14 and 15

Refer to the following scenario:

•   A table occupies 2,000 blocks.

•   FlightNumber is the primary key.

•   An index on FlightNumber occupies 200 blocks.

•   The WHERE clause of a SELECT statement contains this condition:  FlightNumber = 3988

Question 13 [2 marks] What is the maximum number of blocks necessary to process the SELECT statement? In other words, how many blocks do we need to read for retrieving the result to the        SELECT statement in a worst-case scenario?

Question 14 [2 marks] What is the minimum number of blocks (best-case scenario) necessary to process the SELECT statement?

Question 15 [2 marks] Assume the table has no index. What is the maximum number of blocks (worst-case scenario) necessary to process the SELECT statement?

Question 16 [2 marks] Which type of index can have data pointers at any level?

a)  B trees

b)  B+ trees

c)  Both B trees and B+ trees

Question 17 [2 marks] Which index structure enables an index scan using the bottom level only?

a)  B trees

b)  B+ trees

c)  Both B trees and B+ trees

Question 18 [1 mark] How many SQL statements must be in one transaction?

a)  Exactly one

b)  At least one

c)  At least two

Question 19 [1 mark] After a transaction commits, the transaction can be rolled back:

a)  Always

b)  Sometimes

c)  Never

Question 20 [1 mark] After a rollback, the database restarts a transaction:

d)  Always

e)   Sometimes

f)   Never

Informationfor questions 21 - 24:

Each example violates an ACID property. Select the ACID property violated in each example.

Question 21 [2 marks] A transaction withdraws $25 from each account. However, a system failure caused withdrawals for only half of the accounts to be written to the database.

a)  Atomic

b)  Consistent

c)   Isolated

d)  Durable

Question 22 [2 marks] A transaction removes expired subscriptions. The removals are written in the database, but due to a drive failure, the information is permanently lost.

a)  Atomic

b)  Consistent

c)   Isolated

d)  Durable

Question 23 [2 marks] A transaction updates an account with a foreign key. The foreign key does not match any values of the corresponding primary key and is not NULL.

a)  Atomic

b)  Consistent

c)   Isolated

d)  Durable

Question 24 [2 marks] Two transactions run in parallel to set an account's balance to different values.

a)  Atomic

b)  Consistent

c)   Isolated

d)  Durable

Informationfor questions 25 – 27

The following schedule is given:

Question 25 [2 marks] T1 executes:

a)  A dirty read

b)  A non-repeatable read

c)  A phantom read

d)  Without a conflict

Question 26 [2 marks] T2 executes:

a)  A dirty read

b)  A non-repeatable read

c)  A phantom read

d)  Without a conflict

Question 27 [2 marks] T3 executes:

a)  A dirty read

b)  A non-repeatable read

c)  A phantom read

d)  Without a conflict

Informationfor questions 28 – 33

Enter the value of Z after each schedule executes. Initial values: X = 4, Y = 6, Z = 0.

Question 28 [1 mark] The value of Z after executing Schedule A is

Question 29 [1 mark] The value of Z after executing Schedule B is

Question 30 [1 mark] The value of Z after executing Schedule C is

Question 31 [1 mark] Schedules A and B are

a)  Equivalent

b)  Conflicting

Question 32 [1 mark] Schedules A and C are

a)  Equivalent

b)  Conflicting

Question 33 [1 mark] Schedules B and C are

a)  Equivalent

b)  Conflicting

Informationfor questions 34 – 36

Three relations and a query are given:

student(studentID, fname, lname, dob, major, email, nationality)

grade(studentID, courseCode, grade)

course(courseCode, name, dept)

SELECT fname, lname, dob

FROM student, grade, course

WHERE major = ‘BE(Hons)’ AND dept = ‘CSSE’ AND

student.studentID = grade.studentID AND grade.courseCode = course.courseCode; Consider the following query trees corresponding to the given query: