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
[Link] is data integrity? Explain the types of integrity constraints?
[Link] is a view? How to specify a view? Write about view implementation
techniques?
[Link] about Tuple relational calculus?
[Link] 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.
[Link] the integrity constraints over relations
[Link] is a view? How views are implemented?
[Link] about join and division operations in relational algebra?
[Link] a DBMS program to execute all Aggregate Functions
[Link] all Functional Dependencies in DBMS
[Link] 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.
[Link] the relational algebra and its role in manipulating and querying
relational databases. Provide examples to illustrate the different operations and
their outcomes.
[Link] 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.
[Link] is a Relation? Discuss the steps involved in converting ER-Diagram
into relational Model
[Link] 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.
[Link] the following.
i. Insertion Anomalies ii. Deletion Anomalies iii. Update Anomalies
[Link] in detail about multivalued dependency and Fourth Normal Form
UNIT - 3
[Link] aggregate functions in SQL with examples?
[Link] how constraints are specified in SQL during table creation with
suitable examples?
[Link] normal form? Explain 2NF, 3NF and BCNF with example?
[Link] the role of functional dependencies in normalization with suitable
examples?
[Link] Tuple Relational Calculus and Domain Relational Calculus with
supporting examples
[Link] any 6 Relational Algebra Operations?
[Link] do you mean by Schema Refinement? Analyze it, in detail
[Link] Normalization? Look into various Normal Forms of Databases.
[Link] 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.
[Link] is a Trigger? And what are its three parts? Explain the differences
between Triggers and Integrity constraints
[Link] would you use the operators IN, EXISTS, UNIQUE, ANY and ALL in
writing nested queries? Why are they useful? Explain with an example
[Link] 1NF, 2NF & 3NF with examples.
[Link] 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?
[Link] and contrast Third Normal Form with Boyce-Codd Normal
Form.
[Link] 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.
[Link] transaction state diagram and describe each state that a transaction
goes through during its execution
[Link] the principles of timestamp-based protocols in concurrency control.
How are timestamps used to order and manage concurrent transactions?
[Link] the role of transaction logs in the recovery process. How are
transaction logs used to recover a database after a failure?
[Link] with an example, the issues that occur when concurrent
execution is uncontrolled.
[Link] about Two-Phase locking techniques for concurrency control.
[Link] in detail about different types of Failures.
[Link] 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.
[Link] in detail about cluster and multilevel indexes.
[Link] the different ways of organizing records in files and explain any
one file organization in detail.
[Link] 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.
[Link] and contrast the three file organization methods.
[Link] about Indexed Sequential Access Method (ISAM)
[Link] about the following.
i. Clustered versus Unclustered Indexes
Dense versus Sparse Indexes
Primary and Secondary Indexes
[Link] about insertion and deletion operations in B+-Tree index with
examples