SHRIRAMSWAROOPMEMORIALUNIVERSITY
PROBLEM SET–I
Session : 2024-25 (Odd Semester) Semester :III
Course :BCA, [Link]. IT Subject Code :UCS3001
Branch : Subject Name :Database Management System
Group : All Name of Faculty Dr. Archana Sachindeo Maurya, Mr. Sanjay
Member Kumar, Mrs. Sweta Singh, Mr. Sarvjeet
Pal,Mr. Shamim Ahmad
Unit :I Topic Covered : Introduction to Database
1. What are the application programs? Explain database access from application programs?
2. Define a) Entity b) Attribute c) Relationship with examples.
3. State and explain various features of E-R Models.
4. Discuss in detail about super key, candidate key, foreign key, and primary key.
5. Name the main steps in the Database design. What is the goal of each step? In which steps is the
ER model mainly used?
6. Explain about database languages and interfaces.
7. Write the differences between database systems and file system.
8. Explain about Logical database design?
9. Explain about Database Architecture?
10. Explain generalization, aggregation and Specialization with suitable diagram.
Text/Reference Books
T1.“Korth Silbertz, Sudarshan, “Database Cocepts”, McGrawHill
T2.“Elmasri, Navathe, “Fundamentals of Database Systems”, Pearson Education
(Signature of the Faculty Member with date) (Signature of the Dean with date)
SHRIRAMSWAROOPMEMORIALUNIVERSITY
PROBLEM SET–II
Session : 2024-25 (Odd Semester) Semester :III
Course :BCA, [Link]. IT Subject Code :UCS3001
Branch : Subject Name :Database Management System
Group : All Name of Faculty Dr. Archana Sachindeo Maurya, Mr. Sanjay
Member Kumar, Mrs. Sweta Singh, Mr. Sarvjeet
Pal,[Link] Ahmad
Unit :II Topic Covered : Relational Data Model and Languages
1. List the aggregate functions supported by SQL?
2. Discuss the basic form of SQL query?
3. Define CROSS PRODUCT operation in Relational algebra?
4. Define JOIN operation in Relational algebra?
5. Explain about Aggregate operators in SQL with examples?
6. Discuss correlated nested queries?
7. Write and explain a query to find the names of sailors who have reserved a red boat?
8. Explain Set operations of Relational Algebra with examples?
9. Define trigger and explain its three parts? Compare row level and statement level triggers.
10. Explain about Selection, Projection, Rename, Division and Cartesian product operations in
relational algebra.
11. Discuss about Domain Relational Calculus and Tuple Relational Calculus?
12. Write the SQL queries for following dataset.
id first_name last_name department salary
1 Paul Garrix Corporate 3,547.25
2 Astrid Fox Private Individuals 2,845.56
3 Matthias Johnson Private Individuals 3,009.41
4 Lucy Patterson Private Individuals 3,547.25
5 Tom Page Corporate 5,974.41
6 Claudia Conte Corporate 4,714.12
7 Walter Deer Private Individuals 3,547.25
8 Stephanie Marx Corporate 2,894.51
9 Luca Pavarotti Private Individuals 4,123.45
10 Victoria Pollock Corporate 4,789.53
The columns and data in the above table are:
• id – The unique ID of the employee and the table’s primary key.
• first_name – The employee’s first name.
• last_name – The employee’s last name.
• department – The employee’s department.
• salary – The employee’s monthly salary, in USD.
a. Write SQL query to display the first name last name and salary of those employees whose salary is
greater than 3800.
b. Write SQL query to display all details of that employee whose first name is Luca.
c. Write SQL query to display list of all employees who are working in the corporate department.
d. Write SQL query to display the data of all employees in descending order of their salary.
e. Write SQL query to display first name, last name and salary of all employees whose salary is greater
than 5000 and less than 3000. (use logical operator OR)
Text/Reference Books
T1.“Korth Silbertz, Sudarshan, “Database Cocepts”, McGrawHill
T2.“Elmasri, Navathe, “Fundamentals of Database Systems”, Pearson Education
(Signature of the Faculty Member with date) (Signature of the Dean with date)
SHRIRAMSWAROOPMEMORIALUNIVERSITY
PROBLEM SET–III
Session : 2024-25 (Odd Semester) Semester : III
Course :BCA, [Link]. IT Subject Code : UCS3001
Branch : Subject Name : Database Management System
Group : All Name of Faculty : Dr. Archana Sachindeo Maurya, Mr. Sanjay
Member Kumar, Mrs. Sweta Singh, Mr. Sarvjeet
Pal,[Link] Ahmad
Unit :III Topic Covered : Database Design and Normalization
1. What is normalization? What are the conditions are required for a relation to be in 2NF, 3NF and
BCNF explain with examples.
2. What is meant by functional dependencies? Discuss about Third Normal From?
3. Explain BCNF. What are the steps to be followed to convert a relation in 3NF to BCNF?
4. Define Armstrong axioms for FD’s?
5. Explain BCNF. What are the steps to be followed to convert a relation in 3NF to BCNF?
6. Demonstrate transitive dependency? Give an example?
7. Explain about Loss less-join dependency?
8. What three data anomalies are likely to be the result of data redundancy? How can such
anomalies be eliminated?
Text/Reference Books
T1.“Korth Silbertz, Sudarshan, “Database Cocepts”, McGrawHill
T2.“Elmasri, Navathe, “Fundamentals of Database Systems”, Pearson Education
(Signature of the Faculty Member with date) (Signature of the Dean with date)
SHRIRAMSWAROOPMEMORIALUNIVERSITY
PROBLEM SET–IV
Session : 2024-25 (Odd Semester) Semester :III
Course :BCA, [Link]. IT Subject Code :UCS3001
Branch : Subject Name :Database Management System
Group : All Name of Faculty Dr. Archana Sachindeo Maurya, Mr. Sanjay
Member Kumar, Mrs. Sweta Singh, Mr. Sarvjeet
Pal,[Link] Ahmad
Unit :IV Topic Covered : Transaction Processing Concept
1. Define Two Phase Commit Protocol?
2. Explain about multiple granularities?
3. Define a Transaction? List the properties of transaction?
4. Explain in detail about the two-phase locking protocol?
5. Explain about Remote Backup Systems?
6. Explain in detail about Lock-Based Protocols?
7. Explain about Buffer Management?
8. Explain in detail about Validation-Based Protocols?
9. Explain in detail about Serializability, conflict Serializability and view Serializability?
10. Explain in detail about Timestamp-Based Protocols?
11. What is transaction? Explain the ACID Properties of transactions?
Text/Reference Books
T1.“Korth Silbertz, Sudarshan, “Database Cocepts”, McGrawHill
T2.“Elmasri, Navathe, “Fundamentals of Database Systems”, Pearson Education
(Signature of the Faculty Member with date) (Signature of the Dean with date)
SHRIRAMSWAROOPMEMORIALUNIVERSITY
PROBLEM SET–V
Session : 2024-25 (Odd Semester) Semester :III
Course :BCA, [Link]. IT Subject Code :UCS3001
Branch : Subject Name :Database Management System
Group : All Name of Faculty Dr. Archana Sachindeo Maurya, Mr. Sanjay
Member Kumar, Mrs. Sweta Singh, Mr. Sarvjeet
Pal,[Link] Ahmad
Unit :V Topic Covered : Distributed Database
1. Define Distributed Database. Why distributed databases are essential?
2. What are the components of DDBMS? Explain in detail with the help of a diagram.
3. What do you mean by DBMS standardization?
4. What are the three kinds of distributed dbms architecture? Explain briefly about architectural
issues in distributed object DBMS?
5. What is meant by Partitioning or fragmentation? Explain different types of partitioning in
distributed DBMS.
6. What do you understand by concurrency control? Explain various techniques of concurrency
control in DDBMS.
7. What is the difference between homogeneous and heterogeneous distributed database system?
8. Case Study: By taking any real time situation, explain how a participating node performs its
recovery when it fails during the processing of a transaction?
Text/Reference Books
T1.“Korth Silbertz, Sudarshan, “Database Cocepts”, McGrawHill
T2.“Elmasri, Navathe, “Fundamentals of Database Systems”, Pearson Education
(Signature of the Faculty Member with date) (Signature of the Dean with date)