GITAM School of Technology
Hyderabad Campus
Mid 2 Examination
Programme : B.Tech Branch: CSE-CS(SET 1)
Sub Code : CSEN2061
Sub Name : Database Management System
Semester : VI
Year : 2023-24
Date:04/04/24
Maximum Marks: 15
SECTION – A
(Short Answer Questions for 1 mark)
Q.No. Question CO PO Blooms Marks
1 Consider the following bank schema
Loan
Loan no Branch Amount
L01 Main 10000
L02 Sub 5000
L03 Main 15000
L04 Main 2000
Borrower 1 1 L1 1
Cname Loan no
Ritu L01
Mani L02
Deena L04
Write the following queries in TRC and DRC
a) Find the loan number, branch, and total amount of
loans that exceed $10,000.
2 Determine the highest Normal Form of the relation
Restaurant (dish, chef, table, price) having the
following compute set of functional dependencies. 2 3 L3 1
dish ->chef, table
table -> Price
3
Find if a given functional dependency is implied
from a set of Functional Dependencies 3 2 L4 1
For: AB→ CD, AF→ D, DE→ F, C→ G, F→ E,
G→ A
Check: CF→DF
4 Find Prime and Non-Prime Attributes using
Functional Dependencies: 3 2 L4 1
R(ABDLPT) having FDs {B→ PT, A→ D, T→ L}
5 Check whether the given schedule S is conflict 3 2 L4 1
serializable or not-
S : R1(A) , R2(A) , R1(B) , R2(B) , R3(B) , W1(A) ,
W2(B)
SECTION – B
Answer any 2(Two) questions, each question carries 5 Marks each
Q.N Question CO PO BL Ma
o. rks
2 Passenger(Pid,Pname,Pgender,Pcity)
Agency(Aid,Aname,Acity)
Bus(bno,bdate,time,source,destination)
Booking(Pno,Aid,bid,bdate)
Write the following queries in RA
1.give the details of all buses from Hyderabad to Chennai.
2.find the name of passengers who booked at least one bus 2 3 L3 1
3.find the bus no for the passenger with Pid is 2001 for bus to delhi
before 1/4/24
4.find the details of all male passengers associated with KPN
agency
5. List the names of agencies located in 'Hyderabad' and the
corresponding buses they operate
3 Compute the closure of the Following set F of FDs for relations
schema R(A, B,C,D,E)
ABC, CDE, BD, EA 2M
list the candidate keys for R 3 2 L4
Check whether the decomposition of R into D is preserving
dependency: 3M
R(ABCDEG): F = {AB→ C, AC→ B, BC→ A, AD→ E, B→ D,
E→ G}. D = {ABC, ACDE, ADG}
4 Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers.
For each of the following transactions, state the SQL isolation level
that you would use and explain why you chose it.
1. A transaction that determines the total number of items for a 3 2 L4
given supplier 5M
2. A transaction that adds a new part to a supplier’s catalog
3. A transaction that shows, for each part, the supplier that supplies
the part at the lowest price
4. A transaction that increases the price that a supplier charges for a
part
5. Retrieving the cost of a part from the Catalog table.
