Database Management System MCQ for UGC NET/GATE/ISRO/DRDO/NIELIT/KVS/NVS
Q1➡| What is the optimized version of the relation algebra expression πA1(πA2(σF1(σF2(r)))), where A1, A2 are sets of attributes in with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?
Q2➡| Database _____ which is the logical design of the database, and the database ______ which is a snapshot of the data in the database at a given instant in time.
Q3➡| Suppose R1(A, B) and R2(C, D) are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in R2. If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE?
Q5➡| Consider the schema R= ( S, T, U, V ) and the dependencies S→T, T→U, U→V and V→S. Let R (R1 and R2) be a decomposition such that R1∩R2 ≠ Ø. The decomposition is:
Q6➡| Relation R with an associated set of functional dependencies, F is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set relations is.
i ➥ Zero
ii ➥ More than zero but less than that of an equivalent 3NF decomposition
Q9➡| Given the following two statements: S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF. S2: AB->C, D->E, E->C is a minimal cover for the set of functional dependencies AB->C, D->E, AB->E, E->C.
Q11➡| Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH -> G, A -> BC, B -> CFH, E -> A, F -> EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.
Q12➡| Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH -> G, A -> BC, B -> CFH, E -> A, F -> EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.
Q14➡| Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ΠA,B,C(R) and r2 = ΠA,D(R). Let s = r1*r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is TRUE?
Q15➡| Let R1(A,B,C) and R2(D,E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R1 referring to R2. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r1 and r2. Which one of the following relational algebra expressions would necessarily produce an empty relation?
Q16➡| Which of the following query transformations (i.e., replacing the l.h.s. expression by the r.h.s. expression) is incorrect? R1 and R2 are relations. C1, C2 are selection conditions and A1, A2 are attributes of R1.
Q20➡| A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables: Insert into department values (1, ‘Mathematics’) Insert into department values (2, ‘Physics’) Insert into student values (l, ‘Navin’, 1) Insert into student values (2, ‘Mukesh’, 2) Insert into student values (3, ‘Gita’, 1) How many rows and columns will be retrieved by the following SQL statement?
Q21➡| Consider the join of a relation R , with a relation S . If R has m number of tuples and S has n number of tuples then the maximum and minimum sizes of the join respectively are:
Q22➡| Given the relations employee (name, salary, deptno) and department (deptno, deptname, address) Which of the following queries cannot be expressed using the basic relational algebra operations (U, -, x, π ,σ , p)?
i ➥ Department address of every employee
ii ➥ Employees whose name is the same as their department name
Q24➡| Consider two relations R1(A,B) with the tuples (1,5), (3,7) and R2(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R1 and R2. Consider the following tuples of the form (A,B,C): a = (1.5,null), b = (1,null,7), c = (3,null,9), d = (4,7,null), e = (1,5,7), f = (3,7,null), g = (4,null,9). Which one of the following statements is correct?
Q25➡| Which of the following functional dependencies hold for relations R(A, B, C) and S(B, D, E): B -> A A -> C The relation R contains 200 tuples and the relation S contains 100 tuples. What is the maximum number of tuples possible in the natural join of R and S (R natural join S)
Q29➡| Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies F = { {P, R} → {S,T}, {P, S, U} → {Q, R} } Which of the following is the trivial functional dependency in F+ is closure of F?
Q30➡| In a schema with attributes A, B, C, D and E following set of functional dependencies are given A → B A → C CD → E B → D E → A Which of the following functional dependencies is NOT implied by the above set?
Q32➡| Let x, y, z, a, b, c be the attributes of an entity set E. If {x}, {x,y}, {a,b}, {a,b,c}, {x,y,z} are superkeys then which of the following are the candidate keys?
Q38➡| The completeness constraint may be one of the following: Total generalization or specialization, Partial generalization or specialization. Which is the default?
Q39➡| Consider the employee work-team example, and assume that certain employees participate in more than one work team. A given employee may therefore appear in more than one of the team entity sets that are lower level entity sets of employee. Thus, the generalization is________.
Q40➡| There are similarities between the instructor entity set and the secretary entity set in the sense that they have several attributes that are conceptually the same across the two entity sets: namely, the identifier, name, and salary attributes. This process is called
Q44➡| Consider a database that has the relation schema CR(StudentName, CourseName). An instance of the schema CR is as given below. The following query is made on the database. T1 ← πCourseName(σStudentName=’SA’(CR)) T2 ← CR ÷ T1 The number of rows in T2 is ____________.
Q45➡| 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 〈3,8〉 from table T1, the number of additional records that need to be deleted from table T1 is _____.
Q46➡| 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: ρ is the rename operator).
i ➥ Names of girl students with the highest marks
ii ➥ Names of girl students with more marks than some boy student
iii ➥ Names of girl students with marks not less than some boy student
iv ➥ Names of girl students with more marks than all the boy students
Q47➡| 40∨C.Id<15 C"> How many tuples does the result of the following relational algebra expression contain? Assume that the schema of AUB is the same as that of A. (AUB)⋈A.Id>40∨C.Id<15 C
Q48➡| Consider the following relation schema pertaining to a students database: Student (rollno, name, address) Enroll (rollno, course no, 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 (Student * Enroll), where ‘*’ denotes natural join ?
Q49➡| 2)(Q × R))”> How many tuples will be returned by the following relational algebra query? ∏x(σ(P.Y=R.Y ∧ R.V=V2)(P × R)) – ∏x(σ(Q.Y=R.Y ∧ Q.T>2)(Q × R))
Q50➡| Suppose now that R(A,B) and S(A,B) are two relations with r and s tuples, respectively (again, not necessarily distinct). If m is the number of (not necessarily distinct) tuples in the result of the SQL query: R intersect S; Then which of the following is the most restrictive, correct condition on the value of m?
Q53➡| A table has fields F1, F2, F3, F4, and F5, with the following functional dependencies: F1->F3 F2->F4 (F1,F2)->F5 in terms of normalization, this table is in
Q57➡| Let R1(a,b,c) and R2(x,y,z) be two relations in which a is the foreign key of R1 that refers to the primary key of R2 . Consider following four options. (a) Insert into R1 (b) Insert into R2 (c) Delete from R1 (d) Delete from R2 Which of the following is correct about the referential integrity constraint with respect to above ?
i ➥ Operations (a) and (b) will cause violation.
ii ➥ Operations (b) and (c) will cause violation.
iii ➥ Operations (c) and (d) will cause violation.
40∨C.Id<15 C">
2)(Q × R))”>