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.
2
Q
atomicity
Atomicity is like saying “If I can’t do ALL those steps, I won’t do ANY.”
A
- The transaction property that requires all parts of a transaction to be treated as a single, indivisible, logical- unit of work.
- All parts of a transaction must be completed or the entire transaction is aborted.
3
Q
Boyce-Codd normal form (BCNF)
A
- A special type of third normal form (3NF) in which every determinant is a candidate key.
- A table in BCNF must be in 3NF.
4
Q
determinant
A
Any attribute in a specific row whose value directly determines other values in that row.
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
6
Q
dependency diagram
A
A representation of all data dependencies (primary key, partial, or transitive) within a table.
7
Q
first normal form (1NF)
A
- The first stage in the normalization process.
- It describes a relation depicted in tabular format, with no repeating groups and a primary key identified.
8
Q
fourth normal form (4NF)
A
If you have a scenario where:
- An attribute can have multiple values associated with another attribute
- That second attribute is also independent of other attributes
- Consider normalizing the data by splitting it into two tables.
This will help reduce redundancy, improve data integrity, and simplify future data operations.
9
Q
granularity
A
- The level of detail represented by the values stored in a table’s row.
- Data stored at its lowest level of granularity is said to be atomic data.
10
Q
nonprime attribute
A
An attribute that is not part of a key.
11
Q
normalization
A
A process that assigns attributes to entities so that data redundancies are reduced or eliminated.
12
Q
partial dependency
A
A condition in which an attribute is dependent on only a portion (subset) of the composite key.
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
14
Q
second normal form (2NF)
A
The second stage in the normalization process, in which:
- A relation is in 1NF
- There are no partial dependencies
(dependencies in only part of the primary key).
15
Q
third normal form (3NF)
A
A table is in 3NF also:
- When it is in 2NF
- It must not contain transitive dependencies: A transitive dependency occurs when a non-key attribute depends on another non-key attribute, rather than directly on the primary key.
16
Q
transitive dependency
A
A condition in which an attribute is dependent on another attribute that is not part of the primary key.
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.
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.
20
Q
A table that has all of the following is said to be in _____?
- All key attributes defined
- No repeating groups
- All its attributes are dependent on the primary key
A
1NF
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.
22
Q
A relational table must not contain a(n) _____.
A
repeating group
23
Q
When designing a database, you should _____.
A
make sure that the table entities are normalized before table structures are created
24
Q
A dependency based on only a part of a composite primary key is called a _______
A
partial dependency
25
What condition must a table satisfy to be classified as in Boyce-Codd Normal Form (BCNF)?
A table is in BCNF *if every determinant in the table is a candidate key*.
## Footnote
[Note](https://share.evernote.com/note/57907a8b-18c9-3c9f-70f3-ca20ece4ca9a)
26
If there are problems with *the way data is organized* in a database, how can that cause trouble for managers?
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
## Footnote
These problems (reported anomalies) can create lots of headaches for managers.
27
What is true about a table in 2NF regarding transitive dependencies?
* 2NF eliminate _some_ transitive dependencies that *result from partial dependencies* on a candidate key.
* 3NF *is required to remove* all transitive dependencies.
28
An atomic attribute _____ .
cannot be further subdivided
29
Granularity refers to _____.
the level of detail represented *by the values* in a table's row
30
Explain how Attribute A determines Attribute B?
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
31
All relational tables satisfy the ______ requirements.
1NF
32
From a **system functionality** point of view, _____ attribute values can be calculated when they are *needed to write reports or invoices*.
derived
33
What is the objective of normalization in database design?
The objective of normalization is to ensure that **each table** conforms to the concept of *well-formed relations, reducing redundancies and improving data integrity*.
34
1NF, 2NF, and 3NF are _____.
normalization stages
35
A table that is in 1NF and includes *no partial dependencies* is said to be in _____.
2NF
36
In the ______, no row may contain *two or more multivalued facts* about an entity.
4NF
37
Is normalization always a desirable aspect of database design?
Normalization is important, but higher levels may not always be necessary for every database, *depending on specific business requirements*
38
________ is a process that is used for changing *attributes to entities*.
Normalization
39
What is the distinction between a partial dependency and a transitive dependency in database design?
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](https://g.co/gemini/share/f958ab477127)
40
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?
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.
41
Data redundancy produces _____ .
data anomalies
42
Dependencies can be identified with the help of a dependency ______.
diagram
43
Before converting a table into 3NF, it is imperative that the table already be in _____.
2NF
44
From a structural point of view, 2NF is better than _____.
1NF
45
What Anomaly's does normalization aim to avoid?
Normalization is a process of organizing the data in the database to avoid *insertion anomaly, data redundancy, update and deletion anomaly*.
46
If a table has multiple *candidate* keys, but one is a *composite key*. What should be known?
- 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
47
Of the following normal forms, _____ is mostly of *theoretical interest*.
DKNF
## Footnote
Domain-key normal form [Note](https://share.evernote.com/note/c2292849-9f63-5ffa-e0fc-de0203c9eb84)
48
For most business transactional databases, we should normalize relations into _____.
3NF
49
Do all relational tables satisfy the requirements of First Normal Form (1NF)?
Yes, all relational tables meet the requirements of First Normal Form (1NF), which *prohibits repeating groups and mandates a primary key*.
50
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.
Three
51
What do the arrows in a dependency diagram signify?
The arrows in a dependency diagram indicate **desirable functional dependencies** among the attributes of a table.
52
How do you know that a transitive dependency exist?
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
A partial dependency can exist only when?
- 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
The problem with transitive dependencies is that they still yield data _______, inconsistencies, and irregularities.
anomalies
55
A table that displays data redundancies yields _____.
anomalies
56
What leads to data redundancies in the context of partial dependencies
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
What is the process that works through a series of stages called normal forms in database design
## Footnote
reduces redundancy and improves data integrity.
- 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
When a table contains only one candidate key, _____ are considered to be equivalent.
the 3NF and the BCNF
59
How do dependency diagrams assist in understanding a table's structure
Dependency diagrams provide a visual overview of *all the relationships and dependencies among a table's attributes*, aiding in database design and normalization
60
Most designers consider the BCNF as *a special case* of the _____.
3NF
61
What describes a dependency based on only a part of a composite primary key?
A dependency based on only a part of a composite primary key is known as a partial dependency.
62
What defines a determinant in a relational database context?
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
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.*
repeating group
64
An attribute that cannot be further subdivided is said to be ______.
atomic
65
When can the BCNF only be violated?
BCNF can be violated only if *the table contains* more than one candidate key.
66
What is the result of data redundancy in a database?
Data redundancy produces **data anomalies**, which can lead to inconsistencies and errors in the database
67
__________ specifies that a non-prime attribute is functionally dependent on part of a candidate key.
partial dependency
68
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 _____ ______ _______ ______.
Crow's Foot notation ERDs
69
________ refers to the level of detail represented by the values stored in a table's row.
Granularity
70
How does improving atomicity affect queries in a database
1. Improving atomicity leads to more flexible queries
2. It ensures that data is stored at its most detailed level.
## Footnote
allows for more precise information retrieval
71
A table that is in 2NF and contains *no transitive dependencies* is said to be in _____.
3NF
72
An attribute that is part of a key is known as a(n) _____ attribute.
prime
73
Any attribute whose value determines other values within a row is known as a _______
determinant
74
What is a key requirement of *relational models* regarding **data representation**?
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
_Any_ attribute that is *at least part of a key* is known as a
key attribute
76
________ is a process to help reduce the likelihood of data anomalies.
normalization
77
What characterizes a transitive dependency in a database?
A transitive dependency is characterized by an attribute being *functionally dependent on **another** non-key attribute*, rather than directly on the primary key.
78
What type of **dependencies** are _classified_ as *based on part of* a composite primary key?
Dependencies based on *only a part of a* composite primary key *are known as* partial dependencies.
79
Does normalization aim to produce a lower normal form in databases?
No, normalization *works to progress through higher normal forms to eliminate redundancy and anomalies*, not to produce lower normal forms.
80
From a structural point of view, 3NF is better than _____.
2NF
81
What is a key attribute?
An attribute (or a set of attributes) used to uniquely identify a record in a database table
## Footnote
Example: A student ID in a table of students.
82
Define composite key.
A type of primary key that consists of *two or more attributes* that together uniquely identify a record in a table
## Footnote
Example: The combination of student ID and course ID in a course enrollment table.
83
What is a prime key?
Another term for **primary key**, which uniquely identifies a record in a table
## Footnote
Must contain unique values and cannot contain null values.
84
What is partial dependency?
Occurs when a _non-key_ attribute is functionally dependent *on part of a composite key* but not the whole key
## Footnote
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
Why can't a composite key *omit one of its* attributes?
All attributes in a composite key are necessary for unique identification.
86
A _______ is an attribute or a set of attributes that uniquely identifies a record in a table.
primary key
## Footnote
aka. key attribute
87
List the key concepts related to database normalization.
* Key Attribute
* Composite Key
* Prime Key
* Partial Dependency
## Footnote
[Why 📖](https://g.co/gemini/share/710a2ec07c03)
88
What does partial dependency lead to in a database?
Redundancy
## Footnote
It is a concept _addressed_ in the normalization process.
89
What is a table scan?
A database operation that reads all table blocks directly _without_ accessing an index.
## Footnote
[Index V Table](https://g.co/gemini/share/9904bac1bd63)
90
What is an index scan?
An operation that reads index blocks *sequentially to locate* the needed table blocks.
## Footnote
[Table V index](https://g.co/gemini/share/9904bac1bd63)
91
What is a binary search?
A method that repeatedly *splits the index in two* until the entry containing the search value is found.
92
What is a single-level index?
A file containing *column values along with pointers*; to the rows that contain *those values*.
93
What is a multi-level index?
An index that _stores_ *column values and row pointers* in a hierarchy.
## Footnote
[📖](https://share.evernote.com/note/9e9a365e-43e2-90e4-ea59-017e3978960d)
94
What is a primary index?
An index that uses a *primary key column* as the requirement to uniquely identify rows in a table.
95
What is a secondary index?
An index that is not based on the *sort order* of the table.
96
Fill in the blank: A _______ reads all table blocks directly without accessing an index.
table scan
97
Fill in the blank: An _______ reads index blocks sequentially to locate the needed table blocks.
index scan
98
Why makesa binary search is an efficient method for searching indexes.
Binary search is an efficient method for searching through sorted indexes. This method works by repeatedly dividing the array in half and eliminating the half where the target value cannot be.
99
True or False: A multi-level index allows faster access to data by minimizing the number of reads required to find a target entry.
True
100
What is a database design?
A *specification of database objects* such as tables, columns, data types, and indexes
## Footnote
It also refers to the process used to develop the specification.
101
What is the role of the storage manager in a database system?
## Footnote
AKA storage engine
- component of a database system that handles the *physical storage and retrieval of data* on disk
- including managing indexes for efficient access
102
What is a catalog in the context of databases?
A directory of tables, columns, indexes, and other database objects.
103
What does the *physical design phase* of database design involve?
1. Adding indexes
2. Specifying how tables are organized on storage media
104
Define a single-level index.
1. A single-level index is a file containing a *sorted list* or *B-tree of column values*, along with pointers to the rows in a data file that contain those values.
2. This data structure is used to speed up data retrieval by allowing the database to quickly locate rows that match a specific value without having to scan the entire data file.
105
What is a multi-column index?
An index where **each entry is a composite of values** from all indexed columns.
106
What is a table scan?
A database operation that reads table blocks directly, without accessing an index.
107
What is an index scan?
A database operation that reads index blocks *sequentially to locate* the needed table blocks.
108
What is a binary search in the context of databases?
A method that repeatedly splits the index in two until it finds the entry containing the search value.
109
What is a primary index?
The term "primary index" can refer to a clustering index, it is *specifically linked to the primary key that uniquely identifies* each record in a table.
110
What is a secondary index?
## Footnote
[Note](https://share.evernote.com/note/fb8a3b26-3853-38ed-43de-2bace4a8d026)
- Created on **non-primary key columns**. While a secondary index can include the primary key, it doesn't always have to.
- It does not affect the physical order of data but provides *an efficient way to search for records based on non-primary columns*.
111
What is a dense index?
## Footnote
[Note](https://share.evernote.com/note/fb8a3b26-3853-38ed-43de-2bace4a8d026)
1. For every *search key value* in the data file, there is an **index record**.
2. This record contains the search key and also a *reference to the first data record with that search key value*.
112
What is a sparse index?
## Footnote
[Note](https://share.evernote.com/note/fb8a3b26-3853-38ed-43de-2bace4a8d026)
A sparse index **contains fewer entries** than there are rows in the table and *generally points to blocks* rather than every individual entry.
113
What does a multi-level index do?
## Footnote
[Note](https://share.evernote.com/note/fb8a3b26-3853-38ed-43de-2bace4a8d026)
Stores *column values and row pointers* in a hierarchy.
114
Define **fan-out** in the context of *multi-level indexes*.
The number of *index entries per block*.
115
What is a B+tree in database indexing?
A tree structure where *all indexed values appear* in the bottom level; with *pointers to table blocks* only in the bottom level.
116
What is a hash index?
An index where entries are assigned to buckets based on a hash function.
117
What is a bucket in a hash index?
A block or group of blocks containing index entries.
118
What is a bitmap index?
A grid of bits representing index entries.
119
What is a physical index?
An index that normally contains pointers to table blocks.
120
What is a logical index?
An index where pointers to table blocks are replaced with primary key values.
121
What is a function index?
An index where entries contain the result of a function applied to column values.
122
What does physical design specify?
Indexes, table structures, and partitions.
123
What is the role of the storage engine in a database?
Translates instructions *generated by a query processor* into low-level commands that access data on storage media.
124
What does the CREATE INDEX statement do?
Creates an index by specifying *the index name and table columns* that compose the index.
125
What does the DROP INDEX statement do?
Deletes a table's index.
126
What does the `SHOW INDEX` statement do?
Displays a table's index.
127
What is a global catalog?
A directory of *participating database objects*, such as tables, columns, and indexes.
