CS 430 Database System

Views are considered updatable if what conditions are satisfied?

- The from clause only had one database relation - The select clause contains only attribute names of the relation and does not have an expressions, aggregates or distinct specification. -Any attribute not listed in the select clause can be set to null.

The time it takes for a particular hashing or indexing technique to find a particular data item, or set of items.

Access time

After a crash, the transaction table is rebuilt during which phase?

Analysis

T/F: A candidate key must consist of a single attribute with an atomic domain.

False

T/F: BCNF can always be achieved without sacrificing losslessness or dependency preservation.

False

T/F: Data mining refers loosely to the process of semiautomatically analyzing raw input such as newspapers and field data to create reports.

False

T/F: In a database, a write() operation ensures the immediate write of the variable to persistent storage.

False

T/F: In a timestamp concurrency control system, timestamps are assigned by the system administrator before the transaction begins.

False

T/F: Relational schemas and other metadata about relations are stored in a structure called system cache.

False

T/F: Using multigranularity locking, if a node is locked in IX mode, explicit locking is done at the lower level of the tree, but only with shared locks.

False

In SQL, we can using pattern matching to match strings. The pattern '__% Cubs' does which of the following (note, there are 2 underline characters before the %):

Matches any string of at least 2 characters followed by a space and Cubs.

Do the queries below return the same result? SELECT name, employee_dept FROM employee, emp_dept WHERE employee.id = emp_dept.id; SELECT name, employee_dept FROM employee NATURAL JOIN emp_dept;

Same

A database schema is depicted by what?

Schema diagram

When some hash buckets are assigned more records than are others, and a bucket may overflow even as other buckets still have space, it is called ________________.

Skew

What describes the kinds of operations or transactions that will be performed in the database?

Specification of functional requirements

What best describes the advantage of hashing over a tree structure for indexing ?

Storing indexes in hash structures could result in less disk I/Os.

A _______ is a set of one or more attributes that uniquely identify an entity (or tuple).

Superkey

What is the concept of referential integrity?

That a foreign key entry in one table must point to a valid record in the table associated with the foreign key.

We say that a relation schema R is in first normal form if:

The domains of all attributes of R are atomic

T/F: A file is a collection of pages each containing a collection of records. Support must include inserts, deletes, modifies, and reads among other operations.

True

T/F: Aggregation is an abstraction through which relationships are treated as higher level entities.

True

T/F: Using multigranularity locking, if a node is locked in SIX mode, The sub-tree rooted by that node is locked explicitly in shared mode, and explicit locking is done at the lower level of the tree with exclusive locks.

True

T/F: View names may appear in a query any place where a relation name may appear.

True

In a real database system, the write operation updates the data on disk:

eventually

Sorting of relations that do not fit in memory is called _________ sorting.

external

The state after the discovery that normal execution can no longer proceed:

failed

In query processing, the ________ is the lowest-level operator to access data.

file scan

A sequence of primitive operations that can be used to evaluate a query is called a __________ plan.

query-evaluation

The ______________ stores information on a sector magnetically, and there is one for each side of each platter of a disk.

read-write head

The ______________s are kept as close as possible to the disk surface to increase the recording density.

read-write head

A ___________ schedule is one where, for each pair of transactions T1 and T2, such that T1 reads a data item previously written by T2, the commit operation of T2 appears before the commit operation of T1.

recoverable

To remove an authorization, we use the __________ command.

revoke

____________ are a scheme that allows authorizations to be assigned to a set of responsibilities, and people to be assigned to those responsibilities.

roles

Any attribute or set of attributes with which a file may locate a record or set of records.

search key

What are the components of the query processor?

-DML compiler -DDL interpreter -Query evaluation engine

What are reasons to let the DBMS manage disk space and buffer management as opposed to the OS?

-Need the ability to perform special tasks such as pinning a page, pre-fetch policy -OS limitations: files spanning disks -Portability issues between operating system

T/F: A search key is same as a candidate key.

False

The state after the transaction has successfully completed:

committed

A relation schema R is in third normal form with respecto to set F of functional dependencies, if for all functional dependencies in F+ of the form A -> B where A is a subset of R, B is an attribute of R, and at least one of the following holds:

-A -> B is a trivial functional dependency -A is a superkey for R. -B is part of a key for R.

A relation schema R is in BCNF with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form A -> B, where A is a subset of R, B is a subset of R - and at least one of the following hold:

-A -> B is a trivial functional dependency (that is, B is a subset of A) -A is a superkey for schema R.

Why might we not want to place the restriction on an E-R diagram that only binary relationship sets be used?

-A n-ary relationship set shows more clearly that several entities participate in a single relationship -There may be a way to translate constraints on the ternary relationship in to constraints on the binary relationships -An identifying attribute may have to be created for the entity set created to represent the relationship set, increasing the complexity of the design

What are contained in a lock table entry for an object - which can be a page, a record, etc. ?

-A pointer to a queue of lock requests -The number of transactions currently holding a lock on the object -The nature of the lock (shared or exclusive).

Consider the following relation. Check all the answers below that hold for this table. A B C D a1 b1 c1 d1 a1 b3 c1 d2 a3 b1 c1 d3 a2 b7 c1 d5

-ABD -> C -ABC -> D -AB -> D -AB -> CD

Operations and their effect

-Aggregate function: takes a collection of values and returns a single value as a result -Outer join: extension of the join operation to deal with missing information

ARIES phase and its description.

-Analysis: identifies dirty page in the buffer pool and active transactions at the time of the crash -Redo: repeats all actions, starting from an appropriate point in the log, and restore the database state to what it was at the time of the crash -Undo: undoes the actions of the transactions that did not commit, so that the database reflects only the actions of committed transactions

The transaction property and its definition.

-Atomicity: either all of the operations of the transaction are reflected properly in the database or none are -Consistency: execution of a transaction in isolation preserves the consistency of the database -Isolation: even though multiple transactions may execute concurrently, the system guarantees that...... -Durability: after a transaction completes successfully, the change its has made to the database persist

Consider the following relation. Check all the answers below that hold for this table. A B C D a1 b1 c1 d1 a1 b3 c1 d2 a3 b2 c1 d3 a2 b7 c1 d5

-B -> ACD -D -> ABC -AB -> D -B -> A

Type of data storage and definition

-Cache: the fastest and most costly form of storage -Main Memory: the storage medium used for data that is available to be operated on -Magnetic-disk storage: the primary medium for long-term on-line storage of data -Tape Storage: used primarily as a backup storage medium

Which properties does the following schedule have? T1 T2 ----------- ----------- R(A) W(A) R(A) W(A) R(B) W(B) R(B) W(B) Commit Commit

-Conflict Serializable -Recoverable

What are true about the dirty page table used by the ARIES algorithm?

-Contains a field called recLSN which identifies the earliest log record that might have to be redone for this page during the restart from a crash -Is reconstructed during the Analysis phase of the recovery algorithm -Contains one for every dirty page in the buffer pool -The entry contains a field called recLSN, which is the LSN of the first log record that caused the page to be dirty

What are the data structures the storage manager implements as part of the physical system?

-Data files -Data dictionary -Indices

Type of consistency constraint and definition

-Domain constraints: a domain of possible values -Referential integrity: ensuring that a value that appears in one relation also in another -Assertions: any condition at a database must also satisfy -Authorization: type of access users are permitted to do

The recovery manager is responsible for ensuring what transaction properties?

-Durability -Atomicity

Term used in E-R models and its definition.

-Entity: a things or object in the real world that is distinguishable from all other objects -Entity set: a set of objects of the same type that shared same properties -Attribute: descriptive properties possessed by each member of an entity set -Value: assigned to each attribute with an entity

What are techniques used by the buffer manager to address all the disk blocks?

-Forced output of blocks -Pinned blocks -Buffer replacement strategy

When timestamp-based concurrency control is used, transactions are assigned a timestamp when they begin. This timestamp is used to ensure conflict serializability. When a transaction wants to write an object O, what are checks done to ensure serializability?

-If TS(T) < RTS(O), the write action conflicts with the most recent read action of O, and T is aborted and restarted - If all checks are valid, T writes O, and WTS(o) is set to TS(T) -If TS(T) < WTS(O), the Thomas Write Rule allows us to ignore the outdated write

What is necessary for two schedules to be view equivalent?

-If Ti read the initial value of object A in S1, it must also read the initial value of A in S2. -If Ti reads a value of A written by Tj in S1 it must also read the value of A written by Tj in S2. -For each data object A, the transaction (if any) that performs the final write on A in S1 must also perform the final write on A in S2.

What are true about parsing the log during the Analysis phase?

-If a log record other than the end record for a transaction T is encountered, the lastLSN field is set the LSN of the log record. -If a log record other than the end record for a transaction T is encountered, an entry for T is added to the transaction table if it is not already there. -If an end log record for a transaction T is encountered, T is removed from the transaction table. -If a commit log record is encountered, the status of the transaction is set to C. -If a redoable log record affecting page P is encountered and P is not in the dirty page table, an entry is inserted into the table with recLSN equal to the LSN of this redoable log record.

What actions does the lock manger take upon receiving a lock request from a transaction?

-If the requested lock cannot be immediately granted, the lock request is added to the queue of lock requests for this object and the transaction is suspended -If a shared lock is requested, the queue of requests is empty, and the object is not currently locked in exclusive mode, the lock manager grants the lock and updates the lock entry table for the object -If an exclusive lock is requested and no transaction currently holds a lock on the object (which also implies the queue of requests is empty), the lock manager grants the lock and updates the lock table entry for the object

A query evaluation plan consists of what?

-Implementation methods to use for each relational operator -An extended relational algebra tree with additional annotations at each node

What best describes the 2 parts of the representation of a record with variable-length attributes?

-Initial part with fixed length attributes -Data for variable length attributes

Database design phase to its description.

-Initial phase: characterize fully the data needs of the prospective database users -Conceptual-design phase: the designer chooses a data model and by applying the concepts of the chosen data model, translates into a conceptual schema -Specification of functional requirements: users describe the kinds of -Logical-design phase: designer maps the high-level conceptual schema onto the implementation data model of the database system that will be used -Physical-design phase: the physical features of the database, such as internal storage structures are specified

What are types of access for a data manipulation language?

-Insertion and deletion of data -Retrieval of information

What are true about B+ tree-based indexing?

-It is an index structure that ensures that all paths from the root to a leaf in a given tree are of the same height. -The lowest level of the tree - called the leaf level - contains the data entries -Finding the correct leaf page is faster than a binary search

What is true about lock manager?

-It is the part of the DBMS that keeps track of the locks issued to transactions -It maintains a lock table, which is a hash table with the data object identifier as the key

What are reasons to allow transactions to run concurrently rather than serially?

-Multi-user support -Improved throughput and resource utilization -Reduced waiting time

Database users and definition

-Naive user: unsophisticated users accessing the system through application programs -Application programmers: Computer professionals who write packages that facilitate access for end users -Sophisticated/Specialized users: Users who interact with the system and/or write specialized database applications -Database administrators: Professionals that design and maintain the database

What are disadvantages to file processing systems?

-Operating systems provide only a password mechanism for access control -We must protect the data from inconsistent changes made by different users accessing the data concurrently -We must ensure the data is restored to a consistent state if the system cashes while changes are being made -We need special programs to answer each question a user may want to ask about the data

What are steps involved in processing a query?

-Parsing and translation -Evaluation -Optimization

What are common techniques used to develop algorithms for each relational operator evaluation?

-Partitioning: decomposing an operation into a less expensive collection of operations -Iteration: examining all tuples in an input table, one after another -Indexing: using an index to examine just those tuples that satisfy the condition

Type of query language and definition

-Procedural: the user instructs the system to perform a sequence of operations -Non-Procedural: the user describes the desired information

Terms associated with queries and its definition

-Query language: a specialized language in which queries can be posed -Relational algebra: a formal query language based on a collection of operators manipulating relations

Component of a DBMS and its associated responsibility

-Query optimizer: uses information about how the data is stored to produce an efficient execution plan for evaluating the query -File and access methods layer: supports the concept of a file, which in a DBMS is a collection of pages or a collection of records -Buffer manager: brings pages in from disk to memory as needed in responses to read requests -Disk space manager: deals with management of space on disk, where the data is stored. -Transaction manager: ensures that transactions requests and release locks according to a suitable locking protocol -Lock manager: keeps track of requests for locks and grants locks on database objects when they become available -Recovery manager: responsible for maintaining a log and restoring the system to a consistent state after a crash

RAID level and its description

-RAID 0: improves performance by striping the files across multiple drives, NO parity -RAID 1: provides redundancy by mirroring each of the drives -RAID 1+0: provides a striped set from a set of mirrored drive -RAID 5: allows a single drive to fail without data loss by providing block level parity, does NOT provide P+Q redundancy

The phase in optimistic concurrency control and its action.

-Read: the transaction executes, reading values from the database and writing it to a private workspace -Validation: if the transaction decides to commit, the DBMS checks to see whether the transaction could possibly conflict with any other currently executing transaction -Write: if validation determines there are no possible conflicts, the changes to the data objects are made by the transaction in its private workspace are copied into the data

Responsibility for the component of DBMS architecture

-Recovery manager: Ensures transaction atomicity and durability -Transaction manager: Controls the execution of the transactions -Lock managers: Grants and releases locks appropriately

Component of an E-R diagram and its description.

-Rectangles: entity set -Items within the rectangles: attribute -Diamonds: relationship set -Lines: link attributes to entity sets and entity sets to relationship sets -Double line: shows total participation of entity in the relationship -Line with arrow: show a key constraint from the entity to the relationship -Double line rectangle with associated double line diamond: weak entity set

What are valid phases of the Aries recovery algorithm?

-Redo -Undo -Analysis

What are methods to improve reliability?

-Redundancy -Improving MTBF -Mirroring -Redundant arrays of independent disks (RAID)

Term used in relationship sets and its definition

-Relationship: an association among several entities -Relationship set: a set of association among several entities of the same type -Participation: when an entity is a part of a relationship set -Relationship instance: in an E-R schema, this represents an association between the named entities in the real world enterprise being modeled

Join type to the expected effect in the resulting relation.

-Right outer join: right not match any from left -Left outer join: left not math any from right -Full outer join: both the left and right

Functional dependency rules of inference

-Rule of Reflexity: If Y is a subset of X, then X -> Y -Rule of Transitivity: If X -> Y and Y -> Z, then X -> Z -Rule of Augmentation: If X -> Y and Z is a set of attributes, then XZ -> YZ (or XunionZ -> YunionZ) -Rule of Union: If X -> Y and X -> Z, then X-> YZ -Rule of Decomposition: If X-> YZ, then X -> Y and X -> Z

Clause of the SELECT statement to its definition.

-SELECT: list the attributes desired in the result of the query -FROM: list the relations to be scanned in the evaluation of expression -WHERE: consists of the predicate involving attributes of the relations

Disk movement term and its description

-Seek time: moving arms to position the disk head on track -Rotational delay: time for the block to rotate under the head -Transfer time: time to move the data to/from the surface

Relational algebra name and its operation

-Selection: return rows of the input relation that satisfy the predicate -Projection: output specified attributes from all rows of the input relation -Natural join: output pairs of rows from the two input relations that have the same value on all attributes that have the same name -Cartesian product: output all pairs of rows from the two input relations -Union: output the union of tuples from the two input relations

The isolation level and its specification

-Serializable: ensures serializable execution -Repeatable read: allows only committed data to be read and requires that between two reads of an item by a transaction, no other transaction may update -Read committed: allows only committed data to be read, but does not require repeatable reads -Read uncommitted: allows uncommitted data to be read

The operations and their effect

-Set-intersection operation: a more convenient way to write r-(r-s) -Natural join operation: a binary operation that allows us to combine certain selections and a Cartesian-product into a single operation -Division operation: suitable to queries that include phrase "for all" -Assignment operation: assigns parts of a relational-algebra expression to temporary relation variables

The lock type in multigranular locking and its associated action.

-Shared lock(S): used when only reading an object is required. -Exclusive lock(X): used when writing an object is required. -Intention shared(IS): used to lock the ancestors of a object being locked in shared mode. -Intention exclusive(IX): used to lock the ancestors of an object being locked in exclusive mode. -Shared intention exclusive(SIX): logically equivalent to holding both a shared (S) and an intention exclusive (IX) lock.

Multigranular locking schemes are particularly useful in applications that include a mix of:

-Short transactions that only access a few items -Long transactions that produce reports from an entire set of files

Attribute type and its description.

-Simple attributes: attributes that have not been subdivided in to sub-parts -Composite attributes: attributes that are divided into other attributes -Single-valued attributes: attributes that have a single value for a particular entity -Multi-valued attributes:attributes that have a set of value for a particular entity -Derived attributes:an attributes whose value is computed from the value of other attributes

Typical functions of a database administrator would include what?

-Storage structure and access-method definition -Schema definition -Security and access rights definition

Type of keys and its definition

-Superkey: a set of one or more attributes that allow us to identify an entity in the entity set -Candidate key: a superkey that contains no extraneous attributes -Primary key: a candidate key that is chosen by the database designer as the principle means of identifying entities within an entity set -Foreign key: an attribute in one relation that references the primary key of another relation

What is true about checkpointing?

-Taking a checkpoint periodically reduces the amount of work to be done during restart -When a system comes back after a crash, the restart process begins by locating the most recent checkpoint record - A checkpoint is like a snapshot of the DBMS state - A fuzzy checkpoint is one where the system is not quiesed

What is true about a serializable schedule?

-The database instance that results from from executing a schedule that is a serializable schedule is identical to the database instance that results from executing the transactions in some serial order -A serializable schedule of a set S of committed transactions is a schedule whose effect on any consistent database instance is guaranteed to be identical to that of some serial schedule over S

What actions are done when processing the log during the Undo phase of the ARIES algorithm?

-The log is scanned backward from the end of the log -The goal of the phase is to undo the actions of all transactions active at the time of the crash

During the redo phase, a logged action must be redone unless what conditions holds:

-The pageLSN stored on the page on disk is greater than or equal to the LSN of the log record being checked. -The affected page is not in the dirty page table -The affected page is in the dirty page table, but the recLSN for the entry is greater than the LSN of the log record being checked.

What is the information the DDL features of SQL can specify about a relation?

-The schema about each relation -The integrity constraint -The type of an individual attribute within the relation -A set of indices to be maintained for each relation

What are true about the transaction table used in ARIES?

-The status of a transaction can be in progress, committed, aborted. -Each entry in the transaction table contains (among other things) the transaction id, the status, and the LastLSN - the LSN of the most recent log entry for this transaction. -Contains one entry for every active transaction.

What tasks are performed during the Analysis phase of the ARIES algorithm?

-The transactions that were active at the time of the crash and must be undone are determined -A superset of pages in the buffer pool are determined that were dirty at the time of the crash -The point in the log is determined at which to start the Redo phase

A precedence graph - also known as a serializability graph - captures all the potential conflicts between transactions in a schedule. What is true about a precedence graph?

-There exists a node for each committed transaction in S -There exists an edge from Ti to Tj if an action of Ti precedes and conflicts with one of Tj's actions

In the validation phase of optimistic concurreny control, timestamps are assigned to the transactions and a set of criterion is applied to see whether the time-stamp ordering of transactions is equivalent to some serial order. For every pair of transactions Ti and Tj such that TS(Ti) ≤ TS(Tj), one of the following selections must hold:

-Ti complete before Tj starts its write phase and Ti does not write any database object read by Tj -Ti completes all three phases before Tj begins -Ti completes its read phase before Tj completes its read phase and Ti does not write any database object that is either read or written by Tj

We create views for what purposes?

-To provide a restricted set of tables to a set of users -To provide a simplified view of the database for users

Result of handling an unknown value in the "and" Boolean operation.

-True and Unknown: unknown -False and unknown: false

Algebra operation and its effect

-Union operation: binary operation that returns the values present in both tables -Set-difference operations: finds tuples that are in one relation but not in another -Cartesion-product operation: combines information from any two relations -Rename operations: provides a unique identifier to the results of a relational algebra operation

The storage term and their definition.

-Volatile storage: information that does not survive a system crash -Non-volatile storage: information that survive a system crash -Stable storage: information that is never lost

In a deadlock prevention system using timestamps, there are two policies the lock manager can use to prevent deadlock if Ti requests a lock that Tj holds. What are they?

-Wound-wait: if Ti has a higher priority, abort Tj; otherwise, Ti waits -Wait-die - if Ti has a higher priority, it is allowed to wait; otherwise it is aborted

The principle behind the recovery algorithm and its description.

-Write ahead logging: any change to a database object is first recorded in the log, the record to the log must be written to the stable storage before the change to the database is written to disk. -Repeating history during redo: on restart following a crash, the recovery system retraces all actions of the DBMS before the crash and brings the system back to the exact state that it was in at time of.... -Logging changes during undo: changes make to the database while undoing a transaction are logged to ensure such an action is not repeated in the events of the repeated restarts

Under what circumstances must a database index be updated?

-a record is inserted into a database -a record is deleted from the database

The steps to checkpointing in ARIES and their description.

-begin_checkpoint: the record written to indicate when the checkpoint starts -Construction of end_checkpoint record: identifies all the contents in the transaction table and the dirty page table -end_checkpoint record written: contains all the contents of the current transaction table and dirty page table -master record updated: a special record is written that contains the LSN of the last begin_check point record

Domain type and its description

-char(n): a fixed-length character string with user-specified length n -varchar(n): a variable length character string with user-specified maximum length n -int: integer -smallint: a small integer -numeric: a fixed-point number with user-specified precision -real, double-precision: floating-point and double-precision floating-point numbers with machine-dependent precision

The fields in an update log record and its description.

-pageID: the page id of the modified page -length: length in byte of the change -offset: the offset within the record of the change -before-image: the value of the changed bytes before the change -after-image: the value of the changed bytes after the change

What are factors to be taken into account when choosing a RAID level?

-performance during a disk rebuild -monetary cost of extra disk-storage requirements -performance when a disk has failed -performance requirements in terms of number of I/O operations

Which of the relational algebra operations are binary and which are unary?

-select: unary -project: unary -rename: unary -Cartesian product: binary -assignment: binary -set difference: binary -divide: binary -natural join: binary -left outer join: binary -right outer join: binary -full outer join: binary

What are common subqueries - a select-from-where expression nested within another query?

-set comparison -test for empty relations -test for the absence of duplicate tuples -set membership

Relational terms and definition

-table: collection of relations/entity set -row: represents a relationship among set of values/entity -column headers: attruibutes -domain: set of permitted values

A faculty member is a person; a student is a person. These are two examples of ______________.

-the IS-A relationship -generalization

What about a database table are stored in the system catalog?

-the file structure for the table -the integrity constraints on the table -the name of the table -attribute name and type for each attribute in the table

The term associated the log and its definition.

-trail or journal: a history of actions executed by the DBMS -log sequence number: unique id assigned to every log record -pageLSN: contained in every page of database, this describes LSN of the most recent log record that describes a change to this page -prevLSN: the LSN of the last record written by this transaction -transID: the transaction id -type: the type of the log type

When choosing a hash function to assign search key values to buckets, the function should have what?

-uniform distribution -random distribution

For a set of 100 data records with 5 fields per record, how many clustered indexes can there be?

1

Storage device hierarchy and its speed

1(Fastest): cache 2: main memory 3: flash memory 4: disk 5(Slowest): magnetic tapes

Storage device hierarchy and its cost

1(Most expensive): cache 2: main memory 3: flash memory 4: magnetic disk 5: optical disk 6(Least expensive): magnetic tapes

Deleting a data entry from a B+ tree to its order of sequence.

1) Find Leaf L where entry exists 2) Remove entry, replace index if necessary 3) If L falls below d, try to redistribute 4) If L falls below d and redistribution fails, merge L with sibling 5) Recursively merge index node where necessary

The steps to insert a data entry into a B+ tree to their order.

1) Find the correct leaf L. 2) If room in L, put data entry into L, done. 3) Split L, redistribute entries evenly, copy up middle key. 4) Insert index entry pointing to L2 into parent of L. 5) If necessary, split index node recursively.

Clauses of the SELECT statement in their operational order.

1-FROM 2-WHERE 3-SELECT

A B+ tree with a typical order of 100 and fill factor of 2/3(fanout of 133), with height of 4 can handle how many records?

133^4

The primary difference between a B tree indices and a B+ tree indices is what?

A B-tree eliminates the redundant storage of search -key value

Entities are described in a database by what?

A set of attributes

An instance of a relation is what?

A set of tuples, also called records, in which each tuple has the same number of fields as the relation schema

Consider the relation R with attributes: A, B, C, D, E, and F. Let S be a set of functional dependencies in R such that S = { A -> B, CD -> E, C -> A}. Which of these attributes are in the closure of {A, B}+?

A, B

Because all the pages cannot be brought into memory at the same time, the DBMS must bring pages into main memory as they are needed and, in the process, decide what existing page in main memory to replace to make room for the new page. Which architecture layer of a DBMS is responsible for determining which page to replace?

Buffer manager

Consider the relation R with attributes: A, B, C, D, E, and F. Let S be a set of functional dependencies in R such that S = { A -> B, CD -> E, C -> D}. Which of these attributes are in the closure of {C, F}+?

C, D, E, F

T/F: A compensation log record (CLR) is written just after the change recorded in an update log record is undone.

False

T/F: A conjuction of conditions of the form where op is a comparison operator is said to be in Bryce Codd Normal Form.

False

T/F: A database design is in BCNF if one member of the set of relation schemas that constitutes the design is in BCNF.

False

T/F: A database design is in BCNF if one member of the set of the relation schemas that constitutes the design is in BCNF.

False

T/F: A marginalized temporary table is one that is created to pass intermediate tables between operators.

False

T/F: A nested query is a query that has another query embedded within it; the embedded query is called a recursive query.

False

T/F: A primary index is one that includes the primary key, indexes that include only other candidate keys are called secondary indexes.

False

T/F: A schedule is view serializable if and only if it is view equivalent to some conflict serializable schedule.

False

T/F: A search key is the same as a candidate key.

False

T/F: A set of name (first, last) is an example of an atomic value.

False

T/F: A transaction is a single operation that performs multiple logical functions within a database.

False

T/F: A transaction is single operation that performs multiple logical functions within a database.

False

T/F: An assertion is a predicate expressing a condition that you want to ensure never occurs within the database.

False

T/F: An attribute takes a null value when the value of the attribute is computed to be zero.

False

T/F: An attributes takes a null value when the value of the attribute is computed to be zero.

False

T/F: An object-oriented data model permits the specification of data where individual data items of the same type many have different sets of attributes.

False

T/F: Ensuring atomicity is the responsibility of the system administrator.

False

T/F: Every serializable schedule is conflict serializable.

False

T/F: If a lock is acquired on the entire database before each transaction starts, and released when it is committed, a conflict serializable schedule is created, but not a view serializable one.

False

T/F: If we are inserting a data entry into a bucket in a static hashing table and there is no space in the bucket, we split the bucket and put the entry into the new bucket.

False

T/F: If you perform a relational algebra cross-product of two relation instances that contain exactly the same attributes (and their associated domains) and exactly the same values in those relation instances, you will get the same result as performing a natural join on those relation instances.

False

T/F: Indices are created by using the "GRANT index {index-name} on {relation-name} using {field-name}" command.

False

T/F: It is desirable for all users to see the entire logical model.

False

T/F: It is not possible for several attributes to have the same domain, we require they be unique.

False

T/F: It is required that the primary key of an entity set be used as an attribute of another entity set when that entity set is in total participation of a relationship set.

False

T/F: Multiversion concurrency control is rarely used in today's systems.

False

T/F: Relational Algebra is a non-procedural query language.

False

T/F: SQL specifies strings by enclosing them in double quotes.

False

T/F: The DDL interpreter of the query processor is responsible for query optimization.

False

T/F: The NOT NULL specification allows the insertion of a null value for the associated attribute.

False

T/F: The ORDER BY clause lists items in descending order by default.

False

T/F: The SQL phrase "ON DELETE CASCADE" is associated with referential integrity as it applies to the WHERE clause.

False

T/F: The ability to double the directory by simply copying the directory to the lower part is accomplished because we are using the most significant bits.

False

T/F: The discriminator of a weak entity set is the combination of the primary key from the identifying relationship set and enough attributes to distinguish the weak entity set.

False

T/F: The log, sometimes called the trail or journal, is a history of actions executed by the lock manager.

False

T/F: The main advantage to a static hashing schemes is that the number of bucket is variable and memory allocation becomes simplified.

False

T/F: The natural-join algorithm (also called the sort-merge-join algorithm) can be used to compute natural joins and equi-joins.

False

T/F: The order of how a tuple appears in a relations is extremely important as that is what determines the primary key.

False

T/F: The project operation is a tertiary operation that returns its argument relation, the predicate is was called with, and the lead attribute.

False

T/F: The recLSN field in the dirty page table identifies the latest change to page P that may not have been written to disk.

False

T/F: The result of an SQL query is a single value.

False

T/F: The schema of a relation changes when tuples are added to the database.

False

T/F: Two scan algorithms to implement a selection operation are a linear search and a bubble search.

False

T/F: Using multigranularity locking, if a node is locked in IS mode, implicit locking is done at the lower level of the tree, but only with shared locks.

False

T/F: We can disallow null values by specifying NOT NULL as a part of the field definition. This MUST be specified when declaring the primary key.

False

T/F: We create a table in SQL using the insert table command.

False

T/F: When using timestamp-based concurrency control, every data object has a read and a write timestamp associated with it. These are set to the TS of the last transaction to successfully perform that operation on that object.

False

Translate the relational algebra expression: πperson-name(σcompany-name="Alpha"(works))?

Find the name of all employees who work for the company named Alpha

Two schedules are said to be conflict equivalent if they do what?

Involve the same set of actions of the same transactions and they order every pair of conflicting actions of two committed transactions in the same way

Which buffer replacement policy is optimal for a database system in all scenarios?

It depends - no one strategy is always optimal

What is a materialized table used for?

It is a temporary table used to pass tuples between operations

When a compensation log record (CLR) is written, what is the field undoNextLSN set to?

LSN of the next log record that is to be undone for the transaction that wrote the update record

A set of rules that we require each transaction to following while acquiring and releasing locks is called what?

Locking protocol

I've just added a new person to the Employee database and I did not have to alter my application that uses this database. This is an example of which type of abstraction?

Logical

In what phases does the design move from an abstract data mode to the design of the database?

Logical design phase and Physical design phase

How many valid serial schedules exist for a net of N transactions?

N!

A DBMS interleaves the actions of several transactions to achieve what?

None of the above

The act of granting an exclusive lock to a transaction that holds a shared lock - called a lock upgrade - fails under which of the following conditions.

None of the above

During the Undo phase of the ARIES recovery algorithm, a set called toUndo is built. What is toUndo initialized to?

None of the above.

Which of the following is not a transaction state?

None of the answers provided

One domain value that is a member of any possible domain is what?

Null

What are the two most common tertiary storage media?

Optical disks and Magnetic tape

I've just changed the indexing scheme for a certain database. The change is transparent to the rest of the database and other applications. This is an example of which type of abstraction?

Physical

A ________ is a candidate key.

Primary key

A ___________ is a candidate key.

Primary key

The difference between two-phase locking protocol and strict two-phase locking protocol is:

That under strict two_phase locking protocol, all exclusive locks must e held until the commit

The average number of children in a B+ tree is called what?

The fan-out of the tree

T/F: A decomposition is a lossless decomposition if for all legal database instances we project r onto R1 and R2, compute the natural join of the projection results, and we get back exactly r.

True

T/F: A foreign key is the primary key of another relation schema.

True

T/F: A functional dependency is a form of an integrity constraint.

True

T/F: A query is a statement requesting the retrieval of information.

True

T/F: A query language is a language in which the user requests information from the database.

True

T/F: A relation schema consists of a list of attributes and their corresponding domains.

True

T/F: A schedule is conflict serializable if it is conflict equivalent to some serial schedule.

True

T/F: A storage manger is a program module that provides the interface between the low-level data stored in the database and the applications programs and queries submitted to the system.

True

T/F: Aborting a transaction is just a special case of the Undo phase of restart in which a single transaction is undone.

True

T/F: An access path is a way of retrieving tuples from a table and consists of either 1) a file scan or 2) and index plus a matching selection condition.

True

T/F: An audit trail is a log of all changes (inserts/deletes/updates) to the database, along with information such as which user performed the action and when the change was performed.

True

T/F: An identifer that is used to rename a relation is referred to as a correlation name in the SQL standard.

True

T/F: An index on an attribute of a relation is a data structure that allows the database system to find those tuples in the relation that have a specified value for that attribute efficiently.

True

T/F: An item in the select list can be in the form of expression AS column_name, where expression is any arithmetic or string expression.

True

T/F: Atomicity refers to the requirements that either all of a transaction must occur or none of it.

True

T/F: Axioms, or rules of inference, provide a simpler technique for reasoning about functional dependencies.

True

T/F: Division is rarely implemented as a distinct operator in DBMS systems.

True

T/F: Durability refers to the fact that values within a database must survive system failures.

True

T/F: Dynamic hashing techniques allow the hash function to be modified dynamically to accommodate the growth or shrinkage of the database.

True

T/F: Extendible hashing schemes solve the problems of a static hash scheme by implementing a directory of pointers to buckets and growing the directory appropriately.

True

T/F: For each attribute, there is a set of permitted values called the domain or the value set.

True

T/F: For each view, the system catalog stores the view name and the definition of the view.

True

T/F: For two relation instances to be union compatible, the must have the same number of fields and the corresponding fields must have the same domain.

True

T/F: If a relation r is legal under a set F of functional dependencies, we say that r satisfies F.

True

T/F: If a relation schema R is not in BCNF, we can decompose R into a collection of BCNF schemas R1, R2, R3, etc by the use of the BCNF decomposition algorithm.

True

T/F: If during the Redo phase a logged action must be redone, the logged action is reapplied, and the pageLSN on the page is set to the LSN of the redone log record.

True

T/F: If some but not all entities in entity set E participates in at least two relationships in relationship set R, the participation is said to be partial.

True

T/F: If the evaluation of a query requires repeated passes to accomplish the partitioning it is called recursive partitioning.

True

T/F: In a page format that consists of packed fixed length records, free space is contiguous. In a page format that consists of unpacked fixed length records, free space can be non-contiguous.

True

T/F: In a two-phase locking protocol, transactions are required to obtain locks (and not release them) during the growing phase, and release locks (but not obtain them) during the shrinking phase.

True

T/F: In general, the goal of a relational database design is to generate a set of relation schemas that allows us to store information without unnecessary redundancy and yet allows us to retrieve information easily.

True

T/F: It is always possible to decompose a relation into 3NF form that is both lossless join and dependency preserving.

True

T/F: One of the key factors to the success of the recovery algorithm is the Write Ahead Log (WAL) protocol. WAL forces all log records up to and including the one with LSN equal to the pageLSN to stable storage before writing the page to disk.

True

T/F: One of the problems with a static hashing scheme is that long overflow chains develop.

True

T/F: Outer join query operations can be implemented by a simple extension of join algorithms.

True

T/F: SQL allows the use of null values to indicate absence of information about the value of an attribute.

True

T/F: Several operations in a query plan may be grouped together in a pipeline in which each of the operations starts working on its input tuples even as they are being generated by another operation.

True

T/F: Starvation occurs when a transaction is unable to ever acquire the resources (locks) necessary for it to complete.

True

T/F: Strict 2 phase locking protocol allows only conflict serializable schedules.

True

T/F: System catalogs are stored as a collection of tables.

True

T/F: The DBMS sees a transaction as a series of reads, writes, and a commit/abort.

True

T/F: The HAVING clause, if it is present, is applied to each group of the GROUP BY clause.

True

T/F: The buffer manager is responsible for bringing a page into RAM, and the page cannot be removed from RAM until released by the requestor(s).

True

T/F: The log tail - which is the most recent portion of the log, is kept in main memory and is periodically forced to stable storage. This allows log records and data records to be written to the disk at the same granularity.

True

T/F: The natural join operation operates on two relations and produces a single relation as the result.

True

T/F: The output of a relational algebra operation is a single relation.

True

T/F: The participation of an entity set E in a relationship set R is said to be total if every entity E participates in at least one relationship in R.

True

T/F: The person that has central control over a DBMS is called a DataBase Administrator (DBA).

True

T/F: The recLSN field in the dirty page table identifies the fastest change to page P that may not have been written to disk.

True

T/F: The schema developed at the conceptual design phase provides a detailed overview of the enterprise.

True

T/F: The select operation selects tuples that satisfy a given predicate.

True

T/F: We define a view in SQL by using the create view command.

True

T/F: We remove a relation from an SQL database using the drop table command.

True

T/F: When building an access path, an index matches a selection criteria if the index can be used to retrieve just the tuples that satisfy the condition.

True

T/F: When using a heap file organization, any record can be placed anywhere in the file where there is space for the record.

True

What are the 3 mechanisms to modify a database?

Update, Deletion, and Insertion

An entity set that does not have sufficient attributes to form a primary key is called what?

Weak entity set

If a birthdate attribute has three components (birthDay, birthMonth, birthYear), it could be treated as a ________ attribute.

composite

Date is an example of which type of attribute.

composite attribute

The database system must control the interaction among concurrent transaction to prevent them from destroying the consistency of the database. This is done through a variety of mechanisms called the ________________ -control schemes.

concurrency

We say that two operations ___________ if the operations are by different transactions on the same data item and at least one of them is a write operation.

conflict

A _________ nested query is a nested query where the inner query is dependent on the row being examined by the outer query.

correlated

Translate the relational algebra expression: ρ(R1, πnameσage=23(Employees))?

create a relation instance called R1 containing the names of all employees that are 23 years old

The ith tracks of all the platters together are called the ith _______.

cylinder

The key attribute of a hashing function is what?

even distribution of the entries throughout the table

If a transaction T has obtained an __________ lock on item Q, then T can both read and write Q.

exclusive

Which integrity constraint is used to establish and enforce referential integrity?

foreign key constraint

An indexing technique based on a uniform distribution of values across a range of buckets.

hash index

If we wish to constrain ourselves to relations on schema R that satisfy a set F of functional dependencies, we say that F ______ on R.

holds

To distinguish normal joins from outer joins, normal joins are called what?

inner joins

When a transaction holds a shared lock on an object, and wants to acquire an exclusive lock, it issues a:

lock upgrade

A data dictionary contains _____, data about data.

metadata

If a phoneNumber attribute could include more than one phone number, it could be treated as a __________ attribute.

multi-valued

The policy that allows a transaction to commit even if it has modified some blocks that have not yet been written back to disk is called the __________ policy.

no-force

An indexing technique based on a sorted ordering of the values.

ordered index

The state after the final statement has been executed:

partially committed

Each disk _________ has a flat circular shape.

platter

__________ refers to the range of activities involved in extracting data from a database.

query processing

A type of dense index, with an index entry for every search-key value and a pointer to every record in the file, where the records pointed to by successive values in the index are not stored sequentially.

secondary index

A ______________ is the smallest unit of information that can be read from or written to the disk.

sector

Which clauses of the SELECT statement can the AS clause appear?

select clause and from clause

If a transaction T has obtained a ___________ lock on item Q, then T can read but not write Q.

shared

The additional space occupied by an index structure for a particular hashing or indexing technique.

space overhead

The disk platters are mounted on a ______________.

spindle

What is true about tree-structured indexing techniques?

support both range searches and equality searches

Both the global and the local depth use the value to identify what?

the number of bits of the value returned from the hash table to use

The cardinality of a database relation is ________________.

the number of relationships in which the entity is involved

Who is responsible for ensuring durability (and atomicity)?

the recovery system

The surface of a disk is logically divided into concentric ______________s.

track

A ___________ is a statement that the system executes automatically as a side effect of a database modification.

trigger

Some functional dependencies are said to be _______ because they are satisfied by all relations.

trivial

What are set operations within SQL?

union, intersect, except

What can you say about a schedule whose precedence graph contains no cycles?

-The schedule is conflict serializable -The schedule is view serializable

What are components of the storage manager?

- Authorization and integrity manager - Transaction manager - File Manager - Buffer Manager

What are true about B+ tree index files?

- B+ tree is the most widely used index -Insert / delete at logFN cost where F = fanout and N = # leaf pages - Each node contains d

How many disk heads are there in a disk drive that has 4 double-sided platters, 200 cylinders, and 2000 tracks?

8

A language that is used to express database queries and updates is called?

A data manipulation language

What best describes a schedule?

A list of actions (reading, writing, aborting, committing) from a set of transactions and the order in which the actions occur

What is true about primary and secondary indexes?

A primary index is one that does not allow duplicates, a secondary is one that does.

A transaction is what?

Any one execution of a user program in a DBMS

One very important advantage of using a DBMS is that it offers data independence, is defined as what?

Application programs are insulated from changes in the way data is structured and stored

Static hashing is done by performing what?

Applying a hash function h to the search key to identify the bucket to which it belongs and then searching this bucket

We can prevent deadlocks by giving each transaction a priority and ensuring that lower transactions are not allowed to wait for higher priority transactions. One way is to assign timestamps to each transaction. When are these timestamps assigned?

At the time the transaction starts up

A domain is _________ if the elements of the domain are considered to be indivisible units.

Atomic

What is true about SQL?

SQL is both a Data Manipulation Language (DML) and a Data Definition Language (DDL)

A ________ is a minimal superkey.

Candidate key

What are the two possible SQL statements that must end a transaction?

Commit work and Rollback work

A snapshot of the data in a database at a given time is called what?

Database instance

I've just added a new person to the Employee database. Which of the following have I affected?

Database instance

I've just added a new column to the Employee database so that I can keep track of pay rates for each employee. Which of the following have I affected?

Database logical schema and Database instance

The logical design of the database is called what?

Database schema

A set of transactions fail to make progress due to a cycle of lock requests. This problem is best described by what term?

Deadlock

In the circumstances where we would like to apply an aggregate function to a group of sets of tuples, we specify this wish using which of the following clauses?

GROUP BY

The _________ command is used to insert data into a relation.

INSERT INTO

A global depth indicator N in an extendible hashing scheme is used to identify what?

Identifies that there are 2**N number of entries in the directory

What defines a functional dependency?

If there were a schema (A,B), then A is able to serve as a primary key.

Name the two major pitfalls of a database design process.

Incompleteness and Redundancy

What are NOT part of the metadata stored in a system catalog?

Index values

The time it takes for a particular hashing or indexing technique to insert a new data item.

Insertion time

What operations require the relation instances they are operating on to be union-compatible?

Intersection, Union, Set-difference

What things are shown in a schema diagram?

Primary key, Foreign key dependencies, and The database schema

The key advantage of using a B+ tree for an index file as opposed to a hashing scheme is what?

Range searches

What is the lowest isolation level allowed by SQL?

Read uncommitted

Pipelining is important in an execution plan because of what?

Reduces the cost by passing the tables directly from one operator to another

_________________ constraints ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

Referential integrity

A lock protocol assumes that all locks be two-phase and that all locks be held until that transaction commits. What would best describe this protocol?

Rigorous two phase locking

What are the three basic clauses of a SELECT statement?

SELECT clause, FROM clause, WHERE clause

The policy that is used to choose and unpinned page to be swapped out is called what?

The replacement policy, such as LRU and clock

In an extendible hashing scheme, the directory structure is grown when what is true?

The size of the local depth indicator exceeds the global indicator after the insert.

If R is a schema that is not in BCNF, what is true for the case?

There is at least one non-trivial functional dependency A->B such that A is not a superkey for R.

What is a waits-for graph used for?

To detect deadlock cycles.

What is the responsibility of the query optimizer?

To identify an efficient execution plan

How many tuples are there in a row of a table with a column header with 3 attributes?

a 3-tuple

When a file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index, we say that is what?

a clustered index

A language in which a user specifies a database schema is called?

a data definition language

If a view relation is stored, but the DBMS makes sure that if the actual relations in the view definition change the view is kept up to date, the view is called what?

a materialized view

What are the unit of transfer between disk and main memory?

a set of sectors

The state after the transaction has been rolled back and the database has been restored to its state prior to the start of the transaction:

aborted

Index structures are referred to as _________, since they provide a path through which data can be located and acessed.

access paths

The initial state; the state a transaction stays in while it is executing:

active

The read-write heads of all the tracks are mounted on a single assembly called a disk ________.

arm

The element within a(n) __________ domain ate not divisible.

atomic

what functions are aggregate functions?

avg, min, count

A B+ tree index takes the form of a ________ tree in which every path from the root of the tree to a leaf of the tree is of the same length.

balanced

_____________ specify the set of possible values that may be associated with an attribute.

domain constraints

The set of all functional dependencies that can be inferred give the set F, denoted by F+ is called the _______ of set F.

closure

If a relation contains as attributes the birthdate of a person as well at the age of the age of that person, the age could be treated as a ____________________ attribute.

derived

What are data types the SQL standard supports?

date, time, timestamp

If transaction T1 holds a read lock on data item A and is requesting a write lock on B, while at the same time transaction T2 holds a read lock on B and is requesting a write lock on A - this situation is known as _________.

deadlock

Below is the pseudocode for the BCNF _______________ algorithm. result := {R}; done := false; compute F+; while (not done) do if (there is a schema R, in result that is not in BCNF) then begin let A-> B be a non-trivial functional dependency that holds on R, such that A -> R is not in F+ and A intersection B = the empty set; result := (result = Ri) U (Ri-B) U (A,B); end else done := true;

decomposition

SELECT ________ forces the elimination of duplicates.

distinct