Concepts of Database Design Exam 2

The order of the rows and columns is important to the DBMS.

False

A(n) _ index is an index in which the index key can have only one pointer value (row) associated with it.

unique

A(n) table is the implementation of a composite entity used to implement an M:N relationship.

linking

Consider a bookstore table with COD, TITLE, CATEGORY, PAGES, PRICE. Which is a more sensitive selection index?

TITLE, CATEGORY

Only a single attribute, not multiple attributes, can define functional dependence.

False

A data dictionary is sometimes described as .

"the database designer's database"

The operator subtracts one table from the other.

DIFFERENCE

A _ contains all of the attribute names and characteristics for each table in the system.

data dictionary

In a car wash business table with services, which attribute should be a foreign key?

CLIENT_ID

A video game store table with users - best data type for being an adult?

Logical

Character data, also known as ___ data, can contain any character or symbol not intended for mathematical manipulation.

string

In a school, what relationship exists between TEACHER and CLASS when each class has one teacher and a teacher can only be class teacher of one class?

1:1

_, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition.

SELECT

In database context, using the same attribute name to label different attributes indicates a .

homonym

By using the relational data model the designer focuses primarily on the physical storage details.

False

If one shop manager can only be in charge of a single shop, what kind of relationship do the entities shop manager and shop have?

1:1

How many rows and columns does the product of table1 and table2 have?

6 rows, 5 columns

On a student table, what data type is more appropriate for the gpa attribute?

Numeric

Which option is more adequate for a primary key in a table of employees?

employee social security number

What could be an unintended effect of deleting an entry in a vendor table referenced by a products table?

It damages the referential integrity.

The _operator uses one single-column table and one two-column table.

DIVIDE

When you define a table's primary key, the DBMS automatically creates a(n) _ index on the primary key columns you declared.

unique

A primary key can't be null, but it can contain null attributes.

False

In a table of clients of a store, the best description of the attribute domain for how much a client has spent is

[0, ∞)

An index key can have multiple _ (a composite index).

attributes

M:N relationship can be changed into two 1:M relationships using a __.

composite entity

What kind of relationship is "representative sold car" in a car dealership?

1:N

In a large university student table, the name attribute could be used as a _.

secondary key

What kind of relationship exists between university COURSE and STUDENT tables?

M:N

_, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition.

SELECT

What is the function of pointers in an index?

Shows the location of the occurrences of a particular index value.

Why is a foreign key not considered data redundancy?

The elimination of the foreign key would result in losing the information of the relation.

_ returns only the attributes requested, in the order in which they are requested.

PROJECT

Relations are a construct, thus it's easier for users to think in terms of tables.

mathematical

The _ relationship is the "relational model ideal."

1:M

Entity integrity is the condition in which each row in the table has its own unique identity with requirements: all values in the primary key must be unique and no key attribute in the primary key can contain a null.

If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A).

True

What is the result of π price (table)?

price: 19, 8, 22

A(n) _ is an orderly arrangement used to logically access rows in a table.

index

The row's range of permissible values is known as its domain.

False

Which pair of attributes could be a primary key for a table with unique combinations of attribute 1 and attribute 2?

attribute 1, attribute 2

INTERSECT yields only the rows that appear in both tables with identical values.

An index is an ordered arrangement of keys and pointers used to logically access rows in a table, with each key pointing to data locations. Indexes help retrieve data efficiently, order data by attributes, and implement primary keys.

The _ is actually a system-created database whose tables store the user/designer-created database characteristics and contents.

system catalog

Which attribute could be a primary key in a table where attribute 3 has unique values for each row?

attribute 3

A table is a three-dimensional structure composed of depth, width, and height.

False

The idea of determination is unique to the database environment.

False

To avoid potential confusion with attribute names when creating a new table, which strategy is best?

Query the data dictionary to get the necessary information.

Which relational operation gives names from table1 with at least one entry in table2 with total > 100?

π name (table1 ⋈ σ total>100 (table2))

Which key would cause BCNF violation if used more than once?

candidate

A dependency diagram shows relationships among attributes with primary key attributes highlighted and arrows indicating desirable dependencies (based on primary key) above attributes and less desirable dependencies (partial and transitive) below.

In order to meet performance requirements, portions of the database design may need to be occasionally denormalized.

True

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

dependency

All relational tables satisfy 1NF requirements.

True

Boyce-Codd normal form (BCNF) requires every determinant in the table to be a candidate key. BCNF is equivalent to 3NF when there is only one candidate key and is considered a special case of 3NF.

When designing data structure, attribute names should use descriptive suffixes.

use descriptive suffixes

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

True

The objective of normalization is to ensure that each table conforms to the concept of well-formed relations.

True

An atomic attribute cannot be further subdivided.

cannot be further subdivided

When the related table uses a composite primary key, it becomes difficult to create which key?

foreign

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

repeating group

A transitive dependency exists when Y is functionally dependent on X, Z is functionally dependent on Y, and X is the primary key.

transitive dependency

Which trait should be balanced with design integrity?

flexibility

A table in 1NF has all key attributes defined, no repeating groups, and all attributes dependent on the primary key.

1NF

Normalization works through a series of stages called normal forms, with _ stages being sufficient for most business database design.

three

What is the minimum preferred normal form all entities should be in when designing a new database?

3NF

Well-formed relations have characteristics: each table represents a single subject, data is not unnecessarily stored in multiple tables (reducing redundancy), all non-prime attributes depend only on the primary key, and tables are free of data anomalies.

Which table type defines a table in 2NF that contains no transitive dependencies?

3NF

How does an unnormalized data structure with entities having relations with many instances affect data management?

Multiple rows need to be updated.

Denormalization produces a lower normal form.

True

When a table contains only one candidate key, which normal forms are equivalent?

the 3NF and the BCNF

In order to yield a useful ERD, you must combine ER modeling with which entity?

normalization

Normalization works through a series of stages called normal forms.

True

If a table is in 3NF and is also in Boyce-Codd normal form, what functional dependencies are determinants?

candidate keys

Which dependency illustrates how one key determines multiple values of two other independent attributes?

multivalued dependency

Which attribute values can be calculated when needed for reports or invoices?

derived

Granularity refers to _.

the level of detail represented by the values in a table's row

According to the data-modeling checklist, what process ensures an ER model minimizes redundancy for single-place updates?

normalization

A dependency based on only part of a composite primary key is called .

partial

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

1NF

What unwanted element does normalization reduce?

data redundancies

An attribute that cannot be further subdivided displays which quality?

atomicity

What type of data is stored at the lowest level of granularity?

atomic

Attribute A _ attribute B if all rows that agree in value for A also agree in value for B.

determines

Which term represents a micro view of the entities within the ERD?

Normalization

A table where all attributes depend on the primary key but are independent of each other, with no row containing multiple multivalued facts about an entity is in _.

4NF

Which normal form contains no rows with two or more multivalued facts about an entity?

4NF

Raw data in its original state with anomalies such as redundant or multivalued data is _ data.

unnormalized

According to the data-modeling checklist, _ should be nouns familiar to business, short and meaningful.

entity names