Deck 7: Database Administration

Question 1

A program's or an individual user's picture of the database is called a(n) _____.

A) transaction
B) view
C) catalog
D) index

Answer 1

B

Question 2

Views furnish a certain amount of security; if users are accessing the database through a view, they cannot access any data that is not included in the view.

Answer 2

True

Question 3

To ensure the uniqueness of values in a nonprimary key column, you can create an index using the _____.

A) CREATE UNIQUE INDEX command
B) CREATE NONPRIMARY INDEX command
C) CREATE INDEX command
D) CREATE ONLY INDEX command

Answer 3

A

Question 4

A database can be readily updated through a view when that view is derived from joining two base tables on a shared _____.

A) row-column
B) foreign key
C) primary key
D) index

Question 5

Which command will allow user Thompson to remove rows from the CUSTOMER table?

A) GRANT DROP ON CUSTOMER TO THOMPSON;
B) GRANT THOMPSON REMOVE ON CUSTOMER;
C) SELECT* CUSTOMER GRANT THOMPSON DELETE;
D) GRANT DELETE ON CUSTOMER TO THOMPSON;

Question 6

You can create and maintain an index on any row in any table.

Question 7

Which of the following commands deletes an index?

A) DELETE INDEX
B) REMOVE INDEX
C) UNINDEX
D) DROP INDEX

Question 8

The system catalog is also called the _____.

A) system database
B) data dictionary
C) master data table
D) transaction database

Question 9

When users create, alter, or drop tables or create or drop indexes, the DBMS updates the system catalog automatically.

Question 10

When you specify a foreign key, the table referenced by the foreign key is the _____.

A) super
B) sibling
C) parent
D) child

Question 11

Which privilege can be granted to allow a user to retrieve data?

A) SELECT
B) UPDATE
C) RETRIEVE
D) READ

Question 12

The CREATE VIEW command includes the words CREATE VIEW, followed by the name of the view, the word ____, and then a query.

A) AC
B) SA
C) AS
D) IN

Question 13

The general form for assigning a foreign key is ADD FOREIGN KEY, the column name(s) of the foreign key, the ____ clause, and then the table name and column that the foreign key must match.

A) WHERE
B) REFERENCES
C) FROM
D) IN

Question 14

When renaming columns for a view, you include the new column names in _____ following the name of the view.

A) single quotes
B) double quotes
C) parentheses
D) square brackets

Question 15

Which privilege should be granted to users who are authorized to change a table's structure?

A) WRITE
B) UPDATE
C) ALTER
D) MODIFY

Question 16

Percy is promoted and is no longer responsible for making updates to several database tables. Which command should Percy's database administrator use to prevent Percy from making future updates?

A) DROP
B) REMOVE
C) REVOKE
D) CANCEL

Question 17

In a business organization, the person or entire group of people charged with managing the database is known as the _____.

A) database analyst
B) developer
C) database administrator
D) programmer

Question 18

The issues involved in updating data within base tables through a view are the same regardless of the type of view.

Question 19

Adding rows to a view is most troublesome for the view user when that view contains _____.

A) statistics calculated from base tables
B) a base table's primary key
C) more than five columns
D) data from more than one base table

Question 20

An index makes certain types of retrieval more efficient.

Question 21

Updating a database using a view can pose problems. Describe some of these problems that occur with views that join two or more base tables. How can a database administrator minimize problems when creating views that join tables?

Question 22

The types of constraints supported in SQL are ____.

A) data types, indexes, and system catalogs
B) foreign keys, qualifiers, and naming conventions
C) user privileges, data types, and legal values
D) primary keys, foreign keys, and legal values

Question 23

Martine's database administrator has set up a view for her so that she can access the information she needs from the database. What will happen when new columns Martine does not need to see are added to the base tables used for Martine's view?

A) The new columns will appear in Martine's view.
B) The data independence provided by the view will be lost.
C) The administrator will have to change the view's defining query.
D) Martine can continue to use her view as usual.

Question 24

What happens once the following command is executed? ALTER TABLE ORDERS
ADD FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT (CLIENT_ID);

A) The DBMS will reject updates to the ORDERS table with CLIENT_ID values missing from the CLIENT table.
B) The DBMS will reject the insertion of rows that have different primary keys but are otherwise identical into the ORDERS table.
C) The DBMS will reject updates to the CLIENT table with CLIENT_ID values missing from the ORDERS table.
D) The DBMS will ensure that only legal values are entered into the CLIENT_ID column in the ORDERS table.

Question 25

Views can contain only the columns required by a given user. What two advantages are conferred by this aspect of views?

A) customization and data independence
B) simplicity and security
C) data independence and portability
D) performance and reliability

Question 26

What happens if Samantha, whose view of the database contains only a portion of the rows and columns of the base table, attempts to add a row to the table through her view?

A) Samantha's INSERT command will automatically be rejected.
B) Samantha could accidentally add a row with a duplicate primary key to the table.
C) If Samantha's INSERT command succeeds, the new row will contain several null values.
D) If all the columns in the base table accept nulls, Samantha's INSERT command will fail.

Question 27

What are the disadvantages of using an index?

Question 28

Explain what happens when you create a query that involves a view.

Question 29

What does this SQL command accomplish? ALTER TABLE PRODUCTS
ADD CHECK (SIZE IN ('S', 'M', 'L', 'XL', 'XXL'));

A) updates all values in the SIZE column
B) adds a parent-child constraint to the PRODUCTS table
C) establishes a foreign key for the PRODUCTS table
D) establishes legal values for the SIZE column

Question 30

The purpose of creating databases indexes is to _____.

A) maintain the integrity of the data stored in tables
B) reduce the database's storage (memory) requirements
C) avoid searching through every row in a table with every query
D) grant and/or revoke privileges for certain actions to users

Question 31

Which MySQL command displays all tables associated with the database ELPATRON?

A) SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'ELPATRON');
B) SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE (TABLE_SCHEMA = 'ELPATRON');
C) SELECT * FROM USER_OBJECTS 'ELPATRON';
D) SHOW TABLES WHERE TABLE_SCHEMA = ELPATRON;

Question 32

What are the benefits of using views?

Question 33

Which statement about views is correct?

A) A view can join two or more tables and/or involve statistics.
B) A view is a temporary table the DBMS creates in response to a query.
C) A query of a view is executed in the form in which the user enters it.
D) The column names in a view must match those in the base table(s).

Question 34

An index _____.

A) involves complexities that are not managed directly by most DBMSs
B) can be essential to the retrieval performance of very large database
C) must be created at the same time as the database
D) provides significant benefits for databases of all sizes

Question 35

In Oracle, which table contains information about the columns within tables?

A) SYSTABLES
B) DBA_TAB_COLUMNS
C) COLUMNS
D) USER_OBJECTS