Important RGPV Question

CS-502 Database Management Systems

V Sem, CS

UNIT 1-DBMS Concepts and architecture

Q.1) Describe the structure of a DBMS. If your operating system is upgraded to support some new functions on OS files (e.g., the ability to force some sequence of bytes to disk), which layer(s) of the DBMS would you have to rewrite to take advantage of these new functions?

(RGPV Nov 2023)

Q.2) Consider the following ER Diagram with the relations El(a, b) and E2(c, d). R1 and R2 denote relationships between El and E2. Illustrate the table schema of the entities E1, E2 while converting ER Diagram to Relational tables.

(RGPV Nov 2023)

                                       

Q.3) Draw an ER diagram that captures the following information.
“A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.”

(RGPV Nov 2023)

Q.4) Discuss the role of following in representing information about the real world in a database?
i) DDL and DML
ii) The buffer manager
iii) The data model

(RGPV Nov 2023)

Q.5) A woman artist designs and sells jewellery through mail orders or at craft shows. Currently she has a mailing list in a word processing file that she uses for sending catalogs. She also keeps both supplier and customer names and addresses in word processing files and prepares bills using a spreadsheet and keeps her income and expenses on another spreadsheet. What would be the advantages for her to design and use a DBMS for all her records.

(RGPV Nov 2022)

Q.6) Discuss the role of database administrator.

(RGPV Dec 2020)

Q.7) Explain specialization and generalization feature of ER diagram with example.

(RGPV Dec 2020)

UNIT 2-Relational Data models

Q.1) Consider the following Employee Relation.

                                   

Write SQL Queries for the following:
i) Fetch the Dept_ID whose average salary is higher than 4000.
ii) Fetch the Dept_ID whose average salary of male employees is higher than 2000.
iii) Fetch the Name of all employees whose Manager_ID is ‘l’.
iv) Fetch the Name of all employees of Dept_ID as ‘103’ and their Manager_ID is ‘l’.
v) Fetch the name, salary, and Dept_ID of employee getting second highest salary.
vi) Fetch the E_ID and Name of employees who are getting salary higher than the salary of employee ‘F’.
vii) Fetch the D_ID of employees who are getting salary higher than the salary of employee ‘E’.

(RGPV Nov 2023)

Q.2) Consider the following Employee relation with E_ID as the primary key.

                                            

Discuss the anomalies that may arise during Insert, Delete and Updating a record in the above relation.

(RGPV Nov 2023)

Q.3) Consider the relation Emp_Manager with Employee_ID as the primary key and Manager_ID as the foreign key referencing Employee_ID with on-delete cascade.

                                            

Determine all the tuples that will be deleted with the following SQL Command

Delete From Emp_Manager

Where Employee_ID = ‘2

(RGPV Nov 2022)

Q.4) Consider the following Employee relation with E_ID as the primary key

                                 

Discuss the issues that may arise with the following SQL operations in the above relation

i) Insert into Employee (E-ID Ename dno). Values (NULL, ‘T’, 104)

ii) Delete From Employee Where E_ID = ‘3’

iii) Update Table Employee Set dno 109 Where dno = 104

(RGPV Nov 2022)

Q.5) Consider the following Employee Relation

                                                                  

Write SQL Queries for the following:

i) Fetch the E_ID and Name of employees who are getting salary higher than the salary of employee ‘P’
ii) Fetch the D_ID of employees who are getting salary higher than the salary of employee ‘N’
iii) Fetch the E_ID and Name of employees who are getting salary higher than anyone in the Dept_ID ‘102’
iv) Fetch the E_ID and Name of employees who are getting salary higher than all employees in the Dept_ID ‘103’

v) Fetch the E_ID and Name of employees who are getting salary same as the employees of Dept_ID ‘103’

vi) Fetch the name, salary, and Dept_ID of employee getting second highest salary

vii) Fetch the Dept_ID in which the average salary is greater than 3000

(RGPV Nov 2022)

Q.6) Explain the following commands with syntax. 7
i) Select
ii) Update
iii) Delete

(RGPV Dec 2020)

Q.7)  Differentiate DML, DDL and DCL in detail.

(RGPV Dec 2020)

UNIT 3- Data Base Design

Q.1) Discuss the major steps in query optimization. What do you understand by sigma cascade operation?

(RGPV Nov 2023)

Q.2) Discuss various cost components for query execution.

(RGPV Nov 2023)

Q.3) Identify the candidate keys and highest normal form of the relation R (ABCDEF). The functional dependencies of the relation R

A→BC
C→E
E→F
F→AB

(RGPV Nov 2023)

Q.4) Consider a relation R(WXYZ) with the following set of functional dependencies.
X→W
WZ→XY
Y→WXZ

(RGPV Nov 2023)

Q.5) Consider three relations R(a, b), S(b, c), and T(c, d) wi following tuple values
R(a, b) = {(0, 1), (4, 5), (8, 9)}
S(b, c) = {(1, 2), (5, 2), (5, 6), (5, 10), (13, 10)}
T(c, d) = {(2, 3), (6, 7), (10, 11), (10, 3)}
Find the result of the following, where denotes natural join

(RGPV Nov 2022)

Q.6) Discuss, the three major steps in query optimization.

(RGPV Nov 2022)

Q.7) Identify the candidate key and highest normal form of the relation R (E, F, G, H). Following functional dependencies exists for the relation R

E→F
F→G
H→G

(RGPV Nov 2022)

Q.8) Consider a relation R(ABCD) with the following set of functional dependencies 
A→B
B→C
C→D
D→A
The relation R has been decomposed into three sub relations as R1(AB), R2(BC), and R3(CD). Determine the given decomposition satisfies the dependency preservation property or not?

(RGPV Nov 2022)

Q.9) Consider a relation R with five attribute A, B, C, D, E having
following dependencies : A →B BC → E  , and ED → A
i) List all keys for R
ii) In which normal form table is

(RGPV Dec 2020)

Q.10) What is lossless decomposition in database? How it is useful in database?

(RGPV Dec 2020)

Q.11) Explain the concept of query optimization.

(RGPV Dec 2020)

UNIT 4- Transaction Processing Concepts & Control Techniques

Q.1) Consider the following actions taken by transaction T1 on database objects X and Y R(X), W(X), R(Y), W(Y)
Give an example of another transaction T2 that, if run concurrently to transaction T without some form of concurrency control, could interfere with T1.

(RGPV Nov 2023)

Q.2) Explain the concept of recoverability in database management system. Show any recoverable schedule consisting of two transactions.

(RGPV Nov 2023)

Q.3) Consider the following transactions with two data items A and B. Show the precedence graph for the given concurrent schedule and find equivalent serial schedule.  (RGPV Nov 2023)                                                                                             

Q.4) Consider a schedule S using two phase locking mechanism for concurrency control. Convert the given schedule to strict and rigorous two-phase locking protocol. 

Lock Shared(A)

Read(A)

Lock Exclusive(B)

Read(B)

B=B+A

Write (A)

Write (B)

Unlock (A)

Unlock (B)

Commit

(RGPV Nov 2023)

Q.5)  Consider the transaction T as following 
begin
1. Read (A)
2. Read (B)
3. t= A-10% of A
4. B=B+t
5. Write (A)
6. Write (B)
commit
Discuss the transaction states active, partially commit, commit, failure and terminated possible with reference to the above example.

(RGPV Nov 2022)

Q.6) Discuss the issues that may arise with the interleaved execution of the transactions T1 and T2.

(RGPV Nov 2022)

                                              

Q.7) Draw the precedence graph for the given concurrent schedule and determine if it is conflict serializable or not?  (RGPV Nov 2022)

                                                                         

Q.8) Illustrate the concept of two-phase locking protocol and its variants with an example.

(RGPV Nov 2022)

Q.9) How is precedence graph made to check serializability?

(RGPV Dec 2020)

Q.10) What do you mean by deadlock handling? How can we resolve deadlock?

(RGPV Dec 2020)

Q.11) What is two phase locking (2PL)? Describe with the help
of an example.

(RGPV Dec 2020)

Q.12) What is Log? How is it maintained?

(RGPV Dec 2020)

UNIT 5-Study of RDBMS through Oracle/PL SQL
QL/MySQL: 

Q.1) List out the operations that can be performed on files.

(RGPV Dec 2020)

Q.2) Discuss the various exception handling mechanisms in database platforms. 

(RGPV Nov 2022)

EXTRA QUESTIONS-

Q.1) Write short notes on (any two):
a) Oracle exception handling mechanism
b) Functions of DBA
c) SQL Aggregate Functions
d) Distributed databases

(RGPV Nov 2023)

Q.2) Design a possible schema for a doctor’s office. The doctors want immediate access to patient medical information. The records clerk needs to be sure all insurance companies are billed as per patient details. 

(RGPV Nov 2022)

Q.3) Write short note on (any two)

a) Oracle Application Express

b) Specialization vs Generalization 

c) Temporal vs Multimedia Databases

d)Triggers

(RGPV Nov 2022)

Q.4) Explain Fragmentation and its types in detail.

(RGPV Dec 2020)

Q.5) Differentiate physical level and logical level of data abstraction.

(RGPV Dec 2020)

Q.6) Write short note on any three of the following : 
i) Concurrency control
ii) Multivalued dependency
iii) Hashing technique
iv) Join dependency
v) Various keys in DBMS

(RGPV Dec 2020)

— Best of Luck for Exam —