Exam Databases 25 March 2014 (12:00-14:45) Total points: 33 (excluding one bonus point) Grade: (11 + 3*points) / 11

Exam Databases 25 March 2014 (12:00-14:45) Total points: 33 (excluding one bonus point) Grade: (11 + 3*points) / 11

Please answer all questions in English.

If you did the partial exam this year, then we will use your score there, if it is larger than the total you obtain on questions (1) and (2) in this exam (i.e. you might skip questions 1+2 now if your partial exam score is good).

1 Conceptual Design

Consider the following case: You are building a simplified library database.

The library owns (physical) books that are stored on shelves and loaned to customers. Books have an ISBN number, title and publication date. They are published by a publisher (which has a name and phone number).

Books are written by one or more authors (who have a first- and last-name and year of birth). Note that in case of a book written by multiple authors, their order matters.

Each of the books is represented by a catalog entry; think of an

old-fashioned card file as a model of this. Assume that there is only one

“title” card for each book in the catalog, but there can be many physical copies of that book on the shelves. Call the title card class a

“CatalogEntry” and the physical book class a “BookOnShelf.” For each copy of a book on shelf the library keeps a copyNumber and the date on which that copy was acquired by the library.

The library loans books to customers, who have a firstname, lastname, and a phone number. For each loaned book, the library keeps a record (which is also kept after the book is returned). This loan record stores the scannerID of the scanner used to checkout the book, the date when the book was loaned and a dueDate is set for the book to be returned. When the book is in fact returned, the returnDate is also stored.

Question 1(a): (3 points) Provide a conceptual database model in the form of an UML Class Diagram. Explain the most important design choices and document relevant assumptions.

Question 1(b): (2 points) Give the associated relational schema, indicating relations R(A1,..) with their attributes Ai, primary keys

underlined, and foreign key relationships (). Separately also comment on NULLable attributes (if any), and constraints that a relational database could check, including candidate keys.

Question 1(c): (1 points) How (and using what constraints) can a relationship between entities A and B with cardinalities 1..1 to 0..* be modeled in the relational model?

2 Normalization

Given relation R(A,B,C,D,E,F,G) and the functional dependencies F = {A  BC, C  DG, AD  FG, F  B, G  B}

Show your intermediate steps in all the answers.

Question 2(a): (2 points) Create a minimal (canonical) set of FDs for R.

Question 2(b): (2 points) List the minimal key(s) of R. Argue why there are no other minimal keys than the ones you have found.

Question 2(c): (2 points) Is R in 3NF? If not, create a decomposition of R that is, and explain if all dependencies are preserved.

Question 2(d): (2 points) Is R in BCNF? If not, create a decomposition of R that is, and explain if all dependencies are preserved.

3 SQL

Consider the following schema:

Suppliers(sid, sname, saddress) Parts(pid, pname, color)

Catalog(sidSuppliers, pidParts,cost)

The primary keys are underlined and  Rel indicates a foreign key relationship with the primary key of Rel.

Question 3(a): (2 points) Find the names of the cheapest suppliers of part 42 (i.e. pid=42).

Question 3(b): (2 points) Find the pids of parts that are supplied by only one supplier. (1 bonus point if you provide a query using group by and a query using exists)

Question 3(c): (2 points) Find supplier names who sell all black parts

4 Transactions

Question 4(a): (2 points) Describe the most efficient way of deciding whether a schedule is conflict serializable. Use this method on the below schedule:

T1: R(Y) W(V)

T2: W(V) W(Z) T3: R(V) R(Z) R(Y)

Question 4(b): (2 points) Insert explicit lock and unlock actions (S=shared, X=exclusive,U=unlock) in the schedule to show that the schedule can be done with 2 Phase Locking.

Can the schedule be done with with Preclaiming 2 Phase Locking?

Question 4(b): (1 points) Describe the phenomenon of cascading rollbacks? When do they happen?

Question 4(c): (2 points) Name and briefly describe at least 3 isolation levels supported by SQL. What is the purpose of changing (lowering) the isolation level?

5 Database APIs

Question 5(a): (2 points) Describe the problem of SQL injection, how it can occur and how can it be prevented (for example in JDBC).

Question 5(b): (2 points) The original ANSI SPARC architecture identified three levels of database modeling; name them and briefly describe them.

Question 5(c): (2 points) Explain what the term “impedance mismatch”

refers to and discuss LinQ in this respect.