Database Management Systems



Database Management Systems11/1/13Name________Key_____________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.Principles of mapping ER diagrams to relational schemas. Fill in the blanks with relation and attribute names.[15 pts]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_____________ )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________________ )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__________________ )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_____________________ )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); );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.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.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 loopBelow 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 codeb. 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 availablec. Explain the actions of line labeled “C”. [3]It fills in the first ? with a numerically converted string into the SQL statementd. 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 existFor 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 albumsALBUMS (albID, albumTitle, year, label, prodPID, engPID, length, price)CONTRIBS (trID, PID, role) [35 pts]Quick syntax for SQL, where [] means optional, {op1|op2|...} means choiceSELECT [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] NULLAggregate 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.phoneFROM PEOPLE PWHERE zip = ‘90210’; --may or may not be quoted, alias not necessaryList album titles and labels and producer names with a list price of more than $18. [5]SELECT A.albumTitle, A.label, A.price, P.nameFROM Albums A, People P WHERE A.price > 18 AND A,prodPID=P.PIDList all the musicians by name and what they played or contributed to on all jazz type tracks. [5]SELECT P.name, C.roleFROM TRACKS T NATURAL JOIN CONTRIBS C NATURAL JOIN PEOPLE PWHERE T.genre = ‘JAZZ’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 PWHERE P.PID IN (SELECT A.prodPID FROM ALBUMS A UNION SELECT B.engPID FROM ALBUMS B EXCEPT SELECT C.PID FROM CONTRIBS)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.nameFROM PEOPLE P NATURAL JOIN CONTRIBS C NATURAL JOIN TRACKS TWHERE T.rating>4GROUP BY C.trID, C.PIDHAVING COUNT(DISTINCT C.role)>=2What 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.yearFROM ALBUMS AWHERE A.length >=30 and A.albID IN (SELECT T.albID FROM TRACKS T GROUP BY T.albID HAVING COUNT (*)>=6)GROUP BY A.year ................
................

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

Google Online Preview   Download