All Courses
Get the App Signup / Login
Sign in Open App

All questions of Database Management System (DBMS) for Computer Science Engineering (CSE) Exam

1 Crore+ students have signed up on EduRev. Have you? Download the App

Consider the following database schedule with two transactions, T1 and T2.
S = r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2;
where ri(Z) denotes a read operation by transaction Ti on a variable Z, wi(Z) denotes a write operation by Ti on a variable Z and ai denotes an abort by transaction Ti . Which one of the following statements about the above schedule is TRUE?
  • a)
    S is non-recoverable
  • b)
    S is recoverable, but has a cascading abort
  • c)
    S does not have a cascading abort
  • d)
    S is strict
Correct answer is option 'C'. Can you explain this answer?

Tanishq Yadav answered
As we can see in figure,
  • T2 overwrites a value that T1 writes
  • T1 aborts: its “remembered” values are restored.
  • Cascading Abort could have arised if - > Abort of T1 required abort of T2 but as T2 is already aborted , its not a cascade abort. Therefore, Option C
Option A - is not true because the given schedule is recoverable Option B - is not true as it is recoverable and avoid cascading aborts; Option D - is not true because T2 is also doing abort operation after T1 does, so NOT strict.

R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?
  • a)
    A->B, B->CD
  • b)
    A->B, B->C, C->D
  • c)
    AB->C, C->AD
  • d)
    A ->BCD
Correct answer is option 'C'. Can you explain this answer?

Uday Saha answered
Background :
  • Lossless-Join Decomposition:
    Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)
R1 ∩ R2 → R1
OR
R1 ∩ R2 → R2
  • dependency preserving :
    Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional dependencies after decomposition is same as closure of of FDs before decomposition.
    A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposition.
Question : We know that for lossless decomposition common attribute should be candidate key in one of the relation. A) A->B, B->CD R1(AB) and R2(BCD) B is the key of second and hence decomposition is lossless. B) A->B, B->C, C->D R1(AB) , R2(BC), R3(CD) B is the key of second and C is the key of third, hence lossless. C) AB->C, C->AD R1(ABC), R2(CD) C is key of second, but C->A violates BCNF condition in ABC as C is not a key. We cannot decompose ABC further as AB->C dependency would be lost.D) A ->BCD Already in BCNF. Therefore, Option C AB->C, C->AD is the answer.

Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D. Then the decomposition of R into R1(AB) and R2(CD) is
  • a)
    dependency preserving and lossless join
  • b)
    lossless join but not dependency preserving
  • c)
    dependency preserving but not lossless join
  • d)
    not dependency preserving and not lossless join
Correct answer is option 'C'. Can you explain this answer?

Nisha Das answered
Dependency Preserving Decomposition:
Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional dependencies after decomposition is same as closure of of FDs before decomposition.
A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposition.
In the above question R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D) and there are only two FDs A -> B and C -> D. So, the decomposition is dependency preserving
Lossless-Join Decomposition:
Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)
R1 ∩ R2 → R1
OR
R1 ∩ R2 → R2
In the above question R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D), and R1 ∩ R2 is empty. So, the decomposition is not lossless.

The column of a table is referred to as the
  • a)
    Tuple
  • b)
    Attribute
  • c)
    Entity
  • d)
    Degree
Correct answer is option 'B'. Can you explain this answer?

Ameya Basak answered
Every column of the table is referred to as attribute. Row of the table are called as tuples. Number of columns in the table defines the degree of the table.

E-R modeling technique is a
  • a)
    Top-down approach
  • b)
    Bottom-up approach
  • c)
    Left-right approach
  • d)
    Both top-down and bottom-up
Correct answer is option 'A'. Can you explain this answer?

Yash Patel answered
It is a top-down method.
An entity–relationship model (ER model) describes inter-related things of interest in a specific domain of knowledge. An ER model is composed of entity types (which classify the things of interest) and specifies relationships that can exist between instances of those entity types.

In software engineering an ER model is commonly formed to represent things that a business needs to remember in order to perform business processes. Consequently, the ER model becomes an abstract data model that defines a data or information structure that can be implemented in a database, typically a relational database.

Some ER modelers show super and subtype entities connected by generalization-specialization relationships, and an ER model can be used also in the specification of domain-specific ontologies.

In an entity relationship, y is the dominant entity and x is a subordinate entity. Which of the following is/are incorrect?
  • a)
    Operationally , if y is deleted, so is x
  • b)
    x is existence dependent on y 
  • c)
    Operationally x is deleted, so is y
  • d)
    Operationally, x is deleted, y remains the same
Correct answer is option 'C'. Can you explain this answer?

Abhiram Goyal answered
1. y is dominant entity.
2. x is subordinate entity..
Since, y is dominant entity. So does not depend on any other and x is subordinate. So x is existence dependent on y and deletion of x does not effect y, So, x is detected, so y is incorrect.

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH -> G, A -> BC, B -> CFH, E -> A, F -> EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R. How many candidate keys does the relation R have?
  • a)
    3
  • b)
    4
  • c)
    5
  • d)
    6
Correct answer is option 'B'. Can you explain this answer?

Baishali Bajaj answered
Here we can see that D is not part of any FD's , hence D must be part of the candidate key.

Now D+ ={ D}.

 Hence we have to add A,B,C,E,F,G,H  to D and check which of them are Candidate keys of size 2.

We can proceed as 

AD+= {A,B,C,D,E,F,G,H}

Similarly we see BD+ , ED+ and FD+ also gives us all the attributes.Hence AD,BD,ED,FD are definitely the candidate keys.

But CD+ , GD+ and HD+ doesnt give all the attributes hence CD,GD and HD are not candidate keys.

Now we need to check the candidate keys of size 3 . Since AD , BD, ED, FD are all candidate keys hence we can't find candidate keys by adding elements to them as they will give us superkeys as they are already minimal. Hence we have to proceed with CD,GD and HD.

 Also we can't add any of {A,B,E,F} to CD, GD, HD as they will again give us superset of {AD,BD,ED,FD} . 

Hence we can only add among {C,G,H} to CD, GD, HD.

Adding C to GD and HD we get GCD , HCD. Taking closure and we will see they are not candidate keys.

Adding H to GD we get GHD which is also not a candidate key.(no more options with 3 attributes possible)

Now we need to check for candidate keys with 4 attributes . Since only remaining options are CGH and we have to add D only possible key of size 4 is CGHD whose closure also doesn't give us all of the attributes in the relation(All possible options covered)

Hence no of candidate keys are 4 : AD,BD,ED,FD.

A functional dependency of the form X → Y is trivial if
  • a)
  • b)
  • c)
     
  • d)
Correct answer is option 'C'. Can you explain this answer?

Aaditya Ghosh answered
A trivial functional dependency is a database dependency that occurs when describing a functional dependency of an attribute or of a collection of attributes that includes the original attribute.
So, Option c is correct answer.

Suppose a database schedule S involves transactions T1, ....Tn. Construct the precedence graph of S with vertices representing the transactions and edges representing the conflicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?
  • a)
    Topological order
  • b)
    Depth-first order
  • c)
    Breadth-first order
  • d)
    Ascending order of transaction indices
Correct answer is option 'A'. Can you explain this answer?

Ravi Singh answered
Cycle in precedence graph tells that schedule is not conflict serializable. DFS and BFS traversal of graph are possible even if graph contains cycle. And hence DFS and BFS are also possible for non serializable graphs. But Topological sort of any cyclic graph is not possible. Thus topological sort guarantees graph to be serializable. Option D is not valid because in a transaction with more indices might have to come before lower one. Also two non- conflicting schedule can occur simultaneously.

Third normal form is inadequate in situations where the relation
  • a)
    Has multiple candidate keys
  • b)
    Has candidate keys that are composite
  • c)
    Has overlapped candidate keys
  • d)
    All of the above
Correct answer is option 'D'. Can you explain this answer?

Sanya Agarwal answered
Third normal form is considered adequate for relational database design, it is inadequate in all situations with the relation having multiple, composes or overlapped candidate keys.

What is the min and max number of tables required to convert an ER diagram with 2 entities and 1 relationship between them with partial participation constraints of both entities?
  • a)
    Min 1 and max 2
  • b)
    Min 1 and max 3
  • c)
    Min 2 and max 3
  • d)
    Min 2 and max 2
Correct answer is option 'C'. Can you explain this answer?

Baishali Bajaj answered
Maximum number of tables required is 3 in case of many to many relationships between entities. Minimum number of tables is 1 in case of unary relationship and total participation of atleast one entity. But in case of partial participation of both entities, minimum number of tables required is 2.

Consider the following relation schema pertaining to a students database:
Student (rollno, name, address)
Enroll (rollno, courseno, coursename)
where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join ?
  • a)
    8, 0
  • b)
    120, 8
  • c)
    960, 8
  • d)
    960, 120
Correct answer is option 'A'. Can you explain this answer?

Nishanth Roy answered
The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names. What is the maximum possible number of tuples? The result of natural join becomes equal to the Cartesian product when there are no common attributes. The given tables have a common attribute, so the result of natural join cannot have more than the number of tuples in larger table.
What is the maximum possible number of tuples? It might be possible that there is no rollnumber common. In that case, the number of tupples would be 0.

Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size(r(R)) < size(s(S)), the join will have fewer number of disk block accesses if
  • a)
    relation r(R) is in the outer loop.
  • b)
    relation s(S) is in the outer loop.
  • c)
    join selection factor between r(R) and s(S) is more than 0.5.
  • d)
    join selection factor between r(R) and s(S) is less than 0.5.
Correct answer is option 'A'. Can you explain this answer?

Pranab Sharma answered
Nested loop join is one of the methods to implement database in memory. A nested loop join is an  algorithm that joins two sets by using two nested loops.
According to nested join,given relation R and S   For each tuple r in R do For each tuple s in S do If r and s satisfy the join condition Then output the tuple <r,s> Cost estimations for the above loop: – b(R) and  b(S) number of blocks in R and in S – Each block of outer relation is read once – Inner relation is read once for each block of outer relation Summing up : IO= b(R)+b(R)*b(S) total  IO operations Lets assume |R|>|S|  i.e b(R) =10  and b(s) =3 Now,   if R is outer relation then, IO= 10+10*3=40 if S is outer relation then IO=3+10*3=33 As it can be observed , that total IO is lesser if the value of outer variable is less and as it is already given that |R|<|S|.Therefore,  Relation r(R) should be in the outer loop to have fewer number of disk block accesses.

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
Correct answer is option 'A'. Can you explain this answer?

Arka Dasgupta answered
If records of a file are physically ordered on a non-key field which doesn't have a distinct value for each record that field is called the clustering field.

Which level of locking provides the highest degree of concurrency in a relational data base?
  • a)
    Page
  • b)
    Table
  • c)
    Row
  • d)
    Page, table and row level locking allow the same degree of concurrency
Correct answer is option 'C'. Can you explain this answer?

Anshu Mehta answered
  • ● Page level locking locks whole page i.e all rows therefore highly restrictive
    ● Table locking is mainly used for concurrency control with DDL operations
  • ●A row share table lock is the least restrictive, and has the highest degree of concurrency for a table.It indicates the transaction has locked rows in the table and intends to update them. 
    Therefore Row level provides highest level of concurrency.Hence Answer is C

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. RolLnumber = Grades.
RolLnumber
ANDCourse.lnstructor = Korth
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 Korth.
  • 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 Korth.
  • d)
    None of the above.
Correct answer is option 'C'. Can you explain this answer?

Rishabh Pillai answered
Explanation:

The given SQL query selects the distinct names of students who have received an A grade in at least one course taught by Korth. Let's break down the query and understand it step by step.

1. FROM Students, Courses, Grades
- This clause specifies the tables from which we are fetching the data: Students, Courses, and Grades.

2. WHERE Students.Roll_number = Grades.Roll_number
- This condition joins the Students and Grades tables based on the Roll_number attribute. It ensures that we only consider the records where the Roll_number matches in both tables.

3. AND Courses.Instructor = 'Korth'
- This condition further filters the joined result by only considering the records where the Instructor attribute of the Courses table is equal to 'Korth'.

4. AND Courses.Course_number = Grades.Course_number
- This condition ensures that we only consider the records where the Course_number matches in both the Courses and Grades tables.

5. AND Grades.Grade = 'A'
- This condition further filters the result by only considering the records where the Grade attribute of the Grades table is equal to 'A'.

6. SELECT DISTINCT Name
- Finally, we select the distinct names from the result obtained after applying the above conditions.

Conclusion:
The query retrieves the distinct names of students who have received an A grade in at least one course taught by Korth. Therefore, option C is the correct answer: "Names of students who have got an A grade in at least one of the courses taught by Korth."

Given the basic ER and relational models, which of the following is INCORRECT?
  • a)
    An attribute of an entity can have more than one value
  • b)
    An attribute of an entity can be composite
  • c)
    In a row of a relational table, an attribute can have more than one value
  • d)
    In a row of a relational table, an attribute can have exactly one value or a NULL value
Correct answer is option 'C'. Can you explain this answer?

Anshu Kumar answered
The term ‘entity’ belongs to ER model and the term ‘relational table’ belongs to relational model. A and B both are true. ER model supports both multivalued and composite attributes See this for more details. (C) is false and (D) is true. In Relation model, an entry in relational table can can have exactly one value or a NULL.

B+ trees are preferred to binary trees in databases because
  • a)
    Disk capacities are greater than memory capacities
  • b)
    Disk access is much slower than memory access
  • c)
    Disk data transfer rates are much less than memory data transfer rates
  • d)
    Disks are more reliable than memory
Correct answer is option 'B'. Can you explain this answer?

Varun Sen answered
Introduction:
B-trees and binary trees are two commonly used data structures in databases. Both have their advantages and disadvantages, but B-trees are preferred over binary trees in databases for several reasons.

Explanation:
1. Disk access is much slower than memory access:
- Disk access involves mechanical movements, such as the rotation of the disk and the movement of the read/write head, which takes significantly more time compared to accessing data from memory.
- B-trees are designed to minimize the number of disk accesses required to retrieve or update data. They achieve this by maximizing the number of keys stored in each node, reducing the height of the tree, and thus reducing the number of disk accesses needed.

2. Disk data transfer rates are much less than memory data transfer rates:
- Although disks have much higher capacities than memory, their data transfer rates are comparatively slower.
- B-trees are designed to optimize disk I/O by minimizing the amount of data that needs to be transferred between the disk and memory. By storing multiple keys in each node, B-trees reduce the overall amount of data that needs to be read from or written to the disk.

3. Disk capacities are greater than memory capacities:
- Disk capacities have increased significantly over the years and are now much larger than memory capacities.
- B-trees can efficiently utilize the larger disk capacities by storing a large number of keys in each node. This allows B-trees to store a larger amount of data on disk without sacrificing performance.

4. Disks are more reliable than memory:
- Disks are designed to be more reliable and durable compared to memory. They have mechanisms such as error correction codes and redundancy to ensure data integrity.
- B-trees take advantage of the reliability of disks by providing mechanisms like journaling and write-ahead logging, which guarantee the consistency of the database even in the event of system crashes or power failures.

Conclusion:
In conclusion, B-trees are preferred over binary trees in databases because disk access is much slower than memory access, disk data transfer rates are much less than memory data transfer rates, disk capacities are greater than memory capacities, and disks are more reliable than memory. B-trees optimize disk I/O and utilize larger disk capacities efficiently, providing better performance and data integrity in database systems.

For the schedule given below, which of the following is correct?
1 Read A
2 Read B
3 Write A
4 Read A
5 Write A
6 Write B
7 Read B
8 Write B
  • a)
    This schedule is serializable and can occur in a scheme using 2PL protocol.
  • b)
    This schedule is serializable but cannot occur in a scheme using 2PL protocol.
  • c)
    This schedule is not serializable but can occur in a scheme using 2PL protocol.
  • d)
    This schedule is not serializable and cannot occur in a scheme using 2PL protocol.
Correct answer is option 'D'. Can you explain this answer?

Nitin Datta answered
Initial read of A is done by T1, whereas final write of B is also done by T1. Therefore W1(A), R2(A) and W2(B), R1 (B) are conflicting pairs. The schedule is neither T1 → T2 nor T2 →T1 serializable. Since schedule is not serializable, it can’t occur in a scheme using 2PL protocol.

Consider a file of 16384 records. Each record is 32 bytes long and its key field is of size 6 bytes. The file is ordered on a non-key field, and the file organization is unspanned. The file is stored in a file system with block size 1024 bytes, and the size of a block pointer is 10 bytes. If the secondary index is built on the key field of the file, and a multilevel index scheme is used to store the secondary index, the number of first- level and second-level blocks in the multilevel index are respectively
  • a)
    8 and 0
  • b)
    128 and 6
  • c)
    256 and 4
  • d)
    512 and 5
Correct answer is option 'C'. Can you explain this answer?

Rohan Shah answered
Total number of records = 16384 
Record size = 32 B 
lock size = 1024 B
Number of records inside block 
In index block number of entries 
So, at 1st level index block 
at 2nd level index block 

The SQL expression :
SELECT distinct 7.branch_name
FROM branch T, branch S
WHERE T.assets >S.assets and S.branch_city = "PONDICHERRY”
Finds the names of
  • a)
    All branches that have greater assets than any branch located in PONDICHERRY.
  • b)
    All branches that have greater assets than all branches located in PONDICHERRY.
  • c)
    The branch that have greater assetin PONDICHERRY.
  • d)
    Any branches that have greater asset than any branch located in PONDICHERRY.
Correct answer is option 'A'. Can you explain this answer?

Aravind Sengupta answered
The condition T. assets > S. assets and S. branch_city = “PONDICHERRY” checks for the all those branch-name who have greater assets than any branch located in Pondicherry. The keyword distinct removes the duplicacy of branch_name satisfy the given condition.

Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X,Y). Which of the following queries cannot be expressed by a relational algebra expression of constant length?
  • a)
    List of all vertices adjacent to a given vertex
  • b)
    List all vertices which have self loops
  • c)
    List all vertices which belong to cycles of less than three vertices
  • d)
    List all vertices reachable from a given vertex
Correct answer is option 'D'. Can you explain this answer?

Avi Kulkarni answered
(A) This is simple query as we need to find (X, Y) for a given X. (B) This is also simple as need to find (X, X) (C) :-> Cycle < 3 . Means cycle of length 1 & 2. Cycle of length 1 is easy., Same as self loop. Cycle of length 2 is is also not too hard to compute. Though it'll be little complex, will need to do like (X,Y) & (Y, X ) both present & X != Y,. We can do this with constant RA query. (D) :-> This is most hard part. Here we need to find closure of vertices. This will need kind of loop. If the graph is like skewed tree, our query must loop for O(N) Times. We can't do with constant length query here. Answer is :-> D

Entity set TRANSACTION has the attributes transaction number, date, amount. Entity set ACCOUNT has the attributes account number, customer name, balance.
Q. Which is the primary key of the weak entity?
  • a)
    Account number
  • b)
    {Account number, transaction number}
  • c)
    {Account number, date}
  • d)
    {Transaction number, date}
Correct answer is option 'B'. Can you explain this answer?

Krithika Gupta answered
In order to identify each transaction of any account the key of strong entity with the addition of the discriminator of weak entity can be taken. Here [Account number, transaction number] act as the primary key of weak entity. This is because weak entity has no existence without strong entity.

In a schema with attributes A, B, C, D and E following set of functional dependencies are given
A → B A → C CD → E B → D E → A
Q. Which of the following functional dependencies is NOT implied by the above set?
  • a)
    CD → AC
  • b)
    BD → CD
  • c)
    BC → CD
  • d)
    AC → BC
Correct answer is option 'B'. Can you explain this answer?

Rajeev Menon answered
option (b)
For every options given, find the closure set of left side of each FD. If the closure set of left side contains the right side of the FD, then the particular FD is implied by the given set. 
Option (a): Closure set of CD = CDEAB. Therefore CD->AC can be derived from the given set of FDs.
Option (c): Closure set of BC = BCDEA. Therefore BC->CD can be derived from the given set of FDs.
Option (d): Closure set of AC = ACBDE. Therefore AC->BC can be derived from the given set of FDs.
Option (b): Closure set of BD = BD. Therefore BD->CD cannot be derived from the given set of FDs.

A relation (from the relational database model) consists of a set of tuples, which implies that
  • a)
    Relational model supports multi-valued attributes whose values can be represented in sets.
  • b)
    For any two tuples, the values associated with all of their attributes may be the same.
  • c)
    For any two tuples, the value associated with one or more of their attributes must differ.
  • d)
    All tuples in a particular relation may have different attributes.
Correct answer is option 'C'. Can you explain this answer?

Baishali Dasgupta answered
Explanation:

Relational database model is based on the concept of relations or tables. A relation consists of a set of tuples, where each tuple represents a single entity or object in the real world. Each tuple has a set of attributes or fields, which represent the properties or characteristics of that entity. The values of these attributes are stored in the corresponding columns of the table.

Let us now understand the given options one by one:

a) Relational model supports multi-valued attributes whose values can be represented in sets.

This statement is incorrect. Relational model does not support multi-valued attributes. Each attribute in a relation can have only a single value. However, we can represent multiple values of an attribute by creating a separate table and establishing a relationship between the two tables.

b) For any two tuples, the values associated with all of their attributes may be the same.

This statement is also incorrect. In a relation, each tuple represents a unique entity, and therefore, the values associated with all of their attributes cannot be the same. There must be at least one attribute whose value differs between the two tuples.

c) For any two tuples, the value associated with one or more of their attributes must differ.

This statement is correct. As explained above, each tuple in a relation represents a unique entity, and therefore, the values associated with all of their attributes cannot be the same. There must be at least one attribute whose value differs between the two tuples.

d) All tuples in a particular relation may have different attributes.

This statement is also incorrect. In a relation, all tuples must have the same set of attributes, although some attributes may have null values in some tuples.

Therefore, the correct answer is option 'C', which states that for any two tuples, the value associated with one or more of their attributes must differ.

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)

Q. Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?
  • a)
    The schema is in BCNF
  • b)
    The schema is in 3NF but not in BCNF
  • c)
    The schema is in 2NF but not in 3NF
  • d)
    The schema is not in 2NF
Correct answer is option 'A'. Can you explain this answer?

Rajveer Sharma answered
A relation is in BCNF if for every one of its dependencies X → Y, at least one of the following conditions hold:
X → Y is a trivial functional dependency (Y ⊆ X)
X is a superkey for schema R
Since (sname, city) forms a candidate key, there is no non-tirvial dependency X → Y where X is not a superkey

A table has fields Fl, F2, F3, F4, F5 with the following functional dependencies   F1 → F3   F2→ F4   (F1 . F2) → F5 In terms of Normalization, this table is in 
  • a)
    1 NF
  • b)
    2 NF
  • c)
    3 NF
  • d)
    none
Correct answer is option 'A'. Can you explain this answer?

Gauri Banerjee answered
First Normal Form A relation is in first normal form if every attribute in that relation is singled valued attribute. Second Normal Form A relation is in 2NF iff it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. This table has Partial Dependency f1->f3, f2-> f4 given (F1,F2) is Key So answer is A

The employee information in a company is stored in the relation. Assume name is the primary key:
Employee: (name, sex, salary, deptName)
Consider the SQL query;
SELECT deptName
FROM Employee
WHERE sex = M
GROUP By deptName
HAVING avg(salary) > (SELECT avg(salary)
FROM Employee)
It returns the names of the departments in which the average salary. 
  • a)
    Is more than the average salary in the company.
  • b)
    Of the male employees is more than the average salary of all the male employees in the company.
  • c)
    Of the male employees is more than the average salary of the employees in the same department.
  • d)
    Of the male employees is more than the average salary in the company.
Correct answer is option 'D'. Can you explain this answer?

Tarun Saini answered
The given query first calculates the average salary from the relation Employee, then it selects the department name where employees who have average salary more than the average salary of the company.

Consider a relational table with a single record for each registered student with the following attributes.
1. Registration_Num: Unique registration number of each registered student
2. UID: Unique identity number, unique at the national level for each citizen
3. BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or join accounts. This attribute stores the primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel

Q. Which one of the following option is INCORRECT?
  • a)
    BankAccount_Num is candidate key
  • b)
    Registration_Num can be a primary key
  • c)
    UID is candidate key if all students are from the same country
  • d)
    If S is a superkey such that S∩UID is NULL then S∪UID is also a superkey
Correct answer is option 'A'. Can you explain this answer?

Aditya Nair answered
A Candidate Key value must uniquely identify the corresponding row in table. BankAccount_Number is not a candidate key. As per the question “A student can have multiple accounts or joint accounts. This attributes stores the primary account number”. If two students have a joint account and if the joint account is their primary account, then BankAccount_Number value cannot uniquely identify a row.

Given the following two statements:
S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF.
S2: AB->C, D->E, E->C is a minimal cover for the set of functional dependencies AB->C, D->E, AB->E, E->C.

Q. Which one of the following is CORRECT?
  • a)
    S1 is TRUE and S2 is FALSE.
  • b)
    Both S1 and S2 are TRUE.
  • c)
    S1 is FALSE and S2 is TRUE.
  • d)
    Both S1 and S2 are FALSE.
Correct answer is option 'A'. Can you explain this answer?

Abhiram Goyal answered
S1: Every table with two single-valued
attributes is in 1NF, 2NF, 3NF and BCNF.
A relational schema R is in BCNF iff in Every non-trivial Functional Dependency X->Y, X is Super Key. If we can prove the relation is in BCNF then by default it would be in 1NF, 2NF, 3NF also. Let R(AB) be a two attribute relation, then
  1. If {A->B} exists then BCNF since {A}+ = AB = R
  2. If {B->A} exists then BCNF since {B}+ = AB = R
  3. If {A->B,B->A} exists then BCNF since A and B both are Super Key now.
  4. If {No non trivial Functional Dependency} then default BCNF.
Hence it's proved that a Relation with two single - valued attributes is in BCNF hence its also in 1NF, 2NF, 3NF. Hence S1 is true.
S2: AB->C, D->E, E->C is a minimal cover for the set of functional dependencies
AB->C, D->E, AB->E, E->C.
As we know Minimal Cover is the process of eliminating redundant Functional Dependencies and Extraneous attributes in Functional Dependency Set. So each dependency of F = {AB->C, D->E, AB->E, E->C} should be implied in minimal cover. As we can see AB->E is not covered in minimal cover since {AB}+ = ABC in the given cover {AB->C, D->E, E->C} Hence, S2 is false.

Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?
  • a)
    2
  • b)
    3
  • c)
    4
  • d)
    5
Correct answer is option 'B'. Can you explain this answer?

Tanishq Malik answered
The answer is B, i.e minimum 3 tables. Strong entities E1 and E2 are represented as separate tables. In addition to that many-to-many relationships(R2) must be converted as seperate table by having primary keys of E1 and E2 as foreign keys. One-to-many relaionship (R1) must be transferred to 'many' side table(i.e. E2) by having primary key of one side(E1) as foreign key( this way we need not to make a seperate table for R1). Let relation schema be E1(a1,a2) and E2( b1,b2). Relation E1( a1 is the key)
Relation E2( b1 is the key, a1 is the foreign key, hence R1(one-many) relationship set satisfy here)
Relation R2 ( {a1, b1} combined is the key here , representing many-many relationship R2)
Hence we will have minimum of 3 tables.

Consider the relation:
Employee (Emp-No, Emp-name, salary, project- no, due-date)
(Assuming an 1-1 relationship between project and employees)
Project-no is functionally dependent on
  • a)
    Emp-name
  • b)
    Emp-no
  • c)
    Due -date
  • d)
    None of these
Correct answer is option 'B'. Can you explain this answer?

Sarthak Desai answered
The relation EMPLOYEE has one to one relation b/w project and employee i.e. each employee has being assigned a project and each project is associated with only one employee, hence project- number is functionally dependent on Emp-no.

Consider the following relational schema.
Students(rollno: integer, sname: string) Courses(courseno: integer, cname: string) Registration(rollno: integer, courseno: integer, percent: real)
Q. Which of the following queries are equivalent to this query in English?
"Find the distinct names of all students who score more than 90% in the course numbered 107"
  • a)
    I, II, III and IV
  • b)
    I, II and III only
  • c)
    I, II and IV only
  • d)
    II, III and IV only
Correct answer is option 'A'. Can you explain this answer?

Madhurima Kumar answered
Option A:
This is a SQL query expression. It first perform a cross product of Students and Registration, then WHERE clause only keeps those rows in the cross product set where the student is registered for course no 107, and percentage is > 90. Then select distinct statement gives the distinct names of those students as the result set.
Option B:
This is a relational algebra expression. It first perform a NATURAL JOIN of Students and Registration (NATURAL JOIN implicitly joins on the basis of common attribute, which here is rollno ), then the select operation( sigma) keeps only those rows where the student is registered for courseno 107, and percentage is > 90. And then the projection operation (pi) projects only distinct student names from the set.
Note: Projection operation (pi) always gives the distinct result.
Option C:
This is a Tuple Relational Calculus (TRC) language expression, It is not a procedural language (i.e. it only tells “what to do”, not “how to do”). It just represents a declarative mathematical expression.
Here T is a Tuple variable.
From left to right, it can be read like this, “It is a set of tuples T, where, there exists a tuple S in Relation Students, and there exist a tuple R in relation Registration, such that S.rollno = R.rollno AND R.couseno = 107 AND R.percent > 90 AND T.sname = S.sname”. And the schema of this result is (sname), i.e. each tuple T will contain only student name, because only T.sname has been defined in the expression.
As TRC is a mathematical expression, hence it is expected to give only distinct result set.
Option D:
This is a Domain Relational Calculus (DRC) language expression. This is also not procedural. Here SN is a Domain Variable. It can be read from left to right like this “The set of domain variable SN, where, there exist a domain variable SR , and a domain variable Rp, such that, SN and SR domain variables is in relation Students and SR,107,RP is a domain variables set in relation Registration, AND RP > 90 “ Above, SN represents sname domain attribute in Students relation, SR represents rollno domain attribute in Students relation, and RP represents percentage domain attribute in Registration relation. The schema for the result set is (SN), i.e. only student name.
As DRC is a mathematical expression, hence it is expected to give only distinct result set.

With regard to the expressive power of the formal relational query languages, which of the following statements is true?
  • a)
    Relational algebra is more powerful than relational calculus
  • b)
    Relational algebra has the same power as relational calculus
  • c)
    Relational algebra has the same power as safe relational calculus
  • d)
    None of the above
Correct answer is option 'C'. Can you explain this answer?

Rajesh Malik answered
A query can be formulated in relational calculus if and only if it can be formulated in relational algebra. So, relational algebra has the same power as relational calculus. 
But, it is possible to write syntactically correct relational calculus queries that have infinite number of answers. Such queries are unsafe. Queries that have an finite number of answers are safe relational calculus queries. 
Thus, Relational algebra has the same power as safe relational calculus. 
 
Thus, option (C) is the answer. 
 
Please comment below if you find anything wrong in the above post.

Suppose we have a block-addressable disk drive. With such block-organized disk nondata overhead of subblocks and interblock gaps have to be accounted for. There are 40000 bytes per track and the amount of space taken up/by subblocks and interblocks gaps equivalent to 250 bytes per block. A file contains records and record size is 200 bytes to be stored on the disk, if a total of 32 blocks can be stored per track then what is the blocking factor? The term "blocking factor” is used to indicate the number of records that are to the stored in each block in a file. A block is organized to hold an integral number of logical records.
  • a)
    3
  • b)
    4
  • c)
    5
  • d)
    6
Correct answer is option 'C'. Can you explain this answer?

Baishali Dasgupta answered
"blocking factor" refers to the number of records that can fit into a single block.

First, we need to calculate the actual amount of usable space per block:
40000 bytes per track - 250 bytes per block of nondata overhead = 39750 bytes per track
39750 bytes per track / 32 blocks per track = 1242.1875 bytes per block

Next, we calculate the blocking factor:
Each block can hold 1242.1875 bytes / 200 bytes per record = 6.2109375 records per block
Rounding down to the nearest integer, the blocking factor is 6 records per block.

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
Correct answer is option 'D'. Can you explain this answer?

Jyoti Sengupta answered
Relational algebra can not preform the following:
(a) Aggregate computation (avg, sum, etc. must be used).
(b) Multiplication
(c) Finding transitive closure
These operations are beyond the capability of relational algebra.

Chapter doubts & questions for Database Management System (DBMS) - GATE Computer Science Engineering(CSE) 2025 Mock Test Series 2024 is part of Computer Science Engineering (CSE) exam preparation. The chapters have been prepared according to the Computer Science Engineering (CSE) exam syllabus. The Chapter doubts & questions, notes, tests & MCQs are made for Computer Science Engineering (CSE) 2024 Exam. Find important definitions, questions, notes, meanings, examples, exercises, MCQs and online tests here.

Chapter doubts & questions of Database Management System (DBMS) - GATE Computer Science Engineering(CSE) 2025 Mock Test Series in English & Hindi are available as part of Computer Science Engineering (CSE) exam. Download more important topics, notes, lectures and mock test series for Computer Science Engineering (CSE) Exam by signing up for free.

Top Courses Computer Science Engineering (CSE)

Signup to see your scores go up within 7 days!

Study with 1000+ FREE Docs, Videos & Tests
10M+ students study on EduRev
© EduRev
Education Revolution
Follow Us
Signup to see your scores go up within 7 days!
Access 1000+ FREE Docs, Videos and Tests
Takes less than 10 seconds to signup