GATE CS Databases questions

Which one of the following statements is $\text{FALSE}$?

Consider the table employee(empId, name, department, salary) and the two queries $Q_1, \, Q_2$ below. Assuming that department $5$ has more than one employee, and we want to find the employees who get higher salary than anyone in the department $5,$ which one of the statements is TRUE for any arbitrary employee table? $Q_1:$ Select e.empId From …

Consider the relation employee(name, sex, supervisorName) with name as the key, supervisorName gives the name of the supervisor of the employee under consideration. What does the following Tuple Relational Calculus query produce? …

Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amounts $6000, 7000, 8000, 9000$ and $10000$ were each paid by $20\%$ of the students. Consider these …

Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries: Query1: select student from enrolled where student in (select student from paid) Query2: …

Consider the relation account (customer, balance) where the customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank $1.$ Ties are not broke but ranks are skipped: if exactly two customers have the largest balance they each get rank $1$ and …

Consider the following log sequence of two transactions on a bank account, with initial balance $12000,$ that transfer $2000$ to a mortgage payment and then apply a $5\%$ interest. T1 start T1 B old $=12000$ new $=10000$ T1 M old $=0$ new $=2000$ T1 commit T2 start T2 B old $=10000$ new $=10500$ T2 commit Suppose the database system crashes …

Consider a relation scheme $R = (A, B, C, D, E, H)$ on which the following functional dependencies hold: {$A \rightarrow B$, $BC \rightarrow D$, $E \rightarrow C$, $D \rightarrow A$}. What are the candidate keys R?

The relation book (title, price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list? select title from book as B where (select count(*) from book as T where T.price>B.price) < 5

The following table has two attributes $A$ and $C$ where $A$ is the primary key and $C$ is the foreign key referencing $A$ with on-delete cascade. …

Let $E_1$ and $E_2$ be two entities in an $E/R$ diagram with simple-valued attributes. $R_1$ and $R_2$ are two relationships between $E_1$ and $E_2$, where $R_1$ is one-to-many and $R_2$ is many-to-many. $R_1$ and $R_2$ do not have any attributes of their own. What is the minimum number of tables required to represent this situation in …

Let r be a relation instance with schema R = (A, B, C, D). We define $r_1 = \pi_{A, B, C} (R)$ and $r_2=\pi_{A, D} (r)$. Let $s =r_1 \: * \: r_2$ where $*$ denotes natural join. Given that the decomposition of $r$ into $r_1$ and $r_2$ is lossy, which one of the following is TRUE?

Which one of the following statements about normal forms is $\text{FALSE}?$

Which of the following is a key factor for preferring $B^+$-trees to binary search trees for indexing database relations?

The employee information in a company is stored in the relation Employee (name, sex, salary, deptName) Consider the following 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 department in which

The order of an internal node in a $B+$ tree index is the maximum number of children it can have. Suppose that a child pointer takes $6$ bytes, the search field value takes $14$ bytes, and the block size is $512$ bytes. What is the order of the internal node?

Consider the relation Student (name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: $\rho$ is the rename operator). …

The relation scheme $\text{Student Performance (name, courseNo, rollNo, grade)}$ has the following functional dependencies: name, courseNo, $\rightarrow$ grade rollNo, courseNo $\rightarrow$ grade name $\rightarrow$ rollNo rollNo $\rightarrow$ name The highest normal form of this relation scheme is

Consider the following relation schema pertaining to a students database: Students (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 …

Let $R_1 \left(\underline{A}, B, C\right)$ and $R_2\left(\underline{D}, E \right)$ be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in $R_1$ referring to $R_2$. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances $r_1$ and $r_2$. Which …