Rajiv Gandhi Proudyogiki Vishwavidyalaya, Bhopal
New Scheme Based On AICTE Flexible Curricula
CSE-Cyber Security | IV-Semester
UNIT 1
Basic Concepts of Data and DBMS, File organization and access methods; Introduction to DBMS, Difference between DBMS and traditional file storage system. Characteristics of DBMS. Data Models, Schemas and Instances, DBMS architecture, Components of DBMS. Data Independence. Study of Entity Relationship Model, Type of attributes, Entity types, Relationship and Cardinalities, Participation, Roles and constraints.
Previous Years questions appears in RGPV exam.
Q.1) What is DBMS and differentiate between DBMS and traditional file Storage System? (June-2024)
Q.2) Explain different types of data models in DBMS? (June-2024)
Q.3) State and Explain various features of E-R models? (June-2024)
Q.4) Explain the various levels of data abstraction in a database management system. (June-2023)
Q.5) Explain different types of relationships amongst tables in a DBMS. Explain different types of attributes in a DBMS. (June-2023)
Q.6) 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.7) Mention the drawbacks of traditional file-based systems that make a database management system a better option. (June-2022)
Q.8) Define data, database, and database management system. Discuss the advantages of DBMS? (June-2022)
Q.9) What is meant by an entity-relationship (E-R) model? Explain the terms Entity, Entity Type and Entity Set in DBMS. (June-2022)
Q.10) Draw E-R Diagram of Library Management System. (June-2022)
Q.11) Explain the architecture of DBMS and describe the main components. (Dec-2024)
Q.12) Differentiate between DBMS and traditional file systems. Explain how DBMS offers data independence with examples. (Dec-2024)
Q.13) 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.14) Explain the architecture of Database Management Systems with neat diagram? (Nov-2023)
Q.15) What is Null Attribute? With suitable diagram explain Weak and Strong Entity Set. (Nov-2023)
Q.16) Discuss about File Organization and Access methods. (Nov-2023)
Q.17) Write short notes on any two:
ii) Data independence (June-2024)
Q.18) Write short notes on (any two):
ii) Entity-Relationship (ER) model (Dec-2024)
Q.19) Write short notes on any two:
ii) Components of DBMS (Nov-2023)
UNIT 2
Relational Data Model: 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) What are the statements in SQI. for destroying and altering tables. Jexplain with examples? (June-2024)
Q.2) Discuss about different types of Integrity Constraints? (June-2024)
Q.3) 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.4) Describe the different types of keys. Explain different operations in Relational algebra. (June-2023)
Q.5) Consider the student table given below.
| Reg.No. | Branch | Section |
|---|---|---|
| 1 | CSE | A |
| 2 | ECE | B |
| 3 | CIVIL | B |
| 4 | IT | A |
Write commands (using relational algebra operations), and outputs for the followings:
i) To display all the records of student table.
ii) To display all the records of CSE branch in student table.
iii) To display the record of ECE branch section B students.
iv) To display the records of section B CSE and IT branch.
v) To display all the records in student tables whose regno>2. (June-2023)
Q.6) What is the difference between a relation and a relation schema? Discuss their relationship. (June-2022)
Q.7) What is a Foreign key? List the properties of a foreign key. Explain with the help of an example. (June-2022)
Q.8) 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.9) What is the difference between a LEFT JOIN and a FULL JOIN? Explain with the help of an example. (June-2022)
Q.10) Explain aggregate function with syntax and give an example. (June-2022)
Q.11) 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.12) Describe different types of relational keys. What is the importance of primary and candidate keys in relational databases? (Dec-2024)
Q.13) 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.14) 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.15) How to write the queries in Relational Algebra? Explain with examples. (Nov-2023)
Q.16) What do you understand by Tuples and Attributes? Explain with examples. (Nov-2023)
Q.17) Discuss about Joins with suitable examples. (Nov-2023)
Q.18) Write short notes on any two:
iii) Union and Intersection (June-2024)
Q.19) Write short notes on (any two):
i) Relational integrity constraints and their importance in databases (Dec-2024)
Q.20) Write short notes on any two:
iii) Triggers (Nov-2023)
UNIT 3
Normalization Theory and Database methodologies: Relation Schemas, Functional Dependencies- Definition and rules of axioms, Normal forms- 1NF, 2NF, 3NF and BCNF, Dependency preservation, properties, loss less join decomposition.
Previous Years questions appears in RGPV exam.
Q.1) What do you understand by a Functional Dependencies. Explain? (June-2024)
Q.2) State and Explain Lossless join decomposition? (June-2024)
Q.3) Explain in detail about Relation Schemas with suitable examples? (June-2024)
Q.4) What does Normalization mean? Explain different normal forms with examples. (June-2023)
Q.5) Suppose you are given a relation $R=(A,B,C,D,E)$ with the following functional dependencies. {CE→D, D→B, C A}.
i) Find all candidate keys.
ii) Identify the best normal form that R satisfies (INF, 2NF, 3NF, or BCNF).
iii) If the relation is not in BCNF, decompose it until it becomes BCNF. At cach step, identify a new relation, decompose and re-compute the keys and the normal forms they satisfy. (June-2023)
Q.6) Let $R=(A,B,C,D,E,F)$ be a relation scheme with the following dependencies: $C\rightarrow F,$ E→A, EC→D, A B. Find candidate key for R? (June-2023)
Q.7) The following functional dependencies are given:
AB CD, $AF\rightarrow D,$ DEF, CG, FE, G A
Find the closure of CF, BG, AB and AF. (June-2023)
Q.8) What is difference between Lossless join decomposition and lossy less join decomposition? Explain with examples. (June-2022)
Q.9) 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)
Q.10) Define functional dependencies with examples. Explain the importance of functional dependencies in achieving database normalization. (Dec-2024)
Q.11) Normalize the following relation to 2NF and 3NF:
Employee(SSN, EmpName, DeptID, DeptName, DeptLocation, ProjectID, ProjectName) (Dec-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) Write short notes on any two:
iv) Dependency Preservation (June-2024)
Q.16) Write short notes on (any two):
iii) Lossless Join and Dependency Preserving Decomposition (Dec-2024)
UNIT 4
Transaction Processing: States of transaction and desirable properties, Introduction to Concurrency and Recovery, Schedules, Recoverability & Serializability, types of serializability and test for serializability, Concurrency Control: Two phase locking, Timestamp Based concurrency control. Deadlocks: Avoidance, Prevention , detection & resolution, Recovery: Basic concepts, techniques based on deferred update and immediate update, Shadow paging, check points.
Previous Years questions appears in RGPV exam.
Q.1) What is a transaction and explain its properties and States? (June-2024)
Q.2) What is Schedule? Explain about Serial and Non Serial Schedule? (June-2024)
Q.3) How the Lock Manager implements lock and Unlock request? Explain. (June-2024)
Q.4) What is the meaning of ACID characteristics in relational databases? Define the structure of a relational database schema. (June-2023)
Q.5) What do you mean by Serializability? Discuss the conflict and view serializability with suitable example. (June-2023)
Q.6) What do you mean by Multiple granularities? How is it implemented in transaction system? (June-2023)
Q.7) What are the methods used for deadlock prevention in DBMS? What is difference between blocking and deadlock? (June-2023)
Q.8) What is Checkpoint in DBMS? Why is Checkpoint used in database? How Checkpoint works in DBMS? (June-2023)
Q.9) 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.10) 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.11) Define deadlock in the context of databases. Describe one technique each for deadlock prevention, detection and resolution. (Dec-2024)
Q.12) Discuss two-phase locking protocol. Explain its role in maintaining serializability in concurrent transactions. (Dec-2024)
Q.13) 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.14) State and explain shadow paging with suitable example. (Nov-2023)
Q.15) Discuss about Timestamp Based Concurring Control. (Nov-2023)
Q.16) What do you understand by Deadlocks, Explain Avoidance and Prevention in deadlock? (Nov-2023)
Q.17) Write short notes on (any two):
iv) Serializability and its types (Dec-2024)
Q.18) Write short notes on any two:
iv) Recoverability (Nov-2023)
UNIT 5
Storage structures: Secondary Storage Devices, Hashing & Indexing structures: Single level & multilevel indices, Query Processing and Optimization: Various algorithms to implement select, project & join operation of relational algebra, complexity measures. Case Study of any contemporary DBMS.
Previous Years questions appears in RGPV exam.
Q.1) 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.2) Explain Static Hashing? What are the advantages of static hashing in DBMS? (June-2024)
Q.3) What is Secondary storage? Explain types of data storage in DBMS and describe storage hierarchy. (June-2023)
Q.4) Distinguish between:
i) Primary and Secondary indexing.
ii) Ordered indexing and hashing. (June-2023)
Q.5) Why query Optimization is needed? What is the difference between query processing and query optimization? (June-2022)
Q.6) What are the phases of Query Processing? How are query processing and query optimization related? (June-2022)
Q.7) Explain the purpose of indexing in database. How does a primary index differ from a clustering index? (Dec-2024)
Q.8) Describe the different phases of query processing in DBMS. How does query optimization contribute to the efficiency of database systems? (Dec-2024)
Q.9) Given a database of student records, explain how a hash function can be used for quick data retrieval. (Dec-2024)
Q.10) Write an algorithm to implement project and join operation of relational Algebra. (Nov-2023)
Q.11) Compare Single and Multilevel indices. (Nov-2023)
Q.12) Write short notes on any two:
i) Query Processing (June-2024)
Q.13) Write short notes on any two:
i) Complexity Measures (Nov-2023)
