Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

C175 Data Management Foundations > Lesson 5 > Flashcards

Lesson 5 Flashcards

1
Q

atomic attribute

A

An attribute that cannot be further subdivided to produce meaningful components.

For example, a person’s last name attribute cannot be meaningfully subdivided.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

atomicity

Atomicity is like saying “If I can’t do ALL those steps, I won’t do ANY.”

A
  1. The transaction property that requires all parts of a transaction to be treated as a single, indivisible, logical- unit of work.
  2. All parts of a transaction must be completed or the entire transaction is aborted.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Boyce-Codd normal form (BCNF)

A
  1. A special type of third normal form (3NF) in which every determinant is a candidate key.
  2. A table in BCNF must be in 3NF.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

determinant

A

Any attribute in a specific row whose value directly determines other values in that row.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

denormalization

A

A process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed.

Denormalization potentially yields data anomalies. Learn more

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

dependency diagram

A

A representation of all data dependencies (primary key, partial, or transitive) within a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

first normal form (1NF)

A
  1. The first stage in the normalization process.
  2. It describes a relation depicted in tabular format, with no repeating groups and a primary key identified.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

fourth normal form (4NF)

Note

A

If you have a scenario where:

  1. An attribute can have multiple values associated with another attribute
  2. That second attribute is also independent of other attributes
  3. Consider normalizing the data by splitting it into two tables.

This will help reduce redundancy, improve data integrity, and simplify future data operations.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

granularity

A
  1. The level of detail represented by the values stored in a table’s row.
  2. Data stored at its lowest level of granularity is said to be atomic data.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

nonprime attribute

A

An attribute that is not part of a key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

normalization

A

A process that assigns attributes to entities so that data redundancies are reduced or eliminated.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

partial dependency

A

A condition in which an attribute is dependent on only a portion (subset) of the composite key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

repeating group

A

In a relation, a characteristic describing a group of multiple entries of the same type for a single key attribute occurrence.

For example, a car can have multiple colors for its top, interior, bottom, trim, and so on

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

second normal form (2NF)

A

The second stage in the normalization process, in which:

  1. A relation is in 1NF
  2. There are no partial dependencies

(dependencies in only part of the primary key).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

third normal form (3NF)

A

A table is in 3NF also:

  1. When it is in 2NF
  2. Lacks a nonkey attribute that is functionally dependent on another nonkey attribute

cannot include transitive dependencies.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

transitive dependency

A

A condition in which an attribute is dependent on another attribute that is not part of the primary key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Some very specialized applications may require normalization beyond the _____.

A

4NF

Note

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What must be eliminated for each row to define a single entity?

A

Repeating groups must be eliminated to ensure that each row represents a single entity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is the purpose of normalization in database design?

A

Normalization is used to organize data so that attributes are correctly assigned to entities, thereby reducing or eliminating data redundancies.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

A table that has all of the following is said to be in _____?

  1. All key attributes defined
  2. No repeating groups
  3. All its attributes are dependent on the primary key
A

1NF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

If a table in 1NF has a single-attribute primary key, what can be said about its compliance with 2NF?

A

A table with a single-attribute primary key in 1NF is automatically in 2NF because there cannot be any partial dependencies.

Note

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

repeating group

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

When designing a database, you should _____.

A

make sure that the table entities are normalized before table structures are created

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

partial dependency

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What condition must a table satisfy to be classified as in Boyce-Codd Normal Form (BCNF)?

A

A table is in BCNF if every determinant in the table is a candidate key.

Note

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

If there are problems with the way data is organized in a database, how can that cause trouble for managers?

A

Fixing these problems usually requires changing the way the database itself is structured, rather than just making changes to the software that uses the database

These problems (reported anomalies) can create lots of headaches for managers.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What is true about a table in 2NF regarding transitive dependencies?

A
  • 2NF eliminate some transitive dependencies that result from partial dependencies on a candidate key.
  • 3NF is required to remove all transitive dependencies.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

An atomic attribute _____ .

A

cannot be further subdivided

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Granularity refers to _____.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Explain how Attribute A determines Attribute B?

A

Attribute.A determines attribute.B if all of the rows in the table that agree in value for attribute.A also agree in value for attribute.B

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

All relational tables satisfy the ______ requirements.

A

1NF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

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

A

derived

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What is the objective of normalization in database design?

A

The objective of normalization is to ensure that each table conforms to the concept of well-formed relations, reducing redundancies and improving data integrity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

1NF, 2NF, and 3NF are _____.

A

normalization stages

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

A table that is in 1NF and includes no partial dependencies is said to be in _____.

A

2NF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

In the ______, no row may contain two or more multivalued facts about an entity.

A

4NF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Is normalization always a desirable aspect of database design?

A

Normalization is important, but higher levels may not always be necessary for every database, depending on specific business requirements

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

________ is a process that is used for changing attributes to entities.

A

Normalization

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

What is the distinction between a partial dependency and a transitive dependency in database design?

A
  1. A partial dependency occurs when a nonprime attribute is dependent on only a part of a composite primary key.
  2. A transitive dependency arises when a nonprime attribute is dependent on another nonprime attribute, indirectly relating to the primary key transitive explained
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q
  1. In database design, how do we categorize functional dependencies that rely on only a portion of a composite primary key?
  2. Why is it incorrect to label these as transitive dependencies?
A

These dependencies are classified as partial dependencies, not transitive dependencies.

  • Partial Dependency: Occurs when a non-prime attribute (an attribute not part of the primary key) is functionally dependent on only part of the composite primary key.
  • Transitive Dependency: Occurs when a non-prime attribute is functionally dependent on another non-prime attribute.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

Data redundancy produces _____ .

A

data anomalies

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Dependencies can be identified with the help of a dependency ______.

A

diagram

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

Before converting a table into 3NF, it is imperative that the table already be in _____.

A

2NF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

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

A

1NF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

What Anomaly’s does normalization aim to avoid?

A

Normalization is a process of organizing the data in the database to avoid insertion anomaly, data redundancy, update and deletion anomaly.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

If a table has multiple candidate keys, but one is a composite key. What should be known?

A
  • Even if a single-attribute primary key is chosen, the composite candidate key still enforces a unique constraint on the table.
  • This means no two rows can have the same combination of values for the attributes in the composite key
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

Of the following normal forms, _____ is mostly of theoretical interest.

A

DKNF

Domain-key normal form Note

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

For most business transactional databases, we should normalize relations into _____.

A

3NF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

Do all relational tables satisfy the requirements of First Normal Form (1NF)?

A

Yes, all relational tables meet the requirements of First Normal Form (1NF), which prohibits repeating groups and mandates a primary key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

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.

A

Three

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

What do the arrows in a dependency diagram signify?

A

The arrows in a dependency diagram indicate desirable functional dependencies among the attributes of a table.

52
Q

How do you know that a transitive dependency exist?

A

A(n) transitive dependency exists when there are functional dependencies such that: XYZ

  1. Y is functionally dependent on X
  2. Z is functionally dependent on Y, &
  3. X is the primary key.
53
Q

A partial dependency can exist only when?

A
  • A partial dependency can exist only when a table’s primary key is composed of several attributes.
  • A table whose primary key consists of only a single attribute is automatically in 2NF once it is in 1NF.
54
Q

The problem with transitive dependencies is that they still yield data _______, inconsistencies, and irregularities.

A

anomalies

55
Q

A table that displays data redundancies yields _____.

A

anomalies

56
Q

What leads to data redundancies in the context of partial dependencies

A

When a primary key consists of multiple attributes, and a non-key attribute depends on only part of that key (partial dependency).

  1. You need to repeat the same values for that non-key attribute across multiple rows.
  2. Each row needs to include the full primary key, even if the non-key attribute only depends on a portion of it.
57
Q

What is the process that works through a series of stages called normal forms in database design

reduces redundancy and improves data integrity.

A
  • Normalization is the process that works through a series of stages known as normal forms
  • Aims to avoid insertion anomaly, data redundancy, update and deletion anomaly.
58
Q

When a table contains only one candidate key, _____ are considered to be equivalent.

A

the 3NF and the BCNF

59
Q

How do dependency diagrams assist in understanding a table’s structure

A

Dependency diagrams provide a visual overview of all the relationships and dependencies among a table’s attributes, aiding in database design and normalization

60
Q

Most designers consider the BCNF as a special case of the _____.

A

3NF

61
Q

What describes a dependency based on only a part of a composite primary key?

A

A dependency based on only a part of a composite primary key is known as a partial dependency.

62
Q

What defines a determinant in a relational database context?

A

A determinant is any attribute in a table whose value directly determines other attribute values in the same row, not merely within a column.

63
Q

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.

A

repeating group

64
Q

An attribute that cannot be further subdivided is said to be ______.

A

atomic

65
Q

When can the BCNF only be violated?

A

BCNF can be violated only if the table contains more than one candidate key.

66
Q

What is the result of data redundancy in a database?

A

Data redundancy produces data anomalies, which can lead to inconsistencies and errors in the database

67
Q

__________ specifies that a non-prime attribute is functionally dependent on part of a candidate key.

A

partial dependency

68
Q

When designing a new database structure based on the business requirements of the end users, the database designer will construct a data model using a technique such as _____ ______ _______ ______.

A

Crow’s Foot notation ERDs

69
Q

________ refers to the level of detail represented by the values stored in a table’s row.

A

Granularity

70
Q

How does improving atomicity affect queries in a database

A
  1. Improving atomicity leads to more flexible queries
  2. It ensures that data is stored at its most detailed level.

allows for more precise information retrieval

71
Q

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

A

3NF

72
Q

An attribute that is part of a key is known as a(n) _____ attribute.

A

prime

73
Q

Any attribute whose value determines other values within a row is known as a _______

A

determinant

74
Q

What is a key requirement of relational models regarding data representation?

A

Relational models require that key values must be identified as part of a table or collection of tables in order to maintain relational integrity

75
Q

Any attribute that is at least part of a key is known as a

A

key attribute

76
Q

________ is a process to help reduce the likelihood of data anomalies.

A

normalization

77
Q

What characterizes a transitive dependency in a database?

A

A transitive dependency is characterized by an attribute being functionally dependent on another non-key attribute, rather than directly on the primary key.

78
Q

What type of dependencies are classified as based on part of a composite primary key?

A

Dependencies based on only a part of a composite primary key are known as partial dependencies.

79
Q

Does normalization aim to produce a lower normal form in databases?

A

No, normalization works to progress through higher normal forms to eliminate redundancy and anomalies, not to produce lower normal forms.

80
Q

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

A

2NF

81
Q

What is a key attribute?

A

An attribute (or a set of attributes) used to uniquely identify a record in a database table

Example: A student ID in a table of students.

82
Q

Define composite key.

A

A type of primary key that consists of two or more attributes that together uniquely identify a record in a table

Example: The combination of student ID and course ID in a course enrollment table.

83
Q

What is a prime key?

A

Another term for primary key, which uniquely identifies a record in a table

Must contain unique values and cannot contain null values.

84
Q

What is partial dependency?

A

Occurs when a non-key attribute is functionally dependent on part of a composite key but not the whole key

Example: If a composite key consists of student ID and course ID, and a non-key attribute like course title depends only on course ID.

85
Q

Why can’t a composite key omit one of its attributes?

A

All attributes in a composite key are necessary for unique identification.

86
Q

A _______ is an attribute or a set of attributes that uniquely identifies a record in a table.

A

primary key

aka. key attribute

87
Q

List the key concepts related to database normalization.

A
  • Key Attribute
  • Composite Key
  • Prime Key
  • Partial Dependency
88
Q

What does partial dependency lead to in a database?

A

Redundancy

It is a concept addressed in the normalization process.

89
Q

What is a table scan?

A

A database operation that reads all table blocks directly without accessing an index.

90
Q

What is an index scan?

A

An operation that reads index blocks sequentially to locate the needed table blocks.

91
Q

What is a binary search?

A

A method that repeatedly splits the index in two until the entry containing the search value is found.

92
Q

What is a single-level index?

A

A file containing column values along with pointers to the rows that contain those values.

93
Q

What is a multi-level index?

A

An index that stores column values and row pointers in a hierarchy.

94
Q

What is a primary index?

A

An index on a sorted column that determines the order of rows in the table.

95
Q

What is a secondary index?

A

An index that is not based on the sort order of the table.

96
Q

Fill in the blank: A _______ reads all table blocks directly without accessing an index.

A

table scan

97
Q

Fill in the blank: An _______ reads index blocks sequentially to locate the needed table blocks.

A

index scan

98
Q

True or False: A binary search is an inefficient method for searching in sorted indexes.

A

False

99
Q

True or False: A multi-level index allows faster access to data by minimizing the number of reads required to find a target entry.

A

True

100
Q

What is a database design?

A

A specification of database objects such as tables, columns, data types, and indexes

It also refers to the process used to develop the specification.

101
Q

What is the role of the storage manager in a database system?

A

Uses indexes to quickly locate data.

102
Q

What is a catalog in the context of databases?

A

A directory of tables, columns, indexes, and other database objects.

103
Q

What does the physical design phase of database design involve?

A

Adds indexes and specifies how tables are organized on storage media.

104
Q

Define a single-level index.

A

A file containing column values, along with pointers to rows containing the column value.

105
Q

What is a multi-column index?

A

An index where each entry is a composite of values from all indexed columns.

106
Q

What is a table scan?

A

A database operation that reads table blocks directly, without accessing an index.

107
Q

What is an index scan?

A

A database operation that reads index blocks sequentially to locate the needed table blocks.

108
Q

What is a binary search in the context of databases?

A

A method that repeatedly splits the index in two until it finds the entry containing the search value.

109
Q

What is a primary index?

A

An index on a sort column, also called a clustering index.

110
Q

What is a secondary index?

A

An index that is not on the sort column, also called a nonclustering index.

111
Q

What is a dense index?

A

An index that contains an entry for every table row.

112
Q

What is a sparse index?

A

An index that contains an entry for every table block.

113
Q

What does a multi-level index do?

A

Stores column values and row pointers in a hierarchy.

114
Q

Define fan-out in the context of multi-level indexes.

A

The number of index entries per block.

115
Q

What is a B+tree in database indexing?

A

A tree structure where all indexed values appear in the bottom level with pointers to table blocks only in the bottom level.

116
Q

What is a hash index?

A

An index where entries are assigned to buckets based on a hash function.

117
Q

What is a bucket in a hash index?

A

A block or group of blocks containing index entries.

118
Q

What is a bitmap index?

A

A grid of bits representing index entries.

119
Q

What is a physical index?

A

An index that normally contains pointers to table blocks.

120
Q

What is a logical index?

A

An index where pointers to table blocks are replaced with primary key values.

121
Q

What is a function index?

A

An index where entries contain the result of a function applied to column values.

122
Q

What does physical design specify?

A

Indexes, table structures, and partitions.

123
Q

What is the role of the storage engine in a database?

A

Translates instructions generated by a query processor into low-level commands that access data on storage media.

124
Q

What does the CREATE INDEX statement do?

A

Creates an index by specifying the index name and table columns that compose the index.

125
Q

What does the DROP INDEX statement do?

A

Deletes a table’s index.

126
Q

What does the SHOW INDEX statement do?

A

Displays a table’s index.

127
Q

What is a global catalog?

A

A directory of participating database objects, such as tables, columns, and indexes.

C175 Data Management Foundations (29 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions