Exam 2

outstandingmaskΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 9 μήνες)

235 εμφανίσεις

Fall 2012

CS 370 / IT 376 Exam 2

Page
1

Database Management Systems

11/
2//12

Name__________________________


1.

True/False
.

[
20

pts
]

______ A null value has

multiple interpretations and is one
purpose

for first normal form.

______ A view is an acceptable
way to
offer simpler,

unnormalized table
s

to

the user.

______
DELETE
-
ing from

a vi
ew that is based on
a
simple natural join of its

base table
s

is acceptable.

______ A view can
provide

an implementation of virtual attributes.

______
In an object database, the objects in object storage are persistent
in secondary storage.

______ In an object database, an object is related to a set of other objects through explicit relationship
clauses.

______ 3NF relations allow transitive dependencies.

______
Cardinality in an ER

or UML

diagram can specify lower and u
pper limits to numbers of
participants in a relationship
.

______
NOT NULL UNIQUE tags on an attribute implies
the attribute is
a
candidate

key
.

______ JDBC
uses

a
statement

level interface and access protocol for embedded SQL in Java.

______
A cursor provi
des access to exactly one tuple at a time within result set
.

______ The SQLSTATE system variable
holds a

5 digit

error or success
code

string of the last SQL
operation
.

______ The UML diagram
rectangles

essentially represent entities, while relationships a
re lines.

______ Object oriented databases
easily
implement inheritance.

______ Functional dependency analysis
is at the center of the
normalization of a relational
table.

______ Normalization attempts to reduce

or eliminate

redundant facts stored in a dat
abase

table
.

______ Decomposition of a table for normalization results in two or more tables to replace the original
table.

______ A normalization goal is a loss
-
less join decomposition.

______ The SQL SELECT statements specifies the sequence of operations

the server uses to answer the
query.

______ Triggers provide a mechanism to monitor specific changes to a table and take perform additional
actions based on the altered data.

Fall 2012

CS 370 / IT 376 Exam 2

Page
2


2.

Normalize
the

relation

below

by appropriate decomposition
. Underline the prim
ary key
attributes of each table. Language is a multivalued attribute.

Books

(
ISBN
,
Title, CopyrightYear,
Lead
Author
,
Publisher
,
PublisherURL
,
AuthorEmail
,


Language
)

[
1
5

pts]
















3.

Explain what the following SQL trigger accomplishes. Any

syntax errors are not intended and
there
may be

some
liberties taken with

the grammar.

[
10

pts]

CREATE TRIGGER
WatchIt


AFTER
UPDATE (
balance
) OR INSERT


ON
accounts


FOR
STATEMENT


(U
PDATE
accounts


SET

lastChanged = Now


WHERE
acctNo

=
NEW.acctN
o
;


INSERT INTO accountsLog


VALUES (NEW.acctNo, NEW.balance, OLD.balance, Now, Username);


);



Fall 2012

CS 370 / IT 376 Exam 2

Page
3



4.

Below is a JDBC application (
which
should look

vaguely

familiar)
.

Give short answers the
questions belo
w referring to this application, or fill i
n the blanks as directed.

[20 pts]

import java.io.*;

import java.sql.*;

public class dbdemo {



public static void main (String[] args) throws IOException{


String conn
String;


String input;


int population,votes;


String stateName;


BufferedR
eader br = new BufferedReader( new InputStreamReader(System.in));



Connection conn

= null
;


Statement stmt

= null
;


ResultSet res

= null
;


PreparedStatement ps

= null;



String query = "SELECT P.PRES FROM PRESIDENT
S

P";


String p
Qu
ery = "SELECT * FROM STATE
S

WHERE POP
BETWEEN ? AND ?";


conn
String = "jdbc:postgresql://itcsserver/pres
?user=guest&password=guest
";



try {


Class.forName("org.postgresql.Driver");


conn

= DriverManager.getConnection(
connectionString );

//****A****


stmt = conn
.createStatement();



res

= stmt1.executeQuery(query);



while (res
.next()){



System.out.println(res
.getString("PRES"));


}


ps = conn.prepareStatement(
_____________
);


System.out.print("Enter low population level: ");


input = br.readLine();


ps.
setInt
(
_______
, Integer.parseInt(input));



System.out.print("Enter high population level: ");


input = br.readLine();


ps.
_______
(

2
,

Integer.parseInt(input)
);



res = ps.executeQuery();


//****
B
****


while(res
.next()){



stateName = res.getString("STATE");



population = res.getInt("POP");

//****
C
****



votes = res.getInt("STATES_VOTES");



System.out.println(stateName + " has population "+popul
ation




+" and holds "+votes+" electoral votes");


}


} catch (Exception e) {



System.out.println(e.toString());


}


System.exit(0);






}

}

a.
We want to access a

database on a
SQL Server

instead. What
two lines must

changed?

Circle
the par
ts of the lines and indicate what needs to be changed.

[4]




b. Show the modifications
in the first retrieval that are
necessary to

retrieve and

display

the
president’s
home state

with the president’s name. Make
all
the edits clear!

[
5
]


Fall 2012

CS 370 / IT 376 Exam 2

Page
4

c
.
There are thr
ee blanks in the code above. Fill them in to make the prepared statement retri
e
val
work.

[
3
]


d.
Describe
two problems that could arise

a
t the point of the

database connection

(Statement
labeled “A”)
.

[
4
]







e.
Describe a

viable

exception that can be t
hrown at the statements labeled
“B” and ”C”.

[
4
]




B
.



C
.




For the
SQL queries

use the following relational schema
for a rental company database. Keys are
(mostly) underlined. The attributes should be self
-
evident. If not, please ask for clarificat
ion.
Customers may rent several items at a time and they are all listed under one invoice. Payment is
due upon return of the items. Credit cards are used in lieu of a deposit and the hold is released on
return of the items, but payment can be by check as
well.


CUSTOMER
(
custID
, name, address, zip, phone, custType)

CSZ(
zip
, city, state)

INVOICE(
invID
, custID, dateOfInv,
finalTotalBill
,
cr
CardNo)

ITEMSONHAND
(
typeID
, description, totalOwned, dailyRentRate)

ITEMDESCR(
itemID
, typeID, condition, dateBought, purc
hasePrice)

RENTED
(invID, itemID, dateReturned)



(dateReturned is null if still rented)

PAYMENT
(
invID,
pmtT
hh
ype,

c
rCardOrCheckNo, amount, date)


[
35

pts
]

Quick s
yntax for SQL, where [] means optional, {op1|op2|...} means choice

SELECT [DISTINCT] {*

|

attr
ibute
-
list

|

aggregate functions}...

FROM
table {, table | NATURAL JOIN table | LEFT OUTER JOIN table
{USING
(attr)

| ON condition}
}*

WHERE condition

[GROUP BY attr [HAVING condition]]


SQL conditions consist of <,>,<=,>=, <>,=, AND, OR, BETWEEN value AND
value

[NOT] EXISTS ({list

|

SELECT...}),


rel
-
op {ANY|SOME|ALL} ({

list

|

SELECT...}), IS [NOT] NULL

Aggregate functions: COUNT(*
|
[DISTINCT]
attr
), MIN(attr), MAX(attr), SUM(attr), AVG(attr)



a) List all names and phone numbers of ‘contractor’ customer ty
pe.

[5
]

Fall 2012

CS 370 / IT 376 Exam 2

Page
5
















b)

List names of customers from zip 16652 who have had
single
invoices of at least $500.

[
6
]

















c)

The
large barbeque roasters (typeId=BBQRSTR
) bought before 2005 were discovered to
contain asbestos. List names and phone numb
ers of customers who have

ever

rented one of
these items.

[
6
]



Fall 2012

CS 370 / IT 376 Exam 2

Page
6


d)

List customer names who have not rented anything since Jan 1 2010.

[6]

















e)

List each customer by name and phone number and their total invoice value. [6]

















f)

List cus
tomer names from Pennsylvania (‘PA’) who have at least one item not returned but
list the number of unreturned items. [6]