DB EXAM 2

In relational decomposition of a universal relation, the property of attribute preservation requires that every attribute of the universal relation appears in only one relation every attribute of the universal relation appears in exactly two relations every attribute of the universal relation appears in at least one relation every attribute remains in the original relation

every attribute of the universal relation appears in at least one relation

A relation is Boyce-Codd Normal Form if every superkey is a candidate key every determinant is a superkey every candidate key is a primary key it has overlapping candidate keys

every determinant is a superkey

A relation is second normal form if it is 1NF and every attribute is single-valued every attribute is determined by a portion of the key every nonkey attribute is dependent on the entire key no nonkey attribute determines another

every nonkey attribute is dependent on the entire key

A relation is in _______ normal form if, and only if, every attribute is single-valued for each tuple. first second third Boyce-Codd

first

If X and Y are sets of attributes of relation R, we say that Y is functionally dependent on X if for each X value there is only one Y value for each Y value there is only one X value no two X values have the same Y value when two rows have the same Y value they also have the same X value

for each X value there is only one Y value

If a relational decomposition is not lossless, doing joins to recreate the original table may produce a table that has more rows than the original table has fewer rows than the original table does not have the same columns as the original table is not the union of all the subrelations

has more rows than the original table

The standard decomposition algorithm for BCNF requires identifying and removing each violation of BCNF from a universal relation normalizing to 1NF, 2NF, and 3NF before normalizing to BCNF grouping together FDs that have the same determinant mapping relations from an E-R diagram

identifying and removing each violation of BCNF from a universal relation

In the relational model, if A functional determines B, it means that the value of B can be calculated from the value of A the value of B is always the same as the value of A if two rows have the same A value, they must have the same B value if two rows have the same B value, they must have the same A value

if two rows have the same A value, they must have the same B value

Which of the following is not one of the major objectives of normalization? removing redundancy improving efficiency removing anomalies increasing model flexibility

improving efficiency

In the relational model, a spurious tuple is one that appears in the original relation does not have a candidate key has repeating values for an attribute is created by a lossy join

is created by a lossy join

It is always possible to find a Boyce-Codd Normal Form decomposition that is lossless, but may not preserve dependencies preserves dependencies, but may not be lossless is both lossless and dependency-preserving is disjoint

is lossless, but may not preserve dependencies

A relational view cannot be updated if it is missing any attributes of the underlying base table(s) it is missing the primary key of the underlying base table(s) it is constructed from a join of base tables it is a view of a view

it is missing the primary key of the underlying base table(s)

For a relational decomposition, the property of being able to get back exactly the original relation by joins is lossless decomposition dependency preservation attribute preservation relation preservation

lossless decomposition

A relation having only one candidate key is third normal form if it is 2NF and no nonkey attribute is determined by only part of the key no nonkey attribute is dependent on another nonkey attribute no part of the key is dependent on another part of the key there are no partial functional dependencies

no nonkey attribute is dependent on another nonkey attribute

If set S{A,B,C} is a candidate key for the relation R(A,B,C,D,E), then which of the following must be true {A,B,D} must also be a candidate key for R {A,B} or {A,C} or {B,C} must also be a candidate key for R {A,B,C,D} must also be a candidate key for R none of these

none of these

In the relational model, every determinant is always a candidate key a superkey a composite key none of these

none of these

In the relational model, all of the following are types of dependencies that have been shown to lead to update anomalies EXCEPT functional multivalued join simple

simple

The normalization process in which we find and group together functional dependencies with the same determinant is called analysis synthesis generalization specialization

synthesis

In a JDBC application, the class that is used to connect to the database is the DatabaseConnection class the JDBC class the QueryObject class the DriverManager class

the DriverManager class

In a JDBC application, query results can be returned using an object of which class? the DatabaseConnection class the ResultSet class the QueryObject class the GetConnection class

the ResultSet class

In the relational model, a projection is lossless if the join produces the same structure as the original relation no further decomposition is possible the intersection of the relations is empty the join produces the same tuples as the original relation

the join produces the same tuples as the original relation

If a relation is 2NF but not 3NF, it must have which type of functional dependency? multivalued partial join transitive

transitive

A functional dependency in which the attributes on the right hand side are included in the determinant is called redundant normal trivial multi-valued

trivial

If set S{A,B,C} is a superkey for the relation R(A,B,C,D,E), then {A,B} is also a superkey for R no subset of S is also a superkey for R {D, E} is also a superkey for R {A,B,C,D} is also a superkey for R

{A,B,C,D} is also a superkey for R

The SQL function that returns the number of values in a column is SUM COUNT AVER TOTAL

COUNT

To erase all records in the Employee table, while keeping the structure of the table, we could write the SQL command DELETE * FROM Employee; DROP EMPLOYEE; DELETE FROM Employee; DELETE ALL COLUMNS FROM Employee;

DELETE FROM Employee;

To eliminate duplicates in the results of an SQL query, use the option UNIQUE DISTINCT NO REPEATS ORDER BY

DISTINCT

All of the following are SQL DDL commands except CREATE TABLE DROP INDEX ADD TABLE ALTER TABLE

ADD TABLE

All of the following are reasons for leaving a schema in a lower normal form except Desire to keep related items together in the same relation Performance requirements Desire to preserve functional dependencies in the same relation Desire to reduce replication

Desire to reduce replication

In a relational database environment, a user's external model can consist of views, but not base tables base tables, but not views both views and base tables base tables and their indexes only

both views and base tables

A _______ key is a superkey such that no proper subset of its attributes is itself a superkey. composite candidate multi-value foreign

candidate

A _______ key is a key that consists of more than one attribute. multi-value key candidate key superkey composite key

composite key

The SQL ALTER TABLE command can be used to do all of the following except add new columns drop columns drop the table add a constraint

drop the table

_______ means putting a relation into a higher normal form. Normalization Functional dependency Insertion anomaly Deletion anomaly

Normalization

Which of the following is NOT valid in an SQL subquery? ...WHERE empId IN ( SELECT empId... ...WHERE empid = (SELECT empId.. ...WHERE NOT EXISTS ( SELECT * ...WHERE empId IN ( SELECT *

...WHERE empId IN ( SELECT *

It is always possible to find a dependency preserving lossless decomposition for _______. 1NF 2NF 3NF BCNF

3NF

The highest normal form that always allows us to preserve functional dependencies is 1NF 2NF 3NF BCNF

3NF

The essence of _______ normal form is that each non-key attribute is functionally dependent on the entire key, and on no other attribute. 1st 2nd 3rd BCNF

3rd

In the relation R(A, B, C, D), having the composite key {A,B}, which of the following FDs would demonstrate that the relation is not 2NF? A → B A → C C → D any of these

A → C

In a relation R(A, B, C, D), which of the following would prove that R is not 3NF? B → A B → C C → A D → A

B → C

n the relation R(A, B, C, D), having the composite key {A,B}, which of the following FDs would demonstrate that the relation is not 3NF? C → {A, B} { A,B} → C C → D any of these

C → D

The SQL command to advance a cursor to obtain the next row of results is NEXT ADVANCE FETCH SCROLL

FETCH

The specification in the SQL CREATE TABLE command that enforces referential integrity is the PRIMARY KEY UNIQUE INDEX FOREIGN KEY NOT NULL

FOREIGN KEY

When you add a column to an existing table by using an SQL ALTER TABLE command, the column cannot contain the specification WITH DEFAULT CHECK NOT NULL UNIQUE

NOT NULL

The SQL option HAVING can only be used with the option ORDER BY DISTINCT GROUP BY LIKE

GROUP BY

To display data retrieved using an SQL SELECT in ascending order, use the option GROUP BY LIKE ORDER BY ASCENDING ORDER

ORDER BY

To retrieve all columns in a SQL SELECT operation, the SELECT line should read SELECT * SELECT ALL SELECT ALL COLUMNS SELECT

SELECT *

Which of the following is the proper way to create an alias, E, for relational table Employee and retrieve all employee data? SELECT * AS E FROM EMPLOYEE; SELECT * FROM EMPLOYEE E; SELECT E * FROM EMPLOYEE; SELECT * FROM EMPLOYEE WHERE EMPLOYEE = E;

SELECT * FROM EMPLOYEE E;

Given an Employee table with columns for salary and dept, to raise the salaries of all employees in the sales department by 10%, we could write UPDATE Employee SET salary = salary*1.10 WHERE dept='sales'; UPDATE salary SET salary = salary*1.10 WHERE dept='sales'; UPDATE Employee SET salary = 1.10 WHERE dept='sales'; UPDATE Employee LET salary = salary*1.10 WHERE dept='sales';

UPDATE Employee SET salary = salary*1.10 WHERE dept='sales';

If a relation is 3NF but not BCNF, it must have a non-trivial functional dependency X → A such that the primary key does not determine X X is not a superkey A is not a superkey the primary key does not determine A

X is not a superkey

We can ensure that a binary decomposition is lossless if the set of common attributes in the two relations is empty the union of the two relations a superkey of one of the relations functionally dependent on the keys of both relations

a superkey of one of the relations

A relation is first normal form if every attribute is single-valued for each tuple the domains of the attributes are atomic each cell of the table can contain only one value all of these

all of these

In the relational model, a view can be used in the FROM line of an SQL query in the INTO line in an SQL INSERT command as the basis of a derived view all of these

all of these

In relational decomposition of a universal relation, the property of dependency preservation requires that all determinants appear in the same relation all attributes appear in some relation it is possible to reconstruct the original relation by a join all the attributes on both sides of each dependency appear in the same relation

all the attributes on both sides of each dependency appear in the same relation

The normalization process that starts with a universal relation and uses decomposition to produce a set of normalized relations is called analysis synthesis abstraction classification

analysis

In SQL, when creating a view, columns from the original table can be renamed, but not reordered reordered, but not renamed neither renamed nor reordered both renamed and reordered

both renamed and reordered