Q1 (20)
Q2 (20)
Q3 (20)
Q4 (20)
Bonus (5)
Q5 (20)
Total

Test 1

CSCE 520 – Summer 2015

Name:

Major:

Answer the following questions. Be brief and precise. You have 1hour 15 minutes to finish the test. Undergraduate students must answer questions 1,…, 4; graduate students must answeradditional question5for full credit. The bonus question is open for everyone.

1. 20 points–Data models

(15) Explain what the levels of abstraction are in data systems and why these abstractions help to develop and manage databases.

•View level: application programs hide details of data types.

•Logical level: What is the data?

type employee = record
name : string;
address : string;
salary: real;
end;

•Physical level: How the data is stored?

(5) Explain at which level of abstraction database administrators typically interact with modern database systems. What functionalities are supported at this level?

Logical level: design the database system, express data semantics, and generate relation schemas. For each relation, DB administrators described attributes, their data types,their relationships, and constraints.

2. 20 points –Constraints

Consider the World War II. database that contains the following relations.:

Classes(class, type, country, numGuns, bore, displacement)

Ships(name, class, launched)

Battles(name, date)

Outcomes(ship, battle, result)

Note, the primary keys are underlined.

(5) Write the create table SQL statement for the Outcomes relation, that includes the foreign key constraint for the attributes ship (corresponding to the name attribute in the Ships relation) and battle (corresponding to the name attribute in the Battles relation).

CREATE TABLE Outcomes (

ship CHAR(20) REFERECES Ships(name),

battle CHAR(20) REFERECES Battles(name),

results Date) ;

(10) Write a relational algebra constraint that no ship could haveparticipated in a battle after it was sunk.

All ships and dates that were sunk:

R1:= π ship, date ϭ Battles.name-Outcomes.battle AND result=’sunk’ (Outcomes X Battles)

ϭ R1.ship=Outcome.ship AND R1.date < Battles.date AND Battles.name-Outcomes.battle (Outcomes X Battles X R1) = 

(5) Consider the following two DDL statements:

  1. CREATE TABLE Ships (

nameCHAR(30),

class CHAR(30)CHECK ( class IN(SELECT class FROM Classes)),

launched Date );

  1. CREATE TABLE Ships (

nameCHAR(30),

class CHAR(30)References Classes.class,

launched Date );

Are these two statements equivalent? Why / Why not?

Not.

Statement 2 enforces referential integrity constraint that a ship may only belong to a class that is represented in the Classes relation. This constraint is enforced when 1) a new record is inserted to the Ships relations, 2) a record in the Ships relation is modified, OR 3) a record in the Classes relation is modified or deleted.

Statement 1 enforces the constraint that a ship may only belong to a class that is represented in the Classes relation. This constraint is enforced only when the attribute class of the Ships relation is updated or inserted. It is NOT checked if the Classes relation is modified.

3. 20 points –Queries

Consider again the World War II. database:

Classes(class, type, country, numGuns, bore, displacement)

Ships(name, class, launched)

Battles(name, date)

Outcomes(ship, battle, result)

(5) Assume, that you want to find the classes of the ships that participated in the Guadalcanal battle. You write the following relational algebra query:

class, Ship.name(Battles.name=’Guadalcanal’(Ships ⋈ Battles))

What is wrong with this query?

The query creates the natural join of Ships and Battles. However, the joining attribute (name) means different things in the two relations. In Ships, it is the name of the ship. In Battles, this is the name of the battle. There is no meaningful join of the two relations.

(5) Write the correct relational algebra query for the above question.

class, Ship.name (Outcome.battles=’Guadalcanal’ AND Ship.name=Outcomes.ship (Ships X Outcomes))

(10) Write the SQL query that to list the name of the battles where at least one Japanese ship was sunk.

SELECT distinct O.battle

FROM Outcomes O, Ships S, Class C

WHERE O.ship=S.name AND S.class=C.class AND C.country=’Japan’ AND O.result=’sunk’ ;

4. 20 points

(10) Define the followings:

(Super)Key:a set of attribute names that is a unique identifier for each tuple.

Candidate key:a minimal set of attribute names that is a unique identifier for each tuple, i.e., if any of the attributes removed, the remaining set is not a unique identifier any longer.

Primary key:a chosen candidate key.

(10)Consider the relation R. Show the answers to the following queries:R

A / B
1 / 2
1 / 3

Query 1: (A,B (A=1(R ))

A / B
1 / 2
1 / 3
1 / 2

Query 2:

Select A, B

FROM R

WHERE A=1

------

(BONUS QUESTION)

5 points

Give 2 reasons why null values are not desired in databases.

  • Null values are ambiguous, null because we do not know the value? Null because the value is meaningless? Null because the value is sensitive and we must not release it?
  • Some queries may give incorrect results. Remember, 3-values logic.

5. Graduate students only! 20 points

(10) Describe the operational semantics of the following SQL query over relations R(A,B) and S(B,C):

SELECT A,C

FROM R, S

WHERE R.B = S.B AND C > 6;

Assume that R has n tuples, S has m tuples. What is the complexity of the query?

(10) ACID properties of transactions are important to guarantee correct execution. Consider the simplified transaction of an online travel site that supports airplane ticket reservation and car rental:

Reservation = check_flight_availability, check_car_availabitliy, (if flight_available then reserve_seat else abort), (if car_available then reserve_car else abort) commit;

Show an incorrect execution of the above transaction if the atomicity of the transaction is not guaranteed.

1