List of top Data Science and Artificial Intelligence Questions on Database Management Systems

Let \(R(A,B,C,D,E)\) be a relational schema with functional dependency set \(F = \{A \rightarrow BC,\ CD \rightarrow E,\ E \rightarrow A\}\).
Which of the following statements is correct?

Consider two relations \(r\) and \(s\) defined on the relational schemas \(R(A,B)\) and \(S(E,C)\), respectively. \(A\) is the primary key of \(R\) and \(E\) is a foreign key of \(S\) referencing \(A\) in \(R\).

Which of the following operations will NEVER violate the foreign key constraint?

In a relational database, a B+ Tree Index is to be constructed for a relation on a key field. In a B+ Tree, a Node Pointer points to a sub-tree and a Data Record Pointer points to a block of database records.

Let, Node size = 4096 bytes, Node Pointer size = 10 bytes, Search Key Field size = 11 bytes and Data Record Pointer size = 12 bytes.

The maximum number of Node Pointers that can be present in a non-leaf node of the B+ Tree is __________. (Answer in integer)

Consider a B+ Tree where the maximum number of key values in each leaf node is 2 and the maximum number of pointers in each non-leaf node is 3. Let the content of the B+ Tree be as shown in the figure.

Which of the following options denotes the key value(s) stored in the root node after inserting a key value 3 in the given B+ Tree?

Consider the given relations \(X\), \(Y\) and \(Z\). The relation \(X\) has three columns P, Q and R. The relation \(Y\) has three columns P, Q and S. The relation \(Z\) has two columns P and T.

Relation X:

P	Q	R
P1	Q1	R1
P2	Q2	R2
P3	Q3	R2

Relation Y:

P	Q	S
P1	Q1	2
P1	Q2	5
P2	Q1	6
P3	Q3	1

Relation Z:

P	T
P1	T1
P3	T2
P4	T3
P4	NULL

Consider the relational algebra expression
\[ \Pi_{P,R,S}\Big[\big(\sigma_{(Q=Q3 \vee R=R2)}[X \Join Y]\big) \Join \big(\sigma_{(S>1)}[Y \Join Z]\big)\Big] \]
where \(\Join\) denotes natural join operation.

Which of the following options is the correct output for the given expression?

Consider a table Employee(EmpID, TeamID), where the column EmpID (ID of an employee) is the primary key. The column TeamID denotes the team ID of the team of which the employee is a member. TeamID is a NOT NULL column.

We want to display the size of the team (denoted as TeamSize) in which each employee is a member by using SQL. As an example, the desired output for the given Employee table is also shown in tabular form.

Which of the following is/are correct?

Employee table:

EmpID	TeamID
1	8
2	8
3	8
4	7
5	7
6	9

Output table:

EmpID	TeamSize
1	3
2	3
3	3
4	2
5	2
6	1

Let there be two relations \(X\) and \(Y\) as shown. \(X\) has three columns \(P\), \(Q\) and \(R\). \(Y\) has two columns \(P\) and \(S\).

Relation X:

P	Q	R
P1	Q1	R1
P2	Q2	R2
P3	Q3	R2

Relation Y:

P	S
P1	10
P1	15
P2	20
P3	1

Consider that the following tuple relational calculus expression is evaluated.
\[ \{t \mid t \in X \wedge \exists z \in X (t[P] = z[P]) \wedge \exists m \in Y (m[P] = t[P] \wedge m[S] > 1)\} \]
The number of tuples that will be returned is _______. (Answer in integer)

Let Account be a relation as shown.

Table: Account

AccNo	Balance
A1	5000
A2	5000
A3	10000
A4	15000
A5	18000

Consider the given SQL query.

SELECT AccNo FROM Account AS A
WHERE (SELECT COUNT(*) FROM Account AS B
WHERE A.Balance < B.Balance) >= (SELECT COUNT(*)
FROM Account AS C WHERE A.Balance > C.Balance)

The number of rows returned by the SQL query is _______. (Answer in integer)

Consider an ER model with the entities E1(A11, A12, A13) and E2(A21, A22, A23), where A11, A12, A13 are the attributes of E1, and A21, A22, A23 are the attributes of E2. Let A22 be a multi-valued attribute. A11 and A21 are the primary keys of E1 and E2, respectively.

Let R12 be a many-to-many relationship between E1 and E2. Participation of both E1 and E2 in R12 is total.

The minimum number of relations required to convert the ER model into relational model (assuming there is no other functional dependency) where each relation is in third normal form (3NF) is _______. (Answer in integer)