solutions adda
Loading...
Question 1 
Which level of locking provides the highest degree of concurrency in a relational database?
A  Page 
B  Table 
C  Row 
D  Page, table and row level locking allow the same degree of concurrency 
Question 2 
Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation R of cardinality 1 : m.
The attributes of E1 are A11, A12 and A13 where A11 is the key attribute. The attributes of E2 are A21, A22 and A23 where A21 is the key attribute and A23 is a multivalued attribute. Relation R does not have any attribute. A relational database containing minimum number of tables with each table satisfying the requirements of the third normal form (3NF) is designed from the above ERD. The number of tables in the database is
A  2 
B  3 
C  5 
D  4 
Question 3 
A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables:
Insert into department values (1, 'Mathematics') Insert into department values (2, 'Physics') Insert into student values (l, 'Navin', 1) Insert into student values (2, 'Mukesh', 2) Insert into student values (3, 'Gita', 1)How many rows and columns will be retrieved by the following SQL statement?
Select * from student, department
A  0 row and 4 columns 
B  3 rows and 4 columns 
C  3 rows and 5 columns 
D  6 rows and 5 columns 
Question 4 
A relation Empdtl is defined with attributes empcode (unique), name, street, city, state and pincode. For any pincode, there is only one city and state. Also, for any given street, city and state, there is just one pincode. In normalization terms, Empdtl is a relation in
A  1NF only 
B  2NF and hence also in 1NF 
C  3NF and hence also in 2NF and 1NF 
D  BCNF and hence also in 3NF, 2NF an 1NF 
Question 5 
A table T1 in a relational database has the following rows and columns:
roll no. marks 1 10 2 20 3 30 4 NullThe following sequence of SQL statements was successfully executed on table T1.
Update T1 set marks = marks + 5 Select avg(marks) from T1What is the output of the select statement?
A  18.75 
B  20 
C  25 
D  NULL 
Question 6 
Consider two tables in a relational database with columns and rows as follows:
Table: Student ROLL_NO NAME DEPT_ID 1 ABC 1 2 DEF 1 3 GHI 2 4 JKL 3 Table: Department DEPT_ID DEPT_NAME 1 A 2 B 3 CRoll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id.
(i) update Student set Dept_id = Null where Roll_on = 1 (ii) update Department set Dept_id = Null where Dept_id = 1
A  Both (i) and (ii) will fail 
B  (i) will fail but (ii) will succeed 
C  (i) will succeed but (ii) will fail 
D  Both (i) and (ii) will succeed 
Question 7 
Consider a table T in a relational database with a key field K. A Btree of order p is used as an access structure on K, where p denotes the maximum number of tree pointers in a Btree index node. Assume that K is 10 bytes long; disk block size is 512 bytes; each data pointer P_{D} is 8 bytes long and each block pointer P_{B} is 5 bytes long. In order for each Btree node to fit in a single disk block, the maximum value of p is
A  20 
B  22 
C  23 
D  32 
Question 8 
Consider a relational database containing the following schemas.
The primary key of each table is indicated by underlying the constituent fields.
SELECT s.sno, s.sname FROM Suppliers s, Catalogue c WHERE s.sno = c.sno AND Cost > (SELECT AVG (cost) FROM Catalogue WHERE pno = ‘P4’ GROUP BY pno);
The number of rows returned by the above SQL query is
A  0 
B  5 
C  4 
D  2 
Question 9 
Which one of the following is used to represent the supporting manyone relationships of a weak entity set in an entityrelationship diagram?
A  Ovals that contain underlined identifiers

B  Rectangles with double/bold border 
C  Diamonds with double/bold border

D  Ovals with double/bold border

Question 10 
Consider a schedule of transactions T_{1} and T_{2}:
Here, RX stands for “Read(X)” and WX stands for “Write(X)”. Which one of the following schedules is conflict equivalent to the above schedule?
A  
B  
C  
D 
Question 11 
Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?
A  A cell in R holds a set instead of an atomic value. 
B  R has a nontrivial functional dependency X→A, where X is not a superkey and A is a nonprime attribute and X is not a proper subset of any key.

C  R has a nontrivial functional dependency X→A, where X is not a superkey and A is a nonprime attribute and X is a proper subset of some key. 
D  R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute. 
Question 12 
Consider a database implemented using B+ tree for file indexing and installed on a disk drive with block size of 4 KB. The size of search key is 12 bytes and the size of tree/disk pointer is 8 bytes. Assume that the database has one million records. Also assume that no node of the B+ tree and no records are present initially in main memory. Consider that each record fits into one disk block. The minimum number of disk accesses required to retrieve any record in the database is ______.
A  4 
Question 13 
A  A relation with only two attributes is always in BCNF. 
B  If all attributes of a relation are prime attributes, then the relation is in BCNF. 
C  Every relation has at least one nonprime attribute. 
D  BCNF decompositions preserve functional dependencies. 
Question 14 
A  
B  
C  
D 
Question 15 
A  8 
Question 16 
A  
B  
C  
D 
Question 17 
A  2 
Question 18 
A library relational database system uses the following schema
USERS (User#, UserName, HomeTown) BOOKS (Book#, BookTitle, AuthorName) ISSUED (Book#, User#, Date)
Explain in one English sentence, what each of the following relational algebra queries is designed to determine
(a) σ User #=6 (11 User #, Book Title ((USERS ISSUED) BOOKS)) (b) σ Author Name (BOOKS (σ Home Town) = Delhi (USERS ISSUED)))
A  Theory Explanation. 
Question 19 
For a database relation R(a,b,c,d), where the domains a, b, c, d include only atomic values, only the following functional dependencies and those that can be inferred from them hold:
a → c b → d
This relation is
A  in first normal form but not in second normal form 
B  in second normal form but not in third normal form 
C  in third normal form 
D  None of the above 
Question 20 
Let R(a,b,c) and S(d,e,f) be two relations in which d is the foreign key of S that refers to the primary key of R. Consider the following four operations R and S
(a) Insert into R (b) Insert into S (c) Delete from R (d) Delete from S
Which of the following can cause violation of the referential integrity constraint above?
A  None of (a), (b), (c) or (d) can cause its violation 
B  All of (a), (b), (c) and (d) can cause its violation 
C  Both (a) and (d) can cause its violation 
D  Both (b) and (c) can cause its violation 
Question 21 
Given two union compatible relations R_{1}(A,B) and R_{2}(C,D), what is the result of the operation R_{1}A = CAB = DR_{2}?
A  R_{1} ∪ R_{2} 
B  R_{1} × R_{2} 
C  R_{1}  R_{2} 
D  R_{1} ∩ R_{2} 
Question 22 
Which normal form is considered adequate for normal relational database design?
A  2 NF 
B  5 NF 
C  4 NF 
D  3 NF 
Question 23 
There are 5 records in a database.
There is an index file associated with this and it contain the values 1, 3, 2, 5 and 4. Which one of the fields is the index built form?
A  Age 
B  Name 
C  Occupation 
D  Category 
Question 24 
Which of the following query transformations (i.e. replacing the l.h.s. expression by the r.h.s. expression) is incorrect? R_{1} and R_{2} are relations, C_{1}, C_{2} are selection conditions and A_{1}, A_{2} are attributes of R_{1}?
A  σ_{C1}(σ_{C1}(R_{1})) → σ_{C2}(σ_{C2}(R_{1})) 
B  σ_{C1}(σ_{A1}(R_{1})) → σ_{A1}(σ_{C1}(R_{1})) 
C  σ_{C1}(R_{1} ∪ R_{2}) → σ_{C1}(R_{1}) ∪ σ_{C1} 
D  π_{A1}(σ_{C1}(R_{1})) → σ_{C1}(σ_{A1}(R_{1})) 
Question 25 
(a) Suppose we have a database consisting of the following three relations.
FREQUENTS(student, parlor) giving the parlors each student visits.
SERVES(parlor, icecream) indicating what kind of icecreams each parlor serves.
LIKES(student, icecream) indicating what icecreams each parlor serves.
(Assuming that each student likes at least one icecream and frequents at least one parlor)
Express the following in SQL:
Print the students that frequent at least one parlor that serves some icecream that they like.
(b) In a computer system where the 'bestfit' algorithm is used for allocating 'jobs' to 'memory partitions', the following situation was encountered:
When will the 20K job complete? Note  This question was subjective type.
A  Theory Explanation. 
Question 26 
(a) Four jobs are waiting to be run. Their expected run times are 6, 3, 5 and x. In what order should they be run to minimize the average response time?
(b) Write a concurrent program using par begin  par end to represent the precedence graph shown below.
A  Theory Explanation. 
Question 27 
Consider the following database relations containing the attributes
Book_id Subject_Category_of_book Name_of_Author Nationality_of_Author with Book_id as the Primary Key.
(a) What is the highest normal form satisfied by this relation?
(b) Suppose the attributes Book_title and Author_address are added to the relation, and the primary key is changed to (Name_of_Author, Book_Title), what will be the highest normal form satisfied by the relation?
A  Theory Explanation. 
Question 28 
Consider the following relational database schemes:
COURSES(Cno, name) PREREQ(Cno, pre_Cno) COMPLETED(student_no, Cno)
COURSES give the number and the name of all the available courses.
PREREQ gives the information about which course are prerequisites for a given course.
COMPLETED indicates what courses have been completed by students.
Express the following using relational algebra:
List all the courses for which a student with student_no 2310 has completed all the prerequisites.
A  Theory Explanation. 
Question 29 
Consider the join of a relation R with a relation S. If R has m tuples and S has n tuples then the maximum and minimum sizes of the join respectively are
A  m + n and 0 
B  mn and 0 
C  m + n and m – n 
D  mn and m + n 
Question 30 
The relational algebra expression equivalent to the following tuple calculus expression:
{t t ∈ r ∧(t[A] = 10 ∧ t[B] = 20)} isA  σ_{(A=10∨B=20)} (r) 
B  σ_{(A=10)} (r) ∪ σ_{(B=20)} (r) 
C  σ_{(A=10)} (r) ∩ σ_{(B=20)} (r) 
D  σ_{(A=10)} (r)  σ_{(B=20)} (r) 
Question 31 
Let R = (A, B, C, D, E, F) be a relation scheme with the following dependencies: C→F, E→A, EC→D, A→B. Which of the following is a key of R?
A  CD 
B  EC 
C  AE 
D  AC 
Question 32 
Which of the following is correct?
A  Btrees are for storing data on disk and B^{+} trees are for main memory. 
B  Range queries are faster on B* trees. 
C  Btrees are for primary indexes and B* trees are for secondary indexes. 
D  The height of a B* tree is independent of the number of records. 
Question 33 
A  This schedule is serialized and can occur in a scheme using 2PL protocol 
B  This schedule is serializable but cannot occur in a scheme using 2PL protocol 
C  This schedule is not serialiable but can occur in a scheme using 2PL protocol 
D  This schedule is not seralisable and cannot occur in a scheme using 2PL protocol. 
Question 34 
Consider the schema R = (S T U V) and the dependencies S → T, T → U, U → V and V → S. Let R = (R1 and R2) be a decomposition such that R1 ∩ R2 ≠ ∅ . The decomposition is
A  not in 2NF 
B  in 2NF but not 3NF 
C  in 3NF but not in 2NF 
D  in both 2NF and 3NF 
Question 35 
Consider the circuit shown below. In a certain steady state, the line Y is at '1'. What are the possible values of A, B and C in this state?
A  A = 0, B = 0, C = 1 
B  A = 0, B = 1, C = 1 
C  A = 1, B = 0, C = 1 
D  A = 1, B = 1, C = 1 
Question 36 
Which of the following sets of component(s) is/are sufficient to implement any arbitrary Boolean function?
A  XOR gates, NOT gates 
B  2 to 1 multiplexors 
C  AND gates, XOR gates 
D  Threeinput gates that output (A⋅B) + C for the inputs A⋅B and C 
E  Both B and C 
Question 37 
Which of the following is/are correct?
A  An SQL query automatically eliminates duplicates 
B  An SQL query will not work if there are no indexes on the relations 
C  SQL permits attribute names to be repeated in the same relation 
D  None of the above 
Question 38 
Consider a Btree with degree m, that is, the number of children, c, of any internal node (except the root) is such that m ≤ c ≤ 2m1. Derive the maximum and minimum number of records in the leaf nodes for such a Btree with height h, h≥1. (Assume that the root of a tree is at height 0.)
A  Theory Explanation. 
Question 39 
Consider the set of relations
EMP(Employeeno, Deptno, Employeename, Salary) DEPT(Deptno, Deptname, Location)
Write an SQL query to:
(a) Find all employee names who work in departments located at "Calcutta" and whose salary is greater than Rs. 50,000.
(b) Calculate, for each department number, the number of employees with a salary greater than Rs. 100,000.
A  Theory Explanation. 
Question 40 
B^{+}trees are preferred to binary trees in databases because
A  Disk capacities are greater than memory capacities 
B  Disk access is much slower than memory access 
C  Disk data transfer rates are much less than memory data transfer rates 
D  Disks are more reliable than memory 
Question 41 
Given the relations
employee (name, salary, deptno) and department (deptno, deptname, address)
Which of the following queries cannot be expressed using the basic relational algebra operations (σ, π, ×, ⋈, ∪, ∩, )?
A  Department address of every employee 
B  Employees whose name is the same as their department name 
C  The sum of all employees’ salaries 
D  All employees of a given department 
Question 42 
Given the following relation instance.
x y z 1 4 2 1 5 3 1 6 3 3 2 2
Which of the following functional dependencies are satisfied by the instance?
A  XY → Z and Z → Y 
B  YZ → X and Y → Z 
C  YZ → X and X → Z 
D  XZ → Y and Y → X 
Question 43 
Given relations r(w, x) and s(y, z), the result of
select distinct w, x from r, s
is guaranteed to be same as r, provided
A  r has no duplicates and s is nonempty 
B  r and s have no duplicates 
C  s has no duplicates and r is nonempty 
D  r and s have the same number of tuples 
Question 44 
In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent?
A  x = 5 not AND (not (x = 5) 
B  x = 5 AND x > 4 and x < 6, where x is an integer 
C  x ≠ 5 AND not (x = 5) 
D  None of the above 
Question 45 
(a) Suppose you are given an empty B^{+}tree where each node (leaf and internal) can store up to 5 key values. Suppose values 1,2,….. 10 are inserted, in order, into the tree, Show the tree pictorially
(i) After 6 insertions, and
(ii) After all 10 insertions
Do NOT show intermediate stages.
(b) Suppose instead of splitting a node when it is full, we try to move a value to the left sibling. If there is no left sibling, or the left sibling is full, we split the node. Show the tree after values, 1, 2,….., 9 have been inserted. Assume, as in (a) that each node can hold up to 5 keys.
(c) In general, suppose a B^{+}tree node can hold a maximum of m keys, and you insert a long sequence of keys in increasing order. Then what approximately is the average number of keys in each leaf level node.
(i) In the normal case, and
(ii) With the insertion as in (b).
A  Theory Explanation is given below. 
Question 46 
Consider a bank database with only one relation
transaction (transno, acctno, date, amount)
The amount attribute value is positive for deposits and negative for withdrawals.
(a) Define an SQL view TP containing the information.
(acctno, T1.date, T2.amount)
for every pair of transactions T1, T2 such that T1 and T2 are transaction on the same account and the date of T2 is ≤ the date of T1.
(b) Using only the above view TP, write a query to find for each account the minimum balance it ever reached (not including the 0 balance when the account is created). Assume there is at most one transaction per day on each account, and each account has had atleast one transaction since it was created. To simply your query, break it up into 2 steps by defining an intermediate view V.
A  Theory Explanation is given below. 
Question 47 
Relation R with an associated set of functional dependencies, F, is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set of relations is
A  Zero 
B  More than zero but less than that of an equivalent 3NF decomposition 
C  Proportional to the size of F^{+} 
D  Indetermine 
Question 48 
With regard to the expressive power of the formal relational query languages, which of the following statements is true?
A  Relational algebra is more powerful than relational calculus. 
B  Relational algebra has the same power as relational calculus. 
C  Relational algebra has the same power as safe relational calculus. 
D  None of the above. 
Question 49 
A B^{+} tree index is to be built on the Name attribute of the relation STUDENT. Assume that all student names are of length 8 bytes, disk blocks are of size 512 bytes, and index pointers are of size 4 bytes. Given this scenario, what would be the best choice of the degree (i.e. the number of pointers per node) of the B^{+}tree?
A  16 
B  42 
C  43 
D  44 
Question 50 
Relation R is decomposed using a set of functional dependencies, F, and relation S is decomposed using another set of functional dependencies, G. One decomposition is definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a quaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of F and G are available).
A  Dependencypreservation 
B  Losslessjoin 
C  BCNF definition 
D  3NF definition 