subject – solutions adda

Database-Management-System

Please wait while the activity loads.
If this activity does not load, try refreshing your browser. Also, this page requires javascript. Please visit using a browser with javascript enabled.

If loading fails, click here to try again

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

Database-Management-System B-and-B+-Trees ISRO-2018

Question 1 Explanation:

Explanation:
→ If there are K search-key values in the file, the path is no longer than ⌈log⌈n/2⌉(K)⌉.
→ A node is generally the same size as a disk block, typically 4 kilobytes, and n is typically around 100 (40 bytes per index entry).
→ With 1 million search key values and n = 100, at most log50(1000000)=4 nodes are accessed in a lookup.
Note: Contrast this with a balanced binary free with 1 million search key values around 20 nodes are accessed in a lookup. Above difference is significant since every node access may need a disk I/O, costing around 20 milliseconds!

Question 2

Which of the following is a dense index?

A	Primary index
B	Clusters index
C	Secondary index
D	secondary non-key index

Database-Management-System B-and-B+-Trees ISRO-2018

Question 2 Explanation:

→ The primary index is created for the primary key of a table. So, records are usually clustered according to the primary key. It can be sparse.
→ In the sparse index, index records are not created for every search key. An index record here contains a search key and an actual pointer to the data on the disk. To search a record, we first proceed by index record and reach the actual location of the data. If the data we are looking for is not where we directly reach by following the index, then the system starts a sequential search until the desired data is found.
→ The secondary index usually dense.
→ In the dense index, there is an index record for every search key value in the database. This makes searching faster but requires more space to store index records itself. Index records contain search key value and a pointer to the actual record on the disk.

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

Database-Management-System ER-Model ISRO-2018

Question 3 Explanation:

→ It is the best example of referential integrity. In referential integrity, we are using a foreign key.
→ If we want to delete any entity in the dominant entity(Like primary key of a table) then subordinate entity(derived attribute) also deleted.
Note: If we want to delete any subordinate entity in entity then the dominant entity is not going to delete.

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	l and III only
D	lI and III only

Database-Management-System ISRO-2018

Question 4 Explanation:

Step-1: Both < letter > and < digit > produce only a single character
Step-2: Both < pairlet > and < pairdig > produce even number of characters
Step-3: < word > produces odd number of characters.
Step-4: As per the statements I, II and III. I statement having even number of characters
So, a statement I is wrong. Statement II and III are having odd number of characters.

Question 5

Consider the following table in a relational database

A	{Last Name}
B	{Room}
C	{Shift}
D	{Room, Shift}

Database-Management-System Relational-databases ISRO-2018

Question 5 Explanation:

→ Candidate means uniquely identified key.
→ In the above table, Room having duplicate values. So, we can’t say room is candidate key
→ The last name also having duplicates. So, we can’t say the last name is candidate key.
→ Shift also having duplicate keys, so we can’t say shift also a candidate key.
→ Combining Room and Shift we can say that 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

Database-Management-System Functional-Dependency ISRO-2018

Question 6 Explanation:

→ Candidate Key of given relation R is {ab}.
→ So option A is right it is not in second normal form because its part of key(candidate key) determines non-key.
→ The domains of a, b, c and d include only atomic values.
So, which satisfies the definition of first normal form.

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)
Grades: (Roll number, Course number, Grade)
SELECT DISTINCT Name
FROM Students, Courses, Grades
WHERE Students.Roll_number = Grades.Roll_number
AND Courses.Instructor =Sriram
AND Courses.Course_number = Grades.Course_number
AND Grades.Grade = A
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

Database-Management-System SQL ISRO-2018

Question 7 Explanation:

→ The query results a names of students who got an A grade in at least one of the courses taught by Sriram.
→ The above query they are using AND command, it means it satisfy all conditions.

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

Database-Management-System SQL ISRO-2018

Question 8 Explanation:

→ R has no duplicate if R can have duplicates it can be removed in the final state.
→ S in non-empty if S is empty then R*S becomes empty.

Question 9

Database-Management-System

A	Physical Data Independence
B	Logical Data Independence
C	Both (a) and (b)
D	None of the above

Database-Management-System SQL ISRO-2018

Question 9 Explanation:

→ Logical data independence:
The ability to change the Conceptual (Logical) schema without changing the External schema (User View) is called logical data independence. For example, the addition or removal of new entities, attributes, or relationships to the conceptual schema or having to rewrite existing application programs.

→ Physical data independence:
The ability to change the physical schema without changing the logical schema is called physical data independence. For example, a change to the internal schema, such as using different file organization or storage structures, storage devices, or indexing strategy, should be possible without having to change the conceptual or external schemas.
Note: Immunity is when data at one layer is changed, it does not affect the data at another level.

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

Database-Management-System Functional-Dependency ISRO-2018

Question 10 Explanation:

→ A functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.
→ An attribute is fully functional dependent on another attribute, if it is Functionally Dependent on that attribute and not on any of its proper subset.
For example, an attribute X is fully functional dependent on another attribute Y, if it is Functionally Dependent on X and not on any of the proper subset of Y.

Must satisfied conditions:
i) X is functionally dependent on Y and
ii) 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

Database-Management-System Transactions ISRO-2018

Question 11 Explanation:

→ There are 2 conflicting actions a and b is shown in above diagram.
→ In timestamp ordering protocol, conflicting actions in ascending order time-stamps are allowed i.e 'a' is allowed but not 'b'.
→ So we need to roll back T1 after that only it will be allowed. Because of all conflicting actions in ascending order timestamps in below diagram.

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

Database-Management-System Relational-databases ISRO-2007

Question 12 Explanation:

DCL(Data Control Language) includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.
Examples of DCL commands:
GRANT-gives user’s access privileges to database.
REVOKE-withdraw user’s access privileges given by using the GRANT command.

Question 13

Which of the following is aggregate function in SQL?

A	Avg
B	Select
C	Ordered by
D	distinct

Database-Management-System SQL ISRO-2007

Question 13 Explanation:

Avg is one of the aggregate functions. It returns average value after calculating from values in a numeric column.
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

Database-Management-System Schema ISRO-2007

Question 14 Explanation:

A subschema is a subset of the schema and inherits the same property that a schema has. The plan for a view is often called subschema. Subschema refers to an application programmer’s view of the data item types and record types, which he or she uses.

Question 15

Which operation is used to extract specific columns from a table?

A	Project
B	Join
C	Extract
D	Substitute

Database-Management-System Relational-Algebra ISRO-2007

Question 15 Explanation:

Projection (π)
Projection is used to project required column data from a relation. By Default projection removes duplicate data.
Example :
R(A B C)
----------
1 2 4
2 2 3
3 2 3
4 3 4
π (BC)
B C
-----
2 4
2 3
3 4

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

Database-Management-System Normalization ISRO-2007

Question 16 Explanation:

A relation is in Boyce-Codd normal form if all attributes which are determinants are also candidate keys.
Transformation into Boyce-Codd normal form deals with the problem of overlapping keys.

Question 17

Which of the following is correct with respect to Two phase commit protocol?

A	Ensures serializability
B	Prevents Deadlock
C	Detects Deadlock
D	Recover from Deadlock

Database-Management-System Transactions ISRO-2007

Question 17 Explanation:

The two phase commit protocol is a distributed algorithm which lets all sites in a distributed system agree to commit or rollback a transaction based upon consensus of all participating sites.If any database server is unable to commit its portion of the transaction, all database servers participating in the transaction must be prevented from committing their work.
It ensures serializability but does not ensures freedom from deadlock.

Question 18

What does the data dictionary identify?

A	Field names
B	Field Formats
C	Field Types
D	All of these

Database-Management-System Relational-databases ISRO-2017 May

Question 18 Explanation:

→ A data dictionary(or) metadata repository, as defined in the IBM Dictionary of Computing, is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format".
→ Oracle defines it as a collection of tables with metadata. The term can have one of several closely related meanings pertaining to databases and database management systems (DBMS)
1. A document describing a database or collection of databases
2. An integral component of a DBMS that is required to determine its structure
3. A piece of middleware that extends or supplants the native data dictionary of a DBMS

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

Database-Management-System Transactions ISRO-2017 May

Question 19 Explanation:

→ Two-phase locking protocol (2PL) ensures the conflict serializable schedule but it may not free from deadlock.
→ Timestamp ordering protocol ensures conflict serializability and free from deadlock.

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

Database-Management-System Transactions ISRO-2017 May

Question 20 Explanation:

Atomicity: Execute the all the operations or none of them
→ Responsible for Transaction Manager
Consistency: Database should be consistent before and after the execution of the transaction
→ Responsible for user/application manager
Isolation: Each transaction Tie must be executed without knowing what is happening with other transactions. Responsible for Concurrency control manager
Durability: All updates done by a transaction must become permanent.
→ Responsible for recovery manager

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

Database-Management-System SQL ISRO-2017 May

Question 21 Explanation:

Common attributes in both the table column are the department. So, we apply natural join, it will give the output as common tuples in both the table S and R.

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

Database-Management-System ER-Model ISRO-2017 May

Question 22 Explanation:

Derived attributes are depicted by dashed ellipse.
Example:

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

Database-Management-System B-and-B+-Trees ISRO-2017 May

Question 23 Explanation:

No. of nodes in children of a node = no. of keys in parent node + 1
Here, the tree has 4 levels, then 4+1=5 nodes to be present in the newly created process.

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

Database-Management-System Relational-Algebra ISRO CS 2008

Question 24 Explanation:

→ The join operation can be defined as a cartesian product of two relations followed by a selection.

→ A SQL JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

1. INNER JOIN: Returns records that have matching values in both tables

2. LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table

3. RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table

4. FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

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

Database-Management-System SQL ISRO-2017 December

Question 25 Explanation:

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

Database-Management-System B-and-B+-Trees ISRO-2017 December

Question 26 Explanation:

→ A bitmap index is a special kind of database index that uses bitmaps.
→ Bitmap indexes have traditionally been considered to work well for low-cardinality columns, which have a modest number of distinct values, either absolutely, or relative to the number of records that contain the data.
→ The extreme case of low cardinality is Boolean data (e.g., does a resident in a city have internet access?), which has two values, True and False.
→ Bitmap indexes use bit arrays (commonly called bitmaps) and Solution queries by performing bitwise logical operations on these bitmaps. Bitmap indexes have a significant space and performance advantage over other structures for query of such data.
→ Their drawback is they are less efficient than the traditional B-tree indexes for columns whose data is frequently updated: consequently, they are more often employed in read-only systems that are specialized for fast query - e.g., data warehouses, and generally unsuitable for online transaction processing applications.

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

Database-Management-System DML-commands ISRO CS 2009

Question 27 Explanation:

1. The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.

2. The UPDATE statement is used to modify the existing records in a table.

3.An SQL join clause - corresponding to a join operation in relational algebra - combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.

4.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

Database-Management-System Transactions ISRO CS 2009

Question 28 Explanation:

File locking is a data management feature that restricts other users from changing a specific file.
This allows only one user or process access to this file at any given time.
This is to prevent the problem of interceding updates on the same files.

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

Database-Management-System Transactions ISRO CS 2009

Question 29 Explanation:

The transaction log is an integral part of database.
Every database has a transaction log that is stored within the log file that is separate from the data file.
A transaction log basically records all database modifications. When a user issues an INSERT, for example, it is logged in the transaction log.
This enables the database to roll back or restore the transaction if a failure were to occur and prevents data corruption.

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

Database-Management-System ER-Model ISRO CS 2009

Question 30 Explanation:

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table.
In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
For example, a table called Employees has a primary key called employee_id.
Another table called Employee Details has a foreign key which references employee_id in order to uniquely identify the relationship between the two tables.

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 by a committed transaction
D	A transaction reads a data item after it is written by an uncommitted transaction

Database-Management-System ACID-properties ISRO CS 2009

Question 31 Explanation:

Irrecoverable error occurs when a transaction reads a data item after it is written by uncommitted transaction.
An irrecoverable error is an error that remains an error, no matter how many times you try to perform the same action. In the integration space, that could mean trying to access a database table that does not exist, which would cause the JDBC driver to throw back a SQLException

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

Database-Management-System Relational-Algebra ISRO-2016

Question 32 Explanation:

For maximum:
If there is common attribute in R and S, and every row of R match with every row of S then total no. of tuples will be mn.
For minimum:
If there is no common attribute between R and S or if there is common attribute but none of the row of R matches with rows of S then output tuples will be 0.

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

Database-Management-System Constraints ISRO-2016

Question 33 Explanation:

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

Database-Management-System SQL ISRO-2016

Question 34 Explanation:

→ Which results titles of the five most expensive books.
→ The where clause of outer query will be true for 5 most expensive books.

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

Database-Management-System ISRO-2016

Question 35 Explanation:

Logical Schema includes
1. Avoiding data inconsistency
2. Able to construct query easily
3. Able to access data efficiently

Question 36

Given the relations employee (name, salary, dept-no), and department (dept-no, dept-name,address) 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

Database-Management-System Relational-Algebra ISRO-2016

Question 36 Explanation:

The sum of all employee salaries can’t be represented by using the given six basic algebra operation. If we want to represent sum of salaries then we need to use aggregation operator.

Question 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

Database-Management-System Trigger ISRO-2016

Question 37 Explanation:

→ A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database.
→ The trigger is mostly used for maintaining the integrity of the information on the database.
→ For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.
→Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.

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

Database-Management-System B-and-B+-Trees ISRO-2016

Question 38 Explanation:

Disk Block size = 1 KBytes = 2¹⁰ Bytes = 1024 Bytes
Data recorder pointer size, r = 7 bytes
Value size, v = 9 bytes
Disk Block pointer, P = 6 bytes
⇒ Order of leaf be m, then
r*m+v*m+p <= 1024
7m+9m+6 <= 1024
16m <= 1024 - 6
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

Database-Management-System B-and-B+-Trees ISRO-2016

Question 39 Explanation:

Single level index
1. Primary index(Sparse): Ordered with key field
2. Clustered index(Sparse): Ordered with non key field
3. Secondary index(dense): Ordered with either key or 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

Database-Management-System Sorting ISRO CS 2011

Question 40 Explanation:

Selection sort requires maximum number of swaps i.e O(n).
The algorithm finds the minimum value, swaps it with the value in the first position, and repeats these steps for the remainder of the list. It does no more than n swaps, and thus is useful where swapping is very expensive.

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

Database-Management-System Transactions ISRO CS 2011

Question 41 Explanation:

From the following precedence graph, 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

Database-Management-System ER-Model ISRO CS 2011

Question 42 Explanation:

Many object databases, for example Gemstone or VOSS, offer support for versioning.
An object can be viewed as the set of all its versions.
Object versions can be treated as objects in their own right. Some object databases also provide systematic support for triggers and constraints which are the basis of active databases.

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

Database-Management-System Normalization ISRO CS 2011

Question 43 Explanation:

A full functional dependency is a state of database normalization that equates to the normalization standard of Second Normal Form (2NF).
Second normal form (2NF) is a normal form used in database normalization.
To qualify for second normal form a relation must:
→be in first normal form (1NF)
→not have any non-prime attribute that is dependent on any proper subset of any candidate key of the relation.
A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation.

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

Database-Management-System Functional-dependency ISRO CS 2011

Question 44 Explanation:

Armstrong's axioms are a set of axioms (or, more precisely, inference rules) used to infer all the functional dependencies on a relational database.
The axioms are sound in generating only functional dependencies in the closure of a set of functional dependencies when applied to that set
Axiom of Reflexivity:
→If X is a set of attributes and Y is a subset of X, then X holds Y. Hereby, X holds Y ( X -> Y) means that X functionally determines Y.
Axiom of Augmentation:
→If X holds Y and Z is a set of attributes, then XZ holds YZ. It means that attribute in dependencies does not change the basic dependencies.
Axiom of Transitivity:
The axiom of transitivity says if X holds Y, and Y holds Z, then X must also hold Z.

Question 45

Which of the following is the highest isolation level in transaction management?

A	Serializable
B	Repeated Read
C	Committed Read
D	Uncommitted Read

Database-Management-System Transactions ISRO CS 2013

Question 45 Explanation:

→Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions.
→Isolation levels are described in terms of which concurrency side effects, such as dirty reads or phantom reads, are allowed.
→The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multi-user systems.

Question 46

Consider the following relational schema:
Suppliers (sid:integer, sname:string, sadress:string)
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

Database-Management-System SQL ISRO CS 2013

Question 46 Explanation:

→SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sid = Catalog.pid The above query will gives all pids of both Catalog and Supplier .
→SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sname <> 'sachin' and Suppliers.sid = Catalog.sid
*The above query will gives the pids of all parts which are supplied by any other supplier other than Sachin.
→The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement
→The entire query will get the pids which are supplied by only Sachin.

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
Publisher → Address

A	First Normal Form
B	Second Normal Form
C	Third Normal Form
D	BCNF

Database-Management-System Normalization ISRO CS 2013

Question 47 Explanation:

→All attributes in the given functional dependencies are atomic values So it is FIrst normal form.
→In order to check whether it is in Second normal form or not,fist we need to find the candidate key.
→After finding closure of attribute of ISBN from the above dependencies,the candidate key is ISBN.
→A relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key.
→The given dependencies satisfies second normal form rules as as non prime attribute of the relation is dependent on the whole of every candidate key.
→So the above dependencies are in Second normal form.

Question 48

Calculate the order of leaf(p_leaf) 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	p_leaf = 51 & p = 46
B	p_leaf= 47 & p = 52
C	p_leaf= 46 & p = 50
D	p_leaf = 52 & p = 47

Database-Management-System B+trees ISRO CS 2013

Question 48 Explanation:

In B+ trees the satellite information(record information) is stored in only leaf nodes and not in the non leaf nodes, so no need to include record pointer in the non-leaf nodes.
i) leaf node:
let the order of leaf be 'n'
size of search key field * n + record pointer * n + block pointer <= 1024
12 * n + 10 * n + 8 <= 1024
22n <= 1016
n = 46
ii) for non-leaf node:
size of search key field * n + block pointer * (n+1) <= 1024
12 * n + 8 * n + 8 = 1024
n = 50.8
order of non-leaf node (p) = 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

Database-Management-System B+trees ISRO CS 2013

Question 49 Explanation:

Hash File organization method is the one where data is stored at the data blocks whose address is generated by using hash function.

Question 50

Embedded pointer provides

A	a secondary access path
B	a physical record key
C	an inverted index
D	a prime key

Database-Management-System Storage ISRO CS 2013

Question 50 Explanation:

1. To understand how pointers and their associated data elements are allocated in Microsoft RPC, you have to differentiate between top-level pointers and embedded pointers
2. Top-level pointers are those that are specified as the names of parameters in function prototypes. Top-level pointers and their referents are always allocated on the server.
3. Embedded pointers are pointers that are embedded in data structures such as arrays, structures, and unions. When embedded pointers only write output to a buffer and are null on input, the server application can change their values to non-null. In this case, the client stubs allocate new memory for this data.
4.If the embedded pointer is not null on the client before the call, the stubs do not allocate memory on the client on return. Instead, the stubs attempt to write the memory associated with the embedded pointer into the existing memory on the client associated with that pointer, overwriting the data already there.

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

Database-Management-System ISRO CS 2014

Question 51 Explanation:

Association: Relationship between 2 objects. It defines the multiplicity between objects like 1-1, 1-many, many-1, many to many.
Aggregation: A directional between objects. When an object “has-a” another object, then you have got an aggregation between them, you have got an aggregation between them. Direction between them specified which object contains the other object.

Question 52

Let x, y, z, a, b, c be the attributes of an entity set E. If {x}, {x,y}, {a,b}, {a,b,c}, {x,y,z} are superkeys then which of the following are the candidate keys?

A	{x,y} and {a,b}
B	{x} and {a,b}
C	{x,y,z} and {a,b,c}
D	{z} and {c}

Database-Management-System Keys ISRO CS 2014

Question 52 Explanation:

● A candidate key is simply the "shortest" super key. Candidate Key are individual columns in a table that qualifies for uniqueness of each row/tuple.Every table must have at least one candidate key but at the same time can have several.

Question 53

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

Database-Management-System Normalization ISRO CS 2014

Question 53 Explanation:

From the given figure, we can write functional dependencies as
AB→ CDE
C→ B
The candidate keys are "AB" and "AC".
The relation is not in BCNF because the functional dependencies C→ B do not contains the key AB or AC
So, the given relation is in 3NF but not in BCNF

Question 54

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

Database-Management-System Functional-Dependency ISRO CS 2014

Question 54 Explanation:

Given Data,
Schema R(A, B, C, D)
Functional dependencies are A→ B and C→ D
Decomposed Schema is R1(A,B) and R2(C,D)
Dependency preservation decomposition:
It is another property of decomposed relational database schema D in which each functional dependency X → Y specified in F either appeared directly in one of the relation schemas Ri in the decomposed D or could be inferred from the dependencies that appear in some Ri.
Decomposition D={ R1 , R2, R3,,.., ,Rm} of R is said to be dependency-preserving with respect to F if the union of the projections of F on each Ri , in D is equivalent to F.
In other words, R ⊂ join of R1, R1 over X. The dependencies are preserved because each dependency in F represents a constraint on the database. If decomposition is not dependency-preserving, some dependency is lost in the decomposition.
→ R1(A,B) is covered A→ B
→ R2(C,D) is covered C→ D
It is Functional Dependency preserving because both the functional dependencies are covered.
Lossless join:
The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in F+ (where F+ stands for the closure for every attribute or attribute sets in F):
R1 ∩ R2 → R1
R1 ∩ R2 → R2
According to functional dependency R1(A,B) ∩ R2(C,D) = null. There is no common key in both the tables. So, it is not lossless join.

Question 55

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

Database-Management-System Functional-Dependency ISRO CS 2014

Question 55 Explanation:

→ represents functional dependency and
→→ is multivalued dependency.
Functional Dependency:
A → B means that the values of B are determined by the values of A. Two tuples sharing the same values of A will necessarily have the same values of B.
Multivalued dependency: It is a special case of a join dependency, with only two sets of values involved, i.e. it is a binary join dependency.
A multivalued dependency exists when there are at least three attributes (like A,B and C) in a relation and for a value of A there is a well defined set of values of B and a well defined set of values of C. However, the set of values of B is independent of set C and vice versa.

Question 56

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

Database-Management-System UGC-NET JUNE Paper-2

Question 56 Explanation:

Inner Join :
Inner join combines two tables having a common attributes.
While combining it only join the rows of two tables having same value in common attribute.
So, in that way inner join return the records having matching values in both the tables.

Question 57

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.

Database-Management-System SQL UGC-NET JUNE Paper-2

Question 57 Explanation:

→ SQL query, which results titles of the 7 most expensive books.
→ The where clause of outer query will be true for 7 most expensive books.
Note: All aggregate functions except count(*) ignore NULL values in their input collection.

Question 58

In a Hierarchical database, a hashing function is used to locate the

A	Collision
B	Root
C	Foreign Key
D	Records

Database-Management-System B-and-B+-Trees UGC-NET JUNE Paper-2

Question 58 Explanation:

→ A hierarchical database model is a data model in which the data are organized into a tree-like structure. The data are stored as records which are connected to one another through links. A record is a collection of fields, with each field containing only one value. The type of a record defines which fields the record contains.
→ The hierarchical database model mandates that each child record has only one parent, whereas each parent record can have one or more child records.
→ In order to retrieve data from a hierarchical database the whole tree needs to be traversed starting from the root node.
→ In a Hierarchical database, a hashing function is used to locate the root node.

Question 59

Relations produced from E-R Model will always be in

A	1 NF
B	2 NF
C	3 NF
D	4 NF

Database-Management-System ER-Model UGC-NET JUNE Paper-2

Question 59 Explanation:

Relations produced from E-R Model will always be in 3NF.

Question 60

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.

Database-Management-System Transactions UGC-NET JUNE Paper-2

Question 60 Explanation:

Question 61

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

Database-Management-System Normalization UGC-NET JUNE Paper-2

Question 61 Explanation:

Primary key of given relation is “ab”.
And there is a partial dependency exist in given FD’s so the given relation is in 1NF but not in second normal form.

Question 62

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)

Database-Management-System Normalization UGC-NET JUNE Paper-2

Question 62 Explanation:

Here, we have a many to one relationship between between Set(r1) and Set(r2).
→ Elements of Set(r2) can’t identify elements of Set(r1) because one value element in Set(r2) is pointing to more than one element of Set(r1).
→ So, we can’t say Pk(r2) → Pk(r1) but elements of Set(r1) are pointing to exactly one element of Set(r2) so we can say that Pk(r2) → Pk(r1) because r1 is uniquely identifying r2.

Question 63

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.

Database-Management-System Relational-databases UGC-NET JUNE Paper-2

Question 63 Explanation:

When the Database system will store each relation in seperate operating system file then it will become difficult for the administrator to differentiate among the update, delete and insert authorizations and also to keep track of grant options becomes difficult which is a overhead.
So, option 1 is clearly false and option 2 is true.
Option 3: In question it is mentioned that each relation is stored in a separate operating system file.
So, option 3 is true.
Option 4 : Since for each relation there is a seperate operating system file which may use the operating system’s authorization scheme.
So, Operations on the database are speeded up as the authorization procedure is carried out at the operating system level.

Question 64

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

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

Database-Management-System Relational-databases UGC-NET JUNE Paper-2

Question 64 Explanation:

In case of referential integrity Insertion into table containing the foreign key and Deletion from table whose Primary key is referred can cause the violation.

Question 65

___symbol is used to denote derived attribute in ER model

A	Dashed ellipse
B	Square ellipse
C	Ellipse with attribute name undIrected
D	Rectangular box

Database-Management-System Nielit Scentist-B [02-12-2018]

Question 65 Explanation:

Derived attributes are depicted by dashed ellipse.
Example:

Question 66

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

Database-Management-System Nielit Scentist-B [02-12-2018]

Question 66 Explanation:

→ Maximum no. of possible super keys for a relation with n attributes with one candidate key
(only one attribute) = 2n-1
Here, n = 4.
→ So, the possible super keys = 24-1 = 8
→ The super keys are: X, XY, XZ, YZ, XYZ, XYW, YZW, XYZW.

Question 67

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

Database-Management-System Nielit Scentist-B [02-12-2018]

Question 67 Explanation:

Both the statements are true.
(1) TRUE: Lossless, dependency preserving decomposition into 3NF is always possible but not in BCNF. In BCNF lossless is mandatory but dependency preserving is not mandatory.
(ii) TRUE: Any relation with two attributes is in BCNF

Question 68

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

Database-Management-System Nielit Scentist-B [02-12-2018]

Question 68 Explanation:

The above two statements are true.

Question 69

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)

Database-Management-System Nielit Scentist-B [02-12-2018]

Question 69 Explanation:

→ On removal of row (3,4), row (4,3) must also be deleted as they depend on value 3.
→ On removal of row (4,3), row (2,4) and (6,4) must also be deleted as it depends on value 4.
→ As there is no option with row(2,4) and also the question says additional tuples should be deleted. So, option D is eliminated.

Question 70

_____ is NOT a part of the ACID properties.

A	Inconsistency
B	Consistency
C	Atomicity
D	Isolation

Database-Management-System Nielit Scentist-B [02-12-2018]

Question 70 Explanation:

ACID means Atomicity, Consistency ,Isolation and Durability
Atomicity: Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes.
Consistency : Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct.
Isolation : Transactions are often executed concurrently (e.g., reading and writing to multiple tables at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. Isolation is the main goal of concurrency control; depending on the method used, the effects of an incomplete transaction might not even be visible to other transactions.
Durability :Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory.

Question 71

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

Database-Management-System Nielit Scentist-B [02-12-2018]

Question 71 Explanation:

Functional dependencies of the given subrelation R(E,D,A,B) are:
A → B
D → E
E → D
Since A is not determined by any of the given functional dependencies for R(E,D,A,B) so it must be included in the key now check whether “A” is the Primary key or not.
(A)+ = {A}
(AB)+ = {AB}
(AD)+ = {ABDE}
(AE)+ = {ABDE}
So (AD)+ and (AE)+ are the primary keys of R(E,D,A,B).
A → B, in this given FD there exist a partial dependency because here a prime key attribute(attribute which is a part of primary key but not a primary key itself of a function) which is determining a non-key attribute.
So the relation R(E,D,A,B) is not in 2NF .

Question 72

An object can have which of the following multiplicities?

A	Zero
B	More than one
C	One
D	All of the above

Database-Management-System ER-Model Nielit Scientist-B IT 4-12-2016

Question 72 Explanation:

Multiplicity is a definition of cardinality - i.e. number of elements of some collection of elements by providing an inclusive interval of non-negative integers to specify the allowable number of instances of described element. Multiplicity interval has some lower bound and (possibly infinite) upper bound

Question 73

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

Database-Management-System Normalization Nielit Scientist-B IT 4-12-2016

Question 73 Explanation:

The table is in 3NF if and only if both of the following conditions hold:
1. The relation R (table) is in second normal form (2NF)
2. Every non-prime attribute of R is non-transitively dependent on every key of R.

Question 74

Which of the following is a fundamental operation in relational algebra?

A	Set intersection
B	Assignment
C	Natural Join
D	None of the above

Database-Management-System Relational-Algebra Nielit Scientist-B IT 4-12-2016

Question 74 Explanation:

Fundamental operation in relational algebra
1.Select
2.Project
3.Cartesian Product
4.Rename
5.Union
6.Set Difference

Question 75

The primary key is selected from the:

A	Composite keys
B	Determinants
C	Candidate keys
D	Foreign keys

Database-Management-System Normalization Nielit Scientist-B IT 4-12-2016

Question 75 Explanation:

● A candidate key is a column or a row of columns in a table which identifies any database record without utilizing any other data.
● Each table can have one or more candidate keys, but one candidate key is unique, and it is called the primary key.

Question 76

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

Database-Management-System Functional-Dependency Nielit Scientist-B IT 4-12-2016

Question 76 Explanation:

A functional dependency (FD) is a relationship between two attributes, typically between the PK and other non-key attributes within a table. For any relation R, 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
The left side of the above FD diagram is called the determinant, and the right side is the dependent.

Question 77

Which type of statement can execute parameterized queries?

A	PreparedStatement
B	Parameterized Statement
C	ParameterizedStatement and CallableStatement
D	All kinds of Statements

Database-Management-System SQL Nielit Scientist-B IT 4-12-2016

Question 77 Explanation:

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled.
As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.
Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it.

Question 78

Which of the following is a fundamental operation in relational algebra?

A	Set intersection
B	Natural Join
C	Assignment
D	None of the above

Database-Management-System Relational-Algebra Nielit Scientist-B IT 4-12-2016

Question 78 Explanation:

Fundamental operation in relational algebra 1.Select
2.Project
3.Cartesian Product
4.Rename
5.Union
6.Set Difference

Question 79

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

Database-Management-System Normalization Nielit Scientist-B IT 4-12-2016

Question 79 Explanation:

Option A : BCNF is stricter than 3NF. In this all redundancy based on functional dependency has been removed. Option B : Lossless, dependency preserving decomposition into 3NF is always possible. Option C : It is false.
It is not possible to have dependency preserving in BCNF decomposition.
→ Let take an example, 3NF can't be decomposed into BCNF. Option D : It is true.
Let consider two attributes (X, Y).
If (X→Y), X is a candidate key. It is in BCNF and vice-versa.

Question 80

Every Boyce-Codd Normal Form(BCNF) decomposition is

A	dependency preserving
B	not dependency preserving
C	need be dependency preserving
D	none of these

Database-Management-System Functional-Dependency Nielit Scientist-C 2016 march

Question 80 Explanation:

Normalization requires definitely loss less decomposition but not dependency preserving. But BCNF need to be functional dependency.

Question 81

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

Database-Management-System Functional-Dependency Nielit Scientist-C 2016 march

Question 81 Explanation:

Trivial − If a functional dependency (FD) X → Y holds, where Y is a subset of X, then it is called a trivial FD. Trivial FDs always hold.

Question 82

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

Database-Management-System Relational Schema Nielit Scientist-C 2016 march

Question 82 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 83

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

Database-Management-System Functional-Dependency Nielit Scientist-C 2016 march

Question 83 Explanation:

● R1 ∩ R2 = ∅. This makes the decomposition lossless join, as all the attributes are keys, R1 ∩ R2 will be a key of the decomposed relations (lossless condition says the common attribute must be a key in at least one of the decomposed relation).
● Now, even the original relation R is in 3NF (even BCNF) as all the attributes are prime attributes (in fact each attribute is a candidate key). Hence, any decomposition will also be in 3NF (even BCNF).

Question 84

Data warehouse contains___________ data that is never found in operational environment.

A	Scripted
B	Encoded
C	Encrypted
D	Summary

Database-Management-System Data-ware-housing UGC-NET DEC Paper-2

Question 84 Explanation:

Scripted data :
A data that you access using java script is termed as scripted data.
Summary Data :
Summary data means a new record is created to store values in a row of a relation.

Question 85

To overcome difficulties in Readers-Writers problem, which of the following statement/s is/are true?

Writers are given exclusive access to shared objects
Readers are given exclusive access to shared objects
Both readers and writers are given exclusive access to shared objects.

Choose the correct answer from the code given below:

Code

A	1 only
B	Both 2 and 3
C	2 only
D	3 only

Database-Management-System Readers-Writers problem UGC-NET DEC Paper-2

Question 85 Explanation:

In Readers-Writers problem, more than one Reader is allowed to read simultaneously but if a Writer is writing then no other writer or any reader can have simultaneous access to that shared object.
So, Writers are given exclusive access to shared objects.

Question 86

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

Database-Management-System SQL UGC-NET DEC Paper-2

Question 86 Explanation:

The following table is returned as the result of executing FROM and WHERE commands in given query.

Since in query “GROUP BY S.Name” is given so, firstly Group names having same“Name” value and and then perform SUM( ) operation on those values. The below table is returned as the result of given query :

So, the number of rows returned by given query are 2.

Question 87

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

Database-Management-System Data-ware-housing UGC-NET DEC Paper-2

Question 87 Explanation:

Data scrubbing which is also called data cleansing, is the process of amending or removing data in a database that is incorrect, incomplete, improperly formatted, or duplicated.
It is the process carried out before the data is moved into a data warehouse.

Question 88

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

Database-Management-System Relational-Algebra UGC-NET DEC Paper-2

Question 88 Explanation:

EFA⁺ = {EFABCD}
EFB⁺ = {EFABCD}
EFC⁺ = {EFC}
EFD⁺ = {EFDCAB}
So, EFA, EFB, EFD are the keys for the given relation R = (A, B, C, D, E, F).

Question 89

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.

Database-Management-System Transactions UGC-NET DEC Paper-2

Question 89 Explanation:

In log based recovery we must perform 'Redo' operation for those transactions that contains both start and commit log record.
We perform 'undo' operation for those transaction that contains only start but not commit log record.
Therefore we perform 'Redo' of T1 and 'Undo' of T2.
Note: Actually they given option-3 is “ We must redo log record (vi) to set A to 16,500 and then redo log record (ii) and (iii)”. But we found it is wrong. Given correct option instead of wrong one.

Question 90

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

Database-Management-System Clustering UGC-NET DEC Paper-2

Question 90 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.
Ordered Indexing is of two types −Dense Index and Sparse Index

Question 91

________ command is used to remove a relation from an SQL database.

A	Update table
B	Remove table
C	Delete table
D	Drop table

Database-Management-System SQL UGC-NET DEC Paper-2

Question 91 Explanation:

Update table :
Update table command is used to update the content of the table.
Delete table :
Delete table command is used to delete the data stored in the table. When we use Delete command table is not deleted only data stored in the table is deleted.
Drop table :
Using table command we can delete a table/relation.

Question 92

Which one of the following is a key factor for preferring B+-trees to binary search trees for indexing database relations?

Database relations have a large number of records
Database relations are sorted on the primary key
B-trees require less memory than binary search trees
Data transfer from disks is in blocks

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

Database-Management-System B-and-B+-Trees Nielit Scientist-B CS 22-07-2017

Question 92 Explanation:

A B+ tree is an N-ary tree with a variable but often large number of children per node. A B+ tree consists of a root, internal nodes and leaves.
The root may be either a leaf or a node with two or more children.
The primary value of a B+ tree is in storing data for efficient retrieval in a block-oriented storage context — in particular, filesystems.
This is primarily because unlike binary search trees, B+ trees have very high fanout (number of pointers to child nodes in a node,typically on the order of 100 or more), which reduces the number of I/O operations required to find an element in the tree.

Question 93

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.

r1(x); r2(x); w1(x); r3(x); w2(x)
r2(x); r1(x); w2(x); r3(x); w1(x)
r3(x); r2(x); r1(x); w2(x); w1(x)
r2(x); w2(x); r3(x); r1(x); w1(x)

A	1
B	2
C	3
D	4

Database-Management-System Transactions Nielit Scientist-B CS 22-07-2017

Question 93 Explanation:

Question 94

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

Database-Management-System Nielit STA [02-12-2018]

Question 94 Explanation:

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:
1.The data type and the length
2.The NULL value acceptance
3.The allowable values, through techniques like constraints or rules
4.The default value

Question 95

To maintain transactional integrity and database consistency DBMS will use:

A	Triggers
B	Cursors
C	Locks
D	Pointers

Database-Management-System Nielit STA [02-12-2018]

Question 95 Explanation:

→ Locks are used to maintain transactional integrity and database consistency.
→ Concurrency control protocols can be broadly divided into two categories:
1.Lock based protocols
2.Timestamp based protocols

Question 96

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

Database-Management-System Nielit STA [02-12-2018]

Question 96 Explanation:

→ B+ tree in which the maximum number of child nodes is 6. Maximum number of keys 5.
→ Order is Key(5)+1=6.
→ Minimum children that a node can have would be ⌈6/2⌉ =3.
→ Minimum number of keys that a node can have becomes 3-1=2.

Question 97

The primary key of any table is selected from the:

A	Composite keys
B	Foreign keys
C	Candidate keys
D	Alternate keys

Database-Management-System Nielit STA [02-12-2018]

Question 97 Explanation:

→ In the relational model of databases, a primary key is a specific choice of a minimal set of attributes (columns) that uniquely specify a tuple (row) in a relation (table).
→ Informally, a primary key is "which attributes identify a record", and in simple cases are simply a single attribute: a unique id.
→ More formally, a primary key is a choice of candidate key (a minimal superkey); any other candidate key is an alternate key.

Question 98

A lock which required is not acquired by database engine is:

A	Row lock
B	Page lock
C	Table lock
D	Attribute lock

Database-Management-System Nielit STA [02-12-2018]

Question 98 Explanation:

→ Each transaction requests locks of different types on the resources, such as rows, pages, or tables, on which the transaction is dependent.
→ The locks block other transactions from modifying the resources in a way that would cause problems for the transaction requesting the lock.
→ Each transaction frees its locks when it no longer has a dependency on the locked resources.

Question 99

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

Database-Management-System Nielit STA [02-12-2018]

Question 99 Explanation:

→ The parser takes SQL DML statements as input and creates instances of SQL Query Model classes if the SQL is syntactically valid. In addition to the syntactic validation, the parser can also perform a semantic validation.
→ The parser is extensible to support vendor specific dialects and custom source generation.

Question 100

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

Database-Management-System ER-Model Nielit Scientist-B CS 2016 march

Question 100 Explanation:

● Primitive recursive functions are a class of functions that are defined using composition and primitive recursion – described below – as central operations. They are a strict subset of those μ-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.
● Most of the functions normally studied in number theory are primitive recursive.

Question 101

Global locks

A	synchronize access to local resources
B	synchronize access to global resources
C	are used to avoid local locks
D	prevent access to global resources

Database-Management-System Transactions Nielit Scientist-B CS 2016 march

Question 101 Explanation:

● Many of the resources protected by local locks in single-instance Oracle require global exposure in a parallel server database.
● Whenever one of these local locks is needed, a corresponding instance lock must be held as well, to protect the resource across all instances. The instance locks used to protect local locks globally are called global locks

Question 102

In a relational schema, each tuple is divided into fields called

A	relations
B	Domains
C	Queries
D	none of these

Database-Management-System Relational Schema Nielit Scientist-B CS 2016 march

Question 102 Explanation:

● A domain is defined as the set of all unique values permitted for an attribute. For example, a domain of date is the set of all possible valid dates, a domain of integer is all possible whole numbers, a domain of day-of-week is Monday, Tuesday ... Sunday.
● This in effect is defining rules for a particular attribute. If it is determined that an attribute is a date then it should be implemented in the database to prevent invalid dates being entered.

Question 103

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

Database-Management-System Constraints Nielit Scientist-B CS 2016 march

Question 103 Explanation:

● Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.
● Integrity constraints are primary key constraints,Foreign Key Constraints, Check Constraints and so on.
● Check (CHK) constraints can be utilized to check the validity of data entered into particular table columns. Check constraints are used to provide back-end database edits, although edits are commonly found in the front-end application as well.
● A referential integrity constraint is specified between two tables.
● In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.

Question 104

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)

Database-Management-System Relational-Calculus Nielit Scientist-B CS 2016 march

Question 104 Explanation:

The given relational algebra having the two tuples such as t.a=10 and t⋅B = 20
→ (Tuple having A=10) ∩ (Tuple having B=20) is equal to (Tuples having A=10 and B=20)

Question 105

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

Database-Management-System Functional-Dependency Nielit Scientist-B CS 2016 march

Question 105 Explanation:

Here, simple way to solve this question is,
1. First we have to find the right hand side values of a dependencies. Check whether it cover all variables in a relation .
2. In right hand side, CE are missing. It means definitely CE should be there in candidate key.
(CE)⁺ =(A,B,C,D,E,F).
Remaining all are not satisfying candidate key properties.

Question 106

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

Database-Management-System Functional-Dependency Nielit Scientist-B CS 2016 march

Question 106 Explanation:

● First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.
● The Second Normal Form states that it should meet all the rules for 1NF and there must be no partial dependencies of any of the columns on the primary key.
● The questions states that relation consists of automatic values but there no information about prime key.So we don’t about partial dependency.

Question 107

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
E	None of these

Database-Management-System B-and-B+-Trees Nielit Scientist-B CS 2016 march

Question 107 Explanation:

Question and options are wrong. Excluded for evaluation.

Question 108

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

Database-Management-System SQL NieLit STA 2016 March 2016

Question 108 Explanation:

● A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.
● A DML is often a sublanguage of a broader database language such as SQL, with the DML comprising some of the operators in the language.
● Read-only selecting of data is sometimes distinguished as being part of a separate data query language (DQL), but it is closely related and sometimes also considered a component of a DML. some operators may perform both selecting (reading) and writing.
● A popular data manipulation language is that of Structured Query Language (SQL), which is used to retrieve and manipulate data in a relational database.
● Other forms of DML are those used by IMS/DLI, CODASYL databases, such as IDMS and others.

Question 109

A tuple in relation DBMS is a equivalent to

A	Record
B	Field
C	File
D	Database

Database-Management-System Relational-Algebra NieLit STA 2016 March 2016

Question 109 Explanation:

A tuple in relation DBMS is a equivalent to record
Equivalent Database Concepts
Relation → Table
Tuple → Row or record
Attribute → Column or field
Cardinality → Number of rows
Degree → Number of columns
Primary key → Unique identifier
Domain → Pool of legal values

Question 110

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

Database-Management-System B-and-B+-Trees NieLit STA 2016 March 2016

Question 110 Explanation:

Total 5 splitting will occur during 10 successive insertions.
Let’s take 10 successive key values as {1, 2, 3, ... 10} which can cause maximum possible splits.

Question 111

Data warehouse contains___________ data that is never found in operational environment.

A	Scripted
B	Encoded
C	Encrypted
D	Summary

Database-Management-System Data-ware-housing UGC NET CS 2018-DEC Paper-2

Question 111 Explanation:

Scripted data : A data that you access using java script is termed as scripted data.
Summary Data : Summary data means a new record is created to store values in a row of a relation.

Question 112

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

Database-Management-System SQL UGC NET CS 2018-DEC Paper-2

Question 112 Explanation:

The following table is returned as the result of executing FROM and WHERE commands in given query.

Since in query “GROUP BY S.Name” is given so, firstly Group names having same“Name” value and and then perform SUM() operation on those values. The below table is returned as the result of given query :

So the number of rows returned by given query are 2.

Question 113

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.

Database-Management-System Data-ware-housing UGC NET CS 2018-DEC Paper-2

Question 113 Explanation:

Data scrubbing which is also called data cleaning, is the process of amending or removing data in a database that is incorrect, incomplete, improperly formatted, or duplicated.
It is the process carried out before the data is moved into a data warehouse.

Question 114

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

Database-Management-System Functional-Dependency UGC NET CS 2018-DEC Paper-2

Question 114 Explanation:

EFA ⁺ = {EFABCD}
EFB ⁺ = {EFABCD}
EFC ⁺ = {EFC}
EFD ⁺ = {EFDCAB}
So EFA, EFB, EFD are the keys for the given relation R=(A, B, C, D, E, F).

Question 115

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.

Database-Management-System Transactions UGC NET CS 2018-DEC Paper-2

Question 115 Explanation:

In log based recovery we must perform 'Redo' operation for those transactions that contains both start and commit log record.
We perform 'undo' operation for those transaction that contains only start but not commit log record.
Therefore we perform 'Redo' of T1 and 'Undo' of T2.
Note: Actually they given option-3 is “ We must redo log record (vi) to set A to 16,500 and then redo log record (ii) and (iii)”. But we found it is wrong. Given correct option instead of wrong one.

Question 116

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

Database-Management-System Clustering UGC NET CS 2018-DEC Paper-2

Question 116 Explanation:

Question 117

________ command is used to remove a relation from an SQL database.

A	Update table
B	Remove table
C	Delete table
D	Drop table

Database-Management-System SQL UGC NET CS 2018-DEC Paper-2

Question 117 Explanation:

Update table : Update table command is used to update the content of the table.
Delete table : Delete table command is used to delete the data
stored in the table. When we use Delete command table is not deleted only data stored in the table is deleted.
Drop table : Using table command we can delete a table/relation.

Question 118

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

Database-Management-System Normalization Nielit Scientist-B CS 4-12-2016

Question 118 Explanation:

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.
Most commonly used normal forms
First normal form(1NF)
Second normal form(2NF)
Third normal form(3NF)
Boyce & Codd normal form (BCNF)

Question 119

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

Database-Management-System Functional-Dependency Nielit Scientist-B CS 4-12-2016

Question 119 Explanation:

Question 120

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

Database-Management-System Functional-Dependency Nielit Scientist-B CS 4-12-2016

Question 120 Explanation:

A covers B, if every FD in B can be inferred from A.
A covers B if A ⁺ ⊆ B⁺ (+ means closure ).
Every set of functional dependencies has a canonical cover.

Question 121

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

Database-Management-System Functional-Dependency Nielit Scientist-B CS 4-12-2016

Question 121 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.

Question 122

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

Database-Management-System Functional-Dependency Nielit Scientist-B CS 4-12-2016

Question 122 Explanation:

If there is more than one key for relation schema in DBMS then each key in relation schema is classified as candidate key.
→ In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that:
1.The relation does not have two distinct tuples (i.e. rows or records in common database language) with the same values for these attributes (which means that the set of attributes is a superkey)
2.There is no proper subset of these attributes for which (1) holds (which means that the set is minimal).
→ Candidate keys are also variously referred to as primary keys, secondary keys or alternate keys. The constituent attributes are called prime attributes. Conversely, an attribute that does not occur in ANY candidate key is called a non-prime attribute.
→ Since a relation contains no duplicate tuples, the set of all its attributes is a superkey if NULL values are not used. It follows that every relation will have at least one candidate key. The candidate keys of a relation tell us all the possible ways we can identify its tuples. As such they are an important concept for the design of database schema.

Question 123

In tuple relational calculus P1 → p2 is equivalent to

A	~P1 V P2
B	P1 V p2
C	P1 ⋀ P2
D	P1 ⋀ ~P2

Database-Management-System Relational-Calculus Nielit Scientist-B IT 22-07-2017

Question 123 Explanation:

In tuple relational calculus P1→ P2 is equivalent to ¬P1 V P2. (The logical implication expression A -> B, meaning if A then B,is equivalent to ¬A V B)

Question 124

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)\|)

Database-Management-System Relational-Algebra Nielit Scientist-B IT 22-07-2017

Question 124 Explanation:

● 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.
● 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.

Question 125

If an SQl query involves NOT,AND,OR with no parenthesis

A	NOT will be evaluated first; AND will be evaluated second;OR will be evaluated last
B	NOT will be evaluated first; OR will be evaluated second;AND will be evaluated last
C	AND will be evaluated first; OR will be evaluated second;NOT will be evaluated last
D	The order of occurrence determines the order of evaluation

Database-Management-System SQL Nielit Scientist-B IT 22-07-2017

Question 125 Explanation:

Table: SQL Operator Precedence

The above table shows the SQL operator precedence.

Question 126

'AS' clause is used in SQL for

A	Selection operation
B	rename operation
C	Join Operation
D	Projection Operation

Database-Management-System SQL Nielit Scientist-B IT 22-07-2017

Question 126 Explanation:

● AS is a keyword in SQL that allows you to rename a column or table using an alias.
● Syntax: SELECT column_name AS 'Alias' FROM table_name;

Question 127

Related fields in a database are grouped to form a

A	data file
B	data record
C	menu
D	Bank

Database-Management-System Relational-databases Nielit Scientist-B IT 22-07-2017

Question 127 Explanation:

● In a database, a record (sometimes called a row) is a group of fields within a table that are relevant to a specific entity.
● For example, in a table called customer contact information, a row would likely contain fields such as: ID number, name, street address, city, telephone number and so on.

Question 128

A table joined with itself is called

A	join
B	self join
C	outer join
D	Equi join

Database-Management-System Relational-Algebra Nielit Scientist-B IT 22-07-2017

Question 128 Explanation:

● A self join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY.
● To join a table itself means that each row of the table is combined with itself and with every other row of the table.

Question 129

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)

Database-Management-System Relational-Algebra Nielit Scientist-B IT 22-07-2017

Question 129 Explanation:

For maximum:
If there is common attribute in R and S, and every row of R match with every row of S then total no. of tuples will be mn.

Question 130

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)

Database-Management-System ER-Model ISRO CS 2015

Question 130 Explanation:

Given diagram is ER diagram , So any object, for example, entities, attributes of an entity, relationship sets, and attributes of relationship sets, can be represented with the help of an ER diagram.
We need to convert ER diagram to relational model.
There are two entities in the ER diagram named Person and Exam with attributes which are represented in ellipse.
Table name: Person

Now Create table for a relationship
Add the primary keys of all participating Entities as fields of table with their respective data types.
If relationship has any attribute, add each attribute as field of table.
Declare a primary key composing all the primary keys of participating entities.
Declare all foreign key constraints.
Table for “Qualification” relationship

Question 131

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.

T1 start

T1 B old=1200 new=10000

T1 M old=0 new=2000

T1 commit

T2 start

T2 B old=10000 new=10500

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.

Database-Management-System Transactions ISRO CS 2015

Question 131 Explanation:

→ When the database system crashes after the transactions have committed then we need to redo the log records.
→ And if the database system crashes before the transactions have committed then we need to undo the log records.
So from above theory we can say that option (B) is the correct Solution.

Question 132

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

Database-Management-System Indexing ISRO CS 2015

Question 132 Explanation:

Total number of records in the data base=n
Given block will hold either 3 records or 10 key pointers.
Number of blocks to store “n” records = n/3
Number of blocks to store dense file index = n/10
Total blocks to hold data file and dense index = n/3 + n/10 = 13n/30 blocks.

Question 133

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

Database-Management-System Functional-Dependency ISRO CS 2015

Question 133 Explanation:

Here, simple way to solve this question is,
First we have to find the right hand side values of a dependencies. Check whether it cover all variables in a relation .
In right hand side, CE are missing. It means definitely CE should be there in candidate key.
(CE)+=(A,B,C,D,E,F).
Remaining all are not satisfying candidate key properties.

Question 134

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.

Database-Management-System SQL ISRO CS 2015

Question 134 Explanation:

If we execute the given query the output will be S3 and S4 i.e., names of all suppliers who didn’t supply blue parts which is option (A).
Option (D) says names of suppliers who didn’t supply only blue parts that means, supplier should supply all other parts for sure and shouldn’t supply blue part.

Question 135

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

Database-Management-System SQL ISRO CS 2015

Question 135 Explanation:

Query-1: select avg (Salary) from Emp:
This query will give average salary of all the all employes.
Query-2:SELECT Deptt FROM Emp WHERE sex = 'M' GROUP by Dept Having avg (Salary)
Average salary of employee who is male where we grouping by department
Query-2 > Query-1
Average salary of male employee is more than the average salary of the organization

Question 136

If a node has K children in B-tree, then the node contains exactly _____ keys.

A	K²
B	K – 1
C	K + 1
D	K^1/2

Database-Management-System B-and-B+-Trees ISRO CS 2015

Question 136 Explanation:

According to Knuth's definition, a B-tree of order m is a tree which satisfies the following properties: Every node has at most m children. Every non-leaf node (except root) has at least ceil(m/2) children. The root has at least two children if it is not a leaf node. A non-leaf node with k children contains k−1 keys. All leaves appear in the same level, and carry information.

Question 137

If D₁, D₂…D_n are domains in a relational model, then the relation is a table, which is a subset of

A	D₁⊕D₂⊕…⊕D_n
B	D₁xD₂x…xD_n
C	D₁∪D₂∪…∪D_n
D	D₁∩D₂∩…∩D_n

Database-Management-System Relational-Calculus ISRO CS 2015

Question 137 Explanation:

In D₁,D₂,..,D_n are domains in a relational model, then the relation is a table, which s a subset of D₁xD₂x..xD_n

Question 138

The maximum length of an attribute of type text is

A	127
B	255
C	256
D	It is variable

Database-Management-System Relational-Table ISRO CS 2015

Question 138 Explanation:

Correct answer is D. But actual key they given Option C is correct one.

Question 139

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)

Database-Management-System Normalization JT(IT) 2018 PART-B Computer Science

Question 139 Explanation:

BCNF properties
1. BCNF is free from redundancy.
2. If a relation is in BCNF, then 3NF is also also satisfied.
3. Every Binary Relation ( a Relation with only 2 attributes ) is always in BCNF.
4. Sometimes going for BCNF form may not preserve functional dependency.

Question 140

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)

Database-Management-System Relational-databases JT(IT) 2018 PART-B Computer Science

Question 140 Explanation:

DELETE/FROM/WHERE command is used for removing one or more tuples from a table(relation).
DROP command is used for dropping entire table.

Question 141

Referential integrity constraints works on the concept of:

A	Secondary key
B	Super key
C	Foreign key
D	Primary key

Database-Management-System Constraints JT(IT) 2018 PART-B Computer Science

Question 141 Explanation:

Referential integrity constraints works on the concept of foreign key.
Referential Integrity Rules
→ A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).
→ Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values.
The rules associated with referential integrity are:
1. Restrict: Disallows the update or deletion of referenced data.
2. Set to Null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
3. Set to Default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
4. Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
5. No Action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred.

Question 142

State whether TRUE or FALSE

⁺

A	(i)-False, (ii)-True
B	(i)-True, (ii)-True
C	(i)-False, (ii)-False
D	(i)-True, (ii)-False

Database-Management-System B-and-B+-Trees JT(IT) 2018 PART-B Computer Science

Question 142 Explanation:

→ Secondary Index does not have any impact on how the rows are actually organized in data blocks. They can be in any order. The only ordering is w.r.t the index key in index blocks.
→ Indexing can be of the following types −
1. 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.
2. 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.
3. Clustering Index − Clustering index is defined on an ordered data file. The data file is ordered on a non-key field.
→ B⁺ tree
The structure of leaf nodes of a B⁺ tree is quite different from the structure of internal nodes of the B⁺ tree. Since data pointers are present only at the leaf nodes, the leaf nodes must necessarily store all the key values along with their corresponding data pointers to the disk file block, in order to access them. Moreover, the leaf nodes are linked to provide ordered access to the records.

Question 143

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

Database-Management-System Functional-Dependency JT(IT) 2018 PART-B Computer Science

Question 143 Explanation:

Option (C) is the correct option because a decomposition can be lossless if and only if there exists a common attribute between the decomposed relations which is either a candidate key or a key attribute in any of the decomposed tables.
For lossless decomposition:
R₁ ∩ R₂ → R₁ (OR) R₁ ∩ R₂ → R₁ - R₂
R₁ ∩ R₂ → R₂ (OR) R₁ ∩ R₂ → R₂ - R₁

Question 144

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

Database-Management-System Normalization Nielit Scientific Assistance IT 15-10-2017

Question 144 Explanation:

Candidate key is ab.
Since all a, b, c, d are atomic. So the relation is in 1NF.
Checking the FD's
a → c
b → d
We can see that there is partial dependencies. So it is not 2NF.

Question 145

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

Database-Management-System Relational-Algebra Nielit Scientific Assistance IT 15-10-2017

Question 145 Explanation:

● Aggregate Computation is a a function to a collection of values to generate a single result.
● Multiplication means cartesian product
●Transitive closure:
Given a domain D, let binary relation R be a subset of D×D. The transitive closure R⁺ of R is the smallest subset of D×D that contains R and satisfies the following condition:
∀ x∀y∀z((x, y ) ∈ R ⁺ ⋀ ( y, z ) ∈ R⁺ ⇒ ( x, z ) ∈ R ⁺ )

Question 146

When transaction T _i requests a data item currently held by T_j , T_j 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

Database-Management-System Transactions Nielit Scientific Assistance IT 15-10-2017

Question 146 Explanation:

Wait-Die method
In this method, if a transaction requests to lock a resource (data item), which is already held with a conflicting lock by another transaction, then one of the two possibilities may occur −
● If TS(T _i ) < TS(T_j ) − that is T_i , which is requesting a conflicting lock, is older than T_j − then T_i is allowed to wait until the data-item is available.
● If TS(T _i ) > TS(t_j ) − that is T_i is younger than T_j − then T _i dies. T _i is restarted later with a random delay but with the same timestamp.
This method allows the older transaction to wait but kills the younger one.

Question 147

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

Database-Management-System Transactions Nielit Scientific Assistance IT 15-10-2017

Question 147 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.

Question 148

Given relations R(w,x) and S(y,z) the result of SELECT DISTINCT w,x from R,S

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

Database-Management-System SQL Nielit Scientific Assistance IT 15-10-2017

Question 148 Explanation:

r has no duplicate, if r can have duplicates it can be remove in the final state. s in non-empty if s is empty then r*s becomes empty.

Question 149

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

Database-Management-System Nielit Scientist-B 17-12-2017

Question 149 Explanation:

→ Sometimes, BCNF may not be functional dependency preserving.
→ lossless join should be compulsory in any normal form.

Question 150

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

Database-Management-System Nielit Scientist-B 17-12-2017

Question 150 Explanation:

Conservative 2PL prevents deadlocks
. The difference between 2PL and C2PL is that C2PL's transactions obtain all the locks they need before the transactions begin. This is to ensure that a transaction that already holds some locks will not block waiting for other locks.
→ In heavy lock contention, C2PL reduces the time locks are held on average, relative to 2PL and Strict 2PL, because transactions that hold locks are never blocked.
→ In light lock contention, C2PL holds more locks than is necessary, because it is hard to tell what locks will be needed in the future, thus leads to higher overhead.
→ Also, a transaction will not even obtain any locks if it cannot obtain all the locks it needs in its initial request. Furthermore, each transaction needs to declare its read and write set (data items to be read/written during transaction), which is not always possible. Because of these limitations, C2PL is not used very frequently.

Question 151

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

Database-Management-System Nielit Scientist-B 17-12-2017

Question 151 Explanation:

BCNF is a stronger version 3NF. So straight from definition of BCNF every relation in BCNF will also be in 3NF.

Question 152

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

Database-Management-System Nielit Scientist-B 17-12-2017

Question 152 Explanation:

FD set F={ A→CE, B→D, AE→D }
AB+={A,B,C,D,E} it clearly shows that right side, there is no B. So, definitely candidate key require B.
→ The functional dependency B→D violates 2NF requirement. It is a partial dependency, D is partially dependent on B, where B is a proper subset of a candidate key.

Question 153

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

Database-Management-System Nielit Scientist-B 17-12-2017

Question 153 Explanation:

Question 154

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

Database-Management-System Nielit Scientist-B 17-12-2017

Question 154 Explanation:

Given, functional dependencies are F={A→ BC, C→ D}
Finding candidate key of given relation is relation is
A+={A,B,C} because we don’t have an A element in right hand side.
A→ BC dependency A is the candidate key and also part of FD. So, it is lossless.
C→ D is following Transitive dependency. So, it never become 3NF.
According to given choices, A is the correct choice.

Question 155

______ 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

Database-Management-System Relational-Algebra KVS 22-12-2018 Part-B

Question 155 Explanation:

→ A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.
→ A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
→ The SQL OUTER JOIN returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition only.

Question 156

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

Database-Management-System Functional-Dependency KVS 22-12-2018 Part-B

Question 156 Explanation:

From the dependencies A→ B and B→ H, we can imply A→ H by using transitive dependency

Question 157

Consider the relation Emp-Dept with SSn as key

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)

Database-Management-System Relational Schema KVS 22-12-2018 Part-B

Question 157 Explanation:

Inserting an employee without SSn because SSn is key to the table.

Question 158

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

Database-Management-System Data-Cleaning KVS 22-12-2018 Part-B

Question 158 Explanation:

→ 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.

Question 159

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

Database-Management-System SQL KVS 22-12-2018 Part-B

Question 159 Explanation:

→ The GROUP BY clause is a SQL command that is used to group rows that have the same values.
→ HAVING clause is used to filter summarized data or grouped data.
→ WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows
→ As a rule of thumb, use WHERE before GROUP BY and HAVING after GROUP → BY.
→ The order of execution of Clauses i.e FROM > WHERE > GROUP BY > HAVING > DISTINCT > SELECT > ORDER BY.

Question 160

____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

Database-Management-System Constraints KVS 22-12-2018 Part-B

Question 160 Explanation:

→ To ensure entity integrity, it is required that every table have a primary key.
→ Neither the primary key nor any part of it can contain null values. This is because null values for the primary key mean we cannot identify some rows.
→ Referential integrity requires that a foreign key must have a matching primary key or it must be null. This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables. It means the reference from a row in one table to another table must be valid.

Question 161

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

Database-Management-System SQL KVS 22-12-2018 Part-B

Question 161 Explanation:

→ A database system provides a data definition language to specify the database schema and a data manipulation language to express database queries and updates.
→ Storage definition language is to specify the internal schema. This language may specify the mapping between two schemas.
→ A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.

Question 162

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

Database-Management-System Functional-Dependency KVS 22-12-2018 Part-B

Question 162 Explanation:

Rules for determining functional dependency hold or not : → If LHS of a functional dependency is not repeating any value then you. you can say that LHS of a functional dependency is determining RHS of a functional dependency.
→ If LHS of a functional dependency is repeating then see the value of RHS. If values of RHS are same for repeated value of LHS then you can say that LHS of a functional dependency is determining RHS of a functional dependency else not determining.
Now if you see the given options then only option(C) is satisfying above two rules.

Question 163

Given relations R(w,x) and S(y,z) the result of SELECT DISTINCT w,x from R<S

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

Database-Management-System SQL Nielit Scientific Assistance CS 15-10-2017

Question 163 Explanation:

r has no duplicate, if r can have duplicates it can be remove in the final state. s in non-empty if s is empty then r*s becomes empty.

Question 164

E-R model uses this symbol to represent weak entity set?

A	Dotted rectangle
B	Diamond
C	Doubly outlined rectangle
D	None of these

Database-Management-System ER-Model Nielit Scientific Assistance CS 15-10-2017

Question 164 Explanation:

→ A weak entity is an entity that cannot be uniquely identified by its attributes alone.
→ 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.

Question 165

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

Database-Management-System ER-Model Nielit Scientific Assistance CS 15-10-2017

Question 165 Explanation:

→ Primitive recursive functions are a class of functions that are defined using composition and primitive recursion
→ They are a strict subset of those μ-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.
→ A total recursive function is also a partial recursive function

Question 166

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

Database-Management-System Transactions Nielit Scientific Assistance CS 15-10-2017

Question 166 Explanation:

Question 167

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

Database-Management-System SQL Nielit Scientific Assistance CS 15-10-2017

Question 167 Explanation:

This query counts the number of employees who get more than the minimum salary. From the 10 employees, you need to exclude all those employees who are getting the minimum salary. Since the SALARY column is UNIQUE, only one employee will be getting the minimum salary.

Question 168

When transaction T _i requests a data item currently held by T_j , T _j 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

Database-Management-System Transactions Nielit Scientific Assistance CS 15-10-2017

Question 168 Explanation:

Wait-die scheme :
It is a non-preemptive technique for deadlock prevention. 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 is older than T_j ), otherwise T_i is rolled back (dies).
→ In this scheme, if a transaction requests to lock a resource (data item), which is already held with a conflicting lock by another transaction, then one of the two possibilities may occur
1. If TS(T_i ) < TS(T_j ) − that is T_i , which is requesting a conflicting lock, is older than T _j −then T_i is allowed to wait until the data-item is available.
2. If TS(T _i ) > TS(T_j ) − that is T_i is younger than T_j − then T_i dies. T _i is restarted later with a random delay but with the same timestamp.
→ This scheme allows the older transaction to wait but kills the younger one.
Example:
Suppose that transaction T22, T23, T24 have time-stamps 5, 10 and 15 respectively. If T22 requests a data item held by T23 then T22 will wait. If T24 requests a data item held by T23, then T24 will be rolled back.

Question 169

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”;

Database-Management-System SQL KVS DEC-2013

Question 169 Explanation:

→ Condition specified in WHERE clause is used while fetching data (rows) from table, and data which doesn't pass the condition will not be fetched into result set,
→ HAVING clause is used to filter summarized data or grouped data.
→ In the question, we require average salary of employees whose grade is E4. So we require grouped data.

Question 170

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”

Database-Management-System SQL KVS DEC-2013

Question 170 Explanation:

●Describes either the columns in a table or the current values, as well as the default values, for the stage properties for a table.
● DESCRIBE can be abbreviated to DESC.

Question 171

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

Database-Management-System SQL KVS DEC-2013

Question 171 Explanation:

●In SQL, a view is a virtual table based on the result-set of an SQL statement.
● A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
● You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Question 172

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

Database-Management-System SQL KVS DEC-2013

Question 172 Explanation:

→ The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
→ The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
→ To add a column in a table, use the following syntax:
→ ALTER TABLE table_name ADD column_name datatype;

Question 173

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

Database-Management-System Normalization KVS DEC-2013

Question 173 Explanation:

BCNF
● A relation R is in BCNF if and only if it is in 3NF and no any prime attribute is transitively dependent on the primary key.
● An attribute C is transitively dependent on attribute A if there exist an attribute B such that A->B and B->C.
4NF
● A table is in the 4NF if it is in BCNF and has no multivalued dependencies.

Question 174

In an RDBMS relationships between tables are created by using

A	Alternate keys
B	Foreign keys
C	Candidate keys
D	Composite keys

Database-Management-System Relational-databases KVS DEC-2013

Question 174 Explanation:

● A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table.
● In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table

Question 175

A program is a/an____ entity

A	Active
B	Passive
C	Dormant
D	Hyperactive

Database-Management-System ER-Model KVS DEC-2013

Question 175 Explanation:

→ A process is more than a program code. A process is an 'active' entity as oppose to program which consider to be a 'passive' entity.
→ Being a passive, a program is only a part of proces s

Question 176

The following diagram depicts

A	Two-level model
B	Many to one model
C	One to one model
D	Many to many model

Database-Management-System ER-Model KVS DEC-2013

Question 176 Explanation:

→ The 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.

Question 177

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

Database-Management-System Normalization KVS DEC-2013

Question 177 Explanation:

→ A relation R is in 4NF if and only if the following conditions are satisfied:
1. It should be in the Boyce-Codd Normal Form (BCNF).
2. The table should not have any Multi-valued Dependency.

Question 178

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

Database-Management-System SQL KVS DEC-2013

Question 178 Explanation:

It undoes some or all database changes made during the current transaction.

Question 179

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

Database-Management-System Relational-Algebra KVS DEC-2017

Question 179 Explanation:

→ If the tables R and S contains common attributes and value of that attribute in each tuple in both tables are same, then the natural join will result n*m tuples as it will return all combinations of tuples.
Consider following two tables

→ Result of natural join R * S (If domain of attribute C in the two tables are same )
→ You can see both R and S contain the attribute C whose value is 2 in each and every tuple. Table R contains 3 tuples, Table S contains 2 tuples, where Result table contains 3*2=6 tuples.
Note: While performing a natural join, if there were no common attributes between the two relations, Natural join will behave as Cartesian Product.

Question 180

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

Database-Management-System SQL KVS DEC-2017

Question 180 Explanation:

Only logic here is, when we are using keyword IN there must be OR keyword.

Question 181

Which of the following statements best describes the main reasons for normalising 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)

Database-Management-System Normalization KVS DEC-2017

Question 181 Explanation:

Normalising relational database reasons are
1. To remove data anomalies (insertion,update,delete,anomalies)
2. To save space on disk(It happens because of avoiding redundancy)

Question 182

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

Database-Management-System Constraints KVS DEC-2017

Question 182 Explanation:

→ Entity integrity ensures that each row of a table is uniquely identified, so that it can be retrieved separately if necessary. The value set of a primary key is unique; no two values may be the same.
→ The rules of entity integrity state that no primary key can be null and that no change can render the primary key null. These rules guarantee that every row of a table is accessible, whether you're retrieving data or modifying it. You can retrieve each row separately by specifying the value of a primary key.
→ Domain integrity ensures that all the data items in a column fall within a defined set of valid values. Each column in a table has a defined set of values. When you limit the value assigned to an instance of that column (an attribute), you are enforcing domain integrity.
→ Domain integrity enforcement can be as simple as choosing the correct data type and length for a column.

Question 183

Which of the following is not a transaction management SQL command?

A	Rollback
B	Commit
C	Select
D	Savepoint

Database-Management-System Transactions KVS DEC-2017

Question 183 Explanation:

Transaction Control Language(TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.
1. Rollback
2. Savepoint
3. Commit

Question 184

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

Database-Management-System Relational-databases KVS DEC-2017

Question 184 Explanation:

Concurrency control in RDBMS is important to ensure data integrity when updates(insert/delete/modify) occur to the database in a multi user environment

Question 185

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

Database-Management-System Relational-databases KVS DEC-2017

Question 185 Explanation:

Option A: True: depends upon the query, it will give the response
Option B & C: True: Foreign key constraints are referential integrity constraints. A primary key uniquely identifies a row in a table.
Option D: False: Null won’t reduce space requirements in tables.

Question 186

What do data warehouse support?

A	Operational database
B	OLAP
C	OLTP
D	OLAP and OLTP

Database-Management-System Data-ware-housing KVS DEC-2017

Question 186 Explanation:

Data warehousing will support OLAP and OLTP.
→ OLTP (Online Transaction Processing) is characterized by a large number of short online transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF).
→ OLAP (Online Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).

Question 187

Buying and selling goods over the internet is called:

A	Euro-conversion
B	Hyper-marketing
C	Cyber-Selling
D	E-Commerce

Database-Management-System E-Commerce KVS DEC-2017

Question 187 Explanation:

→ Online shopping is a form of electronic commerce which allows consumers to directly buy goods or services from a seller over the Internet using a web browser.
→ Consumers find a product of interest by visiting the website of the retailer directly or by searching among alternative vendors using a shopping search engine, which displays the same product's availability and pricing at different e-retailers.

Question 188

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

Database-Management-System ER-Model KVS DEC-2017

Question 188 Explanation:

→ An entity set that does not have sufficient attribute to form a key is termed as weak entity set.
→ It means a relation(or table) doesn’t have primary key. We are calling that relation is weak entity set.

Question 189

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

Database-Management-System Relational-Algebra KVS DEC-2017

Question 189 Explanation:

→While performing a natural join, if there were no common attributes between the two relations, Natural join will behave as Cartesian Product.
→ Example: If the tables R and S contains common attributes and value of that attribute in each tuple in both tables are same, then the natural join will result n*m tuples as it will return all combinations of tuples.

Question 190

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

Database-Management-System SQL KVS DEC-2017

Question 190 Explanation:

→ Normally we want print all records from database, we use SQL command is SELECT * from filename.
→ Suppose we want to return the number of records, it means count total number of records from relation(or table).
SELECT COUNT(*) FROM persons

Question 191

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

Database-Management-System Relational-databases KVS DEC-2017

Question 191 Explanation:

→ Cardinality of a relation means the number of tuples.
→ The degree of relationship (also known as cardinality) is the number of occurrences in one entity which are associated (or linked) to the number of occurrences in another.
There are three degrees of relationship, known as:
1. one-to-one (1:1)
2. one-to-many (1:M)
3. many-to-many (M:N)

Question 192

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

Database-Management-System Relational-databases KVS DEC-2017

Question 192 Explanation:

Three levels of database architecture:
1. Physical Level
2. Conceptual Level
3. External Level

→ Mapping is the process of transforming request response between various database levels of architecture.
→ Mapping is not good for small database, because it takes more time.
→ In External / Conceptual mapping, DBMS transforms a request on an external schema against the conceptual schema.
→ In Conceptual / Internal mapping, it is necessary to transform the request from the conceptual to internal levels.
1. Physical Level
Physical level describes the physical storage structure of data in database.It is also known as Internal Level. This level is very close to physical storage of data. At lowest level, it is stored in the form of bits with the physical addresses on the secondary storage device. At highest level, it can be viewed in the form of files.The internal schema defines the various stored data types. It uses a physical data model.
2. Conceptual Level
Conceptual level describes the structure of the whole database for a group of users. It is also called as the data model. Conceptual schema is a representation of the entire content of the database. These schema contains all the information to build relevant external records. It hides the internal details of physical storage.
3. External Level
External level is related to the data which is viewed by individual end users. This level includes a no. of user views or external schemas. This level is closest to the user. External view describes the segment of the database that is required for a particular user group and hides the rest of the database from that user group.

Question 193

__ 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

Database-Management-System Data-ware-housing KVS DEC-2017

Question 193 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 194

When is EOQ in inventory control?

A	Economics of quantity
B	Economics occuring quantity
C	Economics over quantity
D	Economics order quantity

Database-Management-System EOQ KVS DEC-2017

Question 194 Explanation:

→ The Economic Order Quantity (EOQ) is the number of units that a company should add to inventory with each order to minimize the total costs of inventory such as holding costs, order costs, and shortage costs.
→ The EOQ is used as part of a continuous review inventory system in which the level of inventory is monitored at all times and a fixed quantity is ordered each time the inventory level reaches a specific reorder point.

Question 195

To add attributes to an existing relation __ commands is used:

A	Update table
B	Alter table
C	Change table
D	Add table

Database-Management-System SQL JT(IT) 2016 PART-B Computer Science

Question 195 Explanation:

ALTER TABLE: changing a relation schema.
SQL allows the owner of the database relation to change it by:
1. Adding one or more attributes to the relation.
2. Removing one or more attributes from the relation.
3. Adding one or more constraints to the relation.
4. Removing one or more constraints from the relation.

Question 196

A relation that is not of connected model but is made visible to a user as a virtual relation is called:

A	Table
B	Query
C	View
D	Joined relations

Database-Management-System SQL JT(IT) 2016 PART-B Computer Science

Question 196 Explanation:

→ In SQL, a view is a virtual table based on the result-set of an SQL statement.
→ A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
→ You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
CREATE VIEW Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note:
A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

Question 197

In transaction management od 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

Database-Management-System Transactions JT(IT) 2016 PART-B Computer Science

Question 197 Explanation:

• The database should be durable enough to hold all its latest updates even if the system fails or restarts.
• If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data.
• If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

Question 198

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

Database-Management-System Relational-Calculus JT(IT) 2016 PART-B Computer Science

Question 198 Explanation:

branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-city)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
→ The loan-number, branch-name, and amount for loans of over $1200
{t | t ∈ loan ∧ t[amount] > 1200}

Question 199

A	Deadlock
B	Starvation
C	Conflict
D	Lock failure

Database-Management-System Transactions JT(IT) 2016 PART-B Computer Science

Question 199 Explanation:

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 starvation.
Reasons of Starvation:
1. If waiting scheme for locked items is unfair. ( priority queue )
2. Victim selection. ( same transaction is selected as a victim repeatedly )
3. Resource leak. (Via denial-of-service attack)

Question 200

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

Database-Management-System Data-ware-housing KVS 30-12-2018 Part B

Question 200 Explanation:

→A data mart is a structure / access pattern specific to data warehouse environments, used to retrieve client-facing data.
→The data mart is a subset of the data warehouse and is usually oriented to a specific business line or team.
→Data Warehouse Bus Matrix is a diagram or tool developed by Kimball group to describe data warehouse design blueprint. It is component or consider as part of data warehouse architecture. It is visual picture of business process and conformed dimensions

Question 201

Consider the relation Emp_Dept with SSn as key

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

Database-Management-System Relational Schema KVS 30-12-2018 Part B

Question 201 Explanation:

→If we delete one employee details means the department details of particular employee is not available. So statement b is correct.
→With inserting and updating operations there is no loss of department information

Question 202

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

Database-Management-System Functional-Dependency KVS 30-12-2018 Part B

Question 202 Explanation:

→Functional dependency is a relationship that exists when one attribute uniquely determines another attribute.
→If R is a relation with attributes X and Y, a functional dependency between the attributes is represented as X→Y, which specifies Y is functionally dependent on X. Here X is a determinant set and Y is a dependent attribute. Each value of X is associated with precisely one Y value.

Question 203

____ 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

Database-Management-System Relational Schema KVS 30-12-2018 Part B

Question 203 Explanation:

→A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data.
→The environment of database is said to be instance. A database instance or an ‘instance’ is made up of the background processes needed by the database software. These processes usually include a process monitor, session monitor, lock monitor, etc.
→A database instance (Server) is a set of memory structure and background processes that access a set of database files.

Question 204

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

Database-Management-System Constraints KVS 30-12-2018 Part B

Question 204 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.
→Functional dependency is a relationship that exists when one attribute uniquely determines another attribute.

Question 205

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

Database-Management-System SQL KVS 30-12-2018 Part B

Question 205 Explanation:

→The GROUP BY clause is a SQL command that is used to group rows that have the same values.
→HAVING clause is used to filter summarized data or grouped data.

Question 206

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

Database-Management-System SQL KVS 30-12-2018 Part B

Question 206 Explanation:

→In order to perform aggregate operations , we need to consider duplicate values also.
→Removing duplication tuple is costly operation in which we need to access all the tuples in order to find the duplicate tuples.

Question 207

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

Database-Management-System SQL KVS 30-12-2018 Part B

Question 207 Explanation:

→Dnum is common field of two tables which may be foreign key.
→Inserting new row in the first table gives some null entries in the second table which is violation of referential integrity

Question 208

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

Database-Management-System SQL UGC NET CS 2017 Nov- paper-2

Question 208 Explanation:

VIEW is a virtual table based on the result set of a SQL statement.
→ In SQL, a view is a virtual table based on the result-set of an SQL statement.
→ A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
→ You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
CREATE VIEW Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

Question 209

In SQL, __________ is an Aggregate function.

A	SELECT
B	CREATE
C	AVG
D	MODIFY

Database-Management-System SQL UGC NET CS 2017 Nov- paper-2

Question 209 Explanation:

Aggregate function is AVG. The AVG() function returns the average value of a numeric column.

Question 210

Match the following with respect to RDBMS :

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)

Database-Management-System Relational-databases UGC NET CS 2017 Nov- paper-2

Question 210 Explanation:

→ Entity Integrity won’t support duplicate rows in a table
→ Domain Integrity enforces valid entries for a column
→ Referential integrity rows can’t be deleted which are used by other records
→ User defined integrity enforces some specific business rule that do not fall into entity or domain

Question 211

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

Database-Management-System Relational-databases UGC NET CS 2017 Nov- paper-2

Question 211 Explanation:

Normal forms in order to reduce data redundancy and improve data integrity. Normal forms to prevent modification anomalies.
Example anomalies:
1. Insertion anomaly
2. Deletion and Updation anomaly

Question 212

__________ SQL command changes one or more fields in a record.

A	LOOK-UP
B	INSERT
C	MODIFY
D	CHANGE

Database-Management-System SQL UGC NET CS 2017 Nov- paper-2

Question 212 Explanation:

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
Syntax:ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Question 213

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

Database-Management-System Data-warehouse-and-data-mining UGC NET CS 2017 Nov- paper-2

Question 213 Explanation:

Question 214

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

Database-Management-System SQL UGC NET CS 2017 Jan -paper-2

Question 214 Explanation:

VARCHAR is variable length and CHAR is fixed length.
Given, varchar(20) and has value ‘Ram’ it means 3 spaces.
char(20) and has value ‘Sita’ but char is fixed spaces. It will take 20 spaces.

Question 215

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

Database-Management-System Constraints UGC NET CS 2017 Jan -paper-2

Question 215 Explanation:

Primary key, every table have at least one attribute as “primary key”. No primary key should ever have null values.
Statement-1: TRUE: Here, instructor ID is primary key. So, they mentioned it is not null.
Statement-2: TRUE: No two citizens have same Adhar-Id. It is clear example of primary key.
Statement-3: FALSE: Budget of a company must be zero. It is violated constraint of “not null”. Because entity integrity constraints should not accept not null but referential integrity constraints will accept null value.

Question 216

Let M and N be two entities in an E-R diagram with simple single value attributes.
R ₁ and R₂ are two relationship between M and N, where as
R ₁ is one-to-many and R ₂ is many-to-many.
The minimum number of tables required to represent M, N, R ₁ and R₂ in the relational model are _______.

A	4
B	6
C	7
D	3

Database-Management-System ER-Model UGC NET CS 2017 Jan -paper-2

Question 216 Explanation:

R ₁ and R₂ two relationships between M and N
R ₁ is one to many.
R ₂ is many to many.
→ M and N have separate table because they need to store multiple values.
→ R ₂ also have separate table by considering Primary keys M and N as foreign keys.
→ R ₁ is converted to many side table i.e., N as Primary key and M as Foreign key.
So, totally we need 3 tables to store the value.

Question 217

Consider a schema R(MNPQ) and functional dependencies M → N, P → Q. Then the decomposition of R into R₁ (MN) and R₂ (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.

Database-Management-System Functional-Dependency UGC NET CS 2017 Jan -paper-2

Question 217 Explanation:

Definition of lossless decomposition: Let R be the relational schema decomposed into R₁
and R ₂ . Given decomposition is lossless only if
1. R ₁ U R₂ =R
2. R ₁ U R₂ =φ
3. R ₁ ∩ R₂ → R₁ (or) R₁ ∩ R₂ → R₂
→ In this schema, there is no common key attribute between R ₁ and R ₂ . So, this relation is lossy relation.
Definition of Functional dependency preserving:
→ Let R be the relational schema with functional dependency set F is decomposed into R ₁ , R ₂ ,..,R _n with functional dependency sets F ₁ ,F₂ ,...,F_n respectively. In general F ₁ ,F₂ ,...,F_n can be ⊆ F.
So, dependencies are preserved in the given decomposition.

Question 218

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 of the above

Database-Management-System B-and-B+-Trees UGC NET CS 2017 Jan -paper-2

Question 218 Explanation:

Excluded for evaluation. No record pointer is given.

Question 219

________ 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

Database-Management-System Relational-databases UGC NET CS 2017 Jan -paper-2

Question 219 Explanation:

→ Data mining refers loosely to the process of semi-automatically analyzing large databases to find useful patterns.
→ Data mining is the process of discovering patterns in large data sets involving methods at the intersection of machine learning, statistics, and database systems.
→ Data mining is the analysis step of the "knowledge discovery in databases" process, or KDD

Question 220

DBMS provides the facility of accessing data from a database through

A	DDL
B	DML
C	DBA
D	Schema

Database-Management-System Data-models UGC NET CS 2016 Aug- paper-2

Question 220 Explanation:

The DBMS provides a set of operations or a language called the data manipulation language (DML) for manipulations include retrieval, insertion, deletion, and modification of the data.
Examples of DML:
1. SELECT – is used to retrieve data from a database.
2. INSERT – is used to insert data into a table.
3. UPDATE – is used to update existing data within a table.
4. DELETE – is used to delete records from a database table
(delete one row at a time and can be roll backed)

Question 221

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.

Database-Management-System Relational-databases UGC NET CS 2016 Aug- paper-2

Question 221 Explanation:

→ There are many small Relational database schema in the database system. If we want to execute a query then it may require multiple relation access.
→ So to avoid this one solution is to have only one big relation so that number of relation access can be reduced.
→ This solution leads to redundancy of data stored in database and various inert, delete, update anomalies. So the solution of these problems is Normalisation using functional dependency. So option B,C,D are clearly correct according to above explanation.
→ Option A is not correct because normalisation does not reduce the number of joins required to satisfy a query it only tries to eliminate redundancy and anomalous using functional dependency.

Question 222

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

Database-Management-System Relational-databases UGC NET CS 2016 Aug- paper-2

Question 222 Explanation:

Option(A) is correct. NULL values can be used to opt a tuple out of enforcement of a foreign key.
Option(B) is correct. Suppose that table T has only one candidate key. If Q is in 3NF, then it is also in BCNF.
Option (C) is correct because the main difference between Project operator and SELECT keyword is that SELECT keyword will return Duplicate values if the exist in the result of a query but Project Operator Do not return Duplicate values if the exist in the result of the query.

Question 223

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., t1 is definitely not equal to t2 ) , 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

Database-Management-System ER-Model UGC NET CS 2016 Aug- paper-2

Question 223 Explanation:

In the given E-R diagram there is Many-To-Many relationship between entity P and Q it means single value in P can relate to Multiple values in Q and vice-versa. And there is One-To-One relationship between S and T it means single value in S can relate to single value in T and vice-versa .
In table1: there is One to many relationship between S and T because "s1" in S attribute related with two values "t1" and "t2" in T attribute. so this table is violating One-to-One relationship of S and T as shown in E-R diagram so it can't be the relationship set of given E-R diagram.
In Table 2: There is one to one relationship between S and T and In P and Q also many-to many relationship constraints are not violated. So it could be the relationship set of given E-R diagram.
In Table 3: "p1" in P attribute is related with two values in Q attribute so many to many relationship constraints are not violate here and S and T attributes are also obeying one-to-one relationship constraints. So it could be the relationship set of given E-R diagram.

Question 224

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;

Database-Management-System SQL UGC NET CS 2016 Aug- paper-2

Question 224 Explanation:

The aggregate functions can't be used without Group By clause.
Common aggregate functions include : COUNT,AVG,MAX,MIN,SUM

Question 225

_______ 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

Database-Management-System Data-ware-housing UGC NET CS 2016 Aug- paper-2

Question 225 Explanation:

→ Data warehouse is subject oriented, integrated, time variant, nonvolatile collection of data in support of management decisions.
→ A data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis, and is considered a core component of business intelligence.
→ DWs are central repositories of integrated data from one or more disparate sources. They store current and historical data in one single place that are used for creating analytical reports for workers throughout the enterprise.

Question 226

In Data mining, classification rules are extracted from _______.

A	Data
B	Information
C	Decision Tree
D	Database

Database-Management-System Data-mining UGC NET CS 2016 Aug- paper-2

Question 226 Explanation:

→ Classification rules are extracted from decision tree. The paths from root to leaf represent classification rules. → A decision tree is a decision support tool that uses a tree-like model of decisions and their possible consequences, including chance event outcomes, resource costs, and utility.

Question 227

In Data mining, ______ is a method of incremental conceptual clustering.

A	STRING
B	COBWEB
C	CORBA
D	OLAD

Database-Management-System Data-mining UGC NET CS 2016 Aug- paper-2

Question 227 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. There are four basic operations COBWEB employs in building the classification tree.
1. Merging Two Nodes
2. Splitting a node
3. Inserting a new node
4. Passing an object down the hierarchy
→ Common Object Request Broker Architecture (CORBA) is an architecture and specification for creating, distributing, and managing distributed program objects in a network. It allows programs at different locations and developed by different vendors to communicate in a network through an "interface broker." CORBA was developed by a consortium of vendors through the Object Management Group (OMG), which currently includes over 500 member companies.
→ STRING and OLAD is irrelevant options.

Question 228

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

Database-Management-System Relational-databases UGC NET CS 2016 July- paper-2

Question 228 Explanation:

→ The constraint that no key attribute (column) may be NULL is referred to as Entity Integrity.
→ Referential integrity to hold in a relational database, any column in a base table that is declared a foreign key can only contain either null values or values from a parent table's primary key or a candidate key.
→ A multivalued dependency exists when there are at least three attributes (like X,Y and Z) in a relation and for a value of X there is a well defined set of values of Y and a well defined set of values of Z. However, the set of values of Y is independent of set Z and vice versa.
→ A functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

Question 229

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

Database-Management-System Relational-databases UGC NET CS 2016 July- paper-2

Question 229 Explanation:

Statement-I is TRUE because a VIEW of a table allows you to display updated version of the particular subset of the data in a database. A VIEW is a virtual table based on the result set of SQL statement.
Statement-II is FALSE because E-R diagram useful to Conceptually model concepts.
Statement-III is FALSE because update anomaly is a data inconsistency results from partial update or data redundancy.
Statement-IV is FALSE because SQL is not a procedural language. A language is procedural when we call procedures( like functions, subroutine) but in SQL we don't do any such calls.

Question 230

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.

Database-Management-System Relational-databases UGC NET CS 2016 July- paper-2

Question 230 Explanation:

→ NULL values are used in a relation when the value of column of a particular row is either Unknown or does Not Exist.
→ Sometimes the a column of a row does not contain any value or may have zero value in that case also NULL values are used to avoid any kind of confusion regarding the values of the column. So, Option (B),(C) and (D) are the cases when NULL values are used in a relation.
Option(A) is incorrect because Primary key of a relation is a key which uniquely identify each tuple of a relation so it can't have NULL values.

Question 231

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

Database-Management-System SQL UGC NET CS 2016 July- paper-2

Question 231 Explanation:

→ Dropping the relation means to delete the content of a table and free up the space allocated to a table.
→ Deleting the tuples means keeping the space allocated to a table/relation but deleting the data relation contains.
→ Now DELETE command is used to delete the tuples of a relation while DROP command is used to delete the schema as well as tuples of a relation.
Hence option (C) is correct.

Question 232

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., d1 is definitely not equal to d2 ), 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

Database-Management-System Candidate-key UGC NET CS 2016 July- paper-2

Question 232 Explanation:

→Given table will find that attribute {B},{A,D},{C,D} can uniquely identify each tuple of the given table.
→ So, we can say that {B},{A,D},{C,D} are the candidate keys of the given relation. And we know that a candidate key is a minimal key using which each tuple of a relation can be uniquely identified And the super set of a candidate key is a Super Key instead of candidate Key.
→ Since {B} is a candidate key so {B,C} is a Super Key, not the Candidate key of the given relation.
→ So, the key which can't be the candidate key of given relation is {B,C}.

Question 233

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.

Database-Management-System Data-ware-housing UGC NET CS 2016 July- paper-2

Question 233 Explanation:

TRUE: Business intelligence and Data warehousing is used for forecasting and Data mining.
TRUE: Business intelligence and Data warehousing is used for analysis of large volumes of sales data.

Question 234

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.

Database-Management-System Data-ware-housing UGC NET CS 2016 July- paper-2

Question 234 Explanation:

→ Data scrubbing is an error correction technique that uses a background task to periodically inspect main memory or storage for errors, then correct detected errors using redundant data in the form of different checksums or copies of data.
→ Data scrubbing reduces the likelihood that single correctable errors will accumulate, leading to reduced risks of uncorrectable errors.

Question 235

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 where city = ‘%GAR%’;
B	Select * from customers where city = ‘$GAR$’;
C	Select * from customers where city like ‘%GAR%’;
D	Select * from customers where city as ‘%GAR’;

Database-Management-System SQL UGC NET CS 2015 Dec- paper-2

Question 235 Explanation:

In above question a specific pattern "GAR" is given for pattern matching.
In SQL "LIKE" clause is used for pattern matching. For LIKE clause we have two wild cards:
1. "%" which represents any sequence of "0" or more characters.
2. "_" is used to replace a single character.
So, Option C is the correct answer because they have used LIKE clause along with "%" which indicates any number of character can be present before and after "GAR" pattern.

Question 236

Match the following database terms to their function:

A	(iv)(iii)(i)(ii)
B	(ii)(iv)(i)(iii)
C	(ii)(iv)(iii)(i)
D	(iv)(iii)(ii)(i)

Database-Management-System Match-the-following UGC NET CS 2015 Dec- paper-2

Question 236 Explanation:

Normalization: Normalization is used to reduce the data redundancy in the database
Data Dictionary: Data dictionary contains meta describing database structure.
Referential Integrity: Referential integrity enforces match of primary key to foreign key
External Schema: Define view(s) of the database for particular user(s)

Question 237

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

Database-Management-System ER-Model UGC NET CS 2015 Dec- paper-2

Question 237 Explanation:

A template for a group of things with the same set of characteristics that may exist in the real world is best description of entity type.
Except option-D, remaining specifying their attributes.

Question 238

Data which improves the performance and accessibility of the database are called:

A	Indexes
B	User Data
C	Application Metadata
D	Data Dictionary

Database-Management-System Constraints UGC NET CS 2015 Dec- paper-2

Question 238 Explanation:

→ An index is a data which improves the performance and accessibility of the database. An index is a copy of selected columns of data from a table that can be searched very efficiently that also includes a low-level disk block address or direct link to the complete row of data it was copied from.
→ To create the application metadata XML file containing details of all the Business Process Server applications that need to be packaged and published.
→ A data dictionary (or) metadata repository is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format".

Question 239

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

Database-Management-System Functional-Dependency UGC NET CS 2015 Dec- paper-2

Question 239 Explanation:

Since no functional dependency is having ABD in their right hand side, so every key should include ABD as a part of them to become a candidate key.
Now check whether ABD is itself a candidate key or not by finding the closure.
(ABD) ⁺ = { ABDCEFG }
since ABD can identify each attribute of the given relation uniquely.
So, ABD is the candidate key.

Question 240

__________ 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

Database-Management-System Data-models UGC NET CS 2015 Dec- paper-2

Question 240 Explanation:

→ OLAP system is market oriented and is used for data analysis by knowledge workers including Managers, Executives and Analysts.
→ OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas, with new applications emerging, such as agriculture.

Question 241

__________ 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

Database-Management-System Data-ware-housing UGC NET CS 2015 Dec- paper-2

Question 241 Explanation:

→ Top-Down is also known as Inmon’s Top Down Approach, data warehouse is built first. Inmon defines a data warehouse as a centralized repository for the entire enterprise. Dimensional data marts are created only after the complete data warehouse has been created.
→ Bottom-Up is also called as Kimball’s bottom up approach, the most important business aspects or departments, data marts are created first. A data mart provide a thin view into the organisational data and addresses a single business area. These data marts are then integrated into larger data warehouse to build a complete data warehouse. The integration of data marts is implemented using Kimball’s data warehousing architecture which is also known as data warehouse bus (BUS).

Question 242

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

Database-Management-System SQL UGC NET CS 2015 Jun- paper-2

Question 242 Explanation:

An Assertion is a condition that we wish the database to always satisfy. Domain constraints, functional dependency and referential integrity are special forms of assertion.
The syntax of Assertion in SQL is:
create assertion assertion-name check predicate

Question 243

Which of the following concurrency protocol ensures both conflict serializability and freedom from deadlock?
(a) Z-Phase Locking
(b) Timestamp ordering

A	Both (a) and (b)
B	(a) only
C	(b) only
D	Neither (a) nor (b)

Database-Management-System Transactions UGC NET CS 2015 Jun- paper-2

Question 243 Explanation:

Timestamp ordering provides a schedule which is conflict serializable and free from deadlock but Z-phase locking provides a schedule which is conflict serializable only.

Question 244

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)

Database-Management-System Keys UGC NET CS 2015 Jun- paper-2

Question 244 Explanation:

Drop Table cannot be used to drop a Table referenced by Foreign Key because deleting a table referenced by a foreign key will violate the referenced key constraint.

Question 245

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)

Database-Management-System Indexing UGC NET CS 2015 Jun- paper-2

Question 245 Explanation:

Drawbacks of using a file system are data redundancy, data inconsistency, difficulty in accessing data, data isolation and data integrity. So to overcome all these disadvantages Database management system are used because it involves specifying the data types, structures and constraints of the data to be stored in the database. Constraints that helps in overcoming the disadvantages of file system are
1. Entity constraint: No primary key should have NULL values.
2. Key constraint: No two rows in a table should be same.
3. Domain constraint: A column in a table should not be multi valued or composite
4. Referential integrity constraint: A foreign key should contain the subset values of primary key.

Question 246

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

Database-Management-System ER-Model UGC NET CS 2015 Jun- paper-2

Question 246 Explanation:

A weak entity set do not have any primary key using which we can identify it uniquely. So to uniquely identify a weak entity set it must be associated with its owner entity set and must have total participation in relationship with its owner entity set.

In above figure "Employee" can have many dependents and a dependent can belongs to only one employee. Now two dependents can have same names. So "Dependent" table don't have any primary key to uniquely identify each dependent uniquely. So, it is a weak entity set and "Employee" entity is having "Emp No" as its primary key. Now using "Emp No. along with Name" in "dependent" relation each dependent can be uniquely identify. So we associated "Dependent" set with "Employee" set.

Question 247

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.

Database-Management-System ER-Model UGC NET CS 2015 Jun- paper-2

Question 247 Explanation:

A weak entity always have total participation relationship with strong entity. It means that every entry in weak entity set will participate in relationship with strong entity and if a strong entity on which weak entity depends is deleted then total participation condition is exploited. So to avoid this Weak entities are deleted automatically when their strong entity is deleted. So option (A) is correct.
Option (B) is correct. A weak entity is an entity which do not have any primary key to uniquely identify its each entry and this leads to data duplication and data inconsistency in the weak entity. So to avoid this problem the key of a strong entity is duplicated into weak entity to uniquely identify each entry of a weak entity. So option (B) is correct.
Option (C) is also correct. Because until the key attributes of a owner strong entity of a weak entity are not included each entry of weak entity can't be uniquely identified.
Option (D) is false because tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity set.

In above example the key attribute of "Employee" (which is a strong entity) is partitioning the the tuples of "Dependent" (which is a weak entity) based on the tuples belongs to Emp No. "1" and tuples belongs to Emp No. "2".
So option(D) is false

Question 248

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

Database-Management-System ER-Model UGC NET CS 2004 Dec-Paper-2

Question 248 Explanation:

The E-R model is expressed in terms of:
Entities: An entity is a object in Database. An entity is expressed using a rectangular box in an E-R diagram. Example: person, car etc.
Attributes: Nouns which describe nouns. Attributes helps to describe an entity in a better way.
Example: If person is an entity then person name, address, phone number can be its attributes.
Relationship: Any verb which you are going to describe. Example: A person owns a car. Here "owns" is a verb describing relationship between two entities(person and car).

In above diagram Person and Car are two entities
"owns" is defining the relationship between Person and Car.
name, address are the attributes of "Person" entity and number and model are the attributes of "Car" entity.
Note: Excluded for evaluation, Given wrong options.

Question 249

The entity type on which the __________ type depends is called the identifying owner.

A	Strong entity
B	Relationship
C	Weak entity
D	E - R

Database-Management-System ER-Model UGC NET CS 2004 Dec-Paper-2

Question 249 Explanation:

A weak entity of a relation do not have any primary key to uniquely identify it's each tuple. To avoid this problem, the weak entity is associated with the keys of it's identifying owner which is a strong entity.

Question 250

Match the following :

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	None of the above

Database-Management-System Normalization UGC NET CS 2004 Dec-Paper-2

Question 250 Explanation:

→ 2NF ensures no partial dependency in the relation.
→ A relation is in 3NF if it is in eliminates transitive dependency from the relation.
→ 4NF eliminates multivalued dependency from a relation.
→ 5NF eliminates join dependency from a table.

Question 251

A schema describes :

A	data elements
B	records and files
C	record relationship
D	all of the above

Database-Management-System Databases UGC NET CS 2005 Dec-Paper-2

Question 251 Explanation:

In a relational database, the 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 252

In a relational schema, each tuple is divided in fields called :

A	Relations
B	Domains
C	Queries
D	All the above

Database-Management-System Relational Schema UGC NET CS 2005 Dec-Paper-2

Question 252 Explanation:

A domain describes the set of possible values for a given attribute(column), and can be considered a constraint on the value of the attribute. Mathematically, attaching a domain to an attribute means that any value for the attribute must be an element of the specified set.
For example in relation given below there are 4 attributes where domain of attributes "Name" and "Course" is character string while domain of "Roll-No" and "Marks" attributes is integer value.
Now the domains of each attribute is dividing a tuple(row) in in fields of sequence Character string, integer value, character string and integer value.

Question 253

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)

Database-Management-System File-System UGC NET CS 2005 Dec-Paper-2

Question 253 Explanation:

File locking is a mechanism that restricts access to a computer file by allowing only one user or process to access it in a specific time.

Question 254

An Entity - relationship diagram is a tool to represent :

A	Data model
B	Process model
C	Event model
D	Customer model

Database-Management-System ER-Model UGC NET CS 2005 june-paper-2

Question 254 Explanation:

A data model is an abstract model that organizes elements of data and standardizes how they relate to one another and to properties of the real world entities. For instance, a data model may specify that the data element representing a car be composed of a number of other elements which, in turn, represent the color and size of the car and define its owner. The "data model" may be defined using the entity-relationship data model.

Question 255

Multi-valued dependency among attribute is checked at which level ?

A	2 NF
B	3 NF
C	4 NF
D	5 NF

Database-Management-System Normalization UGC NET CS 2005 june-paper-2

Question 255 Explanation:

→ Multivalued dependency among attribute is checked in 4NF.
→ A relation is in 4NF if it is in BCNF and has no multivalued dependency.
→ A relation will contain Multivalued dependency if there exists a dependency A → B such that for a single value of A, multiple values of B exists.

Question 256

A WINDOW into a portion of a database is :

A	Schema
B	View
C	Query
D	Data Dictionary

Database-Management-System Databases UGC NET CS 2005 june-paper-2

Question 256 Explanation:

→ A view in database returns a portion of database based on the condition given in the SQL query.
→ View of a database gives the updated data stored in the database. A view returns a virtual view of database. It can join and simplify multiple tables into a single virtual table.

Question 257

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

Database-Management-System ER-Model UGC NET CS 2006 Dec-paper-2

Question 257 Explanation:

→ In deferred update techniques updates are stored to a local storage instead of storing onto the disk. In this technique, a updates is made first later when the transaction gets committed all entries are made in the log file(which is stored into disk) later.
→ If a transaction fails before reaching its commit point, it will not have changed the database in any way so UNDO is not needed. It may be necessary to REDO the effect of the operations that are recorded in the local transaction workspace, because their effect may not yet have been written in the database. Hence, a deferred update is also known as the No-undo/redo algorithm.

Question 258

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

Database-Management-System Relational-databases UGC NET CS 2006 Dec-paper-2

Question 258 Explanation:

TRUE: Hierarchical data model suffers from data independence
TRUE: Network model suffers from data independence
FLASE: Relational model suffers only from logical data independence
TRUE: Relational model suffers only from physical data independence

Question 259

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

Database-Management-System Transactions UGC NET CS 2006 Dec-paper-2

Question 259 Explanation:

The two phase protocol in DBMS refers to a protocol in which locking and unlocking of shared data includes two phases:
1. Growing Phase: In this locks are acquired according to the need as the transaction proceeds.
2. Shrinking Phase: In this release locks as transaction starts reaching to completion.
A transaction can obtain locks but can't release locks in growing phase. Similarly a transaction can release locks but can't obtain lock in shrinking phase.
There are various types of two phase locking mechanism which can provide a conflict serializable schedule but can't provide a schedule which is deadlock free and can be implemented practically.

Question 260

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

Database-Management-System Functional-Dependency UGC NET CS 2006 June-Paper-2

Question 260 Explanation:

Here, Attribute A and D are not present in the right hand side of any production so every key should include AD to be a candidate key.
Step-1: Check whether AD could be a candidate key or not.
(AD) + = {A,D,C,B,F,E}
Step-2: AD can uniquely identifies each attribute. So, AD is the candidate key for given relation.

Question 261

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

Database-Management-System Transactions UGC NET CS 2006 June-Paper-2

Question 261 Explanation:

→ Immediate updates as a recovery protocol is preferable when writes are more than reads.
→ In this technique, when a transaction issues an update command, the database on disk can be updated immediately, without any need to wait for the transaction to reach its commit point.

Question 262

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

Database-Management-System Transactions UGC NET CS 2006 June-Paper-2

Question 262 Explanation:

Option (A) is correct because 2-Phase locking protocol provides a schedule which is conflict serializable but do not deadlock free schedule.
Option (B) is true because of timestamp ordering of the transactions, this protocol suffer more number of aborts.
Option (C) is true. Whenever some transaction T tries to issue a read_item(X) or a write_item(X) operation, the basic Time out algorithm compares the Timestamp of T with read_TS(X) and write_TS(X) to ensure that the timestamp order of transaction execution is not violated. If this order is violated, then transaction T is aborted and resubmitted to the system as a new transaction with a new timestamp.
If T is aborted and rolled back, any transaction T1 that may have used a value written by T must also be rolled back. Similarly, any transaction T2 that may have used a value written by T1 must also be rolled back, and so on. This effect is known as cascading rollback.
But in case of 2-Phase locking, to avoid cascading aborts Strict two-phase locking is introduced in which a transaction holds an exclusive locks until the transaction commits/aborts. Rigorous two-phase locking is even more stricter in which both Exclusive lock and shared lock are hold by the transaction until the transaction commits/abort.

Question 263

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

Database-Management-System Databases UGC NET CS 2006 June-Paper-2

Question 263 Explanation:

The database schema and conceptual organization of the entire database is viewed by Database Administration(DBA).

Question 264

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

Database-Management-System SQL UGC NET CS 2014 Dec-Paper-2

Question 264 Explanation:

→ The DIVISION operation is defined for convenience for dealing with queries that involve universal quantification or the all condition. For a tuple 't' to appear in the result T of the DIVISION, the values in ‘t’ must appear in R in combination with every tuple in S.
→ Note that in the formulation of the DIVISION operation, the tuples in the denominator relation S restrict the numerator relation R by selecting those tuples in the result that match all values present in the denominator. The DIVISION operation can be expressed as a sequence of π, ×, and – operations as follows:
T1 ← πY(R)
T2 ← πY((S × T1) – R)
T ← T1 – T2

Question 265

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	II and III are correct.
C	Only III is correct.
D	Only I is correct.

Database-Management-System SQL UGC NET CS 2014 Dec-Paper-2

Question 265 Explanation:

NATURAL JOIN requires that the two join attributes (or each pair of join attributes) have the same name in both relations. If this is not the case, a renaming operation is applied first.
OUTER JOIN: A set of operations, called outer joins, were developed for the case where the user wants to keep all the tuples in R, or all those in S, or all those in both relations in the result of the JOIN, regardless of whether or not they have matching tuples in the other relation.
In SQL, the same name can be used for two (or more) attributes as long as the attributes are in different relations. If this is the case, and a multi table query refers to two (or more) attributes with the same name, we must qualify the attribute name with the relation name to prevent ambiguity. The ambiguity of attribute names also arises in the case of queries that refer to the same relation twice. In this case, we are required to declare alternative relation names, called aliases or tuple variables.
An alias can follow the keyword "AS" , as shown below
SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;
In above query relation names E and S, called aliases or tuple variables, for the EMPLOYEE relation.
From above explanation it is clear that only option (D) is correct.

Question 266

What kind of mechanism is to be taken into account for converting a weak entity set into strong entity set in entity-relationship diagram ?

A	Generalization
B	Aggregation
C	Specialization
D	Adding suitable attributes

Database-Management-System ER-model UGC NET CS 2014 Dec-Paper-2

Question 266 Explanation:

→ Entity types that do not have key attributes of their own are called weak entity types. In contrast,regular entity types that do have a key attribute are called strong entity types. Entities belonging to a weak entity type are identified by being related to specific entities from another entity type in combination with one of their attribute values. We call this other entity type the identifying or owner entity type, and we call the relationship type that relates a weak entity type to its owner the identifying relationship of the weak entity type.
→ A weak entity type always has a total participation constraint (existence dependency) with respect to its identifying relationship because a weak entity can not be identified without an owner entity.

Question 267

The best normal form of relation scheme R(A, B, C, D) along with the set of functional dependencies F = {AB → C, AB → D, C → A, D → B} is

A	Boyce-Codd Normal form
B	Third Normal form
C	Second Normal form
D	First Normal form

Database-Management-System Normalization UGC NET CS 2014 Dec-Paper-2

Question 267 Explanation:

AB → C
AB → D
C → A
D → B
A + ={A}
B + ={B}
C + ={C,A}
D + ={D,B}
Since single attributes can't determine all the attributes of relation. Find closure of combination of two attributes to check whey could be the key for the relation or not.
AB + ={A,B,C,D}
AC + ={A,C}
AD + ={A,D,B,C}
BC + ={B,C,A,D}
AB + ={A,B,C,D}
AC + ={A,C}
AD + ={A,D,B,C}
BC + ={B,C,A,D}
BD + ={B,D}
CD + ={C,D,B,A}
So, AB,AD,BC,CD are the candidate keys for the given relation. All attributes are key attributes and since last two functional dependencies are violating BCNF property (that LHS if a functional dependency should be a super key) so the given relation is in 3NF only.

Question 268

Identify the minimal key for relational scheme R(A, B, C, D, E) with functional dependencies F = {A → B, B → C, AC → D}

A	A
B	AE
C	BE
D	CE

Database-Management-System Functional-dependency UGC NET CS 2014 Dec-Paper-2

Question 268 Explanation:

Given functional dependencies are F={A → B, B → C, AC → D}
Step-1: Attribute A and E are not present in the right hand side of any production. So, every key should include AE to be a candidate key.
Step-2: To check whether AE could be a candidate key or not.
(AE) + = {A,B,C,D,E}
Since AE can uniquely identify each attribute and we can say this is the candidate key for given relation.

Question 269

Factless fact table in a data warehouse contains

A	only measures
B	only dimensions
C	keys and measures
D	only surrogate keys

Database-Management-System Data-ware-housing UGC NET CS 2014 Dec-Paper-2

Question 269 Explanation:

→ Factless fact table in a data warehouse contains only surrogate keys.
→ The factless fact table is a fact table that does not contain any facts. There are two kinds of factless fact tables:
1. Factless fact table describes event or activity.
2. Factless fact table describes a condition, eligibility or coverage.

Question 270

The upper bound and lower bound for the number of leaves in a B-tree of degree K with height h is given by :

A	K^h and 2⌈K/2⌉^h–1
B	K*h and 2⌊K/2⌋^h–1
C	K^h and 2⌊K/2⌋^h–1
D	K*h and 2⌈K/2⌉^h–1

Database-Management-System B-and-B+-Trees UGC NET CS 2014 June-paper-2

Question 270 Explanation:

→ The upper bound for the number of leaves in a B-tree of degree K with height h is K^h
→ The lower bound for the number of leaves in a B-tree of degree K with height h is 2⌈K/2⌉^h–1

Question 271

Usage of Preemption and Transaction Rollback prevents ______.

A	Unauthorised usage of data file
B	Deadlock situation
C	Data manipulation
D	File preemption

Database-Management-System Transactions UGC NET CS 2013 Sep-paper-2

Question 271 Explanation:

Usage of Preemption and Transaction Rollback prevents deadlock.

Question 272

Views are useful for _____ unwanted information, and for collecting together information from more than one relation into a single view.

A	Hiding
B	Deleting
C	Highlighting
D	All of the above

Database-Management-System SQL UGC NET CS 2013 Sep-paper-2

Question 272 Explanation:

→ Views are useful for hides unwanted information, and for collecting together information from more than one relation into a single view.
→ A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object.
Views advantages over tables:
→Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table. →Views can join and simplify multiple tables into a single virtual table.
→Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.
→Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.
→Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
→Depending on the SQL engine used, views can provide extra security.

Question 273

Thomas-write rule is ______.

A	Two phase locking protocol
B	Timestamp ordering protocol
C	One phase locking protocol
D	Sliding window proto

Database-Management-System Transactions UGC NET CS 2013 Sep-paper-2

Question 273 Explanation:

→ The Thomas write rule is a rule in timestamp-based concurrency control. It can be summarized as ignore outdated writes.
→ The Thomas write rule is applied in situations where a predefined logical order is assigned to transactions when they start.

Question 274

In DML, RECONNCT command cannot be used with

A	OPTIONAL Set
B	FIXED Set
C	MANDATOR Set
D	All of the above

Database-Management-System SQL UGC NET CS 2012 Dec-Paper-2

Question 274 Explanation:

→ The RECONNECT command can be used with both OPTIONAL and MANDATORY sets, but not with FIXED sets.
→ The RECONNECT command moves a member record from one set instance to another set instance of the same set type. It cannot be used with FIXED sets because a member record cannot be moved from one set instance to another under the FIXED constraint.

Question 275

The maximum number of keys stored in a B-tree of order m and depth d is

A	md+1–1
B	(md+1–1) / (m–1)
C	(m–1) (md+1–1)
D	(md–1) / (m–1)
E	None of the above

Database-Management-System B-and-B+-Trees UGC NET CS 2012 Dec-Paper-2

Question 275 Explanation:

Order of a B-tree represents the number of children a node can have and we know the number of keys is always equals to the (Order of B-tree) - 1

So here each of the ‘m’ children is having (m-1) keys.
Hence total no. of keys = m¹ (m-1) keys
(3) Similarly, for a B-tree of order ‘d’ total no. of keys possible = m^d (m-1) keys

Question 276

Given a Relation POSITION (Posting- No, Skill), then query to retrieve all distinct pairs of posting-nos. requiring skill is

A	Select p.posting-No, p.posting No from position p where p.skill = p.skilland p.posting-No < p.posting-No
B	Select p1.posting-No, p2.posting- No from position p1, position p2 where p1.skill = p2.skill
C	Select p1.posting-No, p2posting-No from position p1, position p2 where p1.skill = p2.skill and p1.posting-No < p2.posting-No
D	Select p1.posting-No, p2.posting- No from position p1, position p2 where p1.skill = p2.skill and p1.posting-No = p2.posting-No

Database-Management-System SQL UGC NET CS 2012 Dec-Paper-2

Question 276 Explanation:

Option (A) is incorrect because No self join is not possible without aliasing. Without aliasing it will through an error.
Option(B) is not correct because it is not resulting into distinct pairs of posting-nos.

Question 277

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

Database-Management-System Relational-Algebra UGC NET CS 2018 JUNE Paper-2

Question 277 Explanation:

Inner Join : Inner join combines two tables having a common attributes. While combining it only join the rows of two tables having same value in common attribute. So in that way inner join return the records having matching values in both the tables.

Question 278