Relational Databases I
Exercise Sheet 11: Application Programming 1 (30 points)
(due by Thursday, January 23, 2014)
Please note: You need 50% of all exercise points to receive the Studienleistung. Solutions have to be turned in on paper until Thursday before the lecture either in the lecture hall or into our mailbox (Informatikzentrum 2nd floor). Please write your matriculation numbers and your tutorial group number on your solutions! You may answer in German or English. You need to pass both the exercises and the exam for the RDB 1 module! Unless otherwise specified: Always use your own words!
Exercise 1 – Understanding Application Programming (11 points)
a) Briefly explain which problems may arise with updatable views. (2 points)
b) What is a symmetric view? What does it provide over non-symmetric views? (2 point) c) Briefly explain the advantages and disadvantages of using a materialized view. (2 points)
d) Provide an SQL statement to manually update the materialized view powered_persons. (1 point) e) Assume you want to speed up access to your production database (frequent reads, updates, and
inserts) and you have unlimited disk space: Is it a good idea to create indexes for each possible combination of attributes on every table? Briefly explain your answer! (2 points)
f) What is the impedance mismatch? Briefly explain why the impedance mismatch causes problems when writing an application. (2 points)
Exercise 2 – Views (5 points)
Assume you have a database in your company that contains sensible data in terms of privacy. Solve the following exercises with this scenario in mind:
1) Give an example of a scenario where views may help with solving privacy issues in your database.
2) Provide an updatable view (you might need to adjust your scenario) that solves these issues. Briefly explain why and how the view solves the problem.
3) Provide an SQL statement for a materialized view that is equivalent to the view from exercise 2).
Briefly explain what your statement does.
Exercise 3 – Indexes (6 points)
Given the following database schema (the dom function is not given and no attribute is unique):
Comic(issue NOT NULL, title, pages, publisher) SuperPower(name NOT NULL, effect)
PoweredPerson(alias NOT NULL, firstname NOT NULL, lastname, evilness_level) SuperVillain(alias → PoweredPerson NOT NULL)
HasPowerSince(comic → Comic NOT NULL, super_villain → SuperVillain NOT NULL, power → SuperPower NOT NULL)
Relational Databases I
1) Provide an SQL query for selecting the first and last names of all super villains having the super power Mind Bullets since the Tenacious comic.
2) Provide SQL statements for creating all necessary indexes to speed up the SQL query from 1).
Do not define an index for a (set of) attribute(s) that already has an index defined.
Exercise 4 – Transactions (8 points)
a) Briefly explain each property of the ACID principle. (4 points) b) Why is the ACID principle needed in databases? (1 point)
c) Assume the auto-commit mode of your database is disabled, provide a statement for each of the following tasks: (3 points)
i. Revert all changes made in the current workflow.
ii. Revert all changes made in the current workflow up to SAVEPOINT experimental.
iii. Make all changes in the current workflow permanent and begin with a new workflow.
