Database Test 2

Which of the following is the wildcard operator in SQL statements?

*

What will be returned when the following SQL statement is executed? Select driver_no, count(*) as num_deliveries from deliveries where state = 'MA' group by driver_no;

A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state

In which of the following situations would one have to use an outer join in order to obtain the desired results?

A report is desired that lists all customers, the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero).

What does the following SQL command do? insert into Customer_T values (001,'John Smith','231 West St','Boston','MA','02115');

Adds a new record to the Customer_T

What does the following SQL statement do? Alter Table Customer_T Add (Type Varchar (2));

Alters the Customer_T table, and adds a field called "Type"

What does the following SQL statement do? Update Product_T Set Unit_Price = 775 Where Product_ID = 7

Changes the unit price of Product 7 to 775

What does the following SQL statement do? Update Product_T Set Unit_Price = 775Where Product_ID = 7

Changes the unit price of Product 7 to 775

Which of the following counts ONLY rows that contain a value?

Count

________ is a set of commands used to control a database, which includes security.

DCL

________ is a set of commands used to update and query a database.

DML

Physical database design decisions must be made carefully because of impacts on:

Data accessibility, response times, security

________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated.

Data integrity

Which of the following questions is answered by the SQL statement? Select Count (Product_Description) from Product_T;

How many products have product descriptions in the Product Table?

Which of the following is an objective of selecting a data type?

Improve data integrity

In the figure below, what type of key is depicted?

Recursive foreign

A relation that contains no multivalued attributes and has nonkey attributes solely dependent on the primary key but contains transitive dependencies is in which normal form?

Second

What does the following SQL statement do? Select * From Customer Where Cust_Type = "Best"

Selects all the fields from the Customer table for each row with a customer labeled "Best"

The figure below is an example of mapping which type of relationship?

Ternary

What result will the following SQL statement produce? Select Avg(standard_price) as average from Product_V;

The average Standard_Price of all products in Product_V

What results will be produced by the following SQL query? Select sum(standard_price) as Total_Price from Product_V where Product_Type = 'WOOD';

The total price of all products that are of type wood

The ________ clause is used to combine the output from multiple queries into a single result table.

UNION

In an SQL statement, which of the following parts states the conditions for row selection?

Where

All of the following are common denormalization opportunities EXCEPT:

a one-to-many relationship.

The SQL command ________ adds one or more new columns to a table.

alter table

The benefits of a standardized relational language include:

application longevity. reduced training costs. cross-system communication.

A rule of thumb for choosing indexes is to:

be careful indexing attributes that may be null. index each primary key of each table. use an index when there is variety in attribute values.

A join operation:

causes two tables with a common domain to be combined into a single table or view.

A primary key that consists of more than one attribute is called a:

composite key.

A repository of information about a database that documents data elements of a database is called a:

data dictionary

The storage format for each attribute from the logical data model is chosen to maximize ________ and minimize storage space.

data integrity

Which of the following functions addresses the issue of database passwords?

data policy, procedures, and standards

A detailed coding scheme recognized by system software for representing organizational data is called a(n):

data type.

The value a field will assume unless the user enters an explicit value for an instance of that field is called a:

default value.

The attribute on the left-hand side of the arrow in a functional dependency is the:

determinant.

To eliminate duplicate rows in a query, the ________ qualifier is used in the SQL Select command.

distinct

An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a:

foreign key.

A constraint between two attributes is called a(n):

functional dependency.

The first in a series of steps to follow when creating a table is to:

identify each attribute and its characteristics.

One major advantage of the outer join is that:

information is not lost.

Which of the following anomalies result from a transitive dependency?

insertion, modification, deletion

All of the following are the main goals of normalization EXCEPT:

maximize storage space.

The most commonly used form of join operation is the:

natural join.

A requirement to begin designing physical files and databases is:

normalized relations. definitions of each attribute. technology descriptions.

A join in which rows that do not have matching values in common columns are still included in the result table is called a(n):

outer join.

An attribute (or attributes) that uniquely identifies each row in a relation is called a:

primary key.

A ________ limits the set of permissible values a field may assume.

range control

An integrity control supported by a DBMS is:

range control

A _____ is a DBMS module that restores the database to a correct condition when a failure occurs.

recovery manager

A rule that states that each foreign key value must match a primary key value in the other relation is called the:

referential integrity constraint.

A two-dimensional table of data is called a:

relation.

The ________ is the structure that contains descriptions of objects such as tables and views created by users.

schema

A type of join where a table is joined to itself is called a(n):

self-join.

SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query.

subquery

A method for handling missing data is to:

substitute and estimate for the missing data, track missing data with special reports, perform sensitivity testing

A functional dependency between two or more nonkey attributes is called a:

transitive dependency.

All of the following are objectives when selecting a data type EXCEPT:

use a lot of storage space.

Understanding the steps involved in transforming EER diagrams into relations is important because:

you must be able to check the output of a CASE tool.