intro to databases Chapter 6 (normalization) and chapter 7 Intro to SQL

The following SQL statement uses a(n) ____.SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

"old-style" join

How many rows would be returned from a cross join of tables A and B, if A contains 8 rows and B contains 18?

144

From a structural point of view, 2NF is better than ____.

1NF

A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key, is said to be in ____.

1nf

A table that is in 1NF and includes no partial dependencies is said to be in ____.

2NF

Before converting a table into 3NF, it is imperative the table already be in ____.

2NF

A table that is in 2NF and contains no transitive dependencies is said to be in ____.

3NF

For most business transactional databases, we should normalize relations into ____.

3NF

Most designers consider the BCNF as a special case of the ____.

3NF

Some very specialized applications may require normalization beyond the ____.

4NF

A table where every determinant is a candidate key is said to be in ____.

BCNF

The special operator used to check whether an attribute value is within a range of values is ____.

BETWEEN

To delete a row from the PRODUCT table, use the ____ command.

DELETE

Of the following normal forms, ____ is mostly of theoretical interest.

DKNF

The ____ command is used with the ALTER TABLE command to modify the table by deleting a column.

DROP

____ yields better performance.

Denormalization

Attribute A ____ attribute B if all of the rows in the table that agree in value for attribute A also agree in value for attribute B.

Determines

A dependency of one nonprime attribute on another nonprime attribute is a partial dependency.

False

A determinant is any attribute whose value determines other values within a column.

False

A good relational DBMS excels at managing denormalized relations.

False

A table is in BCNF if every determinant in the table is a foreign key.

False

Any changes made to the contents of a table are not physically saved on disk until you use the SAVE <table name> command.

False

Atomic attributes are attributes that can be further subdivided.

False

Attributes should clearly define participation, connectivity, and document cardinality.

False

Converting a database format from 1NF to 2NF is a complex process.

False

Dependencies that are based on only a part of a composite primary key are called transitive dependencies.

False

Normalization is a process that is used for changing attributes to entities.

False

Normalization is a very important database design ingredient and the highest level is always the most desirable.

False

Normalization purity is easy to sustain in the modern database environment.

False

Only numeric data types can be added and subtracted in SQL.

False

Oracle users can use the Access QBE (query by example) query generator.

False

SQL is considered difficult to learn; its command set has a vocabulary of more than 300 words.

False

The ANSI prescribes a standard SQL-the current fully approved version is known as SQL-07.

False

The CHECK constraint is used to define a condition for the values that the attribute domain cannot have.

False

The combination of normalization and ER modeling yields a useful ERD, whose entities may now be translated into appropriate relationship structures.

False

To join tables, simply enumerate the tables in the FROM clause of the SELECT statement. The DBMS will create a Cartesian product of every table in the FROM clause. To get the correct results, you need to select the rows in which the common attribute values do not match.

False

You cannot insert a row containing a null attribute value using SQL.

False

The SQL command that lets you insert rows into a table is ____.

INSERT

If you wish to create an inner join, but the two tables do not have a commonly named attribute, you can use a(n) ____ clause.

JOIN ON

The special operator used to check for similar character strings is ____.

LIKE

The ____ command is used to restore the table's contents to their previous values.

ROLLBACK;

The SQL command that lets you select attributes from rows in one or more tables is

SELECT

Which of the following is used to select partial table contents?

SELECT <column(s)>FROM <Table name>WHERE <Conditions>

Which query is used to list a unique value for V_CODE, where the list will produce only a list of those values that are different from one another?

SELECT DISTINCT V_CODE FROM PRODUCT;

The query used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the PRODUCT table in ascending order by P_PRICE is ____.

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;

Which query will output the table contents when the value of the character field P_CODE is alphabetically less than 1558-QW1?

SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE <'1558-QW1';

Which query will output the table contents when the value of V_CODE is not equal to 21344?

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;

The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the values of V_CODE match is ____.

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

Which query will use the given columns and column aliases from the PRODUCT table to determine the total value of inventory held on hand and display the results in a column labeled TOTVALUE?

SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE AS TOTVALUE FROM PRODUCT;

Which query will use the given columns and column aliases from the PRODUCT table to determine the total value of inventory held on hand?

SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE FROM PRODUCT;

The syntax for a left outer join is ____.

SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition

UPDATE tablename ***** [WHERE conditionlist]; The ____ command replaces the ***** in the syntax of the UPDATE command, shown above.

SET columnname = expression

The most recent fully approved version of standard SQL prescribed by the ANSI is ____.

SQL-2003

The SQL aggregate function that gives the total of all values for a selected attribute in a given column is ____.

SUM

Normalization works through a series of stages called normal forms. For most purposes in business database design, ____ stages are as high as you need to go in the normalization process.

Three

A database language enables the user to create database and table structures to perform basic data management chores.

True

A table is in 2NF if it is in 1NF and it includes no partial dependencies.

True

A table is in BCNF if every determinant in the table is a candidate key.

True

A table is in fourth normal form if it is in third normal form and has no independent multivalued dependencies.

True

All relational tables satisfy the 1NF requirements.

True

Although SQL commands can be grouped together on a single line, complex command sequences are best shown on separate lines, with space between the SQL command and the command's components.

True

Because a partial dependency can exist only if a table's primary key is composed of several attributes, if a table in 1NF has a single-attribute primary key, then the table is automatically in 2NF.

True

Denormalization produces a lower normal form.

True

Dependency diagrams are very helpful in getting a bird's-eye view of all the relationships among a table's attributes.

True

If you have not yet used the COMMIT command to store the changes permanently in the database, you can restore the database to its previous condition with the ROLLBACK command.

True

In order to meet performance requirements, you may have to denormalize portions of the database design.

True

It is possible for a table in 2NF to exhibit transitive dependency, where the primary key may rely on one or more nonprime attributes to functionally determine other nonprime attributes.

True

Normalization represents a micro view of the entities within the ERD.

True

Normalization should be part of the design process.

True

Normalization works through a series of stages called normal forms.

True

Relational models view the data as part of a table or collection of tables in which all key values must be identified.

True

Repeating groups must be eliminated by making sure that each row defines a single entity.

True

SQL allows the use of logical restrictions on its inquiries such as OR, AND, and NOT.

True

Some RDBMSs, such as Microsoft Access, automatically make the necessary conversions to eliminate case sensitivity.

True

String comparisons are made from left to right.

True

The advantage of higher processing speed must be carefully weighed against the disadvantage of data anomalies.

True

The conditional LIKE must be used in conjunction with wildcard characters.

True

Unnormalized database tables often lead to various data redundancy disasters in production databases.

True

With partial dependencies, data redundancies occur because every row entry requires duplication of data.

True

A table that displays data redundancies yields ____.

anomalies

Improving ____ leads to more flexible queries.

atomicity

The conflicts between design efficiency, information requirements, and processing speed are often resolved through ____.

compromises that include denormalization

Data redundancy produces ____.

data integrity problems

In a(n) ____ diagram, the arrows above the attributes indicate all desirable dependencies.

dependency

From a system functionality point of view, ____ attribute values can be calculated when they are needed to write reports or invoices.

derived

Normalization represents a micro view of the ____ within the ERD.

entities

A ____ join returns rows with matching values and includes all rows from both tables (T1 and T2) with unmatched values.

full outer

A table is in 4NF if it is in 3NF and ____.

has no multivalued dependencies

When using a(n) ____ join, only rows that meet the given criteria are returned.

inner

A(n) ____ join will select only the rows with matching values in the common attribute(s).

natural

1NF, 2NF, and 3NF are ____.

normalization stages

The most likely data type for a surrogate key is ____.

numeric

A(n) ____ join returns not only the rows matching the join condition (that is, rows with matching values in the common columns) but also the rows with unmatched values.

outer

In subquery terminology, the first query in the SQL statement is known as the ____ query.

outer

Dependencies based on only a part of a composite primary key are called ____ dependencies.

partial

An attribute that is part of a key is known as a(n) ____ attribute.

prime

A ____ derives its name from the fact that a group of multiple entries of the same type can exist for any single key attribute occurrence.

repeating group

A relational table must not contain a(n) ____.

repeating group

A(n) ____ is a query that is embedded (or nested) inside another query.

subquery

A(n) ____ exists when there are functional dependencies such that Y is functionally dependent on X and Z is functionally dependent on Y, and X is the primary key.

transitive dependency