ch3

separate from other columns, managed either within the database or by the file system

4-megabyte photographs are usually stored ____.

Heap table initially, then restructure to sorted table after bulk load is complete

A database designer creates a new Employee table with primary key 'EmployeeNumber', loads 1 million rows into the table, then runs many queries that join other tables on the EmployeeNumber column. What is the best structure for Employee?

84

A database uses 16-kilobyte blocks. A table contains 18,200 rows of 100 bytes each. Ignoring space used by the system for overhead, how many bytes are unused on each block? Assume one kilobyte = 1,024 bytes.

has an entry for each tuple is on a secondary key

A dense index is one that

False

A hash index can be sparse.

18 → 20, 20 → 21

A row with key 20 is inserted at the end of the table. Which of the following pointers are added?

True

A table has a multi-level index with no free space in any index blocks. If the index is dense, an insert to the table always generates a new index level.

True

A table has a multi-level index with no free space in any index blocks. If the index is sparse, an insert to the table generates a new index level when the insert caused a table block split.

Free space A

First insert

Either 250 or 2,000

Approximately how many sectors are required to store one megabyte of data?

False

Are the blocks selected the correct ones? "Lufthansa"

50%

Assume 100 rows fit in each block of a sorted table. If a block is full and an insert causes the block to split, roughly what percentage of the new and old blocks is empty?

80

Assume blocks are 8 kilobytes, rows are 100 bytes, and the hash function is modulo 13. If each bucket initially has one block, how many rows fit into a single bucket?

1040

Assume blocks are 8 kilobytes, rows are 100 bytes, and the hash function is modulo 13. In a best-case scenario, what is the maximum number of row inserts before some bucket overflows?

840

Assume blocks are 8,400 bytes and rows are 70 bytes. If only 1 block is used per bucket, how many rows in total can be added to 7 buckets?

3,250 blocks

Assume column-oriented storage. Roughly how many blocks are required to compute average income by postal code for all people?

8,264 blocks

Assume row-oriented storage, with each row containing Name, Income, and PostalCode only. Roughly how many blocks are required to compute average income by postal code for all people?

17

Assume you are using the UNION ALL operator to combine the results from two identical tables, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the UNION ALL operator?

New block

Fourth insert

At least zero, at most many

How many partitions can one table have?

False

In a sparse multi-level index, each table block pointer appears at least once.

Flash memory

Reading one gigabyte takes about one second.

Name State (Name, State)

Refer to Table B. Which of the following (if any) are valid sort columns?

Block 0 and 1

Refer to the figure above. Which table blocks does the following query read? SELECT FlightNumber, AirlineName FROM Flight WHERE DepartureTime > "12:00" AND DepartureTime < "16:00";

First insert - Free space B Second insert - free space A Third insert - Free space A Fourth insert - new block

Row 589 is deleted and becomes free space B. Then, 4 new rows are inserted into the table. Where are the 4 inserts stored?

Free space B

Second insert

cluster

Some DBMSs allow an optional ___________ specification for only one index for each table. With this option, the system will store records in physical order by the values of the indexed field.

F

Sorted tables are always sorted on a single column.

One

The database administrator specifies an eight-kilobyte block size. Flash memory page size is two kilobytes. A user runs a query that reads two pages of flash memory. How many blocks are transferred to main memory?

nested loops

The default method of doing joins is

means either a technique for organizing data on storage media or a type of NoSQL database

The term 'column-oriented' ____.

Free space C

Third insert

Main memory

Upgrading from 16 to 32 gigabytes costs an extra $400 for an Apple laptop computer.

Magnetic disk

Used to store petabytes (millions of gigabytes) of user data in the cloud.