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

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

Clear all your Computer Science Engineering (CSE) doubts with EduRev

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.

Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R, it will,
  • a)
    Result in a deadlock situation
  • b)
    Immediately be granted
  • c)
    Immediately be rejected
  • d)
    Be granted as soon as it is released by A
Correct answer is option 'B'. Can you explain this answer?

Gauri Choudhary answered
Concept:
Simple locking scheme:
1. Shared lock (S)
2. Exclusive lock (X)
Shared Lock (S): If transaction locked data item in shared mode, allowed to read only.
Exclusive Lock (X): If transaction locked data item in shared mode, allowed to read and write both.
Explanation: 
From above table we can see that if one transaction has shared lock on a data item and if one other transaction request for shared lock on the same data item it will be immediately granted.    
You can know more about Transactions & Concurrency Control through the document: 
Transactions & Concurrency Control
          

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.

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.

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.

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 that connect them.
  • d)
    None of the above
Correct answer is option 'A'. Can you explain this answer?

Soumya Pillai answered
A foreign key is a key of a relation which is primary key of some other relation. A primary key is combined with foreign key then are ferential constraint is set up between the two relation, hence it creates a parent-child relationship between the tables that connects them.

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.

Consider the following statements of RDBMS. Which of the following are correct?
I. Every relation in 3NF is also in BCNF
II. Every relation in BCNF is also in 3NF
  • a)
    Only I is correct
  • b)
    Both I and II are correct
  • c)
    Both I and II are wrong
  • d)
    Only II is correct
Correct answer is option 'D'. Can you explain this answer?

Saanvi Chopra answered
Introduction:
RDBMS stands for Relational Database Management System, which is a software system that manages relational databases. 3NF (Third Normal Form) and BCNF (Boyce-Codd Normal Form) are two normal forms used in RDBMS to eliminate data redundancy and ensure data integrity.

Explanation:
I. Every relation in 3NF is also in BCNF:
- This statement is incorrect.
- In 3NF, a relation must satisfy the following conditions:
1. It should be in 2NF.
2. It should not have any transitive dependencies.
- BCNF is a higher normal form than 3NF, and it has an additional condition:
1. It should not have any non-trivial functional dependencies.
- Therefore, a relation in 3NF may or may not satisfy the conditions for BCNF. Hence, it is not necessary for every relation in 3NF to also be in BCNF.

II. Every relation in BCNF is also in 3NF:
- This statement is correct.
- In BCNF, a relation must satisfy the following conditions:
1. It should be in 1NF (First Normal Form).
2. It should not have any non-trivial functional dependencies.
- 3NF is a lower normal form than BCNF, and it has an additional condition:
1. It should not have any transitive dependencies.
- Since BCNF is a higher normal form, any relation that satisfies the conditions for BCNF also satisfies the conditions for 3NF.

Conclusion:
Based on the explanation above, it can be concluded that the correct statement is:
- Only II is correct (Option D).

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))
  • 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?

Eesha Bhat answered
Concept:
Nested loop join algorithm:
No of block transfers = nr x bs + br
Where nr is number of tuples in relation R and bs and br are the number of blocks in relation R and S respectively.
In question it is given that size(r(R))s > br and nr < ns.
Example:
Suppose relation r is in the outer loop:

Suppose relation s is in the outer loop:
Block transfer = 100 x 10 + 20 = 1020
So relation r should be in the outer loop for effective nested loop join algorithm.
Hence option 1 is the correct answer.

State true or false : Nested Subqueries cannot be used for comparing two different sets
  • a)
    True
  • b)
    False
  • c)
    Can not be determined
  • d)
    None of the above
Correct answer is option 'B'. Can you explain this answer?

Rishika Choudhury answered
Explanation:
Nested subqueries can indeed be used for comparing two different sets in SQL. This allows for complex queries to be constructed by nesting one query within another. Here is a breakdown of how nested subqueries can be used for comparing two different sets:
- Definition of Nested Subqueries:
- Nested subqueries refer to a subquery within another subquery in an SQL statement. This allows for more complex queries to be executed, as the result of the inner subquery can be used in the outer query.
- Comparing Two Different Sets:
- By using nested subqueries, you can compare data from two different sets or tables. For example, you can use a subquery to retrieve data from one table and then compare it with data from another table in the outer query.
- Example:
- SELECT column_name
FROM table1
WHERE column_name IN (SELECT column_name
FROM table2);
In the example above, the nested subquery retrieves data from table2, which is then used to compare with the data in table1. This allows for comparisons between two different sets of data.
Therefore, the statement that nested subqueries cannot be used for comparing two different sets is false. Nested subqueries are a powerful feature in SQL that allows for complex comparisons and queries to be executed efficiently.

A database is ___ to reduce the number of disk accesses needed to process queries in order to improve performance.
  • a)
    Non-indexed
  • b)
    Indexed
  • c)
    Inserted
  • d)
    Updated
Correct answer is option 'B'. Can you explain this answer?

Sharmila Gupta answered
Introduction:

A database is a structured collection of data that allows for efficient storage, retrieval, and manipulation of information. One of the key goals of a database system is to improve performance by reducing the number of disk accesses needed to process queries. This can be achieved through indexing.


Indexed Databases:

Indexed databases use data structures called indexes to improve query performance. An index is a separate structure that is created based on one or more columns of a table. It contains a copy of the column(s) along with a pointer to the actual data in the table.


When a query is executed, the database system can use the index to quickly locate the relevant data on the disk, rather than scanning the entire table. This reduces the number of disk accesses needed, which in turn improves performance.


Advantages of Indexed Databases:

Indexed databases offer several advantages:



  • Fast data retrieval: With indexes, the database system can quickly locate the desired data, reducing the time needed to process queries.

  • Reduced disk I/O: By minimizing disk accesses, indexed databases can reduce the amount of data read from or written to the disk, improving overall system performance.

  • Efficient data updates: When data is updated, the database system can update the index along with the actual data. This ensures that the index remains up-to-date and query performance is not affected.

  • Flexible query optimization: Indexed databases provide the database system with more options for optimizing query execution plans. The system can choose the most efficient index to use based on the query conditions and access patterns.



Conclusion:

Indexed databases are designed to reduce the number of disk accesses needed to process queries, thereby improving performance. By using indexes, the database system can quickly locate the relevant data, resulting in faster data retrieval and reduced disk I/O. Indexed databases also provide efficient data updates and flexible query optimization, further enhancing performance.

What is the maximum number of keys that a B+ -tree of order 3 and of height 3 have?
  • a)
    3
  • b)
    80
  • c)
    27
  • d)
    26
Correct answer is option 'D'. Can you explain this answer?

Tanishq Malik answered
Understanding B+ -Trees
A B+ -tree is a self-balancing tree data structure that maintains sorted data and allows for efficient insertion, deletion, and search operations. The order of the tree determines the maximum number of children each node can have.
Order and Height of the B+ -Tree
- Order (m): In this case, the order is 3. This means each internal node can have a maximum of 3 children.
- Height (h): The height of the tree is given as 3.
Calculation of the Maximum Number of Keys
To calculate the maximum number of keys in a B+ -tree, we consider the following:
1. Leaf Nodes: In a B+ -tree, all keys are stored in the leaf nodes. The maximum number of leaf nodes at height 3 can be determined by examining the structure of the tree.
2. Internal Nodes:
- At height 0 (the root node), there can be a maximum of 3 children.
- At height 1, each of those 3 children can again have 3 children, resulting in 3 * 3 = 9 internal nodes.
- At height 2, each of the 9 internal nodes can also have 3 children, leading to 9 * 3 = 27 leaf nodes.
3. Keys in Leaf Nodes:
- Each leaf node can hold a maximum of (m - 1) = 2 keys (since m = 3).
- Therefore, the maximum number of keys in 27 leaf nodes is 27 * 2 = 54 keys.
However, in a B+ -tree, only the internal nodes contribute to the count of keys held, which means:
- The maximum number of keys is determined by the number of leaf nodes at height 3, which is 27, but since each internal node can hold only 2 keys as discussed, the correct total will be 2 * 13 (for 13 nodes) = 26.
Final Answer
Thus, the maximum number of keys that a B+ -tree of order 3 and height 3 can hold is 26. Hence, the correct answer is option 'D'.

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.

Consider the following set of functional dependency on the schema (A, B,C)
A→BC, B→C, A→B, AB→C
The canonical cover for this set is:
  • a)
    A->BC and B->C
  • b)
    A->BC and AB->C
  • c)
    A->BC and A->B
  • d)
    A->B and B->C
Correct answer is option 'A'. Can you explain this answer?

Eesha Bhat answered
All of the FDs are implied by the FDs {A->BC, B->C}
From option b: A+ = BC (hence A->BC, A->b and AB->Care implied by FDs set given in option b)
B+ = BC (hence B->C is implied by FDs set given in option b)

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.

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.

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?

Nayanika Sharma answered
Schedule Analysis:
The given schedule consists of a series of read and write operations on two variables A and B. Let's analyze the schedule to determine its serializability and whether it can occur in a scheme using the Two-Phase Locking (2PL) protocol.

Conflict Serializability:
To determine whether a schedule is serializable, we need to check for conflicts between operations. In this case, conflicts occur when two operations access the same variable and at least one of them is a write operation.

Conflict Analysis:
1. Read A: No conflicts.
2. Read B: No conflicts.
3. Write A: No conflicts.
4. Read A: Conflict with operation 1 (Read A).
5. Write A: Conflict with operation 1 (Read A).
6. Write B: No conflicts.
7. Read B: Conflict with operation 2 (Read B).
8. Write B: Conflict with operation 6 (Write B).

Serializability:
Since the schedule has conflicts, it is not serializable. To be serializable, a schedule must be conflict-serializable, meaning that the order of conflicting operations can be rearranged without changing the final result.

Two-Phase Locking (2PL):
The 2PL protocol ensures serializability by acquiring and releasing locks on resources in two phases: the growing phase and the shrinking phase.
- In the growing phase, a transaction acquires locks on resources before accessing them.
- In the shrinking phase, a transaction releases locks on resources after completing its operations.

2PL Analysis:
In the given schedule, the conflicts between operations 4 and 5 (Read A and Write A) cannot be resolved using the 2PL protocol. According to 2PL, a write operation needs to acquire an exclusive lock (X-lock) on a resource, and a read operation needs to acquire a shared lock (S-lock) on a resource. Since operation 4 (Read A) conflicts with operation 5 (Write A), it violates the 2PL protocol.

Conclusion:
Based on the conflict analysis and 2PL protocol, we can conclude that the given schedule is not serializable and cannot occur in a scheme using the 2PL protocol. Therefore, the correct answer is option 'D'.

___ are found in the second column of the database.
  • a)
    Datasets
  • b)
    Data structure
  • c)
    Data references
  • d)
    Data items
Correct answer is option 'C'. Can you explain this answer?

Rajveer Sharma answered
Data references are found in the second column of the database. Data references are essentially pointers or references that point to the actual location of the data in the database. They provide a way to access and retrieve the data stored in the database.

Explanation:

1. Database Structure:
- A database is a structured collection of data that is organized in a specific way to facilitate efficient storage, retrieval, and manipulation of data.
- The structure of a database typically consists of tables, columns, and rows.
- Each table represents a specific entity or concept, and each column within a table represents a specific attribute or property of that entity.
- Rows, on the other hand, represent individual instances or records of the entity.

2. Columns in a Database:
- Columns are vertical structures in a database table that define the attributes or properties of the entities represented by the table.
- Each column has a unique name and a specific data type, which determines the kind of data that can be stored in that column.
- Columns are typically organized in a tabular format, with each column representing a different attribute or property.

3. Data References:
- Data references, also known as pointers or references, are values that point to the location of the actual data in the database.
- Instead of storing the actual data in the database column, a data reference stores the address or identifier of the data.
- This allows for more efficient storage and retrieval of data, especially when dealing with large amounts of data.
- Data references are commonly used in cases where the actual data is too large to be stored directly in the column or when the data is stored in a different location.

4. Second Column of the Database:
- In the given question, it is stated that data references are found in the second column of the database.
- This means that the second column of the table contains values that point to the location of the actual data.
- The first column of the table would typically contain some kind of identifier or key that uniquely identifies each record in the table.

Therefore, the correct answer is option 'C' - Data references.

Consider Join of a relation R with a relation S. If R has m tuples and S has n tuples, then maximum and minimum sizes of the Join respectively are
  • a)
    m + n and 0
  • b)
    mn and 0
  • c)
    m + n and |m - n|
  • d)
    mn and m + n
Correct answer is option 'B'. Can you explain this answer?

Eesha Bhat answered
Concept: 
A natural join (join) is based on common attributes or common columns to join two tables or relations.
Consider an example, suppose there is two tables Employee(Eid, Ename) consists of 5 tuples and department(Eid, Did) consists of 3 tuples only.


Natural join of employee and department(Employee * Department) gives:
m×n = 5 × 3 = 15
Therefore maximum size is m × n
Minimum size: 
When Both the relations have a common attribute but no tuple in both relations match.
∴ minimum size = 0.

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

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.

In a B+ tree, both the internal nodes and the leaves have keys.
  • a)
    True
  • b)
    False
  • c)
    Can not be determined
  • d)
    None of the above
Correct answer is option 'B'. Can you explain this answer?

Sudhir Patel answered
In a B+ -tree, only the leaves have keys, and these keys are replicated in non-leaf nodes for defining the path for locating individual records.

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)
    2
  • b)
    5
  • c)
    3
  • d)
    6
Correct answer is option 'D'. Can you explain this answer?

Devanshi Desai answered
Explanation:

To determine the maximum number of tuples that could appear in the natural join of two database relations R and S, we need to consider the cardinality of each relation and the common attributes between them.

Given that R has 3 tuples and S has 2 tuples, we can visualize the relations as follows:

```
R:
--------
| R1 |
--------
| R2 |
--------
| R3 |
--------

S:
--------
| S1 |
--------
| S2 |
--------
```

To perform the natural join of R and S, we need to find the tuples that have matching values on the common attributes.

Since there are no common attributes mentioned in the question, we assume that the natural join is performed on all attributes.

In this case, each tuple in R will be compared with each tuple in S, resulting in a maximum of 3 x 2 = 6 comparisons.

```
R x S:
-----------------
| R1S1 | | |
-----------------
| | R1S2 | |
-----------------
| R2S1 | | |
-----------------
| | R2S2 | |
-----------------
| R3S1 | | |
-----------------
| | R3S2 | |
-----------------
```

Therefore, the maximum number of tuples that could appear in the natural join of R and S is 6.

Answer: D) 6

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 

What does the “x” operator do in relational algebra?
  • a)
    Output specified attributes from all rows of the input relation. Remove duplicate tuples from the output
  • b)
    Output pairs of rows from the two input relations that have the same value on all attributes that have the same name
  • c)
    Output all pairs of rows from the two input relations (regardless of whether or not they have the same values on common attributes)
  • d)
    Returns the rows of the input relation that satisfy the predicate
Correct answer is option 'C'. Can you explain this answer?

Sudhir Patel answered
The “x” operator outputs all pairs of rows from the two input relations (regardless of whether or not they have the same values on common attributes). This operation is called as the Cartesian product operation and is similar to the Cartesian product of sets.

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.

How many Primary keys can have in a table?
  • a)
    Only 1
  • b)
    Only 2
  • c)
    Depends on no of Columns
  • d)
    Depends on DBA
Correct answer is option 'A'. Can you explain this answer?

Milan Rane answered
Primary Keys in a Table

The primary key is a crucial concept in database management systems. It is used to uniquely identify each record or tuple in a table. A primary key ensures data integrity and serves as a reference for establishing relationships between tables. In most cases, a primary key is composed of one or more columns.

Defining a Primary Key

To define a primary key in a table, you need to specify the primary key constraint. This constraint enforces the uniqueness and non-nullability of the primary key column(s). Only one primary key constraint can be defined per table.

Options for Primary Keys

When it comes to the number of primary keys a table can have, the correct answer is option 'A': Only 1. Let's explore the other options to understand why they are not correct:

- Option 'B': Only 2 - This option is incorrect because a table can have only one primary key constraint. However, a primary key can be composed of multiple columns, known as a composite key.
- Option 'C': Depends on the number of columns - This option is incorrect because the number of columns in a table does not determine the number of primary keys. A table can have multiple columns, but it can still have only one primary key.
- Option 'D': Depends on DBA - This option is incorrect because the number of primary keys is not determined by the database administrator (DBA). The limit of one primary key constraint per table is a rule imposed by database management systems.

Composite Keys

Although a table can have only one primary key constraint, that key can consist of multiple columns. This is known as a composite key. The combination of these columns must be unique within the table. Composite keys are useful when a single column cannot uniquely identify a record.

Conclusion

In summary, a table can have only one primary key constraint. This constraint ensures that each record in the table is uniquely identified. While a primary key can be composed of multiple columns (composite key), there can only be one primary key constraint per table.

Consider the relation R(X, Y, Z, W, V) in which X, Y, Z, W, and V are the attributes and the following set of functional dependencies
A = {{X, Y} → {Z, W},  {X, W, V} → {Y, Z}}
Which of the following is the trivial functional dependency in A+, where A+ is closure of A?
  • a)
    {X, Z} → {Z, W}
  • b)
    {X, V} → {V}
  • c)
    {X, W, V} → {Y, W}
  • d)
    {Y, W} → {Y, X}
Correct answer is option 'B'. Can you explain this answer?

Sudhir Patel answered
Concept:
The closure of F, denoted as F+, is the set of all regular FD, that can be derived from.
For trivial functional dependency,
Let A and be two sets consists of attributes of a relation
A → B
A ⊇ B 
Option 1: 
{X, Z} → {Z, W}
{X, Z}  ⊉ {Z, W}
Not a trivial functional dependency
Option 2: 
{X, V} → {V}
{X, V} ⊇ {V}
It is a trivial functional dependency
Option 3: 
{X, W, V} → {Y}
{X, W, V} ⊉  {Y}
Not a trivial functional dependency
Option 4: 
{Y, W} → {Y, X}
{Y, W} ⊉ {Y, X}
Not a trivial functional dependency
NOTE:
⊇ → superset
⊉ → not superset

The concept of locking can be used to solve the problem of
1. Lost update
2. Uncommitted dependency
3. Inconsistent data
4. Deadlock
  • a)
    1 and 4
  • b)
    2, 3 and 4
  • c)
    1, 2 and 3
  • d)
    All 1, 2, 3 and 4 can be solved
Correct answer is option 'C'. Can you explain this answer?

Sanchita Chauhan answered
The concept of locking can be used to solve the problem of lost update, uncommitted dependency and prevents inconsistency among data but it is not capable of preventing the deadlock always.

2-3-4 trees are B-trees of order 4. They are an isometric of _____ trees.
  • a)
    AVL
  • b)
    AA
  • c)
    2-3
  • d)
    Red-Black
Correct answer is option 'D'. Can you explain this answer?

Sudhir Patel answered
2-3-4 trees are isometric of Red-Black trees. It means that, for every 2-3-4 tree, there exists a Red-Black tree with data elements in the same order.

Assume that there are 4 attributes A, B, C, D and that F = {A→B, B →C}, then which of the following is not included in F+ ?
  • a)
    A→C
  • b)
    BD→ C
  • c)
    ABC →D
  • d)
    AC → B
Correct answer is option 'C'. Can you explain this answer?

Bijoy Sharma answered
, B, C, D} is the set of all possible attributes.

To find the number of possible combinations of these attributes, we can use the formula for the number of combinations of n objects taken r at a time, which is given by:

C(n, r) = n! / (r!(n-r)!)

In this case, we want to find the number of combinations of 4 attributes taken 1 at a time, which is:

C(4, 1) = 4! / (1!(4-1)!) = 4! / (1!3!) = 4

Therefore, there are 4 possible combinations of attributes taken 1 at a time from the set F. These combinations are:

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

A relation in which every non-key attribute is fully functionally dependent on the primary key and which has no transitive dependencies is in -
  • a)
    3NF
  • b)
    BCNF
  • c)
    5NF
  • d)
    4NF
Correct answer is option 'A'. Can you explain this answer?

Anshu Mehta answered
Understanding 3NF (Third Normal Form)
3NF is a crucial step in the normalization process of relational databases. It ensures that the database design reduces redundancy and enhances data integrity.
Key Characteristics of 3NF:
  • Full Functional Dependency: Every non-key attribute must be fully functionally dependent on the primary key. This means that non-key attributes should not rely on any subset of a composite primary key.
  • No Transitive Dependencies: A non-key attribute should not depend on another non-key attribute. In other words, if A determines B and B determines C, then A should directly determine C—thereby preventing indirect relationships that can lead to redundancy.

Why 3NF is Important:
  • Minimizes Redundancy: By ensuring that non-key attributes are only dependent on the primary key, 3NF eliminates duplicate data, which conserves storage and simplifies updates.
  • Enhances Data Integrity: Since no transitive dependencies exist, 3NF helps maintain consistency and accuracy in the database. Changes to one piece of data do not inadvertently affect others.
  • Improves Query Performance: With a well-structured database in 3NF, queries can be executed more efficiently, as the data is organized logically without unnecessary duplication.

Conclusion:
In summary, a relation that meets the criteria of full functional dependency on the primary key and lacks transitive dependencies qualifies as being in Third Normal Form (3NF). This normalization ensures optimal data organization, integrity, and efficiency in relational databases.

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) - Question Bank for GATE Computer Science Engineering 2025 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) 2025 Exam. Find important definitions, questions, notes, meanings, examples, exercises, MCQs and online tests here.

Chapter doubts & questions of Database Management System (DBMS) - Question Bank for GATE Computer Science Engineering 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.

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
Signup to see your scores go up within 7 days!
Access 1000+ FREE Docs, Videos and Tests
Takes less than 10 seconds to signup