Database Management Systems



Database Management Systems11/6/15Name__________________________True/False.[25 pts]______ A 1NF relation has allows null values and multi-valued attributes.______ A normalized relation’s primary key is exactly one attribute.______ Normalization attempts to identify and then reduce or eliminate redundant facts stored in a database table tuple through decomposition.______ Decomposition of a table for normalization results in two or more tables to replace the original table.______ It is desirable to have a lossless join decomposition. ______ Normalization should remove transitive functional dependencies.______ In a CREATE TABLE command, each attribute must have a data type associated with it.______ The INTEGER type is the best for accounting and monetary representation. ______ Referential integrity ensures that a value stored in the foreign key matches a primary key in the referred table and what to do if that primary key value changes.______ A view is classified as part of the external schema and is an acceptable way to offer simpler tables with customized attribute names to the user._______A view is referred to like other tables in a SELECT._______A view is inefficient and should be used minimally.______ Besides a shorthand reference to the table throughout the query, using an alias also implements the rename capability of relational algebra.______ DELETE-ing from a view that is based on a simple selection of its base table is acceptable.______ A view can provide a rejoining of decomposed relations for the end user.______ In an object database, the database objects must have persistent storage in secondary storage.______ In an object database, an object can be related to many other objects through the set collection.______ NOT NULL UNIQUE tags on an attribute implies the attribute is a foreign key.______ JDBC uses a statement level interface and access protocol for embedded SQL in Java.______ 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, their attributes and methods.______ Isa relationships in an EER diagram represent the object form of relationships.______ An INSERT requires the input values sequence to match the attribute sequence when the attributes are specified in the INSERT statement.______ The SQL SELECT statement specifies not only the operations the server uses to answer the query, but the sequence of steps as well.______ Triggers provide a mechanism to monitor specific changes to a table and perform additional actions based on the altered data.Below is the Postgres trigger on the Presidents database that was shown in class. It updates a population statistic anytime a population value changes in the States table.[15 pts]CREATE FUNCTION resetPopTotal() RETURNS trigger AS $$ BEGIN UPDATE state_stats SET attrValue = (SELECT Sum(pop) from states) WHERE attrName = 'total'; INSERT INTO STATELOG (State,oldPop,newPop) VALUES (OLD.state, OLD.Pop, NEW.Pop); RETURN null; END; $$ LANGUAGE plpgsql; CREATE TRIGGER UpdateTotalTrigger AFTER UPDATE OR DELETE OR INSERT ON states FOR STATEMENT EXECUTE PROCEDURE resetPopTotal();All of your projects could calculate a “balance due” based on the sum of the charges, less the payments. Photography balance due based on event scheduled, photos bought less payments madeUniversity pledges not fulfilled have balance on the pledge not paidThe medical center patients have balance on their account of visits, procedures, prescriptions and insurance paymentsThe theatre has balances due on ticket sales and membership dues.For your project, or any of the four projects, assume there is an attribute (that you’ve been told not to have in your ER diagram) called BalanceDue on the Person table. Rough code a function body that would calculate the balance due based on the sources of the charges and the sources of the payments. Correct syntax of plpgsql is not required; use pseudo-code as necessary. [9]Rough code the triggers you would need to ensure the balance due kept accurate. You may need more than one. [6]JDBC short answers.[12 pts]a. What is the purpose of the initially loaded database driver in a JDBC application? Why is it a separate class library? [3]b. What is happening in a database connection? (And don’t just say that a connection is being made to the database.) [3]Below is a segment of Java code that is processing a result set. ResultSet res = stmt.executeQuery(query); while(res.next()){ stateName = res.getString("STATE"); population = res.getInt("POP"); . . . . c. What is a cursor in the result set? And how is it used and advanced? [3]d. Describe what is happening in the assignment statements in the loop of the code above. [3] Disk drives.a. If a disk has sectors that are 2048 bytes and tuple sizes are 100 bytes, describe how the data in a very large relational table of those tuples would likely be placed in those sectors. Do not split a tuple across a sector; do not consider any indexing structures.[3 pts]b. A disk spins at 6000 rpm (revolutions per minute or 100 rev/sec or 10 msec per revolution) and average read/write heads armature motion is 8 ms. (ms = .001 sec)[6 pts]Average rotational latency = ___________ms, and average seek time = ______________ms. If there are 100 sectors per track, the transfer time for 10 contiguous sectors = ____________ms.The total expected access time for these 10 sectors is ___________________ms.Assume the object database schema segment below. Answer the short questions.[9 pts] class Person { attribute int pId; attribute Struct Name (string fname, char mi, string lname) fullName; attribute string zip; attribute string strAddress; attribute String phone; Name getName( ); void setName(Name newName); }; class Student extends Person { attribute int credits; real getGpa(); int getCredits( ); void addCredits(int numCredits); relationship hasZip Inverse CSZ::hasAddresses; relationship Set enrollsClass Inverse ClassSection::hasStudent; relationship Faculty has Advisor Inverse Faculty::advises; }; What other attributes will a Student data object contain than those listed in the class? [3]How many classes can a student enroll in? [1] ______How many advisors can a student have? [1] ______Give an example of a composite attribute above. [2] ________________________________Name one class being referenced in a relationship in the above code. [1] _______________Name a role being referenced in a relationship. [1] For the remaining questions, use the following relational schema for a music albums database. Keys are 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 with 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”. Each album 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, labelID, prodPID, engPID, length, price)PERFORMERS (trID, PID, role)LABELS(labelID, name, address, zip) [30 pts total]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 attribute-list [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. [3]List album titles and their label names that have a list price of more than $18. [5]List all the performer names and their role on all jazz genre tracks. [5]Get a list of names of people who produced OR engineered an album, AND also performed on any track. [6] List years of the albums and average price of albums for each year, but only list those year and averages if there are at least 5 albums that year. (Hint: use group by having). [5]List names of musicians who have contributed in at least two different roles on tracks that are of the ‘classics’ genre. (Hint: use group by having). [6] ................
................

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

Google Online Preview   Download