Scribd
Upload
20 views

Bcs403 Dbms Assignment-2

This document is an assignment for the Database Management System course, detailing various questions related to SQL commands, functional dependencies, normalization, update anomalies, and transaction properties. It includes tasks on SQL queries, ACID properties, locking protocols, and NOSQL databases. The assignment is structured into modules with specific questions aimed at assessing students' understanding of database concepts.

Uploaded by

singhsujana50
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
20 views

Bcs403 Dbms Assignment-2

This document is an assignment for the Database Management System course, detailing various questions related to SQL commands, functional dependencies, normalization, update anomalies, and transaction properties. It includes tasks on SQL queries, ACID properties, locking protocols, and NOSQL databases. The assignment is structured into modules with specific questions aimed at assessing students' understanding of database concepts.

Uploaded by

singhsujana50
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

ASSINGMENT I, MAY-2025
DATABASE MANAGEMENT SYSTEM
Sub Code: BCS403 SEM: IV (A, B, C & D Sec) Date: 12-05-2025
Marks: 10
NOTE: Answer all the questions.
Q. No. Questions CO BTL
MODULE 3
1 Illustrate insert, delete, update, alter & drop commands in SQL. CO3 L4

2 Consider two sets of functional dependency. F={A->C, AC->D, E->AD, CO4 L3


E->H} E= {A->CD, E->AH}. Are they Equivalent?
3 Explain the types of update anomalies in SQL with an example. CO3 L2
4 Explain the Cursor & its properties in embedded SQL with an example. CO3 L2

5 What is a Normalization? Explain the 1NF, 2NF ,3NF, BCNF,4NF, 5NF with CO4 L2
examples.
6 Which normal form is based on transitive dependencies and full CO4 L2
functional dependency? Explain the same with examples?
7 Consider the universal relation: R= {A,B,C,D,E,F,G,H,I,J} and the set of CO4 L3
functional dependencies: F= { AB-> C, A->DE, B->F, F->GH, D->IJ}.
Determine whether each decomposition has the loss less join property
with respect to F. D1={R1, R2, R3} ; R1={A, B, C, D, E} ;
R2={B,F,G,H}

8 Discuss EXISTS and UNIQUE functions in sql with suitable examples CO3 L4

MODULE - 4
1 Demonstrate working of Assertion & Triggers in database? Explain with an CO4 L2
example
2 Explain stored procedure language in SQL with an example. CO3 L2

3 Consider the following schema: CO3 L3


Sailors(Sid, Sname, rating , age)
Boats(bid,bname, color)
Reserves(Sid, bid, day)
Write the queries in SQL:
i)Find the names of sailors who have reserved at least one boat.
ii)Find sailors whose rating is better than some sailor called “Jennifer’
(Use Nested Query)
iii)Find the average age of sailor for each rating level that at least two
sailors
iv)Find the name and age of the oldest sailor
4 Describe the six clauses in the syntax of an SQL retrieval query. Show CO3 L2
what type of constructs can be specified in each of six clauses. Which of
the six clauses are required and which are optional
5 a) Explain the ACID properties of transactions. Why are these CO5 L3
properties critical for ensuring database consistency and reliability?
b) Define and distinguish between recoverable, cascadeless, and strict
schedules. For each type, provide an example schedule and explain
whether it satisfies the definition.
6 a) Define conflict serializability and view serializability. How do they CO5 L3
differ in terms of schedule equivalence and validation?
b) Describe the steps involved in testing conflict serializability using a
precedence graph. Construct a precedence graph for the following and
Check whether the given schedule S is conflict serializable or not-
S : R1(A) , R2(A) , R1(B) , R2(B) , R3(B) , W1(A) , W2(B):
MODULE-5
1 a) Explain the Two-Phase Locking (2PL) protocol. How does it ensure conflict CO6 L2
serializability?
b) Compare basic 2PL, strict 2PL, and rigorous 2PL. What are the trade-offs
between concurrency and recoverability among these variants?
2 a) Describe the principles behind Timestamp Ordering Concurrency CO5 L3
Control. How are read and write operations ordered in this approach?
b) What is the Thomas Write Rule and how does it improve concurrency in
timestamp ordering protocols? Illustrate with an example
3 a) Define Multiple Granularity Locking. What is the purpose of introducing CO6 L3
different lock modes such as IS, IX, S, and X? (5 marks)
b) Consider a database where the hierarchy is: Database → Table → Page →
Record. Explain how granularity of data items affects concurrency and
overhead. Give an example scenario showing lock escalation or de-escalation.
4 What is NOSQL? Explain the CAP theorem. CO6 L2
5 What are document based NOSQL systems? basic operations CRUD in
MongoDB.
6 What is NOSQL Graph database? Explain Neo4j. CO6 L2

You might also like