DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
DATA SCIENCE
UNIT TEST-II
Class: SE Semester: IV Subject: DBMS
Date: 17-04-2023 Time: 2.00pm-3.30pm Max marks: 40
Note the following instructions
1. Attempt all questions.
2. Draw neat diagrams wherever necessary.
3. Write everything in Black ink (no pencil) only.
4. Assume data, if missing, with justification.
Q.N Questions Marks CO Blooms POs
Taxonomy
Level
Q.1. Attempt any two.
i) Explain ACID properties of transaction. [5] CO6 L2 -
ii) Explain log based recovery. [5] CO6 L2 -
iii) Explain deadlock? How it is detected? [5] CO6 L2 -
iv) Explain time stamp based protocol. [5] CO6 L2 -
Q.2. Attempt any two.
a) Make the use of SQL constructs, consider [10] CO4 L3 PO3,PO5,
following database schema: PO12
Book(bid,title,author,cost)
Store(store_no, city,state,inventory_val)
Stock(store_no, bid, quantity)
Write SQL queries for following queries:
i. Modify the cost of DBMS books by 10%
ii. Find the author of the books which are
available in Mumbai store
iii. Find the title of the most expensive book.
iv. Find the total quantity of books in each store
v. Add a new record in Book table(assume
values as per requirements)
1/2
b) Make the use of SQL constructs, write SQL [10] CO4 L3 PO3,PO5,
queries for the given database PO12
Employee(eid,e_name,street,city)
Works(eid,cid,salary)
Company(cid,c_name,city)
Manager(eid,m_name)
i. Find the names of all employees having ‘S’
as first letter in their names.
ii. Display annual salary of all the employees.
iii. Find the name, street and city of all
employees who work for “Accenture” and
earn more than 30000
iv. Give total number of employees.
v. Delete a record of manager whose name is
Jones.
c) Make the use of SQL constructs, write SQL [10] CO4 L3 PO3,PO5,
queries for the following database. PO12
Department(did,dname,mgrid,loc_id)
Location(loc_id,street,pincode,city,state,cid)
Employee(eid,fname,lname,email,phno,jobid,
mgrid,date,salary,commission,did)
i. Display department no, last name,
department name for all employees.
ii. Create unique listing of all jobs that are in
department, also include location of
department.
iii. Display last name, department name,
location id and city of all employees who
earn commission.
iv. Display the difference between highest and
lowest salary and label output as
“Difference”
v. List department id from department that do
not contain jobid 20 using set operations.
Q.3 Attempt any one.
a) Apply the concept of normalization, identify [10] CO5 L3 PO1,PO12
following relation is in 2NF or not.
R(ABCDEF)
FD={C->F,E->A,EC->D,A->B}.
b) Apply the concept of normalization, identify [10] CO5 L3 PO1,PO12
following relation is in 3NF or not.
R(ABCD)
FD={AB->C,C->D}
2/2
