## Database-Management-System

 Question 1
The following relation records the age of 500 employees of a company, where empNo (indicating the employee number) is the key: empAge (empNo, age) Consider the following relational algebra expression:

What does the above expression generate?
 A Employee numbers of all employees whose age is not the maximum. B Employee numbers of only those employees whose age is the maximum. C Employee numbers of all employees whose age is not the minimum. D Employee numbers of only those employees whose age is more than the age of exactly one other employee.
Question 1 Explanation:
The given relational algebra expression will result in employees whose age is not minimum. The given conditional join, joins the relations if the age is greater than any of the ages mentioned in the database.
 Question 2
Let ri(z) and wi(z) denote read and write operations respectively on a data item z by a transaction Ti. Consider the following two schedules.
S1: r1(x) r1(y) r2(x) r2(y) w2(y) w1(x)
S2: r1(x) r2(x) r2(y) w2(y) r1(y) w1(x)
Which one of the following options is correct?
 A S1is not conflict serializable, and S2 is conflict serializable. B Neither S1nor S2is conflict serializable. C Both S1and S2are conflict serializable. D S1is conflict serializable, and S2is not conflict serializable.
Question 2 Explanation:
 Question 3
Consider the relation R(P, Q, S, T, X, Y, Z, W) with the following functional dependencies

Consider the decomposition of the relation R into the consistent relations according to the following two decomposition schemes.
D1: R=[(P,Q,S,T); (P,T,X); (Q,Y); (Y,Z,W)]
D2: R=[(P,Q,S);(T,X);(Q,Y);(Y,Z,W)]
Which one of the following options is correct?
 A D1is a lossy decomposition, but D2is a lossless decomposition. B Both D1and D2are lossy decompositions. C Both D1and D2are lossless decompositions. D D1is a lossless decomposition, but D2is a lossy decomposition.
Question 3 Explanation:

Given functional dependencies set:

PQ->X

P->YX

Q->Y

Y->ZW

• While merging the tables there should be some common attribute(s) and it should be a candidate key of one of the tables.

• R1 should be merged with R2 because PT is a key of R2.
• R3 should be merged with PQSTX because Q is a key of R3.
• R4 should be merged with PQSTXY because Y is a key of R4.

• R1 should be merged with R3 because Q is a key of R3.
• R4 should be merged with PQSY because Y is a key of R4.
• Now, there is no common attribute in between R2(TX) and PQSYZW.
• Hence, D2 is lossy decomposition.
 Question 4
Suppose a database system crashes again while recovering from a previous crash. Assume checkpointing is not done by the database either during the transactions or during recovery. Which of the following statements is/are correct?
 A The same undo and redo list will be used while recovering again. B The database will become inconsistent. C The system cannot recover any further. D All the transactions that are already undone and redone will not be recovered again.
Question 4 Explanation:
Suppose a database system crashes again while recovering it from the previous crash then also we need to make use of the redo and undo list to recover the system.
 Question 5
A relation r(A, B) in a relational database has 1200 tuples. The attribute A has integer values ranging from 6 to 20, and the attribute B has integer values ranging from 1 to 20. Assume that the attributes A and B are independently distributed.
 A 820
Question 5 Explanation:

Explanation :

Probability of 1st condition being satisfied(say P(A)) = 10/15 = 2/3

Probability of 2nd condition being satisfied(say P(B)) = 1/20

Probability of both conditions being satisfied(say P(A intersection B)) = 2/3*1/20 = 1/30

Probability of any one condition being satisfied = P(A union B) = P(A)+P(B)-P(A intersection B) = 2/3 + 1/20 - 1/30 = 41/60

therefore, expected number of tuples = (41/60)*1200 = 820

 Question 6

State True or False with reason
There is always a decomposition into Boyce-Codd normal form (BCNF) that is lossless and dependency preserving.

 A True B False
Question 6 Explanation:
BCNF decomposition can always be lossless, but it may not be always possible to get a dependency preserving BCNF decomposition.
 Question 7

An instance of a relational scheme R(A, B, C) has distinct values for attribute A.
Can you conclude that A is a candidate key for R?

 A Yes B No
Question 7 Explanation:
Because FD's are defined on the schema itself, not the instance. So, based on the state of the instance we cannot say what holds for schema (there can be many instances for R).
 Question 8

Give a relational algebra expression using only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.

 A Out of syllabus (For explanation see below)
Question 8 Explanation:
R - (R - S)
→ No need of using Union operation here. → In question they gave (∪, −) but we don't use both.
→ And also they are saying that only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.
So, the expression is minimal.
 Question 9

State True or False with reason
Logical data independence is easier to achieve than physical data independence

 A True B False
Question 9 Explanation:
Logical data independence is more difficult to achieve than physical data independence, since application programs are heavily dependent on the logical structure of the data that they access.
 Question 10

Consider the following relational schema:

COURSES (cno, cname)
STUDENTS (rollno, sname, age, year)
REGISTERED FOR (cno, rollno)

(a) Write a relational algebra query to
Print the roll number of students who have registered for cno 322.
(b) Write a SQL query to
Print the age and year of the youngest student in each year.

 A Theory Explanation.
 Question 11

Consider B+ − tree of order d shown in figure? (A) B+ − tree of order d contains between d and 2d keys in each node. (a) Draw the resulting B+ − tree after inserted in the figure.

(b) For a B+ − tree of order d with n leaf nodes, the number of nodes accessed during a search is 0(-).

 A Theory Explanation.
 Question 12

(a) Consider the relation scheme R(A, B, C) with the following functional dependencies:
A, B → C, C → A
Show that the scheme R is the Third Normal Form (3NF) but not in Boyce-Code Normal Form (BCNF).
(b) Determine the minimal keys of relation R.

 A Theory Explanation.
 Question 13

Consider the relation scheme.

AUTHOR      (ANAME, INSTITUTION, ACITY, AGE)
PUBLISHER   (PNAME, PCITY)
BOOK        (TITLE, ANAME, PNAME)

Express the following queries using (one or more of )SELECT, PROJECT, JOIN and DIVIDE operations.
(a) Get the names of all publishers.
(b) Get values of all attributes of all authors who have published a book for the publisher with PNAME = ‘TECHNICAL PUBLISHERS’.
(c) Get the names of all authors who have published a book for any publisher located in Madras.

 A Theory Explanation.
 Question 14

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 14 Explanation:
The inner query “select avg(cost) from catalogue where pno='P4' group by pno;” returns:
AVG(COST)
------------
225
The outer query “select s.sno, s.sname from suppliers s, catalogue c where s.sno=c.sno” returns:
SNO SNAME
----------------------------------------
S1 M/s Royal furniture
S1 M/s Royal furniture
S1 M/s Royal furniture
S2 M/s Balaji furniture
S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
So, the final result of the query is:
SN SNAME
----------------------------------------
S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
Therefore, 4 rows will be returned by the query.
 Question 15

Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship 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 15 Explanation:
An entity set that does not have sufficient attributes to form a primary key is termed as a weak entity and an entity set that has a primary key is termed as strong entity set. For a weak entity set to be meaningful, it must be associated with another entity set, called identifying or owner entity set. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship and it is represented by double diamond. The identifying relationship is many-to-one from the weak entity set to the identifying entity set and the participation of weak entity set in the relationship is total.
 Question 16

Consider a schedule of transactions T1 and T2:

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 16 Explanation:
• Two schedules are said to be conflict equivalent, if conflict operations in both the schedules are executed in the same order.
• First, let’s list the conflict operations of each of the schedule given in the options and compare with the conflict operations of schedule which is given in the question.
Given schedule:

Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R1(C) → W2(C)
R2(D) → W1(D)
Option(1):

Conflict operations:
R1(C) → W2(C)
W1(D) → R2(D)
W1(B) → R2(B)
W1(B) → W2(B)
Option(2):

Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R2(D) → W1(D)
R1(C) → W2(C)
Option(3):

Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R2(D) → W1(D)
W2(C) → R1(C)
Option(4):

Conflict operations:
R1(C) → W2(C)
W1(D) → R2(D)
R2(B) → W1(B)
W2(B) → W1(B)
The conflict operations in the option (2) and given schedule are appearing in the same sequence order, so option (2) is the answer.
 Question 17

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 non-prime 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 non-prime 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 17 Explanation:
R(ABCD)
FDs:
AB → C
BC → A
(BD)+ = BD ✖
(ABD)+ = ABDC ✔
(CBD)+ = CBDA ✔
Candidate keys = {ABD, CBD}
• The relation R is in 3NF, as there are no transitive dependencies.
• The relation R is not in BCNF, because the left side of both the FD’s are not Super keys.
• In R, BC → A is a non-trivial FD and in which BC is not a Super key and A is a prime attribute.
 Question 18

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 18 Explanation:
Block factor = 4096/20 = 204
(1) Database BF = 1
No. of block = 106 } ➝ 1 block access from database
(2) ⎡106/204⎤ = 491
(3) ⎡491/204⎤ = 3
(4) ⎡3/204⎤ = 1
So, 1+3 = 4 disk accesses are required to retrieve any record in the database.
 Question 19

Which of the following is TRUE?

 A Every relation in 3NF is also in BCNF B A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R C Every relation in BCNF is also in 3NF D No relation can be in both BCNF and 3NF
Question 19 Explanation:
BCNF is a stronger version 3NF. So straight from definition of BCNF every relation in BCNF will also be in 3NF.
 Question 20

Given the basic ER and relational models, which of the following is INCORRECT?

 A An attribute of an entity can have more than one value B An attribute of an entity can be composite C In a row of a relational table, an attribute can have more than one value D In a row of a relational table, an attribute can have exactly one value or a NULL value
Question 20 Explanation:
Option (A): In ER-model, multivalued attribute of an entity can have more than one value.
Option (B): In ER model, the attribute which can be further broken down into some other attributes is called composite attribute.
Option (C): In Relational model, the intersection of one row and column should contain only one value. So, option (C) is INCORRECT.
Option (D): In Relational model, the intersection of one row and column should contain either exactly one value or NULL.
 Question 21

Which of the  following statements are TRUE about an SQL query?

P:An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause.
Q:An SQL query can contain a HAVING clause even if it has a GROUP BY clause.
R: All attributes used in the GROUP BY clause must appear in the SELECT clause.
S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
 A P and R B P and S C Q and R D Q and S
Question 21 Explanation:
The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. The attributes used in GROUP BY clause must present in SELECT statement.
The HAVING Clause enables you to specify conditions that filter which group results appear in the results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. So, we cannot use HAVING clause without GROUP BY clause.
 Question 22

Consider the following transactions with data items P and Q initialized to zero:

T1: read (P) ;
read (Q) ;
if P = 0 then Q : = Q + 1 ;
write (Q) ;
T2: read (Q) ;
read (P) ;
if Q = 0 then P : = P + 1 ;
write (P) ;

Any non-serial interleaving of T1 and T2 for concurrent execution leads to

 A a serializable schedule B a schedule that is not conflict serializable C a conflict serializable schedule D a schedule for which a precedence graph cannot be drawn
Question 22 Explanation:

The above schedule is not conflict serializable.
 Question 23

Suppose R1(A, B) and R2(C, D) are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in R2. If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE?

 A ∏B (r1) - ∏C (r2) = ∅ B ∏C (r2) - ∏B (r1) = ∅ C ∏B (r1) = ∏C (r2) D ∏B (r1) - ∏C (r2) ≠ ∅
Question 23 Explanation:
Referential integrity means, all the values in foreign key should be present in primary key.
So we can say that r2(C) is the superset of r1(B).
So (subset - superset) is always empty.
 Question 24

Consider the following relations A, B, C.

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of AUB is the same as that of A.

(AUB)⋈A.Id>40∨C.Id<15 C
 A 7 B 4 C 5 D 9
Question 24 Explanation:

Performs the cross product and selects the tuples whose A∙Id is either greater than 40 or C∙Id is less than 15. It yields:
 Question 25

Consider the following relations A, B, C.

How many tuples does the result of the following SQL query contain?

SELECT A.id
FROM A
WHERE A.age > ALL (SELECT B.age
FROM B
WHERE B. name = "arun")
 A 4 B 3 C 0 D 1
Question 25 Explanation:

First query (2) will be executed and 0 (no) rows will be selected because in relation B there is no Name ‘Arun’.
The outer query (1) results the follow and that will be the result of entire query now. (Because inner query returns 0 rows).
 Question 26

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 27

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 27 Explanation:
Candidate key is ab.
Since all a, b, c, d are atomic. So the relation is in 1NF.
Checking the FD's
a → c
b → d
We can see that there is partial dependencies. So it is not 2NF.
So answer is option (A).
 Question 28

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 28 Explanation:
Let take example:

Here 'd' is the foreign key of S and let 'a' is the primary key of R.
(A) Insertion into R: will cause no violation.
(B) Insertion into S: may cause violation because there may not be entry of the tuple in relation R. Example entry of 〈S4, __, __〉 is not allowed.
(C) Delete from R: may cause violation. For example, deletion of tuple 〈S2, __, __〉 will cause violation as there is entry of S2 in the foreign key table.
(D) Delete from S: will cause no violation as it does not result inconsistency.
 Question 29

Given two union compatible relations R1(A,B) and R2(C,D), what is the result of the operation R1A = CAB = DR2?

 A R1 ∪ R2 B R1 × R2 C R1 - R2 D R1 ∩ R2
Question 29 Explanation:
The join here will be selecting only those tuples where A=C and B=D, meaning it is the intersection.
 Question 30

Which normal form is considered adequate for normal relational database design?

 A 2 NF B 5 NF C 4 NF D 3 NF
Question 30 Explanation:
3NF, is considered as adequate for normal relational database design, because we can have a 3NF decomposition which is dependency preserving and lossless (not possible for any higher forms).
 Question 31

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 31 Explanation:
Indexing will be on occupation field because occupation field lexigraphically sorted will give the sequence 1, 3, 2, 5, 4.
 Question 32

Which of the following query transformations (i.e. replacing the l.h.s. expression by the r.h.s. expression) is incorrect? R1 and R2 are relations, C1, C2 are selection conditions and A1, A2 are attributes of R1?

 A σC1(σC1(R1)) → σC2(σC2(R1)) B σC1(σA1(R1)) → σA1(σC1(R1)) C σC1(R1 ∪ R2) → σC1(R1) ∪ σC1 D πA1(σC1(R1)) → σC1(σA1(R1))
Question 32 Explanation:
If the selection condition is on attribute A2, then we cannot replace it by RHS as there will not be any attribute A2 due to projection of A1 only.
 Question 33

(a) Suppose we have a database consisting of the following three relations.
FREQUENTS(student, parlor) giving the parlors each student visits.
SERVES(parlor, ice-cream) indicating what kind of ice-creams each parlor serves.
LIKES(student, ice-cream) indicating what ice-creams each parlor serves.
(Assuming that each student likes at least one ice-cream and frequents at least one parlor)
Express the following in SQL:
Print the students that frequent at least one parlor that serves some ice-cream that they like.

(b) In a computer system where the 'best-fit' 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 34

(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 35

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 36

Consider the following relational database schemes:

COURSES(Cno, name)
PRE-REQ(Cno, pre_Cno)
COMPLETED(student_no, Cno)

COURSES give the number and the name of all the available courses.
PRE-REQ gives the information about which course are pre-requisites 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 pre-requisites.

 A Theory Explanation.
 Question 37

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 37 Explanation:
For maximum:
Suppose there is no common attribute in R and S due to which natural join will act as cross product. So then in cross product total no. of tuples will be mn.
For minimum:
Suppose there is common attribute in R and S, but none of the row of R matches with rows of S then minimum no. of tuples will be 0.
 Question 38

The relational algebra expression equivalent to the following tuple calculus expression:

{t| t ∈ r ∧(t[A] = 10 ∧ t[B] = 20)} is
 A σ(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 38 Explanation:
The given relational algebra expression represents tuples having A=10 and B=20 which is equivalent to
σ(A=10) (r) ∩ σ(B=20) (r)
 Question 39

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 39 Explanation:
Let's check closure for each option,
A) (CD)+ = cdf
Not a key.
B) (EC)+ = ecdabf
Yes, it is a key.
C) (AE)+ = aeb
Not a key. D) (AC)+ = abcf
Not a key.
 Question 40

Which of the following is correct?

 A B-trees are for storing data on disk and B+ trees are for main memory. B Range queries are faster on B* trees. C B-trees 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 40 Explanation:
Range queries are faster on B+ trees.
 Question 41
For the schedule given below, which of the following is Correct?
1 Read A
2 Read B
3 Write A
4 Read A
5 Write A
6 Write B
7 Read B
8 Write B
 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 41 Explanation:
Let's draw precedence graph,

Since cycle exist so not conflict serializable.
And we know that if the schedule is not serializable then it is not 2PL.
Hence correct option is (D).
 Question 42

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 42 Explanation:
Since R1 ∩ R2 ≠ ∅, so the decomposition is lossless join. Now since all the attributes are keys, so R1 ∩ R2 will be a key of the decomposed relation.
And since every attribute is key so the decomposed relation will be in BCNF and hence in 3NF.
 Question 43

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 43 Explanation:

So the above equation is satisfied if either C=0 or A=0 and B=1.
Hence, Option (B) is correct.
 Question 44

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 Three-input gates that output (A⋅B) + C for the inputs A⋅B and C E Both B and C
Question 44 Explanation:
(A) Not complete because, XOR can be used to make only NOT gate and NOT gate is already available. Hence not complete.
(B) 2 to 1 multiplexors is functionally complete.
(C) XOR gate can be used to make a NOT gate. So, (AND, NOT) is functionally complete.
(D) With given gates and inputs NOT gate cannot be derived.
Hence, not complete.
 Question 45

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 45 Explanation:
→ SQL won't remove duplicates like relational algebra projection, we have to remove it explicitly by distinct.
→ If there are no indexes on the relation SQL, then also it works.
→ SQL does not permit 2 attributes to have same name in a relation.
 Question 46

Consider a B-tree with degree m, that is, the number of children, c, of any internal node (except the root) is such that m ≤ c ≤ 2m-1. Derive the maximum and minimum number of records in the leaf nodes for such a B-tree with height h, h≥1. (Assume that the root of a tree is at height 0.)

 A Theory Explanation.
 Question 47

Consider the set of relations

EMP(Employee-no, Dept-no, Employee-name, Salary)
DEPT(Dept-no, Dept-name, 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 48

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 48 Explanation:
In B+ trees it is easy to reduce the number of last level access from the disk when the disk size is too large.
 Question 49

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 49 Explanation:
The sum of all employee’s salaries can’t be represented by using the given six basic algebra operation. If we want to represent sum of salaries then we need to use aggregation operator.
 Question 50

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 50 Explanation:
A functional dependency A→B is said to hold if for two tuples t1 and t2.
If for t1[A] = t2[A] then t1[Y] = t2[Y].
There are 50 questions to complete.

Register Now