Database Final

Define primary key field

a primary key is the ordering key field of the file. A file that is uniquely identifies a record

How does a B-tree differ from a B+-tree?

-a B-Tree has data pointers in the both internal and leaf nodes -in B+-Tree, it has only tree pointers in internal nodes and all data pointers are in leaf nodes

primary index

-primary index is specifies on the ordering key fields of an ordered file of a records -number of index entries that blocks in data file -every record has unique value for that field -primary index is non-dense

secondary index

-secondary index specifies on any non-ordering field. it has a unique value -number of index entries that records in data file -a file have several secondary indexes and in addition to its primary access method -secondary index is dense

clustering index

-the file with numerous records and have the same value of the ordering filed -number of index entries that distinct index field values -it has at most one physical ordering field. so , it can have at most one primary index or one clustering index, but not both -clustering index is non-dense

How does multilevel indexing improve the efficiency of searching an index file?

-while searching the record, it reduces the access of number of blocks in the given indexing field value -the benefits of multi-level indexing include the reduction of insertion and deletion problems in indexing -while inserting new entries, it leaves some space that deals to the advantage to developers to adopt the multi-level indexing -by using B-trees and B+ trees, it is often implemented

Define clustering field

a secondary index is also an ordered field with two fields. (like a primary index). The first field is of the same data type as some non-ordering field of the data file that is an indexing field. If the secondary access structure uses a key field, which has a distinct value for every record. Therefore, it is called as secondary key field

atomicity property of a database transaction

a transaction is an atomic unit of processing; it soul either be performed in its entirety or not performed at all

Define the violation caused by non repeatable read

A transaction T1 may read a given value from a table. If another transaction T2 later updates that value and T1 reads that value again, T1 will see a different value

Define the violation caused by phantoms

A transaction T1 may read a set of rows from a table, perhaps based on some condition specified in the SQL WHERE-clause. Now suppose that a transaction T2 inserts a new row r that also satisfies the WHERE-clause condition used in T1, into the table used by T1. The record r is called a phantom record because it was not there when T1 starts but is there when T1 ends. T1 may or may not see the phantom, a row that previously did not exist. If the equivalent serial order is T1 followed by T2, then the record r should not be seen; but if it is T2 followed by T1,then the phantom record should be in the result given to T1. If the system cannot ensure the correct behavior, then it does not deal with the phantom record problem.

isolation property of a database transaction

a transaction should appear as though it is being executed in isolation from other transactions, even though many transactions are executing concurrently. That is, the execution of a transaction should not be interfered with by any other transactions executing concurrently

Which of the indexes are dense, and which are not?

Dense: Secondary Non-Dense: Primary, Clustering

consistency preservation property of a database transaction

a transaction should be consistency preserving, meaning that if it is completely executed from beginning to end without interference from other transactions, it should take the database from one consistent state to another

Define the violation caused by dirty read

a transaction that reads information from another transaction, the initial transaction commits while the other transaction aborts. This causes the source used in the initial transaction to become incorrect

Describe the four levels of isolation in SQL.

Level 0: if it does not overwrite the dirty reads of higher-level transactions Level 1: has no lost updates Level 2: has no lost updates and no dirty reads Level 3: (true isolation) has, in addition to level 2 properties, repeatable reads

Define indexing field

Record structure is consisting of several fields. The record fields are used to construct an index. An index access structure is usually defined on a single field of a file. Any field in a file can be used to create an index and multiple indexes on different fields can be constructed on a field.

durability property of a database transaction

the changes applied to the database by a committed transaction must persist in the database. These changes must not be lost because of any failure

define dense index

an index has an index entry for every search key value (and hence every record) in the data file. Index record contains the pointer and search key value to the records on the disk

define nondense (sparse) index

an index has entries for only some of the search values

Why is a B+-tree usually preferred as an access structure to a data file?

entries in the internal nodes of a B+-Tree leading to fewer levels improving the search time. In addition that, the entire tree can be traversed in order using the pent pointers

Why can we have at most one primary or clustering index on a file, but several secondary indexes?

primary and clustering index can use a single key field such that both of them cannot be there in a file but for secondary index, a unique value can be taken as a key field in every records or a non-key filed with the repeated values in which the pointers will point to another block that have pointers to the repeated values

define block anchor

the total number of entries in the index is the same as the number of disk block in the ordered data file. The first record in each block of the data file is called a block anchor.