GLOBAL ACADEMY OF TECHNOLOGY
Department of Artificial Intelligence and Data Science
Affiliated to VTU, Accredited by NAAC with 'A' grade
RR Nagar, Bengaluru – 560 098
21ADS33: DATABASE MANAGEMENT SYSTEM
TUTORIAL CLASSES
1. Draw E-R diagram for Hospital Management System.
2. ER Diagram for Company Database
3. ER Diagram for Book Publishing company
4. ER diagram for Car Rental Company
5. ER diagram for Student progress monitoring system
6. ER diagram for Library Database
7. ER diagram for Order Database
8. ER diagram for Movie Database
9. ER diagram for College Database
10. ER diagram for Music Database
11. Relational calculus in DBMS
12. Basic SQL Queries (DDL, DML)
13. Normalization with respect to Student Database and Company Database
14. Pstress-Database Concurrency and Crash Recovery Testing Tool
15. Basic MongoDB tutorials
GATE Previous year questions
1) 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 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
(A) P and R
(B) P and S
(C) Q and R
(D) Q and S
2) Given the basic ER and relational models, which of the following is
INCORRECT?
(A) An attributes of an entity can have more that one value
(B) An attribute of an entity can be composite
(C) In a row of a relational table, an attribute can have more than one value
(D) In a row of a relational table, an attribute can have exactly one value or a
NULL value
3) Suppose (A, B) and (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?
4) Which of the following is TRUE?
(A) Every relation in 2NF is also in BCNF
(B) A relation R is in 3NF if every non-prime attribute of R is fully functionally
dependent on every key of R
(C) Every relation in BCNF is also in 3NF
(D) No relation can be in both BCNF and 3NF
5)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 );
(A) 3
(B) 9
(C) 5
(D) 6
5) 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;
(A) 127
(B) 255
(C) 129
(D) 257
6)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 = [Link])
(A) 1, 0
(B) 1, 2
(C) 1, 3
(S) 1, 5
7) Which of the following concurrency control protocols ensure both
conflict serializability and freedom from deadlock?
I. 2-phase locking
II. Time-stamp ordering
(A) I only
(B) II only
(C) Both I and II
(D) Neither I nor II
8) Consider the following schedule for transactions T1, T2 and T3:
Which one of the schedules below is the correct serialization of the
above?
(A)T1 →T3 →T2
(B)T2 →T1 →T3
(C)T2 →T3 → T1
(D)T3 →T1 →T2
8) Let R and S be two relations with the following schema
R (P,Q,R1,R2,R3)
S (P,Q,S1,S2)
Where {P, Q} is the key for both schemas. Which of the following
queries are equivalent?
(A) Only I and II
(B) Only I and III
(C) Only I, II and III
(D) Only I, III and IV
9) Consider the following ER diagram.
The minimum number of tables needed to represent M, N, P, R1, R2 is
(A) 2
(B) 3
(C) 4
(D) 5
10) 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?
(A) Both Book and Collection are in BCNF
(B) Both Book and Collection are in 3NF only
(C) Book is in 2NF and Collection is in 3NF
(D) Both Book and Collection are in 2NF only
11) Information about a collection of students is given by the
relation studinfo(studId, name, sex). The relation enroll(studId,
courseId) gives which student has enrolled for (or taken) that course(s).
Assume that every course is taken by at least one male and at least one
female student. What does the following relational algebra expression
represent?
(A) Courses in which all the female students are enrolled.
(B) Courses in which a proper subset of female students are enrolled.
(C) Courses in which only male students are enrolled.
(D) None of the above