UNIT-1

1. Explain the main characteristics of the database approach versus the file processing
approach?
2. Demonstrate the schema architecture with neat diagram?
3. Discuss about different types of attributes with examples?
4. Draw an E-R diagram for a BANK database schema with atleast five entity types. Also
specify primary key and structural constraints?
5. Descript Database Languages, in detail.
6. Draw an ER Diagram of your campus Recruitment process with a detailed description.
7. Convert an ER Model of a Bank Account into a Relational Model
8. How Integrity Constraints are implemented in Relational Databases?
9. Explain Entities, Attributes and Entity sets of an ER Diagram
10. Illustrate Database Architecture with a supporting diagram
11. Explain in detail about Database Management System advantages over file management
system
12. Explain DML and DDL Commands with example
13. Identify the components that are necessary for building the architecture of DBMS with
neat diagram.
14. Explain the different types of database languages: DDL (Data Definition Language) and
DML (Data Manipulation Language). Provide examples of each.
15. Define entities, attributes, and entity sets in the context of database design. Provide
examples to illustrate these concepts.

16. Draw an ER diagram for Hospital management system with Multi-valued attributes,
Composite attributes, Relationships, Generalization & Aggregation

17. Discuss the concept of logical database design using the ER (Entity-Relationship) model.
Explain the process of converting an ER diagram to a relational database schema.
18. Draw the 3-tyre architecture of DBMS and Illustrate different levels
19. Illustrate the operations supported by DDL and DML with an example
20. What is an attribute? Discuss all types of attributes.
21. Draw an ER model of the Banking database application considering the
22. following constraints:
i) A bank has many entities
ii) Each customer has multiple accounts
iii) Multiple customers belong to a single branch
iv) Single customer can borrow multiple loans
23. v)A branch has multiple employees.
 UNIT-2
1.What is data integrity? Explain the types of integrity constraints?
2.What is a view? How to specify a view? Write about view implementation
techniques?
3.Discuss about Tuple relational calculus?
4.Design a database for an airline. The database must keep track of customers and
their reservations, flights and their status, seat assignments on individual flights,
and the schedule and routing of future flights. Your design should include an E-R
diagram, a set of relational schemas, and a list of constraints, including primary-
key and
foreign-key constraints.
5.Explain the integrity constraints over relations
6.What is a view? How views are implemented?
7.Explain about join and division operations in relational algebra?
8.Write a DBMS program to execute all Aggregate Functions
9.Represent all Functional Dependencies in DBMS
10.Analyze different methods for enforcing integrity constraints in a relational
database, such as primary keys, foreign keys, and check constraints. Compare
and contrast their effectiveness and usability.
11.Describe the relational algebra and its role in manipulating and querying
relational databases. Provide examples to illustrate the different operations and
their outcomes.
12.What are the Aggregate Functions in SQL? Explain with syntax and
examples.
13. Write SQL queries by considering the below relational schema:
EMPLOYEE (Ssn,Name,Bdate,Address,Sex,Salary,Super_ssn,Dno)
DEPARTMENT(Dno,Dname,Mger_ssn,Mgr_start_date)
DEPT_LOCATIONS(Dno,Dlocation)
PROJECT(Pno,Plocation,Pname,Dno)
WORKS_ON(Ssn,Pno,Hours)DEPENDENT(Ssn,Dep_name,Sex,Bdate,Relatio
nship)
i. Retrieve the names of employees who work on project P1.
ii. Calculate the total number of hours worked by each employee.
iii. Find the employees who have dependents.
iv. Retrieve the names of employees along with their dependent names.
v. Calculate the total salary expenditure for each department.
 vi. Find the names of employees who have the same supervisor.
vii. Retrieve the names of employees who are managers.
viii. Calculate the average age of dependents.
ix. Find the projects that have not been assigned to any employee.
x. Retrieve the names of employees who do not have any dependents.

14.What is a Relation? Discuss the steps involved in converting ER-Diagram

into relational Model
15.Answer each of the following questions briefly. The questions are based
on the following relational schema:
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, dname: string, budget: real, managerid: integer)
1. Give an example of a foreign key constraint that involves the Dept
relation. What are the options for enforcing this constraint when a user
attempts to delete a Dept tuple?
2. Write the SQL statements required to create the above relations,
including appropriate versions of all primary and foreign key integrity
constraints.
3. Define the Dept relation in SQL so that every department is
guaranteed to have a manager.
4. Write an SQL statement to add ‘John Doe’ as an employee with eid =
101, age = 32 and salary = 15, 000.
5. Write an SQL statement to give every employee a 10% raise.
16. Discuss about of alteration of Tables and Views
17. Consider the following schema and Write the following queries in
relational algebra.
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
1. Find the names of suppliers who supply some red part.
2. Find the sids of suppliers who supply some red or green part.
3. Find the sids of suppliers who supply some red part or are at Delhi
4. Find the sids of suppliers who supply some red part and some green
part. 5. Find the sids of suppliers who supply every part.
18.Explain the following.
i. Insertion Anomalies ii. Deletion Anomalies iii. Update Anomalies

19.Explain in detail about multivalued dependency and Fourth Normal Form

UNIT - 3
1.Demonstrate aggregate functions in SQL with examples?
2.Explain how constraints are specified in SQL during table creation with
suitable examples?
3.Define normal form? Explain 2NF, 3NF and BCNF with example?
4.Explain the role of functional dependencies in normalization with suitable
examples?
5.Differentiate Tuple Relational Calculus and Domain Relational Calculus with
supporting examples
6.Present any 6 Relational Algebra Operations?
7.What do you mean by Schema Refinement? Analyze it, in detail
8.Define Normalization? Look into various Normal Forms of Databases.
9.Consider the following schemas:
Sailors (sid, sname, rating, age)
Reserves (sid, bid, day)
Boats (bid, bname, color)
Write the following queries in relational algebra, tuple relational Calculus and
domain relational calculus:
a) Find the name of sailors who have reserved boat 103.
b) Find the names and ages of sailors with a rating above 7.
c) Find the names of sailors who have reserved a red boat.
d) Find the sname, bid, and day for each reservation.
e) Find the name of sailors who have reserved at least one boat.
10.What is a Trigger? And what are its three parts? Explain the differences
between Triggers and Integrity constraints
11.How would you use the operators IN, EXISTS, UNIQUE, ANY and ALL in
writing nested queries? Why are they useful? Explain with an example
12.Explain 1NF, 2NF & 3NF with examples.
13.Suppose you are given a relation Grade_report(StudNo, StudName, Major,
Adviser,CourseNo, Ctitle, InstrucName, InstructLocn, Grade) with the
following functional dependencies:
FD1: StudNo ->StudName
FD2: CourseNo ->Ctitle,InstrucName
FD3: InstrucName ->InstrucLocn
FD4: StudNo,CourseNo,Major -> Grade
FD5: StudNo,Major -> Advisor
FD6: Advisor -> Major
 i. Find all candidate keys?
ii. Identify the best normal form that Grade_report satisfies (1NF, 2NF, 3NF,
or BCNF)?
iii.. If the relation is not in BCNF, decompose it until it becomes BCNF. At
each step, identify a new relation, decompose and recompute the keys and the
normal forms they satisfy?
14.Compare and contrast Third Normal Form with Boyce-Codd Normal
Form.
15.Discuss the following
i. i. Comparison using NULL values ii. Outer Joins

UNIT - 4
1. What is concurrency control? Explain how multiple granularity protocol
is used to control concurrent transactions?
2. Discuss the desirable properties of Transactions?
3. Describe the properties of Transaction Atomicity and Durability, and
Transaction Isolation. And also define Serializability.

4. What is transaction? Explain the ACID Properties of transactions

5. Explain two-phase locking for ensuring serializability?
6. Discuss about remote backup systems?
7. Compare Lock-Based Protocols and Timestamp-Based Protocols
8. Explain various anomalies that arise due to interleaved execution of
transactions with suitable examples..
9. Discuss in detail about timestamp based concurrency control techniques.
10.Draw transaction state diagram and describe each state that a transaction
goes through during its execution
11.Describe the principles of timestamp-based protocols in concurrency control.
How are timestamps used to order and manage concurrent transactions?
12.Analyze the role of transaction logs in the recovery process. How are
transaction logs used to recover a database after a failure?
13.Illustrate with an example, the issues that occur when concurrent
execution is uncontrolled.
14.Explain about Two-Phase locking techniques for concurrency control.
15.Explain in detail about different types of Failures.
16.Analyze about serial, non-serial and conflict serializable schedules.
UNIT - 5
1. Explain about the measures that are to be considered for comparing the
 performance of various file organization techniques?
2. Discuss about Pros and Cons of Indexed Sequential Access Method
(ISAM)?
3. What are the benefits of using dynamic indexing?
4. Discuss about the insert and delete operations in B+ trees with an
Example?
5. Apply syntax for Search, Insert and Delete operations on a given B+ Tree
6. Do you support Indexed Sequential Access Method? Give a detailed
Analysis with your supporting reasons.
7. Explain the following: i) Primary index ii) Clustered index iii) Secondary
index
8. What are the benefits of using dynamic indexing? Explain in detail B+
tree file organization.
9. Describe the structure of B+ tree and list the characteristics of B+ tree.
10.Discuss in detail about cluster and multilevel indexes.
11.Explain the different ways of organizing records in files and explain any
one file organization in detail.
12.Suppose that we are using linear hashing on a file that contains records
with the following search-key values: 2, 3, 5, 7, 11, 17, 19, 23, 29, 31
Show the linear hash structure for this file if the hash function is h(x) = x
mod 8 and buckets can hold three records and also Show how the linear
hash structure of above solution changes as the result of each of the
following steps: (i). Delete 11 and 31 , (ii) Insert 1 and 15.
13.Compare and contrast the three file organization methods.
14.Discuss about Indexed Sequential Access Method (ISAM)
15.Discuss about the following.
i. Clustered versus Unclustered Indexes
Dense versus Sparse Indexes
Primary and Secondary Indexes
16.Discuss about insertion and deletion operations in B+-Tree index with
examples