Database Management Systems



Database Management SystemsNovember 11, 2019Name__________________________True/false.[25 pts]______ Normalization attempts to reduce or eliminate redundant facts stored in a database table tuple.______ Any truly 1NF relation permits nulls to be stored in database tables.______ Multi-valued attributes are permitted in a 1NF relation.______ A null value is the same as an empty string in the case of a non-numeric type attribute.______ UPDATE and DELETE SQL commands use the same WHERE syntax as the SELECT.______ A 1NF relation’s primary key is always just one attribute.______ Decomposition into two tables into higher normal form will require a foreign key to refer back to the other table’s key.______ A lossless join decomposition is so that when rejoining the tables from the decomposition process, tuples may not be lost from the original table. ______ 3NF relations have allow transitive functional dependencies in them.______ 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 or DOUBLE type is the best for accounting and monetary situations. ______ 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 requires a special SELECT statement, different than that for tables.______ 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 convenient rejoining of relations that were decomposed because of normalization.______ NOT NULL UNIQUE constraints on an attribute implies the attribute is a candidate key.______ A Python or Java compiler will identify embedded SQL syntax errors.______ 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.______ The ORDER BY clause and GROUP BY clauses are interchangeable.______ 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 (CREATE TABLE MAJORS (CREATE TABLE CSZ (Security.[5 pts]What 3 items of information are found in an access matrix of a database?What 2 SQL commands give or deny access to database objects?Circle 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?What/when is the consideration of this trigger?What is the granularity of this trigger?What is the alternative granularity of a trigger? For 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.List all names and zip codes of people who earn more than $50000 in salary.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.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.List employee names from the Accounting department who have not traveled (Hint: use left outer join or except).List employee names who have traveled to Paris at least three times (use aggregate and grouping, not self-join).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 ____________ ( ) AS ................
................

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

Google Online Preview   Download