Ques 1:In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in R and that the cardinality of E1 is greater than the cardinality of E2.
GATE 2018 Q no 11
Which one of the following is true about R?
(A) Every entity in E1 is associated with exactly one entity in E2
(B) Some entity in E1 is associated with more than one entity in E2
(C) Every entity in E2 is associated with exactly one entity in E1
(D) Every entity in E2 is associated with at most one entity in E1
Ans: (A) Every entity in E1 is associated with exactly one entity in E2
Solution: Since it is a many to one relationship from E1 to E2, therefore:
- No entity in E1 can be related to more than one entity in E2. ( hence B is incorrect)
- An entity in E2 can be related to more than one entity in E1.(hence C and D are incorrect).
Ques 2: Consider the following two tables and four queries in SQL.
GATE 2018 Q no 12
Book (isbn, bname), Stock(isbn, copies)
Query 1: SELECT B.isbn, S.copies FROM Book B INNER JOIN Stock S ON B.isbn=S.isbn;
Query 2: SELECT B.isbn, S.copies FROM Book B LEFT OUTER JOIN Stock S ON B.isbn=S.isbn;
Query 3: SELECT B.isbn, S,copies FROM Book B RIGHT OUTER JOIN Stock S ON B.isbn=S.isbn
Query 4: SELECT B.isbn, S.copies FROM Book B FULL OUTER JOIN Stock S ON B.isbn=S.isbn
Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?
(A) Query 1
(B) Query 2
(C) Query 3
(D) Query 4
Ans: (D) Query 4
Solution: Since the full-outer join is nothing but a combination of inner-join and the remaining tuples of both the tables that couldn’t satisfy the common attributes’ equality condition, and merging them with “null” values.
Ques 3:Consider the relations r(A,B) and s(B,C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query
GATE 2018 Q no 41
Q:r⋈(σB<5(s))
Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.
Which of the following is NOT equivalent to Q?
(A) σB<5(r ⋈ s)
(B) σB<5(r LOJ s)
(C) r LOJ (σB<5(s))
(D) σB<5(r) LOJ s
Ans: (C) r LOJ (σB<5(s))
Solution: Option A, B, D will restrict all record with B<5 but option C will include record with B>=5 also, so false.
Ques 4: Consider the following four relational schemas. For each schema , all non-trivial functional dependencies are listed, The underlined attributes are the respective primary keys.
GATE 2018 Q no 42
Schema I: Registration(rollno, courses)
Field ‘courses’ is a set-valued attribute containing the set of courses a student has registered for.
Non-trivial functional dependency
rollno → courses
Schema II: Registration (rollno, coursid, email)
Non-trivial functional dependencies:
rollno, courseid → email
email → rollno
Schema III: Registration (rollno, courseid, marks, grade)
Non-trivial functional dependencies:
rollno, courseid, → marks, grade
marks →grade
Schema IV: Registration (rollno, courseid, credit)
Non-trivial functional dependencies:
rollno, courseid → credit
courseid → credit
Which one of the relational schemas above is in 3NF but not in BCNF?
(A) Schema I
(B) Schema II
(C) Schema III
(D) Schema IV
Ans: (B) Schema II
Solution: rollno, courseid → email
rollno, courseid is a super key, so it comes under 3NF as well as BCNF
email → rollno
Here, email is not a key though but rollno comes under prime-attribute. Hence it’s in 3NF but not BCNF.