Week 19

 CST-363: Week 3

This is the third week of CST-363, also known as Intro. to Databases.

Questions

What is an SQL view.  How is it similar to a table? In what ways is it different?

• An SQL View stores a SELECT statement for later use, allowing the statement to be easily accessed in the future. A View functions similarly to a table, because SELECT statements return tables. However, the values that a View shows aren't being stored in the View, but the actual tables. This means that when one of those tables is updated, the View has to be as well. This also means that one needs to be very careful with updating or deleting rows using the View.

Take a minute to think about how SQL compares to other programming languages like Java. What features are similar, and which are present in one language but not the other?

• The first and most clear difference is the syntax. SQL syntax is very different from that of Java and other programming languages. I think this makes basic SQL commands easy to learn, although complex SQL statements can be harder to interpret if one is used to other programming languages. Since SQL is specialized for database access, it has many features for database access, and makes database access relatively simple. However, SQL does not have access to nearly as many features as other programming languages, such as Java.

Textbook Vocabulary

Storage Media

Speed: 

• Access Time: The time required to access the first byte in a read or write operation.
• Transfer Rate: The speed at which data is read or written, following initial access.
  

Cost: Typically ranges from pennies to dollars per gigabyte of memory, depending on the media type.

Volatile Memory: Memory that is lost when disconnected from power.

Non-Volatile Memory: Memory that is retained without power.

Three types of media important for database management:

• Main Memory:  Also called random-access memory (RAM), is the primary memory used when computer programs execute. Main memory is fast, expensive, and has limited capacity.
• Flash Memory: Also called solid-state drive (SSD), is less expensive and higher capacity than main memory. Writes to flash memory are much slower than reads, and both are much slower than main memory writes and reads.
• Magnetic Disk: Also called hard-disk drive (HDD), is used to store large amounts of data. Magnetic disk is slower, less expensive, and higher capacity than flash memory.

Table Structures

Table Structure: A scheme for organizing rows in blocks on storage media.

Heap Table: No order is imposed on rows.

Sorted Table: The database designer identifies a sort column that determines physical row order.

Hash Table: Rows are assigned to buckets.

• Bucket: A block or group of blocks containing rows.
• Hash Key: A column or group of columns, usually the primary key.
• Hash Function: Computes the bucket containing the row from the hash key.

Table Clusters: Also called multi-tables, interleave rows of two or more tables in the same storage area.

• Cluster Key: A column that is available in all interleaved tables. 

Single-Level Indexes

Single-Level Index: A file containing column values, along with pointers to rows containing the column value.

Multi-Column Index: Each index entry is a composite of values from all indexed columns.

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

Index Scan: A database operation that reads index blocks sequentially, in order to locate the needed table blocks.

Hit Ratio: Also called filter factor or selectivity, is the percentage of table rows selected by a query.

Primary Index: Also called a clustering index, is an index on a sort column.

Secondary Index:  Also called a non-clustering index, is an index that is not on the sort column.

Dense Index: Contains an entry for every table row.

Sparse Index: Contains an entry for every table block.

Multi-Level Indexes

Multi-Level Index: Stores column values and row pointers in a hierarchy.

Branch: Each path from the top-level block to a bottom-level block.

Balanced: When all branches of a multi-level index are the same length.

Imbalanced: When all branches of a multi-level index are different lengths.

B-Tree: If an indexed value appears in a higher level, the value is not repeated at lower levels. Instead, a pointer to the corresponding table block appears in the higher level along with the value.

B+Tree: All indexed values appear in the bottom level. Pointers to table blocks appear only in the bottom level.

Other Indexes

Hash Index: In a hash index, index entries are assigned to buckets.

Bitmap Index: Is a grid of bits.

• Each index row corresponds to a unique table row.
• Each index column corresponds to a distinct table value.

Physical Index: A single- or multi-level index that contains pointers to table blocks.

Logical Index: A single- or multi-level index in which pointers to table blocks are replaced with primary key values.

Tablespaces and Partitions

Tablespace: A database object that maps one or more tables to a single file.

Partition: A subset of table data.

Horizontal Partition: A subset of table rows.

Vertical Partition: A subset of table columns.

Range Partition: Associates each partition with a range of partition expression values.

List Partition: Associates each partition with an explicit list of partition expression values using the VALUES IN keywords.

Hash Partition: Requires a partition expression with positive integer values.

Key Partition: Similar to a hash partition, except the partition expression is determined automatically by the database.

Physical Design

Logical Design: Specifies tables, columns, and keys.

Physical Design: Specifies indexes, table structures, and partitions.

Storage Engine/Storage Manager: Translates instructions generated by a query processor into low-level commands that access data on storage media.

EXPLAIN: Generates a result table that describes how a statement is executed by the storage engine.