# Database Management System | Subject-Wise Solved Questions

## DBMS-subject-wise

 Question 1
In a file which contains 1 million records and the order of the tree is 100, then what is the maximum number of nodes to be accessed if B+ tree index is used?
 A 5 B 4 C 3 D 10
Question 1 Explanation:
Given
File contains 1 million records
Order of the B+ tree = Number of ptrs per node = p = 100
Maximum number of nodes = ?

So, Consider Minimum Fill factor in order to find Maximum number of nodes to be accessed
Minimum ptrs per node = ⌈p/2⌉ = 50
Number of Node in last level = 106/50 = 2 * 104
Number of  Nodes in 2nd last level= 2*104/50 =400
Number of Nodes in 3rd last level= 400/50 =8
Number of Nodes in 4th last level= 8/50 =1
The maximum number of nodes to be accessed = Number of B+ tree levels = 4
 Question 2
Which of the following is a dense index?
 A Primary index B Clusters index C Secondary index D Secondary non-key index
Question 2 Explanation:
In a Dense Index an index entry appears for every search key value in the ﬁle

option(A) : Primary index is maintain for the anchor value of block i,e 1 key value per block maintained in Index file.

option(B) : Clustering index is basically mixed sort of Indexing i,e Dense as it is maintained for unique value (key) and Sparse as it is not maintained for every value.

option(C) : Secondary index is usually dense because Secondary Index is maintained for every key value.

option(D) : There will be repetition of values in Non-Key, So there will be index only for 1 of the repeated values. Since we left some of the values so it is not dense index
 Question 3
In E-R model, Y is the dominant entity and X is a subordinate entity
 A If X is deleted, then Y is also deleted B If Y is deleted, then X is also deleted C If Y is deleted, then X is not deleted D None of the above
Question 3 Explanation:
-If any changes occur in dominant entity then they are also reflected in sub-ordinate entity but not the vice versa.
-When a tuple from the dominant is deleted then the related tuple from the sub-ordinate also deleted
 Question 4
A particular BNF definition for a “word” is given by the following rules.
• <word> ::= <letter> | <letter><pairlet> | <letter><pairdig>
• <pairlet> ::= <letter><letter> | <pairlet><letter><letter>
• <pairdig> ::= <digit><digit> | <pairdig><digit><digit>
• <letter> ::= a | b | c | ... | y | z
• <digit> ::= 0 | 1 | 2 | ... | 9
Which of the following lexical entries can be derived from < word > ?
I. pick
II. picks
III. c44
 A I, II and III B I and II only C I and III only D II and III only
Question 4 Explanation:
It is regarded as a form of automatic inspection machine problem. The key is to analyze exactly what each mode on behalf of nature.
<Letter> represents a single letter;
<digit> represent a single digit;
<pairdig> two <digit>, or a recursive <pairdig> and two <digit>, in fact, indicates the even number <digit>, i.e. even figures ;
Similarly, <pairlet> is an even number of letters. So <word> may be: a letter, letters odd or even number of digits and a letter. So the second and third are correct
 Question 5
Consider the following table in a relational database

According to the data shown in the table, which of the following could be a candidate key of the table ?
 A {Last Name} B {Room} C {Shift} D {Room, Shift}
Question 5 Explanation:
CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes.
• CANDIDATE KEY must contain unique values
• CANDIDATE KEY may have multiple attributes
• CANDIDATE KEY Must not contain null values
• CANDIDATE KEY should contain minimum fields to ensure uniqueness
• CANDIDATE KEY Uniquely identify each record in a table
option(A) : In the given Table, Last Name cannot be the Candidate key because as smith value are repeated.

option(B) : Room having duplicate values. So, we can't say that Room is Candidate key. Example 33 value is repeated

option(C) : Shift also having duplicate values as morning is repeated.

option(D) : "Room+shift" having no duplicate values. Every tuple is unique. we can uniquely identify any tuple in a table using Room+shift column

So, option(D) is candidate key
 Question 6
For a database relation R(a,b,c,d) where the domains of a, b, c and d include only atomic values, only the following functional dependencies and those that can be inferred from them hold
a → c
b → d

The relation is in
 A First normal form but not in second normal form B Second normal form but not in third normal form C Third normal form D None of the above
Question 6 Explanation:
Candidate Key for the above relation is ab.

Checking the FD's :
a→c  (Prime derives Non-Prime.)
b→d (Prime derives Non-Prime.)

Prime attribute { a,b}
non prime attribute {c,d}.

Since all a,b,c,d are atomic so the relation is in 1NF.

The relation will be in 2NF if every Non Prime attribute is fully functional dependent on KEY.

Here {c,d } are not fully functionally dependent, rather they are partially dependent so, it is not in 2NF.

Hence, this relation is in 1NF but not in 2NF
 Question 7
Consider the set of relations given below and the SQL query that follows

Students : (Roll number, Name, Date of birth)
Courses: (Course number, Course name, instructor)

SELECT DISTINCT Name
AND Courses.Instructor =Sriram

Which of the following sets is computed by the above query?
 A Names of Students who have got an A grade in all courses taught by Sriram B Names of Students who have got an A grade in all courses C Names of Students who have got an A grade in at least one of the courses taught by Sriram D None of the above
Question 7 Explanation:
Students : (Roll number, Name, Date of birth)
Courses: (Course number, Course name, instructor)

Distinct name is to be selected, where
1. Name of the student is selected on the basis of the grade.
2. Instructor of the course in Sriram
3. Courses selected on the basis of grade.

The Query results a names of the students who have got an A grade in at least one of the courses taught by Sriram.
 Question 8
Given relations R(w,x) and S(y,z), the result of
SELECT DISTINCT w, x
FROM R, S
Is guaranteed to be the same as R, if
 A R has no duplicates and S is non-empty B R and S have no duplicates C S has no duplicates and R is non-empty D R and S have the same number of tuples
Question 8 Explanation:

-The query selects all attributes of R. Since we have distinct in query, result can be equal to R only if R doesn’t have duplicates. If S is empty RXS becomes empty, so S must be non empty.

Option(A):R has no duplicates and S is non-empty
Incase If R has duplicates in that case due to DISTINC keyword in query will eliminate duplicates in FINAL result and the results in query !=R
So, R cannot have duplicate values
Incase Relation "S" is Empty then RXS becomes empty So, Relation "S" must be non-empty

Option(B): R and S have no duplicates
Assume Relation ''S' is Empty which has no duplicate values then RXS becomes empty So, Relation "S" must be non-empty

Option(C) : S has no duplicates and R is non-empty
S has no duplicate means we can relation "S" as Empty which is discussed in option(B)

Option(D) : R and S have the same number of tuples
Incase If R has duplicates in that case due to DISTINC keyword in query will eliminate duplicates in FINAL result So results in query !=R which is discussed in option(A)
 Question 9
Immunity of the external schemas (or application programs) to changes in the conceptual schema is referred to as:
 A Physical Data Independence B Logical Data Independence C Both (a) and (b) D None of the above
Question 9 Explanation:
Immunity is when data at one layer is changed, it does not affect the data at another level.
Physical data independence  :- if changes are made in the physical storage of schema then it will not affect the logical schema of the database.
Logical data independence :- if any changes are made in the conceptual schema then it will not affect external schema or the view level of the database.
 Question 10
The set of attributes X will be fully functionally dependent on the set of attributes Y if the following conditions are satisfied.
 A X is functionally dependent on Y B X is not functionally dependent on any subset of Y C Both (a) and (b) D None of these
Question 10 Explanation:
The term full functional dependency (FFD) is used to indicate the minimum set of attributes in of a functional dependency (FD). In other words, the set of attributes X will be functionally dependent on the set of attributes Y if the following conditions are satisfied:
• X is functionally dependent on Y and
• X is not functionally dependent on any subset of Y.
 Question 11
Let us assume that transaction T1 has arrived before transaction T2. Consider the schedule
S=r1(A); r2(B) ; w2(A); w1(B)
Which of the following is true?
 A Allowed under basic timestamp protocol. B Not allowed under basic timestamp protocols because T1 is rolled back C Not allowed under basic timestamp protocols because T2 is rolled back D None of these
Question 11 Explanation:
Not allowed under basic timestamp protocols because T1 is rolled back
 Question 12
Which commands are used to control access over objects in relational database?
 A CASCADE & MVD B GRANT & REVOKE C QUE & QUIST D None of these
Question 12 Explanation:
Data Control Language (DCL) is a subset of the SQL that allows database administrators to configure security access to relational databases.
It consists of only three commands: GRANT, REVOKE, and DENY.
GRANT  :It allow specified users to perform specified tasks.
REVOKE : Revoke to cancel previously granted or denied permissions.
 Question 13
Which of the following is aggregate function in SQL?
 A Avg B Select C Ordered by D Distinct
Question 13 Explanation:
Aggregate functions in SQL:
1) MIN
2) MAX
3) AVG
4) COUNT
5) SUM
Avg is an aggregate function returns the average of a group of elements.
Syntax:
SELECT AVG(column_name) FROM table_name;
 Question 14
A view of database that appears to an application program is known as
 A Schema B Subschema C Virtual table D None of these
Question 14 Explanation:
• Schema is the physical arrangement of the data as it appears in the DBMS.
• Sub-schema is the logical view of the data as it appears to the application program.
 Question 15
Which operation is used to extract specific columns from a table?
 A Project B Join C Extract D Substitute
Question 15 Explanation:
• To extract a specified row in a table select operation is used
• Project operator is denoted by ∏ symbol and it is used to extract specified columns (or attributes) from a table (or relation).
• BY default project operation removes duplicate data
 Question 16
BCNF is not used for cases where a relation has
 A Two (or more) candidate keys B Two candidate keys and composite C The candidate key overlap D Two mutually exclusive foreign keys
Question 16 Explanation:
• We say A relation is in Boyce–Codd Normal Form (BCNF) if all attributes which are determinants are also candidate keys in every relation.
• Transformation into Boyce–Codd Normal Form (BCNF) deals with the problem of overlapping keys and there is no problem with 2 or more Candidate keys .
 Question 17
Which of the following is correct with respect to Two phase commit protocol?
Question 17 Explanation:
 Question 18
What does the data dictionary identify?
 A Field names B Field Formats C Field Types D All of these
Question 18 Explanation:
• A data dictionary is a file or a set of files that contains a database's metadata. The data dictionary contains records about other objects in the database, such as data ownership, data relationships to other objects, and other data.
• The data dictionary is a crucial component of any relational database.
• Data dictionary will identify
-Field name
-Field format
-Field types
 Question 19
Which of the following concurrency control protocol ensures both conflict serializability and free from deadlock?
 A Timestamp ordering B 2 Phase Locking C Both (a) and (b) D None of the above
Question 19 Explanation:
Time stamp – ordering concurrency protocol ensures both conflict serializability and freedom from deadlock.
• In Basic 2 phase locking there is a chance for deadlock and starvation but ensure serializability
• Strict 2PL  ensure serializability and recoverability but deadlock and starvation possible
• Conservative 2PL ensure serializability and no deadlock but starvation possible
• Time stamp ordering  ensure serializability and no deadlock and avoid starvation by using wait-die and wound -wait scheme
 Question 20
ACID properties of a transactions are
 A Atomicity, consistency, isolation, database B Atomicity, consistency, isolation, durability C Atomicity, consistency, integrity, durability D Atomicity, consistency, integrity, database
Question 20 Explanation:
 Question 21
Database table by name overtime_allowance is given below
What is the output of the following SQL query?

`select count(*) from`
`((select Employee, Department from Overtime_allowance) as S`
`natural join`
`(select Department, OT_allowance from Overtime_allowance)``as T);`
 A 16 B 4 C 8 D None of the above
Question 21 Explanation:
Natural Join joins two tables based on same attribute name and datatypes. The resulting table will contain all the attributes of both the table but keep only one copy of each common column.

For example consider the below two tables :

Student Table
```Roll_No  Name
1       Arya
2       Bindu
3       Cherry```

Another table

Marks Table
```Roll_No   Marks
2         70
3         50
4         85```

Query
`SELECT *` `FROM Student S NATURAL JOIN Marks M;`

Output
```Roll_No    Name    Marks
2         Bindu   70
3         Cherry  50```

Coming to the question :

Given table
Overtime_allowance
 Employee Department OT_allowance RAMA Mechanical 5000 GOPI Electrical 2000 SINDHU Computer 4000 MAHESH Civil 1500

Given query :
`select count(*) from ` `((select Employee, Department from Overtime_allowance) as S` `natural join` ` (select Department, OT_allowance from Overtime_allowance) as T);`

Inner query will executed first
`(select Employee, Department from Overtime_allowance) as S` `natural join (select Department, OT_allowance from Overtime_allowance) as T)`
```Table S                          Table T
Employee    Department      Department     OT_allowance
Rama        Mechanical      Mechanical      5000
Gopi        Electrical      Electrical      2000
Sindhu      Computer        Computer        4000
Mahesh      Civil           Civil           1500```

Now when we apply natural join on S and T, it matches for the common attribute(Department) in both tables and outputs the common tuples.

 Department OT_allowance Mechanical 5000 Electrical 2000 Computer 4000 Civil 1500

Final query : outer query will be executed now

`select count(*) from ` `((select Employee, Department from Overtime_allowance) as S` `natural join` ` (select Department, OT_allowance from Overtime_allowance) as T);`

Output : 4
Natural join will return the table with 4 tuples.

Therefore, the correct option is (B)
 Question 22
Which symbol denote derived attributes in the ER Model?
 A Double ellipse B Dashed ellipse C Squared ellipse D An ellipse with attribute name underlined
Question 22 Explanation:
 Question 23
A B-Tree used as an index for a large database table has four levels including the root node. If a new key is inserted in this index, then the maximum number of nodes that could be newly created in the process are
 A 5 B 4 C 1 D 2
Question 23 Explanation:
-If all nodes are completely full then every node has n−1 keys.
-Insertion of new key is leading to insertion of new node at all 4 levels
-In worst case root node will also be broken into 2 parts.
-Maximum number of nodes that could be created are 5 because tree will be increased with 1 more level
 Question 24
The join operation can be defined as
 A A cartesian product of two relations followed by a selection B A cartesian product of two relations C A union of two relations followed by cartesian product of the two relations D A union of two relations
Question 24 Explanation:
• Join is a combination of a Cartesian product followed by a selection process.
• A Join statement is used to combine rows from 2 or more tables based on the selection of common field between them
• Different types of Joins are :
-RIGHT JOIN
-FULL JOIN
-INNER JOIN
-LEFT JOIN
 Question 25
Consider the schema Sailors(sid, sname, rating, age) with the following data
For the query

SELECT S.rating, AVG(S.age) AS average
FROM Sailors S
Where S.age >= 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT(*) FROM Sailors S2 where S.rating = S2.rating)

The number of rows returned is
 A 6 B 5 C 4 D 3
Question 25 Explanation:
Step 1 : Where S.age >= 18
All the tuples with age less than 18 will be eliminated.

Step 3 : GROUP BY S.rating
Group By the remaining tuples after eliminated in step1 will be grouped according to rating

After this, all the group-by tuples having their count more than 1 will be selected and their ratings and average age will be given as output.
 Question 26
Consider a table that describes the customers :
Customers(custid, name, gender, rating)
The rating value is an integer in the range 1 to 5 and only two values (male and female) are recorded for gender.
Consider the query “how many male customers have a rating of 5”?
The best indexing mechanism appropriate for the query is
 A Linear hashing B Extendible hashing C B+ Tree D Bit-mapped hashing
Question 26 Explanation:
• Best indexing mechanism appropriate for the given query is Bit-mapped index.
• To record gender(Male and Female) of customer we require only 2 bits 0 and 1
• we can implement it using Bit-mapped indexing.
 Question 27
The ‘command’ used to change contents of one database using the contents of another database by linking them on a common key field?
 A Replace B Join C Change D Update
Question 27 Explanation:
Replace : Replaces all occurrences of a specified string value with another string value

Join :
• Join is a combination of a Cartesian product followed by a selection process.
• A Join statement is used to combine rows from 2 or more tables based on the selection of common field between them
UPDATE: Update a record in table

Change : There is no change command in SQL
 Question 28
A locked database file can be
 A Accessed by only one user B Modified by users with the correct password C Used to hide sensitive information D Updated by more than one user
Question 28 Explanation:
File locking is a mechanism that restricts access to a computer file by allowing only one user or process access to the file at any specific time.
 Question 29
Which of the following contains complete record of all activity that affected the contents of a database during a certain period of time?
 A Transaction log B Query language C Report writer D Data manipulation language
Question 29 Explanation:
• A transaction log is a sequential record of all changes made to the database while the actual data is contained in a separate file.
• The transaction log contains enough information to undo all changes made to the data file as part of any individual transaction
 Question 30
Purpose of ‘Foreign Key’ in a table is to ensure
 A Null Integrity B Referential Integrity C Domain Integrity D Null and Domain Integrity
Question 30 Explanation:
• A FOREIGN KEY is a column or columns that references a column (most often the primary key) of another table
• The primary purpose of FOREIGN KEYs is to maintain Referential Integrity (RI).
 Question 31
Which of the following scenarios may lead to an irrecoverable error in a database system?
 A A transaction writes a data item after it is read by an uncommitted transaction B A transaction reads a data item after it is read by an uncommitted transaction C A transaction reads a data item after it is written bya committed transaction D A transaction reads a data item after it is written by an uncommitted transaction
Question 31 Explanation:
Option A can be recovered
Option B is also fine as no write operation is involved.
Option C is a normal operation.
Option D Reading uncommitted data is called as dirty read. If transaction reads uncommitted data and commits before the transaction that writes data item leads to non-recoverable schedule if and if transaction that writes data item rolled back
 Question 32
Consider the join of a relation R , with a relation S . If R has m number of tuples and S has n number of tuples then the maximum and minimum sizes of the join respectively are:
 A M + n & 0 B Mn & 0 C M + n & | m – n | D Mn & m + n
Question 32 Explanation:
 Question 33
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.
I. Insert into R
II. Insert into S
III. Delete from R
IV. Delete from S
Which of the following can cause a violation of the referential integrity constraint above?
 A Both I and IV B Both II and III C All of these D None of these
Question 33 Explanation:
"d" is the foreign key of S that refer to "a" primary key of R
• Insert into R will not cause any violation.
• Insert into S may cause violation if any value is inserted into d of S, which value is not in a of R.
• Delete from S will not cause any violation.
• Delete from R may cause violation because for the deleted entry in R there may be referenced entry in the relation S
 Question 34
The relation book ( title & price ) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list?
select title
from book as B
where (select count(*)
from book as T
where T.price>B.price)<5
 A Titles of the four most expensive books B Title of the fifth most inexpensive book C Title of the fifth most expensive book D Titles of the five most expensive books
Question 34 Explanation:
The outer query selects all titles from book table. For each title the inner query will be evaluated. For every selected book, the subquery will return the count of books which are more expensive than the selected book. T.price > B.price is evaluated for every B.price.
• For the 1st book (B.price = 65) Inner query gives count =6 (75,85,95,105,115,125).
• For the 2nd book (B.price = 75) Inner query gives count =5 (85,95,105,115,125).
• For the 3rd book (B.price = 85) Inner query gives count =4 (95,105,115,125).
• For the 4th book (B.price = 95) Inner query gives count =3 (105,115,125).
• For the 5th book (B.price = 105) Inner query gives count =2 (115,125).
• For the 6th book (B.price = 115) Inner query gives count =1 (125).
• For the 7th book (B.price = 125) Inner query gives count =0.
Hence the entire query will list out the title of a book when the count is less than 5. Hence as shown above from 3rd book onwards the titles will be listed. GHI, JKL, MNO, PQR, STU will be listed
Source : BtechOnline
 Question 35
Goals for the design of the logical scheme include
 A Avoiding data inconsistency B Being able to construct query easily C Being able to access data efficiently D All of the above
Question 35 Explanation:
A logical schema describes what data are stored in the database, and what relationships exist among those data. The logical level thus describes the entire database in terms of a small number of relatively simple structures.

Goals for the design of the logical scheme include
• Avoiding data inconsistency
• Being able to construct query easily
• Being able to access data efficiently
 Question 36
Given the relations

employee (name, salary, dept-no), and

Which of the following queries cannot be expressed using the basic relational algebra operations (σ, π, x, -, , p)
 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 36 Explanation:
 Question 37
Trigger is
 A Statement that enables to start any DBMS B Statement that is executed by the user when debugging an application program C The condition that the system tests for the validity of the database user D Statement that is executed automatically by the system as a side effect of a modification of the database
Question 37 Explanation:
Statement that is executed automatically by the system as a side effect of a modification of the database
A database Trigger is executed automatically in response to certain events on a particular table or view in a database.
 Question 38
The order of a leaf node in a B+ tree is the maximum number of (value, data record pointer) pairs it can hold. Given that the block size is 1K bytes, data record pointer is 7 bytes long, the value field is 9 bytes long and a block pointer is 6 bytes long, what is the order of the leaf node?
 A 63 B 64 C 67 D 68
Question 38 Explanation:
Given
Block size = 1K bytes = 1024 bytes
Data record pointer r = 7 bytes
Value field V= 9 bytes
Block pointer P= 6 bytes
order of the leaf node = ?
Let order of leaf = m
r*m + V*m + p <= 1024
7m + 9m + 6 <= 1024
16m <= 1024-6
16m <= 1018
m =< 63
 Question 39
A clustering index is defined on the fields which are of type
 A Non-key and ordering B Non-key and non-ordering C Key and ordering D Key and non-ordering
Question 39 Explanation:
Primary Index(Sparse)
- It is defined as an ordered data file.
- The data file is ordered on a key field.
- The key field is generally the primary key of the relation.

Secondary Index(Dense)
- This index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.

Clustering Index(Sparse)
- This index is defined on an ordered data file.
- The data file is ordered on a non-key field.
 Question 40
Which one of the following in place sorting algorithms needs the minimum number of swaps?
 A Quick sort B Insertion sort C Selection sort D Heap sort
Question 40 Explanation:
Selection Sort is an in-place algorithm having minimum number of swaps.
It works on greedy approach and takes O(n) swaps to sort the array of n elements

Number of swaps : Worst case scenario
• Quick sort = O(n2)
• Insertion sort = О(n2)
• Selection sort = O(n)
• Heap sort = O(n logn)
Number of swaps require in Selection Sort :
Best Case : No swaps required as all elements are properly arranged
Worst Case : n-1 swaps required
 Question 41
What is the equivalent serial schedule for the following transactions?
 A T1 − T2 − T3 B T3 − T1 − T2 C T2 − T1 − T3 D T1 − T3 − T2
Question 41 Explanation:

There are no Loop's So it is conflict serializable
And Serial schedule will is T3-T1-T2
 Question 42
Which type of DBMS provides support for maintaining several versions of the same entity?
 A Relational Database Management System B Hierarchical C Object Oriented Database Management System D Network
Question 42 Explanation:
• Object Oriented Systems(OODBMS) provide support for maintaining several versions of the same object.
• An old version of an object in OODBMS that represents a tested and verified design should be retained until the new version is tested and verified.
 Question 43
Which normal form is based on the concept of ‘full functional dependency’ is
 A First Normal Form B Second Normal Form C Third Normal Form D Third Normal Form
Question 43 Explanation:
Second normal form (2NF) is based on the concept of full functional dependency.

Functional Dependency:
The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B.

Example: EMP_EID → EMP_NAME
In this case, the attribute EMP_EID is known as the determinant attribute and the attribute EMP_NAME is known as the dependent attribute.
 Question 44
In functional dependency Armstrong inference rules refers to
 A Reflexive, Augmentation and Decomposition B Transitive, Augmentation and Reflexive C Augmentation, Transitive, Reflexive and Decomposition D Reflexive, Transitive and Decomposition
Question 44 Explanation:
Transitive, Augmentation and Reflexive
Armstrong's Axioms are most basic inference rules. These are 3 rules:
• Reflexivity: If Y is a subset of X, then X → Y
• Augmentation: If X → Y, then XZ → YZ
• Transitivity: If X → Y and Y → Z, then X → Z
Using the inference rule, we can derive additional functional dependency from the initial set.
• Union: If X → Y and X → Z, then X → YZ
• Decomposition: If X → YZ, then X → Y and X → Z
• Pseudo transitivity: If X → Y and WY → Z, then WX → Z
• Composition: If X → Y and Z → W, then XZ → YW
 Question 45
Which of the following is the highest isolation level in transaction management?
Question 45 Explanation:
• Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.
• Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it.
• Repeatable Read – This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read.
• Serializable – This is the Highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.
References : Wikipedia
 Question 46
Consider the following relational schema:
• Parts (pid:integer, pname:string, pcolor:string)
• Catalog (sid:integer, pid:integer, pcost:real)
What is the result of the following query?

( SELECT Catalog.pid
from Suppliers, Catalog
WHERE Suppliers.sid = Catalog.pid )
MINUS
( SELECT Catalog.pid
from Suppliers, Catalog
WHERE Suppliers.sname <> 'sachin'
and
Suppliers.sid = Catalog.sid)
 A Pid of Parts supplied by all except sachin B Pid of Parts supplied only by sachin C Pid of Parts available in catalog supplied by sachin D Pid of Parts available in catalogs supplied by all except sachin
Question 46 Explanation:
step 1:
(SELECT Catalog.pid from Suppliers, Catalog

WHERE Suppliers.sid = Catalog.pid)
We are getting pids for all which are supplied.

step 2:
(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sname <> 'sachin' and Suppliers.sid = Catalog.sid)
we are getting pids for all parts which are supplied by any other supplier other than Sachin.

step 3:
When we perform MINUS [All pids – pids(all others except sachin) = We get only sachin]

step 4:
We get the pids which are supplied by only Sachin

NOTE : MINUS operator will return only those rows which are unique in only first SELECT query and not those rows which are common to both first and second SELECT queries.
 Question 47
Consider the following dependencies and the BOOK table in a relational database design. Determine the normal form of the given relation.
ISBN → Title
ISBN → Publisher
 A First Normal Form B Second Normal Form C Third Normal Form D BCNF
Question 47 Explanation:
• Given relation is in 1NF because All attribute in the given functional dependencies are atomic values
• Candidate key = ISBN
• A relation is in 2NF if every non-prime attribute of the relation is dependent on the whole of every candidate key. So the given dependencies satisfies the 2NF
•  3NF : Address which is Transitively dependent on ISBN . Hence Transitive dependency exists .
• So the given relation is in 2NF and not in 3NF
 Question 48
Calculate the order of leaf(pleaf) and non leaf(p) nodes of a B+ tree based on the information given below Search key field = 12 bytes Record pointer = 10 bytes Block pointer = 8 bytes Block size = 1 KB
 A Pleaf = 51 & p = 46 B Pleaf= 47 & p = 52 C Pleaf= 46 & p = 50 D Pleaf = 52 & p = 47
Question 48 Explanation:
Search key field = 12 bytes
Record pointer = 10 bytes
Block pointer = 8 bytes
Block size = 1 KB
For Leaf node
Let order of leaf be 'n'
size of search key * n + record pointer * n + block pointer <=1024
12*n + 10*n+8 <=1024
12n + 10n+8 <=1024
22n+8 <=1024
22n <=1024-8
22n <=1016
n<=1016/22
n=46
For Non-Leaf Node
size of search key * n + block pointer * (n+1) <=1024
12*n + 8*n+8 <=1024
20n+8<=1024
20n <=1024-8
20n <=1016
n=50
∴order of leaf node(p) = 46
∴order of non-leaf node(pleaf) = 50
 Question 49
The physical location of a record determined by a formula that transforms a file key into a record location is
 A Hashed file B B-Tree file C Indexed file D Sequential file
Question 49 Explanation:
Correct option is Hashed File.

Hash File Organization is a file organization technique where a hash function is used to compute the address of a record. It uses the value of an attribute or set of attributes as input and gives the location (page/block/bucket) where the record can be stored.
 Question 50
Embedded pointer provides
 A A secondary access path B A physical record key C An inverted index D A prime key
Question 50 Explanation:
Embedded Pointer are Pointer Set in a data record and it provides a secondary access path
 Question 51
An aggregation, the association is drawn using which symbol?
 A A line which loops back on to the same table B A small open diamond at the end of a line connecting two tables C A small closed diamond at the end of a line connecting two tables D A small closed triangle at the end of a line connecting two tables
Question 51 Explanation:
Association is a relationship between two objects .It defines the multiplicity between objects like 1-1,1-M,M-1,M-M relations.
Aggregation is a special form of association
Composition is a special form of aggregation.
 Question 52
Consider the following table The table is in which normal form?
 A First Normal Form B Second Normal Form C Third Normal Form but not BCNF D Third Normal Form but BCNF
Question 52 Explanation:
From the given diagram we can write Functional Dependencies(Fd's) as
AB  -> CDE
C     -> B
Candidate keys = AB,AC
So, The given relation is in 3NF but not BCNF because "C" is not a super key
 Question 53
Consider the schema R(A,B,C,D) and the functional dependencies A→ B and C→ D. If the decomposition is made as R1(A,B) and R2(C,D), then which of the following is TRUE?
 A Preserves dependency but cannot perform lossless join B Preserves dependency and performs lossless join C Does not perform dependency and cannot perform lossless join D Does not preserve dependency but perform lossless join
Question 53 Explanation:
Dependency Preserving.
R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D) and there are only two FDs A -> B and C -> D. So, the decomposition is dependency preserving
• R1 (A, B) is covered A -> B
• R2(C, D) is covered C -> D
it is functional dependency preserving because both FD's are covered

Lossless-Join Decomposition:
Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)
R1 ∩ R2 → R1
OR
R1 ∩ R2 → R2
In the given relation R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D), and R1 ∩ R2 is empty. So, the decomposition is not lossless
 Question 54
Every time the attribute A appears, it is matched with the same value of attribute B but not the same value of attribute C. Which of the following is true?
 A A→ (B,C) B A → B, A→→ C C A→ B, C→→A D A→→B, B→ C
Question 54 Explanation:
represents Functional dependency
→→ represents multivalue dependency
A → B means that the values of B are determined by the value of A
A→→ C means for A ,C has more than one value.
Example for multivalue dependency
A C
1 5
1 6
we have multiple value of c but c does not have multiple values for a . so we cannot say c->>a but we can say a->>c. and question is saying c can take different value not a .
 Question 55
In RDBMS, which type of Join returns all rows that satisfy the join condition ?
 A Inner Join B Outer Join C Semi Join D Anti Join
Question 55 Explanation:
INNER JOIN-
The INNER JOIN selects all rows from both the tables as long as the condition satisfies. INNER JOIN will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.
 Question 56
Consider a relation book(title, price) which contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list ?
```SELECT title
FROM book AS B
WHERE( SELECT COUNT(*)
FROM book AS T
WHERE T.price > B.price ) < 7```
 A Titles of the six most expensive books. B Title of the sixth most expensive books. C Titles of the seven most expensive books. D Title of the seventh most expensive books.
Question 56 Explanation:
 Question 57
In a Hierarchical database, a hashing function is used to locate the
 A Collision B Root C Foreign Key D Records
Question 57 Explanation:
Hierarchical Model
• The data is organize in a tree structure where the nodes represent the records and the branches of the tree represent the fields.
• Since the data is organized in a tree structure, the parent node has the links to its child nodes.
• If we want to search a record, we have to traverse the tree from the root through all its parent nodes to reach the specific record. Thus, searching for a record is very time consuming.
• Hashing function is used to locate the root.
 Question 58
Relations produced from E-R Model will always be in
 A 1 NF B 2 NF C 3 NF D 4 NF
Question 58 Explanation:
Relations produced from an E-R model will always be in 3NF
 Question 59

Consider the following schedules involving two transactions.

```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 statements is correct with respect to above ?

 A Both S1 and S2 are conflict serializable. B Both S1 and S2 are not conflict serializable. C S1 is conflict serializable and S2 is not conflict serializable. D S1 is not conflict serializable and S2 is conflict serializable.
Question 59 Explanation:
 Question 60

For a database relation R(a, b, c, d) where the domains of a, b, c and d include only atomic values, and only the following functional dependencies and those that can be inferred from them hold :

• a → c
• b → d

The relation is in

 A First normal form but not in second normal form B Second normal form but not in third normal form C Third normal form D BCNF
Question 60 Explanation:
 Question 61

A many-to-one relationship exists between entity sets r1 and r2. How will it be represented using functional dependencies if Pk(r) denotes the primary key attribute of relation r?

 A Pk(r1) → Pk(r2) B Pk(r2) → Pk(r1) C Pk(r2) → Pk(r1) and Pk(r1) → Pk(r2) D Pk(r2) → Pk(r1) or Pk(r1) → Pk(r2)
Question 61 Explanation:
We have many-to-one relationship set between entity sets student and Department.
Let two relations be
R1(Student with pk SId)
R2(Department with pk DId)

NOTE :
SId can uniquely identify DId but DId can not uniquely identify the SId.
Thus,pk(R1) -> pk(R2)
Using Student ID(SID) we can unique identify Department(DID) but using DID we can find the student uniquely because one department have many students
 Question 62

Database systems that store each relation in a separate operating system file may use the operating system’s authorization scheme, instead of defining a special scheme themselves. In this case, which of the following is false ?

 A The administrator enjoys more control on the grant option. B It is difficult to differentiate among the update, delete and insert authorizations. C Cannot store more than one relation in a file. D Operations on the database are speeded up as the authorization procedure is carried out at the operating system level.
Question 62 Explanation:
Database systems have special requirements which are typically more refined than most operating systems. For example, a single user may have different privileges on different files throughout the system, including changing indices and attributes which file systems typically don’t monitor. The advantage of using the operating system’s security mechanism is that it simplifies the database system and can be used for simple (read/write) security measures.
 Question 63

Let R1(a,b,c) and R2(x,y,z) be two relations in which a is the foreign key of R1 that refers to the primary key of R2 . Consider following four options.

• (a) Insert into R1
• (b) Insert into R2
• (c) Delete from R1
• (d) Delete from R2

Which of the following is correct about the referential integrity constraint with respect to above ?

 A Operations (a) and (b) will cause violation. B Operations (b) and (c) will cause violation. C Operations (c) and (d) will cause violation. D Operations (d) and (a) will cause violation.
Question 63 Explanation:
Given
R1(a,b,c) and R2(x,y,z)

Let us say "a" is the foreign key of R1 that refers to "x" primary key of R2
• Insert into R1 operation will take place there will be inconsistency in the database, since it has a foreign key which refers to the primary key of R2
• Insert into R2 Can not cause violation
• Delete from R1 Can not cause violation.
• Delete from R2 Can cause violation if we delete any value of x in R2, then the value referred by a in R1 should also be deleted.
 Question 64
Maximum number of superkeys for the relation schema R(X,Y,Z,W) with X as the key is:
 A 6 B 8 C 9 D 12
Question 64 Explanation:
Maximum number of Superkeys possible for a table with n attributes = 2(n-1)
Given n = 4.
So, Possible Superkeys = 24-1 = 8
Possible Superkeys = X, XW, XZ, XY, XZW, XYW, XYZ, XYZW
 Question 65
Identify the true statement from the given statements.
(1) Lossless, dependency preserving decomposition into 3NF is always possible
(2) Any relation with two attributes is in BCNF
 A (1) B (2) C (1) and (2) D None of these
Question 65 Explanation:
• Lossless, dependency-preserving decomposition into 3NF is always possible - TRUE
IN 3NF it is possible to satisfy both lossless and dependency preserving. i.e., for any relation there always exist a decomposition to 3NF, which is guaranteed to satisfy both the given properties.
• Any relation with two attributes is in BCNF. - TRUE
 Question 66
Identify the true statement from the given statements
(1) Number of child pointers in a B/ B+ tree node is always equal to number of keys in it plus one.
(2) B/B+ tree is defined by a term minimum depends on hard disk block size, key and address sizes.
 A (1) B (1) and (2) C (2) D None of these
Question 66 Explanation:
• TRUE - Number of child pointers in a B/B+ tree node is always equal to number of keys in it plus one
• TRUE - B/B+ tree is defined by a term minimum depends on hard disk block size, key and address sizes.
 Question 67
The following table has two attributes X and Y where X is the primary key and Y is the foreign key referencing X with on delete cascade

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (3,4) is deleted is:
 A (4,3) and (6,4) B (2,4) and (7,2) C (3,2) and (9,5) D (3,4),(4,3) and (6,4)
Question 67 Explanation:
• Given X is a primary key and Y is foreign key on deleting (3,4), all the foreign key occurrences of 3 will also be deleted (4,3).
• Now 4 is also deleted which was also primary key in (4,3), its corresponding foreign key occurrences will also be deleted (2,4) and (6,4).
• NOW 2 and 6 are also deleted, so their foreign key occurrences also deleted (5,2),(7,2).
• Similarly 5 and 7 foreign key occurrences will also be deleted (9,5).
 Question 68
_____ is NOT a part of the ACID properties.
 A Inconsistency B Consistency C Atomicity D Isolation
Question 68 Explanation:
 Question 69
Consider the following functional dependencies in a database:
A → B
B → C
D → E
E → D
F → G
F → H
(E,F) → I
The relation (E,D,A,B) is :
 A 2 NF B 3 NF C BCNF D None of the above
Question 69 Explanation:
Given
Functional Dependencies
A→B
B→C
D→E
E→D
F→G
F→H
(E,F)→l

(AE)+ ={A,B,D,E}
So (AD) & (AE) are candidate keys for this relation R(E,D,A,B)
Given relation is in 1NF because it contains an atomic values.
There exists PARTIAL DEPENDENCY
A -> B
D -> E
E -> D
So the relation not in 2NF
 Question 70
An object can have which of the following multiplicities?
 A Zero B More than one C One D All of the above
Question 70 Explanation:
• Multiplicity defines how many objects participate in a relationship and it is the number of instances of one class related to one instance of the other class.
• For each association and aggregation, there are two multiplicity decisions to make, one for each end of the relationship.
• Multiplicity is represented as a number and a * is used to represent a multiplicity of many.
 Question 71
Normalization from which is based on transitive dependency is classified as:
 A First normal form B Second normal form C Fourth normal form D Third normal form
Question 71 Explanation:
For 3NF every non‐prime attribute of table must be dependent on primary key. The transitive functional dependency should be removed from the table. The table must be in second normal form 2NF.

Transitive Functional
• When an indirect relationship causes functional dependency it is called Transitive Dependency.
• If P -> Q and Q -> R is true, then P-> R is a transitive dependence
• A transitive dependency can only occur in a relation of three of more attributes
 Question 72
Which of the following is a fundamental operation in relational algebra?
 A Set intersection B Assignment C Natural Join D None of the above
Question 72 Explanation:
The fundamental operations in relation algebra are
• Select
• Project
• Union
• Set difference,
• Rename
• Cartesian product
 Question 73
The primary key is selected from the:
 A Composite keys B Determinants C Candidate keys D Foreign keys
Question 73 Explanation:
• CANDIDATE KEY is a set of attributes that uniquely identify tuples(rows) in a table.
• Candidate Key is a super key with no repeated attributes
• Each Table have one or more candidate keys
• Primary key should be selected from the candidate keys.
• Every table must have at least a single candidate key.
• Each table can have multiple candidate keys but only a single primary key
 Question 74
In functional dependency between two sets of attribute A and B then set of attributes A of database is classified as:
 A Top right side B Down left side C Left hand side D Right hand side
Question 74 Explanation:
In functional dependency between two sets of attribute A and B denoted as
A →B
A is determinant, B is dependent
Dependent It is displayed on the right side of the functional dependency diagram.
Determinant It is displayed on the left side of the functional dependency diagram.
 Question 75
Which type of statement can execute parameterized queries?
 A PreparedStatement B Parameterized Statement C ParameterizedStatement and CallableStatement D All kinds of Statements
Question 75 Explanation:
Prepared Statement
• PreparedStatement interface extends the Statement interface.
• It represents a precompiled SQL statement which can be executed multiple times.
• This accepts parameterized SQL quires and you can pass 0 or more parameters to this query.
• Initially, this statement uses place holders “?” instead of parameters, later on, you can pass arguments to these dynamically using the setXXX() methods of the PreparedStatement interface.
Source : Tutorialspoint
 Question 76
Which of the following is a fundamental operation in relational algebra?
 A Set intersection B Natural Join C Assignment D None of the above
Question 76 Explanation:
Following are the fundamental operations in relation algebra
• Select
• Project
• Union
• Set difference
• Rename
• Cartesian product
 Question 77
Which one of the following statements about normal forms is FALSE?
 A BCNF is stricter than 3NF B Lossless,dependency preserving decomposition into BCNF is always possible C Lossless,dependency preserving decomposition into 3NF is always possible D Any relation with two attributes is BCNF
Question 77 Explanation:
• True : BCNF is stricter than 3NF
• False Lossless, dependency-preserving decomposition into BCNF is always possible
It is not always possible to decompose a table in BCNF and preserve dependencies.
• True Lossless, dependency-preserving decomposition into 3NF is always possible
• True Any relation with two attributes is in BCNF
 Question 78
Every Boyce-Codd Normal Form(BCNF) decomposition is
 A Dependency preserving B Not dependency preserving C Need be dependency preserving D None of these
Question 78 Explanation:
BCNF decomposition can always satisfies lossless, but it may not be always possible to get a dependency preserving BCNF decomposition.

Let take an example Relation R (V, W, X, Y, Z), with functional dependencies
• V,W -> X
• Y,Z -> X
• W -> Y
It may not be possible to get a dependency preserving BCNF decomposition.

Note : Redundancies are sometimes still present in a BCNF relation as it is not always possible to eliminate them completely.
 Question 79
A functional dependency of the form x → y is trivial if
 A Y ⊆ x B Y ⊂ x C X ⊆ y D X ⊂ y and y ⊂ x
Question 79 Explanation:
-A functional dependency X → Y is said to be trivial if and only if Y ⊆ X.
-If RHS of a functional dependency is a subset of LHS, then it is called as a trivial functional dependency.

Let us take an examples of trivial functional dependencies are
• XY  →  X
• XY  →  Y
• XY  →  XY
 Question 80
A primary key, if combined with a foreign key creates
 A Parent child relationship between the tables that connect them B Many-to-many relationship between the tables that connect them C Network model between the tables connect them D None of these
Question 80 Explanation:
Using the two relationships mother and father provides us a record of a child’s mother, even if we are not aware of the father’s identity; a null value would be required if the ternary relationship parent is used. Using binary relationship sets is preferable in this case.
 Question 81
Consider the schema R=(S,T,U,V) and the dependencies S→ T, T→ U, U→ V and V→ S. If R=(R1 and R2) be a decomposition such that R1 ∩R2=​ ∅ ​ , then decomposition is
 A Not in 2 NF B In 2 NF but not in 3 NF C In 3 NF not in 2 NF D In both 2NF and 3 NF
Question 81 Explanation:
Given dependencies
S→T
T→U
U→V
V→S.

Candidate keys are S,T,U and V.
All attributes are Candidate keys
All attributes are Prime Attributes So, given relation R is 3NF and BCNF

R1(S,T) { S→T ,T →S} ∩ R2(U,V){ U → V , V → U} = Ø
R1∩R2 = Ø means there is no common attribute in R1 and R2. This makes the decomposition lossy
Decomposition in BCNF but dependency not preserved.
 Question 82
Data warehouse contains___________ data that is never found in operational environment.
 A Scripted B Encoded C Encrypted D Summary
Question 82 Explanation:
 Question 83

Consider the following tables (relations):

Primary keys in the tables are shown using Underline. Now, Consider the following query:

```SELECT S.Name, Sum (P.Marks)
FROM Students S, Performance P
WHERE S.Roll-No = P.Roll-No
GROUP BY S.Name```
The number of rows returned by the above query is
 A 3 B 2 C 0 D 1
Question 83 Explanation:
 Question 84
Data Scrubbing is
 A A process to upgrade the quality of data after it is moved into a data warehouse B A process to upgrade the quality of data before it is moved into a data warehouse C A process to lead the data in the warehouse and to create the necessary indexes D A process to reject data from the data warehouse and to create necessary indexes
Question 84 Explanation:
• Data scrubbing is also called  as data cleansing it is the process of amending or removing data in a database that is incorrect, incomplete, improperly formatted, or duplicated.
• By using data scrubbing tool can save a database administrator a significant amount of time and can be less costly than fixing errors manually.
 Question 85

Consider the schema R = (A, B, C, D, E, F) on which the following functional dependencies hold :

```A ➝ B
B,C ➝ D
E ➝ C
D ➝ A```
What are the candidate keys of R ?
 A AEF, BEF and DEF B AEF, BEF and BCF C AE and BE D AE, BE and DE
Question 85 Explanation:
Find the closure set of all the options given. If any closure includes all attributes of a table then it becomes the candidate key.

Algorithm to find Closure Set
• Step1: Equate an attribute or attributes to X for which closure needs to be identified.
• Step2: Take each FD (functional dependency) one by one and check whether the left side of FD is available in X, if yes then add the right side attributes to X if it is not available.
• Step3: Repeat step 2 as many times as possible to cover all FD's.
• Step4: After no more attributes can be added to X declare it as the closure set.
Closure of AEF, i.e. AEF+ = {ABCDEF}
Closure of BEF, i.e. BEF+ = {ABCDEF}
Closure of DEF, i.e. DEF+ = {ABCDEF}

So AEF, BEF and DEF are candidate keys So, option(A) is correct.
 Question 86

Consider the following sequence of two transactions on a bank account(A) with initial balance 20,000 that transfers 5,000 to another account (B) and then apply 10% interest.

(i)   T1 start
(ii)  T1 A old=20000 new 15,000
(iii) T1 B old=12000 new=17000
(iv)  T1 commit
(v)   T2 start
(vi)  T2 A old=15000 new=16500
(vii) T2 commit

Suppose the database system crashes out just before log record (vii) is written. When the system is restricted, which one statement is true of the recovery process ?

 A We can apply redo and undo operation in arbitrary order because they are idempotent. B We must redo log record (vi) to set A to 16,500. C We must undo log record (vi) to set A to 16,500 and then redo log record (ii) and (iii). D We need not redo records (ii) and (iii) because transaction T1 has committed.
Question 86 Explanation:
Answer should be option(c ) Here we are not using any checkpoints. So, We must undo log record (vi) to set A to 16,500 and then redo log records (ii) and (iii) because system fail before commit operation. So we need to undone active transactions(T2) and redo committed transactions (T1)

Checkpoint : Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed.

Recovery : When a system with concurrent transactions crashes and recovers, it behaves in the following manner?
⇒ The recovery system reads the logs backwards from the end to the last checkpoint.
⇒ It maintains two lists, an undo-list and a redo-list.
⇒ If the recovery system sees a log with and or just, it puts the transaction in the redo-list.
⇒ If the recovery system sees a log with but no commits or abort log found, it puts the transaction in undo-list.
All the transactions in the undo-list are then undone and their logs are removed. All the transactions in the redo-list and their previous logs are removed and then redone before saving their logs.
 Question 87
The clustering index is defined on the fields which are of type
 A Key and ordering B Key and non-ordering C Non-key and ordering D Non-key and non-ordering
Question 87 Explanation:
Primary Index(Sparse)
- It is defined as an ordered data file.
- The data file is ordered on a key field.
- The key field is generally the primary key of the relation.

Secondary Index(Dense)
- This index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.

Clustering Index(Sparse)
- This index is defined on an ordered data file.
- The data file is ordered on a non-key field.
 Question 88
________ command is used to remove a relation from an SQL database.
 A Update table B Remove table C Delete table D Drop table
Question 88 Explanation:
• UPDATE a DML command is used to update the data or row in the table
• DELETE is a DML command and is used to remove tuples/records from a relation/table.
• DROP is a DDL command and is used to remove the whole structure of the relation. we can delete a table/relation
 Question 89
Which one of the following is a key factor for preferring B+-trees to binary search trees for indexing database relations?
 A Database relations have a large number of records B Database relations are sorted on the primary key C B+-trees require less memory than binary search trees D Data transfer from disks is in blocks
Question 89 Explanation:
• B+ tree is a balanced Tree and it can store multiple keys in each node of a B+ tree so that tree height is small
• Where BST where each node contains only one key, So height of BST would be more than height of B+ tree
• Thus, disk can transfer data in blocks when B+ tree is used for indexing database relations.
 Question 90
Consider the following four schedules due to three transactions (indicated by the subscript) using read and write on a data item x, denoted by r(x) and w(x) respectively. Which one of them is conflict serializable.
1: r1(X); r2(X); w1(X); r3(X); w2(X)
2: r2(X); r1(X); w2(X); r3(X); w1(X)
3: r3(X); r2(X); r1(X); w2(X); w1(X)
4: r2(X); w2(X); r3(X); r1(X); w1(X)
 A 1 B 2 C 3 D 4
Question 90 Explanation:
First we need to Draw precedence graph for all the options and for conflict serializability graph must not contain cycle.
option (D) is the only graph will be acyclic remaining all are cyclic
 Question 91
In a database system, the domain integrity is not defined by:
 A The data type and the length B The NULL value rejection C The allowable values, through techniques like constraints or rules D Default value
Question 91 Explanation:
Domain Level Integrity
A domain defines the possible values of an attribute. Domain Integrity rules govern these values. In a database system, the domain integrity is defined by:
• The datatype and the length
• The NULL value acceptance
• The allowable values, through techniques like constraints or rules
• The default value
 Question 92
To maintain transactional integrity and database consistency DBMS will use:
 A Triggers B Cursors C Locks D Pointers
Question 92 Explanation:
Locks are used In-order to maintain transactional integrity and database consistency.
 Question 93
Consider a B+ tree in which the maximum number of child nodes is 6. What is the minimum and maximum number of keys in such a tree?
 A 3 and 4 B 2 and 5 C 2 and 4 D 3 and 5
Question 93 Explanation:
Maximum number of children node is 6
Maximum number of keys can have is 5
Order is Key(5)+1=6
Minimum number of children that a node can have is ceil(6/2) = 3.
Therefore, Minimum number of keys that a node can have becomes 2 i.e (3-1)
 Question 94
The primary key of any table is selected from the:
 A Composite keys B Foreign keys C Candidate keys D Alternate keys
Question 94 Explanation:
• CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. A super key with no redundant attribute is known as candidate key
• PRIMARY KEY should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.
 Question 95
A lock which required is not acquired by database engine is:
 A Row lock B Page lock C Table lock D Attribute lock
Question 95 Explanation:
 Question 96
The name of parser generator that is used for SQL query parsing is:
 A Lexer parser generator B Syntactic parser generator C Tokenizer parser generator D SQL DML parser generator
Question 96 Explanation:
• SQL Parser parses a SQL query in a string field.
• When parsing a query the processor generates fields based on the fields defined in the SQL query and specifies the CRUD operation, table, and schema information in record header attributes.
• SQL DML Parser generator is used for SQL query parsing
• In addition to syntactic validation the parser can also perform a semantic validation
 Question 97
Bounded minimization is a technique for
 A Proving whether a promotivate recursive function is turing computable or not B Proving whether a primitive recursive function is a total function or not C Generating primitive recursive functions D Generating partial recursive functions
Question 97 Explanation:
One useful way of generating more primitive recursive functions from existing ones is through what is known as bounded summation and bounded product
Reference : https://planetmath.org/boundedminimization
 Question 98
Global locks
Question 98 Explanation:
• Holding a lock is how one thread tells other threads: “I am updating this thing, So don’t touch it right now.”
 Question 99
In a relational schema, each tuple is divided into fields called
 A Relations B Domains C Queries D None of these
Question 99 Explanation:
• In a relational schema, each tuple is divided into fields called Domains .
•  In a relational model, each attribute carries a domain of its own.
• A domain refers to the set of unique values used to define that attribute and will act as a "model" set of values.
• These values, being unique to that attribute are referred to as "atomic values".
•  A domain is a set of acceptable values that a column is allowed to store
• The rule for determining the domain boundary may be as simple as a data type with an enumerated list of values.
 Question 100
The employee salary should not be greater than Rs.2000. This is
 A Integrity constraint B Referential constraint C Over-defined constraint D Feasible constraint
Question 100 Explanation:
 Question 101
The relational algebra expression equivalent to the tuple calculus expression
{ t | t ​ ∈ ​ r ⋀ ( t[A]=10 ⋀ t[B]=20 ) } is
 A Σ​ (A=10 v B=20) (r) B Σ​ (A=10)​ (r)​ Uσ ( B=20) (r) C Σ​ (A=10)​ (r)​ ∩ σ​ ( B=20) (r) D Σ​ (A=10)​ (r)​ - ​ σ​ ( B=20) (r)
Question 101 Explanation:
In Given relational algebra, Tuple t should have 2 attributes A and B such that t.A=10 and t.B=20.

option(A) : Incorrect
Select tuples having A=10 or B=20

option(B) : Incorrect
Select tuples having A=10 or we can select tuples having B=20

option(C) : Correct
Select tuples having A=10 and tuples having B=20
(Tuples having A=10)∩( Tuples having B=20) Which is equal to (Tuples having A=10 and B=20).
 Question 102
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 for R?
 A CD B EC C AE D AC
Question 102 Explanation:
Find the closure set of all the options given. If any closure covers all the attributes of the relation R then that is the key.
{CD}+={CDF}
{EC}+={ABCDEF}
{AE}+={ABE}
{AC}+={ABCF}
EC is the key So, Option(B) is Correct
 Question 103
For a database relation R(a,b,c,d) where the domains of 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
the relation is in
 A First normal form but not in second normal form B Second normal form but not in third normal form C Third normal form D None of these
Question 103 Explanation:
 Question 104
A program P reads and processes 1000 consecutive records from a sequential file F stored on device D without using any file system facilities. Given the following:

(i) Size of each record = 3200 bytes.
(ii) Access time of D = 10 m secs.
(iii) Data transfer rate of D = 800 x 103 bytes/sec
(iv) CPU time to process each record = 3 m secs.

What is the elapsed time of P if records of F are organized using a blocking factor of 2(i.e. each block on D contains two records of F) and P uses one buffer?
 A 12sec B 14sec C 17sec D 21sec
Question 104 Explanation:
In this case 500 accesses are sufficient to transfer 1000 records.
∴ Elapsed time=(Time taken to transfer Z records in one access +Processing time of two records) x 500
= [(10 + 4 + 4) + 3 + 3] x 500m sec
= [(18) + 6] x 500m sec
= 12 sec.
 Question 105
The data manipulation language(DML)
 A Refers to data using physical addresses B Cannot interface with high-level programming language C Is used to define the physical characteristics of each record D None of these
Question 105 Explanation:
These SQL commands are mainly categorized into 4 categories as:
• DDL – Data Definition Language
• DQl – Data Query Language
• DML – Data Manipulation Language
• DCL – Data Control Language
 Question 106
A tuple in relation DBMS is a equivalent to
 A Record B Field C File D Database
Question 106 Explanation:
Remember Names
• Relation or Table
• Tuple or Row or Record
• Attribute or column or filed
• Degree means Number of columns
• Cardinality means Number of rows
• Domain means data type(legal values)
 Question 107
A B-tree of order 4 is built from scratch by 10 successive insertions. What is the maximum number of node splitting operations that may take place?
 A 3 B 4 C 5 D 6
Question 107 Explanation:
option(C) is correct Total 5 splits will occur
Let take an example of inserting 10 keys from 1 to 10 which cause maximum split
Insertion of 3 keys
1 2 3
Insertion of 4th key 1st split will occur
 Question 108
​Data warehouse contains___________ data that is never found in operational environment.
 A Scripted B Encoded C Encrypted D Summary
Question 108 Explanation:
 Question 109

Consider the following tables (relations):

Primary keys in the tables are shown using Underline. Now, Consider the following query:

```SELECT S.Name, Sum (P.Marks)
FROM Students S, Performance P
WHERE S.Roll-No = P.Roll-No
GROUP BY S.Name```
The number of rows returned by the above query is
 A 3 B 2 C 0 D 1
Question 109 Explanation:
 Question 110
Data Scrubbing is
 A A process to upgrade the quality of data after it is moved into a data warehouse B A process to upgrade the quality of data before it is moved into a data warehouse C A process to lead the data in the warehouse and to create the necessary indexes. D A process to reject data from the data warehouse and to create necessary indexes.
Question 110 Explanation:
• Data scrubbing is also called  as data cleansing it is the process of amending or removing data in a database that is incorrect, incomplete, improperly formatted, or duplicated.
• By using data scrubbing tool can save a database administrator a significant amount of time and can be less costly than fixing errors manually.
 Question 111
Consider the schema R=(A, B, C, D, E, F) on which the following functional dependencies hold :
A➝B
B,C➝D
E➝C
D➝A
What are the candidate keys of R ?
 A AEF, BEF and DEF B AEF, BEF and BCF C AE and BE D AE, BE and DE
Question 111 Explanation:
Find the closure set of all the options given. If any closure includes all attributes of a table then it becomes the candidate key.

Algorithm to find Closure Set
• Step1: Equate an attribute or attributes to X for which closure needs to be identified.
• Step2: Take each FD (functional dependency) one by one and check whether the left side of FD is available in X, if yes then add the right side attributes to X if it is not available.
• Step3: Repeat step 2 as many times as possible to cover all FD's.
• Step4: After no more attributes can be added to X declare it as the closure set.
Closure of AEF, i.e. AEF+ = {ABCDEF}
Closure of BEF, i.e. BEF+ = {ABCDEF}
Closure of DEF, i.e. DEF+ = {ABCDEF}

So AEF, BEF and DEF are candidate keys So, option(A) is correct.
 Question 112
Consider the following sequence of two transactions on a bank account(A) with initial balance 20,000 that transfers 5,000 to another account (B) and then apply 10% interest.
(i) T1 start
(ii) T1 A old=20000 new 15,000
(iii) T1 B old=12000 new=17000
(iv) T1 commit
(v) T2 start
(vi) T2 A old=15000 new=16500
(vii) T2 commit
Suppose the database system crashes out just before log record (vii) is written. When the system is restricted, which one statement is true of the recovery process ?
 A We can apply redo and undo operation in arbitrary order because they are idempotent. B We must redo log record (vi) to set A to 16,500. C We must undo log record (vi) to set A to 16,500 and then redo log record (ii) and (iii). D We need not redo records (ii) and (iii) because transaction T1 has committed.
Question 112 Explanation:
Answer should be option(c ) Here we are not using any checkpoints. So, We must undo log record (vi) to set A to 16,500 and then redo log records (ii) and (iii) because system fail before commit operation. So we need to undone active transactions(T2) and redo committed transactions (T1)

Checkpoint : Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed.

Recovery : When a system with concurrent transactions crashes and recovers, it behaves in the following manner?
⇒ The recovery system reads the logs backwards from the end to the last checkpoint.
⇒ It maintains two lists, an undo-list and a redo-list.
⇒ If the recovery system sees a log with and or just, it puts the transaction in the redo-list.
⇒ If the recovery system sees a log with but no commits or abort log found, it puts the transaction in undo-list.
All the transactions in the undo-list are then undone and their logs are removed. All the transactions in the redo-list and their previous logs are removed and then redone before saving their logs.
 Question 113
The clustering index is defined on the fields which are of type
 A Key and ordering B Key and non-ordering C Non-key and ordering D Non-key and non-ordering
Question 113 Explanation:
Primary Index(Sparse)
- It is defined as an ordered data file.
- The data file is ordered on a key field.
- The key field is generally the primary key of the relation.

Secondary Index(Dense)
- This index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.

Clustering Index(Sparse)
- This index is defined on an ordered data file.
- The data file is ordered on a non-key field.
 Question 114
​ ________ command is used to remove a relation from an SQL database.
 A Update table B Remove table C Delete table D Drop table
Question 114 Explanation:
• UPDATE a DML command is used to update the data or row in the table
• DELETE is a DML command and is used to remove tuples/records from a relation/table.
• DROP is a DDL command and is used to remove the whole structure of the relation. we can delete a table/relation
 Question 115
Process of analyzing relation schemas to achieve minimal redundancy and insertion or update anomalies is classified as:
 A Normalized of data B Denomination of data C Isolation of data D Denormalized of data
Question 115 Explanation:
Process of analyzing relation schemas to achieve minimal redundancy and insertion or update anomalies is classified as normalization of data

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.

Following are the most commonly used normal forms:
• First normal form(1NF)
• Second normal form(2NF)
• Third normal form(3NF)
• Boyce & Codd normal form (BCNF)
 Question 116
Rule which states that addition of same attributes to right side and left side will result in other valid dependency is classified as:
 A Referential rule B Inferential rule C Augmentation rule D Reflexive rule
Question 116 Explanation:
 Question 117
If every functional dependency in set E is also in closure of F then this is classified as:
 A FD is covered by E B E is covered by F C F is covered by E D F plus is covered by E
Question 117 Explanation:
• If every functional dependency in set E is also in closure of F means F+ ⊆ E+
• E cover F if every FD in F can be referred from E.
• E cover F if F+ ⊆ E+ (+ means closure)
• Every set of functional dependencies has a canonical cover
 Question 118
Considering relational database, functional dependency between two attributes A and B is denoted by:
 A A--> B B B<-- A C AB--> R D R<-- AB
Question 118 Explanation:
• Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value in R is associated with precisely one Y value in R; R is then said to satisfy the functional dependency X → Y.
• A functional dependency is denoted by an arrow "→". The functional dependency of X on Y is represented by X → Y.
• X → Y means X attribute we can derive attribute Y.
• The left side of FD is known as a determinant, the right side of the production is known as a dependent
 Question 119
If there is more than one key for relation schema in DBMS then each key in relation schema is classified as:
 A Primary key B Super key C Candidate key D Primary key
Question 119 Explanation:
• A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data.
• Candidate Key is a super key with no repeated attributes
• Each Table have one or more candidate keys
• Primary key should be selected from the candidate keys.
• Every table must have at least a single candidate key.
• Each table can have multiple candidate keys but only a single primary key
• Each table may have one or more candidate keys, but one candidate key is unique, and it is called the primary key.
• A candidate key is the minimal subset of a super key.
• Candidate key of having "no redundant attributes" and being a "minimal representation of a tuple" in a relational database table
 Question 120
In tuple relational calculus P1 → P2 is equivalent to
 A ~P1 V P2 B P1 V p2 C P1 ⋀ P2 D P1 ⋀ ~P2
Question 120 Explanation:
In tuple relational calculus P1 → P2 is equivalent to ~P1 V P2
 Question 121
If R is a relation in relational data Model and A1,A2,..An are the attributes of relation R, what is the cardinality of R expressed in terms of domain of attributes?
 A |R|<=|dom(A1)X dom(A2)..dom(An)| B |R|>=|dom(A1)X dom(A2)..dom(An)| C |R|=max(|dom(A1)|,|dom(A2)|,..|dom(An)|) D |R|=min(|dom(A1)|,|dom(A2)|,..|dom(An)|)
Question 121 Explanation:
• The term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column.
• In the context of databases, cardinality refers to the uniqueness of data values contained in a column.
• Cardinality refers to a number. It gives the number of unique values that appear in the table for a particular column.
• cardinality of relation= multiplication of domain of each attribute
• ∣R∣ ≤ ∣ dom(A1)×dom(A2)…dom​​​​​​​(An) ∣ For eg: you have a table called Person with column Gender. Gender column can have values either 'Male' or 'Female".
• Then the cardinality of Gender column is 2, since there are only two unique values that could possibly appear in that column – Male and Female.
Reference : https://en.wikipedia.org/wiki/Cardinality
 Question 122
If an SQl query involves NOT,AND,OR with no parenthesis
 A NOT will be evaluated first; AND will be evaluated second;OR willbe evaluated last B NOT will be evaluated first; OR will be evaluated second;AND willbe evaluated last C AND will be evaluated first; OR will be evaluated second;NOT willbe evaluated last D The order of occurrence determines the order of evaluation
Question 122 Explanation:
We can use parentheses to override the rules of precedence
 Question 123
'AS' clause is used in SQL for
 A Selection operation B Rename operation C Join Operation D Projection Operation
Question 123 Explanation:
"AS command" in SQL is used to rename a column or table with an alias name.
An alias only exists for the duration of the query.
Example
SELECT StudentID AS ID, StudentName AS Student
FROM Student;
 Question 124
Related fields in a database are grouped to form a
 A Data file B Data record C Menu D Bank
Question 124 Explanation:
• Relation sometime called as Table
• Fields sometime called as columns or Attributes
• Rows sometime called as Records or Tuples
In a database, Related fields are grouped within a table to form a record
 Question 125
A table joined with itself is called
 A Join B Self join C Outer join D Equi join
Question 125 Explanation:
• A table joined with itself is called self join
• Table must contain a column (call it X) that acts as the primary key and a different column (call it Y) that stores values that can be matched up with the values in Column X
 Question 126
Consider the join of a relation R with relation S. If R has m tuples and S has n tuples, then the maximum size of join is
 A Mn B M+n C (m+n)/2 D 2(m+n)
Question 126 Explanation:
 Question 127
Consider the following Relationship Entity Diagram(ERD) Which of the following possible relations will not hold if the above ERD is mapped into a relation model?
 A Person (NID, Name) B Qualification (NID, ExamID, QualifiedDate) C Exam (ExamID, NID, ExamName) D Exam (ExamID, ExamName)
Question 127 Explanation:
Create one table for each entity while converting an ER diagram into relation model
So we got 2 tables i.e.
One for Exam(ExamID, ExamName)
Another for Person(NID, Name)

Now problem is with relationship Entity Qualification, on which side we can add it, unfortunately we cannot add QualifiedDate in any of the two tables formed so we need to create a new table using Primary Key from both Entity Person and Entity Exam.

We obtain Qualification(NID, ExamID, QualifiedDate)
 Question 128
Consider the following log sequence of two transactions on a bank account, with initial balance 12000, that transfer 2000 to a mortgage payment and then apply a 5% interest.
1. T1 start
2. T1 B old=12000 new=10000
3. T1 M old=0 new=2000
4. T1 commit
5. T2 start
6. T2 B old=10000 new=10500
7. T2 commit
Suppose the database system crashes just before log record 7 is written. When the system is restarted, which one statement is true of the recovery procedure?
 A We must redo log record 6 to set B to 10500 B We must undo log record 6 to set B to 10000 and then redo log records 2 and 3. C We need not redo log records 2 and 3 because transaction T1 has committed D We can apply redo and undo operations in arbitrary order because they are idempotent.
Question 128 Explanation:
Answer should be option(B ) Here we are not using any checkpoints. So, We must undo log record 6 to set b to 10,000 and then redo log records 2 and 3 because system fail before commit operation. So we need to undone active transactions(T2) and redo committed transactions (T1)
 Question 129
Given a block can hold either 3 records or 10 key pointers. A database contains n records, then how many blocks do we need to hold the data file and the dense index
 A 13n/30 B N/3 C N/10 D N/30
Question 129 Explanation:
Given data
Database contains n records
Single block can hold either 3 records or 10 key pointers
By using Dene index
Total Number of blocks needed to store data file with "n" records = n/3
Total Number of blocks needed to store dense file index = n/10
Total blocks required to hold data and dense index= n/3 + n/10 = 13n/30 blocks.
 Question 130
Let R = (A, B, C, D, E, F) be a relation schema 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 130 Explanation:
Find the closure set of all the options given. If any closure covers all the attributes of the relation R then that is the key.
{CD}+={CDF}
{EC}+={ABCDEF}
{AE}+={ABE}
{AC}+={ABCF}
EC is the key So, Option(B) is Correct
 Question 131
Consider the following relational schema:
Suppliers( sid:integer, sname:string, city:string, street:string )
Parts( pid:integer, pname:string, color:string)
Catalog( sid:integer, pid:integer, cost:real)
Consider the following relational query on the above database:
```SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN (SELECT C.sid
FROM Catalog C
WHERE C.pid NOT IN (SELECT P.pid
FROM Parts P
WHERE P.color <> 'blue'))```
Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?
 A Find the names of all suppliers who have supplied a non-blue part. B Find the names of all suppliers who have not supplied a non-blue part C Find the names of all suppliers who have supplied only non blue parts. D Find the names of all suppliers who have not supplied only non-blue parts.
Question 131 Explanation:
SELECT P.pid FROM Parts P WHERE P.color<>’blue’
Above query select all non blue parts

SELECT C.sid FROM Catalog C WHERE C.pid NOT IN
Above query returns all suppliers who have supplied a blue part

SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN
Above query returns suppliers who have not supplied any blue parts.

So, None of the options are matching.

Option(A): wrong, It may include who have supplied blue parts and may not include “null” parts.
Option(B): wrong because it returning other than any blue part.
Option(C): wrong as it does not select suppliers who have not supplied any parts which the given query does
Option(D): wrong

Reference : http://sqlfiddle.com/#!9/9ae12d/1/0
 Question 132
Consider the following schema:
Emp (Empcode, Name, Sex, Salary, Deptt)
A simple SQL query is executed as follows:
```SELECT Deptt
FROM Emp
WHERE sex = 'M'
GROUP by Dept
Having avg(Salary) > {SELECT avg(Salary)
FROM Emp }```
The output will be
 A Average salary of male employee is the average salary of the organization B Average salary of male employee is less than the average salary of the organization C Average salary of male employee is equal to the average salary of the organization D Average salary of male employee is more than the average salary of the organization
Question 132 Explanation:
Query 1 : select avg (salary) from Employee
Above query return Average salary of an employee in organization

Query 2: select deptName from Employee where sex = 'Male' GROUP by Dept Having avg (Salary)
Average salary of Male employee where grouped by department

Query 2 > Query 1
Average salary of male employee is more than the average salary of the organization
 Question 133
If a node has K children in B-tree, then the node contains exactly _____ keys.
 A K2 B K – 1 C K + 1 D K1/2
Question 133 Explanation:
In a B-tree, if a node contains "n" keys then it has "n+1" child pointers. So, if it has "K" child pointers then it must have "K-1" keys.
 Question 134
If D1, D2…Dn are domains in a relational model, then the relation is a table, which is a subset of
 A D1⊕D2⊕…⊕Dn B D1xD2x…xDn C D1∪D2∪…∪Dn D D1∩D2∩…∩Dn
Question 134 Explanation:
A relation or table is a subset of the Cartesian product of a list of domains characterized by a name

Below are the steps for Logic between a relation and its domains.
• Given n domains are denoted by D1, D2, … Dn
• And r is a relation defined on these domains
• Then r ⊆ D1×D2×…×Dn
Reference : http://people.cs.pitt.edu/~chang/156/04reldb.html
 Question 135
The maximum length of an attribute of type text is
 A 127 B 255 C 256 D It is variable
Question 135 Explanation:
 Question 136
Which of the following relation schemas is definitely in BCNF?
 A R1(A,B) B R4(A,B,C,D,E) C R3(A,B,C,D) D R2(A,B,C)
Question 136 Explanation:
Every Binary Relation(a relation with only two attributes) is always in BCNF because if we have only 2 attributes then definitely one will be key
For example 2 attributes A and B
If we have a functional dependency as A->B then A must be the Super key.
(OR)
If we have a functional dependency as B->A then B must be the Super key.
(OR)
{ A->b, B->a} Then Both Keys will be super keys
 Question 137
The DELETE/FROM/WHERE command is used for removing one or more ___.
 A Attributes from a table(relation) B Tables from a database C Databases D Tuples from a table(relation)
Question 137 Explanation:
DELETE/FROM/WHERE command is used for removing one or more Tuples from a Table
DROP command is used to drop the entire table(schema) from the database
 Question 138
Referential integrity constraints works on the concept of:
 A Secondary key B Super key C Foreign key D Primary key
Question 138 Explanation:
FOREIGN KEY constraints enforce referential integrity which essentially says that if column value X refers to column value Y, then column value Y must exist.
X should be FOREIGN KEY
Y should be Primary Key
 Question 139
State whether TRUE or FALSE
(i) Secondary index cannot be defined on key attribute values
(ii) In B+ tree indexing the non-leaf nodes contain the actual data pointers
 A (i)-False, (ii)-True B (i)-True, (ii)-True C (i)-False, (ii)-False D (i)-True, (ii)-False
Question 139 Explanation:
Primary Index : Primary index is defined on an ordered data file. The data file is ordered on a key field. The key field is generally the primary key of the relation.
Secondary Index : Secondary index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.
Clustering Index : Clustering index is defined on an ordered data file. The data file is ordered on a non-key field.

Internal node in B+ tree
-Internal node of the B+ tree can contain at least n/2 record pointers except the root node.
-At most, an internal node of the tree contains n pointers.
leaf node in B+ tree
-leaf node of the B+ tree can contain at least n/2 record pointers and n/2 key values.
-At most, a leaf node contains n record pointer and n key values.
-Every leaf node of the B+ tree contains one block pointer P to point to next leaf node.
 Question 140
If R(A,B,C,D) is a relation schema, which is decomposed into R1(A,B,C) and R2(C,D), which of the following ensures that the given decomposition is non additive(or lossless)?
 A R1 → R2 B R2 → R1 C R1 ∩ R2 → R1 or R1 ∩ R2 → R2 D R1 U R2 → R1 R1 U R2 → R2
Question 140 Explanation:
R1 ∩ R2 → R1 or R1 ∩ R2 → R1-R2
R1 ∩ R2 → R2 or R1 ∩ R2 → R2-R1
Decomposition should be lossless if and only if there exist a common attribute between the decomposed relation which is either a candidate key or key attribute in any of the decomposed tables
 Question 141
For a database relation R(a,b,c,d) where the domains of a,b,c and d include only atomic values, only the following functional dependencies and those that can be inferred from them hold
• A → c
• B → d
The relation is in
 A 1 normal form but not in 2 normal form B 2 normal form but not in 3 normal form C 3 normal form D None of these
Question 141 Explanation:
 Question 142
Which of the following desired features are beyond the capability of relational algebra?
 A Aggregate Computation B Multiplication C Finding transitive closure D All of the above
Question 142 Explanation:
• Aggregate is a function and to collection of values to generate a single result.( sum(), avg(),max(),min(),any(),count())
• Relational algebra can not preform the Multiplication ( Cartesian product) and Aggregate function
• Transitive closure is an operation on relation tables that is not expressible in relational algebra. Transitive closure is an operation on directed graphs where the output is a graph with direct connections between nodes only when there is a path between those nodes in the input graph.
 Question 143
When transaction T​i​ requests a data item currently held by T​j​ , T​i​ is allowed to wait only if it has a timestamp smaller than that of T​j​ (that is, T​i​ older than T​j​ ). Otherwise, T​i​ is rolled back(dies). this is
 A Wait-die B Wait-wound C Wound-Wait D Wait
Question 143 Explanation:
This question dicuss about definition of Wait-die scheme
 Question 144
Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R. It will
 A Result in deadlock situation B Immediately be granted C Immediately be rejected D Be granted as soon as it is released by A
Question 144 Explanation:
• Shared locks exist when two transactions are granted read access.
• One transaction gets the shared lock on data and when the second transaction requests the same data it is also given a shared lock.
• Both transactions are in a read-only mode, updating the data is not allowed until the shared lock is released.
• There is no conflict with the shared lock because nothing is being updated.
• Shared locks last as long as they need to last; it depends on the level of the transaction that holds the lock
• Transaction hold shared lock on data item A .
• So any number of transaction can read that the data from the requesting item using shared locking
• So transaction B request will immediately granted
Source : Wiki
 Question 145
Given relations R(w,x) and S(y,z), the result of
SELECT DISTINCT w, x
FROM R, S
Is guaranteed to be the same as R, if
 A R has no duplicates and S is non empty B R and S have no duplicates C S has no duplicates and R is non empty D R and S has the same number of tuples
Question 145 Explanation:

-The query selects all attributes of R. Since we have distinct in query, result can be equal to R only if R doesn’t have duplicates. If S is empty RXS becomes empty, so S must be non empty.

Option(A):R has no duplicates and S is non-empty
Incase If R has duplicates in that case due to DISTINC keyword in query will eliminate duplicates in FINAL result and the results in query !=R
So, R cannot have duplicate values
Incase Relation "S" is Empty then RXS becomes empty So, Relation "S" must be non-empty

Option(B): R and S have no duplicates
Assume Relation ''S' is Empty which has no duplicate values then RXS becomes empty So, Relation "S" must be non-empty

Option(C) : S has no duplicates and R is non-empty
S has no duplicate means we can relation "S" as Empty which is discussed in option(B)

Option(D) : R and S have the same number of tuples
Incase If R has duplicates in that case due to DISTINC keyword in query will eliminate duplicates in FINAL result So results in query !=R which is discussed in option(A)
 Question 146
Which of the following statements are not correct?
S1: 3NF decomposition is always lossless join and dependency preserving
S2: 3NF decomposition is always lossless join but may or may not be dependency preserving
S3: BCNF decomposition is always lossless join and dependency preserving
S4: BCNF decomposition is always lossless join but may or may not be dependency preserving
 A Only S1 B Only S4 C Boh S1 and S4 D Both S2 and S3
Question 146 Explanation:
• 3NF decomposition is always lossless join and dependency preserving. -TRUE
• 3NF decomposition is always lossless join but may or may not be dependency preserving. -False
• BCNF decomposition is always lossless join and dependency preserving.-False
• BCNF decomposition is always lossless join but may or may not be dependency preserving. -TRUE
 Question 147
In conservative two phase locking protocol, a transaction
 A Should release all the locks only at beginning of transaction B Should release exclusive locks only after the commit operation C Should acquire all the exclusive locks at beginning of transaction D Should acquire all the locks at beginning of transaction
Question 147 Explanation:
In Conservative 2-PL we need to lock all the data items which the transaction required before the transaction begins execution by predeclaring its read-set and write-set. And for its conservative nature, this protocol is deadlock free but starvation can occur.
 Question 148
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 attributes 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 148 Explanation:
Every relation in BCNF is also in 3NF
 Question 149
Consider the relation scheme R(A,B,C,D) with following FD set
F={A → CE, B → D, AE → D},
Identify the highest normal form satisfied by the relation R
 A 2NF B BCNF C 3NF D 1NF
Question 149 Explanation:
F = {A→CE, B→D, AE→D }
AB+ ={A,B,C,D,E}
Candidate key is = AB
Prime Attributes or Key Attributes ={A,B}
Non-Prime Attributes or No-Key Attributes={C,D,E}

B→D is a partial dependency,
Functional dependency B→D violates 2NF requirement
∴ The highest normal form satisfied by the relation is 1NF
 Question 150
The condition for total participation of entity in a relationship is__
 A Maximum cardinality should be one B Minimum cardinality should be one C Minimum cardinality should be zero D None of the options
Question 150 Explanation:
Total Participation
-Minimum = 1
-Maximum =N

Partial Participation
-Minimum = 0
-Maximum =N
 Question 151
Consider the relation schema R(A,B,C,D) with following functional dependency set F={A→ BC, C→ D}; The relation R is in ___
 A 2NF B BCNF C 3NF D 1NF
Question 151 Explanation:
Given Functional dependency
A→BC
C→D
A+={A,B,C,D}
Candidate Key = A
Prime Attribute or Key Attribute = {A}
Nom-Prime Attribute or Non-Key Attribute = {B C D}
Satisfies the 1NF because of Atomic
C→D following the Transitive dependency so it is not 3NF
Satisfies the 2NF because there is no Partial dependency
 Question 152
______ operation preserves those tuples that would be lost in____
 A Natural join, outer join B Outer join, natural join C Left outer join, right outer join D Left outer join, natural join
Question 152 Explanation:
The outer join operation preserves a few tuples that are otherwise lost in the join operation. The outer join operation preserves the tuples to the right of the operation.
 Question 153
Given a relation schema R(ABCDEFGH) in first normal form. For the set of dependencies
F={ A→ B, A→ C, CG→ H, B→ H, G→ F},
which dependency is logically implied?
 A AC→ H B C→ H C G→ H D A→ H
Question 153 Explanation:
Given FD
A→ B
A→ C
CG→ H
B→ H
G→ F
A→ B and B→ H By using Transitive dependency we can imply A→ H
 Question 154
Consider the relation Emp-Dept with SSn as key

(Ename, SSn, EAddr, Dept.No, Dept.Name, Dept.Manager, SSn)
Which of the following is (are) invalid operation(s)?
a) Inserting an employee without name and address
b) Inserting an employee with only SSn
c) Inserting a department with no employee
d) Inserting an employee without SSn
 A (a) B (d) C (c) and (d) D (b) and (c)
Question 154 Explanation:
They clearly specified in the question that SSn is the key So, Inserting an employee without SSn is invalid operation
 Question 155
The process of removing deficiencies and loopholes in the data is called as____
 A Data aggregation B Extraction of data C Compression of data D Cleaning of data
Question 155 Explanation:
The process of removing the deficiencies and loopholes in the data is called as cleaning up of data.

Data cleansing or data cleaning is the process of detecting and correcting (or removing) corrupt or inaccurate records from a record set, table, or database and refers to identifying incomplete, incorrect, inaccurate or irrelevant parts of the data and then replacing, modifying, or deleting the dirty or coarse data. Data cleansing may be performed interactively with data wrangling tools, or as batch processing through scripting.

Source : https://en.wikipedia.org/wiki/Data_cleansing
 Question 156
Given the following two statements about SQL

(a) An SQL query can contain HAVING clause only if it has GROUP BY clause.
(b) In an SQL query “SELECT_FROM_WHERE_GROUP BY_HAVING”, HAVING is executed before WHERE.

Which of the following is correct?
 A (a) and (b) are true B (a) is true, (b) is false C (a) is false, (b) is true D (a) and (b) both are false
Question 156 Explanation:
(A) : An SQL query can contain HAVING clause only if it has GROUP BY clause - TRUE

(B) :In an SQL query “SELECT_FROM_WHERE_GROUP BY_HAVING”, HAVING is executed before WHERE. - False
The order of execution of Clauses: FROM > WHERE > GROUP BY > HAVING > DISTINCT > SELECT > ORDER BY

 Question 157
____key must satisfy referential integrity in a relation, while__key must satisfy entity integrity.
 A Candidate, primary B Foreign,primary C Primary,foreign D Foreign,superkey
Question 157 Explanation:
• Referential integrity ensures that the values for a set of attributes in one relation must also appear the same for the particular set attributes in another relation.
• FOREIGN KEY constraints enforce referential integrity which essentially says that if column value X refers to column value Y, then column value Y must exist.
--X should be FOREIGN KEY
--Y should be Primary Key
• Entity integrity constraint ensures that the primary key attribute in a relation, should not accept a null value. This is because the primary key attribute value uniquely defines an entity in a relation. So, it being null would not work.
 Question 158
Which of the following statements is incorrect?
 A Data definition languages is used by DBA and database designers to define schemas B Storage definition language is used to specify the internal schema. C Storage definition language is used to insert,delete and update data D Data definition languages is used to retrieve data from the database
Question 158 Explanation:
Storage definition language is used to insert, delete and update data - False
--Data Manipulation Language(DML) is used to insert, delete and update data
 Question 159
Given an instance of the relation R(ABCD).

Which of the following functional dependencies hold?
 A {AB} → D and D → A B {AB → D and B → D C {AB} → C and B → C D {AB} → D and A → D
Question 159 Explanation:
 Question 160
Given relations R(w,x) and S(y,z), the result of
SELECT DISTINCT w, x
FROM R, S
Is guaranteed to be the same as R, if
 A R has no duplicates and S is non empty B R and S have no duplicates C S has no duplicates and R is non empty D R and S has the same number of tuples
Question 160 Explanation:

-The query selects all attributes of R. Since we have distinct in query, result can be equal to R only if R doesn’t have duplicates. If S is empty RXS becomes empty, so S must be non empty.

Option(A):R has no duplicates and S is non-empty
Incase If R has duplicates in that case due to DISTINC keyword in query will eliminate duplicates in FINAL result and the results in query !=R
So, R cannot have duplicate values
Incase Relation "S" is Empty then RXS becomes empty So, Relation "S" must be non-empty

Option(B): R and S have no duplicates
Assume Relation ''S' is Empty which has no duplicate values then RXS becomes empty So, Relation "S" must be non-empty

Option(C) : S has no duplicates and R is non-empty
S has no duplicate means we can relation "S" as Empty which is discussed in option(B)

Option(D) : R and S have the same number of tuples
Incase If R has duplicates in that case due to DISTINC keyword in query will eliminate duplicates in FINAL result So results in query !=R which is discussed in option(A)
 Question 161
E-R model uses this symbol to represent weak entity set?
 A Dotted rectangle B Diamond C Doubly outlined rectangle D None of these
Question 161 Explanation:
• Weak entity is an entity that cannot be uniquely identified by its attributes alone
• Weak entity must use a foreign key in conjunction with its attributes to create a primary key
• Foreign key is typically a primary key of an entity it is related to
• Weak entity doesn't have primary key
 Question 162
Choose the correct statements
 A A total recursive function is also a partial recursive function B A partial recursive function is also a total recursive function C A partial recursive function is also a primitive recursive function D None of the above
Question 162 Explanation:
• An equivalent definition states that a partial recursive function is one that can be computed by a Turing machine.
• A total recursive function is a partial recursive function that is defined for every input.
• Every primitive recursive function is total recursive, but not all total recursive functions are primitive recursive.
• Primitive recursive functions are a class of functions that are defined using composition and primitive recursion
• They are a strict subset of those p-recursive functions (also called partial recursive functions) which are also total functions.
• Primitive recursive functions form an important building block on the way to a full formalization of computability.
 Question 163
Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R. It will
 A Result in deadlock situation B Immediately be granted C Immediately be rejected D Be granted as soon as it is released by A
Question 163 Explanation:
• Shared locks exist when two transactions are granted read access.
• One transaction gets the shared lock on data and when the second transaction requests the same data it is also given a shared lock.
• Both transactions are in a read-only mode, updating the data is not allowed until the shared lock is released.
• There is no conflict with the shared lock because nothing is being updated.
• Shared locks last as long as they need to last; it depends on the level of the transaction that holds the lock
• Transaction hold shared lock on data item A .
• So any number of transaction can read that the data from the requesting item using shared locking
• So transaction B request will immediately granted
Source : Wiki
 Question 164
Table Employee has 10 records. It has a non-NULL SALARY column which is also UNIQUE.
The SQL statement
```SELECT COUNT(*)
FROM Employee
WHERE SALARY > ANY (SELECT SALARY
FROM EMPLOYEE);
```
 A 10 B 9 C 5 D 0
Question 164 Explanation:
ANY compares a value with each of the values in a list or results from a query and evaluates to true if the result of an inner query contains at least one row. ANY must be preceded by comparison operators
(=, >, <, <=, >=, <>).

Employee table has 10 records and each value in non-NULL SALARY column is unique i.e different. So, in that 10 records one of the record will be minimum which cannot be greater than any nine value of the salary column. Hence the condition
WHERE SALARY > ANY (SELECT SALARY FROM employee)
will be true nine times. So, the COUNT(*) outputs 9.
 Question 165
When transaction T​i​ requests a data item currently held by T​j​ , T​i​ is allowed to wait only if it has a timestamp smaller than that of T​j​ (that is, T​i​ older than T​j​ ). Otherwise, T​i​ is rolled back(dies). this is
 A Wait-die B Wait-wound C Wound-Wait D Wait
Question 165 Explanation:
This question dicuss about definition of Wait-die scheme
 Question 166
Consider the following EMP table and answer the question below: Which of the following select statement should be executed if we need to display the average salary of employees who belongs to grade “E4”?
 A Select avg(salary) from EMP whose grade=”E4”; B Select avg(salary) from EMP having grade=”E4”; C Select avg(salary) from EMP group by grade where grade=”E4”; D Select avg(salary) from EMP group by grade having grade=”E4”;
Question 166 Explanation:

→Having Clause is basically like the aggregate function with the GROUP BY clause.
→HAVING clause is used instead of WHERE with aggregate functions.
→While the GROUP BY Clause groups rows that have the same values into summary rows.
→The having clause is always used after the group By clause.

Syntax :
SELECT column__name
FROM table__name
WHERE condition
GROUP BY column__name
HAVING condition
ORDER BY column__name

Option(A) : "Whose" Invalid
Option(B) : Having clause is always used after the group By clause.
Option(C): Incorrect Syntax "Where" should come after "From" clause
Option(D):Correct
 Question 167
The command used to see the fields of the table along with their datatypes in SQL is
 A Select fields from dual where table=”MANAGER”; B Select field_names, datatype from dual where table_name=”MANAGER”; C Desc MANAGER; D Select description from dual where table_name=”MANAGER”
Question 167 Explanation:
Desc MANAGER;

DESCRIBE or DESC command is used to describe the structure of a table.
It provides the list of column definitions for specified table
You can use either DESC or DESCRIBE statement. both are Case-Insensitive

Syntax
DESCRIBE table_name;
OR
DESC table_name;
 Question 168
Which of the following statements is not true for views in SQL?
 A Select statement used in the view definition cannot include ORDER BY clause B A view derives its data from the base tables(s) C A view is updatable if it has been defined from a single relation D A view contains a copy of the data
Question 168 Explanation:
When can a view will be updated ?
1. View is defined based on one and only one table.
2. View must include the PRIMARY KEY of the table based upon which the view has been created.
3. View should not have any field made out of aggregate functions.
4.  View must not have any DISTINCT clause in its definition.
5.  View must not have any GROUP BY or HAVING clause in its definition.
6.  View must not have any SUBQUERIES in its definitions.
7.  If the view you want to update is based upon another view, the later should be updatable.
8.  Any of the selected output fields (of the view) must not use constants, strings or value expressions.
 Question 169
In order to add a new column to an existing table in SQL, we can use the command
 A MODIFY TABLE B EDIT TABLE C ALTER TABLE D ALTER COLUMNS
Question 169 Explanation:
In order to add a new column to an existing table in SQL, we can use the  ALTER TABLE ADD COLUMN command as follows

ALTER TABLE table_name

In the above statement
→At First, specify the table name to which you want to add the new column.
→At Second, specify the column definition after the ADD COLUMN clause.
 Question 170
If a table is in BCNF and has no multivalued dependencies, then it is said to be in
 A 3NF B 2NF C 4NF D 5NF
Question 170 Explanation:
First Normal Form
-Each table cell should contain a single value.
-Each record needs to be unique.

Second Normal Form
-A relation R is in 2NF if and only if it is in 1NF
-There no partial dependencies

Third Normal Form
-A relation R is in 3NF if and only if it is in 2NF
-There is no transitive functional dependencies

BCNF
-A relation R is in BCNF if and only if it is in 3NF
-A relation is in BCNF if every functional dependency A → B and A is the super key of the given relation

4NF
-A relation R is in 4NF if and only if it is in BCNF.
-There is no multi-valued dependency.
 Question 171
In an RDBMS relationships between tables are created by using
 A Alternate keys B Foreign keys C Candidate keys D Composite keys
Question 171 Explanation:
 Question 172
A program is a/an____ entity
 A Active B Passive C Dormant D Hyperactive
Question 172 Explanation:
• A program is a passive entity. It is a sequence of instructions to be executed on a computer to perform particular task
• A process is a active entity. It is an instance of a computer program
 Question 173
The following diagram depicts
 A Two-level model B Many to one model C One to one model D Many to many model
Question 173 Explanation:
• Many to Many model multiplexes any number of user threads onto an equal or smaller number of kernel threads, combining the best features of the one-to-one and many-to-one models.
• Users have no restrictions on the number of threads created.
• Blocking kernel system calls do not block the entire process.
• Processes can be split across multiple processors.
• Individual processes may be allocated variable numbers of kernel threads, depending on the number of CPUs present and other factors.
 Question 174
Which of the following statements is NOT true with respect to database normalization?
 A A 4NF is more immune against logical inconsistencies than a 3NF table. B A 3NF table will have fewer anomalies than a 2NF table C A 3NF table is more vulnerable than a 2NF table D A database is said to be in 3NF if all its tables are in 3NF
Question 174 Explanation:
First Normal Form
-Each table cell should contain a single value.
-Each record needs to be unique.

Second Normal Form
-A relation R is in 2NF if and only if it is in 1NF
-There no partial dependencies

Third Normal Form
-A relation R is in 3NF if and only if it is in 2NF
-There is no transitive functional dependencies

BCNF
-A relation R is in BCNF if and only if it is in 3NF
-A relation is in BCNF if every functional dependency A → B and A is the super key of the given relation

4NF
-A relation R is in 4NF if and only if it is in BCNF.
-There is no multi-valued dependency.
 Question 175
Which of the following statements is NOT true for Rollback statements in SQL?
 A All save points marked after the save points to which you rollbacked, are erased B It does not free any resources held by the transaction C The save point to which you rollback is not erased D The Rollback statement will erase all data modifications made from the start of the transaction to the savepoint
Question 175 Explanation:
ROLLBACK  Command
ROLLBACK command is transactional command used to undo transactions that have not already been saved to the database. ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
Syntax :
ROLLBACK

SAVEPOINT Command
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
Syntax
SAVEPOINT SAVEPOINT_NAME ;
Above command serves only in the creation of a SAVEPOINT among all the transactional statements. The ROLLBACK command is used to undo a group of transactions.
Syntax for rolling back to a SAVEPOINT is as shown below :
ROLLBACK TO SAVEPOINT_NAME;

 Question 176
Consider two database relations R and S having 3 tuples in R and 2 tuples in S. What is the maximum number of tuples that could appear in the natural join of R and S?
 A 6 B 2 C 3 D 5
Question 176 Explanation:
 Question 177
What result set is returned from the following SQL query ?
```SELECT customer_name, telephone
FROM customers
WHERE city IN('Jaipur','Delhi','Agra');```
 A The cusomer_name of all customers who are not living IN Jaipur,Delhi OR Agra B The customer_name and telephone of all customers C The customer_name and telephone of all customers living IN either Jaipur, Delhi OR Agra D The customer_name and telephone of all customers living IN Jaipur,Delhi AND Agra
Question 177 Explanation:
-SQL IN condition allows to easily test if an expression matches with any value in a list of values.
-SQL IN condition reduces the need for multiple OR conditions in sql statements
WHERE city IN('Jaipur' , 'Delhi' , 'Agra' ) ;
Which is same as
WHERE  city ='Jaipur' OR city ='Delhi' OR city ='Agra' ;
 Question 178
Which of the following statements best describes the main reasons for normalizing relational database?
(i) To achieve physical data independence
(ii) To remove data anomalies (insertion, update, delete, anomalies)
(iii) To save space on disk.
 A (i),(ii) and (iii) B (i) and (ii) C (i) and (iii) D (ii) and (iii)
Question 178 Explanation:
Benefits to a database Normalization
• Greater overall database organization
• To remove data anomalies(insertion/update/delete anomalies)
• Reduction of redundant data
• To save space on disk (it is happened because of avoiding redundancy)
• Data consistency within the database
• A much more flexible database design
 Question 179
Consider the database table "persons" having Person_ID as the primary key.

what are the violated by the above table?
 A Relationship integrity B Referential integrity only C Entity and domain integrities D Referential and domain integrities
Question 179 Explanation:
 Question 180
Which of the following is not a transaction management SQL command?
 A Rollback B Commit C Select D Savepoint
Question 180 Explanation:
TCL Commands (Transaction Control Language command ) deals with transitions
-COMMIT − to save the changes.
-ROLLBACK − to roll back the changes.
-SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
-SET TRANSACTION − Places a name on a transaction.

DML Commands (Data Manipulation Language Commands)
-INSERT – is used to insert data into a table.
-UPDATE – is used to update existing data within a table.
-DELETE – is used to delete records from a database table.

DQL Commands (Data Query Language Commands) :
-SELECT – is used to retrieve data from the a database.

DCL Commands (Data Control Language Commands) :
-GRANT -Gives user’s access privileges to database.
-REVOKE - withdraw user’s access privileges given by using the GRANT command.

DDL Commands (Data Definition Language Commands) :
-CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
-DROP – is used to delete objects from the database.
-ALTER-is used to alter the structure of the database.
-TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.
-RENAME –is used to rename an object existing in the database.
 Question 181
Concurrency control in RDBMS is important for which of the following reasons?
 A To ensure data integrity when reads occur to the database in a multi user environment B To ensure data integrity when updates occur to the database in a single user environment C To ensure data integrity when updates occur to the database in a multi user environment D To ensure data integrity when reads occur to the database in a single user environment
Question 181 Explanation:
Concurrency control is a process which allows multiple people to access a shared resource, which in this case is a database. There are several different strategies when it comes to configuring your database to prevent data corruption when multiple users are accessing it at the same time that are all called concurrency controls.
 Question 182
Which of the following statements is false with respect to relational DBMS?
 A A disadvantage of highly normalized tables is that queries may require too many time consuming joins B Foreign key constraints are referential integrity constraints C A primary key uniquely identifies a row in a table D Nulls reduce space requirements in tables
Question 182 Explanation:
True - A disadvantage of highly normalized tables is that queries may require too many time consuming joins
True -Foreign key constraints are referential integrity constraints
True -A primary key uniquely identifies a row in a table
False- Nulls reduce space requirements in tables
 Question 183
What do data warehouse support?
 A Operational database B OLAP C OLTP D OLAP and OLTP
Question 183 Explanation:
 Question 184
Buying and selling goods over the internet is called:
 A Euro-conversion B Hyper-marketing C Cyber-Selling D E-Commerce
Question 184 Explanation:
Buying and selling goods over the internet is called E-commerce
Example : Flipkart.com,Amazon.com,RentAcenter.com
 Question 185
An entity set that does not have sufficient attribute to form a key is termed as:
 A Primary entity set B Strong entity set C Weak entity set D Simple entity set
Question 185 Explanation:
 Question 186
In relational databases, the natural join of two tables is
 A Cartesian product always B Combination of union and filtered cartesian product C Combination of selection and filtered cartesian product D Combination of projection and filtered cartesian product
Question 186 Explanation:
• Natural join is used to join two relations having any number of attributes. It is denoted by a symbol ( ⨝). It is the combination of projection and filtered cartesian product.
• Projection to remove duplicate attributes in the resultant relation( common column will be present twice in cartesian product) and Filtering with match on common column.
 Question 187
With SQL, how can you return the number of records in the "person's" table?
 A SELECT COUNT (*) FROM persons B SELECT COUNT () FROM persons C SELECT COLUMN () FROM persons D SELECT COLUMN (*) FROM Persons
Question 187 Explanation:
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
 Question 188
In a relational database model, cardinality of a relation means
 A The number of constraints B The number of tuples C The number of attributes D The number of tables
Question 188 Explanation:
 Question 189
Conceptual level, Internal level and external level are three components of the three level RDBMS architecture Which of the following is not a part of the conceptual level?
 A Storage dependent details B Entities,attributes and relationships C Constraints D Semantic information
Question 189 Explanation:
 Question 190
__ is an intermediate storage area used for data processing during the extract transformation and load process of data warehousing
 A Inter storage area B Buffer C Staging area D Virtual memory
Question 190 Explanation:
• A staging area, or landing zone, is an intermediate storage area used for data processing during the extract, transform and load (ETL) process.
• The data staging area sits between the data source(s) and the data target(s), which are often data warehouses, data marts, or other data repositories.
• Data staging areas are often transient in nature, with their contents being erased prior to running an ETL process or immediately following successful completion of an ETL process.
• There are staging area architectures, however, which are designed to hold data for extended periods of time for archival or troubleshooting purposes.
 Question 191
When is EOQ in inventory control?
 A Economics of quantity B Economics occuring quantity C Economics over quantity D Economics order quantity
Question 191 Explanation:
• EOQ stands for Economic Order Quantity
• EOQ refers to the ideal order quantity a company should purchase in order to minimize its inventory costs, such as holding costs, shortage costs, and order costs.
• EOQ is necessarily used in inventory management, which is the oversight of the ordering, storing, and use of a company's inventory.
• Inventory management is tasked with calculating the number of units a company should add to its inventory with each batch order to reduce the total costs of its inventory.
 Question 192
To add attributes to an existing relation __ commands is used:
 A Update table B Alter table C Change table D Add table
Question 192 Explanation:

To add attributes to an existing relation ALTER TABLE commands is used

In order to add a new column to an existing table in SQL, we can use the  ALTER TABLE ADD COLUMN command as follows

ALTER TABLE table_name

In the above statement
→At First, specify the table name to which you want to add the new column.
→At Second, specify the column definition after the ADD COLUMN clause.
 Question 193
A relation that is not of conceptual model but is made visible to a user as a virtual relation is called:
 A Table B Query C View D Joined relations
Question 193 Explanation:
Standard definition for view
Any relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called a view.
 Question 194
In transaction management of database, ‘After a transaction completes successfully the changes it has made to the database persists even if there are system failures’. This property is referred to as:
 A Atomicity B Consistency C Isolation D Durability
Question 194 Explanation:
 Question 195
For the relation loan(loan_number, branch_name, amount), the query
{ t|t ∈ loan ∧ t[amount] > 1200}  gives:
 A The loan_number, branch_name and amount for loans over \$1200 B The loan_number for each loan of an amount greater than \$1200 C The branch_name for each loan of an amount greater than \$1200 D The amount for each loan of an amount greater than \$1200
Question 195 Explanation:
 Question 196
In concurrency control a situation where ‘ A transaction may be waiting for an x-lock on an item, while a sequence of other transactions request and are granted on s-lock on the same item’. May lead to:
 A Deadlock B Starvation C Conflict D Lock failure
Question 196 Explanation:
 Question 197
Data warehouse bus matrix is a combination of
 A Dimensions and data marts B Dimensions and facts C Facts and data marts D Dimensions and detained facts
Question 197 Explanation:
Data warehouse bus matrix is simply a vertical list of data marts and a horizontal list of dimensions.
 Question 198
Consider the relation Emp_Dept with SSn as key

( Ename, SSn, Addr, Dept_No, Dept_Name, Dept_Manager_SSN )
Following operations are performed:
a.Insert a record of department with no employee
b.Delete the last employee of the department
c.Update the department name for department #5

Which of the following will lead to complete loss of information about a department?
 A Only b B Only c C A and b D B and c
Question 198 Explanation:
→ With Insert and update operation there is no loss of information about department
→ Deleting one Employee details leads to department details of particular employee is not available
 Question 199
Which of the statements are true about functional dependency X→ Y in relation R ?
A. Whenever two tuples t1 and t2 in R have t1(X)=t2(X) then t1(Y)=t2(Y)
B. Whenever two tuples t1 and t2 in R have t1(Y)=t2(Y) then t1(X)=t2(X)
 A A and b B Only a C Only b D Neither a nor b
Question 199 Explanation:
 Question 200
____ is logical design of a database, while____is snapshot of data in the database at a point in time.
 A Database schema, database instance B Database relation,attribute C Attribute domain, attribute value D Database schema, attribute domain
Question 200 Explanation:
→Database schema is logical design of a database.
→Instance is snapshot of data in the database at a point in time.
→Instance is an instance of time and schema is a representation.
 Question 201
Real life constraints on the relational schema can be specified by
 A Functional dependency B Domain Integrity rules C Referential Integrity rules D Entity Integrity rules
Question 201 Explanation:
→A relation is nothing but a table of values. Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity or relationship.
→A functional dependency (FD) is a relationship between two attributes attribute Y is functionally dependent on attribute X (usually the PK), if for every valid instance of X, that value of X uniquely determines the value of Y. This relationship is indicated by the representation below :
X —> Y
Left side of the above FD diagram is called the determinant
Right side is the dependent.
 Question 202
Given the following two statements about SQL:
a) An SQL query can contain HAVING clause only if it has GROUP BY clause
b) Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
Which of the following is correct?
 A Both a and b are true B Both a and b are false C A is true, b is false D B is true, a is false
Question 202 Explanation:
Answer key is (A) A and B are true.
But correct answer is a) is False b) True
 Question 203
SQL automatically does not eliminate duplicate tuples in the results of queries because
a) In aggregation function duplicates are useful
b) Duplicate elimination is expensive
c) memory chips are cheap and large these days, and can accommodate large number of tuples
Which of the following is/are correct?
 A A and b B B and c C Only b D Only c
Question 203 Explanation:
• SQL automatically does not remove duplicates like relational algebra projection we have to remove it explicitly by using DISTINCT Keyword.
• "ALL" causes an aggregate function to consider all values, including all duplicates.
• Removing duplicate tuples is costly operation in which we need to access all the tuples in order to find the duplicate records
 Question 204
Consider the database state for the two tables given below:

SQL query- INSERT INTO DEPT(Dname, Dnum, Mgr_SSn) VALUES (Dept2,3,Mgr3)
Is rejected because of
 A Duplication of department name B Incorrect Mgr_SSN C Violation of referential Integrity D Violation of entity integrity
Question 204 Explanation:
• Dnum which is common attribute in both DEPT and DEPT_LOC Table
• Assume Dnum is Foreign Key in DEPT Table and Primary Key in DEPT_LOC Table
• Dnum in DEPT Table referring to Dnum in DEPT_LOC Table
• Inserting a new row in the DEPT table gives some null entries in the DEPT_LOC Table which is violation of referential integrity
Example
Inserting (Dept4,4,Mgr4 ) hear Dnum=4 in DEPT table gives some null entries in the DEPT_LOC Table because Dnum=4 entry not exist
 Question 205
Which of the following is/are true with reference to ‘view’ in DBMS ?
(a) A ‘view’ is a special stored procedure executed when certain event occurs.
(b) A ‘view’ is a virtual table, which occurs after executing a pre-compiled query.
code:
 A Only (a) is true B Only (b) is true C Both (a) and (b) are true D Neither (a) nor (b) are true
Question 205 Explanation:
A VIEW is a virtual table through which a selective portion of the data from one or more tables can be seen. A view do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.

Syntax to create a sql view :
CREATE VIEW view_name
AS
SELECT column_list
FROM table_name [WHERE condition];

view_name is the name of the VIEW.
SELECT statement is used to define the columns and rows that you want to display in the view
 Question 206
In SQL, __________ is an Aggregate function.
 A SELECT B CREATE C AVG D MODIFY
Question 206 Explanation:
Aggregate functions in SQL:
1) MIN
2) MAX
3) AVG
4) COUNT
5) SUM
Avg is an aggregate function returns the average of a group of elements.
Syntax:
SELECT AVG(column_name) FROM table_name;
 Question 207
Match the following with respect to RDBMS :
 A. Entity Integrity 1. Enforces some specific business rule that do not fall into entity or domain B. Domain integrity 2. Rows can’t be deleted which are used by other records C. Referential Integrity 3. Enforces valid entries for a column D. User defined Integrity 4. No duplicate rows in a table
 A (a)-(iii), (b)-(iv), (c)-(i), (d)-(ii) B (a)-(iv), (b)-(iii), (c)-(ii), (d)-(i) C (a)-(iv), (b)-(ii), (c)-(iii), (d)-(i) D (a)-(ii), (b)-(iii), (c)-(iv), (d)-(i)
Question 207 Explanation:
Entity Integrity : No duplicate rows n a table because of primary key
Domain Integrity : Enforces Valid entries for a column. All columns in a relational database must be declared upon a defined domain
Referential integrity :Rows can’t be deleted which are used by another record
User defined Integrity : Enforces some specific business rule that do not fall into entity or domain means we cannot delete records in Parent table(PK) before deleting the records from the child table(FK).
 Question 208
In RDBMS, different classes of relations are created using __________ technique to prevent modification anomalies.
 A Functional Dependencies B Data integrity C Referential integrity D Normal forms
Question 208 Explanation:
• Functional dependency is a constraint that describes the relationship between attributes in a relation.
• Data integrity refers to the accuracy and consistency of data stored in a database, data warehouse, data mart or other construct.
• Referential integrity is a relational database concept, which states that table relationships must always be consistent.
• Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly and deletion anomaly. 1NF, 2NF, 3NF and BCNF are types of normal forms.
 Question 209
__________ SQL command changes one or more fields in a record.
 A LOOK-UP B INSERT C MODIFY D CHANGE
Question 209 Explanation:
 Question 210
Which of the following is not a Clustering method ?
 A K-Mean method B Self Organizing feature map method C K-nearest neighbor method D Agglomerative method
Question 210 Explanation:
Clustering is one of the most common exploratory data analysis technique used to get an intuition about the structure of the data.
Clustering analysis can be done on the basis of features where we try to find subgroups of samples based on features or on the basis of samples where we try to find subgroups of features based on samples
• k-means clustering is a method of vector quantization, originally from signal processing, that is popular for cluster analysis in data mining.
• Self-Organizing Map Self Organizing Map (SOM) provides a data visualization technique. SOM also represents clustering concept by grouping similar data together.
• k-nearest neighbors algorithm (k-NN) is a non-parametric method used for classification and regression.
• Agglomerative hierarchical clustering is a bottom-up clustering method where clusters have sub-clusters, which in turn have sub-clusters, etc.
 Question 211
An attribute A of data type varchar (20) has value ‘Ram’ and the attribute B of data type char (20) has value ‘Sita’ in oracle. The attribute A has _______ memory spaces and B has _______ memory spaces.
 A 20,20 B 3,20 C 3,4 D 20,4
Question 211 Explanation:
• CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example : CHAR(20) can hold up to 20 characters.
• varchar will acquire the exact memory of attribute and it varies from tuple to tuple while char will acquire memory space which is define at the time of table creation it is fixed
• VARCHAR(20) can also hold up to 20 characters but if character is less it will take less space
• varchar(20) ‘Ram’ will take 3 and ‘Sita’ will take 20 character space in memory.
 Question 212
Integrity constraints ensure that changes made to the database by authorized users do not result into loss of data consistency.
Which of the following statement(s) is (are) true w.r.t. the examples of integrity constraints ?
(A) An instructor Id. No. cannot be null, provided Instructor Id No. being primary key.
(B) No two citizens have same Adhar-Id.
(C) Budget of a company must be zero.
 A (A), (B) and (C) are true. B (A) false, (B) and (C) are true. C (A) and (B) are true; (C) false. D (A), (B) and (C) are false
Question 212 Explanation:

Option(A) : True
• Primary key does not allow Null values
• The PRIMARY KEY integrity constraint guarantees that both of the following are true: No two rows of a table have duplicate values in the specified column or set of columns.
• In simple words two NULL values are not considered as equal.
• This is the reason why Primary Key can’t have NULL values as they are not compared with any other value.
Option(B) : True
Aadhar number is identification number for citizens in India so it must be unique across India consider as it as primary key So primary does not allow null values

Option(C) : False
Budget of a company must be zero - We can't say or it is not necessarily be true
 Question 213
Let M and N be two entities in an E-R diagram with simple single value attributes.
R​1​ and R​2​ are two relationship between M and N, where as
R​1​ is one-to-many and R​2​ is many-to-many.
The minimum number of tables required to represent M, N, R​1​ and R​2​ in the relational model are _______.
 A 4 B 6 C 7 D 3
Question 213 Explanation:
Strong entities E1 and E2 are represented as separate tables i.e One table for each entity = 2
R1 is one-to-many(No separate Table is required) = 0
R2 is many-many So, it requires one separate table = 1
Total Number of tables = 3
 Question 214
Consider a schema R(MNPQ) and functional dependencies M → N, P → Q. Then the decomposition of R into R​1​ (MN) and R​2​ (PQ) is________.
 A Dependency preserving but not lossless join B Dependency preserving and lossless join C Lossless join but not dependency preserving D Neither dependency preserving nor lossless join.
Question 214 Explanation:
R(MNPQ) is decomposed into R1(MN) M → N is preserved  R2(PQ) P → Q is also preserved, dependency will be preserved and there will be no common key attribute between R1 and R2. So, this relation is lossy relation
 Question 215
​ The order of a leaf node in a B​ +​ tree is the maximum number of children it can have. Suppose that block size is 1 kilobytes, the child pointer takes 7 bytes long and search field value takes 14 bytes long. The order of the leaf node is ________.
 A 16 B 63 C 64 D 68 E None
Question 215 Explanation:

***This question is excluded from evaluation**

Given data,
Key size = 14 bytes,
Child pointer = 7 bytes,
Block Size= 1 Kilobytes,
Assume order of B+ tree = ‘n’.

Block size >= (n – 1) * key size + n * child pointer

1038 >= (n – 1) * 14 + n * 7
1038>= 14 * n – 14 + 7 * n
1038>= 14n – 14 + 7n
1038 >= 21n – 14
1038>= 21n
n <= 1038 / 21
n <= 49.42
 Question 216
________ refers loosely to the process of semi-automatically analyzing large databases to find useful patterns.
 A Datamining B Data warehousing C DBMS D Data mirroring
Question 216 Explanation:
• The term data mining refers loosely to the process of semi automatically analyzing large databases to find useful patterns. Like knowledge discovery in artificial intelligence (also called machine learning).
• In general terms, “Mining” is the process of extraction of some valuable material from the earth e.g. coal mining, diamond mining etc.
• The term “Data Mining” refers to the extraction of us, “Data Mining” refers to the extraction of useful information from a bulk of data or data warehouses
Reference : https://en.wikipedia.org/wiki/Data_mining
 Question 217
DBMS provides the facility of accessing data from a database through
 A DDL B DML C DBA D Schema
Question 217 Explanation:
DBMS provides the facility of accessing data from a database through DML Commands
These SQL commands are mainly categorized into 4 categories as:
• DDL – Data Definition Language
• DQl – Data Query Language
• DML – Data Manipulation Language
• DCL – Data Control Language
 Question 218
Relational database schema normalization is NOT for:
 A Reducing the number of joins required to satisfy a query. B Eliminating uncontrolled redundancy of data stored in the database. C Eliminating number of anomalies that could otherwise occur with inserts and deletes. D Ensuring that functional dependencies are enforced.
Question 218 Explanation:
• Relational database schema normalization is for eliminating uncontrolled redundancy of data stored in the database.
• Relational database schema normalization is for eliminating number of anomalies that could otherwise occur with inserts and deletes.
• Relational database schema normalization is for ensuring that functional dependencies are enforced.
Option(A) is not correct because normalization is  does not for reduce the number of joins required to satisfy a query it only tries to eliminate redundancy and anomalous using Functional dependency(FD's)
 Question 219
Consider the following statements regarding relational database model:

(a) NULL values can be used to opt a tuple out of enforcement of a foreign key.
(b) Suppose that table T has only one candidate key. If Q is in 3NF, then it is also in BCNF.
(c) The difference between the project operator (Π) in relational algebra and the SELECT keyword in SQL is that if the resulting table/set has more than one occurrences of the same tuple, then Π will return only one of them, while SQL SELECT will return all.

One can determine that:
 A (a) and (b) are true. B (a) and (b) are true C (b) and (c) are true D (a), (b) and (c) are true
Question 219 Explanation:
True - NULL values can be used to opt a tuple out of enforcement of a foreign key.
True - Suppose that table T has only one candidate key. If Q is in 3NF, then it is also in BCNF.
True - The difference between the project operator (Π) in relational algebra and the SELECT keyword in SQL is that if the resulting table/set has more than one occurrences of the same tuple, then Π will return only one of them, while SQL SELECT will return all.
 Question 220
Consider the following Entity-Relationship (E-R) diagram and three possible relationship sets (I, II and III) for this E-R diagram: If different symbols stand for different values (e.g., t​1​ is definitely not equal to t​2 ) ​, then which of the above could not be the relationship set for the E-R diagram ?
 A I only B I and II only C II only D I, II and III
Question 220 Explanation:
 Question 221
Consider a database table R with attributes A and B. Which of the following SQL queries is illegal ?
 A SELECT A FROM R; B SELECT A, COUNT(*) FROM R; C SELECT A, COUNT(*) FROM R GROUP BY A; D SELECT A, B, COUNT(*) FROM R GROUP BY A, B;
Question 221 Explanation:
• SELECT A FROM R -legal query
• SELECT A, COUNT(*) FROM R; Illegal query
Aggregate functions can't be used without "Group By" clause.
It is Legal Query SELECT A, COUNT(*) if it is grouped by A.
• SELECT A, COUNT(*) FROM R GROUP BY A; - legal query
• SELECT A, B, COUNT(*) FROM R GROUP BY A, B; -legal query
 Question 222
_______ is subject oriented, integrated, time variant, nonvolatile collection of data in support of management decisions.
 A Data mining B Web mining C Data warehouse D Database Management System
Question 222 Explanation:
A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision making process.
• “Subject-Oriented”: Contrary to what many people believe when first hearing the term, a data warehouse does not have to be one monolithic repository of all your data. A data warehouse can be used to store and analyze data on a specific subject.
• “Integrated” : One of the key features of data warehouses is that they store and leverage data from multiple different sources.
• “Time variant” : Data warehouses are the home of historical data. You can pull up data from a specific time period, be that 3 months or 3 years ago. Data warehouses often also contain detailed temporal data, showing changes to a dataset. For example, if you’re using your data warehouse to store key customer metrics, you can not only see a customer’s current address or recent purchase history, but all data previously associated with this customer & how it’s changed over time.
• “Non-volatile” : The data contained within a data warehouse is not subject to change.
Reference : https://dataconomy.com/2014/10/what-is-a-data-warehouse/#:~:text=A%20data%20warehouse%20is%20a,of%20management's%20decision%20making%20process.
 Question 223
In Data mining, classification rules are extracted from _______.
 A Data B Information C Decision Tree D Database
Question 223 Explanation:
• In Data mining, classification rules are extracted from decision Tree.
• A decision tree can be used to visually and explicitly represent decisions and decision making.
• In data mining, a decision tree describes data but not decisions; rather the resulting classification tree can be an input for decision making.
• A tree is built by splitting the source set, constituting the root node of the tree, into subsets which constitute the successor children.
• The splitting is based on a set of splitting rules based on classification features. This process is repeated on each derived subset in a recursive manner called recursive partitioning.
Refer :  https://en.wikipedia.org/wiki/Decision_tree_learning
 Question 224
In Data mining, ______ is a method of incremental conceptual clustering.
 A STRING B COBWEB C CORBA D OLAD
Question 224 Explanation:
• COBWEB is an incremental system for hierarchical conceptual clustering.
• COBWEB incrementally organizes observations into a classification tree. Each node in a classification tree represents a class (concept) and is labeled by a probabilistic concept that summarizes the attribute-value distributions of objects classified under the node.
• This classification tree can be used to predict missing attributes or the class of a new object
• COBWEB incrementally organizes observations into a classification tree. Each node in a classification tree represents a class (concept) and is labeled by a probabilistic concept that summarizes the attribute-value distributions of objects classified under the node.
• This classification tree can be used to predict missing attributes or the class of a new object
Refer : https://en.wikipedia.org/wiki/Cobweb_(clustering)
 Question 225
In RDBMS, the constraint that no key attribute (column) may be NULL is referred to as:
 A Referential integrity B Multi-valued dependency C Entity Integrity D Functional dependency
Question 225 Explanation:
Entity Integrity :
The primary key or part of it in any relation can not be null

Referential integrity rule :
Referential integrity states that table relationships must always be consistent that a foreign key can be either null or it can have those values which are present in the  primary key with which it is related

Multi-valued dependency :
Multi-valued dependency is a full constraint between two sets of attributes in a relation

Functional dependency :
Functional dependency is a relationship that exists when one attribute uniquely determines another attribute.
 Question 226
Which of the following statement(s) is/are FALSE in the context of Relational DBMS ?
I. Views in a database system are important because they help with access control by allowing users to see only a particular subset of the data in the database.
II. E-R diagrams are useful to logically model concepts.
III. An update anomaly is when it is not possible to store information unless some other, unrelated information is stored as well.
IV. SQL is a procedural language.
 A I and IV only B III and IV only C I, II and III only D II, III and IV only
Question 226 Explanation:
Statement 1 : True
Statement 2 : False
ER diagram is used for conceptual modelling not logical modelling
Statement 3 : False
update anomaly is concerned with related information
Statement 3 : False
SQL is non procedure language

what is difference between conceptual modelling and logical modelling ?
Refer : https://stackoverflow.com/questions/4279089/what-is-the-difference-between-logical-data-model-and-conceptual-data-model
 Question 227
In a relational database model, NULL values can be used for all but which one of the following ?
 A To allow duplicate tuples in the table by filling the primary key column(s) with NULL. B To avoid confusion with actual legitimate data values like 0 (zero) for integer columns and ’’ (the empty string) for string columns. C To leave columns in a tuple marked as ’’unknown’’ when the actual value is unknown. D To fill a column in a tuple when that column does not really ”exist” for that particular tuple.
Question 227 Explanation:
• Null is not equal to any value, it cannot be used as a primary key
• A PRIMARY KEY column is equivalent to UNIQUE and NOT NULL
• PRIMARY KEY should be UNIQUE because a primary key identifies rows in a table so 2 different row should not have the same key.
• Sometimes column of a row not require any value in that case we can use NULL values. So, In option(B),(C),(D) we can use null values in the relation
 Question 228
Consider the following two commands C1 and C2 on the relation R from an SQL database:
C1 : drop table R;
C2 : delete from R;
Which of the following statements is TRUE ?
I. Both C1 and C2 delete the schema for R.
II. C2 retains relation R, but deletes all tuples in R.
III. C1 deletes not only all tuples of R, but also the schema for R.
 A I only B I and II only C II and III only D I, II and III
Question 228 Explanation:
-Drop command in sql remove all tuples of the relation as well as schema also
-Delete command in sql removes tuples from the relation but retain the schema of table in database.
So, option(C) is correct
 Question 229
Consider the following database table having A, B, C and D as its four attributes and four possible candidate keys (I, II,III and IV) for this table :

I : {B}
II : {B, C}
III : {A, D}
IV : {C, D}
If different symbols stand for different values in the table (e.g., d​1​ is definitely not equal to d​2​ ), then which of the above could not be the candidate key for the database table ?
 A I and III only B III and IV only C II only D I only
Question 229 Explanation:
Below Functional dependency holds true the given relation
B → { A, C, D }
A,D → {A, B, C, D}
C,D → {A, B, C, D}
B,C → {A, B, C, D}

We can simply say B alone is the candidate key because in the table "B" Attribute all rows are unique (b1,b2,b3) Hence {B} is the candidate key
[A,D] and [C,D] are the candidate keys but [B,C] is not candidate key.
[B] is a Candidate key so [B,C] is super key, not the candidate key of the given relation
Hence {B, C} cannot be a candidate key in the above database table
 Question 230
Consider the following two statements :
(A) Business intelligence and Data warehousing is used for forecasting and Data mining.
(B) Business intelligence and Data warehousing is used for analysis of large volumes of sales data.
Which one of the following options is correct ?
 A (A) is true, (B) is false. B Both (A) and (B) are true. C (A) is false, (B) is true. D Both (A) and (B) are false.
Question 230 Explanation:
• Business intelligence(BI) and data warehousing have different goals. While they are connected and cannot function without each other, BI is mainly focused on generating business insights, whether operational or strategic efficiency such as product positioning and pricing to goals, profitability, sales performance, forecasting, strategic directions, and priorities on a broader level.
• The point is to access, explore, and analyze measurable aspects of a business.
• A Data Warehouse (DWH) has its significance in storing all the company’s data (from one or several sources) in a single place. In a nutshell, BI systems and tools make use of data warehouse while data warehouse acts as a foundation for business intelligence.
 Question 231
Consider the following two statements :
(A) Data scrubling is a process to upgrade the quality of data, before it is moved into Data warehouse.
(B) Data scrubling is a process of rejecting data from data warehouse to create indexes.
Which one of the following options is correct ?
 A (A) is true, (B) is false. B (A) is false, (B) is true. C Both (A) and (B) are false. D Both (A) and (B) are true.
Question 231 Explanation:
• Data scrubbing is also called  as data cleansing it is the process of amending or removing data in a database that is incorrect, incomplete, improperly formatted, or duplicated.
• By using data scrubbing tool can save a database administrator a significant amount of time and can be less costly than fixing errors manually.
• Data scrubbing is a process to upgrade the quality of data, before it is moved into Data warehouse.
 Question 232
Consider a “CUSTOMERS” database table having a column “CITY” filled with all the names of Indian cities (in capital letters). The SQL statement that finds all cities that have “GAR” somewhere in its name, is:
 A Select * from customers wherecity = ‘%GAR%’; B Select * from customers where city = ‘\$GAR\$’; C Select * from customers where city like ‘%GAR%’; D Select * from customers where city as ‘%GAR’;
Question 232 Explanation:
 Question 233
Match the following database terms to their function:
```   List - I                       List - II
(a) Normalization          (i)  Enforces match of primary key
to foreign key
(b) Data Dictionary        (ii) Reduces data redundancy
in a database
(c) Referential Integrity (iii) Defines view(s) of the database
for particular user(s)
(d) External Schema        (iv) Contains metadata describing
database structure```

Code :
 A (iv)(iii)(i)(ii) B (ii)(iv)(i)(iii) C (ii)(iv)(iii)(i) D (iv)(iii)(ii)(i)
Question 233 Explanation:
• Normalization reduces data redundancy in a database
• Data Dictionary contains metadata describing database structure defines view(s) of the database for particular user(s)
• Referential Integrity enforces match of primary key to foreign key
• External Schema defines view(s) of the database for particular user(s)
 Question 234
Which of the following provides the best description of an entity type?
 A A specific concrete object with a defined set of processes (e.g. Jatin with diabetes) B A value given to a particular attribute (e.g. height - 230 cm) C A thing that we wish to collect data about zero or more, possibly real world examples of it may exist D A template for a group of things with the same set of characteristics that may exist in the real world
Question 234 Explanation:
A template for a group of things with the same set of characteristics that may exist in the real world
 Question 235
Data which improves the performance and accessibility of the database are called:
 A Indexes B User Data C Application Metadata D Data Dictionary
Question 235 Explanation:
• Indexes are special lookup tables that the database search engine can use to speed up data retrieval
• An index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
• Simply we can say that an index is a pointer to data in a table which increases the accessibility in a book or databases, we can easily access any information with the help of index.
Refer : https://www.tutorialspoint.com/sql/sql-indexes.htm
 Question 236
A relation R = {A, B, C, D, E, F,G} is given with following set of functional dependencies:
F = {AD → E, BE → F, B → C, AF → G}
Which of the following is a candidate key ?
 A A B AB C ABC D ABD
Question 236 Explanation:
Given Relation R={A,B,C,D,E,F,G}
From the options
[A]+ =  {A}
[AB]+ = {ABC}
[ABC ]+ = {ABC}
[ABD ]+ = {ABCDEFG}
{ABD} is the candidate key.
 Question 237
__________ system is market oriented and is used for data analysis by knowledge workers including Managers, Executives and Analysts.
 A OLTP B OLAP C Data System D Market System
Question 237 Explanation:
 Question 238
__________ allows selection of the relevant information necessary for the data warehouse.
 A The Top - Down View B Data Warehouse View C Datasource View D Business Query View
Question 238 Explanation:
They are four views regarding the design of a data warehouse
• Top down view allows selection of the relevant information necessary for the data warehouse
• Data-source view exposes the information being captured, stored, and managed by operational systems
• Data warehouse view consists of fact tables and dimension tables
• Business query view sees the perspectives of data in the warehouse from the view of end-user
 Question 239
An Assertion is a predicate expressing a condition we wish database to always satisfy. The correct syntax for Assertion is :
 A CREATE ASSERTION ‘ASSERTION Name’ CHECK ‘Predicate’ B CREATE ASSERTION ‘ASSERTION Name’ C CREATE ASSERTION, CHECK Predicate D SELECT ASSERTION
Question 239 Explanation:
 Question 240
Which of the following concurrency protocol ensures both conflict serializability and freedom from deadlock?
(a) 2-Phase Locking
(b) Timestamp ordering
 A Both (a) and (b) B (a) only C (b) only D Neither (a) nor (b)
Question 240 Explanation:
Time stamp – ordering concurrency protocol ensures both conflict serializability and freedom from deadlock.
• In Basic 2 phase locking there is a chance for deadlock and starvation but ensure serializability
• Strict 2PL  ensure serializability and recoverability but deadlock and starvation possible
• Conservative 2PL ensure serializability and no deadlock but starvation possible
• Time stamp ordering  ensure serializability and no deadlock and avoid starvation by using wait-die and wound -wait scheme
 Question 241
Drop Table cannot be used to drop a Table referenced by __________ constraint.
(a)Primary key
(b)Sub key
(c)Super key
(d)Foreign key
 A (a) B (a), (b) and (c) C (d) D (a) and (d)
Question 241 Explanation:
Drop Table cannot be used to drop a Table referenced by Foreign key(FK) constraint because deleting a table referenced by FK will leads to violate the referenced key constraint
 Question 242
Database applications were built directly on top of file system to overcome the following drawbacks of using file-systems:
(a) Data redundancy and inconsistency
(b) Difficulty in accessing Data
(c) Data isolation
(d) Integrity problems
 A (a) B (a) and (d) C (a), (b) and (c) D (a), (b), (c) and (d)
Question 242 Explanation:
• Data redundancy and inconsistency Redundancy is the concept of repetition of data i.e. each data may have more than a single copy. We can overcome over it with the help of Normalization
• Difficulty in accessing Data can be overcome by different type of queries i.e. procedural and non procedural queries.
• Data isolation can be removed by join etc.
• Integrity problems Integrity Constraints are the compulsory conditions which should be satisfied by every data value present in the relational table at any instance of time to ensure that the database consists of only meaningful and relevant data. There are four types of integrity constraints:
• Domain Constraints : Every attribute should have values within its defined domain.
• Key Constraints : There should be a primary key for every relational table.
• Entity Integrity Constraints : No NULL values should be there for the Primary Keys.
• Referential Integrity Constraints : In relational model, when two tables are related to each other with the help of some common attributes, the value of referencing attribute should be present in the referenced attribute else it should be NULL.
 Question 243
For a weak entity set to be meaningful, it must be associated with another entity set in combination with some of their attribute values, is called as:
 A Neighbour Set B Strong Entity Set C Owner Entity Set D Weak Set
Question 243 Explanation:
In the above figure "Employee" is the Owner Set and "Dependents" is the Weal Entity Set
 Question 244
Which of the following statements is FALSE about weak entity set?
 A Weak entities can be deleted automatically when their strong entity is deleted. B Weak entity set avoids the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity. C A weak entity set has no primary keys unless attributes of the strong entity set on which it depends are included D Tuples in a weak entity set are not partitioned according to their relationship with tuples in a strong entity set.
Question 244 Explanation:
option(A) : True
option(B) : True
option(C) : True
option(D) : False because Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity set. Employee table which is strong entity is partitioning the tuples of week entity(Dependent table) based on the tuples belongs to Emp.N0 ="1" and tuples belongs to EMP.NO="2"
 Question 245
The E-R model is expressed in terms of :
(i) Entities
(ii) The relationship among entities
(iii) The attributes of the entities
Then
 A (i) and (iii) B (i) and (ii) C (ii) and (iii) D None of the above
Question 245 Explanation:
ER diagram has three main components
1. Entity
2. Attribute
3. Relationship
 Question 246
The entity type on which the __________ type depends is called the identifying owner.
 A Strong entity B Relationship C Weak entity D E - R
Question 246 Explanation:
• weak entity is an entity that cannot be uniquely identified by its attributes alone; therefore, it must use a foreign key in conjunction with its attributes to create a primary key.
• The foreign key is typically a primary key of an entity it is related to
• In entity relationship diagrams (ER diagrams), a weak entity set is indicated by a bold (or double-lined) rectangle (the entity) connected by a bold (or double-lined) type arrow to a bold (or double-lined) diamond (the relationship).
• This type of relationship is called an identifying relationship
• An identifying relationship is one where the primary key is populated to the child weak entity as a primary key in that entity.

 Question 247
Match the following :
```A. 2 NF - (i)  Transitive dependencies eliminated
B. 3 NF - (ii) Multivalued attribute removed
C. 4 NF -(iii) Contain no partial functional dependencies
D. 5 NF - (iv) Contains no join dependency```

Code :
 A I-a, ii-c, iii-b, iv-d B I-d, ii-c, iii-a, iv-b C I-d, ii-c, iii-b, iv-a D I-a, ii-b, iii-c, iv-d E I-(c), II-(a), III-(b), IV-(d)
Question 247 Explanation:
First Normal Form
-Each table cell should contain a single value.
-Each record needs to be unique.

Second Normal Form
-A relation R is in 2NF if and only if it is in 1NF
-There no partial dependencies

Third Normal Form
-A relation R is in 3NF if and only if it is in 2NF
-There is no transitive functional dependencies

BCNF
-A relation R is in BCNF if and only if it is in 3NF
-A relation is in BCNF if every functional dependency A → B and A is the super key of the given relation

4NF
-A relation R is in 4NF if and only if it is in BCNF.
-There is no multi-valued dependency.

5NF
-A relation R is in 5NF if and only if it satisfies following conditions:
--R should be in 4NF (no multi-valued dependency exists).
--It cannot undergo lossless decomposition (join dependency)
 Question 248
A schema describes :
 A Data elements B Records and files C Record relationship D All of the above
Question 248 Explanation:
• In a relational database The overall design of the database is called database schema.
• Schema defines the tables, fields, relationships, views, indexes, packages, procedures, functions, queues, triggers, types, sequences, materialized views, synonyms, database links, directories, XML schemas, and other elements.
 Question 249
In a relational schema, each tuple is divided in fields called :
 A Relations B Domains C Queries D All the above
Question 249 Explanation:
• In a relational schema, each tuple is divided into fields called Domains .
•  In a relational model, each attribute carries a domain of its own.
• A domain refers to the set of unique values used to define that attribute and will act as a "model" set of values.
• These values, being unique to that attribute are referred to as "atomic values".
•  A domain is a set of acceptable values that a column is allowed to store
• The rule for determining the domain boundary may be as simple as a data type with an enumerated list of values.
 Question 250
A locked file can be :
 A Accessed by only one user B Modified by users with the correct password C Is used to hide sensitive information D Both (B) and (C)
Question 250 Explanation:
File locking is a mechanism that restricts access to a computer file by allowing only one user or process access to the file at any specific time.
 Question 251
An Entity - relationship diagram is a tool to represent :
 A Data model B Process model C Event model D Customer model
Question 251 Explanation:
 Question 252
Multi-valued dependency among attribute is checked at which level ?
 A 2 NF B 3 NF C 4 NF D 5 NF
Question 252 Explanation:
First Normal Form
-Each table cell should contain a single value.
-Each record needs to be unique.

Second Normal Form
-A relation R is in 2NF if and only if it is in 1NF
-There no partial dependencies

Third Normal Form
-A relation R is in 3NF if and only if it is in 2NF
-There is no transitive functional dependencies

BCNF
-A relation R is in BCNF if and only if it is in 3NF
-A relation is in BCNF if every functional dependency A → B and A is the super key of the given relation

4NF
-A relation R is in 4NF if and only if it is in BCNF.
-There is no multi-valued dependency.

5NF
-A relation R is in 5NF if and only if it satisfies following conditions:
--R should be in 4NF (no multi-valued dependency exists).
--It cannot undergo lossless decomposition (join dependency)
 Question 253
A WINDOW into a portion of a database is :
 A Schema B View C Query D Data Dictionary
Question 253 Explanation:
• View is a logical portion of a database which is needed by some users
• The logical database view is how the data appear to the user to be stored.
• This view represents the structure that the user must interface with in order to extract data from the database
• A view in database returns a portion of database based on condition given in the sql query.
• View of a database gives the updated data stored in the database
• A view return a virtual view of database, it can join and simply multiple tables into a single virtual table
 Question 254
In DBMS, deferred update means :
 A All the updates are done first but the entries are made in the log file later B All the log files entries are made first but the actual updates are done later C Every update is done first followed by a writing on the log file D Changes in the views are deferred till a query asks for a view
Question 254 Explanation:
 Question 255
Which statement is false regarding data independence ?
 A Hierarchical data model suffers from data independence B Network model suffers from data independence C Relational model suffers only from logical data independence D Relational model suffers only from physical data independence
Question 255 Explanation:
Data Independence is defined as a property of DBMS that helps you to change the Database schema at one level of a database system without requiring to change the schema at the next higher level. Data independence helps you to keep data separated from all programs that make use of it.
• Hierarchical data model suffers from data independence - True
• Network model suffers from data independence - True
• Relational model suffers only from logical data independence - False
• Relational model suffers only from physical data independence - True
 Question 256
Two phase protocol in a database management system is :
 A A concurrency mechanism that is not deadlock free B A recovery protocol used for restoring a database after a crash C Any update to the system log done in 2-phases D Not effective in Database
Question 256 Explanation:
Two Phase Locking Protocol also known as 2PL protocol is a method of concurrency control in DBMS that ensures serializability by applying a lock to the transaction data which blocks other transactions to access the same data simultaneously

The Two-Phase Locking protocol(2PL) allows each transaction to make a lock or unlock request in two steps:

Growing Phase
In this phase transaction may obtain locks but may not release any locks.
Shrinking Phase
In this phase, a transaction may release locks but not obtain any new lock

2PL protocol offers serializability but it is is not deadlock free
 Question 257
A relation R={A, B, C, D, E, F} is given with following set of functional dependencies :
F =, {A →B,AD →C,B→F,A →E}
Which of the following is candidate key?
 A A B AC C AD D None of these
Question 257 Explanation:
Trick:
Attribute A and D are not present in the RHS(Right hand Side) of any given functional dependencies. So, every key should include AD to be a candidate key
[AD]+ ={A, B, C, D, E, F}
In all the options . option(C) contains AD none of the options contains AD so option(C) is correct one
So, AD is the candidate key
 Question 258
Immediate updates as a recovery protocol is preferable, when :
 A Database reads more than writes B Writes are more than reads C It does not matter as it is good in both the situations D There are only writes
Question 258 Explanation:
 Question 259
Which of the following statement is wrong ?
 A 2 - phase locking protocol suffers from dead locks B Time - Stamp protocol suffers from more abort C Time stamp protocol suffers from cascading rollbacks where as 2 - phase locking protocol do not D None of these
Question 259 Explanation:
Option(A) : True
• 2-phase locking protocol suffer from dead lock.
Option(B) : True
• Time stamp protocol suffer from more aborts because suffer from cascading rollback
• Time stamp protocol is not recoverable and can lead to cascading rollback
Option(C) : False
•  In 2 Phase Locking protocol Cascading roll-back is possible. We can use strict two-phase locking to avoid Cascading roll-back .
• In  strict two-phase locking protocol  a transaction must hold all its exclusive locks till it commits/aborts.
• Rigorous two-phase locking is even stricter: here all locks are held till commit/abort. In this protocol transactions can be serialized in the order in which they commit.
 Question 260
Which data management language component enabled the DBA to define the schema components ?
 A DML B Subschema DLL C Schema DLL D All of these
Question 260 Explanation:
 Question 261
Division operation is ideally suited to handle queries of the type :
 A Customers who have no account in any of the branches in Delhi. B Customers who have an account at all branches in Delhi. C Customers who have an account in at least one branch in Delhi. D Customers who have only joint account in any one branch in Delhi
Question 261 Explanation:
Division operator
• Division operator is used when we have to evaluate queries which contain the keyword ALL
• Division identify the attribute values from relation that are found to be paired with all of the values from the other relation.
• As multiplication is to division in arithmetic, Cartesian Product (×) is to Division in relational algebra.
• Division is the opposite of Cartesian Product:
 Question 262
Which of the following is true ?
I. Implementation of self-join is possible in SQL with table alias.
II. Outer-join operation is basic operation in relational algebra.
III. Natural join and outer join operations are equivalent.
 A I and II are correct. B