All DBMS questions

Consider a relational database schema with a relation $R(A, B, C, D)$. If $\{A, B\}$ and $\{A, C\}$ are the only two candidate keys of the relation $R$, then the number of superkeys of relation $R$ is ______. (*answer in integer*)

Consider the following database tables of a sports league.

player(pid,pname,age)        team(tid,tname,city,cid)
coach(cid,cname)              members(pid,tid)

An instance of the table and an SQL query are given.

player                  coach            team                         members
pid  pname    age       cid  cname       tid  tname  city    cid      pid  tid
1    Jasprit  31        101  Ricky       10   MI     Mumbai  102      1    10
2    Atharva  24        102  Mark        20   DC     Delhi   101      2    30
3    Ishan    26        103  Trevor      30   PK     Mohali  103      3    10
4    Axar     30                                                      4    20

SELECT MIN(P.age)
FROM player P
WHERE P.pid IN (
    SELECT M.pid
    FROM team T, coach C, members M
    WHERE C.cname = 'Mark'
          AND T.cid = C.cid
          AND M.tid = T.tid
    )

The value returned by the given SQL query is ______. (Answer in integer)

In a B+- tree where each node can hold at most four key values, a root to leaf path consists of the following nodes: A = (49, 77, 83, -), B = (7, 19, 33, 44), C = (20*, 22*, 25*, 26*) The *-marked keys signify that these are data entries in a leaf. Assume that a pointer between keys $k_1$ and $k_2$ points to a subtree containing keys in $[k_1, k_2)$, and that when a leaf is created, the smallest key in it is copied up into its parent. A record with key value 23 is inserted into the B+- tree. The smallest key value in the parent of the leaf that contains 25* is __________. (Answer in integer)

Consider the following two relations, $R(A, B)$ and $S(A, C)$ shown in the figure. The total number of tuples obtained by evaluating the following expression $$\sigma_{B < C}\left(R \bowtie_{R.A = S.A} S\right)$$ is __________

A functional dependency $F: X \to Y$ is termed as a *useful functional dependency* if and only if it satisfies all the following three conditions: • $X$ is not the empty set. • $Y$ is not the empty set. • Intersection of $X$ and $Y$ is the empty set. For a relation $R$ with 4 attributes, the total number of possible useful functional dependencies is __________

Consider the following table named Student in a relational database. The primary key of this table is rollNum.

Student
rollNum  name    gender  marks
1        Naman   M       62
2        Aliya   F       70
3        Aliya   F       80
4        James   M       82
5        Swati   F       65

The SQL query below is executed on this database.

SELECT *
FROM Student
WHERE gender = 'F' AND
      marks > 65;

The number of rows returned by the query is __________

Consider a database of fixed-length records, stored as an ordered file. The database has 25,000 records, with each record being 100 bytes, of which the primary key occupies 15 bytes. The data file is block-aligned in that each data record is fully contained within a block. The database is indexed by a primary index file, which is also stored as a block-aligned ordered file. The figure below depicts this indexing scheme. Suppose the block size of the file system is 1024 bytes, and a pointer to a block occupies 5 bytes. The system uses binary search on the index file to search for a record with a given key. You may assume that a binary search on an index file of $b$ blocks takes $\lceil \log_2 b \rceil$ block accesses in the worst case. Given a key, the number of block accesses required to identify the block in the data file that may contain a record with the key, in the worst case, is __________

Consider a relation $R(A, B, C, D, E)$ with the following three functional dependencies. $$AB \to C;\quad BC \to D;\quad C \to E;$$ The number of superkeys in the relation R is __________

Consider the relational database with the following four schemas and their respective instances (see figure). Student(sNo, sName, dNo) Dept(dNo, dName) Course(cNo, cName, dNo) Register(sNo, cNo) SQL Query:

SELECT * FROM Student AS S WHERE NOT EXIST
  (SELECT cNo FROM Course WHERE dNo = "D01"
     EXCEPT
   SELECT cNo FROM Register WHERE sNo = S.sNo)

The number of rows returned by the above SQL query is __________

A relation $r(A, B)$ in a relational database has 1200 tuples. The attribute $A$ has integer values ranging from 6 to 20, and the attribute $B$ has integer values ranging from 1 to 20. Assume that the attributes $A$ and $B$ are independently distributed. The estimated number of tuples in the output of $\sigma_{(A > 10) \lor (B = 18)}(r)$ is __________.

A data file consisting of 1,50,000 student-records is stored on a hard disk with block size of 4096 bytes. The data file is sorted on the primary key RollNo. The size of a record pointer for this disk is 7 bytes. Each student-record has a candidate key attribute called ANum of size 12 bytes. Suppose an index file with records consisting of two fields, ANum value and the record pointer to the corresponding student record, is built and stored on the same disk. Assume that the records of data file and index file are not split across disk blocks. The number of blocks in the index file is __________.

Consider a database implemented using B+ tree for file indexing and installed on a disk drive with block size of 4 KB. The size of search key is 12 bytes and the size of tree/disk pointer is 8 bytes. Assume that the database has one million records. Also assume that no node of the B+ tree and no records are present initially in main memory. Consider that each record fits into one disk block. The minimum number of disk accesses required to retrieve any record in the database is _________.

A relational database contains two tables Student and Performance as shown below: The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together form the primary key. Consider the SQL query given below:

SELECT S.Student_name, sum(P.Marks)
FROM Student S, Performance P
WHERE P.Marks > 84
GROUP BY S.Student_name;

The number of rows returned by the above SQL query is _____.

Consider the following relations P(X,Y,Z), Q(X,Y,T) and R(Y,V). How many tuples will be returned by the following relational algebra query? $$\Pi_X\left(\sigma_{(P.Y = R.Y \land R.V = V2)}(P \times R)\right) - \Pi_X\left(\sigma_{(Q.Y = R.Y \land Q.T > 2)}(Q \times R)\right)$$

Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

EMP
EmpId  EmpName  DeptName
1      XYA      AA
2      XYB      AA
3      XYC      AA
4      XYD      AA
5      XYE      AB
6      XYF      AB
7      XYG      AB
8      XYH      AC
9      XYI      AC
10     XYJ      AC
11     XYK      AD
12     XYL      AD
13     XYM      AE

SELECT AVG(EC.Num)
FROM EC
WHERE (DeptName, Num) IN
      (SELECT DeptName, COUNT(EmpId) AS
                 EC(DeptName, Num)
       FROM EMP
       GROUP BY DeptName)

The output of executing the SQL query is ________.

Consider a database that has the relation schema CR(StudentName, CourseName). An instance of the schema CR is as given below.

CR
StudentName  CourseName
SA           CA
SA           CB
SA           CC
SB           CB
SB           CC
SC           CA
SC           CB
SC           CC
SD           CA
SD           CB
SD           CC
SD           CD
SE           CD
SE           CA
SE           CB
SF           CA
SF           CB
SF           CC

The following query is made on the database. $$T1 \leftarrow \pi_{CourseName}(\sigma_{StudentName = 'SA'}(\text{CR}))$$ $$T2 \leftarrow CR \div T1$$ The number of rows in $T2$ is ________.

Consider the following tables T1 and T2.

T1          T2
P   Q       R   S
2   2       2   2
3   8       8   3
7   3       3   2
5   8       9   7
6   9       5   7
8   5       7   2
9   8

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascade and on-update cascade. In table T2, R is the primary key and S is the foreign key referencing P in table T1 with on-delete set NULL and on-update cascade. In order to delete record $\langle 3, 8 \rangle$ from table T1, the number of additional records that need to be deleted from table T1 is ________.

Two transactions $T_1$ and $T_2$ are given as $T_1: r_1(X)\, w_1(X)\, r_1(Y)\, w_1(Y)$ $T_2: r_2(Y)\, w_2(Y)\, r_2(Z)\, w_2(Z)$ where $r_i(V)$ denotes a *read* operation by transaction $T_i$ on a variable $V$ and $w_i(V)$ denotes a *write* operation by transaction $T_i$ on a variable $V$. The total number of conflict serializable schedules that can be formed by $T_1$ and $T_2$ is ________.

Consider the following database table named *top_scorer*.

top_scorer
player       country     goals
Klose        Germany     16
Ronaldo      Brazil      15
G Müller     Germany     14
Fontaine     France      13
Pelé         Brazil      12
Klinsmann    Germany     11
Kocsis       Hungary     11
Batistuta    Argentina   10
Cubillas     Peru        10
Lato         Poland      10
Lineker      England     10
T Müller     Germany     10
Rahn         Germany     10

Consider the following SQL query:

SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals >ALL (SELECT tb.goals
                     FROM top_scorer AS tb
                     WHERE tb.country = 'Spain')
AND ta.goals >ANY (SELECT tc.goals
                     FROM top_scorer AS tc
                     WHERE tc.country = 'Germany')

The number of tuples returned by the above SQL query is ________.

In a B+ tree, if the search-key value is 8 bytes long, the block size is 512 bytes and the block pointer size is 2 bytes, then the maximum order of the B+ tree is ________.