D426 Final Exam Study Guide 2024

D426 Final Exam Study Guide 2024

Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form. Denormalization means intentionally introducing redundancy by merging tables Databasescommonlysupportfouralternativetablestructures:  Heap table In a heap table, no order is imposed on rows. o Heaptablesoptimizeinsertoperations.Heaptablesareparticularlyfastforbulk loadof manyrows,sincerowsarestoredinloadorder  Sortedtable In a sorted table, the database designer identi昀椀es a sort column that determines physical row order.  Hash table In a hash table,rows are assigned to buckets. A bucketis a block or group of blocks containing rows. The modulo function is a simple hash function with four steps:  Tablecluster Table clusters, also called multi-tables, interleave rows of two or more tables in the same storage area.  A table scan is a database operation that reads table blocks directly, without accessing anindex.  An index scan is a database operation that reads index blocks sequentially, in order to locatetheneededtableblocks. Hit ratio, also called 昀椀lter factor or selectivity, is the percentage of table rows selected by a query.WhenaSELECTqueryisexecuted In a binary search, the database repeatedly splits the index in two until it 昀椀nds the entry containingthesearchvalue: Indexesmayalsobedenseorsparse:  A dense index contains an entry for every table row.  A sparse index contains an entry for every table block. Hashindex  Bitmapindex  Logicalindex  Functionindex In a hash index, index entries are assigned to buckets. A bitmap index is a grid of bits: Bitmapindexescontainonesandzeros. A tablespace is a database object that maps one or more tables to a single