Download GTU (Gujarat Technological University Ahmedabad) B.Tech/BE (Bachelor of Technology/ Bachelor of Engineering) 2020 Summer 3rd Sem 3130703 Database Management Systems Previous Question Paper
Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER? III EXAMINATION ? SUMMER 2020
Subject Code: 3130703 Date:29/10/2020
Subject Name: Database Management Systems
Time: 02:30 PM TO 05:00 PM Total Marks: 70
Instructions:
1. Attempt all questions.
2. Make suitable assumptions wherever necessary.
3. Figures to the right indicate full marks.
Marks
Q.1 (a) What are the main functions of a database
03
administrator?
(b) Explain the difference between physical and logical
04
data independence.
(c) Explain DBMS System Architecture.
07
Q.2 (a) Describe the differences in meaning between the
03
terms relation and relation schema.
(b) Write the following queries in relational algebra:
04
(1) Find the names of suppliers who supply
some red part.
(2) Find the IDs of suppliers who supply some
red or green part.
(c) An ER diagram can be viewed as a graph. What do
07
the following mean in terms of the structure of an
enterprise schema?
(1) The graph is disconnected.
(2) The graph is acyclic.
OR
(c) Draw ER diagram for university database consisting
07
four entities Student, Department, Class and
Faculty.
Student has a unique id, the student can enroll for
multiple classes and has a most one major. Faculty
must belong to department and faculty can teach
multiple classes. Each class is taught by only
faculty. Every student will get grade for the class
he/she has enrolled.
Q.3 (a) What is normalization? Explain 2NF.
03
(b) Explain typical query processing strategy of
04
DBMS?
(c) Compute the closure of the following set F of
07
functional dependencies for relation schema R = (A,
B, C, D, E).
A BC
1
CD E
B D
E A
List the candidate keys for R.
OR
Q.3 (a) What is normalization? Explain 3NF.
03
(b) Write short on block nested loop join.
04
(c) Use the definition of functional dependency to argue
07
that each of Armstrong's axioms (reflexivity,
augmentation, and transitivity) is sound.
Q.4 (a) Explain hashing.
03
(b) What is transaction? What are the functions of
04
commit and rollback?
(c) Write a short note on SQL injection.
07
OR
Q.4 (a) Explain B-trees.
03
(b) Explain
conflict
serializability
and
view
04
serializability.
(c) Write a short note on intrusion detection.
07
Q.5 (a) What is trigger? Explain its type with their syntax.
03
(b) Write a PL/SQL block to print the given number is
04
odd or even.
(c) Consider the following relational schemas:
07
EMPLOYEE (EMPLOYEE_NAME, STREET,
CITY)
WORKS (EMPLOYEE_NAME,
COMPANYNAME, SALARY)
COMPANY (COMPANY_NAME, CITY)
Give an expression in SQL for each of queries
below::
(1) Specify the table definitions in SQL.
(2) Find the names of all employees who work
for first Bank Corporation.
(3) Find the names and company names of all
employees sorted in ascending order of
company name and descending order of
employee names of that company.
(4) Change the city of First Bank Corporation to
`New Delhi'.
OR
Q.5 (a) Explain cursor and its types.
03
(b) Write a PL/SQL block to print the sum of even
04
numbers from 1 to 50.
(c) Given the following relations
07
TRAIN (NAME, START, DEST)
TICKET (PNRNO., START, DEST, FARE)
2
PASSENGER (NAME, ADDRESS, PNRNO.)
Write SQL expressions for the following queries:
Note: Assume NAME of Train is a column of
Ticket.
(1) List the names of passengers who are
travelling from the start to the destination
station of the train.
(2) List the names of passengers who have a
return journey ticket.
(3) Insert a new Shatabti train from Delhi to
Bangalore.
(4) Cancel the ticket of Tintin.
3
This post was last modified on 04 March 2021