In this article you will get DBMS GATE Questions 2018 with answers.
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.
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.
Answer : a)
2. Consider the following two tables and four queries in SQL.
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
Answer : d)
3. Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys.
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
Answer : b)
4. 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
Q: r⋈(σB<5(s))
Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.
Which one 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
Answer : c)
