Fall 2013 CS 370 / IT 376 Exam 2 Page 6
Database Management Systems
11/1/13
Name________Key_____________
1. True/False.
[20 pts]
___T__ A null value has multiple interpretations and must be eliminated for first normal form.
___ T __ A view is an acceptable way to offer simpler tables with customized attribute names to the user.
___ T __ DELETE-ing from a view that is based on a natural join of its base tables is not acceptable.
___ T __ A view can provide an implementation of multivalued attributes.
___ T __ In an object database, the database objects have persistent storage in secondary storage.
__ F ___ In an object database, an object is related to other objects through repeated, common values like in relational databases.
___ T __ 3NF relations remove transitive functional dependencies.
___ T __ Cardinality in an EER or UML diagram can specify lower and upper limits to numbers of participants in a relationship.
___ F __ NOT NULL UNIQUE tags on an attribute implies the attribute is a foreign key.
__ T ___ JDBC uses a call level interface and access protocol for embedded SQL in Java.
___ T __ A cursor provides active 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 ___ Isa “relationships” in an EER diagram utilizes inheritance.
___ F __ Functional dependency analysis is used for relationship analysis.
___T__ Normalization attempts to reduce or eliminate redundant facts stored in a database table tuple.
___ T _ Decomposition of a table for normalization results in two or more tables to replace the original table.
___ F __ An INSERT requires the values sequence to match the attribute sequence when the table was created.
___F__ The SQL SELECT statement specifies the exact 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.
2. Principles of mapping ER diagrams to relational schemas. Fill in the blanks with relation and attribute names.
[15 pts]
a. If you have an entity E with attributes A, B and C in your ER diagram, with A as the primary key, what is its corresponding relation in relational schema? [3]
____E____ ( ____A, B, C_____________ )
b. If entity F has attributes G, H and I with G as the primary key, but is related to entity E in a 1-many relationship, what is its corresponding relation? [4]
___F____ ( _____G, H, I, A________________ )
c. If entity L with attributes M, N and P with M as the primary key, and it is related to entity E in a many-many relationship called R, what is the corresponding relation that properly establishes the relationship? [4]
___R____ ( _______A,M__________________ )
d. If entity L’s attribute P is multivalued, show the relations that implement the handling of multivalued attributes. [4]
____L____ ( _____M,N_______________ )
____LP____ ( ______M, P_____________________ )
3. Answer the short question below regarding this trigger. Any syntax errors are not intended and there may have been some liberties taken with the grammar.
[10 pts]
CREATE TRIGGER LogIt
AFTER UPDATE (balance) OR INSERT OR DELETE
ON accounts
FOR ROW
(UPDATE accounts
SET lastChanged = Now
WHERE acctNo = NEW.acctNo;
INSERT INTO accountsLog
VALUES (NEW.acctNo, NEW.balance, OLD.balance, Now, Username);
);
a. If an INSERT INTO accounts (acctNo, balance) VALUES (98765,1000.00) statement is executed, explain the actions of the trigger. [3]
The INSERT is followed by an UPDATE the same table that also sets a timestamp. The action is then logged into accountsLog with the new account number and balance (old balance will be NULL along with the timestamp and the user who made the change.
b. If an UPDATE accounts SET balance = balance*1.005 statement is executed, explain the actions of the trigger. [4]
Every account balance is increased by 0.5%. and for each account the updated timestamp is also applied. The log is updated with old and new balances with time stamp. Every account causes the trigger to fire.
c. Could this trigger loop forever since it is triggered on table accounts and it then updates table accounts? Why or why not? [3]
NO, the trigger updates the lastChanged attribute and not the balance attribute which won’t cause a loop
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 = null;
String input =null;
int population,votes;
String stateName = null;
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"); //****A****
conn = DriverManager.getConnection(connString ); //****B****
stmt = conn.createStatement();
res = stmt1.executeQuery(query);
while (res.next()){
System.out.println(res.getString("PRES"));
}
ps = conn.prepareStatement( pQuery );
System.out.print("Enter minimum population level: ");
input = br.readLine();
ps.setInt(1, Integer.parseInt(input)); //****C****
System.out.print("Enter maximum population level: ");
input = br.readLine();
ps.setInt( 2, Integer.parseInt(input));
res = ps.executeQuery();
while(res.next()){ //****D****
stateName = res.getString("STATE");
population = res.getInt("POP");
votes = res.getInt("STATES_VOTES"); //****E****
System.out.println(stateName + " has population "+population
+" and holds "+votes+" electoral votes");
}
} catch (Exception e) {
System.out.println(e.toString());
}
System.exit(0);
}
}
a. What is the purpose of the Java code line labeled “A”. [4]
This loads the driver software dynamically matching the database server type to the Java code
b. List 3 ways the connection action in Java code line labeled “B” can fail. [6]
Wrong path, wrong protocol, wrong username or unauthorized, bad password, network not available
c. Explain the actions of line labeled “C”. [3]
It fills in the first ? with a numerically converted string into the SQL statement
d. Explain the next() method in the line labeled “D” [4]
The cursor is advanced to the next or first tuple (if first loop). Access to the result set is limited to this tuple. If there are no more tuples, null/false is returned.
e. Describe two exceptions that can be thrown by a resultSet.getInt(“attrName”) method call such as found in line labeled “E”. [3]
bad attribute name, attribute can be made as an integer, resultset tuple not exist
For the remaining questions, use the following relational schema for a music albums database. Keys are (mostly) underlined. The attributes should be self-evident. If not, please ask for clarification. For a given music track, we code the title, its play length in time (minutes:seconds), its genre (pop, metal, jazz, etc.) and a 5 star maximum rating. The musicians, singers and instrumentalists are all listed in on their contribution to the track. A person may have 1 or more listing for a track. For example someone may both sing and play the piano. The album is a collection of tracks. An album is distributed and owned by a company called the label and has a producer and an engineer.
PEOPLE (PID, name, address, zip, phone)
CSZ (zip, city, state)
TRACKS (trID, title, length, genre, rating, albID) //trID is unique across all albums
ALBUMS (albID, albumTitle, year, label, prodPID, engPID, length, price)
CONTRIBS (trID, PID, role)
[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, IN (SELECT…)
[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)
(Select1 {UNION | INTERSECT | EXCEPT} Select2)
a) List all names and phone numbers of people from zip 90210. [5]
SELECT P.name, P.phone
FROM PEOPLE P
WHERE zip = ‘90210’; --may or may not be quoted, alias not necessary
b) List album titles and labels and producer names with a list price of more than $18. [5]
SELECT A.albumTitle, A.label, A.price, P.name
FROM Albums A, People P
WHERE A.price > 18 AND A,prodPID=P.PID
c) List all the musicians by name and what they played or contributed to on all jazz type tracks. [5]
SELECT P.name, C.role
FROM TRACKS T NATURAL JOIN CONTRIBS C
NATURAL JOIN PEOPLE P
WHERE T.genre = ‘JAZZ’
d) Get a list of names and addresses of people who produced OR engineered an album, but did not perform on any track. (Hint: subselect and set operations are very helpful). [6]
SELECT P.name, P.address, Z.city, Z.state, Z.zip
FROM PEOPLE P
WHERE P.PID IN
(SELECT A.prodPID
FROM ALBUMS A
UNION
SELECT B.engPID
FROM ALBUMS B
EXCEPT
SELECT C.PID
FROM CONTRIBS
)
e) List names of musicians who have contributed in at least two different roles on the same tracks with ratings 4 or higher. (Use group by… having and not a self-join). [6]
SELECT P.name
FROM PEOPLE P NATURAL JOIN CONTRIBS C NATURAL JOIN TRACKS T
WHERE T.rating>4
GROUP BY C.trID, C.PID
HAVING COUNT(DISTINCT C.role)>=2
f) What is the average price of albums for each year of release (show years), but only for albums with 6 or more tracks and length of 30 or more minutes. (Need a subselect and group by having.) [8]
SELECT AVG(A.price), A.year
FROM ALBUMS A
WHERE A.length >=30 and A.albID IN
(SELECT T.albID
FROM TRACKS T
GROUP BY T.albID
HAVING COUNT (*)>=6
)
GROUP BY A.year