# Gate/GATE STUDY MATERIAL /DBMS SET 1 Sample Test,Sample questions

## Question: ``` Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {Aâ€“>B, BCâ€“>D, Eâ€“>C, Dâ€“>A}. What are the candidate keys of R?```

1.AE, BE

2. AE, BE, DE

3. AEH, BEH, BCH

4. AEH, BEH, DEH

Posted Date:-2022-06-15 10:18:40

## Question: ``` Consider the FDs given in above question. The relation R is```

1.in 1NF, but not in 2NF

2.in 2NF, but not in 3NF

3.n 3NF, but not in BCNF

4.in BCNF

Posted Date:-2022-06-15 08:18:13

## Question: ``` Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection (Title, Author, Catalog_no) with in the following functional dependencies: I. Title Author --> Catalog_no II. Catalog_no --> Title, Author, Publisher, Year III. Publisher Title Year --> Price Assume {Author, Title} is the key for both schemes. Which of the following statements is true?```

1.Both Book and Collection are in BCNF

2.Both Book and Collection are in 3NF only

3.Book is in 2NF and Collection is in 3NF d.

4.Both Book and Collection are in 2NF only

Posted Date:-2022-06-15 08:36:48

## Question: ``` Given the following schema: employees(emp-id, first-name, last-name, hire-date, dept-id, salary) departments(dept-id, dept-name, manager-id, location-id) You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query: SQL> SELECT last-name, hire-date FROM employees WHERE (dept-id, hire-date) IN (SELECT dept-id, MAX(hire-date) FROM employees JOIN departments USING(dept-id) WHERE location-id = 1700 GROUP BY dept-id); What is the outcome?```

1.It executes but does not give the correct result

2.It executes and gives the correct result

3.generates an error because of pairwise comparison

4.It generates an error because the GROUP BY clause cannot be used with table joins in a subquery

Posted Date:-2022-06-15 06:27:07

## Question: ``` Given the following statements: S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL. S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition. CREATE TABLE S ( a INTEGER, d INTEGER, e INTEGER, PRIMARY KEY (d), FOREIGN KEY (a) references R) Which one of the following statements is CORRECT?```

1.S1 is TRUE and S2 is FALSE

2. Both S1 and S2 are TRUE

3.S1 is FALSE and S2 is TRUE

4.Both S1 and S2 are FALSE

Posted Date:-2022-06-15 06:25:56

## Question: ``` 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?```

1.CD â†’ AC

2.BD â†’ CD

3.BC â†’ CD

4.AC â†’ BC

Posted Date:-2022-06-15 07:39:16

## Question: ``` The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is```

1.5

2.6

3.7

4.8

Posted Date:-2022-06-15 08:46:58

## Question: ``` Which of the following command is used to delete a table in SQL? ```

1.delete

2.truncate

3.remove

4.drop

Posted Date:-2022-06-15 06:47:50

## Question: ``` Which one of the following statements about normal forms is FALSE? ```

1.BCNF is stricter than 3NF

2.Lossless, dependency-preserving decomposiÂ­tion into 3NF is always possible

3.Lossless, dependency-preserving decomposiÂ­tion into BCNF is always possible

4.Any relation with two attributes is in BCNF

Posted Date:-2022-06-15 08:48:46

## Question: ``` With regard to the expressive power of the formal relational query languages, which of the following statements is true?```

1.Relational algebra is more powerful than relational calculus

2.https://compsciedu.com/mcq-questions/DBMS/GATE-cse-question-paper/6#:~:text=Relational%20algebra%20has%20the%20same%20power%20as%20relational%20calculus

3.Relational algebra has the same power as safe relational calculus

4.None of the above

Posted Date:-2022-06-15 10:33:47

## Question: ` r âŠ‚ s `

1. s âŠ‚ r

2. r âˆª s

3. r âŠ‚ s

4. r * s = s

Posted Date:-2022-06-15 08:58:58

## Question: ```A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema: salesinfo = (salespersonid, totalsales, commission) In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula: If commission < = 50000, enhance it by 2% If 50000 < commission < = 100000, enhance it by 4% If commission > 100000, enhance it by 6% The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows: T1 Update salesinfo Set commission = commission * 1.02 Where commission < = 50000; T2 Update salesinfo Set commission = commission * 1.04 Where commission > 50000 and commission is < = 100000; T3 Update salesinfo Set commission = commission * 1.06 Where commission > 100000; Which of the following options of running these transactions will update the commission of all salespersons correctly?```

1.Execute T1 followed by T2 followed by T3

2.Execute T2, followed by T3; T1 running concurrently throughout

3. Execute T3 followed by T2; T1 running concurrently throughout

4.Execute T3 followed by T2 followed by T1

Posted Date:-2022-06-15 07:26:09

## Question: ```A database of research articles in a journal uses the following schema. (VOLUME, NUMBER, STARTPGE, ENDPAGE, TITLE, YEAR, PRICE) The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema. (VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> TITLE (VOLUME, NUMBER) -> YEAR (VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> PRICE The database is redesigned to use the following schemas. (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE) (VOLUME, NUMBER, YEAR) Which is the weakest normal form that the new database satisfies, but the old one does not?```

1.1NF

2.2NF

3.3NF

4.BCNF

Posted Date:-2022-06-15 07:41:01

## Question: `A Relation R with FD set {A->BC, B->A, A->C, A->D, D->A}. How many candidate keys will be there in R?`

1.1

2.2

3.3

4.4

Posted Date:-2022-06-15 07:43:15

## Question: ```A relational schema for a train reservation database is given below. Passenger (pid, pname, age) Reservation (pid, class, tid) Table: Passenger pid pname age ----------------- 0 Sachin 65 1 Rahul 66 2 Sourav 67 3 Anil 69 Table : Reservation pid class tid --------------- 0 AC 8200 1 AC 8201 2 SC 8201 5 AC 8203 1 SC 8204 3 AC 8202 What pids are returned by the following SQL query for the above instance of the tables? SELECT pid FROM Reservation , WHERE class â€˜ACâ€™ AND EXISTS (SELECT * FROM Passenger WHERE age > 65 AND Passenger. pid = Reservation.pid)```

1.1, 0

2.1, 2

3.1, 3

4.1, 5

Posted Date:-2022-06-15 06:18:27

## Question: ```A table 'student' with schema (roll, name, hostel, marks), and another table 'hobby' with schema (roll, hobbyname) contains records as shown below: Table: Student ROLL NAME HOSTEL MARKS 1798 Manoj Rathod 7 95 2154 Soumic Banerjee 5 68 2369 Gumma Reddy 7 86 2581 Pradeep Pendse 6 92 2643 Suhas Kulkarni 5 78 2711 Nitin Kadam 8 72 2872 Kiran Vora 5 92 2926 Manoj Kunkalikar 5 94 2959 Hemant Karkhanis 7 88 3125 Rajesh Doshi 5 82 Table: hobby ROLL HOBBYNAME 1798 chess 1798 music 2154 music 2369 swimming 2581 cricket 2643 chess 2643 hockey 2711 volleyball 2872 football 2926 cricket 2959 photography 3125 music 3125 chess The following SQL query is executed on the above tables: select hostel from student natural join hobby where marks > = 75 and roll between 2000 and 3000; Relations S and H with the same schema as those of these two tables respectively contain the same information as tuples. A new relation Sâ€™ is obtained by the following relational algebra operation: Sâ€™ = âˆhostel ((Ïƒs.roll = H.roll (Ïƒmarks > 75 and roll > 2000 and roll < 3000 (S)) X (H)) The difference between the number of rows output by the SQL statement and the number of tuples in Sâ€™ is ```

1.6

2.4

3.2

4.0

Posted Date:-2022-06-15 07:27:22

## Question: ```Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and My denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out? SELECT Y FROM T WHERE X=7;```

1.127

2.255

3.129

4.257

Posted Date:-2022-06-15 06:15:11

## Question: `Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size(r(R)) < size(s(S)), the join will have fewer number of disk block accesses if`

1.relation r(R) is in the outer loop

2.relation s(S) is in the outer loop

3.join selection factor between r(R) and s(S) is more than 0.5

4.None of These

Posted Date:-2022-06-15 07:34:28

## Question: ```Consider a relational table with a single record for each registered student with the following attributes. 1. Registration_Num: Unique registration number of each registered student 2. UID: Unique identity number, unique at the national level for each citizen 3. BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or join accounts. This attribute stores the primary account number. 4. Name: Name of the student 5. Hostel_Room: Room number of the hostel Which one of the following option is INCORRECT?```

1.BankAccount_Num is candidate key

2.Registration_Num can be a primary key

3.UID is candidate key if all students are from the same country

4.If S is a superkey such that Sâˆ©UID is NULL then SâˆªUID is also a superkey

Posted Date:-2022-06-15 08:22:20

## Question: ```Consider a relational table with a single record for each registered student with the following attributes. 1. Registration_Num: Unique registration number of each registered student 2. UID: Unique identity number, unique at the national level for each citizen 3. BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or join accounts. This attribute stores the primary account number. 4. Name: Name of the student 5. Hostel_Room: Room number of the hostel Which one of the following option is INCORRECT?```

1.BankAccount_Num is candidate key

2.Registration_Num can be a primary key

3.UID is candidate key if all students are from the same country

4.If S is a superkey such that Sâˆ©UID is NULL then SâˆªUID is also a superkey

Posted Date:-2022-06-15 08:24:12

## Question: `Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D. Then the decomposition of R into R1(AB) and R2(CD) is`

1.dependency preserving and lossless join

2.lossless join but not dependency preserving

3. dependency preserving but not lossless join

4.not dependency preserving and not lossless join

Posted Date:-2022-06-15 10:41:56

## Question: ```Consider the following Employee table ID salary DeptName 1 10000 EC 2 40000 EC 3 30000 CS 4 40000 ME 5 50000 ME 6 60000 ME 7 70000 CS How many rows are there in the result of following query? SELECT E.ID FROM Employee E WHERE EXISTS (SELECT E2.salary FROM Employee E2 WHERE E2.DeptName = 'CS' AND E.salary > E2.salary)```

1.0

2.4

3.5

4.6

Posted Date:-2022-06-15 07:06:14

## Question: ```Consider the following functional dependencies in a database: Data_of_Birth â†’ Age Age â†’ Eligibility Name â†’ Roll_number Roll_number â†’ Name Course_number â†’ Course_name Course_number â†’ Instructor (Roll_number, Course_number) â†’ Grade The relation (Roll_number, Name, Date_of_birth, Age) is:```

1.In second normal form but not in third normal form

2. In third normal form but not in BCNF

3. In BCNF

4.None of the above

Posted Date:-2022-06-15 10:22:57

## Question: ```Consider the following relation Cinema (theater, address, capacity) Which of the following options will be needed at the end of the SQL query SELECT P1. address FROM Cinema P1 Such that it always finds the addresses of theaters with maximum capacity?```

1.WHERE P1. Capacity> = All (select P2. Capacity from Cinema P2)

2.WHERE P1. Capacity> = Any (select P2. Capacity from Cinema P2)

3.WHERE P1. Capacity > All (select max(P2. Capacity) from Cinema P2)

4.WHERE P1. Capacity > Any (select max (P2. Capacity) from Cinema P2)

Posted Date:-2022-06-15 07:24:28

## Question: ```Consider the following relation schema pertaining to a students database: Student (rollno, name, address) Enroll (rollno, courseno, coursename) where the primary keys are rollno,courseno. 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 ?```

1.8, 0

2.120, 8

3.960, 8

4.960, 120

Posted Date:-2022-06-15 06:56:36

## Question: ```Consider the following relational schema: employee(empId, empName, empDept) customer(custId, custName, salesRepId, rating) salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return? SELECT empName FROM employee E WHERE NOT EXISTS (SELECT custId FROM customer C WHERE C.salesRepId = E.empId AND C.rating <> â€™GOODâ€™);```

1.Names of all the employees with at least one of their customers having a â€˜GOODâ€™ rating.

2.Names of all the employees with at most one of their customers having a â€˜GOODâ€™ rating.

3.Names of all the employees with none of their customers having a â€˜GOODâ€™ rating.

4.Names of all the employees with all their customers having a â€˜GOODâ€™ rating.

Posted Date:-2022-06-15 06:36:45

## Question: ```Consider the following relational schema: Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real) Consider the following relational query on the above database: SELECT S.sname FROM Suppliers S WHERE S.sid NOT IN (SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color<> 'blue')) Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?```

1.Find the names of all suppliers who have supplied a non-blue part.

2.Find the names of all suppliers who have not supplied a non-blue part.

3.Find the names of all suppliers who have supplied only blue parts.

4.Find the names of all suppliers who have not supplied only blue parts.

Posted Date:-2022-06-15 06:20:09

## Question: ```Consider the following relational schema: Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real) (sid,pid are primary keys) Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?```

1.The schema is in BCNF

2. The schema is in 2NF but not in 3NF

3. The schema is in 2NF but not in 3NF

4. The schema is not in 2NF

Posted Date:-2022-06-15 08:30:18

## Question: ```Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection (Title, Author, Catalog_no) with in the following functional dependencies: I. Title Author --> Catalog_no II. Catalog_no --> Title, Author, Publisher, Year III. Publisher Title Year --> Price Assume {Author, Title} is the key for both schemes. Which of the following statements is true?```

1.Both Book and Collection are in BCNF

2.Both Book and Collection are in BCNF

3.Both Book and Collection are in 3NF only

4. Both Book and Collection are in 2NF only

Posted Date:-2022-06-15 08:42:31

## Question: ```Consider the following three table to store student enrollements in different courses. Student(EnrollNo, Name) Course(CourseID, Name) EnrollMents(EnrollNo, CourseID) (EnrollNo,CourseID are primary keys) What does the following query do? SELECT S.Name FROM Student S, Course C, Enrollments E WHERE S.EnrollNo = E.EnrollNo AND C.Name = "DBMS" AND E.CourseID = C.CourseID AND S.EnrollNo IN (SELECT S2.EnrollNo FROM Student S2, Course C2, Enrollments E2 WHERE S2.EnrollNo = E2.EnrollNo AND E2.CourseID = C2.CourseID C2.Name = "OS")```

1.Name of all students who are either enrolled in "DBMS" or "OS" courses

2.Name of all students who are enrolled in "DBMS" and "OS"

3.Name of all students who are either enrolled in "DBMS" or "OS" or both

4.None of the above

Posted Date:-2022-06-15 07:05:29

## Question: ```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: select student from paid where student in (select student from enrolled) Query3: select E.student from enrolled E, paid P where E.student = P.student Query4: select student from paid where exists (select * from enrolled where enrolled.student = paid.student) Which one of the following statements is correct?```

1.All queries return identical row sets for any database

2.Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.

3.There exist databases for which Query4 will encounter an integrity violation at runtime.

4.There exist databases for which Query3 returns strictly fewer rows than Query2

Posted Date:-2022-06-15 06:51:11

## Question: `Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, M} and the set of functional dependencies {{E, F} -> {G}, {F} -> {I, J}, {E, H} -> {K, L}, K -> {M}, L -> {N} on R. What is the key for R?`

1.{E, F}

2. {E, F, H}

3. {E, F, H, K, L}

4. {E}

Posted Date:-2022-06-15 08:43:41

## Question: `Consider the relations r1(P, Q, R) and r2(R, S, T) with primary keys P and R respectively. The relation r1 contains 2000 tuples and r2 contains 2500 tuples. The maximum size of the join r1â‹ˆ r2 is :`

1.2000

2.2500

3.4500

4.5000

Posted Date:-2022-06-15 07:29:41

## Question: ```Consider the set of relations shown below and the SQL query that follows. Students: (Roll_number, Name, Date_of_birth) Courses: (Course number, Course_name, Instructor) Grades: (Roll_number, Course_number, Grade) select distinct Name from Students, Courses, Grades where Students. Roll_number = Grades.Roll_number and Courses.Instructor = Korth and Courses.Course_number = Grades.Course_number and Grades.grade = A Which of the following sets is computed by the above query?```

1.Names of students who have got an A grade in all courses taught by Korth

2.Names of students who have got an A grade in all courses

3.Names of students who have got an A grade in at least one of the courses taught by Korth

4. None of the above

Posted Date:-2022-06-15 06:59:23

## Question: ```Consider the table employee(empId, name, department, salary) and the two queries Q1 ,Q2 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? Q1 : Select e.empId From employee e Where not exists (Select * From employee s where s.department = â€œ5â€ and s.salary >=e.salary) Q2 : Select e.empId From employee e Where e.salary > Any (Select distinct salary From employee s Where s.department = â€œ5â€)```

1.Q1 is the correct query

2.Q2 is the correct query

3. Both Q1 and Q2 produce the same answer

4.Neither Q1 nor Q2 is the correct quer

Posted Date:-2022-06-15 06:23:29

## Question: ```Database table by name Loan_Records is given below. Borrower Bank_Manager Loan_Amount Ramesh Sunderajan 10000.00 Suresh Ramgopal 5000.00 Mahesh Sunderajan 7000.00 What is the output of the following SQL query? SELECT Count(*) FROM ( (SELECT Borrower, Bank_Manager FROM Loan_Records) AS S NATURAL JOIN (SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T );```

1.3

2.9

3.5

4.6

Posted Date:-2022-06-15 06:16:44

## Question: ```From the following instance of a relation scheme R (A, B, C), we can conclude that : A B C 1 1 1 1 1 0 2 3 2 2 3 2```

1.A functionally determines B and B functionally determines C

2.A functionally determines B and B does not functionally determine C

3.B does not functionally determine C

4.A does not functionally determine B and B does not functionally determine C

Posted Date:-2022-06-15 10:36:55

## Question: ```Given relations r(w, x) and s(y, z), the result of SELECT DISTINCT w, x FROM r, s is guaranteed to be same as r, provided```

1.r has no duplicates and s is non-empty

2.r and s have no duplicates

3. s has no duplicates and r is non-empty

4.r and s have the same number of tuples

Posted Date:-2022-06-15 07:01:31

## Question: ```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. Which one of the following is CORRECT?```

1.S1 is TRUE and S2 is FALSE

2. Both S1 and S2 are TRUE

3.S1 is FALSE and S2 is TRUE

4.Both S1 and S2 are FALSE

Posted Date:-2022-06-15 08:45:26

## Question: ```In an inventory management system implemented at a trading corporation, there are several tables designed to hold all the information. Amongst these, the following two tables hold information on which items are supplied by which suppliers, and which warehouse keeps which items along with the stock-level of these items. Supply = (supplierid, itemcode) Inventory = (itemcode, warehouse, stocklevel) For a specific information required by the management, following SQL query has been written Select distinct STMP.supplierid From Supply as STMP Where not unique (Select ITMP.supplierid From Inventory, Supply as ITMP Where STMP.supplierid = ITMP.supplierid And ITMP.itemcode = Inventory.itemcode And Inventory.warehouse = 'Nagpur'); For the warehouse at Nagpur, this query will find all suppliers who```

1.do not supply any item

2.supply exactly one item

3.supply one or more items

4.supply two or more items

Posted Date:-2022-06-15 07:28:28

## Question: `In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent?`

1.x = 5 AND not(not(x = 5))

2.x = 5 AND x> 4 and x < 6, where x is an integer

3. x < 5 AND not (x = 5)

4.None of the above

Posted Date:-2022-06-15 07:03:18

## Question: `Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?`

1.2

2.3

3.4

4.5

Posted Date:-2022-06-15 07:37:37

## Question: `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?`

1.s âŠ‚ r

2.r âˆª s

3.r âŠ‚ s

4. r * s = s

Posted Date:-2022-06-15 08:50:38

## Question: `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. How many candidate keys does the relation R have?`

1.3

2.4

3.5

4.6

Posted Date:-2022-06-15 08:16:18

## Question: `Relation R is decomposed using a set of functional dependencies, F and relation S is decomposed using another set of functional dependencies G. One decomposition is definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a guaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of F and G are available).`

1.Dependency-preservation

2.Lossless-join

3.BCNF definition

4. 3NF definition

Posted Date:-2022-06-15 10:35:25

## Question: `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.`

1.Zero

2. More than zero but less than that of an equivalent 3NF decomposition

3.Proportional to the size of F+

4.Indeterminate

Posted Date:-2022-06-15 10:32:08

## Question: `Select operation in SQL is equivalent to`

1.the selection operation in relational algebra

2. the selection operation in relational algebra, except that select in SQL retains duplicates

3.the projection operation in relational algebra

4.the projection operation in relational algebra, except that select in SQL retains duplicates

Posted Date:-2022-06-15 07:08:47

## Question: ```SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below: select * from R where a in (select S.a from S)```

1.select R.* from R, S where R.a=S.a (D)

2.select distinct R.* from R,S where R.a=S.a

3.select R.* from R,(select distinct a from S) as S1 where R.a=S1.a

4.select R.* from R,S where R.a=S.a and is unique R

Posted Date:-2022-06-15 06:33:48

## Question: ```Student (school-id, sch-roll-no, sname, saddress) School (school-id, sch-name, sch-address, sch-phone) Enrolment(school-id sch-roll-no, erollno, examname) ExamResult(erollno, examname, marks) What does the following SQL query output? SELECT sch-name, COUNT (*) FROM School C, Enrolment E, ExamResult R WHERE E.school-id = C.school-id AND E.examname = R.examname AND E.erollno = R.erollno AND R.marks = 100 AND S.school-id IN (SELECT school-id FROM student GROUP BY school-id HAVING COUNT (*) > 200) GROUP By school-id```

1.for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students

2.for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students

3.for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam

4.nothing; the query has a syntax error

Posted Date:-2022-06-15 07:31:42

## Question: ```Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X,Y). Which of the following queries cannot be expressed by a relational algebra expression of constant length? ```

1.List of all vertices adjacent to a given vertex

2.List all vertices which have self loops

3.List all vertices which belong to cycles of less than three vertices

4.List all vertices reachable from a given vertex

Posted Date:-2022-06-15 10:44:09

## Question: ```Table A Id Name Age ---------------- 12 Arun 60 15 Shreya 24 99 Rohit 11 Table B Id Name Age ---------------- 15 Shreya 24 25 Hari 40 98 Rohit 20 99 Rohit 11 Table C Id Phone Area ----------------- 10 2200 02 99 2100 01 Consider the above tables A, B and C. How many tuples does the result of the following SQL query contains? SELECT A.id FROM A WHERE A.age > ALL (SELECT B.age FROM B WHERE B. name = "arun")```

1.4

2.3

3.0

4.1

Posted Date:-2022-06-15 06:13:56

## Question: ```The employee information in a company is stored in the relation Employee (name, sex, salary, deptName) (name is primary key ) 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```

1.the average salary is more than the average salary in the company

2.the average salary of male employees is more than the average salary of all male employees in the company

3.the average salary of male employees is more than the average salary of employees in the same department

4.the average salary of male employees is more than the average salary in the company

Posted Date:-2022-06-15 06:58:01

## Question: ```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. A C ----- 2 4 3 4 4 3 5 2 7 2 9 5 6 4 The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:```

1.(3,4) and (6,4)

2. (5,2) and (7,2)

3.(5,2), (7,2) and (9,5)

4. (3,4), (4,3) and (6,4)

Posted Date:-2022-06-15 06:52:22

## Question: ```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```

1.Titles of the four most expensive books

2. Title of the fifth most inexpensive book

3.Title of the fifth most expensive bookTitles of the five most expensive books

4. Titles of the five most expensive books

Posted Date:-2022-06-15 06:53:52

## Question: ```The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies: name, courseNo â†’ grade rollNo, courseNo â†’ grade name â†’ rollNo rollNo â†’ name The highest normal form of this relation scheme is```

1.2 NF

2. 3 NF

3.BCNF

4. 4NF

Posted Date:-2022-06-15 10:21:37

## Question: `The statement that is executed automatically by the system as a side effect of the modification of the database is`

1.backup

2.assertion

3.recovery

4.trigger

Posted Date:-2022-06-15 06:41:34

## Question: `What is the min and max number of tables required to convert an ER diagram with 2 entities and 1 relationship between them with partial participation constraints of both entities?`

1.Min 1 and max 2

2.Min 1 and max 3

3.Min 2 and max 3

4. Min 2 and max 2

Posted Date:-2022-06-15 07:44:28

## Question: `Which of the following is TRUE?`

1.Every relation in 3NF is also in BCNF

2. A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R

3.Every relation in BCNF is also in 3NF

4.No relation can be in both BCNF and 3NF

Posted Date:-2022-06-15 08:20:11

## Question: ```Which of the following relational query languages have the same expressive power? 1.Relational algebra 2.Tuple relational calculus restricted to safe expressions 3.Domain relational calculus restricted to safe expressions```

1.II and III only

2. I and II only

3.I and III only

4. I, II and III

Posted Date:-2022-06-15 07:42:17

## Question: ```Which of the following statements are TRUE about an SQL query? P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause R : All attributes used in the GROUP BY clause must appear in the SELECT clause S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause```

1.P and R

2.P and S

3.Q and R

4.Q and S

Posted Date:-2022-06-15 06:11:47

## More MCQS

##### R4R Team
R4Rin Top Tutorials are Core Java,Hibernate ,Spring,Sturts.The content on R4R.in website is done by expert team not only with the help of books but along with the strong professional knowledge in all context like coding,designing, marketing,etc!