代写INFO2820编程、代做data编程、SQL程序语言调试

日期：2020-12-04 09:21

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: