CIS112 Midterm

Vertical partitioning ____.

- means dividing the columns of a table into groups and storing the groups separately, on different areas of a disk or on different disks - may involve splitting large text attributes off from the rest of a table

A relational table in third normal form ____.

-is also in first normal form, -is also in second normal form, and -has no transitive dependencies

Data normalization ____.

-is capable of creating relational tables that are free of data redundancy, -is a logical database design technique, and -can be used to check the accuracy of the E-R diagrams to relational tables conversion technique

What is true regarding indexed sequential files and B+-trees?

A B+-tree indexed file is an indexed sequential file.

What is true about indexes, in general?

An index can be used to retrieve the records of a file in logical sequence based on the indexed field.

The most common data indexing system in use today is the _____.

B+-tree index

What are logical database design techniques?

Conversion of E-R diagrams to relational tables and Data normalization.

What is a potential problem caused by adding too many indexes to a relational database?

Updating the indexes when the data is updated can slow down queries executing at the same time.

In a hashed file using the division remainder method _____.

a collision results from the key values of two records having the same remainder in the hashing calculation

Taking application priorities into account in physical database design is important because ____.

a modification to a table that's designed to help the performance of one application may hinder the performance of another application

All of the following are factors affecting application and database performance, EXCEPT ____. a. the length of attribute names b. large data volumes c. the number of data access operations d. the need for joins e. related data dispersed on the disk

a. the length of attribute names

All of the following are characteristics of an indexed computer file and its index, EXCEPT _____. a. the records of the file are sorted into the same order as the values in the index b. a set of field values is copied from the file into the index c. the field values copied into the index are sorted in the index d. the field values in the index are associated with the location of the records in the file from which they were copied e. the index consists of two columns, one for the field values copied from the file and one for the pointers back to the records of the file.

a. the records of the file are sorted into the same order as the values in the index

All of the following are advantages of the B+-tree index arrangement, compared to the simple linear index arrangement, EXCEPT _____. a. The upper portion of the tree index can be held in main memory indefinitely to improve performance. b. The insertion of a new record requires either a track split or a cylinder split, but not both for a single insertion. c. Even for large files, only a small number of index records have to be read for a direct search. d. The nature of the index together with the track split and cylinder split capabilities make record insertions manageable. e. Some insertions can be made without track splits or cylinder splits if reserve space has been provided on the data tracks.

b. The insertion of a new record requires either a track split or a cylinder split, but not both for a single insertion.

All of the following are true about B+-tree indexes and their files, EXCEPT _____. a. The records of the file are stored in order by key value. b. Both a number of tracks and a number of cylinders are held empty in reserve for new record insertion purposes. c. Every insertion of a new record requires the use of an empty reserve track or cylinder. d. It is possible that a new record insertion can increase the number of levels of the index tree. e. The lowest level of the index is stored on the same cylinders as the data.

c. Every insertion of a new record requires the use of an empty reserve track or cylinder.

Hashed files _____.

can also have indexes built over them

Converting a many-to-many unary relationship into a relational database from an E-R diagram ____.

can be done by adding another copy of the unique identifier of the single entity's table into the same table with a different attribute name

Converting a one-to-many unary relationship into a relational database from an E-R diagram ____.

can be done by adding another copy of the unique identifier of the single entity's table into the same table with a different attribute name

Converting a one-to-one unary relationship into a relational database from an E-R diagram ____.

can be done by adding another copy of the unique identifier of the single entity's table into the same table with a different attribute name

Converting a one-to-many binary relationship into a relational database from an E-R diagram ____.

can be done by placing the unique identifier of one of the tables into the other

Converting a one-to-one binary relationship into a relational database from an E-R diagram ____.

can be done by placing the unique identifier of one of the tables into the other OR by creating a single table that combines the attributes of the two entities

In a hashed file, synonym pointer chains _____.

can include only one record in the main storage area

Consider Table A on the "one side" of a one-to-many relationship and Table B on the "many side" of the relationship. The primary key of Table A has been placed in Table B as a foreign key. The substituting foreign keys physical database design technique ____.

can only be performed if Table A has more than one candidate key

Consider Table A on the "one side" of a one-to-many relationship and Table B on the "many side" of the relationship. Denormalization ____.

combines the data in the two tables into one table

Placing the unique identifier of one database table in another existing or new table in the database ____.

creates a foreign key, may indicate the creation of a one-to-many binary relationship, and may indicate the creation of a many-to-many binary relationship

In a magnetic disk device, a set of tracks, one above the other (and one from each recording surface) is called a _____.

cylinder

All of the following are good candidates for having an index built over them, EXCEPT ____. a. foreign key b. search attribute c. primary key d. All of the above. e. None of the above.

d. All of the above.

Application data requirements that enter into the physical database design process include ____. a. the number of applications that will share particular database tables b. the frequency with which applications that utilize particular tables are run c. whether or not particular applications require that tables be joined d. All of the above.

d. All of the above.

If the primary key of a table is clumsy because it has too many attributes and is replaced by a new, single attribute key, ____. a. it becomes the new primary key of its own table b. it should replace the old primary key where it was used as a foreign key for one-to-many relationships c. it should replace the old primary key where it was used as a foreign key for many-to-many relationships d. All of the above. e. None of the above.

d. All of the above.

In the adding new tables physical database design technique ____. a. subsets of the most heavily accessed portions of existing tables can be copied and created as new tables b. entire tables can be copied and new tables created c. data redundancy across tables is traded-off against improved performance d. All of the above. e. None of the above.

d. All of the above.

Which of the following is true regarding hashed files? a. The hashing algorithm may direct more than one record to the same location. b. The number of record storage locations allocated is typically much fewer than the maximum number possible based on the range of values of the key field. c. The same hashing algorithm is used for both record insertions and record retrievals. d. All of the above.

d. All of the above.

Which of the following regarding checking the results of one logical database design technique with another is TRUE? a. Neither the E-R diagram conversion process result nor the data normalization result can be checked with the other process. b. The E-R diagram conversion process result and the data normalization result can each be checked with the other process. c. The result of data normalization is checked with the E-R diagram conversion process. d. The result of the E-R diagram conversion process is checked with data normalization. e. None of the above.

d. The result of the E-R diagram conversion process is checked with data normalization.

All of the following are inputs into the physical database design process, EXCEPT ____. a. application data requirements b. throughput requirements c. hardware characteristics d. functional dependencies e. application priorities

d. functional dependencies

The frequency with which stored data is updated is known as ____.

data volatility

The term that describes an attribute whose value establishes the value of another attribute is ____.

determinant

All of the following are true about B+-tree indexes and their files, EXCEPT _____. a. A file with a B+-tree index built over the field by which the file is stored in sequence is an indexed sequential file. b. A track split might lead to a cylinder split. c. A track split might not lead to a cylinder split. d. A cylinder split must lead to a change in the tree index. e. A cylinder split must lead to an increase in the number of levels of the index tree.

e. A cylinder split must lead to an increase in the number of levels of the index tree.

Referential integrity ____. a. is ONLY an issue in the relational database approach b. is NOT an issue in the relational database approach c. involves individual relations in isolation d. ONLY involves relations that contain redundant data e. None of the above

e. None of the above

A relational table in first normal form ____. a. is also in second normal form b. is also in third normal form c. has no transitive dependencies d. has no determinants e. None of the above.

e. None of the above.

A ternary relationship ____. a. CANNOT have intersection data associated with it b. stores its intersection data in the three relations created to represent the three binary many-to-many relationships that form the ternary relationship c. stores its intersection data in the three relations that represent the three entity types in the ternary relationship d. stores its intersection data in one of the three relations that represent the three entity types in the ternary relationship e. None of the above.

e. None of the above.

A ternary relationship is the equivalent of ____ binary many-to-many relationship(s). a. one b. two c. three d. four e. None of the above.

e. None of the above.

Logical database design ____. a. is a part of physical database design b. includes physical database design as one of its elements c. results in a set of database tables that contain redundant data d. results in a set of database tables that are optimized for performance e. None of the above.

e. None of the above.

The storing derived data physical database design technique is designed to ____. a. reduce the number of joins b. reduce the number of indexes c. replace clumsy keys that have a large number of attributes d. All of the above. e. None of the above.

e. None of the above.

All of the following are factors affecting application and database performance, EXCEPT ____. a. overly liberal data access b. the need to calculate totals c. clumsy primary keys d. the lack of direct access e. the presence of primary keys

e. the presence of primary keys

All of the following are true about secondary memory devices, EXCEPT _____. a. they include magnetic disk b. they are necessary because primary memory cannot hold all of a company's data and programs c. they are non-volatile d. data and programs are loaded from secondary memory into primary memory for processing e. they are volatile

e. they are volatile

Data in ____ has only a single attribute value in every attribute position.

first normal form, second normal form, and third normal form

In third normal form, the exception to the rule that a non-key attribute cannot define another non-key attribute is ____.

if the defining attribute is a candidate key

Consider Table A on the "one side" of a one-to-many relationship and Table B on the "many side" of the relationship. In the storing derived data physical database design technique, the best place to store the derived data from Table B is ____.

in a new column in Table A

Referential integrity is an issue in record ____.

insertion, deletion, and update

The drawback of denormalization is that it ____.

introduces data redundancy

In the diagram, the Course file and its index _____.

is NOT an indexed sequential file because the indexed field is not the one on which the file is sequenced

A relational table in second normal form ____.

is also in third normal form

The clustering files physical database design technique ____.

is designed to improve the performance of a join operation

Combining two tables that represent a one-to-one relationship ____.

is designed to reduce the number of joins in the database environment

Horizontal partitioning ____.

means dividing the rows of a table into groups and storing the groups separately, on different areas of a disk or on different disks

Consider relations A and B. Relation A represents the entity on the "one side" of a one-to-many relationship; Relation B represents the entity on the "many side" of the one-to-many relationship. The delete rule between relations A and B is restrict. If an attempt is made to delete a record in relation A and its primary key value appears as a foreign key value in one or more records of relation B ____.

no records will be deleted

In terms of time, rotational delay in a disk device takes on average roughly _____.

one half of a revolution

The term that describes a non-key field of a relational table being dependent on only part of the table's key is ____.

partial functional dependency

Denormalization can improve the ____.

performance of the database by reducing the number of join operations

Data normalization ____.

precedes physical database design

A logical view is an important device in ____.

protecting the security of the data

In a magnetic disk device, the number of tracks in a cylinder is equal to the number of _____.

recording surfaces

The advantage of using the substituting foreign keys physical database design technique is that it is designed to ____.

reduce the number of joins

In the clustering files physical database design technique, ____ are clustered near each other on a disk.

related records from different tables

The creating a new primary key physical database design technique is designed to ____.

replace clumsy keys that have a large number of attributes

Representing a unary one-to-many relationship in a relational database ____.

requires adding a new column, the domain of values of which is the same as the relation's primary key

Representing a ternary relationship in a relational database ____.

requires the creation of an additional relation

Representing a unary many-to-many relationship in a relational database ____.

requires the creation of an additional relation

Converting a ternary relationship into a relational database from an E-R diagram ____.

requires the creation of an additional table beyond the three that represent the three entities in the relationship

Converting a many-to-many binary relationship into a relational database from an E-R diagram ____.

requires the creation of an additional table beyond the two that represent the two entities in the relationship

The delay from the time that the Enter Key is pressed to execute a query until the result appears on the screen is called ____.

response time

All of the following are steps in finding and transferring data from disk, except _____.

scan time

Data in ____ cannot have partial functional dependencies but can have transitive dependencies.

second normal form

In the diagram, for the Course file and its index, ___________.

the Course Name field in the index must be in sorted order.

The common advantage of separating portions of a table's data using either horizontal or vertical partitioning is that ____.

the data in a partition can be stored in a concentrated space on the disk, which will speed up its retrieval

In an indexed sequential file _____.

the field on which the file is sequenced must be indexed

In the division remainder hashing algorithm, the divisor (the number divided into the other number) is _____.

the number of main record storage locations (not including overflow locations)

In terms of performance, there is no point in indexing a table if ____.

the number of rows of the table is very small

The starting point of the physical database design process is ____.

the output of the logical database design process

Consider relations A and B. Relation A represents the entity on the "one side" of a one-to-many relationship; Relation B represents the entity on the "many side" of the one-to-many relationship. The delete rule between relations A and B is cascade. If an attempt is made to delete a record in relation A and its primary key value appears as a foreign key value in one or more records of relation B ____.

the record in relation A and all of the records in relation B with foreign key values that match the primary key value of the record in relation A will be deleted

Consider relations A and B. Relation A represents the entity on the "one side" of a one-to-many relationship; Relation B represents the entity on the "many side" of the one-to-many relationship. The delete rule between relations A and B is set-to-null. If an attempt is made to delete a record in relation A and its primary key value appears as a foreign key value in one or more records of relation B ____.

the record in relation A will be the only record deleted and the foreign key values in relation B that match the primary key value of the record in relation A will be updated to null

Consider relations A and B. Relation A represents the entity on the "one side" of a one-to-many relationship; Relation B represents the entity on the "many side" of the one-to-many relationship. The delete rule between relations A and B is restrict. If an attempt is made to delete a record in relation B ____.

the record in relation B will be the only record deleted and no other changes will occur

Using the division remainder algorithm for a hashed file _____.

the record insertion location is based on the division's remainder

An access method is _____.

the way that data is retrieved based on the file organization in use

Consider Table A on the "one side" of a one-to-many relationship and Table B on the "many side" of the relationship. The "repeating groups" in Table B may be considered for merging into the related records of Table A if ____.

there is a small number of occurrences of the repeating groups of Table B for each record of Table A

Data in ____ cannot have transitive dependencies.

third normal form

A ternary relationship involves ____ entity type(s).

three

The goal of logical database design is ____.

to create well-structured tables that properly reflect the company's business environment

Physical database design is necessary ____.

to improve the performance of the run-time environment.

In a magnetic disk device, the reason for storing data on a cylinder-wise basis is _____.

to minimize access arm movement in order to improve performance

In a magnetic disk device, a single circle of stored data is called a _____.

track

In a magnetic disk device, the number of positions to which the access arm mechanism can move is equal to the number of _____.

tracks on a recording surface

The term that describes one non-key field of a table being dependent on another non-key field of the table is ____.

transitive dependency

The Bill of Materials problem is an example of a ____.

unary many-to-many relationship

Direct access can be achieved _____.

with either an index or a hashing method