GATE QUESTIONS

[Audio] Hello Everyone, Today we are gonna solve the gate cse question papers.

[Audio] This is the 68th question of 2006th year cse gate quesetion paper and the question is 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:.

[Audio] and its related four queries are : 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) now let's check how all this queries behave.

[Audio] Query1: Selects students who are enrolled and have also paid. This query checks for students who are in the enrolled table and also in the paid table. Query2: Selects students who have paid and are also enrolled. This query checks for students who are in the paid table and also in the enrolled table. Query3: Joins the enrolled and paid tables and selects the students who are in both. This query explicitly joins the tables based on the student column and selects the student from the enrolled table. Query4: Selects students from the paid table where there exists a corresponding entry in the enrolled table. This query checks if there is any entry in the enrolled table corresponding to each entry in the paid table. Now let's analyze the options:.

[Audio] A) All queries return identical row sets for any database - This is not necessarily true because the queries are structured differently. (B) Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets. - This option seems plausible as Query2 and Query4 essentially check for the same condition. Query1 and Query2 might return different results depending on the specific data in the database. (C) There exist databases for which Query3 returns strictly fewer rows than Query2 - This is possible because Query3 explicitly performs a join between the tables, which might result in fewer rows compared to Query2. (D) There exist databases for which Query4 will encounter an integrity violation at runtime. - There are no foreign key or integrity constraints mentioned in the question, so there shouldn't be any integrity violations based on the provided information..

[Audio] So, the correct answer seems to be (B) Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets..

THE QUESTION. Q.70 In a Schema with attribute 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? A) CD -> AC B) BD -> CD C) BC -> CD D) AC -> BC.

EXPLANATION. Functional dependency:. -It is a concept that specifies the relationship between two sets of attributes where one attribute determines the value of another attribute. -It is denoted as X → Y, where the attribute set on the left side of the arrow, X is called Determinant, and Y is called the Dependent..

EXPLANATION. Closure of sets:. 1) CD->AC CD+ = CDEAB 2) BD->CD BD+ = BD 3) BC->CD BC+ = BCDEA 4) AC-> BC AC+ = ACBDE.

TRY THIS. Q. Consider a relation schema R(A,B,C). If A -> B and B -> C are functional dependencies on R, then which of the following can be considered as candidate key ? A) AB B) B C) BC D) C E) A.

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

01. (A) 2. 02. 03. 04. (B) 3. (C) 4. (D) 5. CORRECT ANSWER : OPTION B.

EXPLANATION. This is a many-to-one relationship. All performers must have an agent associated with them, but not all agents will be involved in a booking for a performer.A typical way to implement this is for each Performer row to have a foreign key field that holds the value of the related agents primary key.The attribute agent-id is therefore a foreign key in the Performer relation. So, no, it is not necessary to create a third table to create a one-to-many relationship..

EXPLANATION. This is a many-to-many relationship all performers must be represented by agents, and all agents must represent performers.Three relations are required to represent a relationship of this kind between two entities, one for each entity and one for the relationship itself, i.e. one to represent the performers, another to represent the agents, and a third to represent the relationship between the performers and the agent.

EXPLANATION. Here In one to many relationship E2 merge with R1 so require 2 tables and many to many R2 required to show realtions between E1 and E2 so require 3 tables but 2 alreday in one to many so 1 is here Therefore total 3 tables Required.

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..

ORDER ID ORDER DATE 1 4/4/22 2 5/6/22. ORDER DETAILS ID ORDER ID PRODUCT ID QUANTITY 1 1 105 5000 2 1 106 10000 3 2 107 2000.

Q) 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?.

Title Price JAVA 100 DBMS 200 C++ 300 IOT 400 OS 500 DS 600 python 700.

Title count JAVA 6 DBMS 5 C++ 4 IOT 3 OS 2 DS 1 python 0.