Databases Exam #3 - Multiple Choice

Which SQL statement is used to retrieve and display the rows of view instances? A) CREATE B) DELETE C) INSERT D) SELECT E) UPDATE

D) SELECT

A stored program that is attached to a table or view is called A) a CHECK constraint. B) a View. C) embedded SQL. D) a trigger. E) a stored procedure.

D) a trigger.

To change a table name, we A) use the SQL RENAME TABLE command. B) use the SQL ALTER TABLE NAME command. C) use the SQL MODIFY TABLE NAME command. D) create a new table, move the data, and drop the old table. E) None of the above is the correct way to change a table name.

D) create a new table, move the data, and drop the old table.

Database redesign is fairly easy when A) information systems and organizations influence each other. B) the design was done correctly the first time. C) there is no data in the database. D) good backups of the database are available. E) all of the above are true.

C) there is no data in the database.

To which of the following actions are referential integrity constraints NOT applied? A) Create B) Insert C) Modify D) Delete E) Referential integrity constraints are applied to all of the listed actions.

A) Create

In a correlated subquery of a database that has tables TableOne and TableTwo, and if table TableOne is used in the upper SELECT statements, then which table is used in the lower SELECT statement? A) TableOne. B) TableTwo. C) both TableOne and TableTwo. D) either TableOne or TableTwo. E) neither TableOne nor TableTwo.

A) TableOne.

When transforming an ID-dependent E-R data model relationship into a relational database design, the referential integrity actions should specify A) parent updates to cascade. B) child updates to cascade. C) child deletes to cascade. D) A and B. E) A, B, and C.

A) parent updates to cascade.

When transforming an E-R data model into a relational database design, the key of the parent entity should be placed as part of the primary key into the child entity when A) the child entity is ID-dependent. B) the child entity is non ID-dependent. C) the child entity has a 1:1 relationship with the parent entity. D) the child entity has a 1:N relationship with the parent entity. E) the child entity has a recursive relationship with the parent entity.

A) the child entity is ID-dependent.

An SQL virtual table is called A) a CHECK constraint. B) a view. C) embedded SQL. D) a trigger. E) a stored procedure.

B) a view.

In the SQL statements SELECT C1.CustName, C1.SalesRepNo FROM CUSTOMER C1; the "C1" is called a(n) A) term. B) alias. C) convention. D) phrase. E) label.

B) alias.

Given the following SQL statement, we know that CREATE TABLE SALESREP( SalesRepNo Number(10) NOT NULL, RepName varchar(35) NOT NULL, HireDate Date NOT NULL, CONSTRAINT SalesRep_PK PRIMARY KEY (SalesRepNo), CONSTRAINT SalesRep_AK1 UNIQUE (RepName) ); A) RepName is the primary key. B) RepName is a foreign key. C) RepName is a candidate key. D) RepName is a surrogate key. E) None of the above is true.

C) RepName is a candidate key.

When the correct SQL command is used to delete a table's structure, what happens to the data in the table? A) If the deleted table was a parent table, the data is added to the appropriate rows of the child table. B) If the deleted table was a child table, the data is added to the appropriate rows of the parent table. C) The data in the table is also deleted. D) Nothing, because there was no data in the table, since only an empty table can be deleted. E) A and B.

C) The data in the table is also deleted.

Which of the following is NOT true about surrogate keys? A) They are identifiers that are supplied by the system, not the user. B) They have no meaning to the users. C) They are nonunique within a table. D) They can be problematic when combining databases. E) The DBMS will not allow their values to be changed.

C) They are nonunique within a table.

Which of the following SQL statements is a correctly stated correlated subquery? A) SELECT C1.CustName, C1.SalesRepNo FROM CUSTOMER C1 WHERE C1.SalesRepNo IN (SELECT S1.SalesRepNo FROM SALESREP S1 WHERE S1.RepName = 'Smith' ); B) SELECT C1.CustName, C1.SalesRepNo FROM CUSTOMER C1 WHERE C1.SalesRepNo IN (SELECT S1.SalesRepNo FROM SALESREP S1 WHERE S1.RepName = 'Smith' AND C1.SalesRepNo = S1.SalesRepNo ); C) SELECT C1.CustName, C1.SalesRepNo FROM CUSTOMER C1 WHERE C1.SalesRepNo IN (SELECT S1.SalesRepNo FROM SALESREP S1 WHERE S1.RepName = 'Smith' AND C1.SalesRepNo <> S1.SalesRepNo ); D) SELECT C1.CustName, C1.SalesRepNo FROM CUSTOMER C1 WHERE C1.SalesRepNo IN (SELECT C2.SalesRepNo FROM CUSTOMER C2 WHERE C1.SalesRepNo = C2.SalesRepNo AND C1.OrderNo <> C2.OrderNo ); E) None of the above is a correctly stated correlated subquery.

D) SELECT C1.CustName, C1.SalesRepNo FROM CUSTOMER C1 WHERE C1.SalesRepNo IN (SELECT C2.SalesRepNo FROM CUSTOMER C2 WHERE C1.SalesRepNo = C2.SalesRepNo AND C1.OrderNo <> C2.OrderNo );

When a trigger is fired, the DBMS makes the appropriate data available to A) the SQL interpreter. B) the application code. C) the embedded SQL code. D) the trigger code. E) the stored procedure code.

D) the trigger code.

SQL views are used A) to hide columns. B) to show results of complicated columns. C) to provide a level of indirection between data processed by applications and the data actually stored in the database tables. D) A and B. E) A, B, and C.

E) A, B, and C.

In many-to-many relationships in a relational database design A) the intersection table is ID-dependent on one of the parents. B) the intersection table is ID-dependent on both of the parents. C) the minimum cardinality from the intersection table to the parents is always M. D) A and B. E) B and C.

E) B and C.

In many-to-many relationships in a relational database design A) the key of the child is placed as a foreign key into the parent. B) the key of the parent is placed as a foreign key into the child. C) the keys of both tables are placed in a third table. D) the keys of both tables are joined into a composite key. E) C and D.

E) C and D.

Which of the following is NOT true of recursive relationships? A) When the recursive relationship is M:N, an intersection table is created. B) The rows of a single table can play two different roles. C) The techniques for representing the tables are the same as for non-recursive relationships except the rows are in the same table. D) They can be 1:1, 1:N, or M:N relationships. E) Even when the relationship is 1:N, a new table must be defined to represent the relationship.

E) Even when the relationship is 1:N, a new table must be defined to represent the relationship.

Based on the tables below, which of the following commands would increase the balance of Gonzales by $100 to a total of $450? SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/1999 734 Smith 02/03/2000 345 Chen 01/25/1998 434 Johnson 11/23/1998 CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345 A) SELECT Gonzales FROM CUSTOMER INSERT VALUES PLUS (100) INTO Balance; B) SELECT Gonzales FROM CUSTOMER INSERT VALUES (450) INTO Balance; C) INSERT INTO CUSTOMER VALUES PLUS (100) SELECT Balance WHERE CustName = 'Gonzales'; D) INSERT INTO CUSTOMER VALUES (450) SELECT Balance WHERE CustName = 'Gonzales'; E) UPDATE CUSTOMER SET Balance = 450 WHERE CustName = 'Gonzales';

E) UPDATE CUSTOMER SET Balance = 450 WHERE CustName = 'Gonzales';

When running a correlated subquery, the DBMS A) runs the lower SELECT statement by itself and then sends the results to the upper SELECT statement. B) runs the upper SELECT statement by itself and then sends the results to the lower SELECT statement. C) alternates running the lower SELECT statement with running the upper SELECT statement based on each result of the lower SELECT statement. D) either a or b may be used, depending on the query. E) None of the above describes how a correlated subquery is run by the DBMS.

C) alternates running the lower SELECT statement with running the upper SELECT statement based on each result of the lower SELECT statement.

SQL statements are set-oriented, whereas programs are element-oriented, so the results of SQL statements used in programs are accessed using A) standard programming tools. B) custom-written programming tools. C) an SQL cursor. D) an SQL trigger. E) an SQL stored procedure.

C) an SQL cursor.

When transforming an ID-dependent E-R data model relationship into a relational database design, and the child entity is redesigned to use a surrogate key, then A) the parent entity must also use a surrogate key. B) the relationship remains an ID-dependent relationship. C) the relationship changes to a non-ID-dependent relationship. D) A and B. E) A and C.

C) the relationship changes to a non-ID-dependent relationship.