Exam 2 key

luckyhillockData Management

Nov 29, 2012 (4 years and 10 months ago)

219 views

Fall 20
1
1

CS 370 / IT 376 Exam 2

Page
1

Database Management Systems

11/
4/11

Name__________
Key
_____________


1.

True/False
.

[
1
7

pts
]

______
Disk accesses are
only

a thousand times slower than RAM access.

______ A cylinder on a disk is the set of
spiraled
tracks
found on one surface of a disk platter
.

______ A heap or pile file maintains the data in
sorted

order

by primary key
.

______ 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
clause
s
.

______
The UML diagram blocks essentially represent entit
ies, while relationships are lines
.

______
Object oriented databases implement inheritan
ce.

______ 3NF relations
allow no

transitive dependencies
.

______ Null values have multiple interpretations and is one reason for first normal form.

______ A view is an acceptable
way to pres
ent

unnormalized table
s

to the user.

______
DELETE
-
ing f
rom

a vi
ew that is based on
a row selection of
a
base table
s

is acceptable.

______ A view can
provide

an implementation of virtual attributes.

______
Cardinality in an ER diagram can specify lower and upper limits to numbers of participants in a

relationship
.

______
NOT NULL UNIQUE tags on an attribute implies a
foreign

key
.

______
JDBC
uses

a
statement

level interface

and access protocol

for embedded SQL

in Java
.

______
A cursor provides access to exactly one tuple at a time within re
sult set
.

______
The SQLSTATE system variable
holds a

Unix based integer
error or success
code
.


2.

Disk drives.

a.

If a disk has sectors that are
2048
bytes and tuple sizes are
40

bytes, describe how the data in a
large relational table of those tuples would likely be placed in those sectors. Do not split a
tuple across a sector.

[
4

pts]




b. A disk spins at
7200

rpm (revolutions

per minute) and average read/write heads armature
motion is
10

ms. (ms = 1/1000 sec)

[8 pts]

A
verage seek time = ___________ms
, and a
verage

rotational

latency = ______________
ms.

If there are
100

sectors per track, the transfer time fo
r 5 conti
guous sectors = ______
_
___
___
ms.

The total expected access time for these 5 sectors is ___________________
ms.


Fall 20
1
1

CS 370 / IT 376 Exam 2

Page
2

3.

Below is a JDBC application (
which
should look

vaguely

familiar)
.

Give short answers the
questions b
elo
w referring to this application, or fill in the blanks as directed.

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;


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



Connection conn

= null
;


Statement stmt

= null
;


ResultSet res

= null
;


PreparedStatement ps

= null;



St
ring 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);

//****B****


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));

//****C****


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


input = br.readLine();


ps.setInt(
______
,

Integer.parseInt(input)
);



res = ps.executeQuery();


while(res
.next()){



stateName = res.getString("STATE");



population = res.getInt("POP");

//****D****



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);






}

}

[
3
0

pts]

a.
We want to access an Oracle database instead. What has to be changed?

[4]


org.postgresql.Driver replaced by one from Oracle
and the connections string protocol will be
changed


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

the president’s
political party

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

[6]


Fall 20
1
1

CS 370 / IT 376 Exam 2

Page
3

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

[6]


d.
Describe 2

purpose
s are served by
a database connection.

(don’t use the word connection in
your answer!)

[6]


establish communication to the server,

choose database on server

authorize as user





e.
Describe an exception that can be thrown at the statements labeled “A”, “B”,”C” and “D”
.
[8]



A.

bad URL, db not exist, not authorized



B.

malformed query, table/attribute names wrong, not authorized



C.

input is malformed, reference to ? is out of range



D.

Data not integer/numeric or field name non
-
existent




4.

Explain what the following
SQL trigger

accomplish
es
. Any syntax errors are not intended and
there are some extensions to the grammar.

[
9

pts]

CREATE TRIGGER
StateStatsUpdate1


AFTER
UPDATE (pop) OR DELETE OR INSERT


ON
state
s


FOR
STATEMENT


(
U
PDATE state_stats


SET

value = (SELECT Sum(p
op)
FROM

states)


WHERE attribute = 't
otal
Pop
';


UPDATE state_stats


SET value = (SELECT Count(*) FROM states)


WHERE attribute = ‘stateCount’;


)
;


When any modification happens to the state the trigger runs and updates the states_stats

table
with current population level and number of states in the tables



Fall 20
1
1

CS 370 / IT 376 Exam 2

Page
4

For the
SQL queries

use the following relational schema for a
volunteer organization
that
needs to keep
track of members, projects, and sponsors, and the relationships between them.
Each member is
given a memberID. Each project has a unique name, and has a coordinator, a budget, a timeframe,
and so on. A sponsor may be a business, foundation, government agency, and other source that
donates money for projects. Every project must have
at least one sponsor, but may have several.
Projects are worked on by the members of the organization. Members may work on several
projects. Assume sponsor names and project names are unique, but member names may not be.


Members (
memId
, name, street, zip,

phone, dateJoined)

Corp
Sponsors (
c
Name
, street, zip, phone, type, contact
MemId
)

Projects (
pName
, coord
MemId
, budget, startDate, endDate)

ProjectSponsors (
pName, c
Name
, amountDonated, dateDonated)

ProjectWorkers (
pName, memId
, numberHours
Total
)

CSZ (
zip
,
city, state)


[
6

pts each=
3
6
]

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 consist of <,>,<=,>=, <>,=, AND, OR, BETWEEN value AND value

[NOT] EXISTS ({list

|

SELECT...}),


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

list

|

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

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



a) List all members from the 16652 zip area.


SELECT *

FROM Members m

WHERE m.zip = 16652;










b)

List member names

and phone numbers

who have worked at least 10 hours on the

HuntingdonHH


project.


SELECT m.name, m.phone

FROM Members m NATURAL JOIN ProjectWorkers pw

WHERE pw.numberHoursTotal>=10

AND pw.pName=’HuntingdonHH’






Fall 20
1
1

CS 370 / IT 376 Exam 2

Page
5

c)

For projects that are still ongoing (endDate

is later than today
, use
now()
),
what

are the
project sponsor

corporations and contact person names
?

(Hint: natural joins don’t work
completely across all joins)


SELECT p.pName, cs.cName, m.name

FROM Projects p NATURAL JOIN ProjectSponsors ps


NATURA
L JOIN CorpSponsors cs , Members m

WHERE p.endDate > now()

AND m.memID = cs.contactMemID








d)

What projects have had more than
$
50000 in
total
donations?

(Hint: group by…)


SELECT p.pName
, SUM(p.amountDonated)

FROM ProjectSponsors p

GROUP BY p.cName

HAVING SUM(p.amountDonated)>50000








e)

What project
s have no corporate sponsors? (H
int: use outer join

or except
)


SELECT p.pName

FROM Projects p LEFT OUTER JOIN ProjectSponsors ps

WHERE ps.cName is null


(SELECT p.pName

FROM Projects p)

EXCEPT (SELECT
s.pName

FROM ProjectSponsors s)


f)

How many different cities are represented by the corporate sponsors?

(Hint:
use aggregate
Count(Distinct
a
ttribute
))


SELECT Count(Distinct z.city)

FROM CorpSponsors c NATURAL JOIN CSZ z