Brainscape's Knowledge GenomeTM

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


See full index

C175 Data Management Foundations > Chapter 5 Data Storage > Flashcards

Chapter 5 Data Storage Flashcards

1
Q

What is transfer rate?

A

Speed of data reading or writing.

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

What is cost in terms of storage media?

A

Price per gigabyte of storage media.

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

What does capacity refer to?

A

Amount of data a media can store.

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

What is volatility?

A

Data retention when power is lost.

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

What is volatile memory?

Think vo•lost

A

Memory lost when power is disconnected.

Note

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

What is non-volatile memory?

A

Memory retained without power.

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

What are sectors?

A

Data groups on magnetic disks, traditionally 512 bytes.

Also hard-disk drive (HDD)

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

What are pages in flash memory?

Flash aka RAM

A

Data groups in flash memory, 2-16 kilobytes.

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

What are blocks in databases?

A

Uniform size for data transfer in databases.

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

What is a storage controller?

A

The storage controller needs to be able to convert between blocks and the appropriate unit of storage for the type of storage device being used.

Usually between blocks and sectors.

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

What is block size?

A

Uniform size specified by database administrator.

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

What are transactional applications?

A

Access few rows per query, prefer smaller blocks.

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

What is PostgreSQL?

Note

A

Relational database supporting column-oriented storage.

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

What is Vertica?

A

Relational database optimized for analytic applications.

📖

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

What is the standard block size in storage systems?

A

4 Kilobytes.

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

What is bulk load?

A

Fast insertion of multiple rows into a table.

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

What is an SQL query?

A

Command to extract or manipulate data.

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

What is a primary key?

A

Unique identifier for each row in a table.

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

What is a NoSQL database?

A

Non-relational database for unstructured data.

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

What is row assignment?

A

Process of placing data in specific buckets.

Learn more

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

What is a free space pointer?

A

Reference to available storage in a table.

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

What is a free space linked list?

Note

A
  • The primary purpose of a free space linked list is to manage free memory blocks efficiently.
  • When memory is allocated to an application, this data structure helps keep track of what portions of memory are available for future allocations.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is an insert operation?

A

Adding data to the first available space.

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

What is an update operation?

A

Modifying existing row data in a table.

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

What is a JOIN query?

A

Combines rows from two or more tables based on conditions.

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

What is a modulo function?

A

Simple hash function using division remainder.

📖 Hash Fx?

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

What are efficient inserts?

A

Inserts that quickly determines row location

via hash key.

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

Who is a database administrator?

A

Person managing database structures and performance.

Aka root account

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

What is data distribution?

A

Method of organizing data across storage.

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

What is block allocation?

A

Process of assigning additional storage blocks.

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

What is a cluster key?

A

A column shared by all interleaved tables.

Interleaved

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

What is table structure?

A

Arrangement of data in database tables.

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

What is a dense index?

A

Index with entries for every table row.

📖

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

What is a sparse index?

A

Index with entries for every table block.

📖

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

What is an index entry?

Data Address: Data value

A

Pointer to a specific data location.

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

What is block read time?

A

Time taken to read a block from disk.

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

What is query performance?

A

Efficiency of retrieving data from tables.

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

What is a bitmap index?

A

Uses bits to represent data presence in rows.

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

What is read query performance?

A

Logical index that requires additional read.

Slower than physical.

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

What is primary index memory?

A

Often retained in memory to speed access.

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

What is a hash table?

A

Data structure using hash functions for indexing.

Indexing: assigns rows to buckets

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

What is index storage?

A

Indexes stored in the same tablespace as tables.

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

What are concurrent updates?

A

Simultaneous modifications to multiple tables.

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

What is fragmentation?

A

Scattering of data blocks across storage.

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

What is fast storage media?

A

Fast storage media refers to storage hardware that can be accessed quickly, improving performance for frequently used data.

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

What is the size of sectors used in magnetic memory?

A

512 bytes

Magnetic memory typically uses 512-byte sectors for data storage.

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

How many sectors are required to store one megabyte in magnetic memory?

A

Approximately 2000 sectors

One megabyte requires 1,000,000 bytes, which when divided by 512 bytes/sector results in about 2000 sectors. 📖

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

What is the size of sectors used in newer storage systems?

A
  • Older standard: 512 bytes per sector
  • Newer standard: 4096 bytes (4KB) per sector
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

How many sectors are required to store one megabyte in newer systems with 4KB sectors?

A

250 sectors

📖

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

What is the minimum block size that must be transferred into memory from flash memory?

A

8 kilobytes

A minimum of one eight-kilobyte block must be transferred into memory, despite reading four kilobytes from flash memory.

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

What is the page size of flash memory?

A

2 kilobytes

Flash memory page size is typically two kilobytes.

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

If a user runs a query that reads two pages of flash memory, how many blocks are transferred to main memory?

A
  1. One block
  2. Even though two pages (4 kilobytes total) are read, only one block of eight kilobytes is transferred.

📖

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

What is transferred to the database in blocks.

A

Data transfers to the database occurs in blocks, not individual bytes.

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

What type of applications are most relational databases optimized for?

A

Transactional applications.

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

What is row-oriented storage?

A

A storage method where an entire row is stored within one block.

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

When does row-oriented storage perform best?

A

When row size is small relative to block size.

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

What are the two reasons row-oriented storage performs best with small row sizes?

A
  • Improved query performance
  • Less wasted storage
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

What happens to storage when row size is small relative to block size?

A

Wasted space is insignificant.

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

What is a common approach for tables containing very large columns?

A

Each row contains a link to the large column stored separately.

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

What types of applications are some newer relational databases optimized for?

A

Analytic applications.

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

What is column-oriented storage?

A

A storage method where each block stores values for a single column only.

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

What are the benefits of column-oriented storage for analytic applications?

A
  • Faster data access
  • Better data compression

Also use the same data type

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

Why is data compression more effective in column-oriented storage?

A

Because all values have the same data type.

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

What is a disadvantage of column-oriented storage for transactional applications?

column-oriented storage is often associated with NoSQL databases

A
  1. Column-oriented storage is bad for transactions needing full row access.
  2. It requires accessing multiple blocks to retrieve a single row.
  3. This is because data is stored by column, not by row.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

Which two relational databases support column-oriented storage.

A
  • PostgreSQL
  • Vertica
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
66
Q

Why are NoSQL databases not optimized for transactional applications.

A
  1. NoSQL databases are built for managing large amounts of unstructured data, emphasizing high scalability and availability.
  2. Transactional applications, on the other hand, require strong consistency and ACID properties, which are not typically prioritized in NoSQL databases.

(atomicity, consistency, isolation, durability)

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

The term column-oriented & columnar represent _ ?

A
  1. Mean a technique for organizing data on storage media.
  2. Sometimes these terms mean a type of NoSQL database, commonly called wide column database.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

What type of storage performs better than column-oriented storage for most transactional databases?

A

Row-oriented storage

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

What are the four alternative table structures supported by databases?

A
  • Heap table
  • Sorted table
  • Hash table
  • Table cluster
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
70
Q

What is a heap table?

A

A table where no order is imposed on rows

📖

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

How does a database manage inserts in a heap table?

A

Maintains a list of blocks and the address of the first available space for inserts

📖

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

What happens when all blocks in a heap table are full?

A

The database allocates a new block for inserts

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

What occurs when a row is deleted from a heap table?

A

The space occupied by the row is marked as free

📖

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

How is free space typically tracked in heap tables?

A

As a linked list

📖

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

Heap tables optimize which type of operations?

A

Insert operations

📖

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
76
Q
  1. If each table in a database can have a different structure, and databases assign a default structure to all tables.
  2. How can the default structure of a table in a database be modified?
A

Database administrators can override the default structure to optimize performance for specific queries.

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

What is a heap table?

A

A heap table is a table structure with no specific order for rows, where rows are stored in the order they are inserted.

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

How does a database manage insertions in a heap table?

A
  1. The database maintains a list of blocks and the address of the first available space for inserts.
  2. If blocks are full, a new block is allocated.
79
Q

What happens to space when a row is deleted in a heap table?

A

The available free space defined as a space from the deleted row; is recorded and managed using a separate linked list data structure.

80
Q

What are the performance characteristics of heap tables?

A

Heap tables optimize insert operations and are particularly fast for bulk loads, but are not optimal for queries that require rows in a specific order.

81
Q

What is a sorted table?

A

A sorted table organizes rows based on a designated sort column, typically the primary key.

82
Q

How are rows managed in a sorted table?

A
  1. Rows are assigned to blocks according to the value of the sort column.
  2. The databases maintain pointers to efficiently manage order during inserts.
83
Q

What are the advantages of using sorted tables?

A

Sorted tables are optimal for read queries that utilize the sort column, such as JOINs and SELECT with ranges.

84
Q

How does a hash table assign rows?

A

A hash table assigns rows to buckets based on a hash key, usually the primary key, using a hash function.

85
Q

Describe the storage mechanism of a hash table.

A

Rows are distributed across buckets using a hash function, where each bucket contains a block or group of blocks.

86
Q

What are the performance benefits of hash tables?

A

Hash tables are optimal for inserting and deleting individual rows quickly, but are inefficient for range-based queries.

87
Q

What is a table cluster?

A

A table cluster interleaves rows from related tables based on a cluster key, typically involving a primary key and a corresponding foreign key.

📖

88
Q

How does a table cluster improve query performance?

A

Table clusters optimize queries that join on the cluster key by ensuring that related rows are stored physically close together.

📖

89
Q

What are the limitations of table clusters?

A

Table clusters are not optimal for queries that join on non-cluster keys or for reading multiple rows from a single table.

📖

90
Q

What are the different types of table structures in databases?

A
  • Heap Table
  • Sorted Table
  • Hash Table
  • Table Cluster
91
Q

A sorted table is best for _______ queries.

A

read-heavy

92
Q

Which table structure are useless for queries that require specific row ordering.

A
  1. When you query a heap table, the database system may return rows in any order it finds them.
  2. This order might be based on the physical storage location or the order in which rows were inserted, but it’s not predictable or reliable.
93
Q

What happens when a sorted table’s blocks fill up?

A

When a block is full, it splits to create space for new inserts.

94
Q

What is a heap table?

A

A type of database table structure that allows for dynamic storage of data with free space management.

95
Q

What happens when the first row is inserted into a heap table?

A

The first inserted row is placed at the location pointed to by the free space pointer, which is then reset to point to free space B.

Order 📖

96
Q

After the second insert in a heap table, where does the free space pointer point?

A

The free space pointer points to free space C.

Order 📖

97
Q

What happens to the free space pointer after the second insert?

A

It is reset to the space at the end of the block. (Right side)

🤔

98
Q

What is the purpose of the free space pointer in a heap table?

A

To track available space for new row inserts.

99
Q

List the order of free spaces used during the insertions in the heap table.

A
  • Free space A
  • Free space B
  • Free space C
  • New block
100
Q

What is the significance of allocating a new block in a heap table?

A

It allows for continued storage of data when the current block becomes full.

101
Q

What occurs after the fourth insert in a heap table?

A

The fourth insert goes to the beginning of the new block.

102
Q

What is the modulo function 4 steps?

📖

A
  1. Convert the hash key by interpreting the key’s bits as an integer value.
  2. Divide the integer by the number of buckets.
  3. Interpret the division remainder as the bucket number.
  4. Convert the bucket number to the physical address of the block containing the row.
103
Q

What is a table structure in a database?

A

A scheme for organizing rows in blocks on storage media.

104
Q

What are the four alternative table structures supported by databases?

A
  • Heap table
  • Sorted table
  • Hash table
  • Table cluster
105
Q

What is the default behavior of databases regarding table structures?

A

Databases assign a default structure to all tables.

106
Q

Can database administrators modify the default table structure?

A

Yes, to optimize performance for specific queries.

107
Q

Describe the organization of rows in a heap table.

A

No order is imposed on rows; a list of blocks and first available space for inserts is maintained.

108
Q

How do heap tables optimize insert operations?

A

They are particularly fast for bulk load of many rows since rows are stored in load order.

109
Q

Are heap tables optimal for reading rows in a specific order?

A

No, they are not optimal for such queries as rows are scattered randomly.

110
Q

What is the purpose of a hash function in a hash table?

A

To compute the bucket, containing the row from the hash key.

📖

111
Q

What does the modulo function do in hash tables?

A

It determines the bucket number from the hash key.

112
Q

What are deep buckets in hash tables?

A

Buckets that contain long chains of linked blocks due to fixed hash function allocation.

113
Q

What is the advantage of using dynamic hash functions?

A

They automatically allocate more blocks and distribute rows across all buckets.

📖

114
Q

When are hash tables optimal?

A
  • Inserts and deletes of individual rows
  • Selecting a single row with specified hash key value
115
Q

Why are hash tables slow for certain queries?

A

They are slow on queries that select many rows with a range of values.

116
Q

What determines the physical row order in a sorted table?

A

A sort column identified by the database designer.

117
Q

What is the typical use of the sort column in sorted tables?

A

Usually the primary key, but can be a non-key column or group of columns.

118
Q

What is a potential downside of maintaining sorted tables?

A

Maintaining correct sort order can be slow during inserts and updates.

119
Q

What happens when a block in a sorted table is full?

A

The block splits in two, moving half the rows to a new block.

120
Q

What are table clusters also known as?

A

Multi-tables.

121
Q

How are rows stored in table clusters?

A

Rows of two or more tables are interleaved in the same storage area based on a cluster key.

122
Q

What is the function of the cluster key in table clusters?

A

It determines the order in which rows are interleaved.

123
Q

Why are table clusters optimal for certain queries?

A

They are optimal when joining interleaved tables on the cluster key.

124
Q

What are the performance issues with table clusters for other queries?

A
  • Join on columns other than cluster key
  • Read multiple rows of a single table
  • Update cluster key
125
Q

What is the steps for Calculating Storage Metrics in Database Management

A
126
Q

What is the steps for Calculating Storage Metrics in Database Management

A
127
Q

Fill in the blanks

A
128
Q

What is the formula for a table scan?

A

Number of rows / rows per table block

129
Q

How do you calculate a single-level index scan?

A

Number of index blocks plus referenced table blocks

130
Q

What is the formula for a single-level index binary search?

A

log base 2 (number of index blocks) plus referenced table blocks

131
Q

How is a multi-level dense index search calculated?

A

log base fan-out (number of rows), rounded up, plus referenced table blocks

132
Q

What is the formula for a multi-level sparse index search?

A

log base fan-out (number of rows / rows per table block), rounded up, plus referenced table block

133
Q

Can a hash table have a primary index?

A

No, hash tables cannot have a primary index

Rows of a hash table are not stored in sort order.

134
Q

What type of indexes can a hash table have?

A

Secondary indexes

Secondary indexes can be any structure, including hash.

135
Q

How is a primary index usually structured?

A

As a sparse multi-level index

In principle, a primary index can be structured as a hash index.

136
Q

What determines the location of a row containing a hash key value?

A

Applying a hash function to a hash key

Hash keys do not store table block pointers.

137
Q

Are hash keys implemented as single- or multi-level indexes?

A

No, hash keys are not implemented as single- or multi-level indexes.

138
Q

Can a hash index be sparse?

A

Yes, a hash index can be sparse.

139
Q

What does each column value appear as in a hash index?

A

An index entry in a bucket.

140
Q

Are hash indexes ever sparse?

A

No, hash indexes are never sparse.

141
Q

Fill in the blank: A hash table can have a _______ index.

A

hash index.

142
Q
  1. What are the steps to locate rows containing a table value for the following index?
  2. Hash Index
A
  1. Apply the hash function to the column value to compute a bucket number
  2. Read the index blocks for the bucket number.
  3. Find the index entry for the column value and read the table block pointer.
  4. Read the table block containing the row.
143
Q
  1. What are the steps to locate rows containing a table value for the following index?
  2. Bitmap Index
A
  1. Determine the index column coresponding to the table value.
  2. Read the index column and find index rows that are set to ‘one’.
  3. Determine table rows corresponding to the index rows.
  4. Determine pointers to blocks containing the table rows.
144
Q
  1. What are the steps to locate rows containing a table value for the following index?
  2. Logical Index
A
  1. Look up the column value in the logical index to find the primary key value.
  2. Look up the primary key value in the primary index to find the table block pointer.
  3. Read the table block containing the row.
145
Q
  1. What are the steps to locate rows containing a table value for the following index?
  2. Function Index
A
  1. Specify a function on the column value to transform stored values.
  2. Use the transformed values in the index to process queries instead of the original column values.
146
Q

What is a partition in the context of database tables?

A

A partition is a subset of table data

147
Q

How does a range partition assign rows to partitions?

A

Associates each partition with a range of partition expression values using VALUES LESS THAN keywords

148
Q

What keyword represents the highest column value in a range partition?

A

MAXVALUE

149
Q

What does VALUES LESS THAN MAXVALUE specify in a range partition?

A

The highest range

150
Q

How are partitions named in a range partition?

A

Each partition is explicitly named by the database administrator

151
Q

What is a list partition?

A

Associates each partition with an explicit list of partition expression values using the VALUES IN keywords

152
Q

What type of partition requires a partition expression with positive integer values?

A

Hash partition

153
Q

How is the partition number computed in a hash partition?

A

(partition expression value) modulo N

154
Q

What is a key partition in MySQL?

A

Similar to a hash partition, but the partition expression is determined automatically by the database

155
Q

What is the default behavior for tablespaces in most databases?

A

Automatically create one tablespace for each table

156
Q

What is one benefit of storing frequently accessed tables in separate tablespaces?

A

Improves query performance

157
Q

What happens to the associated file when a table is dropped?

A

The associated file is deleted and storage is released

158
Q

What is the impact of fragmentation on query performance?

A

Queries that scan tables on heavily fragmented files are slow

159
Q

What is the advantage of storing one table per file?

A

Minimizes fragmentation and optimizes table scans

160
Q

How do partitions improve query performance?

A

By reducing the amount of data accessed by INSERT, UPDATE, DELETE, and SELECT statements

161
Q

What is the difference between horizontal and vertical partitioning?

A

Horizontal partitioning is a subset of table rows; vertical partitioning is a subset of table columns

162
Q

Which type of partitioning do MySQL and most relational databases use?

A

Horizontal partitioning

163
Q

What happens to table indexes when a table is partitioned?

A

Table indexes are also partitioned

164
Q

Fill in the blank: Each partition contains index entries only for rows in the _______.

A

partition

165
Q

What are the restrictions on partitioned tables in MySQL?

A

A partitioned table may not contain foreign keys and foreign keys may not refer to a partitioned table

This restriction limits the usability of partitions in MySQL.

166
Q

What must all partition columns do in MySQL?

A

All partition columns must appear in all unique columns, including the primary key, of the partitioned table

This requirement can complicate table design and limits flexibility.

167
Q

Why do partitions lack value in MySQL?

A

Due to the restrictions and requirements, partitions have limited value in MySQL.

168
Q

Which storage engines support partitioning in MySQL?

A

InnoDB and NDB

MyISAM does not provide native partitioning support.

169
Q

What does the PARTITIONS table in the INFORMATION_SCHEMA database provide?

A

Insights into the partitioned tables and their structures

This table helps users understand how their partitioning is set up.

170
Q

True or False: MyISAM supports native partitioning in MySQL.

A

False

MyISAM does not provide native partitioning support.

171
Q

What is the purpose of documenting known restrictions and limitations in MySQL 8.0 regarding partitioning?

A

User awareness

This helps users understand the constraints they may encounter.

172
Q

Fill in the blank: Partitioning is supported only by _______ and NDB storage engines.

A

InnoDB

173
Q
A
174
Q

What does logical design specify?

A

Tables, columns, and keys

Logical design is an essential part of database schema design.

175
Q

What does physical design specify?

A

Indexes, table structures, and partitions

Physical design focuses on how data is stored and accessed.

176
Q

How does physical design affect query performance?

A

It affects query performance but never affects query results

This distinction is crucial for database design.

177
Q

What is the role of a storage engine?

A

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

Storage engines manage how data is stored and retrieved.

178
Q

What is the default storage engine installed with MySQL?

A

InnoDB

InnoDB is widely used for its transaction support and referential integrity features.

179
Q

What are the key features of InnoDB?

A
  • Full support for transaction management
  • Foreign keys
  • Referential integrity
  • Locking

These features make InnoDB suitable for many applications requiring data integrity.

180
Q

What are the transaction management capabilities of MyISAM?

A

Limited transaction management and locking capabilities

MyISAM is often used in scenarios with fewer data updates.

181
Q

What type of applications is MyISAM commonly used for?

A

Analytic applications with limited data updates

MyISAM’s design is better suited for read-heavy workloads.

182
Q

What does the MEMORY storage engine do?

A

Stores all data in main memory

MEMORY is used for fast access with databases small enough to fit in main memory.

183
Q

Which table structures does Oracle Database support?

A
  • Heap
  • Sorted
  • Hash
  • Cluster

Oracle’s flexibility in table structures is beneficial for various applications.

184
Q

What table structures does MySQL with InnoDB support?

A
  • Heap
  • Sorted

InnoDB’s support is limited compared to other databases.

185
Q

What index types does MySQL with InnoDB or MyISAM support?

A

B+tree indexes

B+tree indexes are commonly used for efficient data retrieval.

186
Q

What index types does MySQL with MEMORY support?

A
  • B+tree
  • Hash

The MEMORY engine’s support for hash indexes allows for faster lookups in certain scenarios.

187
Q

What does the column ‘select_type’ indicate in the EXPLAIN statement output?

A

The type of the query being executed, such as SIMPLE, PRIMARY, or SUBQUERY.

  • SIMPLE: A straightforward query without nesting or unions.
  • PRIMARY: The main or outer SELECT in a nested query.
  • SUBQUERY: An inner SELECT inside a nested query.
188
Q

What information does the ‘table’ column provide in the EXPLAIN output?

A

The name of the table being described in that row of the EXPLAIN result.

This helps identify which table’s data is being analyzed in the query.

189
Q

What does the ‘type’ column describe in the EXPLAIN statement?

A

The join type being used in the query, such as const, range, eq_ref, or ALL.

  • const: The table has a row that matches at most one condition.
  • range: The query uses a constant to filter rows.
  • eq_ref: One row from this table is selected for each matching row of another table.
  • ALL: Every row from the table is scanned.
190
Q

What does ‘possible_keys’ represent in the EXPLAIN output?

A

Indexes that could potentially be used to speed up the query.

191
Q

What does the ‘key’ column indicate in the output of the EXPLAIN statement?

A

The specific index that the database has decided to use for the query.

If it’s NULL, no index was used and a full table scan was performed.

192
Q

What information does the ‘ref’ column provide in the EXPLAIN output?

A

Constants or expressions compared with the selected index.

193
Q

What does the ‘rows’ column estimate in the context of an EXPLAIN query?

A

How many rows will be read from the table to execute the query.

194
Q

What does the ‘filtered’ column estimate in the EXPLAIN output?

A

The number of rows that qualify based on the query conditions.

C175 Data Management Foundations (30 decks)

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