CS3492 – DATABASE MANAGEMENT SYSTEMS
Question Bank
UNIT-I (Relational Database)
PART A
1. What is DBMS?
2. Define Relational DBMS.
3. State the merits and demerits of redundancy.
4. List out some applications of databases.
5. Define Schema. Mention its types.
6. List the various levels of abstraction.
7. What is a Data Model?
8. Mention the various types of Data Models with examples.
9. What is Dynamic SQL?
10. State the use of Embedded SQL.
11 List the various operations performed using relational algebra.
12 Define Candidate Key.
13 Differentiate Primary Key and Super Key.
14 Draw a schema and list out the elements in it.
15 List the various types of SQL commands.
16 What is the purpose of DBMS?
17 Define Data Dictionary.
18 State ACID property.
19 What are the various levels of viewing Data?
20 What are the advantages of File Processing System?
UNIT-I
PART – B
1. Explain the Characteristics and Purpose of DBMS. (13)
2. Explain the various views of Data with examples. (13)
3. Discuss all the Data Models with examples. (13)
4. Explain DBMS Architecture with an example. (13)
5. What is Procedural Query Language? Explain the various operations (13)
performed using relational operators.
6. Explain Relational Databases with examples. (13)
7. Draw a Schema and mention the various elements of the schema with (13)
examples
8. Explain the different types of keys used in DBMS. (13)
9. Explain Embedded SQL with its applications. (13)
10. Discuss the use of Dynamic SQL in DBMS in detail. (13)
11. Explain the various types of SQL commands. Discuss the features of SQL. (13)
PART – C
1. Consider the following relations for a company database Application. (15)
Employee (Eno, Name, Sex, DOB, Doj, Designation, Basic_Pay, Dept_No)
Department (DeptNo, Name) Project
(ProjNo, Name, Dept_No)
Works for (Eno,ProjNo,Date, Hours)
The attributes specified for each relation is self-explanatory. However the business rules
are stated as follows. A department can control a project. An employee can work on any
number of projects on a day.
However an employee cannot work more than once on a project he/she worked on that
day. The primary key are underlined.
(i). Identify the foreign keys, Develop DDL to implement the above schema.
(ii) Develop an SQL query to list the department number and the number of Employee
in each department.
(iii) Develop a View that will keep track of the department number, the number of
employees in the department, and the total basic pay expenditure for each
department.
(iv) Develop an SQL query to list the details of employees who have worked in
more than three projects on a day.
Consider the following table
2. Degree(degcode, name, subject)
Candidate(seatno, degcode, semester, month, year, result) Marks(seatno,
degcode, semester, month, year, papcode, marks) Degcode-degree code,
Name-name of the degree (MSc, MCOM)
Perform various query operations using SQL:
(i) Write a SELECT statement to display all the degree codes which are there in the
candidat table but not present in degree table in the order of degcode.
(ii) Write a SELECT statement to display the name of all the candidates who have got
less than 40 marks in exactly 2 subjects.
(iii) Write a SELECT statement to display the name, subject and number of candidates
for all degrees in which there are less than 5 candidates.
Write a SELECT statement to display the names of all the candidates who have
got highest total marks in MSc.,(Maths)
UNIT-II (Database Design)
PART A
1. Define entity and relationship set.
2. Define Cardinality.
3. What is weak entity?
4. Define Functional Dependency.
5. What is Multivalued Dependency?
6. List the properties of Decomposition.
7. What is a Relational Mapping?
8. Define Normalization.
9. What is the difference between unique and primary key?
10. What is domain key normal form?
11 What is transitive functional dependency?
12 Define 1NF with an example.
13 Define 2NF with an example.
14 Define 3NF with an example.
15 Define BCNF with an example.
16 Define 4NF with an example.
17 Define 5NF with an example.
18 Boyce-Codd normal form is found to be stricter than 3NF’. Justify the statement
19 Draw an ER model showing one-to-many relationship.
20 Draw an ER model showing many-to-many relationship.
UNIT-II PART –
B
1. Draw an ER model by taking Hospital management/Banking (13)
System/University Database and explain all the relationship sets with cardinality.
2. Explain EER Model with a neat sketch for School Database. (13)
3. Explain various functional dependencies of an ER model with an example (13)
4. What is Normalization? Explain 1NF, 2NF, 3NF, BCNF, 4NF, 5NF with (13)
examples.
5. Explain the concept of Non-Loss Decomposition with an example (13)
6. Discuss in detail the steps involved in the ER – to Relational mapping in the (13)
process of relational database design
7. Explain with suitable example, the constraints of specialization and (13)
generalization in ER data modelling.
PART – C
1. Draw E – R Diagram for the “Restaurant Menu Ordering System”, which will (15)
Facilitate the food items ordering and services within a restaurant.
The entire restaurant scenario is detailed as follows. The Customer is able to view the food
items menu, call the waiter, place orders and obtain the final bill through the computer kept in
their table. The waiters through their wireless tablet PC are able to initialize a table for
customers, control the table functions to assist customers, orders, send orders to food
preparation staff (chef) and finalize the customer’s bill.
The food preparation staffs (Chefs), with their touch-display interface to the system, are able to
view orders sent to the kitchen by waiters. During preparation, they are able to let the waiter
know the status of each item, and can send notification when items are completed. The system
should have full accountability and logging facilities, and should support supervisor actions to
account for exceptional circumstances, such as a
meal being refunded or walked out on.
2. A car rental company maintains a database for all vehicles in its current fleet. For all vehicles, it (15)
includes the vehicle identification number, license number, manufacturer, model, date of purchase,
and color. Special data are included for certain types of vehicles.
➢ Trucks: cargo capacity.
➢ Sports cars: horsepower, renter age requirement.
➢ Vans: number of passengers.
➢ Off-road vehicles: ground clearance, drivetrain (four- or two-wheel drive). Construct an
ER model for the car rental company database
UNIT-III(Transactions) PART A
1. Define Transaction.
2. Mention the types of failures.
3. What is average response time?
4. State the need of time stamps.
5. Differentiate strict two phase locking protocol and rigorous two phase locking protocol.
6. What are the different modes of lock?
7. Define deadlock.
8. Define Concurrency
9. Brief the phases of two phase locking protocol.
10. What are uncommitted modifications?
11 What is Shadow Paging?
12 Define ARIES. List the various phases of ARIES.
13 What is serializability?
14 What do you mean by phantom problem?
15 What is an isolation level?
16 List the various Recovery techniques.
17 What is deferred update in recovery?
18 Define Validation.
19 What is Snapshot isolation?
20 Define Scheduling.
UNIT-III
PART – B
1. Discuss view serializability and conflict serializability. (13)
2. Briefly describe two phase locking in concurrency control techniques. (13)
3. Explain the concepts of concurrent execution in Transaction processing (13)
system.
4. Brief the ACID properties. Explain Transactions with SQL support for a (13)
banking application
5. What is concurrency control? How is it implemented in DBMS? Illustrate (13)
with a suitable example.
6. Briefly explain about Two phase commit and three phase commit protocols. (13)
7. What is deadlock? How does it occur? How transactions be written to (13)
(i) Avoid deadlock
(ii) Guarantee correct execution. Illustrate with suitable example.
8. (i) Narrate the actions that are considered for deadlock detection and the recovery from (13)
deadlock
(ii) Discuss the properties of a transaction that ensure integrity of data in the database system.
9. Write Short notes on (13)
(i) Validation and Snapshot Isolation.
(ii) Multiple Granularity locking.
10. What is Recovery? Explain various recovery techniques during transactions (13)
in detail
11. Write Short notes on (13)
(i) Shadow Paging.
(ii) ARIES Algorithm.
PART – C
1. Consider the following extension to the tree-locking protocol, which allows both shared and (15)
exclusive locks:
➢ A transaction can be either a read-only transaction, in which case it can request only
shared locks, or an update transaction, in which case it can request only exclusive locks.
➢ Each transaction must follow the rules of the tree protocol. Read- only transactions
may lock any data item first, whereas update transactions must lock the root first. Show
that the protocol ensures serializability and deadlock freedom.
2. Consider the following schedules. The actions are listed in the order they are schedule, and (15)
prefixed with transaction name.
S1: T1: R(X), T2: R(x), T1: W(Y), T2: W(Y), T1: R(Y), T2: R(Y)
S2:T3: R(X), T1: R(X), T1: W(Y), T2: R (Z), T2: W (Z), T3: R (Z)
For each of the schedules, answer the following questions:
➢ What is the precedence graph for the schedule?
➢ Is the schedule conflict-serializable? If so, what are all the conflict equivalent serial
schedules?
➢ iii. Is the schedule view-serializable? If so, what are all the view equivalent serial
schedules?
UNIT-IV(Implementation Techniques)
PART A
1. State the various heuristics involved in query optimization.
2. Mention the various types of record organizations in a file.
3. Draw the storage device hierarchy.
4. What is RAID?
5. List the factors to be taken into account in choosing a RAID system.
6. Mention the various types of file organization with ex.
7. What are the two basic kinds of indices?
8. Differentiate clustering index and non-clustering index
9. What is a balanced tree?
10. Write a query for B+ tree.
11 Define a bucket.
12 Define hash function.
13 What is dynamic hashing?
14 List the steps involved in query processing.
15 Which cost components are used for cost function estimation?
16 What is replication transparency?
17 State the need for query optimization.
18 Differentiate left join and right join.
19 What is Outer join? Give example.
20 Differentiate Data Dictionary Storage and Column Oriented Storage.
UNIT-IV PART –
B
1. Explain how the RAID systems improve performance and reliability. (13)
2. What is RAID? List the different level in RAID technology and explain its (13)
Features with neat sketches.
3. Describe the structure of B+ tree and list the characteristics of a B+tree with (13)
indexing operation.
4. Explain the steps involved in Query Processing with a neat sketch. (13)
5. Discuss static hashing and dynamic hashing with examples. (13)
6. Discuss in detail about how records are represented in a file and how to (13)
organize them in a file.
7. Explain the cost estimation for query processing. (13)
8. Discuss the selection, sorting and join operations using appropriate (13)
algorithms.
9. Explain the various heuristics involved in query optimization. (13)
10. Explain B tree indexing with an example. (13)
PART – C
1. Explain the various steps involved for Select Operation with example. (15)
2. Explain the various steps involved for Join Operation with example. (15)
3. Explain Cost-based optimization with example. (15)
UNIT-V(Advanced Topics)
PART A
1. What are Distributed Databases?
2. Define Filtering and Polinstantiation.
3. List the different types of security problem.
4. What is discretionary access control?
5. What is mandatory access control?
6. What is NOSQL database?
7. State CAP Theorem.
8. What are the advantages of distributed databases?
9. List the issues of distributed databases.
10. State the various utilities of graph databases.
11 What is a threat in DBMS?
12 What are the various security metrics used for statistical databases?
13 What is SQL Injection?
14 How to encrypt a database system? Illustrate with an example.
15 List the open challenges faced in securing a database system.
16 What are Document based systems?
17 What are Column based systems?
18 Define role based access.
19 State the needs for encrypting a database system.
20 What are Graph Databases? Give examples
UNIT-V PART –
B
1. Explain in detail about Distributed Databases with a neat sketch. (13)
2. Discuss the query processing and optimization for a transaction in (13)
Distributed Databases.
3. Explain NOSQL databases with applications. (13)
4. Explain about the various threats and risks in Database Management (13)
Systems.
5. Discuss about various Access Control Mechanisms and Efficient Methods (13)
to Secure the Databases.
6. Write Short notes on (13)
(i) CAP Theorem.
(ii) Key Value Stores.
7. Write Short notes on (13)
(i) Column based systems
(ii) Graph Databases
8. What is Statistical Database? Explain the security measures involved for (13)
having uniqueness of access.
9. Explain the encryption techniques used for securing a database system. (13)
10. Explain Discretionary Access control based on Granting and Revoking (13)
Privileges.
11. Explain about the Federation of Distributed Database Systems (13)
12. Explain SQL Injection Methods with an example. (13)
PART – C
1. Explain the Granting and Revoking Privileges in a Distributed Database (15)
with example.
2. Discuss the Risks associated with SQL Injection Methods with suitable (15)
Preventive measures.
3. Explain the various types of NOSQL Database systems (15)