Database Management Systems



Database Management SystemsNovember 2, 2018Name__________________________True/false.[25 pts]______ Functional dependency analysis is core in the normalization process of a relational schema, but is irrelevant in the analysis of an entity’s attributes in the ER model.______ Multi-valued attributes are not permitted in a 1NF relation.______ First normal form (1NF) still permits nulls to be stored in database tables.______ A null value is just the empty string in the case of a non-numeric type attribute.______ In practical terms, we want other all non-key attributes in the relation to be functionally dependent only on the candidate keys of the relation.______ 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.______ A lossless join decomposition is when rejoining the tables from the decomposition process, tuples are not lost from the original table. ______ 3NF relation will have no transitive functional dependencies in it.______ 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 can be 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 relations that were decomposed because of normalization.______ NOT NULL UNIQUE constraints on an attribute implies the attribute is a candidate key.______ The SQLSTATE system variable holds a 5 digit error or success code string of the last SQL operation.______ An INSERT requires the input values sequence to match the attribute sequence when the attributes are specified in the INSERT statement.______ Triggers provide a mechanism to monitor specific changes to a table and perform additional actions based on the altered data.______ An INSERT requires the input values sequence to always match the attribute sequence in the CREATE TABLE statement.Assume we have a relation with the following schema where a student majors in at most one major, and a major belongs to one department. The attributes are in random order.Students (DeptID, DeptName ,DeptChair, StuId, StuName, StuAddr, StuCity, StuState, StuZip, Major)[15 pts]List all non-trivial functional dependencies [6]?Now decompose this relation into several appropriate 3NF relations based on your functional dependency analysis. Underline the primary keys. [9]Embedded SQL short answers.[10 pts]Describe 3 necessary items of information to establish a database connection. [4]Describe what a cursor is in Java or Python and how it is different from a connection. [3]Why is a for loop necessary to extract data from a result set after a query. [3]Below is a Postgres trigger on the Presidents database similar to 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();Consider the company database schema that tracks trip expense totals per trip (tripTotal).184213517780018369631725380019186071725380EmpS (empId, name, address, zip, phone, deptId, jobTitle, salary)CSZ (zip, city, state)DEPTS (deptId, deptName, deptMgrId)1689735151130TRIPS (tripId, destinationCity, departureDate, returnDate,?empId, tripTotal)EXPENSES (tripId, item,?date, amount)a) Fill in a plpgsql function that recalculates the total expenses for a trip (TRIPS) based on the amounts in the EXPENSES table. [9]CREATE FUNCTION ______________ () RETURNS trigger AS $$ BEGIN UPDATE _________ AS T SET _________ = (SELECT Sum(_________) from __________ AS E WHERE E.________ = NEW.tripID) WHERE _____ .tripId = _______.tripId; RETURN null; END; ______ LANGUAGE plpgsql;b) Create the trigger you would need to ensure the trip totals are kept accurate. [6]CREATE 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 past tenants. Keys are underlined. The attributes should be self-evident. If not, please ask for clarification.17024351625601511935162560TENANTS (TID, name, PriorAddress, PriorZip, Phone)1511935128270CSZ (zip, city, state)1829435170180APTS (AptID, AptNo, AptAddress, AptZip, Bedrooms, MonthRent) RENTED (TID, AptId, StartLeaseDate, EndLeaseDate, Deposit, YTDPaid) // Past renters’ data are kept. // If EndLeaseDate is later than now(), the apartment is currently rented and leased to that date.Give SQL statements for the following queries on the company database schema. Use the R.A. notation below. BE EXPLICIT in the join condition which attributes make the join where necessary.[6 pts each = 36 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 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 those who lived at zip 90210 prior to renting.List names and phones of past renters (no longer renting) who had monthly payments greater than $1500.Get a list of apartment IDs, number of bedrooms, and zip codes that are not currently rented (Hint: set difference or NOT IN -simplest). 17024351625601511935162560TENANTS (TID, name, PriorAddress, PriorZip, Phone)1511935128270CSZ (zip, city, state)1829435170180APTS (AptID, AptNo, AptAddress, AptZip, Bedrooms, MonthRent) RENTED (TID, AptId, StartLeaseDate, EndLeaseDate, Deposit, YTDPaid)// Past renters’ data are kept. // If EndLeaseDate is later than now(), the apartment is currently rented and leased to that date.List names of renters who have rented three different apartments at any point in time. (Hint: use group by having)What is the average rent of apartments in ‘Pittsburgh’?Create a view CURRENTERS that creates a table of current renters with all elements of a US postal address of the currently rented apartment.CREATE VIEW ( ) AS ................
................

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

Google Online Preview   Download