Final Exam Review

Created by jwong115 

Upgrade to
remove ads

The purpose of a database is to help people keep track of things.

True

A relational database stores data in the form of lists.

False

A database is a set of one or more computer programs that serves as an intermediary between the users and the database management system (DBMS).

False

In the general division of labor between database applications and the DBMS, the application program determines which tables need to be modified.

True

Which of the following problems associated with storing data in a list is avoided by storing data in a relational database? A) Maintaining the data may require changing the same data value in many locations.
B) Inconsistency when a data item is used multiple times
C) Inability to store partial data
D) Duplication of data items
E) All of the above

E) All of the above

Today almost every commercial database is based on:

the relational model

A relational database stores data in the form of:

Tables

A relational database is:

a self-describing collection of related tables.

The component of a database that makes it self-describing is the:

metadata

Which of the following would not be an example of database metadata?

Queries against records in the database tables

Every relation is a table, but not every table is a relation.

True

Every cell in a relation can hold only a single value.

True

To be considered a composite key, a key must contain at least two attributes.

True

Candidate keys may or may not be unique.

False

The primary key is used both to identify unique rows in a relation and to represent rows in relationships.

True

If the condition exists such that knowing the value of attribute X determines the value attribute Y, then attribute X is functionally dependent on attribute Y.

False

Given the functional dependency for the attributes of ENTITY1, X → (A, B, C), X is a candidate key for the relation ENTITY1 (A, B, C, X).

True

Any table that meets the definition of a relation is said to be in second normal form.

False

In the normalization process, it is not necessary to identify all the functional dependencies in a relation.

False

In the normalization process, if you find that every determinant in a relation is a candidate key, then you have determined that the relation is well formed.

True

Which of the following is not true about a relation?

A) A relation is a two-dimensional table.

B) The cells of a relation must hold a single value.

C) A relation may have duplicate column names.***

D) A relation does not have duplicate rows.

E) The order of the rows of a relation is insignificant.

Which of the following terms is synonymous with "tuple"?

Row

A primary key is:

A) required to be unique.

B) used to represent rows in relationships.

C) a candidate key.

D) used to identify unique rows.

E) All of the above***

A candidate key is:

A) required to be unique for each row.

B) used to represent rows in relationships.

C) a candidate to be the primary key.

D) Both A and B

E) Both A and C***

STUDENT (SID, StudentName, Major, AdvisorID)

ADVISOR (AdvisorID, AdvisorName, Office, Phone)

Given the relations above such that each student is assigned to one advisor, which of the following is true?

AdvisorID is a foreign key in the Student relation.

A rule that requires that the values in a foreign key must have a matching value in the primary key to which the foreign key corresponds is called:

a referential integrity constraint

MedicineCode → (MedicineName, ShelfLife, Manufacturer, Dosage)

Given the above functional dependency, which of the following statement is not known to be true?

MedicineName is a determinant.

One important relational design principle is that:

every determinant must be a candidate key.

The first step of the normalization process is to:

identify all the candidate keys of a relation.

A table that meets the requirements of a relation is said to be in which normal form?

First normal form

SQL is a data sublanguage, not a complete programming language.

True

When using SQL to create a table, a column is defined by declaring, in this order: data type, column name, and optional constraints.

False

When using SQL to create a table, specifying the NULL property for a column indicates that only null values may be stored in that column.

False

When using SQL to create a table, specifying a data type of Char(10) indicates a fixed length field of 10 characters.

True

Data is added to a table using the SQL INSERT command.

True

The result for SELECT statements in SQL is a relation unless the result is a single number.

False

To force the DBMS to remove duplicate rows from the results of an SQL SELECT query, the keyword DISTINCT must be used.

True

In SQL, to refer to a range in a WHERE clause, use the WITHIN keyword.

False

In SQL, the order of the rows that result from a SELECT statement can be set using the SORT BY phrase.

False

A subquery is appropriate only if the final result contains data from a single table.

True

The basic idea of a join is to combine the contents of two or more relations into a new relation.

True

Values of existing data can be changed using SQL through the CHANGE command.

False

Which of the following is not true about primary keys?

A) Primary keys cannot be null.

B) Primary keys must be unique.

C) Primary keys must be a single attribute.***

D) Primary keys are used to represent relationships.

E) Primary keys can be defined using an SQL CONSTRAINT phrase.

Which of the following cannot be done using the CONSTRAINT phrase?

A) Create a single attribute primary key.

B) Define a foreign key.

C) Establish a referential integrity constraint.

D) All of the above can be done using the CONSTRAINT phrase.***

E) None of the above can be done using the CONSTRAINT phrase.

The order of the columns returned by an SQL SELECT statement are determined by the:

order they are listed in following SELECT.

Which of the following is the correct SQL clause to restrict the results of a SELECT query to only records that have a value in the range of 10 to 50 in the Hours column?

WHERE Hours BETWEEN 10 AND 50

Which of the following is the correct SQL clause to sort the results of a SELECT query in reverse-alphabetic order using the Department field?

ORDER BY Department DESC

Which of the following SQL commands would be used to remove both the data and the table structure of a table named STUDENT?

DROP TABLE STUDENT;

Which SQL keyword is used to apply conditions to restrict groups that appear in the results of a SELECT query that uses GROUP BY?

HAVING

Which SQL keyword can be used in conjunction with wildcards to select partial values?

LIKE

When developing a database system, the database is constructed during the implementation phase.

True

Use cases can be used to validate the data model, design, and implementation.

True

System requirements as documented by use cases, business rules, forms, reports, and queries are sufficient to design the database without using a formal data model.

False

The E-R Model assumes that all instances of a given entity class have the same attributes.

True

In crow's foot E-R notation, a circle indicates a minimum cardinality of zero.

True

Subtype entities may be either exclusive or inclusive.

True

Which of the following is not a basic element of the E-R Model?

A) Identifiers

B) Entities

C) Cardinalities

D) Use cases***

E) Relationships

Which of the following is not a source for user requirements during the requirements analysis stage of developing a database system?

A) Business rules

B) User interviews

C) Forms

D) Use cases

E) All of the above are sources for user requirements***

In the Entity-Relationship data model, all instances of an entity of a given type are grouped into:

entity class

The number of entity classes involved in a relationship is known as the relationship's:

degree

If a weak entity is ID-dependent but not existence-dependent, it can be represented using the same techniques as a strong entity.

False

The key of the parent entity becomes part of the key of an ID-dependent entity.

True

From a pragmatic standpoint, the only important rule of normalization is that the determinant of every functional dependency must be a candidate key.

True

To represent a 1:N relationship in the relational model, the key of the entity on the ONE side of the relationship is placed as a foreign key in the entity on the MANY side of the relationship.

True

To represent a 1:1 relationship in the relational model, the key of either entity may be placed as a foreign key in the other entity.

True

To represent a M:N relationship in the relational model, an intersection relation is created to represent the relationship itself.

True

The key for an intersection relation is always the combination of the keys of the parent entities.

True

All recursive relationships are 1:1.

False

Which of the following is the first step in representing entities using the relational model?

Define a table for each entity.

Which of the following would be a reason to denormalize a relation?

Improve performance

The purpose of concurrency control is to ensure that one user's work does not inappropriately influence another user's work.

True

All actions in an atomic transaction are completed successfully or none of the actions are completed at all.

True

One way to prevent concurrent processing problems is to lock data that are retrieved for update.

True

Lock granularity refers to whether a lock is placed by the DBMS automatically or is placed by a command from the application program.

False

Two-phased locking can be used to ensure that transactions are serializable.

True

With optimistic locking, locks are first issued, then the transaction is processed, and then the locks are released.

False

Generally, the more restrictive the transaction isolation level, the less the throughput of database processing.

True

In terms of database security, a user can be assigned many roles, and a role can be assigned to many users.

True

The simplest and most feasible means of recovering a database is through reprocessing.

False

Rollback requires the use of logs, but rollforward does not.

False

Transactions should be written to the log before they are applied to the database itself.

True

An ACID transaction is one that is:

A) atomic

B) consistent

C) isolated

D) durable

E) all of the above***

Preventing multiple applications from obtaining copies of the same record when the record is about to changed is called:

resource locking.

Which of the following is not a means of processing databases?

A) Internet applications using ASP

B) Internet applications using JSP

C) Applications invoking stored procedures

D) Programs coded in C#

E) All of the above are means of processing a database.***

What concurrent processing problem occurs when a transaction rereads data and finds new rows that were inserted by a different transaction since the prior read?

Phantom reads

SQL stands for Structural Question Language.

False

Metadata is the user data stored in the database.

False

The statement:
SELECT STUDENT.StudentNumber, STUDENT.StudentName,

FROM STUDENT

WHERE STUDENT.StudentNumber = S12345678;

is an example of:

SQL

When used to represent a relationship, the primary key must have the same name as the corresponding foreign key.

False

If the condition exists such that knowing the value of attribute X determines the value attribute Y, then attribute Y is functionally dependent on attribute X.

True

To create a well-formed relation through normalization, every determinant must be a candidate key.

True

Which of the following is true about a relation?

All entries in any column must be of the same kind.

In the normalization process, if you find that every determinant in a relation is a candidate key when you have determined that:

the relation is well formed.

The SUM built-in function in SQL is used to total the values in a numeric column.

True

The TOP built-in function in SQL is used to find the maximum value in a numeric column.

False

A composite primary key can be defined using the CONSTRAINT phrase in which SQL command?

CREATE TABLE

Briefly describe four components of a database system.

1. User- can enter & create data, queries.
2. Database Application- has apps that run he database.
3. Database Management System (DBMS)- manages the whole data system/apps; contains the metadata in the database.
4. Database- contains all the information needed from the other 3 components of a database system to run; self describing.

Explain the terms relation, tuple, and attribute.

Relation= Table, File
Tuple= Row, Record
Attribute= Column, Field

The number of entity classes involved in a relationshop is the cardinality of the relationship.

False

It is possible for an entity to have a relationship to itself.

True

In crow's foot E-R notation, the crow's foot indicated a maximum cardinality of one.

False

Which of the following activities is not performed during the implementation phase of developing a database system?

Transforming the datamodel into a database design.

An ID-dependent entity is:

A) a special type of weak entity.

B) an entity in which the identifier of the entity includes the identifier of another entity.

C) a strong entity.

D) an entity with a maximum cardinality of one.

E) Both A and B***

When applied to 1:N relationships, the term "parent" refers to the many side of the relationship.

False

The fundamental rule of normalization can be stated as:

every determinant must be a candidate key.

Concurrent processing of a database only poses problems when two or more processes attempt to use the same data.

True

What concurrent processing problem occurs when a transaction reads a changed record that has not been committed to the database?

Dirty reads

Whether a lock applies to data at the record level, page level, table level, or database level is referred to as:

lock granularity

Which type of lock assumes that no conflicts will occur?

Optimistic locks

Distinguish between an entity class and an entity instance.

Entity class= describes the structure & format of the occurrences of an entity.
Ex: Student, can stand alone

Entity instance= specific occurrences of an entity within a entity class.
Ex: Mary Smith, can't stand alone

Briefly explain the strategy of two-phase locking.

1. Growing lock - when the transaction continues to request additional locks.
2. Shrinking lock - when the transaction starts to release the locks.

Explain the process of using PESSIMISTIC locking.

1. Lock required resources.
2. Reads data.
3. Process transaction.
4. Issue commit.
5. Releases Locks.

Please allow access to your computer’s microphone to use Voice Recording.

Having trouble? Click here for help.

We can’t access your microphone!

Click the icon above to update your browser permissions above and try again

Example:

Reload the page to try again!

Reload

Press Cmd-0 to reset your zoom

Press Ctrl-0 to reset your zoom

It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

Please upgrade Flash or install Chrome
to use Voice Recording.

For more help, see our troubleshooting page.

Your microphone is muted

For help fixing this issue, see this FAQ.

Star this term

You can study starred terms together

NEW! Voice Recording

Create Set