(Page 1 of 2)

USN CS54

B. E. Degree (Autonomous) Fifth Semester End Examination (SEE), Dec 2018/Jan 2019

DATABASE MANAGEMENT SYSTEM

(Model Question Paper - 1)

Time: 3 Hours ] [ Maximum Marks: 100

Instructions to students:

1. Answer FIVE FULL questions.

2. Answer ANY ONE from Question No.1 and 2
3. Answer ANY ONE from Question No.3 and 4
4. Answer ANY ONE from Question No.5 and 6
5. Answer ANY ONE from Question No.7 and 8
6. Answer ANY ONE from Question No.9 and 10

a) Define the following terms. 10 Marks

1. DBMS
2. DBA
1.
3. Metadata
4. Entity
5. Attribute
Draw the ER Diagram for student database considering at least minimum 5 entities 10 Marks
b)
OR
With a neat block diagram explain the DBMS architecture. Also discuss the 3 different 10 Marks
2 a)
levels of abstraction.
b) Explain with a neat sketch, the different phases of database design 10 Marks
3. a) With respect to the Relational data model, explain the concept of primary keys and 10 Marks
Foreign keys. Illustrate your answer with examples.
b) Discuss the following relational algebra operations. Illustrate each of them with an 10 Marks
example.
1. SELECT
2. PROJECT
3. DIFFERENCE
4. UNION
OR
4 a) Discuss the entity integrity and referential integrity constraints. Why each one is 06 Marks
considered important.
b) Explain domain constraints, key constraints and constraints on NULL values. 07 Marks

c) With the help of examples, explain the different set operations in relational algebra. 07 Marks

5 a) Consider the following relation schema 10 Marks

Project(P_No, P_Name, P_Incharge)
Employee(E_No, E_Name)
Assigned_to(P_No, E_No)
Write the SQL Quires for the following:
(i) List details of employees who are working on all the projects.
(ii) List E_No of employees who are not working on project number 2K.
(iii) List the names of employees who are working in the same project as employee
named ‘Tom’.
(iv) List the names of employees who are not working in any project.

Dr. Ambedkar Institute of Technology, Bengaluru – 560056

(An Autonomous Institution Affiliated to Visvesvaraya Technological University, Belgaum)
 (Page 2 of 2)
b) What do you mean by JOIN in SQL? Explain various JOIN operations in SQL with an 10 Marks
example.
OR
6 a) Explain more complex SQL queries with examples. 10 Marks

b) Briefly explain Views in SQL along with the syntax. Discuss the problems that may arise 10 Marks
when one attempts to update a view. How are views practically implemented?

7. a) What is the need for normalization? Explain 1NF, 2NF, 3NF with examples. 10 Marks

b) Consider the universal relation R={A,B,C,D,E,F,G,H,I,J} and the set of functional 10 Marks
dependencies F={{A,B}→{C}, {A}→{D,E}, {B}→{F}, {F}→{G,H}, {D}→{I.J}}.
What is the key for R? Decompose R into 2 NF and then 3 NF relations.
OR
8 a) Consider the following relations: 12 Marks
CAR_SALE(Car_no, Date_sold, Salesman_no, Commission_%, Discount)
(Assume a car can be sold by multiple sales man and hence primary key is {car_no,
Salesman_no})
Additional dependencies are:date_sold->Discount_amt and
Salesman_no->Commission_%.
i. Is this relation in 1NF, 2NF, 3NF? Why or why not?
ii How would you normalize this completely?

b) Define Boyce-Codd normal form. How does it differ from 3 NF? Why is it considered a 08 Marks
stronger form of 3 NF?
9. a) Explain a mechanism of concurrency control that uses time stamping with the help of an 10 Marks
example
b) Discuss the problems of deadlock and starvation and different approaches to deal with 10 Marks
these problems.
OR

10. a) Explain Multiple-granularity locking with example and also explain under what 10 Marks
circumstances is it used.
b) Prove that Strict two Phase locking protocol guarantees Strict Schedule with example 10 Marks

******

Dr. Ambedkar Institute of Technology, Bengaluru – 560056

(An Autonomous Institution Affiliated to Visvesvaraya Technological University, Belgaum)
 (Page 1 of 2)

USN CS54

B. E. Degree (Autonomous) Fifth Semester End Examination (SEE), Dec 2018/Jan 2019

DATABASE MANAGEMENT SYSTEM

(Model Question Paper - 2)

Time: 3 Hours ] [ Maximum Marks: 100

Instructions to students:

1. Answer FIVE FULL questions.

2. Answer ANY ONE from Question No.1 and 2
3. Answer ANY ONE from Question No.3 and 4
4. Answer ANY ONE from Question No.5 and 6
5. Answer ANY ONE from Question No.7 and 8
6. Answer ANY ONE from Question No.9 and 10

a) Bring out the important advantages and disadvantages of DBMS over file system. 06 Marks
1.
Define entity, entity set, attribute with respect to ER model. List different types of 08 Marks
b)
attributes along with their symbols.
c) Explain three schema architecture and languages used at each level. 06 Marks
OR
2 a) With a neat block diagram explain the DBMS architecture. Also discuss the 3 different 10 Marks
levels of abstraction.
b) Draw the ER Diagram for Bank database considering at least minimum 5 entities 10 Marks

3. a) Consider the following schema for a company database. 10 Marks

Employee (Name, SSN, Address, Sex, Salary, Dno)
Department (Dname, Dnumber, MGR SSN, MGRSTART DATE)
Dept_Location (Dnumber, Dlocations)
Project (Pname, Pnumber, Plocation, Dnum)
Works_on (ESSN, Pno, Hours)
Dependent (ESSN, Dependent_name, Sex, Ddate, Relationship)
Write the queries in relational algebra to
1. Retrieve the name and address of all employees who work for the research
department
2. Find the name of employees who work on all projects controlled by department
number 5.
3. List all projects on which employee Smith is working.
4. Retrieve the names of employees who have no dependents.
b) Define the terms with an example for each 10 Marks
1. Domain
2. Relation schema
3. Relation state
4. Relation database schema.
OR
4 a) With respect to the Relational data model, explain the concept of primary keys and 10 Marks
Foreign keys. Illustrate your answer with examples.
b) Discuss the following relational algebra operations. Illustrate each of them with an 10 Marks
example.
1. SELECT
2. PROJECT
3. DIFFERENCE
4. UNION

Dr. Ambedkar Institute of Technology, Bengaluru – 560056

(An Autonomous Institution Affiliated to Visvesvaraya Technological University, Belgaum)
 (Page 2 of 2)
5 a) Explain Insert, Delete & Update Operation with example. 08 Marks

b) What do you mean by JOIN? Explain various JOIN operations in SQL with an 08 Marks
example.
c) Explain Set theoretic operations used in SQL. 04 Marks

OR
6 a) List the data types that are allowed for SQL attributes.examples. 04 Marks

b) Consider the following relation schema 08 Marks

Project(P_No, P_Name, P_Incharge)
Employee(E_No, E_Name)
Assigned_to(P_No, E_No)
Write the SQL Quires for the following:
(i) List details of employees who are working on all the projects.
(ii) List E_No of employees who are not working on project number 2K.
(iii) List the names of employees who are working in the same project as employee
named ‘Tom’.
(iv) List the names of employees who are not working in any project.
c) How are Triggers and Assertions are defined in SQL explain with example. 08 Marks

7. a) State the informal guidelines for relational schema design. Illustrate how violation of 10 Marks
these guidelines may be harmful.
b) Explain Multi-valued Dependencies, Fourth Normal Form, Join Dependencies and Fifth 10 Marks
Normal Form with examples.
OR
8 a) What is the need for normalization? Explain 1NF, 2NF, 3NF with examples. 10 Marks

b) Consider the universal relation R={A,B,C,D,E,F,G,H,I,J} and the set of functional 10 Marks
dependencies F={{A,B}→{C}, {A}→{D,E}, {B}→{F}, {F}→{G,H}, {D}→{I.J}}.
What is the key for R? Decompose R into 2 NF and then 3 NF relations.
9. a) Discuss the ACID properties. 04 Marks

b) Which of the following schedules is (conflict) serializable? For each serializable 12 Marks
schedule, determine the equivalent serial schedules.
1. r1(x); r3(x); w1(x); r2(x); w3(x);
2. r1(x); r3(x); w3(x); w1(x); r2(x);
3. r3(x); r2(x); w3(x); r1(x); w1(x);
4. r3(x); r2(x); r1(x); w3(x); w1(x);
c) What is a schedule (history)? Define the concept of strict schedules. 04 Marks

OR

10. a) Explain the two phase locking protocol with examples and along with its advantages. 08 Marks

b) Explain time stamp ordering algorithm with example. 08 Marks

c) Explain Serializability with example. How can serializability be ensured? Do you need 04 Marks
to restrict concurrent execution of transaction to ensure serializability? Justify your
answer.

******

Dr. Ambedkar Institute of Technology, Bengaluru – 560056

(An Autonomous Institution Affiliated to Visvesvaraya Technological University, Belgaum)
 (
Page1of1)

USN CS54
B.E.Degr
ee(
Aut
onomous)Fi
ft
hSemest
erEndExami
nat
ion(
SEE)
,Dec2017/
Jan2018

DATABASEMANAGEMENTSYSTEM
(
Model
Quest
ionPaper-3)
Ti
me:
3Hour
s] [Maxi
mum Mar
ks:
100

I
nst
ruct
ionst
ostudent
s:
1.AnswerFIVEFULLquesti
ons.
2.Questi
onNo.1,2and5ar eCOMPULSORY
3.AnswerANYONEf rom Quest
ionNo.3and4
4.AnswerANYONEf rom Quest
ionNo.6and7

Bri
ngoutt hei mpor tantadv ant agesanddi sadvantagesofDBMS 06Mar
ks
1.
a) overf i
lesy st em.
Defineent i
ty ,ent it
yset ,att
ri
but ewi threspectt oERmodel .List 08Mar
ks
b)
dif
ferentt y pesofat t
ributesal ongwi ththeirsymbol s.
Explaint hreeschemaar chit
ect ureandl anguagesusedateach 06Mar
ks
c)
l
evel.
2. a) Consi dert hef oll
owi ngschemaf oracompanydat abase. 10Mar
ks
Empl oy ee ( Name, SSN, Addr ess, Sex,Salary,
Dno)
Depar tment( Dname, Dnumber ,MGRSSN, MGRSTARTDATE)
Dept _Locat ion( Dnumber ,Dlocat i
ons)
Project( Pname, Pnumber ,
Pl ocat i
on,Dnum)
Wor ks_ on( ESSN, Pno, Hours)
Dependent( ESSN, Dependent _name, Sex, Ddate,Relati
onship)
Writet hequer i
esi nr elat
ional algebrato
1.Ret ri
ev et henameandaddr essofal lemployeeswhowor kfor
ther esear chdepar tment
2.Fi ndt henameofempl oy eeswhowor konal lprojects
cont rolledbydepar t
mentnumber5.
3.Li stal lpr ojectsonwhi chempl oyeeSmi thiswor king.
4.Ret ri
ev et henamesofempl oyeeswhohav enodependent s.
b) Definet het er mswi thanexampl eforeach 10Mar
ks
1.Domai n
2.Rel ati
onschema
3.Rel ati
onst ate
4. Re lati
ondat abaseschema.

3 a) Expl
ainI
nser
t,Del
ete&Updat
eOper
ati
onwi
thex
ampl
e. 08Mar
ks

b) Whatdoy oumeanbyJOIN?Explai
nvari
ousJOI
Noper
ati
onsi
n 08Mar
ks
SQL wi t
hanexample.
c) Expl
ainSettheor
eti
coper
ati
onsusedinSQL. 04Mar
ks

OR
4 a) Li
stt
hedat
aty
pest
hatar
eal
l
owedf
orSQLat
tri
but
es.
exampl
es. 04Mar
ks

Dr.AmbedkarI
nst
it
uteofTechnol
ogy,
Bengal
uru–560056
(
AnAut
onomousI
nst
it
utionAff
il
iat
edtoVi
svesvar
ayaTechnol
ogi
calUni
ver
sit
y,Bel
gaum)
 (
Page2of2)
b) Wr
it
etheSQLst
atementf
orQuest
ionno:
2(A)
. 08Mar
ks

c) HowareTri
gger
sandAsser
ti
onsar
edef
inedi
nSQLexpl
ainwi
th 08Mar
ks
exampl
e.

5. a) Stat
et heinformal gui
del
inesforrel
ati
onalschemadesi
gn. 10Mar
ks
Il
l
ustratehowv i
olati
onoftheseguidel
inesmaybeharmful.
b) Expl
ainMul t
i-v
aluedDependencies,FourthNormal
Form,Join 10Mar
ks
DependenciesandFi f
thNormal For
m withexampl
es.

6. a) Di
scusst
heACI
Dpr
oper
ti
es. 04Mar
ks

b) Whichoft hefoll
owingschedul esis(conf
li
ct)ser
ial
izable?Foreach 12Mar
ks
seri
ali
zableschedule,determinetheequival
entseri
alschedules.
1.r 1(
x);r
3(x);w1(x);r
2(x);w3(x)
;
2.r 1(
x);r
3(x);w3(x);w1(x);r
2(x)
;
3.r 3(
x);r
2(x);w3(x);r
1(x);w1(x)
;
4. r 3(x)
;r2(x)
;r1(x);w3(x)
;w1(x);
c) Whatisaschedul e(history)
?Def i
netheconceptofst r
ict 04Mar
ks
schedules.
OR

7. a) Expl
aint
het
wophasel
ocki
ngpr
otocol
wit
hexampl
esandal
ong 08Mar
ks
wit
hitsadvant
ages.
b) Expl
ainti
mestampor der
ingal
gor
it
hm wi
thexampl
e. 08Mar
ks

c) Expl
ainSeri
ali
zabi
l
itywit
hexampl e. Howcanser iali
zabi
li
tybe 04Mar
ks
ensur
ed?Doy ouneedtor estri
ctconcurr
entexecutionof
tr
ansact
iontoensureseri
alizabi
li
ty?Justi
fyyouranswer .

*
***
**

Dr.AmbedkarI
nst
it
uteofTechnol
ogy,
Bengal
uru–560056
(
AnAut
onomousI
nst
it
utionAff
il
iat
edtoVi
svesvar
ayaTechnol
ogi
calUni
ver
sit
y,Bel
gaum)