CPSC 4620 Final

The _____ specification creates an individual index on a respective attribute; use it to avoid having duplicated values in a column. UNIQUE VARCHAR NOT NULL UPDATE

UNIQUE

_____ data exist in the format in which they were collected. Structured Historical Semistructured Unstructured

Unstructured

A(n) _____ is the set of possible values for a given attribute. domain range identifier ke

domain

A(n) _____ database is used by an organization and supports many users across many departments. enterprise workgroup transactional desktop

enterprise

The default comparison condition for the subtype discriminator attribute is the _____ comparison. less than greater than nonequality equality

equality

When two or more tables share the same number of columns, and when their corresponding columns share the same or compatible domains, they are said to be _____. difference-compatible union-compatible intersect-compatible select-compatible

union-compatible

A _____ entity has a primary key that is partially or totally derived from the parent entity in the relationship. child weak business strong

weak

What type of command does this SQL statement use? SELECT P_CODE, P_DESCRIPT, P_PRICE_V_NAME FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE=VENDOR. V_CODE "old-style" join procedural statement set operator natural join

"old-style" join

The Crow's foot symbol with two vertical parallel lines indicates _____ cardinality. (1,N) (0,1) (1,1) (0,N)

(1,1)

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 _____. 3NF 1NF 4NF 2NF

1NF

From a structural point of view, 2NF is better than _____. 1NF BCNF 4NF 3NF

1NF

From a structural point of view, 3NF is better than _____. 6NF 3NF 5NF 2NF

2NF

A table that is in 2NF and contains no transitive dependencies is said to be in _____. 1NF 2NF 3NF 4NF

3NF

All changes in a table structure are made using the _____ TABLE command, followed by a keyword that produces the specific changes a user wants to make. UPDATE ALTER ROLLBACK COMMIT

ALTER

_____ requires that all operations of a transaction be completed. Atomicity Durability Time stamping Specificity

Atomicity

The special operator used to check whether an attribute value is within a range of values is _____. IN BETWEEN NULL LIKE

BETWEEN

The SQL command that allows a user to permanently save data changes is _____. COMMIT UPDATE INSERT SELECT

COMMIT

The _____ command permanently saves all changes—such as rows added, attributes modified, and rows deleted—made to any table in the database. SELECT ROLLBACK UPDATE COMMIT

COMMIT

The SQL aggregate function that gives the number of rows containing non-null values for a given column is _____. MAX COUNT SUM MIN

COUNT

Using the _____ command, SQL indexes can be created on the basis of any selected attribute. CREATE INDEX UPDATE CASCADE SELECT VARCHAR

CREATE INDEX

The _____ constraint assigns a value to an attribute when a new row is added to a table. NOT NULL DEFAULT CASCADE UNIQUE

DEFAULT

Which command would be used to delete the table row where the P_CODE is 'BRT-345'? REMOVE FROM PRODUCT WHERE P_CODE = 'BRT-345'; ERASE FROM PRODUCT WHERE P_CODE = 'BRT-345'; DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345'; ROLLBACK FROM PRODUCT WHERE P_CODE = 'BRT-345';

DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';

A table can be deleted from the database by using the _____ TABLE command. DELETE ERASE MODIFY DROP

DROP

_____ can render data useless to unauthorized users who might have violated some of the database security layers. Data encryption Password security Physical security Access rights

Data encryption

_____ exists when different versions of the same data appear in different places. Conceptual dependence Data inconsistency Structural dependence Poor data security

Data inconsistency

SQL requires the use of the _____ command to enter data into a table. INSERT SELECT COMMIT NOT NULL

INSERT

_____ is a string function that returns the number of characters in a string value. SUBSTRING CONCAT UCASE LENGTH

LENGTH

According to the rules of precedence, which of the following computations should be completed first? Operations within parentheses Multiplications and divisions Additions and subtractions Power operations

Operations within parentheses

_____ are especially useful when you are trying to determine what values in related tables cause referential integrity problems. Equijoins Outer joins Inner joins Theta joins

Outer Joins

_____ is the process of determining the data storage organization and data access characteristics of the database to ensure its integrity, security, and performance. Logical design Network design Conceptual design Physical design

Physical design

The _____ command is used to restore the database to its previous condition. ROLLBACK BACKUP ROWCOUNT COMMIT

ROLLBACK

A(n) _____ query specifies which data should be retrieved and how it should be filtered, aggregated, and displayed. SELECT INSERT COMMIT UPDATE

SELECT

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 ONLY V_CODE FROM PRODUCT; SELECT DISTINCT V_CODE FROM PRODUCT; SELECT UNIQUE V_CODE FROM PRODUCT; SELECT DIFFERENT V_CODE FROM PRODUCT;

SELECT DISTINCT V_CODE FROM PRODUCT;

The syntax for a left outer join is _____. SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition SELECT column-list WHERE LEFT table1 = table SELECT column-list FROM table1 LEFT table2 [JOIN] WHERE join-condition SELECT column-list FROM table1 OUTER JOIN table2 LEFT WHERE join-condition

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

Which SQL format would be best used for a small, numeric data type? SMALLINT CHAR(L) INTEGER NUMERIC(L,D)

SMALLINT

Which is a feature of a correlated subquery? The inner subquery executes first. The outer subquery initiates the process of execution in a subquery. The outer subquery executes independent of the inner subquery. The inner subquery initiates the process of execution in a subquery.

The outer subquery initiates the process of execution in a subquery.

_____ is a technique that creates logical representations of computing resources that are independent of the underlying physical computing resources. Specialization Generalization Normalization Virtualization

Virtualization

When a user issues the DELETE FROM tablename command without specifying a WHERE condition, _____. all rows will be deleted the first row will be deleted no rows will be deleted the last row will be deleted

all rows will be deleted

A(n) _____ is the equivalent of a field in a file system. constraint attribute relationship entity

attribute

The tables on which a view, or a virtual table derived from a SELECT query, are based are called _____ tables. core indexed relation base

base

Another name for a composite entity is a(n) _____ entity. directive bridge linked associative

bridge

A _____ key can be described as a minimal superkey, a superkey without any unnecessary attributes. foreign candidate primary secondary

candidate

An atomic attribute _____. cannot be further subdivided displays multiplicity is always chosen to be a foreign key cannot exist in a relational table

cannot be further subdivided

The database structure in a DBMS is stored as a _____. single file collection of queries set of key/value pairs collection of files

collection of files

A _____ attribute can be further subdivided to yield additional attributes. simple multivalued single-valued composite

composite

A(n) _____ is a restriction placed on the data. relationship attribute entity constraint

constraint

_____ are normally expressed in the form of rules. Attributes Relationships Constraints Entities

constraints

In the context of the database design process, the conceptual design step that determines end-user views, outputs, and transaction-processing requirements is _____. distributed database design entity relationship modeling and normalization data analysis and requirements data model verification

data analysis and requirements

Once the data has been loaded into the database, the _____ tests and fine-tunes the database for performance, integrity, concurrent access, and security constraints. manager database administrator systems administrator programmer

database administrator

From a system functionality point of view, _____ attribute values can be calculated when they are needed to write reports or invoices. historical derived granular atomic

derived

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. derives from controls determines owns

determines

When you create a new database, the RDBMS automatically creates the data _____ tables in which to store the metadata and creates a default database administrator. appendix dictionary index chapter

dictionary

In a(n) _____, only the objects that have been updated or modified since the last full backup are backed up. adaptive backup differential backup conservative backup transaction log backup

differential backup

In the context of the database design process, the conceptual design step that defines the fragmentation and allocation strategy is _____. ER modeling and normalization database analysis and requirements distributed database design data model verification

distributed database design

A(n) _____ represents a particular type of object in the real world. attribute relationship entity node

entity

The CUSTOMER table's primary key is CUS_CODE. The CUSTOMER primary key column has no null entries, and all entries are unique. This is an example of _____ integrity. entity relational null referential

entity

The _____ model was developed to allow designers to use a graphical tool to examine structures rather than describing them with text. entity relationship network hierarchical object-oriented

entity relationship

A _____ is a character or group of characters that has a specific meaning. file field database record

field

In a real-world environment, we must strike a balance between design integrity and _____. ease of use robustness flexibility uniqueness

flexibility

A _____ is the primary key of one table that has been placed into another table to create a common attribute. foreign key superkey candidate key composite primary key

foreign key

Lock _____ indicates the level of lock use. shrinking growing granularity serializability

granularity

The first step in developing the conceptual model using ER diagrams is to _____. define the attributes, primary keys, and foreign keys for each of the entities identify, analyze, and refine the business rules normalize the entities complete the initial ER diagram

identify, analyze, and refine the business rules

A(n) _____ cursor is automatically created in procedural SQL when the SQL statement returns only one value. dynamic implicit static explicit

implicit

When using a(n) _____ join, only rows from the tables that match on a common value are returned. outer inner set full

inner

A(n) _____ only returns matched records from the tables that are being joined. equijoin outer join inner join theta join

inner join

A specialization hierarchy can have _____ level(s) of supertype/subtype relationships. zero only one one or many many

many

In a _____ situation, one key determines multiple values of two other attributes and those attributes are independent of each other. functional dependency multivalued dependency transitive dependency partial dependency

multivalued dependency

A _____ key is a real-world, generally accepted identifier used to uniquely identify real-world objects. foreign natural surrogate primary

natural

A(n) _____ join will select only the rows with matching values in the common attribute(s). outer cross natural full

natural

The most likely data type for a surrogate key is _____. numeric character date logical

numeric

Within a specialization hierarchy, every subtype can have _____ supertype(s) to which it is directly related. zero one or many many only one

only one

A data warehouse contains historical data obtained from the _____. operational databases enterprise databases desktop database workgroup databases

operational databases

The existence of a(n) _____ entity indicates that its minimum cardinality is zero. strong weak optional ternary

optional

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. cross inner outer equi-

outer

In subquery terminology, the first query in the SQL statement is known as the _____ query. base inner outer left

outer

A _____ lock will lock the entire diskpage. transaction-level page-level table-level row-level

page-level

When writing SQL table-creating command sequences, the entire table definition is enclosed in _____. quotations parentheses asterisks commas

parentheses

If a table has multiple candidate keys and one of those candidate keys is a composite key, the table can have _____ based on this composite candidate key even when the primary key chosen is a single attribute. partial dependencies Boyce-Codd normal forms redundancies time-variances

partial dependencies

One important inheritance characteristic is that all entity subtypes inherit their _____ key attribute from their supertype. primary surrogate foreign natural

primary

A _____ is a logically connected set of one or more fields that describes a person, place, or thing. column database record file

record

You cannot have an invalid entry in the foreign key column; at the same time, you cannot delete a vendor row as long as a product row references that vendor. This is known as _____. referential integrity cross joining authentication batch updating

referential integrity

A verb associating two nouns in a business rule translates to a(n) _____ in the data model. entity attribute relationship constraint

relationship

A(n) _____ is bidirectional. entity relationship attribute constraint

relationship

A _____ derives its name from the fact that a collection of multiple entries of the same type can exist for any single key attribute occurrence. repeating group primary key partial dependency transitive dependency

repeating group

A relational table must not contain a(n) _____ relationship repeating group entity attribute

repeating group

Words used by a system that cannot be used for any other purpose are called _____ words. For example, the word GROUP cannot be used to name tables or columns. reserved unique null character

reserved

The SQL data manipulation command HAVING: restricts the selection of rows based on a conditional expression. modifies an attribute's values in one or more table's rows. restricts the selection of grouped rows based on a condition. groups the selected rows based on one or more attributes.

restricts the selection of grouped rows based on a condition.

A _____ key is defined as a key that is used strictly for data retrieval purposes. secondary foreign candidate lookup

secondary

A(n) _____ lock exists when concurrent transactions are granted read access on the basis of a common lock. shared binary exclusive two-phase

shared

A(n) _____ phase in a two phase lock is when a transaction releases all locks and cannot obtain a new lock. shrinking unlocking locking growing

shrinking

A(n) _____ is a query that is embedded (or nested) inside another query. view alias operator subquery

subquery

A(n) _____ is the attribute in the supertype entity that determines to which entity subtype each supertype occurrence is related. subtype discriminator entity supertype specialization hierarchy inheritance discriminator

subtype discriminator

A _____ is a primary key created by a database designer to simplify the identification of entity instances. compound key natural key surrogate key composite key

surrogate key

When the specific cardinalities are not included on the diagram in Crow's Foot notation, cardinality is implied by the use of _____. symbols tables images attributes

symbols

The process of creating an information system is known as _____. network development database development systems development enterprise development

systems development

The CREATE TABLE command lets you define constraints when you use the CONSTRAINT keyword, known as a(n) _____ constraint. column index cell table

table

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. two three four five

three

A(n) _____ exists when there are functional dependencies such that Y is functionally dependent on X, Z is functionally dependent on Y, and X is the primary key. partial dependency repeating group atomic attribute transitive dependency

transitive dependency

What SQL construct could be used for automatically maintaining audit log data? triggers tables stored procedures stored functions

triggers

A _____ relationship exists when an association is maintained within a single entity. weak unary strong ternary

unary