Set 3-1

Question 1

Used to store petabytes of user data in cloud

Answer 1

Magnetic Disk

Question 2

Reading one gigabyte takes about one second

Answer 2

Flash Memory

Question 3

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

Answer 3

Main Memory

Question 4

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

Answer 4

Either 250 or 2000 sectors

Question 5

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?

Answer 5

One

Question 6

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

Answer 6

84

Question 7

4-megabyte photographs are usually stored ____.

Answer 7

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

Question 8

What are some examples of DBs that support column oriented storage?

Answer 8

PostgreSQL and Vertica

Question 9

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

Answer 9

3250 blocks

Question 10

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?

Answer 10

8264 blocks

Question 11

The term ‘column-oriented’ ____.

Answer 11

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

Question 12

A DB supports which four table structures

Answer 12

Heap, Sorted, Hash, Table Cluster

Question 13

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?

Answer 13

50%

Question 14

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?

Answer 14

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

Question 15

Sorted tables are always sorted on a single column. T/F

Answer 15

False

Question 16

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

Answer 16

40

Question 17

Assume blocks are 8 kilobytes, rows are 200 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?

Answer 17

520

Question 18

Assume blocks are 8 kilobytes, rows are 200 bytes, and the hash function is modulo 13. 1,000,000 rows are inserted. In a worst-case scenario, what is the maximum number of blocks chained together in a single bucket?

Answer 18

25,000

Question 19

What happens when a new row is inserted into a full bucket?

Answer 19

A new block is linked to the initial block, and the new row is stored in the new block.

Question 20

The cluster key is normally the primary key of both tables in a cluster. T/F

Answer 20

False

Question 21

Table clusters are the most commonly used physical structure for tables. T/F

Answer 21

False

Question 22

Cluster keys in a table cluster are similar to sort columns in a sorted table. T/F

Answer 22

True

Question 23

Table clusters always contain exactly two tables. T/F

Answer 23

False

Question 24

How many tables can be stored in one tablespace?

Answer 24

At least zero, at most many

Question 25

The Airport table has two thousand rows. The Airline table has ten thousand rows. Both tables are updated infrequently. Should Airport and Airline be stored on the same Tablespace

Answer 25

Sometimes

Question 26

The Booking table has ten million rows and is updated frequently. The Airline table has ten thousand rows and is updated infrequently. Should Booking and Airline be stored in the same tablespace?

Answer 26

Never

Question 27

How many partitions can one table have?

Answer 27

At least zero, at most many

Question 28

The Airport table has two thousand rows and is updated infrequently. Should Airport be partitioned?

Answer 28

Never

Question 29

The Reservation table has one hundred million rows and is updated frequently. Most queries access recent bookings. Should Reservation be partitioned?

Answer 29

Yes, Reservation should be partitioned on the ReservationDate column