Database Management Systems - Juniata College



Database Management SystemsNovember 2, 2018Name_________KEY______________True/false.[25 pts]__F___ 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.__ T ___ Multi-valued attributes are not permitted in a 1NF relation.__ F ___ First normal form (1NF) still permits nulls to be stored in database tables.__ F ___ A null value is just the empty string in the case of a non-numeric type attribute.___ T __ 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.___ T __ A 1NF relation has no null values or multi-valued attributes.___ F ___ A 1NF relation’s primary key is exactly one attribute.___ 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.__ T ___ A lossless join decomposition is when rejoining the tables from the decomposition process, tuples are not lost from the original table. ___ T __ 3NF relation will have no transitive functional dependencies in it.___ F __ In a CREATE TABLE command, attribute entries may be omitted.___ T __ In a CREATE TABLE command, each attribute must have a data type associated with it.___ F __ The FLOAT type is the best for accounting and monetary representation. ___ T __ Referential integrity ensures that a value stored in the foreign key matches a primary key in the referred table.___ T __ 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.____ T __A view can be referred to like other tables in a SELECT.___ F __ Besides a shorthand reference to the table throughout the query, using an alias makes the query run much faster as a result.___ F __ DELETE-ing from a view that is based on a natural join of its base tables is acceptable.___ T __ A view can provide a rejoining of relations that were decomposed because of normalization.___ T __ NOT NULL UNIQUE constraints on an attribute implies the attribute is a candidate key.___ T __ The SQLSTATE system variable holds a 5 digit error or success code string of the last SQL operation.___ T __ An INSERT requires the input values sequence to match the attribute sequence when the attributes are specified in the INSERT statement.___ T __ Triggers provide a mechanism to monitor specific changes to a table and perform additional actions based on the altered data.___ F __ 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]?deptID -> DeptName, DeptChairStudId -> StuName, StuAddres, StuCity, StuState, Stu,Zip, Major, DeptId, DeptName, DeptChairStuZip -> StuCity, StuStateMajor -> deptID, DeptName,DeptChairDeptName -> DeptId, DeptChairNow decompose this relation into several appropriate 3NF relations based on your functional dependency analysis. Underline the primary keys. [9]Depts ( DeptId, DeptName, DeptChair)Majors( Major, DeptId)CSZ(Zip,City, State)Students(StuId, StuName, StuAddr, StuZip, Major)Embedded SQL short answers.[10 pts]Describe 3 necessary items of information to establish a database connection. [4]URL, database name, user, passwordDescribe what a cursor is in Java or Python and how it is different from a connection. [3]This object can hold the results of a query and allow stepping through the data as tuplesWhy is a for loop necessary to extract data from a result set after a query. [3]A result set is a list of tuples and the programming language needs to consider and process the tuples one at a time.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 __UpdateTotals___ () RETURNS trigger AS $$ BEGIN UPDATE __TRIPS__ AS T SET __tripTotals__ = (SELECT Sum(__E.amount__) from __EXPENSES_ AS E WHERE E.tripId__ = NEW.tripID) WHERE ___NEW .tripId = ____T__.tripId; RETURN null; END; __$$__ LANGUAGE plpgsql;b) Create the trigger you would need to ensure the trip totals are kept accurate. [6]CREATE TRIGGER UpdateTotalsTrigger AFTER UPDATE OR DELETE OR INSERT ON Expenses FOR STATEMENT EXECUTE PROCEDURE UpdateTotals();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.SELECT name,PhoneFROM TENANTSWHERE priorzip = ‘90210’List names and phones of past renters (no longer renting) who had monthly payments greater than $1500.SELECT name,phoneFROM TENANTS T NATURAL JOIN RENTED R NATURAL JOIN APTS AWHERE R.EndLeaseDate < Now() AND A.monthrent >1500Get a list of apartment IDs, number of bedrooms, and zip codes that are not currently rented (Hint: set difference or NOT IN -simplest). SELECT A.AptID, A.bedrooms, A.aptzipFROM APTS A WHERE A.AptID not in (SELECT R.Aptid FROM RENTED R WHERE R.EndLeaseDate<Now())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)SELECT T.nameFROM TENANTS T NATURAL JOIN RENTED RGROUP BY T.PIDHAVING count(T.AptID) >=3What is the average rent of apartments in ‘Pittsburgh’?SELECT AVG (MonthRent)FROM APTS A, CSZ WHERE A.Aptzip = CSZ.zip AND CSZ.City=’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 CURRENTERS ( TID, name, Address, City, State, Zip ) ASSELECT T.TID, T,name, A.AptAddress, Z.City, Z.State, Z.zipFROM TENANTS T, RENTED R, APTS A, CSZ ZWHERE T.TID=R.TID and R.AptID = A. APtID AND A.AptZip=Z.zipAND R.EndLeaseDate > now() ................
................

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

Google Online Preview   Download