Database Management Systems



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 tables to the user.

______ DELETE-ing from a view that is based on a simple natural join of its base tables 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 upper 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 provides 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 are 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 database 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.

2. Normalize the relation below by appropriate decomposition. Underline the primary key attributes of each table. Language is a multivalued attribute.

Books (ISBN, Title, CopyrightYear, LeadAuthor, Publisher, PublisherURL, AuthorEmail,

Language)

[15 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

(UPDATE accounts

SET lastChanged = Now

WHERE acctNo = NEW.acctNo;

INSERT INTO accountsLog

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

);

4. Below is a JDBC application (which should look vaguely familiar). Give short answers the questions below 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 connString;

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 PRESIDENTS P";

String pQuery = "SELECT * FROM STATES WHERE POP BETWEEN ? AND ?";

connString = "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 "+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]

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]

c. There are three blanks in the code above. Fill them in to make the prepared statement retrieval work. [3]

d. Describe two problems that could arise at the point of the database connection (Statement labeled “A”). [4]

e. Describe a viable exception that can be thrown 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 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 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, crCardNo)

ITEMSONHAND(typeID, description, totalOwned, dailyRentRate)

ITEMDESCR(itemID, typeID, condition, dateBought, purchasePrice)

RENTED(invID, itemID, dateReturned) – (dateReturned is null if still rented)

PAYMENT(invID, pmtThhype, crCardOrCheckNo, amount, date)

[35 pts]

Quick syntax 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 functions: COUNT(*|[DISTINCT] attr), MIN(attr), MAX(attr), SUM(attr), AVG(attr)

a) List all names and phone numbers of ‘contractor’ customer type. [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 numbers of customers who have ever rented one of these items. [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 customer names from Pennsylvania (‘PA’) who have at least one item not returned but list the number of unreturned items. [6]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download