辅导INFO2820编程、讲解data编程、SQL程序语言调试 讲解SPSS|辅导Web开发
- 首页 >> Python编程 Paper code: EXAMPLE EXAM 2017, Semester 1 Page 1 of 14
INFO2120/INFO2820
Database Systems 1
Example Exam Script
2017, Semester 1
MARKING SCHEME
This example examination paper consists of 14 pages.
Information:
1. This is a collection of possible exam questions . Some of them are taken from previous
assignments and tutorials, sometimes with slight changes to make them more suitable
for an exam. Question 1 is actually from one of the previous exams.
2. Please note that the questions, the number of sub-questions and the weighting are just
examples. The exam will somewhat differ...
3. The final exam will be a partially-open book exam: Your are allowed to bring one doublesided
A4 sheet of paper of your own notes to the exam, but otherwise no course materials
or textbooks are allowed.
4. There will be common questions to be answered by all streams, plus a few dedicated
questions per stream (either INFO2120 or INFO2820). Check your exam thoroughly for
which parts you are expected to answer. Do not miss any subquestion (the exam will be
printed double-sided) nor answer a question for the wrong stream...
5. Try to answer all questions within the space provided on this question paper.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 2 of 14
Question 1: Database Design [20 points]
The purpose of this question is to test your ability to map an E-R diagram into a relational
schema, and to apply integrity constraints. Note: This question has four parts (a – d).
(a) [10 points] Given the following E-R diagram:
Accident
Person Vehicle
involved
owns
license name
address
model
location date
report_nr
damageamount
year
driver
regno
ISA
Car Truck
engine axes
license_
validUntill
owner
disjoint
Transform the E-R diagram into relations using a graphical notation. Clearly indicate all
primary and foreign keys.
Solution:
license name address validUntil
Person
regno model year owner
Vehicle
regno engine
Car
regno axes
Truck
report nr location date
Accident
driver regno report nr damage amount
Involved
(b) [3 points] The entity types PERSON and VEHICLE are connected via a binary relationship.
Give the SQL CREATE TABLE statements needed to capture both entity types, as well
as the relationship connecting them. Choose appropriate data types and pay specifically
attention to correctly represent the key and the participation constraints of that relationship.
Solution:
CREATE TABLE Person (
license VARCHAR(10) PRIMARY KEY,
name VARCHAR(30),
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 3 of 14
address VARCHAR(50),
validUntil DATE
);
CREATE TABLE Vehicle (
regno CHAR(6)) PRIMARY KEY,
model VARCHAR(10),
year INTEGER,
owner VARCHAR(10) NOT NULL REFERENCES Person(license)
);
(c) [3 points] According to our E/R diagram, a CAR is a special type of VEHICLE. Indicate
whether there is a way to express this with the CREATE TABLE statement, and if yes, how
would you do it.
Solution:
CREATE TABLE Car (
regno CHAR(6) REFERENCES Vehicle ON DELETE CASCADE ON UPDATE CASCADE,
engine VARCHAR(50)
);
(d) [4 points] According to our E/R diagram, a VEHICLE is either a CAR or a TRUCK. Give an
SQL assertion that ensures that this is always true.
Solution:
CREATE ASSERTION AssertVehicleDisjoint CHECK (
NOT EXISTS (
SELECT regno FROM Car
INTERSECT
SELECT regno FROM Truck
) );
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 4 of 14
Question 2: Data Normalisation [16 points]
Consider the following BIGPATIENT table and its functional dependencies:
visitID visitDate patID age city zip provNo provSpeciality diagnosis
V10020 13/2/2015 P1 35 Sydney 80217 D1 Internist Ear Infection
V10020 13/2/2015 P1 35 Sydney 80217 D2 Practitioner Influenca
V93030 20/2/2015 P3 19 Sydney 80113 D2 Gynecologist Pregnancy
V82110 18/2/2015 P2 60 Newcastle 85932 D3 Cardiologist Murmur
Functional dependencies:
PatID → Age, Zip
Zip → City
ProvNo → ProvSpeciality
VisitID → PatID, VisitDate
(a) [2 points] Explain whether the following meaning is expressed by any of the given functional
dependencies:
There can be multiple Zip-codes within the same city.
Solution: According to the given functional dependencies (Zip → City ), this is possible:
It states that a zip code functionally determines the city, so if ou know the zip code,
you also know the city. But note that this does not restrict that different zip codes could
be used for the very same city.
(b) [2 points] One of the functional dependencies is violated in the given BIGPATIENT instance.
Explain which functional dependencies is violated and why.
Solution: No - rows two and three contradict the third FD (ProvNo -¿ ProvSpeciality)
as the same ProvNo is associated with two different specialities;
(c) [4 points] Identify a candidate key for the given table. Show the intermediate steps on
how you got your solution.
Solution: (VisitID, ProvNo, Diagnosis)
(d) [2 points] In which normal form is the table BIGPATIENT?
Solution: 1NF as all attributes are atomic, but there are partial dependencies.
(e) [3 points] Decompose the BIGPATIENT table into two smaller tables. Explain whether
your decomposition is lossless join and dependency preserving
Solution: Multiple possibilities, e.g: R1(vistiID, visitDate, patID, age, zip, provNo,
provSpeciality, diagnosis) and R2(zip, city)
(f) [3 points] Show whether any of your two tables is in BCNF already.
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 5 of 14
Solution: R2(zip, city) is in BCNF as any binary relation is in BCNF.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 6 of 14
Question 3: SQL and Relational Algebra [22 points]
The purpose of this question is to test your knowledge of SQL and relational algebra.
Answer the given questions based on the following relational schema:
Product(model, maker, eclass, price)
ProductType(model, type, category)
Manufacturer(maker, city, region, country)
EnergyClass(eclass, min consumption, max consumption)
(a) [3 points] Write an SQL query that finds the average price of products in the ’electronics’
category, whose energy class (eclass) is at least 2 or above.
Solution: Example solution:
SELECT AVG(price)
FROM Product NATURAL JOIN ProductType
WHERE category='electronics'
AND eclass >= 2
Assume the following example database instance for above’s schema:
Product
model maker eclass price
’T20FV300’ ’Tony’ 1 400
’T34HS510’ ’Tony’ 1 600
’B45’ ’Bundig’ 2 2000
’Ice200’ ’Niele’ 4 400
’ABC tba’ ’ABC’ 3 null
ProductType
model type category
’T20FV300’ ’TV’ ’electronics’
’T34HS510’ ’TV’ ’electronics’
’Ice200’ ’fridge’ ’white goods’
’B45’ ’VCR’ ’electronics’
’ABC tba’ ’TV’ ’electronics’
(b) [2 points] What will be the result of your SQL query from the previous subquestion (a) on
this example database instance?
Solution: Expected result: (2000)
(c) [2 points] Write an SQL query that lists all countries in the database in alphabetical order,
and per country the number of manufacturers from that country.
Solution: Example solution:
SELECT country, COUNT(maker)
FROM Manufacturer
GROUP BY country
ORDER BY country
(d) [4 points] Write an SQL query that answers the following question: Which is the cheapest
TV made by ’Tony’ and what does it cost?
Solution: Example solution:
SELECT model, price
FROM Product NATURAL JOIN ProductType
WHERE maker = 'Tony' AND type = 'TV' AND
price <= ALL ( SELECT price
FROM Product NATURAL JOIN ProductType
WHERE maker = 'Tony' AND type = 'TV' )
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 7 of 14
(e) [3 points] Convert the following query to a non-nested SQL query. [3 marks]
SELECT P.maker
FROM Product P
WHERE P.model IN ( SELECT T.model
FROM ProductType T
WHERE T.type = 'Plasma TV' )
Solution: Example solution:
SELECT maker
FROM Product NATURAL JOIN ProductType
WHERE type = 'Plasma TV'
(f) [4 points] Write in Relational Algebra a corresponding relational algebra query for your
non-nested SQL query from the previous sub-question.
Solution: Example solution:
πMAKER(PRODUCT ✶ (σType=0P lasmaT V 0(ProductType)))
(g) [4 points] Write a SQL OLAP query that computes for each manufacturer from Japan
(country code ’JPN’) the average prices for each of their product types. The query shall
be used to populate the following summary table:
DVDplayer TV VCR . . . Total
ABC . . .
Tony . . .
. . . . . . . . . . . . . . .
Total . . .
Solution: Example solution:
SELECT maker, type, AVG(price)
FROM Product NATURAL JOIN ProductType NATURAL JOIN Manufacturer
WHERE country='JPN'
GROUP BY CUBE(maker,type);
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 8 of 14
Question 4: Transactions [20 points]
The purpose of this question is to test the understanding of transactions.
Given the following relation
Employee(empId:INT, name:VARCHAR(20), department:CHAR(2), salary:INT)
with this example instance:
empId name department salary
12122 Steve IT 75000
12123 Jack HR 80000
12130 Sarah HR 85000
Consider the following hypothetical interleaved execution of two transactions T1 and T2 in a
DBMS where concurrency control (such as locking) is not done; that is, each statement is
executed as it is submitted, using the most up-to-date values of the database contents and
without proper isolation from concurrent transactions:
T1: BEGIN TRANSACTION
T1: SELECT * FROM Employee WHERE department = 'HR'
T2: BEGIN TRANSACTION
T2: SELECT salary INTO :sal FROM Employee WHERE empId = 12130
T1: UPDATE Employee SET salary=salary+1000 WHERE department = 'HR'
T2: UPDATE Employee SET salary=:sal+5000 WHERE empId = 12123
T1: COMMIT
T2: COMMIT
(a) [3 points] Indicate the values returned by the SELECT statements and the final value of
the database.
Solution: T1: sees the two original tuples in ’HR’ of table content: (12123, Jack, HR,
80000) and (12130, Sarah, HR, 85000))
T2: sees the original salary of Jack (empId=12130): 80000
Final DB state: first tuple unmodified, (12123, Jack, HR, 90000), (12130, Sarah, HR,
86000)
(b) [3 points] Indicate which, if any, of the following anomalies are produced by the given
execution. The possible anomalies are: (i) dirty read, (ii) lost update, (iii) unrepeatable
read.
Solution: lost-update problem for T1’s change of the salary of ’Jack’ (empId=12123)
(overwritten by T2)
(c) [2 points] Explain whether the execution is serializable or not.
Solution: non-serializable: the end-effect of this is neither the same as T1 before T2
or T2 before T1
(d) [3 points] In a single sentence each, briefly define the exact meanings of the COMMIT
and the ABORT commands in SQL.
Solution:
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 9 of 14
COMMIT – requests(!) to successfully finish a transaction; might still end in an abort.
This is indicated by the return value of COMMIT
(e) [4 points] What does the ACID acronym stand for? Explain each property briefly.
Solution:
Atomicity Transactions cannot be partially committed, they either happen completely
or not at all
Consistency A transaction takes the database from one valid state to another by
some semantically meaningful transition
Isolation The changes made by one transaction should not be visible to another.
Durability After a system failure the database is guaranteed to be restored to the last
consistent state (i.e., as it was immediately after the last successful COMMIT).
(f) [5 points] Consider the following code fragment from the booking functionality of a carsharing
application:
Members ( memberNo, name, email, booking_stats );
Availabilities car_regno, avail_start, avail_duration, available );
Bookings ( memberNo, bookingNo, car_regno, starttime, duration );
The MEMBERS relation stores information about members of the car sharing organisation.
Besides core attributes of each member, such as name and email, it also contains a
statistics attribute about the number of bookings done per Member.
Members can book cars, as long as those are available for the intended time period.
The AVAILABILITIES table lists all availabilities of cars from the organisation. The last
’available’ attribute is 1, if a given car is available for the time period from ’avail start’ for
’avail duration’. Bookings are stored in the BOOKINGS table.
Members of the car-sharing organisation can book a car with a stored procedure bookCar.
Your colleague has written the following first implementation of that stored procedure, as
shown on the following page.
1. Add BEGIN TRANSACTION, COMMIT AND ROLLBACK statements at the appropriate
placeholders of this function to make it a correct transaction. Where would you
put those commands. Note that you can leave some placeholders ( ) empty.
2. Discuss whether the execution of this stored procedure with auto-commit on would be
correct. Auto-commit would commit each SQL statement individually.
Solution:
1. Statements added in code below.
2. Splitting the code into separately committed statements mean that they do not
operate as a logical unit - examples could be given here for how the ACID properties
would fail to hold.
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 10 of 14
1. PROCEDURE bookCar(member IN INT, car IN VARCHAR, start IN DATE, duration IN INT)
2. AS $$
3. DECLARE
4. car_availability INT;
5. BEGIN
6. BEGIN TRANSACTION
7. /* check whether the car is available */
8. /* assume that there's a overlaps() function */
9. SELECT available_seats INTO :car_availability
FROM Availabilities
WHERE car_regno=:car
AND overlaps(interval(avail_start,avail_duration), interval(start,duration));
10. ____________
11. /* continue the booking only if there is at least one free seat */
12. IF ( :car_availability NOT NULL AND :car_availability > 0 ) THEN
13. ____________
14. /* make the booking */
15. INSERT INTO Bookings VALUES (:member,
(SELECT MAX(bookingNo)+1
FROM Bookings
WHERE memberNo = :member),
:car, :start, :duration );
16. ____________
17. /* change the availability */
18. UPDATE Availabilities
SET available=0
WHERE car_regno=:car AND avail_start=:start;
19. ____________
21. /* adjust the member statistics */
22. UPDATE Members
SET booking_stats = booking_stats + 1
WHERE memberNo=:member;
23. COMMIT;
24. ELSE
25. :my_ticket := NULL; /* to indicate that booking failed */
26. ROLLBACK;
27. END IF;
28. ____________
29. END;
30. $$ LANGUAGE plpgsql
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 11 of 14
Question 5: Review Questions [22 points]
The purpose of this question is to test your understanding of different database concepts that
we have covered throughout the course.
Tick the circle (or circles) corresponding to each correct answer:
(a) [2 points] What is the difference between a database trigger and an SQL assertion?
An SQL assertion is not part of the SQL standard, while triggers are.
A trigger is bound to just a single table, while an assertion can express an integrity
constraint over multiple tables.
A trigger can be used to check for a dynamic integrity constraint, while a SQL
assertion is a static integrity constraint.
A trigger can be used to view maintenance, while a SQL assertion is a dynamic
integrity constraint.
(b) [2 points] Why do database systems need three-valued logic?
Because of NULL values, SQL works with three-value logic as a comparison with
NULL results in a special UNKNOWN value.
Databases do not use three-valued logic.
Three-value logic is needed to test tables to be in Boyce-Codd normal form.
Because of the accent of the lecturer, it is never clear whether something is true,
false - or remains unknown.
(c) [2 points] Select two differences between a SQL WINDOW clause and grouping.
Ranking can be expressed only with GROUP BY.
Grouping works on sets, while windowing allows order per window.
Groups are disjoint, windows can overlap.
Aggregate functions require GROUP BY, while counting requires a SQL window.
(d) [2 points] What is the meaning of the following relational algebra query?
πlocation(Accident)
List all accidents in order of their location.
List accidents which have a known (NOT NULL) location.
List accidents by driver location.
List the locations of all accidents.
(e) [2 points] Which two of the following statements are correct about referential integrity?
Referential integrity is the guarantee that no foreign key value is NULL.
Referential integrity ensures the uniqueness of foreign key values in the dependent
table
Referential integrity ensures that each foreign key value is present in the parent
table.
Referential integrity is a static integrity constraint.
Referential integrity is a dynamic integrity constraint.
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 12 of 14
(f) [2 points] Briefly explain: What is an index and what is it used for?
Solution: An index is an access path or a physical data structure that allows faster
query execution for a given search key.
(g) [2 points] The DBA of your database finds that your application frequently executes the
following query:
SELECT a, b
FROM R
WHERE c > 500
Which index would make this query faster?
A primary index on a could make this query faster.
A secondary index on c could make it faster.
A multi-attribute index on a, b, c could make it faster.
A clustered index on b could make it faster.
(h) [2 points] What are two advantages of stored procedures as compared to client-side
database application development?
A stored procedure is written in SQL, while client applications are written in a
(complex) programming languages.
Stored procedures are executed within the database system close to the accessed
data, avoiding complex network communication between client and server.
Stored procedures are executed faster than compiled client code.
Stored procedures can be used as a central code base in the database server
which can be shared by multiple applications.
The languages, in which stored procedures are written, are very vendor specific.
(i) [2 points] What is the cursor concept and why is it needed in database APIs?
A cursor allows to process a set of return values from a SQL query row-by-row
without the need to load the complete (potentially very large) query result into
the main memory. It also allows programming languages that have no native
support for sets to work with database result sets.
A cursor allows to define dedicated error handling routines for a whole block
of code without the need to write individual condition statements around each
database API call.
A cursor allows to efficiently find individual rows in a very large data set by their
primary key.
The cursor concept allows to iterate over a set of rows and also to jump to specific
rows in a table using their position. This is needed as programming languages do
not directly support SQL, hence the table access must be programmed manually
using such cursors.
(j) [2 points] Is a typical fact table of a star schema in BCNF?
No because there are no non-trivial functional dependencies that hold on a fact
table.
No because a fact table can have functional dependencies between non-key
attributes, while BCNF requires all non-trivial functional dependencies to have a
key on the left side.
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 13 of 14
Yes, because in a fact table, the key is the set of all dimension keys, and the only
functional dependency is that the fact attributes depend on all the dimensions
(the key) - which is what BCNF requires.
There is no definite statement possible about this without an analysis of the actual
schema and fact table. mainly historic as data warehouses are a relatively
new technology which was only lately introduced into the existing IT infrastructures.
(k) [2 points] Select examples of syntactic and semantic transformations that might have to
be made while loading data into a data warehouse.
Semantic transformation: The weekly salary of an employee is mapped and
converted to an annual salary of the employee in the data warehouse.
Semantic transformation: Mapping a varchar(20) attribute to a char(20) data type
in the data warehouse.
Syntactic transformation: A source attribute firstname is mapped to an attribute
givenName in the data warehouse.
Syntactic transformation: The SQL query to retrieve the name of a customer if
mapped to an OLAP MDX query in the data warehouse to retrieve this attribute.
Question 6: Advanced Question (INFO2820 Students Only) [16 points]
Some advanced-only questions:
(a) [2 points] Why does embedded SQL/C require a precompiler?
Solution:
(b) [4 points] Given the following assertion, write corresponding row-level triggers that implement
the corresponding integrity constraint.
CREATE ASSERTION AssertDisjointEmployeeTypes CHECK ( NOT EXISTS (
SELECT empid FROM BankManager
INTERSECT
SELECT empid FROM Secretary
INTERSECT
SELECT empid FROM Teller ) )
Solution:
(c) [3 points] Consider the following Datalog schema:
directFlight(From,To,Distance)
Write a recursive Datalog rule that computes all direct or indirect connections between two
cities. Using this rule, find all cities reachable from London.
Solution:
(d) [3 points] Given the following schedule S:
w1(x)r1(x)w3(z)r2(x)r2(y)r4(z)r3(x)w3(y)
Check whether this schedule is conflict-serializable or not. If the schedule is conflict equivalent
to a serial schedule, choose the equivalent serial order. (Note: ’Ti ¿ Tj’ means Ti
executes before Tj)
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 14 of 14
Solution:
(e) [4 points] Briefly explain the difference between locking and snapshot isolation concurrency
control.
Solution:
INFO2120/INFO2820
Database Systems 1
Example Exam Script
2017, Semester 1
MARKING SCHEME
This example examination paper consists of 14 pages.
Information:
1. This is a collection of possible exam questions . Some of them are taken from previous
assignments and tutorials, sometimes with slight changes to make them more suitable
for an exam. Question 1 is actually from one of the previous exams.
2. Please note that the questions, the number of sub-questions and the weighting are just
examples. The exam will somewhat differ...
3. The final exam will be a partially-open book exam: Your are allowed to bring one doublesided
A4 sheet of paper of your own notes to the exam, but otherwise no course materials
or textbooks are allowed.
4. There will be common questions to be answered by all streams, plus a few dedicated
questions per stream (either INFO2120 or INFO2820). Check your exam thoroughly for
which parts you are expected to answer. Do not miss any subquestion (the exam will be
printed double-sided) nor answer a question for the wrong stream...
5. Try to answer all questions within the space provided on this question paper.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 2 of 14
Question 1: Database Design [20 points]
The purpose of this question is to test your ability to map an E-R diagram into a relational
schema, and to apply integrity constraints. Note: This question has four parts (a – d).
(a) [10 points] Given the following E-R diagram:
Accident
Person Vehicle
involved
owns
license name
address
model
location date
report_nr
damageamount
year
driver
regno
ISA
Car Truck
engine axes
license_
validUntill
owner
disjoint
Transform the E-R diagram into relations using a graphical notation. Clearly indicate all
primary and foreign keys.
Solution:
license name address validUntil
Person
regno model year owner
Vehicle
regno engine
Car
regno axes
Truck
report nr location date
Accident
driver regno report nr damage amount
Involved
(b) [3 points] The entity types PERSON and VEHICLE are connected via a binary relationship.
Give the SQL CREATE TABLE statements needed to capture both entity types, as well
as the relationship connecting them. Choose appropriate data types and pay specifically
attention to correctly represent the key and the participation constraints of that relationship.
Solution:
CREATE TABLE Person (
license VARCHAR(10) PRIMARY KEY,
name VARCHAR(30),
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 3 of 14
address VARCHAR(50),
validUntil DATE
);
CREATE TABLE Vehicle (
regno CHAR(6)) PRIMARY KEY,
model VARCHAR(10),
year INTEGER,
owner VARCHAR(10) NOT NULL REFERENCES Person(license)
);
(c) [3 points] According to our E/R diagram, a CAR is a special type of VEHICLE. Indicate
whether there is a way to express this with the CREATE TABLE statement, and if yes, how
would you do it.
Solution:
CREATE TABLE Car (
regno CHAR(6) REFERENCES Vehicle ON DELETE CASCADE ON UPDATE CASCADE,
engine VARCHAR(50)
);
(d) [4 points] According to our E/R diagram, a VEHICLE is either a CAR or a TRUCK. Give an
SQL assertion that ensures that this is always true.
Solution:
CREATE ASSERTION AssertVehicleDisjoint CHECK (
NOT EXISTS (
SELECT regno FROM Car
INTERSECT
SELECT regno FROM Truck
) );
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 4 of 14
Question 2: Data Normalisation [16 points]
Consider the following BIGPATIENT table and its functional dependencies:
visitID visitDate patID age city zip provNo provSpeciality diagnosis
V10020 13/2/2015 P1 35 Sydney 80217 D1 Internist Ear Infection
V10020 13/2/2015 P1 35 Sydney 80217 D2 Practitioner Influenca
V93030 20/2/2015 P3 19 Sydney 80113 D2 Gynecologist Pregnancy
V82110 18/2/2015 P2 60 Newcastle 85932 D3 Cardiologist Murmur
Functional dependencies:
PatID → Age, Zip
Zip → City
ProvNo → ProvSpeciality
VisitID → PatID, VisitDate
(a) [2 points] Explain whether the following meaning is expressed by any of the given functional
dependencies:
There can be multiple Zip-codes within the same city.
Solution: According to the given functional dependencies (Zip → City ), this is possible:
It states that a zip code functionally determines the city, so if ou know the zip code,
you also know the city. But note that this does not restrict that different zip codes could
be used for the very same city.
(b) [2 points] One of the functional dependencies is violated in the given BIGPATIENT instance.
Explain which functional dependencies is violated and why.
Solution: No - rows two and three contradict the third FD (ProvNo -¿ ProvSpeciality)
as the same ProvNo is associated with two different specialities;
(c) [4 points] Identify a candidate key for the given table. Show the intermediate steps on
how you got your solution.
Solution: (VisitID, ProvNo, Diagnosis)
(d) [2 points] In which normal form is the table BIGPATIENT?
Solution: 1NF as all attributes are atomic, but there are partial dependencies.
(e) [3 points] Decompose the BIGPATIENT table into two smaller tables. Explain whether
your decomposition is lossless join and dependency preserving
Solution: Multiple possibilities, e.g: R1(vistiID, visitDate, patID, age, zip, provNo,
provSpeciality, diagnosis) and R2(zip, city)
(f) [3 points] Show whether any of your two tables is in BCNF already.
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 5 of 14
Solution: R2(zip, city) is in BCNF as any binary relation is in BCNF.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 6 of 14
Question 3: SQL and Relational Algebra [22 points]
The purpose of this question is to test your knowledge of SQL and relational algebra.
Answer the given questions based on the following relational schema:
Product(model, maker, eclass, price)
ProductType(model, type, category)
Manufacturer(maker, city, region, country)
EnergyClass(eclass, min consumption, max consumption)
(a) [3 points] Write an SQL query that finds the average price of products in the ’electronics’
category, whose energy class (eclass) is at least 2 or above.
Solution: Example solution:
SELECT AVG(price)
FROM Product NATURAL JOIN ProductType
WHERE category='electronics'
AND eclass >= 2
Assume the following example database instance for above’s schema:
Product
model maker eclass price
’T20FV300’ ’Tony’ 1 400
’T34HS510’ ’Tony’ 1 600
’B45’ ’Bundig’ 2 2000
’Ice200’ ’Niele’ 4 400
’ABC tba’ ’ABC’ 3 null
ProductType
model type category
’T20FV300’ ’TV’ ’electronics’
’T34HS510’ ’TV’ ’electronics’
’Ice200’ ’fridge’ ’white goods’
’B45’ ’VCR’ ’electronics’
’ABC tba’ ’TV’ ’electronics’
(b) [2 points] What will be the result of your SQL query from the previous subquestion (a) on
this example database instance?
Solution: Expected result: (2000)
(c) [2 points] Write an SQL query that lists all countries in the database in alphabetical order,
and per country the number of manufacturers from that country.
Solution: Example solution:
SELECT country, COUNT(maker)
FROM Manufacturer
GROUP BY country
ORDER BY country
(d) [4 points] Write an SQL query that answers the following question: Which is the cheapest
TV made by ’Tony’ and what does it cost?
Solution: Example solution:
SELECT model, price
FROM Product NATURAL JOIN ProductType
WHERE maker = 'Tony' AND type = 'TV' AND
price <= ALL ( SELECT price
FROM Product NATURAL JOIN ProductType
WHERE maker = 'Tony' AND type = 'TV' )
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 7 of 14
(e) [3 points] Convert the following query to a non-nested SQL query. [3 marks]
SELECT P.maker
FROM Product P
WHERE P.model IN ( SELECT T.model
FROM ProductType T
WHERE T.type = 'Plasma TV' )
Solution: Example solution:
SELECT maker
FROM Product NATURAL JOIN ProductType
WHERE type = 'Plasma TV'
(f) [4 points] Write in Relational Algebra a corresponding relational algebra query for your
non-nested SQL query from the previous sub-question.
Solution: Example solution:
πMAKER(PRODUCT ✶ (σType=0P lasmaT V 0(ProductType)))
(g) [4 points] Write a SQL OLAP query that computes for each manufacturer from Japan
(country code ’JPN’) the average prices for each of their product types. The query shall
be used to populate the following summary table:
DVDplayer TV VCR . . . Total
ABC . . .
Tony . . .
. . . . . . . . . . . . . . .
Total . . .
Solution: Example solution:
SELECT maker, type, AVG(price)
FROM Product NATURAL JOIN ProductType NATURAL JOIN Manufacturer
WHERE country='JPN'
GROUP BY CUBE(maker,type);
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 8 of 14
Question 4: Transactions [20 points]
The purpose of this question is to test the understanding of transactions.
Given the following relation
Employee(empId:INT, name:VARCHAR(20), department:CHAR(2), salary:INT)
with this example instance:
empId name department salary
12122 Steve IT 75000
12123 Jack HR 80000
12130 Sarah HR 85000
Consider the following hypothetical interleaved execution of two transactions T1 and T2 in a
DBMS where concurrency control (such as locking) is not done; that is, each statement is
executed as it is submitted, using the most up-to-date values of the database contents and
without proper isolation from concurrent transactions:
T1: BEGIN TRANSACTION
T1: SELECT * FROM Employee WHERE department = 'HR'
T2: BEGIN TRANSACTION
T2: SELECT salary INTO :sal FROM Employee WHERE empId = 12130
T1: UPDATE Employee SET salary=salary+1000 WHERE department = 'HR'
T2: UPDATE Employee SET salary=:sal+5000 WHERE empId = 12123
T1: COMMIT
T2: COMMIT
(a) [3 points] Indicate the values returned by the SELECT statements and the final value of
the database.
Solution: T1: sees the two original tuples in ’HR’ of table content: (12123, Jack, HR,
80000) and (12130, Sarah, HR, 85000))
T2: sees the original salary of Jack (empId=12130): 80000
Final DB state: first tuple unmodified, (12123, Jack, HR, 90000), (12130, Sarah, HR,
86000)
(b) [3 points] Indicate which, if any, of the following anomalies are produced by the given
execution. The possible anomalies are: (i) dirty read, (ii) lost update, (iii) unrepeatable
read.
Solution: lost-update problem for T1’s change of the salary of ’Jack’ (empId=12123)
(overwritten by T2)
(c) [2 points] Explain whether the execution is serializable or not.
Solution: non-serializable: the end-effect of this is neither the same as T1 before T2
or T2 before T1
(d) [3 points] In a single sentence each, briefly define the exact meanings of the COMMIT
and the ABORT commands in SQL.
Solution:
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 9 of 14
COMMIT – requests(!) to successfully finish a transaction; might still end in an abort.
This is indicated by the return value of COMMIT
(e) [4 points] What does the ACID acronym stand for? Explain each property briefly.
Solution:
Atomicity Transactions cannot be partially committed, they either happen completely
or not at all
Consistency A transaction takes the database from one valid state to another by
some semantically meaningful transition
Isolation The changes made by one transaction should not be visible to another.
Durability After a system failure the database is guaranteed to be restored to the last
consistent state (i.e., as it was immediately after the last successful COMMIT).
(f) [5 points] Consider the following code fragment from the booking functionality of a carsharing
application:
Members ( memberNo, name, email, booking_stats );
Availabilities car_regno, avail_start, avail_duration, available );
Bookings ( memberNo, bookingNo, car_regno, starttime, duration );
The MEMBERS relation stores information about members of the car sharing organisation.
Besides core attributes of each member, such as name and email, it also contains a
statistics attribute about the number of bookings done per Member.
Members can book cars, as long as those are available for the intended time period.
The AVAILABILITIES table lists all availabilities of cars from the organisation. The last
’available’ attribute is 1, if a given car is available for the time period from ’avail start’ for
’avail duration’. Bookings are stored in the BOOKINGS table.
Members of the car-sharing organisation can book a car with a stored procedure bookCar.
Your colleague has written the following first implementation of that stored procedure, as
shown on the following page.
1. Add BEGIN TRANSACTION, COMMIT AND ROLLBACK statements at the appropriate
placeholders of this function to make it a correct transaction. Where would you
put those commands. Note that you can leave some placeholders ( ) empty.
2. Discuss whether the execution of this stored procedure with auto-commit on would be
correct. Auto-commit would commit each SQL statement individually.
Solution:
1. Statements added in code below.
2. Splitting the code into separately committed statements mean that they do not
operate as a logical unit - examples could be given here for how the ACID properties
would fail to hold.
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 10 of 14
1. PROCEDURE bookCar(member IN INT, car IN VARCHAR, start IN DATE, duration IN INT)
2. AS $$
3. DECLARE
4. car_availability INT;
5. BEGIN
6. BEGIN TRANSACTION
7. /* check whether the car is available */
8. /* assume that there's a overlaps() function */
9. SELECT available_seats INTO :car_availability
FROM Availabilities
WHERE car_regno=:car
AND overlaps(interval(avail_start,avail_duration), interval(start,duration));
10. ____________
11. /* continue the booking only if there is at least one free seat */
12. IF ( :car_availability NOT NULL AND :car_availability > 0 ) THEN
13. ____________
14. /* make the booking */
15. INSERT INTO Bookings VALUES (:member,
(SELECT MAX(bookingNo)+1
FROM Bookings
WHERE memberNo = :member),
:car, :start, :duration );
16. ____________
17. /* change the availability */
18. UPDATE Availabilities
SET available=0
WHERE car_regno=:car AND avail_start=:start;
19. ____________
21. /* adjust the member statistics */
22. UPDATE Members
SET booking_stats = booking_stats + 1
WHERE memberNo=:member;
23. COMMIT;
24. ELSE
25. :my_ticket := NULL; /* to indicate that booking failed */
26. ROLLBACK;
27. END IF;
28. ____________
29. END;
30. $$ LANGUAGE plpgsql
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 11 of 14
Question 5: Review Questions [22 points]
The purpose of this question is to test your understanding of different database concepts that
we have covered throughout the course.
Tick the circle (or circles) corresponding to each correct answer:
(a) [2 points] What is the difference between a database trigger and an SQL assertion?
An SQL assertion is not part of the SQL standard, while triggers are.
A trigger is bound to just a single table, while an assertion can express an integrity
constraint over multiple tables.
A trigger can be used to check for a dynamic integrity constraint, while a SQL
assertion is a static integrity constraint.
A trigger can be used to view maintenance, while a SQL assertion is a dynamic
integrity constraint.
(b) [2 points] Why do database systems need three-valued logic?
Because of NULL values, SQL works with three-value logic as a comparison with
NULL results in a special UNKNOWN value.
Databases do not use three-valued logic.
Three-value logic is needed to test tables to be in Boyce-Codd normal form.
Because of the accent of the lecturer, it is never clear whether something is true,
false - or remains unknown.
(c) [2 points] Select two differences between a SQL WINDOW clause and grouping.
Ranking can be expressed only with GROUP BY.
Grouping works on sets, while windowing allows order per window.
Groups are disjoint, windows can overlap.
Aggregate functions require GROUP BY, while counting requires a SQL window.
(d) [2 points] What is the meaning of the following relational algebra query?
πlocation(Accident)
List all accidents in order of their location.
List accidents which have a known (NOT NULL) location.
List accidents by driver location.
List the locations of all accidents.
(e) [2 points] Which two of the following statements are correct about referential integrity?
Referential integrity is the guarantee that no foreign key value is NULL.
Referential integrity ensures the uniqueness of foreign key values in the dependent
table
Referential integrity ensures that each foreign key value is present in the parent
table.
Referential integrity is a static integrity constraint.
Referential integrity is a dynamic integrity constraint.
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 12 of 14
(f) [2 points] Briefly explain: What is an index and what is it used for?
Solution: An index is an access path or a physical data structure that allows faster
query execution for a given search key.
(g) [2 points] The DBA of your database finds that your application frequently executes the
following query:
SELECT a, b
FROM R
WHERE c > 500
Which index would make this query faster?
A primary index on a could make this query faster.
A secondary index on c could make it faster.
A multi-attribute index on a, b, c could make it faster.
A clustered index on b could make it faster.
(h) [2 points] What are two advantages of stored procedures as compared to client-side
database application development?
A stored procedure is written in SQL, while client applications are written in a
(complex) programming languages.
Stored procedures are executed within the database system close to the accessed
data, avoiding complex network communication between client and server.
Stored procedures are executed faster than compiled client code.
Stored procedures can be used as a central code base in the database server
which can be shared by multiple applications.
The languages, in which stored procedures are written, are very vendor specific.
(i) [2 points] What is the cursor concept and why is it needed in database APIs?
A cursor allows to process a set of return values from a SQL query row-by-row
without the need to load the complete (potentially very large) query result into
the main memory. It also allows programming languages that have no native
support for sets to work with database result sets.
A cursor allows to define dedicated error handling routines for a whole block
of code without the need to write individual condition statements around each
database API call.
A cursor allows to efficiently find individual rows in a very large data set by their
primary key.
The cursor concept allows to iterate over a set of rows and also to jump to specific
rows in a table using their position. This is needed as programming languages do
not directly support SQL, hence the table access must be programmed manually
using such cursors.
(j) [2 points] Is a typical fact table of a star schema in BCNF?
No because there are no non-trivial functional dependencies that hold on a fact
table.
No because a fact table can have functional dependencies between non-key
attributes, while BCNF requires all non-trivial functional dependencies to have a
key on the left side.
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 13 of 14
Yes, because in a fact table, the key is the set of all dimension keys, and the only
functional dependency is that the fact attributes depend on all the dimensions
(the key) - which is what BCNF requires.
There is no definite statement possible about this without an analysis of the actual
schema and fact table. mainly historic as data warehouses are a relatively
new technology which was only lately introduced into the existing IT infrastructures.
(k) [2 points] Select examples of syntactic and semantic transformations that might have to
be made while loading data into a data warehouse.
Semantic transformation: The weekly salary of an employee is mapped and
converted to an annual salary of the employee in the data warehouse.
Semantic transformation: Mapping a varchar(20) attribute to a char(20) data type
in the data warehouse.
Syntactic transformation: A source attribute firstname is mapped to an attribute
givenName in the data warehouse.
Syntactic transformation: The SQL query to retrieve the name of a customer if
mapped to an OLAP MDX query in the data warehouse to retrieve this attribute.
Question 6: Advanced Question (INFO2820 Students Only) [16 points]
Some advanced-only questions:
(a) [2 points] Why does embedded SQL/C require a precompiler?
Solution:
(b) [4 points] Given the following assertion, write corresponding row-level triggers that implement
the corresponding integrity constraint.
CREATE ASSERTION AssertDisjointEmployeeTypes CHECK ( NOT EXISTS (
SELECT empid FROM BankManager
INTERSECT
SELECT empid FROM Secretary
INTERSECT
SELECT empid FROM Teller ) )
Solution:
(c) [3 points] Consider the following Datalog schema:
directFlight(From,To,Distance)
Write a recursive Datalog rule that computes all direct or indirect connections between two
cities. Using this rule, find all cities reachable from London.
Solution:
(d) [3 points] Given the following schedule S:
w1(x)r1(x)w3(z)r2(x)r2(y)r4(z)r3(x)w3(y)
Check whether this schedule is conflict-serializable or not. If the schedule is conflict equivalent
to a serial schedule, choose the equivalent serial order. (Note: ’Ti ¿ Tj’ means Ti
executes before Tj)
Question continues on next page.
Paper code: EXAMPLE EXAM 2017, Semester 1 Page 14 of 14
Solution:
(e) [4 points] Briefly explain the difference between locking and snapshot isolation concurrency
control.
Solution: