0 ratings 0% found this document useful (0 votes) 101 views 15 pages PYQS
The document is an examination paper for a Database Management System course, containing various questions related to database concepts, SQL queries, and theoretical aspects of database management. It includes sections on ER diagrams, normalization, functional dependencies, transaction management, and recovery mechanisms. Students are instructed to attempt multiple questions from each section, with a focus on practical and theoretical knowledge in database systems.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content,
claim it here .
Available Formats
Download as PDF or read online on Scribd
Go to previous items Go to next items
erat no of pase:
Course Codi
Course Title; Database Management System
Note: Attempt all questions.
‘mentioned in the answer.
16022 CFM
Roll No,
IV Sem. — B.Tech
END-SEMESTER EXAMINATION, May, 2022
EECSCO9/ ICCSCO9
Max Marks: 40
‘Time:03:00 Hours
Missing datw/nformation (ifany), may be suitably assumed &
‘Marks | CO
Question
[QNNe.
QL
Ta
1b
each level. = _ 4
iat are various kinds of Users of database management systems.
Wh Kinds of Us iB 4 i
Te
ras an ER diagiam for university aeademio ofc. The office altars
Gata about each couse, instructors teaching te cous, students enralled in
cures th tine, dead room number ofthe clas eld, Fr each stent ~
Source par agree also rovonded Take stable asunptons irra x8
Convert the above ER model wo rlaional model
2a
Q2-
‘Attempt any 2 parts of the following _
Differentiate between the Following with sutable examples of each, 4 12
1 DMLand DDL.
H__ Super Key and Primary ke}
2b
‘Consider the following relations P(ABC), Q(ABD) and R(AE): a 4
Pe Q R
ATs [c¢ Als
AL | Bt | Cr al | BI
az | Bz | 2 al | 32
re
aan
AL et
73 [Ez
Ba fea) [a2 | ot a4] 3
as] es [i} [aa none
|. What is the output of following rational algebra query:
T Tpcoblcioresvencal? 9 RD v (@eosnf0 % RD]
TL. What is the output ofthe following SQL query on the above relations?
SELECT * FROM Q FULL OUTER JOIN R
Define a Trigger with the help of sutable example
alalele
Consider the following schema for institute library: 4 7
‘Smdent (RoliNo, Name, Father Name, Branch),
Book (ISBN, Title, Author, Publisher),
Issue (RollNo, ISBN, Date-of -Issue).
Write the following queries in SQL end relational algebra:
TF he autor ofthe books who wrote books fr the publishers Tata Me
11, Find the students who have issued all the books.
"Attempt any 2 parts of the following
What is Funeinal Dependency? Erolsin te procedure of caleuaing he] 2
nical Cove iven Functional Dependency Set with suitable example,
7 z Die) with set of funcional Dependencies P= { eS
Tossles
relation in 3NE.
eserving and
3e | Explain Dependency pre
ee
[Qa [Attempt any 2 parts of th
‘What is Conflict Seri ible “Schedule? ‘Cheel
sty your answer
| Define Strict and Rig
| schedule generated b; re cascade-less a
these protocols a
arts of the followin
| it. What is curability of transaction? How
recovery mechanism.
Consider following senedules
Schedule $1
RA)
A= A-40
WA)
R®)
B=B+10
Ww(B)
‘Lotthe transaction Fails after WB).
i [Write the log records for the above transactions,
| il, What actions will be performed during log based recovery mechanism.
5b
———— ati id Deo Se
[——]—What are the prime attributes oF i a |
|_| relation in 3NF.___— sitions with 8 |b nel
ee fuel
e followin ah the given Sonedule $1 jg Conflict
| Serializable or not? Jus
| Si: RICX), R2CX),R2 ry), W2(Y), RU (Y),W: nsactions
Discuss various methods of Deadlock a in Te ether the | 4 ies
rotocol
os Taro Phase locking Prot0e?s OT ecable? _
eC
Qs [Auempt any 2p
yt ib
mt nrmean by SWDIESOFARE. ig ensured by 98 esol
Justify your answer.
Differentiate between with suitable examples
L ‘Dense index and Sparse index.
i, Primary Index and secondary index
Sc im
Total no. of pages......, Rollno-
B.Tech. 3“ & 5" END SEMESTER EXAMS, NOV-DEC 2022
courte odes : COCSC05/CACSC0S/CMCSC05S/CDCSCOS
‘ourse name : Database Management system
Time : 3 Hrs.
Note: Attempt all the five qu
ons. Missing data/ information if any, may be suitably assumed.
‘Qno._] Attempt any two paris of question | [co
Qn , What is data abstraction? Explain in brief with the help of suitable | 4 11
diagram,
b. Draw and explain the detailed structure of data base management
system,
©. Give the merits s of database management system Aiea zi
Q2. | Attempt any two parts of question 2
| a. Explain weak entity set with example and how it is different
strong entity set
b. Convert the given ER model to relational ae state how you | 4
cas as, GP (6)
\_ =n ' n 1 x
pares a
. Consider the given specialization hierarchy in the ER model with mutual
exclusive and total participation constraint. Convert the following into
from | 4
@3.__| Attempt any two parts of question 3
a, Refer to the relational schema given below
Worker(empid, empname, empmagr, dept, birthdate, hiredate,
salary)
Project(projne, projname, projmgr, budget, stardate, duration)
Assign(projno, empid, hoursassign, rating)
Write the relational algebra expression and SQL statements for the | 2+2
following queries i
(i.)Find the projects and names of all thesworks assigned to projects with
startdate Ifeb, 2022.
(ii) Find the employee names and manager names of the workers who are
not assigned to any project
b. Consider a relation R(A,B,C,D.E) 143
‘You are given the following dependencies
ADB, BC>E, ED>A
(a i Qa
Sal
Find the candidate key for R.
Is R in 3NF? Show the steps of checking. If not then |
| decompose it in 3NF.
| c. Suppose the relation schema R(A,B.C,D.E) is eo
| “Felations 2s RI(A.B.C) and RADE). The set of functional
dependencies are given as
A->BC, CD->E, BD, E>A, B>E
(i) Show the steps in deriving the number of super keys for
telation K og ition? If
| Gi) Is the above decomposition a loss-less join decomposition?
yes then why?
‘Aitempt any two parts of question 4
a. Consider the following partial schedule ST
S1: RICK), R2(¥), R3(¥), W2C¥), WICO, W300, R200, W200
i) Check if the schedule $1 conflict seraliabe? If St is conflict
serializable, write the equivalent serial schedule for Si.
ii) Does the schedule SI have cascaded roll backs? Justify your
answer.
; Differentiate
c. What do you mean by two phase locking protocol? :
berween strict two phase locking and rigorous two phase locki a
protocol. Are the schedules generated by these protoco!
serializable and cascadeless
242
Qs.
‘Aifempt any two parts of question 5 " z
a Consider the following schedule having three transactions as given
below
Tl 2 13
RA)
RB)
R(C)
WA)
way
we)
we)
wo)
‘Assume that each write operation is increasing the value by 50. Initially A
= 500 and B = 300.
Create log file for this given schedule using defferred and immediate log
based recovery system.
b. What is meant by shadow paging? Explain its advantages and
disadvantages
¢. What are checkpoints? Explain the database recovery mechanism using
checkpoints. Total no. of Pages: 02 Roll no.
= OISEMB. Tech
MID-SEMESTER EXAMINATION, October= 2021
Course Code~ ITITCOS/INITCOS TECEBIME)
Course Title- Database Management System
Time-3 hours Max. Marks- 40
Note: - Attempt all questions. Missing datay information if any, maybe suitably -
assumed & mentioned in the answer. i
QNo | Question Marks [ CO
Ql _| Attempt any two part of the following
Ja__ | Whatis DBMS? Discuss its di = cea
1b | Docs an ecommerce application follow a thioe- ey achictecture? Justify 4 {a
jour answer,
1¢._| How data independence and data abstadiion achieved in DBMS. plane Te
02 | Attempt any two parts of the following
2a | Consider the relational database, Amz
Worker (Worker_1d, First_namo, salary, joining date, department}Title
(Worker_ref id, Worker fitle, affected from)
‘Write an SQL queties forthe following questions:
1. To fetch the list of employees with the same salary.
Spe Mint the name of employees having the highest salary in each
department, S
Fe J0 Print names of the Workers who are Managers-and have joined
after 20.6.2022,
Toa ptrteh the names of the workers with salaries >= 50000 and <=
100000.
%__ | Consider therelational database as mentioned in ‘Q2a, Give an expression | 4 [> |
2. List the details of the Workers who are Managers. .
}2e | Explain the concept of referential integrity constraint, Write an SQL 4/2
Ca ye Enly referential integrity constraint on the relations given ia
Qla (Worker, Title). g
{| (Qa Atepear 'y two parts of the following :
4a | Find the highest normal form inR (A, B, GD, E) tinder following 4/3
functional dependencies, : :
ABC->D
: CD—> AB g 2
Decompose the relation in the required normal form: :
4b | Consider a relation R (A,B,CD,B), IF (A) and GO) are the candidate keys |_4_—+4 3
Of, find the number of superkeys and Hist them, In tle instance of the relation RB, CD) Showa below, wich of the
following functional dependencies (FD's) hold? Briefly justify your
answer. >
L ABC I B>D UL DE>A
Ey
Design an ER diagram for a university registrar's cllice. The oHice 7
maintains data about each class, including the instractor, the number of
student-class pair, a grade is recorded, Jclude the following concepts in
the ERdiagram: = :
1. Anentity having derived and multivalued attribute
2. A 1:1 mapping
3. A weak entity with its discriminator
ma
4a
Attempt any two parts of the following
Discuss the advantages ofa. serial schedule. Check if the following
schedile is conflict serializable. Show the serializable ‘scheule,if possible.,
Til 12 r #
4b
‘What is conservative 2-phase locking protoooL Discuss the advantages of | 4
ftric ard Hgorous locking protocol over conservative Protocol. Does
with an example,”
exanzples,
Explain the dirty read problem, How we ead aveiooue the problem of
dity read. Explain with exampte,
Explain the difference between deadlock and starvation with the help of
locking always eliminates the problem of deadiocky Tustrate your answer 3
4
Sa
05 [Attempt any two paris of the following
Discuss the advantages and limitations of MIS. : 4
Shaal
Discuss the challenges of MIS. : ae
Sc
Discuss the management hierarchy with fictions ofeach jevel.
sehexeaepypeemenye __END SEMESTER EXAMINATION, May 2017
COE 312: Information Systems and Data Management
270
. Time: 3:00 Hrs
Note! Quetieninort lee
ote: Question No, | is compulsory. Attempt any 4 from the remaining questions
All questions carry equal marks.
Assume missing data suitably, if any.
Ql
2) Differentiate between the following with suitable examples. (10)
(i) Primary and Secondary Index
Gi) Degree and cardinality
ii) Inner join and outer join
(iv) DDL and DML
(¥) Serial schedule and Serializable schedule
b) Answer the following in one word. (4)
i) The property of transaction which allows either the complete execution of the transaction
or execution of no part of itis called
ii) Ifa proper subset of one candidate key determines the proper subset of another candidate
key in a relation, the condition is a violation of Normal form.
iii) ig a feature of the entity relationstip model that allows a relationship set to
participate in another relationship set.
jy) Fora relation to be in 2NF, there should not be any _dependency.
Q2
‘a) What is Functional Dependency? Discuss the concept of minimal cover of a set of functional
dependencies. (3) |
by What isa view? Give an example of @ view and define it using SQL. How views are related to
data independence? (4) .
¢) Discuss all the possible states of a transaction with a diagram. (3) ;
@) What are fixed length and variable length records? Briefly discuss their organization.(4)
Q3.
the following relational schema
fe, office, age)
publisher)
a) Consider t!
employee(empno, nam:
books(isbn, title, authors
19, isbn, date,
ign the following queries in relational algebra and SQL. ,
( i sad the names of employees who have borrowed a book published by Pearson
(i) Find the names of employees who have borrowed all books published by Pearson.
(iii) Find the names of emplovees ‘who have borrowed more than five different books
published by Pearson
For each publisher, fis
books of that publisher.
w) ‘nd the names of employees who have borrowed more than five
; (8)
vega trigger to deny insertion when an employee issues more than 5 books 2)
2) Discuss iB rage scheme that provide redundancy by combining disk striping with parity
©) DiserAtso, suggest the factors that affects the choice of level in the scheme. (4)
Q4. b)
Q7.
b)
we)
vase Hocking protocol? DISCUSS Ihe bas
i
m se of serializability 1S assured by ws Pio find the equivalents schedule gy”
Meee arg mn detail Also SUBEES! © vcking protocol? (°)
ale amplementing ° P
precedence graph
iit serial sche’
concurrent sched
Constder the following
If yes, give all the equivale!
Js the corresponding, schedule conflict Serializable,
p, Is the
,dules. (2)
What are checkpoints? How the implementation of checkpoints is different in case of seria
and concurrent schedules? (3) P
What is significance of weak entity sets? With the help of suitable ean represent a strong
and weak entity along with their mapping cardinalities in an ER model (4).
Define deadlocks. Discuss the ways to handle a deadlock. (5)
Write Armstrong's axioms used to find closure of set of FDs. (3)
Which properties of a transaction are ensured by recovery mechanisms in DBMS? What are
the different versions for log based recovery? Compare in terms of ease of implementation
and overhead cost. (6)
Relation R has attributes ABCDEFGH. Fields of R contain only atomic values and having
following set of functional dependencies. {AB-> C, ACB, AD>E, B->D, BCA, E>G}.
(i) Write all the candidate keys. (3)
(ii) Check if the relation is in 2NF. Also find th
given relation. (5) 1 INF and BCNF decomposition of the
(iii) Is the decomposition achieved "i .
your answer.(4) «ved in part (ii) lossless and dependency preserving? Justify
What is the role of normalization in achieving database desi ign goals? (2)
als?
respect to serializability, deadlocks, starvan
What do you mean by multiple granulmin ere eOverAbility, case; 8
for all possible locks in this proweal. Tock based pi Seana
rotocols? Write compatibility mat"
>
’
d
d
) Total No. of pa
! Roll no
Sixth Semester
BLICOL)
(END SEMESTER EXAMINATION, May 2018
COE 312: Information Systems and Data Management
N . .
‘ote: Attempt any five questions
Assume missing data, suitably, if'any
QUG)(@)_— What do you mean by Specialization/Generalization hierarchies in BR model,
Represent it with a suitable example.
(b) Discuss the constraints which can be imposed on Specialization/Generalization
hierarchies and how these constraints affect the design of relations in relational
___ Schema. (444)
(ii) Define Triggers and its syntax. Discuss any two applications of triggers in brief.
(6)
Q2 i) Discuss validation based concurrency control protocol in detail oa)
(ii) Differentiate between serial and serializable schedules. How will you check whether a
schedule is conflict serializable or not? (4)
(iii) Is the time stamp based protocol serializable and free from deadlocks? Justify your
answer, )
Q3(i) Define Cascadeless and Recoverable schedules. Explain any ILock based concurrency
control protocol in, detail which generates these kinds of schedules and how? _(8)
(ii) Define various types of OUTER JOIN with suitable examples Ca)
Q4. Consider followingltelational schema:
Student(rolino, name, dob, branch)
Books(ISBN, Title, publisher, edition)
Issue(rollno, Book 1D, issuedate)
Return(rollno, Book_ID, return
Book Details(ISBN. Bookid)
Line)
(i) Write SQL queries to create lables for “Student” and “Issue” relations (4)
(ii) Write SQL and relational‘alyebraic expressions for the following queries
a) Find the roll number of students who have issued maximum number of books
b) Find the title of the book which has miiyimum number of copies.
¢) Find the roll number of students who total fine of more than Rs, $00/- 6
iii) Define view. Create a materialized view to store number of students in cach branch.
; a) i j at are various Lypes of lay
Q5 (i), Name different types of failure? Define log and what are
“
sy
ecover from failure vith cheek poy
(ii) Discuss the recovery mechanism, in detail, to Tee
mechanism,
sadvantages of normalyzation
Q6 (1) Define Normalisation. Discuss advantages and disadvantags
i he ¢ present in di
(i) Explain various kinds of anomalies which may be present
(4)
of suitable example.
jata base, with thy help
; (6
Gii) What is Functional Dependency? Define Armsiong rules of inference and trivial
functional dependencies
Q7_ Write notes on any two of the following : oe
i) Dependency preserving and lossless join decompositions
) Integrity constraints s
iii) Hashing and Techniques to resolve collision
iv) Database Abstraction and data independence Total no. of Pages: 03 Roll no. .
1 SEM B. Tech
END-SEMESTER EXAMINATION,
Nov-Dec 2023
Course Code- ITITCOS/INITCOS
Course Title- Database Management System
Time- 3 hours : Max, Marks- 40
Note: - Attempt all questions, Missing data/ information if any, maybe suitably
assumed & mentioned in the answer. s
QNo
~ Question
Marks
co
QL
Attempt any two parts of the following _
ja
List four applications you have used that most likely employed a database
system to store persistent data. Also Give examples of systems in which it
may make sense to use traditional file processing instead of a database
approach.
4
Ib
List five responsibilities of a database-management system. For each
responsibility, explain the problems that would arise if the responsibility
were not discharged,
Explain the key differences between the three main types of data models:
Hierarchical, Network, and Relational, Provide examples of scenarios
where each model is most suitable and discuss their advantages and
limitations.
Q2
Attempt any fwo parts of the following
2a
Write SQL queries to create the following database:
Suppliers(sid:_ integer, sname:varchar(10),address: varchar(10),)
Parts(pid: integer, pname: varchar(10), color: varchar(10))
Catalog(sid: integer, pid: integer, cost: real)
For the database, write the following sql queries:
1. The parts can only be of colours: green, red, blue and black. Write a sql
query to add this constraint in parts relation,
2, For every supplier that only supplies black parts, display the name of the
supplier and the total number of parts that she supplies.
3. Drop the attribute, ‘cast’ from the catalog relation.
4, Display the cost of all the items supplied by the supplier whose id is 50.
re
2b
Consider the relational database as mentioned in Q2a, Give an expression
in the relational algebra to express each of the following queries:
1.Find the name of suppliers who supply some red parts.
2.Find the sids of suppliers who supply some red or green parts.
3.Find the sids of suppliers who supply some red part or are at address 221
packer Ave.
4,Find the sids of suppliers who supply every red part. What are integrity constraints? How are they useful. Explain any 4
integrity constraints with the help of suitable examples and syntax.
@
‘Atienpt ay two pars of the folowing
30
Consider R(A.B,C,D) = F{A> B,B> CCSD}
This relation is decomposed into Rl (A,B) , R2 (A,D) and R3 (CD)
Find whether decomposition is lossless or lossy. _
3b
Fad the bighest normal form in R (4, B.C, D, E) under following
functional dependencies. ;
ABSC.COD,DXEADC. |
Decompose the relation in the 3rd iormal form. Find whether tie
decompostion is lossless and dependency preserving
Explain the following concepts in ER diagram: participation constraints
and detived atributes. :
Conver the folowing ER diagram into the relational mouet with
‘minimum number of relations.
@Q =
5
Be
‘Attempt any two parts of the following
aig]
‘Consider the following two transactions:
Ti readtal) %
read(®); ‘
ifA=Othen B= B+1;
write(®),
Te read(By;
ready;
ifB=0then A:=A+1;
write).
Let the consistency requirement be A= OV B= 0, with A = B= Othe
initial valves.
4.) Show that every serial execution involving these two transactions
serves the consistency of the database.
.) Show @ concurrent execution of T} and T2 that produces a
nonserializable schedule.
Isthere 2 concurrent execution of TI and'T2 thet produces a
serializable schedule?
B
‘Does conservative 2-phase locking protocol eliminate the problem oF
eadtock,
‘Wich of the helow tanssctions follow 2PL protocol? Give reason to
sippont youranswer,
Explain which statements i
fhe transactions ere in which phase. Give
.20n to Stipport your as Total no. of Pages: 02 Roll no.
Degree: B. Tech Semester: 3rd - Course work
END-SEMESTER EXAMINATION, Nov-Dec 2023
Course Title: Database Management System
Course Code: COCSCOS/CACSCO5/CDCSCO5/CMCSCOS
Time: 03 Hours Max. Marks: 40
Note: - Attempt all the five questions. Missing data/ information {if any), maybe suitably assumed &
mentioned inthe answer.
Q. No. Question Marks | CO
Q1_| Attempt any 2 parts of the following _
a | Highlight the key difference between the following with the help of suitable 4 COL
sae
Poot and OML i. Candidate toy and super key
li, Procedutal and non-procedurallanguage tv. Strong and weak enties
Ib | Briefly describe the following terms with the help of an example. 4 co.
i, ACID properties ii, Data abstraction Ili, Role of OBA iv. GRANT and REVOKE
Te | Anaiyze the following ER-diagram. Find the number of tables/relations and provide | 4 | COL
their schema, State al the rules to transform the R-dlagram to schema.
Bi fn Be
é a
2 5
mR Ca) B ‘Re
Ga Attempt any? pars ofthe folowing —
2a | considering relational databases below give SOL expresions forthe queries. 7 cor
employee (ID, person name, street, city), works (ID, company name, salary)
company comuaay.name, ct), manages U2, manage i)
i. Find the /D of each employee who does not work for “First Bank Corpo- ration”.
li. Find the /D of each employee who eaitis Inote than every employee of “Sinall Bank
Corporation’
2b | Cansiering ratios below, Give relational agebra erosions forthe quel, a pee
branch(branch name, branch city, assets)
customer 10, customer name, customer_street, customer.city)
loan (loan number, branch name, amount), borrower (10, loan number)
‘account (account number, branch name, balance)
depositor (10, account number)
i. Find each loan number with a loan amount greater than 11000.
fi, Find the ID of each depositor who has an account with a balance greater than
18000 at the “Uptown” branch, Je [Describe the concept of cardinality n the context of Entty-Relationship modeling. | 4 | COZ
Provide examples of different cardinality ratios in relationships and explain their
significance.
‘G3 Attempt any 2 parts of the following
3a | Consider relation R(A 8 C.D E F GH) with the FDs = [CH->G, ADBC, B>CFH, EA, | 4 | COB
F->€G), Find the highest normal form of Rif Ris not in @CNF. convert i into BCN
‘Bb | Consider the relation R (A 8 CO E F) with following Fs. a | co3
{(&->8, CD>AB, BC->D, AE->F, CE->0A} decomposed into the following relations
(AB), R2(AC D), R3(BCD), RAIAEF), ASICD E)
Using the 3 properties of lossless decomposition check whether its lossless or lossy
decomposition. If it is lossless, prove it by applying the natural join of all the
decomposed relations.
3e | Explain the canonical cover or minimal cover for functional dependencies. a | co3
Consider F = {BCD->H, A>BC, CD->E, E->C, D>AEH, ABH>BD, DH->C). Find the
canonical cover for
Ga4_| Attempt any 2 parts of the following
‘43 | (l) What is conflict serializabilty? 7 | Coa
{il Consider the following schedules. Here, wc denote read, write and commit. 3
Su: 40K); 22); F(Z}; 30); BLY, WHR), el, SKY} 3; 2); WALZ); we); €2
52: A(X); 22) r1(2h 30K; BCU); WAX), BLY) MY; wz); wl); cs 2 cB
$3: r4(X);r2(2); r3(X); r4(2); AV); BOY); WMH; cH; WAL); WKY; W20M) ee
Using the precedence graph method, analyze the conflict seralizability for schedules
1, $2, and $3.
“4B _| Consider the following two transactions a | oa
Ta: readtX) T2iread(Y)
ready) read{2)
readi2) read(X)
1X20 then Ve¥ed iF¥=0 then X:=Ke1
writetY) write(X)
{Add lock and unlock instructions to the transactions T1-and 72, so that they observe a
two phase locking protocol. Can the execution of these transactions result in
deadlock?
Ge [Consider the schedules 51 end S2 in da, Determine whether each schedule [4 | CO4
cascadeléss and recoverable.
{Q5_| Attempt any 2 parts of the following
5a | Explain deadlock with the help ofa suitable schedule example, Analyse strict2-Phase | 4 | CO4
locking (2-PL) protocol with respect to deadlock handling.
Sb _| Consider the following log sequence of two transactions on a bank account (8) with | 2+2 | COS |
an initial balance 12,000 that transfers 2000 to a mortgage (™™) payment and then |
applies 5% interest j
1. Th star> |
2. i
3, |
4. <7, commit>
5. <12,start>
6. <12, 8, 19000, 10500>
7. <12, commit>
Now, what will happen to these transactions if the database system crashes as:
I Just before log record 7th is written. Ui, Just before log record Ath.
Justify your answer in each case.
Se__[ustrate shadow paging with the help of a suitable diagram. 4 _| 005
