GBGS SCHEME
BCS403
USN
Examingtíon, June/July 2025
Fourth Semester B.E/B.Tech. Degree
Database Management $ystems
Max. Marks: 100
Time. 3 hrs.
chÍosing ONE full question from each module.
Note: I. An swer any FWE full questions, Course outcomes.
2. M: Marks , L:Bloom 's level, C:
M
Module -1 4 L2 C01
Q.1 8. Explain the types of attributeswith example.
approach. 8 L2 CO1
b. Define database. Explain thc main characterist ics of the databast
Show the ERdiagram for, an EMPLOYEE database by assuming your own L3 C02
entitics (minimum 4)attributes and relationships, mention cardinality ratios
wherever appropriate,
OR
Describe the three schema architecture.
4 L2 C01
0.2 a.
interaction with the help 8 L2 C01
b. Explain the cómponent models of DBMS and their
of diagram.
8 L3 C02
c. Design ER diagram for a university database by assuming your own entities
(4). Mention primary key , constraints and relationships.
Module -2
6 L2 CO1
Q.3 a. Explain relational model constraints.
b. Explain the characteristics of relations with suitable example for each. 6 L2 CO1
L3 CO1
c. Considering the following schema
Sailors (sid., sname ,rating, age)
Boats (bid, bname ,color)
Reseryes (sid , bid, day)
Write a relational algebra queries for the following:
i) Find the names of sailors, who have reserved red and a green boat.
ii) Find the names of sailorswho have re_erved a red boat.
ji) Find the naies of sailors who have reserved a red or green boat.
iv) Findthe narnes of sailors who hâve reserved all boats.
OR
Q.4 a. Explain the steps to convert the basic ER model to relational Database 6 L2 C01
schema.
b. Explain Unary relational óperations with example. L2 CO1
AM
- AM
Iof 3
Employce database. L3
Consider the relation schema SSn, Bdates , Address, Sex ,
Salary
EMPLOYEE (Fname ,Minit , Lname,
Super SSn, Dno)
DEPARTMENT (Dname,Dnumber,
Mgr SSn, Mgr start date)
Plocation, Dnum)
PROJECT (Pname , PNumber,
WORKS ON (Essn,Pno , Hours) Bdate, Relationship)
DEPENDENT (Essn, Depcndent name , sex,
following :
Write relational algebra queries for the enployees who work for the
i) Retrieve the name and address ofall
'Research' department.
dependents,
ii) List the names of all employçes with 2or more controlled
i) Find the names of employees who work on all the project_
by department number 5.
iv) List the names of emplóyees whohave no dependents.
Module -3
What is the need for normalization? Explain second and third normal fornm 6 L2
Q.5
with examples.
b. Outline constraints in SQL. 6 L2
C Identify the given Relation R(ABCDE) and its instance, check whether 8 L3
FDS given hold or not. Give reasons.
i) AB iü) B’C iii) DE iv) CD ’E
ABCDE
bË C1 d1 e
Raj | b C1 d
b
b3 C3 d2 e
OR
Q.6 a. What is Multiyalued dependency? Explain 4NF and 5NF
example, with suitable 6 L2
b. Outline the informal design guidelines for
relational schema. 6 L2
c. Consider relation R with following
EMPPROJ (SSn,Pnumber , Hours function dependency:
, Ename Pname, Plocation) L3
SSN, Pnumber ’ Hours,
SSNEname
Pnumber ’ Pname , Plocaion.
Is it 2NF? Verify? If no give
reason.
2 of3
BCS403
Module 4
Q.7 Considcr the following sehema for acompany database 10 L3 CO3
Employee (FNanne, LName, SSn , Adderss, Sex , Salary Dno ,
Super SSn)
Department (Dname, Dumbcr, mgr SSn, mgr st date)
Project (Pname, Pnumber, Plocation , Dnum)
WORKS on (EsSn, Pno, Hours)
DEPENDENT(Essn, Dependent name,Sex Bdate, relationship).
Write the SQL queries for the following
i) List the names of managers who have aleast one dependent (use
correlatcd nested).
i) Retrieve the name of cach employce who has a dependent with the
same first name and is the sanc sex as the cmployce.
ii) For cach project retrieve the project number , project nme and the
number ofemployces vho work on that project.
iv) Retricve the SSN ofullêmployees who work on project number 1, 2
or 3. (Use 1N).
V)
Find the sum of the salaries of allemployees ofthe'Research'
department as well as maximum salary, minimom salary, average
salary in this department.
b Why concurrency control is needed? Demonstrate with an example. 10 L2 COS
OR
Q.8 a. Consider the following schedule. The actions are listed in the order they are 10 L3 COS
scheduled and prefixed with the transaction name.
SI :TI:R(X), T2: R(X) TI W(Y), T2 : W(Y.TI:R(Y),T2: R(Y)
$2:T3: W(X), TI :R(QX),T1 : W(Y), T2: R(Z), T2: W(Z), T3: R(Z)
For each schedule answer the following
i) What is the precedehce graph for the schedule?
ii) Is the schedule conflict serializable? If so what are all the conflicts
equivalent Serial schedules?
iii) Is the schedule view serializable? Ifso what are all the view equivalent
serial schedules?
b. Explain triggers with example write a trigger in SQL to call a procedure 10 L3 COS
"Inform Supervisor" whenever an employees salary is greater than the
salary of his or her direct supervisor in the COMPANY database.
Module 5
Q.9 a Describe the two phase locking protoçol for concurrency control provide 10 L2 COS
example to illustrate how it ensures [erializability in transaction schedule.
b. Explain the characteristics of NOSQL system. 10 L2 CO6
OR
Q.10 a. Explain binary locks and shared lock with algorithm. 10 L2 CO5
b. Explain MongoDB data model, CRUD operations and distributed systemn| 10 L2 CO6
characteristics.
AM.
3 of3
AM.
