Database Management Systems



Database Management Systems11/7/14Name__________________________True/False.[25 pts]______ A 1NF relation has no null values or multi-valued attributes.______ A 1NF relation’s primary key is exactly one attribute.______ Normalization attempts to reduce or eliminate redundant facts stored in a database table tuple.______ Decomposition of a table for normalization results in two or more tables to replace the original table.______ When rejoining the tables from the decomposition process, tuples are not lost from the original table—it is a lossless join decomposition. ______ 3NF decomposition removes transitive functional dependencies.______ In a CREATE TABLE command, attribute entries may be omitted.______ In a CREATE TABLE command, each attribute must have a data type associated with it.______ The FLOAT 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.______ 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.______ Besides a shorthand reference to the table throughout the query, using an alias makes the query run much faster as a result.______ DELETE-ing from a view that is based on a natural join of its base tables is acceptable.______ A view can provide a rejoining of decomposed relations.______ 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 candidate 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 inheritance.______ 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.Answer the short questions below regarding this Postgres trigger on the Presidents database.[10 pts]CREATE FUNCTION resetPopTotal() RETURNS trigger AS $$ BEGIN UPDATE state_stats SET attrValue = (SELECT Sum(pop) from states) WHERE attrName = 'total'; RETURN null; END; $$ LANGUAGE plpgsql; CREATE TRIGGER UpdateTotalTrigger AFTER UPDATE OR DELETE OR INSERT ON states FOR STATEMENT EXECUTE PROCEDURE resetPopTotal();What are the events that would cause the trigger activation? [3]What is the trigger’s granularity? [2]Explain the action of the trigger. Alternatively, how can you characterize/narrate the integrity constraint that is maintained here? [5]JDBC and PHP short answers.[20 pts]a. What is the purpose of the initially loaded database driver in a JDBC application? How is it useful as a separate class library? [4]b. Describe 3 elements of, or established in, a database connection (JDBC or PHP). [6]c. What is the advantage of using a JDBC prepared statement for querying? [3]d. What is a cursor in the result set? And how is it used and advanced? [4]e. Below is a segment of Java code that is processing a result set. Describe what is happening in the assignment statements. [3] ResultSet res = stmt.executeQuery(query); while(res.next()){ stateName = res.getString("STATE"); population = res.getInt("POP"); . . . . Assume the object database schema segment below. Answer the short questions.[10 pts] class Person { attribute pId int; attribute name string; attribute Struct Addr(string street, string city, string state, string zip) address; attribute phone string; string getName( ); void setName(string newName); }; class Student extends Person { attribute credits int; attribute gpa real(3,2); int getCredits( ); void addCredits(int numCredits); relationship Set takesClass Inverse ClassSection::hasStudent; relationship Set earnedGrade Inverse Grade::givenStudent; 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 take? [1] ______How many advisors can a student have? [1] ______Give an example of a composite attribute above. [2] ________________________________How might you redesign the Student class to better represent the gpa attribute? Think how GPAs are calculated. [3]For the remaining questions, use the following relational schema for an apartment rental company. They track tenants or potential tenants. Apartments have a unique id and are spread around in several towns and have different rental rates. They track the current leasing of an apartment to a tenant as well as past leases to each apartment and tenant. Let me know if you have any other clarifications.TENANTS (PID, name, PriorAddress, Priorzip, phone)CSZ (zip, city, state)APTS (AptID, AptNo, AptAddress, AptZip, bdrms, Monthrent) RENTED (PID, AptId, StartDate, EndDate, deposit, YTDPaid) //past renter data are kept; if EndDate is null, the apartment is currently rented if StartDate is null then the apartment is not rented. [5 pts each = 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 those who lived at zip 90210 prior to renting.List all names of those who lived in ‘PA’ prior to renting. List names and phones of past renters (or have switched apartments) who had monthly payments greater than $1500.Get a list of names and PIDs in the tenant table that have not rented. (Hint: Subselect and IN operator or outer join useful.) List names of renters who have rented at lease two different apartments. (Hint: use group by having)Get a list that displays the count of apartments and the total rent that are at each different address. There are many apartment units at the same address/zip.List names and phone numbers who are renting, or have rented, the apartments with the most bedrooms. You will need a subselect to determine what is the largest number of bedrooms. ................
................

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

Google Online Preview   Download