Exam 2 key

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

1 Δεκ 2012 (πριν από 4 χρόνια και 8 μήνες)

208 εμφανίσεις

Fall 2012

CS 370 / IT 376

Exam 2

Page
1

Database Management Systems

11/
2//12

Name_________
Key
______________


1.

True/False
.

[
20

pts
]

__
T
___ A null value has

multiple interpretations and is one
purpose

for first normal form.

__

T
___ A view is an acceptable
way to
offer simpler,

unnormalized table
s

to the user.

_
_
F
__
_

DELETE
-
ing from

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

base table
s

is acceptable.

__
T

___ A view can
provide

an implementation of virtual attributes.

_
_
T

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

__
T

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

_
_
F

__ 3NF relations allow transitive dependencies.

__
T
___
Cardinality in an ER

or UML

diagram can specify

lower and upper limits to numbers of
participants in a relationship
.

__
T

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

key
.

__
F

___ JDBC
uses

a
statement

level interface and access protocol for embedded SQL in Java.

__
T

__
A cursor provides access to exactly one tuple at a time within result set
.

__
T

___ The SQLSTATE system variable
holds a

5 digit

error or success
code

string of the last SQL
operation
.

__
T

___ The UML diagram
rectangles

essentially represent entities, while

relationships are lines.

__
T

___ Object oriented databases
easily
implement inheritance.

__
T

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

__
T

__ Normalization attempts to reduce

or eliminate

redundant facts stored in a database

table
.

__
T

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

__
T

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

__
F
__ The SQL SELECT statements spec
ifies the sequence of operations the server uses to answer the
query.

__
T

___ 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 d
ecomposition
. Underline the primary key
attributes of each table. Language is a multivalued attribute.

Books

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


Language
)

[
1
5

pts]



Books(
ISBN
, Title, CopyrightYear,
LeadAuthor, Publisher)

Authors(
LeadAuthor, AuthorEmail
)

Publishers(
Publisher, PublisherURL
)

Languages(
ISBN, Language
) // actually the different languages would likely require different ISBNs
,
but because it’s specified as a multivalued attribute, this is
the implementation.






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.acctNo
;


INSERT INTO accountsLog


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


);



When the ‘balance’ attribute of the ‘accounts’ table is updated or a new tuple is inserted, the
‘lastChanged’ attribute is updated with a timestamp, offloading the need for timestamp
acquisition by the application.

Further the account information of the o
ld and new balances, along with a timestamp and
username of the application was logged. This is again behind the scenes and not in control by
the applications or even SQL statements.
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 in 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
St
ring;


String input;


int population,votes;


String stateName;


BufferedReader 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
Query = "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(
__
pQuery
___
);


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


input = br.readLine();


ps.
setInt
(
_
___
1
___
, Integer.parseInt(input));



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


input = br.readLine();


ps.
_
setInt
_
(

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 "+population




+" 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 parts of the lines and indicate what needs to be changed.

[4]

Square blue boxes above sh
ow where postgres would be replaced with SqlServer versions



,P.STATE is inserted (b)

Add a similar line for “STATE” (b)
=
Fall 2012

CS 370 / IT 376

Exam 2

Page
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
]

See text boxes ab
ove marked (b)


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

[
3
]

See filled in blanks.


d.
Describe
two problems that could arise

a
t the point of the

database connection

(Statement
labeled “A”)
.

[
4
]


Server doesn’t exist or cannot be reached. Database doesn’t exist. Username and/or password
bad. User not permitted access.



e.
Describe a

viable

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

[
4
]




B
.

Syntax of query flawed,

table and/or attributes referenced do not exist



C
.

Returned attribute value of ‘pop’ is not integer




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 clarification.
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
retu
rn 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, purchasePrice)

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] {*

|

attribute
-
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 co
nsist 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)

Fall 2012

CS 370 / IT 376

Exam 2

Page
5



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

[5
]


SELECT C.name, C.phone

FROM CUSTOMER C

WHERE C.custType=’contractor’


b)

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

[
6
]


SELECT C.name, I.finalTotalBil
l

FROM CUSTOMER C Natural Join INVOICE I

WHERE C.zip=’16652’ AND I.finalTotalBill >=500


c)

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

ever

rented on
e of
these items.

[
6
]

SELECT C.name, C.phone

FROM CUSTOMER C

Natural Join INVOICE I Natural Join RENTED R


Natural Join ITEMDESC D

WHERE D.typeId=’BBQRSTR’ AND D.dateBought<’2005
-
01
-
01’



d)

List customer names who have not rented anything since Jan 1

2010.

[6]


SELECT C.name

FROM CUSTOMER C

WHERE
NOT EXISTS

( SELECT
I.custId







FROM INVOICE I






WHERE I.dateOfInv >=’2010
-
01
-
01’

AND C.custId=I.custId
)


e)

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


SELECT C.Name,
C.phone, SUM(I.finalTotalBill)

FROM CUSTOMER C Natural Join INVOICE I

GROUP BY C.custId



f)

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


SELECT C.name, C.phone, COUNT(R.it
emID)

FROM CUSTOMER C

Natural Join INVOICE I Natural Join RENTED R


Natural Join CSZ Z

WHERE Z.state = ‘PA’ and R.dateReturned is null

GROUP BY C.custID