Database Design and Applications (S2-14_SSZG518)

1.In a wait-for graph, an edge drawn from  transaction  T1  to  T2  indicates that-

Select one:

a.T1 reads a data item before  T2 reads the same.   

b.T2 waits on T1 (for some data item to be released).

c.T2 reads a data item before  T1 reads the same. 

d.T1 waits on T2 (for some data item to be released).

Answer:d

2.It is possible to construct a secondary index on a unique non-ordering attribute/field of a relation. 

Select one:

True

False

Answer: True

3.In a B+ tree, if the block size is 1024 Bytes,  record size is 148 Bytes,  key field length is 12 Bytes,  Block pointer length is 6 Bytes, the max. number of keys in each internal node of the B+ tree is___.

Select one:

a.57

b.58

c.56

d.55

Answer: 56

4.A transaction can abort/fail even after its entering into partially committed state.

Select one:

True

False

Answer: True

5.Which of the following actions is idempotent?

Select one:

a.both redo and undo

b.Redo 

c.Undo

d.none

Answer: b

6.Assume that we have 5246 index blocks in  the third level of a multilevel Indexing structure. If the Block size is 512 Bytes and the blocking factor for Index file is 40, then we need __ number of index blocks at 4th level. Assume unspanned records organization.

Select one:

a.13

b.132

c.11

d.26

Answer: b

7.In a precedence graph  schedules become nodes.

Select one:

True

False

Answer: False

8.The concept of Checkpointing in database recovery avoids ___.

Select one:

a.Unnecessary  concurrency during the recovery process.

b.Unnecessary redo operations during the recovery process.

c.Early transaction failures during the recovery process.

d.Unnecessary locks during the recovery process.

Answer: b

9.We can have more than one clustering index for a give relation.      

Select one:

True

False

Answer: False

10. Database Tuning is the activity that is performed after deploying the database and the related Applications.  

Select one:

True

False

Answer: True

11.If the order (p) of the B+ tree is 7, then the minimum number of keys in any internal node is-

Select one:

a.3

b.6

c.2

d.4

Answer: d

12.Assume a scenario, where we use Timestamp-based concurrency control protocol. Consider that a data item X has been written by 5  transaction, as mentioned below, with corresponding timestamps, in the same order-

 TS-235; TS-219; TS-240; TS-225; TS-237. 

 Then the Write Timestamp of the data item X is___. 

Select one:

a.235

b.219

c.240

d.237

Answer: c

13. If we have two transactions T1 and T2 in a schedule S, and if T2 reads a data item A before T1 reads it, then in the corresponding precedence graph, we can introduce an edge from T2 to T1.     

Select one:

True

False

Answer: False

14. A transaction enters committed state immediately after__

Select one:

a.permanently writing the log to the Disk 

b.writing the updates to the log available in the buffer

c.executing the last operation   

d.Anytime

Answer: a

15.A Query Graph  gives/incorporates details about the order in which the operations are to be executed for a query. 

Select one:

True

False

Answer: b

16.If we have 18456 data blocks and 355 Index blocks in a Primary indexing scheme, then the number of block accesses needed to retrieve a record with given key value is____.

Select one:

a.12

b.11

c.10

d.9

Answer: c

17.In a B+ tree, we can find a pointer to the record with given key value,  at any level.

Select one:

True

False

Answer: False

18.The Two-phase locking protocol   will always avoid/prevents deadlocks.  

Select one:

True

False

Answer: False

19.In timestamp based concurrency control protocol,  we can apply shared and/or exclusive locks to data items.

Select one:

True

False

Answer: False

20.In a multilevel Indexing scheme (with root), the number of block accesses required to access a record with given key value is dependent on___.

Select one:

a.Number of Index blocks in the first level

b.Number of data blocks

c.Number of index levels

d.fanout of the Index block

Answer: c

21. In shadow paging based recovery approach, modified data blocks of a file  are pointed to by the shadow page-table or shadow-page directory.  

Select one:

True

False

Answer: False

22.If there are 32000 records, and the record size is 120 Bytes; and the block size is 512 Bytes. And if the records are ordered based on the key attribute. Then what is worst case number of block accesses required to access a record with given key value?    Hint: binary search can be applied on the key attribute value. Assume unspanned record organization.  Note: there is no Indexing is done.

Select one:

a.12

b.10

c.11

d.13

Answer: d

23.An Index created on non-ordering key-field is a Dense index.  

Select one:

True

False

Answer: False

24.If the block size is 1024 Bytes, and we have 32678 records in a file and each record size is 132 Bytes, then the Blocking factor for Data file is_____. (Assume unspanned records organization).

Select one:

a.10

b.7

c.32

d.8

Answer: b

25.If a schedule S2 is conflict equivalent to another schedule S1;  and  schedule S3 is conflict equivalent to schedule S2, it does not imply that S1 and S3 are conflict equivalent.  

Select one:

True

False

Answer: False

Database Design and Applications (S2-14_SSZG518)

1.Assume that we have two tables EMP and DEPT. Now when we execute the SQL query- "SELECT * FROM EMP, DEPT; "  ,and which is syntactically perfect, which of the following is correct- 

Select one:
a.Result will have EMP tuples joined with DEPT table  if some join condition is met.
b.It is Cartesian product of EMP and DEPT
c.Gives zero tuples in the result.
d.Shows SQL error since no join condition in WHERE clause

Answer: b

2.Assume that a relation R(A, B, C, D) with F={A>BCD; C>D}, is decomposed in to R1(A,B,D) and R2(A,C). Then the projection of F on R1 includes the  A > C.      
Select one:
True
False

Answer: False

3.Relational Data model is a strictly structured data model.  
Select one:
True
False

Answer: True

4.In Extendible hashing scheme, if we start with GD=3, and LD=2, then how many buckets are allocated for storing data records, in the beginning?
Select one:
a.1
b.8
c.4
d.2

Answer: c

5.For the relation R(A,B,C,D), with F={AB>C, AB>D, D>B}, the highest NF satisfied is____.
Select one:
a.BCNF
b.1NF
c.2NF
d.3NF

Answer: d

6.In case of unordered files, inserting a new record into the file is very easy compared to ordered files.
Select one:
True
False

Answer: True

7.In the process of testing whether a given decomposition is lossy or not, we need not compute the closure of original set of FDs (F) of the original undecomposed relation.
Select one:
True
False

Answer: True

8.Assume that a relation R(A, B, C, D) with F={A>BCD; C>D}, is decomposed in to R1(A,B,D) and R2(A,C). Then this decomposition is Dependency preserving.          
Select one:
True
False

Answer: False

9.Which of the following query languages is not declarative?
Select one:
a.Tuple Relational Calculus
b.DRC
c.Relational Algebra
d.SQL

Answer: c

10. In Linear Hashing scheme a random bucket can get its split image when the split criterion is met.
Select one:
True
False

Answer: False

11.If we have a FD  X (p) Y, and Y>Z, then  WX (p) Z is also a valid FD.
Select one:
True
False

Answer:False

12. We have foreign keys in  ER modeling.              
Select one:
True
False

Answer: False

13. The ordering of tuples in a relation is not significant.
Select one:
True
False

Answer: True

14. Relational Algebra is more expressive than SQL.        
Select one:
True
False

Answer: True

15. In ER modeling, the relationship between a weak entity type and its corresponding owner entity type is known as  ______ Relationship.
Select one:
a.Partial
b.Depending
c.Weak
d.Identifying

Answer: d

16.If the R/W head of a Disk-pack is on the appropriate track from where we need to read data, then the time required to place the R/W head at the beginning of the required sector/block is known as-

Select one:
a.Sector latency
b.Rotation delay
c.Sector delay
d.Disk latency

Answer:b

17.In relational algebra, the result of projection operation includes duplicates.
Select one:
True
False

Answer: False

18.In a relation, a primary key attribute can also be a foreign key.
Select one:
True
False

Answer:True

19.Why Functional Dependencies are so significant?

Select one:
a.Because they reflect data types for attributes
b.Because they represent Foreign keys
c.Because they are essential for data security
d.Because they represent business logic or some constraint.

Answer: d

20.In a disk-pack with uniform disk/surface configuration, the number of cylinders in the Disk-pack is same as number of tracks on a surface.
Select one:
True
False

Answer: True

21. In Normalization process, all decompositions that are lossless are also dependency preserving.
Select one:
True
False

Answer: False

22.The database design step, where we translate the high-level conceptual schema to implementation specific conceptual schema (Ex.  ER to Relational Schema) is known as _______ step.
Select one:
a.Implementation DB design
b.Logical DB design
c.Conceptual DB design
d.Schema refinement

Answer: b

23.For any functional dependency of the form X --> Y, in a relation, BCNF requires that      
Select one:
a.X must be a key
b.Y must be akey
c.X and Y both must be key attributes
d.Y must be a key attribute 

Answer: a

24.A data model is a collection of tables and relationships.
Select one:
True
False

Answer: False

25.In spanned record organization, some records are stored across two disk blocks.  That is some portion is one block and remainder is in the next.
Select one:
True
False

Answer: True