Database Management Systems



Database Management SystemsNovember 11, 2019Name__________________________True/false.[25 pts]__T___ Normalization attempts to reduce or eliminate redundant facts stored in a database table tuple.__F___ Any truly 1NF relation permits nulls to be stored in database tables.__F___ Multi-valued attributes are permitted in a 1NF relation.__F___ A null value is the same as an empty string in the case of a non-numeric type attribute.__T___ UPDATE and DELETE SQL commands use the same WHERE syntax as the SELECT.__F___ A 1NF relation’s primary key is always just one attribute.__T___ Decomposition into two tables into higher normal form will require a foreign key to refer back to the other table’s key.__T___ A lossless join decomposition is so that when rejoining the tables from the decomposition process, tuples may not be lost from the original table. __F___ 3NF relations have allow transitive functional dependencies in them.__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 or DOUBLE type is the best for accounting and monetary situations. __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.__F___ A view requires a special SELECT statement, different than that for tables.__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 convenient rejoining of relations that were decomposed because of normalization.__T___ NOT NULL UNIQUE constraints on an attribute implies the attribute is a candidate key.__F___ A Python or Java compiler will identify embedded SQL syntax errors.__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.__F___ The ORDER BY clause and GROUP BY clauses are interchangeable.__T___ The LIMIT clause allows you to control the number of tuples returned in a query.Assume we have a 1NF relation with the following schema attributes where a student majors in at most one major, and a major belongs to only one department. These attributes are in random order.Students (DeptName, StuId, StuName, StuBirthDate, StuAddr, StuCity, StuState, StuZip, Major)[15 pts]Without explicitly listing functional dependencies, define the three 3NF tables directly in SQL that would best model this data with appropriate SQL attribute types. DDL syntax is given. { } = grouping, [ ] = optional, + = one or more CREATE TABLE <tablename> ({ <attribute> <type> [NOT NULL] [UNIQUE], }+ [ PRIMARY KEY (<attributes>) ] [ FOREIGN KEY (<attributes>) REFERENCES <table> (<attributes>) ]) ;<type> :== NUMERIC(w,d) | VARCHAR(n) | CHAR(n) | INTEGER | FLOAT | SERIAL | DATECREATE TABLE STUDENTS (StuId Serial NOT NULL,StuName VarChar(35) NOT NULL,StuBirthDate DATE NOT NULL,StuAddr VarChar(35),StuZip Char(5) NOT NULL,Major Char(15),PRIMARY KEY (StuId),FOREIGN KEY (StuZip) REFERENCES CSZ (Zip),FOREIGN KEY (Major) REFERENCES MAJORS (Major))CREATE TABLE MAJORS (Major Char(15) NOT NULL,DeptName Char(15) NOT NULL,PRIMARY KEY (Major))CREATE TABLE CSZ (Zip Char(5) NOT NULL,City Char(15) NOT NULL,State Char(10) NOT NULL,PRIMARY KEY (Zip))Security.[5 pts]What 3 items of information are found in an access matrix of a database?Who-user, What database object, How-type of access (SELECT, MODIFY, Aggregate etc)What 2 SQL commands give or deny access to database objects?GRANT and REVOKECircle all the elements of the Python code below that represents each of these embedded SQL concepts. Label the circled items.[10 pts]The database connection. The result set.The cursor.The driver.The query.import psycopg2def presdb(q): try: c = psycopg2.connect(host="itcsdbms",database="maindb", user="phantom", password="secret") d = c.cursor() d.execute(q) for e in d: print(e[0], e[1]) except (Exception, psycopg2.DatabaseError) as error : print ("Error while connecting to PostgreSQL", error)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.[10 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();What is the activation of this trigger? UPDATE, DELETE, INSERTWhat/when is the consideration of this trigger? Deferred, Generally no additional condition, always consideredWhat is the granularity of this trigger? STATEMENT levelWhat is the alternative granularity of a trigger? ROW levelFor the remaining questions, use the following relational schema for a company database tracking employees, their trips and expenses. Primary keys are underlined. Foreign keys share the same name as the primary keys. The attributes should be self-evident. If not, please ask for clarification.EmpS (SSN, name, address, zip, phone, deptno, jobTitle, salary)CSZ (zip, city, state)DEPTS (deptno, deptName, deptMgrSSN)TRIPS (tripId, DestinationCity, DepartureDate, ReturnDate,?SSN)EXPENSES (tripId, Item,?Date, Amount)Give neatly formatted SQL statements for the following queries on this company database.[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 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)List all names and job titles of employees.SELECT name, jobTitleFROM EMPS;List all names and zip codes of people who earn more than $50000 in salary.SELECT name, zipFROM EMPSWHERE salary > 50000;List employee names and job titles of those who are currently traveling. You can use the function call NOW() to refer to today. And assume ReturnDate can be in the future.SELECT e.name, e.jobTitleFROM EMPS e NATURAL JOIN TRIPS tWHERE t.DepartureDate <NOW() AND t.ReturnDate > NOW()EmpS (SSN, name, address, zip, phone, deptno, jobTitle, salary)CSZ (zip, city, state)DEPTS (deptno, deptName, deptMgrSSN)TRIPS (tripId, DestinationCity, DepartureDate, ReturnDate,?SSN)EXPENSES (tripId, Item,?Date, Amount)List the number of expense items and total amount of expenses for employee John Dough having traveled to Paris.SELECT Count(t.item),Sum(t.amount)FROM EMPS e NATURAL JOIN TRIPS t NATURAL JOIN EXPENSES eWHERE e.name=’John Dough’ AND t.DestinationCity=’Paris’;List employee names from the Accounting department who have not traveled (Hint: use left outer join or except).SELECT e.name FROM EMPS e NATURAL JOIN DEPTS d LEFT OUTER JOIN TRIPS t USING (SSN)WHERE t.SSN is NULL AND d.DeptName=’Accounting’ ;SELECT e.name FROM EMPS e NATURAL JOIN DEPTS d WHERE d.DeptName=’Accounting’ AND t.SSN NOT IN (SELECT SSN FROM TRIPS);List employee names who have traveled to Paris at least three times (use aggregate and grouping, not self-join).SELECT e.nameFROM EMPS e NATURAL JOIN TRIPS tWHERE t.DestinationCity=’Paris’GROUP BY t.SSNHAVING Count(t.SSN) >= 3;Create a view EMPADDR that creates a table of employees with all address elements including city and state, and department names instead of department numbers.CREATE VIEW EMPADDR ( SSN, name, address, city, state, zip, phone, deptName, jobTitle, salary ) ASSELECT e.SSN, e.name, e.address, z.city. z.state, e.zip, e.phone, d.deptname, e.jobTitle, e.salaryFROM EMPS e NATURAL JOIN CSZ z NATURAL JOIN DEPTS d ................
................

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

Google Online Preview   Download