Rajiv Gandhi Proudyogiki Vishwavidyalaya, Bhopal
New Scheme Based On AICTE Flexible Curricula
CSIT-Computer Science & Information Technology | IV-Semester
Unit-I Basic Concepts:
Introduction to DBMS, File system vs DBMS, Advantages of database systems, Database System architecture, Data models, Schemas and instances, Data independence, Functions of DBA and designer, Entities and attributes, Entity types, Key attributes, Relationships, Defining the E-R diagram of database.
Previous Years questions appears in RGPV exam.
Q.1) Differentiate between File System and DBMS. Discuss the advantages of a database system over a file-based system. (June-2025)
Q.2) Draw and explain an E-R diagram for a University database system. Consider entities like students, courses and faculty. (June-2025)
Q.3) Explain the concept of weak entity sets. How do generalization, specialization and aggregation extend the E-R model. (June-2025)
Q.4) What is data independence? Explain logical and physical data independence with an example. (June-2025)
Q.5) Explain the architecture of DBMS and describe the main components. (Dec-2024)
Q.6) Differentiate between DBMS and traditional file systems. Explain how DBMS offers data independence with examples. (Dec-2024)
Q.7) Draw and explain an ER diagram for a Hospital Management System with entities like Patients, Doctors and Appointments, including relationships and constraints. (Dec-2024)
Q.8) Write short notes on (any two):
ii) Entity-Relationship (ER) model (Dec-2024)
Q.9) What is DBMS and differentiate between DBMS and traditional file Storage System? (June-2024)
Q.10) Explain different types of data models in DBMS? (June-2024)
Q.11) State and Explain various features of E-R models? (June-2024)
Q.12) Write short notes on any two:
ii) Data independence (June-2024)
Q.13) Explain the architecture of Database Management Systems with neat diagram? (Nov-2023)
Q.14) What is Null Attribute? With suitable diagram explain Weak and Strong Entity Set. (Nov-2023)
Q.15) Discuss about File Organization and Access methods. (Nov-2023)
Q.16) Write short notes on any two:
ii) Components of DBMS (Nov-2023)
Q.17) Describe the three levels of schema architecture. Why do we need mappings between different schema levels? (June-2023)
Q.18) Explain how the data is organized in a network DBMS? How it is different from Hierarchical DBMS. (June-2023)
Q.19) What is the impact of weak entity set in database design? Discuss with an example. (June-2023)
Q.20) Develop an Entity Relationship diagram for Indian movies. It should include where it was shot, details of the actors, directors, producers, the movie language and so on. (June-2023)
Q.21) Define degree and cardinality. Based upon the given table write degree and cardinality.
Patients
| PatNo | PatName | Dept | DocID |
|---|---|---|---|
| 1 | Leena | ENT | 100 |
| 2 | Supreeth | Ortho | 200 |
| 3 | Madhu | ENT | 100 |
| 4 | Neha | ENT | 100 |
| 5 | Deepak | Ortho | 200 |
(June-2023)
Q.22) Mention the drawbacks of traditional file-based systems that make a database management system a better option. (June-2022)
Q.23) Define data, database, and database management system. Discuss the advantages of DBMS? (June-2022)
Q.24) What is meant by an entity-relationship (E-R) model? Explain the terms Entity, Entity Type and Entity Set in DBMS. (June-2022)
Q.25) Draw E-R Diagram of Library Management System. (June-2022)
Q.26) Define and explain following terms: Levels of data abstraction, Instances, Schema, Physical data independence, Logical data independence. (June-2022)
Q.27) Differentiate specialization and generalization with help of example. (June-2022)
Q.28) Explain the components of DBMS with a neat diagram. (June-2022)
Unit-II Relational Model:
Structure of relational databases, Domains, Tuples, Attributes, Relations, keys and types of keys, Integrity Constraints, Relational Algebra: Queries using Select operation, project operation, renaming, joins, union, intersection, difference, division, and product etc. SQL –basic SQL queries, functions, constraints, joins and nested queries, Triggers, assertions, views and stored procedures and PL/SQL.
Previous Years questions appears in RGPV exam.
Q.1) Consider the following relational schema:
Student(Roll_no, Name, Dept, Year, Marks)
write relational algebra queries to:
i) Retrieve names of students in the CS department
ii) Find students who scored more than 80 marks (June-2025)
Q.2) How does relational algebra help in database query processing? Explain with an example. (June-2025)
Q.3) Describe different types of relational keys. What is the importance of primary and candidate keys in relational databases? (Dec-2024)
Q.4) Write SQL queries to perform the following tasks on a student database:
i) Retrieve all students who scored above 85 in Mathematics.
ii) Find the names of students who are enrolled in both Math and Science courses. (Dec-2024)
Q.5) Define triggers in SQL. Write a trigger that updates the total order amount in an "Orders" table whenever a new order is added in an "OrderItems" table. (Dec-2024)
Q.6) Write short notes on (any two):
i) Relational integrity constraints and their importance in databases (Dec-2024)
Q.7) What are the statements in SQI. for destroying and altering tables. Jexplain with examples? (June-2024)
Q.8) Discuss about different types of Integrity Constraints? (June-2024)
Q.9) Write Relational Algebra Queries for the following for (Sailor Database), Sailors (Sid, Sname, Rating. Age), Boats (Bid. Bname, Color), Reserves (Sid, Bid, Day)
i) Find the following id's with age over 20 and who have not reserved a red boat.
ii) Find the names of Sailors who reserved boat 103. (June-2024)
Q.10) Write short notes on any two:
iii) Union and Intersection (June-2024)
Q.11) How to write the queries in Relational Algebra? Explain with examples. (Nov-2023)
Q.12) What do you understand by Tuples and Attributes? Explain with examples. (Nov-2023)
Q.13) Discuss about Joins with suitable examples. (Nov-2023)
Q.14) Write short notes on any two:
iii) Triggers (Nov-2023)
Q.15) Consider the following two tables as following. (June-2023)
R1
| A | B | C |
|---|---|---|
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
| 10 | 11 | 12 |
R2
| D | E | C |
|---|---|---|
| 1 | 2 | 3 |
| 7 | 8 | 9 |
| 25 | 26 | 27 |
| 79 | 80 | 81 |
Show the output for the following Relational Algebra Operators:
i) $\Pi_A(R1 * R2)$ where * denotes natural join
ii) $\Pi_B(R1 ⟕ R2)$ where ⟕ denotes left outer join
iii) $\Pi_C(R1 ⟕ R2)$ where ⟕ denotes left outer join
Q.16) Consider the following two tables. (June-2023)
Department
| dno | dname |
|---|---|
| 101 | CSE |
| 102 | DS |
| 103 | IT |
| 104 | CCE |
Employee
| Eno | Ename | dno |
|---|---|---|
| E1 | A | 101 |
| E2 | B | 101 |
| E3 | D | 102 |
| E4 | A | NULL |
| E5 | Y | 104 |
Discuss the foreign key and referential integrity constraints on above tables.
Q.17) Give an example of two relations that would give a meaningful result of carrying out a full outer join. Formulate the full outer join using SQL. (June-2023)
Q.18) What is the difference between a relation and a relation schema? Discuss their relationship. (June-2022)
Q.19) What is a Foreign key? List the properties of a foreign key. Explain with the help of an example. (June-2022)
Q.20) Write a query and explain with examples that JOIN two tables so that all rows from the first table are in the result. (June-2022)
Q.21) What is the difference between a LEFT JOIN and a FULL JOIN? Explain with the help of an example. (June-2022)
Q.22) Explain aggregate function with syntax and give an example. (June-2022)
Q.23) Write the SQL query for the followings:
i) Fetch the EmpId and FullName of all the employees working under Manager with id '121' from Employee table.
ii) Fetch the different projects available from the Employee Salary table.
iii) Fetch the count of employees working in project 'P1' from Employee Salary table.
iv) Find the maximum, minimum, and average salary of the employees from Employee Salary table.
v) Find the employee id whose salary lies in the range of 9000 and 15000 from Employee Salary Table. (June-2022)
Q.24) Describe the concept of Referential Integrity. (June-2022)
Q.25) Consider the following relational schema:
Doctor (DName, Reg_no) Patient (Pname, Disease)
Assigned To (Pname, Dname)
Give expression in both SQL and relational algebra for each of the queries:
i) Get the names of patients who are assigned to more than one doctor.
ii) Get the names of doctors who are treating patients with 'Polio'. (June-2022)
Q.26) Explain in detail about various key constraints used in database system. (June-2022)
Unit-III SQL:
Data definition in SQL, update statements and views in SQL: Data storage and definitions, Data retrieval queries and update statements, Query Processing & Query Optimization: Overview, measures of query cost, selection operation, sorting, join, evaluation of expressions, transformation of relational expressions, estimating statistics of expression results, evaluation plans. Case Study of ORACLE and DB2.
Previous Years questions appears in RGPV exam.
Q.1) Assume the following employee and department table and write the SQL queries-
Emp (EID, Ename, DOJ, Salary, Dname)
Dept(DID, Dname)
i) List the employees having salary between 10,000 and 20,000
ii) Update the salaries of all employees in marketing department and hike it by 10%
iii) Get the gross salaries of all employees
iv) Display the department that has no employee
v) Find the number of employees in the marketing department
vi) Find the name of employee having maximum salary
vii) Find the second maximum salary in the employee table (June-2023)
Q.2) Write short note on (any two):
iv) Views (June-2023)
Unit-IV Relational Database design:
Functional Dependency –definition, trivial and non-trivial FD, closure of FD set, closure of attributes, irreducible set of FD, Normalization –1NF, 2NF, 3NF, Decomposition using FD-dependency preservation, lossless join, BCNF, Multi-valued dependency, 4NF, Join dependency and 5NF
Previous Years questions appears in RGPV exam.
Q.1) Discuss the types of anomalies that arise in an un-normalized database. Explain how normalization helps to eliminate these anomalies. (June-2025)
Q.2) Consider the following relation.
Employee (Emp_ID, Name, Dept, Salary, Project_ID, Project_Name, Manager_ID)
i) Identify functional dependencies
ii) Convert it to 3NF and BCNF. (June-2025)
Q.3) Explain the importance of decomposition in database design. What is lossless and dependency-preserving decomposition? (June-2025)
Q.4) Write short notes on (any two):
d) Multi-valued dependency (June-2025)
Q.5) Define functional dependencies with examples. Explain the importance of functional dependencies in achieving database normalization. (Dec-2024)
Q.6) Normalize the following relation to 2NF and 3NF:
Employee(SSN, EmpName, DeptID, DeptName, DeptLocation, ProjectID, ProjectName) (Dec-2024)
Q.7) Write short notes on (any two):
iii) Lossless Join and Dependency Preserving Decomposition (Dec-2024)
Q.8) What do you understand by a Functional Dependencies. Explain? (June-2024)
Q.9) State and Explain Lossless join decomposition? (June-2024)
Q.10) Explain in detail about Relation Schemas with suitable examples? (June-2024)
Q.11) Write short notes on any two:
iv) Dependency Preservation (June-2024)
Q.12) Explain normalization using (1NF, 2NF, 4NF) with suitable examples? (Nov-2023)
Q.13) What are the steps to be followed to convert a relation in 3NF to BCNF? (Nov-2023)
Q.14) Compute the closer of the following set of functional dependencies for a relation scheme.
R(A, B, C, D, E), $F=\{A\rightarrow BC, CD\rightarrow E, B\rightarrow D, E\rightarrow A\}$
List out the candidate keys of R. (Nov-2023)
Q.15) Identify the Highest Normal Form of the Relation R (A, B, C, D, E, F, G, H) and Functional Dependency set F= {AB→CD, D→EG, F→H, C→EF, H→A, G→B, A→B}. (June-2023)
Q.16) Consider the following employee table. Suppose that employees assigned to multiple projects and can have multiple jobs. (June-2023)
| Eno | Pno | Skill |
|---|---|---|
| E1 | 101, 102 | Coding, Testing, Maintenance |
| E2 | 110, 111, 115 | Analysis, Design, Testing |
Normalize the table to the possible extent.
Q.17) Write short note on (any two):
ii) Database Anomalies (June-2023)
Q.18) State the Armstrong inference rules. Provide suitable examples to describe each. (June-2022)
Q.19) What do you mean by Normalization? Explain BONE, 3NF and 2NF with a suitable example. (June-2022)
Q.20) Given a set of FDs
A→B, ABCD→E, EF→G
Is ACDF→G implied by the set of given FDs? Justify your answer. (June-2022)
Q.21) What is difference between Lossless join decomposition and lossy less join decomposition? Explain with examples. (June-2022)
Q.22) Given a relation $R(A,B,C,D)$ and Functional Dependency set $FD=\{AB\rightarrow CD, B\rightarrow C\}$, determine whether the given R is in 2NF? If not convert it into 2 NF. (June-2022)
Unit-V Introduction of transaction, transaction processing and recovery, Concurrency control:
Lock management, specialized locking techniques, concurrency control without locking, Protection and Security Introduction to: Distributed databases, Basic concepts of object oriented data base system.
Previous Years questions appears in RGPV exam.
Q.1) Define a transaction in a database. Explain the ACID properties of transactions with examples. (June-2025)
Q.2) Why is concurrency control important in database systems? Discuss the impact of concurrency control on transaction performance. (June-2025)
Q.3) What is deadlock in a database? Explain deadlock prevention and deadlock detection mechanisms. (June-2025)
Q.4) Write short notes on (any two):
a) Two-Phase Locking (2PL) protocol (June-2025)
Q.5) Write short notes on (any two):
c) Serializability in transactions (June-2025)
Q.6) Define deadlock in the context of databases. Describe one technique each for deadlock prevention, detection and resolution. (Dec-2024)
Q.7) Discuss two-phase locking protocol. Explain its role in maintaining serializability in concurrent transactions. (Dec-2024)
Q.8) Differentiate between serial and non-serial schedules. Test if the following schedule is serializable or not:
T1: R(X), W(X), R(Y), W(Y)
T2: R(X), R(Y), W(Y)
T3: R(Y), W(X) (Dec-2024)
Q.9) Write short notes on (any two):
iv) Serializability and its types (Dec-2024)
Q.10) What is a transaction and explain its properties and States? (June-2024)
Q.11) What is Schedule? Explain about Serial and Non Serial Schedule? (June-2024)
Q.12) How the Lock Manager implements lock and Unlock request? Explain. (June-2024)
Q.13) State and explain shadow paging with suitable example. (Nov-2023)
Q.14) Discuss about Timestamp Based Concurring Control. (Nov-2023)
Q.15) What do you understand by Deadlocks, Explain Avoidance and Prevention in deadlock? (Nov-2023)
Q.16) Write short notes on any two:
iv) Recoverability (Nov-2023)
Q.17) Identify the given schedule is conflict serializable or not. (June-2023)
| T1 | T2 | T3 |
|---|---|---|
| R(A) | ||
| W(A) | ||
| W(A) | ||
| W(A) | ||
| R(B) | ||
| R(B) | ||
| W(B) |
Q.18) Consider the following two transactions T1 and T2 as following (June-2023)
T1: Transfer of Rs. 1,000 from Account A to Account B
T2: Transfer of 10% amount from Account A to Account B
The Initial value of Account A is 2000 and Account B is 3000. Create any one concurrent schedule with T1 and T2 and calculate the final values of A and B.
Q.19) Discuss with an example when concurrent execution of two transactions lead to conflict? (June-2023)
Q.20) Illustrate the concept of two-phase locking protocol and its variants with an example. (June-2023)
Q.21) What is Transaction? Explain its four important Properties. (June-2022)
Q.22) Explain in detail about timestamp based concurrency control techniques. (June-2022)
Q.23) Write and explain optimistic concurrency control algorithm. (June-2022)
Q.24) What is Log? How is it maintained? Discuss the salient features of deferred database modification and immediate database modification strategies in brief. (June-2022)
Q.25) What is Recoverable schedule? Why is recoverability of schedules desirable? Are there any circumstances under which it would be desirable to allow non-recoverable schedules? Explain your answer (June-2022)
Q.26) Compare sorting, join and selection operations in query processing. (June-2025)
Q.27) Why is query optimization necessary in large databases? Discuss with an example. Explain the measures of query cost. (June-2025)
Q.28) Write short notes on (any two):
b) Distributed databases: Concepts and Challenges (June-2025)
Q.29) Explain the purpose of indexing in database. How does a primary index differ from a clustering index? (Dec-2024)
Q.30) Describe the different phases of query processing in DBMS. How does query optimization contribute to the efficiency of database systems? (Dec-2024)
Q.31) Given a database of student records, explain how a hash function can be used for quick data retrieval. (Dec-2024)
Q.32) Explain extendable hashing techniques for indexing data records. Consider your Class students data records and roll numbers as index attribute and show the Hash directory? (June-2024)
Q.33) Explain Static Hashing? What are the advantages of static hashing in DBMS? (June-2024)
Q.34) Write short notes on any two:
i) Query Processing (June-2024)
Q.35) Write an algorithm to implement project and join operation of relational Algebra. (Nov-2023)
Q.36) Compare Single and Multilevel indices. (Nov-2023)
Q.37) Write short notes on any two:
i) Complexity Measures (Nov-2023)
Q.38) Write short note on (any two):
i) Oracle Application Express (APEX) (June-2023)
Q.39) Write short note on (any two):
iii) Distributed Databases (June-2023)
Q.40) What are the problems encountered in DDBMS while considering concurrency control and recovery. (June-2022)
Q.41) What is Secondary storage? Explain types of data storage in DBMS and describe storage hierarchy. (June-2022)
Q.42) Distinguish between:
i) Primary and Secondary indexing.
ii) Ordered indexing and hashing. (June-2022)
Q.43) Why query Optimization is needed? What is the difference between query processing and query optimization? (June-2022)
Q.44) What are the phases of Query Processing? How are query processing and query optimization related? (June-2022)
Q.45) Write short note on any two:
b) Expression Evaluation Plan (June-2022)
Q.46) Write short note on any two:
c) Object Oriented Data Base System. (June-2022)
