Database Management: Exam 2 (Ch. 4 & 5)

A domain definition consists of the following components:

- domain name - meaning - data type - size (or length) - allowable values or - allowable range (if applicable)

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

1) Data accessibility 2) Response times 3) Security

An integrity control supported by a DBMS is:

1) Default Value 2) Range Control 3) Null Value Control 4) Referential Integrity

Advantages of partitioning are:

1) Efficiency 2) Local Optimization 3) Security 4) Recovery and Uptime 5) Load Balancing

Factors to consider when choosing a file organization are:

1) Fast data retrieval 2) security 3) efficient storage

Disadvantages of partitioning are:

1) Inconsistent Access Speed 2) Complexity 3) Extra Space and Update Time

main goals of normalization:

1) Minimize data redundancy, thereby avoiding anomalies and conserving storage space. 2) Simplify the enforcement of referential integrity constraints. 3) Make it easier to maintain data (insert, update, and delete). 4) Provide a better design that is an improved representation of the real world and a stronger basis for future growth

Requirements to begin designing physical files and databases is:

1) Normalized Data 2) Definitions of each attribute 3) Technology Descriptions

Objectives in selecting a data type:

1) Represent all possible values. 2) Improve data integTity. 3) Support all data manipulations. 4) Minimize storage space.

A candidate key must satisfy all of the following conditions:

1) Unique identification: For every row, the value of the key must uniquely identify that row. 2) Non-redundancy: No attribute in the key can be deleted without destroying the property of unique identification.

A method for handling missing data is to:

1) substitute and estimate for the missing data. 2) track missing data with special reports. 3) perform sensitivity testing.

All of the following are common denormalization opportunities:

1) two entities with a 1:1 relationship 2) a M:M relationship with non-key attributes 3) Reference data

Which of the following are properties of relations?

1. Each relation (or table) in a database has a unique name. 2. An entry at the intersection of each row and column is atomic (or single valued). There can be only one value associated with each attribute on a specific row of a table; no multivalued attributes are allowed in a relation. 3. Each row is unique; no two rows in a relation can be identical. 4. Each attribute (or column) within a table has a unique name. 5. The sequence of columns (left to right) is insignificant. The order of the. columns in a relation can be changed without changing the meaning or use of the relation. 6. The sequence of rows (top to bottom) is insignificant. As with columns, the. order of the rows of a relation may be changed or stored in any sequence.

________ 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

The relational data model consists of which components?

Data structure Data integrity Data manipulation

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

Default Value

A file organization is a named portion of primary memory. (T/F)

False

Indexes are most useful on small, clustered files. (T/F)

False

When transforming a weak entity, one should create one relation with both the attributes of the strong entity and the attributes of the weak entity. (T/F)

False

A relation is in first normal form if it has no more than one multivalued attribute. (T/F)

False (1ST NF = no repeating groups and a PK has been established)

An anomaly is a type of flaw in the database server. (T/F)

False (Anomaly: An error or inconsistency that may result when a user attempts to update a table that contains redundant data)

The entity integrity rule states that a primary key attribute can be null. (T/F)

False (a PK always needs a value, it's the f*cking PK)

In the relational data model, associations between tables are defined through the use of primary keys. (T/F)

False (associations between tables is a Foreign Key)

When transforming a unary many-to-many relationship to relations, a recursive foreign key is used. (T/F)

False (both take their values from the primary key)

Reduced uptime is a disadvantage of partitioning. (T/F)

False (it's an advantage)

When transforming a one-to-one relationship, a new relation is always created. (T/F)

False (it's optional)

Denormalization is the process of transforming relations with variable-length fields into those with fixed-length fields (T/F)

False (it's the process of transforming normalized relations into non-normalized physical record specifications)

Unlike columns, the rows of a relation may not be interchanged and must be stored in one sequence. (T/F)

False (row is the data going across, like an individual Employee's info. so the order they're in doesn't matter either)

A key is a data structure used to determine the location of rows in a file that satisfy some condition. (T/F)

False (that's an index)

A transversal dependency is a functional dependency between two or more non-key attributes. (T/F)

False (transversal isn't a thing)

Anomalies do not generally arise out of transitive dependencies. (T/F)

False (you have to remove transitive dependencies to make it 3rd NF, therefore they must exist in there in the first place)

A foreign key is a primary key of a relation that also is a primary key in another relation (T/F)

False (not the PK of both)

When all multi-valued attributes have been removed from a relation, it is said to be in:

First Normal Form

A form of database specification that indicates all the parameters for data storage that are then input to database implementation is:

Physical Database

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

Second Normal Form

A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. (T/F)

True

A cascading delete removes all records in other tables associated with the record to be deleted. (T/F)

True

A partial functional dependency is a functional dependency in which one or more non-key attributes are functionally dependent on part (but not all) of the primary key. (T/F)

True

A range control limits the set of permissible values that a field may assume. (T/F)

True

A referential integrity constraint is a rule that maintains consistency among the rows of two relations. (T/F)

True

A well-structured relation contains minimal redundancy and allows users to manipulate the relation without errors or inconsistencies. (T/F)

True

All values that appear in a column of a relation must be taken from the same domain. (T/F)

True

Clustering allows for adjacent secondary memory locations to contain rows from several tables. (T/F)

True

If an identifier is not assigned, the default primary key for an associative relation consists of the two primary key attributes from the other two relations. (T/F)

True

Keeping the zip code with the city and state in a table is a typical form of denormalization. (T/F)

True

Security is one advantage of partitioning. (T/F)

True

The allowable range of values for a given attribute is part of the domain constraint. (T/F)

True

The columns of a relation can be interchanged without changing the meaning or use of the relation. (T/F)

True

When normalizing, the goal is to decompose relations with anomalies to produce smaller, well-structured relations. (T/F)

True

In the figure below, each employee has exactly one manager. (T/F)

True EMPLOYEE --|o------o<--(back to itself)

All of the following are valid datatypes in Oracle 11G:

VARCHAR2 CHAR CLOB NUMBER DATE BLOB

Sensitivity testing involves:

checking to see if missing data will greatly impact results

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

determinant

Horizontal partitioning makes sense when:

different categories of rows of a table are processed separately

The ________ states that no primary key attribute may be null.

entity integrity rule

The smallest unit of application data recognized by system software is a:

field

Distributing the rows of data into separate files is called:

horizontal positioning

The entity integrity rule states that:

no primary key attribute can be null

While Oracle has responsibility for managing data inside a table-space, the tablespace as a whole is managed by the:

operating system

A(n) ________ is a field of data used to locate a related field or record.

pointer

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.

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

representing organizational data

Database access frequencies are estimated from:

transaction volumes

A functional dependency between two or more non-key attributes is called a:

transitive dependency

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