DATABASE MANAGEMENT SYSTEM

Question Bank

Module 1

1. Define DBMS. Discuss the advantages of DBMS over the traditional file system.
2. Explain the types of end users with examples.
3. What are the advantages of using DBMS? Explain.
4. Describe the characteristics of database.
5. Explain the categories of Data Models.
6. What are the responsibilities of DBA & database designers?
7. Explain different types of attributes in ER model.
8. What is database? Explain the three-schema architecture with neat diagram.
9. Explain the component modules DBMS and their interaction, with the help of a diagram. (08 Marks)
10. Define the following with an example :
i. Weak entity type ii. Participation constraints
iii. Cardinality ratio iv. Recursive relationship. v. Data Dictionary
11. Draw an ER diagram of Banking system taking into account atleast five entities, indicate all keys,
constraints and assumptions that are made.
12. Describe three-schema architecture. Why do we need mappings among schema levels?
13. Explain the difference between logical and physical data independence.
14. Draw an ER diagram for a COMPANY database with employee, department, and project as strong entities
and dependent as weak entity. Specify the constraints, relationships and ratios in the ER diagram
15. Define the following terms with example for each using ER notations: Entity, attribute, composite attribute,
multivalued attribute, and participation role.

Module 2

1. What is meant by Integrity Constraint? Explain the importance of referential integrity constraint. How
referential integrity constraint is implemented in SQL?
2. Explain Entity integrity constraint & referential integrity constraints? Why each of these is important in a
database?
3. Explain the following relational operation JOIN, DIFFERENCE, SELECT, UNION.
4. Discuss the ER to Relational mapping algorithm with example for each step.
5. Explain the different Relational model constraints.
6. Demonstrate the concepts of Generalization & Specialization with examples.
7. Explain the different types of update operations on relational database. How basic operation deals with
constraint violations with suitable examples.
8. Explain Unary relational operations with examples.
9. Illustrate the relational algebra operators with examples for select and project operation.
10. Discuss the characteristics of relation that make them different from ordinary table and files.
11. Consider the following Movie database :
Movie (Title. director. Myear. Rating)
Actors (Actor , Aage)
Acts (Actor, title)
Directors (Director, dage)
Write the following queries in relational algebra on the database given ;
i. Find movies made by "Hanson" after 1997.
ii. Find all actors and directors.
iii. Find “Coen’s” movie with “Mc Dormand".
iv. Find (director, actor) pairs where the director is younger than the actor. (08 Marks)
12. Perform i) Student U Instructor ii) Student∩ Instructor iii) Student – instructor iv) Instructor- Student
on the following tables Student
Instructor
Fname Lname
Fname Lname
Susan Yao
John Smith
Ramesh Shah
Ricardo Browne
Johnny Kohler
Sunsan Mao
Barbara Jones
Francis Johnson
Ammy Ford
Ramesh Shah
Jimmy Wang
Ernest Gilbert

13. Consider the following relational database schema and write the queries in relational algebra expressions:
EMP (Eno, Ename, Salary, Address, Phone, DNo)
DEPT (DNo, Dname, Dloc, MgrEno)
DEPENDENT (Eno,Dep_Name, Drelation, Dage)
i. List all the employees who reside in ‘ Belagavi’
ii. List all the employees earn salary between 30000 and 40000
iii. List all the employees work for the ‘Sales’ department
iv. List all the employees have at least one daughter
v. List the department names along with the names of the managers.

14. Consider the following two tables T1 and T2 show the result of the following operations
15. Discuss the Equijoin & Natural join with suitable example.
16. Explain the relational algebra operation for set theory with examples.
17. Write the SQL querries for the following relational schema ;
Sailors (Sid ,Snarne , Rating, Age)
Boats (Bid, Bname, color) .: ,
Reserve (Sid, Bid , Date)
i. Retrieve the Sailor's name who have reserved red and green boat.
ii. Retrieve the no : of boats which are not reserved.
iii. Retrieve the Sailors name who have reserved boat number 103.
iv. Retrieve the Sailors name who have reserved all boats.
v. Find the colors of boats reserved by Alber
vi. Find all sailor ids of sailors who have a rating at least 8 or reserved boat 103.
vii. Find the names of sailors who have not reserved a boat whose name contains the string “storm”,
order the names in ascending order.
viii. Find the sailor ids of sailor with age over 20 who have not reserved a boat whose name includes the
string “thunder”.

Module-3

1. Discuss the informal design guidelines for relation schema design.

2. Define Normalization. Explain 1NF, 2NF and 3NF with examples.
3. Write the syntax for INSERT, UPDATE, and DELETE statements in SQL and explain with suitable
examples.
4. Discuss insertion, deletion and modification anomalies. Why are they considered bad? Illustrate with an
example.
5. Describe Boyce Code Normal Form (BCNF). Also explain the differences between 3NF and BCNF
6. What is functional dependency? Explain the inference rules for functional dependency with proof.
7. Explain the cursor & its properties in embedded SQL with an example
8. Illustrate insert, delete, update, alter and drop commands in SQL.
9. Illustrate the following with suitable examples:
i. Data types in SQL ii. Substring Pattern Matching SQL
10. Which Normal form is based on the concept of transitive functional dependency? Explain the same with an
example.
11. Explain the types of update anomalies in SQL with an example.
12. What is the need for normalization? Consider the relation:
Emp – proj = {SSn , Pnumber , Ename , Pname, Plocation},
Assume {SSn , Pnumber} as primary key.
The dependencies are
{SSn. Pnumber}  hours
SSn  Ename
Pnumber  {Pname,Plocation}
 Normalize the above relation to 3NF.
13. What is functional dependency? Find the minimal cover using the minimal cover algorithm for the
following functional dependency. F= {ABD, BC, AEB, AD, DEF}
14. Consider two sets of functional dependency. F= {A C, ACD, EAD, EH} and G = {ACD,
EAH}. Are they equivalent?

Module 4

1. Demonstrate working of Triggers and Assertions in database? Explain with an example.

2. What do you understand by correlated Nested Queries in SQL? Explain with suitable example
3. How are views created and dropped? Explain how the views are implemented and updated.
4. Discuss the ACID properties of a database transaction.
5. Explain correlated Nested Queries in SQL with an example
6. Illustrate with an example the requirement for concurrency control in transaction
7. Discuss the types of problems that may encounter with transactions that run concurrently.
8. In SQL, write the usage of GROUP BY and HAVING clauses with suitable examples.
9. Explain with the neat diagram, the state transition diagram of a transaction. Or demonstrate the Database
Transaction with transaction diagram.
10. Demonstrate the System Log in database transaction.
11. Demonstrate transaction states & additional operations.
12. Explain stored procedure language in SQL with an example.

Module 5

1. Why concurrency control is needed? Demonstrate with an example.

2. Demonstrate concurrency control based on Timestamp ordering.
3. Define schedule? Illustrate with an example.
4. Discuss how replication and sharding are done in MongoDB.
5. Explain the two phase locking protocol. How does it guarantee serializability?
6. Describe the wait-die and wound wait protocols for deadlock prevention
7. What is multiple Granularity locking? How is it implemented using intension locks? Explain.
8. Discuss the UNDO and REDO operations and recovery techniques that use each.
9. List and explain the four major categories of NOSQL system.
10. Explain the characteristics of NOSQL systems
11. What are document based NOSQL systems? Explain basic CRUD operations in MongoDB
12. Discuss the following MongoDB CRUD operations with their formats
i. Insert ii. Delete iii. Read
13. What is a NOSQL Graph database? And briefly discuss about Neo4j data model.
14. What is NOSQL? Explain the CAP theorem.
15.