DBW301
# What is the difference between the minimum and the maximum number of records a table having a B+-tree index with 3 index levels (root and two more) and a leaf level (rough estimate). Leafs contain the rows. An entry (key, record) has 100 Bytes. The number of successors of index nodes is 50 <=k <= 100. The net page size (header not taken into account) of leafs and internal nodes is 8000 Bytes.
About 75,000,000
# The advantage of hashing is
Fast computation of hash values from a key
# Why is dynamic block assignment (indirect mapping) of the DBS page address space to disk blocks is to prefer a static assignment
Flexibility of disk block assignment
# A secondary index may be used
For evaluation of a foreign key relationship
# What is the main difference between ISAM and B+-tree indexes?
ISAM has a fixed number of index levels
# Database replication typically
Increases availability
# In the interaction between a database and a program a result set
Is fetched record by record from the DBS
# When are all records of a table stored on disk in increasing key order?
Key-sequenced B+-tree index (Oracle: index organized table)
# Suppose locking is used for synchronization of transactions. A lost update may occur if
Locks are set before accessing/ updating an object and released immediately after.
# The primary Copy (Master copy) server for replication
May be be substituted by a copy in case of a crash by a voting protocol
# What does the "WAL" mean?
Never write into the database before a log record has been written
# Suppose in a relational schema for a library we have a table "Books" and the row of a book contains attributes of the student who lend the book, (E.G. Books(bNo, title, ..., studentNo, studentName, Tel, ...) What is wrong with the design?
Not in 3rd normal form
# Which kind of relationship between entities does not necessarily need a separate table when represented in the relational model?
One-to-one
# A deadlock my be solved
Only if the DBS aborts one of the involved transactions
# Which is the correct sequence of query processing steps
Parsing - query rewrite - optimization - code generation - execution
# The following histtory is executed in this order (without synchronization). r2(x) r1(y) w2(x) r1(x) c1 a2 (a2 means abort of TA2) This results in a
Read uncommitted
# Why is traversal of a non-clustered (B+-tree) index - leaf pages contain pointers to heap storage - expensive?
Records may be distrubuted over the persistent storage, needs block access for each key
# Why are B+trees used in database systems instead of e. g. hash maps?
Records may be traversed in key sequence
# What can a correctly defined trigger not be used for?
Sending an email after a deletion of a row of some table occurred
# Which of the following statements about a bitmap index for attributes of some table R is wrong?
Should only be used if s has a large number of values
# Concurrent execution of 2 transactions with isolation level "read uncommitted" will
Sometimes results in an inconsistent DB state
# Which of the following is NOT part of the disk access time?
Surface torsion time
# Stored procedures have the advantage
That they can use iterative statements
# Which parameter is not relevant for calculating the height of a B+-tree index?
The data type of the attributes of the records which are not keys
# The coordinator of a distrubuted transaction crashes after sending the decision (commit/abort) to the resource managers and after having received one of the acknowledgements from the resource managers (more than one). What will happen?
The resource managers which did not get the decision are blocked
# Which statement is correct with regard to transactions?
Transaction is a logical unit of work on the database
# Which statement is incorrect with regards to the deadlock?
Two-phase locking (2PL) eliminates the deadlock
# Which one is not an element of a database ER model
Verification
# When does a transaction T using snapshot isolation (MVCC) read a version which was committed after the begin but before the end of T?
When no other transaction has written x and committed during the lifetime of T
#QN=20 Immediate database modification technique uses
aa. Both undo and redo
#QN=10 What is a disjoint less constraint?
aa. It requires that an entity belongs to no more than one level entity set
#QN=17 The operation which is not considered a basic operation of relational algebra is
aa. Join
#QN=4 Checkpoints are a part of
aa. Recovery measures
#QN=8 The rule that a value of a foreign key must appear as a value of some specific table is called a
aa. Referential constrain
#QN=16 In an E-R diagram double lines indicate
aa. Total participation
#QN=43 Which one is true statement?
aa. With finer degree of granularity of locking a high degree of concurrency is possible
#QN=27 In SQL the word ?natural? can be used with
aa. inner join
#QN=30 Shadow paging has
aa. no redo
#QN=31 If the closure of an attribute set is the entire relation then the attribute set is a
aa. superkey
#QN=26 When R ? S ? ? , then the cost of computing R S is
aa. the same as R x S
#QN=13 A relation is in _________ if an attribute of a composite key is dependent on an attribute of other composite key.
bb. 3NF
#QN=39 Which of the following aggregate functions does not ignore nulls in its results?.
bb. COUNT (*)
#QN=2 Which of the following is not a property of transactions?
bb. Concurrence
#QN=15 What are the desirable properties of a decomposition?
bb. Dependency preservation
#QN=14 What is data integrity?
bb. It is the data contained in database that is accurate and consistent
#QN=6 The language that requires a user to specify the data to be retrieved without specifying exactly how to get it is
bb. Non-Procedural DM
#QN=9 The clause in SQL that specifies that the query result should be sorted in ascending or descending order based on the values of one or more columns is
bb. Order b
#QN=21 In SQL the statement select * from R, S is equivalent to
bb. Select * from R cross join S
#QN=22 Which of the following is not a consequence of concurrent operations?
bb. Update anomaly
#QN=47 If an entity can belong to only one lower level entity then the constraint is
bb. partial
#QN=25 When an E-R diagram is mapped to tables, the representation is redundant for
bb. weak relationship sets
#QN=12 It is an abstraction through which relationships are treated as higher level entities
cc. Aggregation
#QN=3 Relational Algebra does not have
cc. Aggregation operators
#QN=32 DROP is a statement in SQL
cc. DDL
#QN=5 Tree structures are used to store data in
cc. Hierarchical model
#QN=18 Fifth Normal form is concerned with
cc. Join dependency
#QN=35 In 2NF
cc. No partial FDs exist
#QN=37 In an E-R, Y is the dominant entity and X is a subordinate entity. Then which of the following is incorrect
cc. Operationally, if X is deleted, so is Y
#QN=38 Relational Algebra is
cc. Procedural query Language
#QN=34 The keyword to eliminate duplicate rows from the query result in SQL is
cc. UNIQUE
#QN=50 Use of UNIQUE while defining an attribute of a table in SQL means that the attribute values are
cc. both (A) & (B)
#QN=29 If a transaction T has obtained an exclusive lock on item Q, then T can
cc. both read and write Q
#QN=41 Consider the join of relation R with a relation S. If R has m tuples and S has n tuples, then the maximum and minimum size of the join respectively are
cc. mn and 0
#QN=48 The common column is eliminated in
cc. natural join
#QN=11 According to the levels of abstraction, the schema at the intermediate level is called
conceptual schema
#QN=36 Which one is correct statement? Logical data independence provides following without changing application programs: (i) Changes in access methods. (ii) Adding new entities in database (iii) Splitting an existing record into two or more records (iv) Changing storage medium
dd. (ii) and (iii)
#QN=19 Block-interleaved distributed parity is RAID level
dd. 5
#QN=40 R (A,B,C,D) is a relation. Which of the following does not have a lossless join dependency preserving BCNF decomposition
dd. A?BCD
#QN=23 As per equivalence rules for query transformation, selection operation distributes over
dd. All of the above
#QN=46 Which of the following is a reason to model data?
dd. All of the above
#QN=49 In SQL, testing whether a subquery is empty is done using
dd. EXISTS
#QN=1 Which of the following relational algebra operations do not require the participating tables to be union-compatible?
dd. Joi
#QN=42 Maximum height of a B+ tree of order m with n key values is
dd. None of these
#QN=45 Which of the following concurrency control schemes is not based on the serializability property?
dd. None of these
#QN=28 The default level of consistency in SQL is
dd. Serializable
#QN=44 Which of the following statement on the view concept in SQL is invalid?
dd. The definition of a view should not have GROUP BY clause in it
#QN=33 If two relations R and S are joined, then the non matching tuples of both R and S are ignored in
dd. inner join
# May transactions using optimistic concurrency control (BOCC) be involved in a deadlock?
never
# Which of the following algebraic query transformations is correct in all cases? Tables are R(a,b), S(a,b)
proj b (R join S) j = proj b (R join proj b (S), proj b = project on attribute b
# Suppose strict 2PL is used for the sequence of operations received by the scheduler r1(x) r2(x) r2(y) w2(x) r1(y) w1(y). What is the effective sequence of operations executed?
r1(x) r2(x) r2(y) r1(y) - deadlock
# What is the minimal number of tables used to represent a specialization of an entity set E with two specializations S1 and S2
1
# What is the estimated cardinality of the following operations on tables T(a,b), S(a,c). T has 100000 rows, 10000 different values in column a, 5000 in column b, table S has 2000 rows, 100 different values in a, 20 in column c: sel (a=10)(T)
10
# What is the estimated cardinality of the following operations on tables T(a,b), S(a,c). T has 100000 rows, 10000 different values in column a, 5000 in column b, table S has 2000 rows, 100 different values in a, 20 in column c: (sel (c=1)(T join S)
1000
# How many hash collisions occur if the key set {110, 15, 98, 22, 18, 9} is mapped onto a table of length k using the hash function h(k) = k modulo 7 (or h(k) = k%7)
2
# What is the estimated cardinality of the following operations on tables T(a,b), S(a,c). T has 100000 rows, 10000 different values in column a, 5000 in column b, table S has 2000 rows, 100 different values in a, 20 in column c: UNIQUE(proj (c) (S))
20
# What is the estimated cardinality of the following operations on tables T(a,b), S(a,c). T has 100000 rows, 10000 different values in column a, 5000 in column b, table S has 2000 rows, 100 different values in a, 20 in column c: S join T
20,000
# Given tables R(a,b), S(a,c). What is the estimated cardinality of the following operation assumed table R has 100000 rows, 100000 different values in column a primary keys, 500 in column b, table S has 2000 rows, 500 different values in a, 1000 in column c. proj c (R join S) /* "join" is always natural join, value preservation.
2000
# Let R be a table with B(R) blocks on disk. B(R) <= M*(M-1), where M is the number of blocks of main memory which can be used for sorting, aggregation, join operations etc. What is the cost estimated cost of a merge sort in terms of block accesses
3*B(R)
# Given tables R(a,b), S(a,c). What is the estimated cardinality of the following operation assumed table R has 100000 rows, 100000 different values in column a primary keys, 500 in column b, table S has 2000 rows, 500 different values in a, 1000 in column c. sel(b=3)(R join S)
4
# How many bits are used for a bitmap index of attribute b of a table with 100000 rows, if b has 5 values? The index is uncompressed.
500,000
# How many bushy query trees (not deep right or left) are possible for the expression? R join S join T
6
# Hash collisions need
A mechanism to find the keys/ records with the same hash value (like chaining)
# Why does consistent hashing *using a hash ring like [0...2^128-1] support availability?
A new node may be easily inserted because of the large spectrum [0...2^128-1] of addresses
# If the isolation level is Repeatable Read
A phantom record may occur (e.g. after insertion)
#QN=7 Precedence graphs help to find a
Aa. Serializable schedule
#QN=24 The metadata is created by the
Cc. DDL interpreter
# What does lazy replication mean?
Copies are updated at some time in the future
# What are bitmap indexes are often used for?
Counting
# Which one of the following sentences is correct when choosing an index?
Do not add secondary index to any attribute or relation that is frequently updated
# Database logs
Ensure recovery after failure