DBMS End Term: 28 April 2024, Set QDB1 (January 2024 term)

Let us consider the following statistics for searching a condition within a given relation.

• Number of blocks containing record of the relation $(b) = 400$
• Time to transfer one block $(t_b) = 0.6$ milliseconds
• Time for one seek $(t_s) = 8$ milliseconds

What will be the average cost of selection query on a key attribute using linear search file scan?

Consider the relational schema:

prescription(doctor_id, doctor_name, patient_id, patient_name, medicine_id, medicine_name), where the domains of all the attributes consist of atomic values. Consider the following FDs for the relation prescription .

• $doctor\_id \rightarrow doctor\_name$,
• $patient\_id \rightarrow patient\_name$,
• $medicine\_id \rightarrow medicine\_name$,
• $doctor\_id \rightarrow\rightarrow patient\_id$,
• $doctor\_id \rightarrow\rightarrow medicine\_id$

From among the decompositions given, identify the one that is in 4NF.

Consider the table Students given below:

Let hash function h(x) generate 16-bit binary hash values for the distinct elements in Department attribute:

Comp. Sci.- 1100 0010 1110 0101
History- 1000 1010 0101 1110
Maths- 0111 1100 0011 0110
Geography- 1110 0101 0000 1101
Music- 0100 1010 1111 1011
Biology- 0011 1111 1010 0101

If we insert the records in the following order:
Harry, Liam, Niall, Connor, Bradley, Luke, Louis, Zayn, Alex, Ashton.

Considering bucket size as 2, using dynamic hashing technique, which one of the following denotes the correct distribution of records in hash buckets?

Choose the correct statement(s):

Choose the correct statement(s):

Consider the following relations:

players(pid, name, age, jersey_no)

teams(team_name, matches, points, pid)

Choose the correct TRC or DRC expression which is equivalent to the below SQL query.

Consider you are designing a database schema for a university management system. One of the key relations, R, represents information about courses offered, including details such as course code ($X$), instructor ($Y$), course title ($Z$), and maximum enrollment capacity ($W$). The functional dependencies for this relation are as follows:

$\mathcal{F} = \{X \rightarrow Y, YZ \rightarrow W, W \rightarrow X\}$

During the normalization process, you decide to decompose R into two relations: R1$(XY)$ and R2$(YZW)$. Your goal is to ensure that this decomposition preserves all the information without any loss. Determine whether this decomposition is lossless or lossy. If it is lossy, identify which additional functional dependency from the following would make the decomposition lossless.

Choose the correct option(s).

Consider the following two schedules S1 and S2 and three transactions $T_1$, $T_2$, $T_3$:

$\mathbf{S1}: R_2(X); R_3(Y); W_2(X); R_1(X); W_3(Y); W_1(X); R_2(Y); W_2(Y);$

$\mathbf{S2}: R_3(X); W_3(X); W_2(X); W_2(Y); W_3(Z); R_1(Z); R_1(X); ; W_1(Y);$

where $R_i(X)$ denotes a read operation by transaction $T_i$ on a data item X, $W_i(X)$ denotes a write operation by transaction $T_i$ on a data item X.

Which among the following statements is/are correct?

Consider the following monthly backup schedule used by a company:

Let A be the number of backup sets that need to be loaded for a complete recovery, if there is a system failure on the 11th day of the month (after the backup for the day had been completed). Let B be the number of backup sets that need to be loaded for a complete recovery , if there is a system failure on the 25th day of the month (before the backup for the day had been completed).What will be the value of B-A?

Consider the table Points_Table given below to answer the given subquestions.

Table 1: Points_Table

What will be the output of the following SQL query:

Consider the table Points_Table given below to answer the given subquestions.

Table 1: Points_Table

Choose the correct expression(s) for the statement given below:
Name all the teams from England, with atmost 5 wins and at least 3 draws.