PDBMS [CE/CSE/IT]

QUESTION BANK

Unit 1:
Introduction
1) Define: Data, Database, DBMS, Data Redundancy.
2) Explain the purpose of the database system. (4 Marks)
3) Explain the purpose and application of DBMS. (5 Marks)
4) List the benefits of database approach. (2 Marks)
5) Describe various disadvantages of file system compared to Data base management system.
(7 Marks)
6) Discuss disadvantages of file processing systems and advantages of Database Management
Systems. (Diffferentiate it) (7 Marks)
7) Explain different database users. (3 Marks)
8) What are the responsibilities of a DBA? (4 Marks)
9) List the major functions performed by DBA. (2 Marks)
10) What is schema and instance explain with example. (4 Marks)
11) Explain three level architecture of database system. (3 Marks)
12) Explain three levels of data abstraction in DBMS. (4 Marks)
13) What is data independence? Explain the difference between physical and logical data
independence with example. (5 Marks)
14) Explain database system architecture with diagram in detail. (6 Marks)
15) Explain database system 3 tier architecture with diagram in detail. (7 Marks)

Entity-Relationship model
1) What is Entity-Relationship model? Explain the steps to reduce the ER diagram to ER
database schema.
2) What is mapping cardinalities? Explain it with real time examples.
3) Explain types of attribute with example
4) Explain Specialization feature of ER diagram with example.
5) Explain Generalization feature of E-R Diagram.
6) Explain aggregation operation of ER diagram.
7) Draw an ER diagram for exam system of INDUS UNIVERSITY.
PDBMS [CE/CSE/IT]

8) Construct E-R diagram for a hospital with a set of patients and medical doctors.
Associate with each patient a log of various tests and examinations conducted.
1. Construct E-R diagram of the bank. It provides different kinds of bank accounts. And
loans. It operates number of branches.
2. Draw E – R Diagram for the School Management System.
3. Give Symbol used in E-R Diagram and Draw the E-R diagram of Library Management
System.
4. Draw E-R diagram for supplier who supplies different parts. The parts are used in
different projects. Explain the mapping cardinality used. Assume suitable attributes.
5. Construct an E-R Diagram for an insurance company with a set of customers, each of
whom owns number of cars, also each can have number of recorded accident associated
with it.
6. What is constraint in database? Explain types of constraints with suitable example.
7. Draw symbols for following in E-R diagram:
a. Weak Entity set, Derived attribute, Multivalued
b. Relationship Set, and Primary key attribute

Unit 2:
Relational Model
1) Explain candidate key, primary key and foreign key. (3 Marks)
2) Explain following Term with suitable example (6 Marks)
a. Primary Key
b. Candidate Key
c. Super Key
d. On delete cascade
3) Explain following relational algebra operations:
a. Natural join operation (2 Marks)
b. Selection and projection operation (2 Marks)
4) List relational algebra operators and explain any two with example. (5 Marks)
5) What is Relational Algebra? Define Relational Algebra Operation cross product with
example. (3 Marks)
6) Explain selection and projection operation with example. (5 Marks)
7) What is database schema? Explain the select, project, natural join, union and Cartesian
product operations. (7 Marks)
PDBMS [CE/CSE/IT]

8) Consider following schema and represent given statements in relation algebra form.
Branch(branch_name,branch_city) (6 Marks)
Account(branch_name, acc_no, balance)
Depositor(Customer_name, acc_no)
(i) Find out list of customer who have account at ‘abc’ branch.
(ii) Find out all customer who have account in ‘Ahmedabad’ city and balance is
greater than 10,000.
(iii) Find out list of all branch name with their maximum balance.
9) Explain transformation of relational expression into equivalent relational expression.
(7 Marks)

Relational Database design

1) What are anomalies in database design? How can we solve it? (4 Marks)
2) What problems can occur due to wrong database design? How they can be solved?
(4 Marks)
3) Explain the issues of a database design. (7 Marks)
4) Explain BCNF with example. (4 Marks)
5) Explain how to find closure of a set of attributes? (Dec. 2009 – 3 Marks)
6) Define functional dependency. Explain trivial and non-trivial FD with example.
(3 Marks)
7) Explain irreducible sets of Functional dependency with example.( 3 Marks)
8) What is functional dependency? Explain its usage in database design.
(3 Marks)
9) What is Functional Dependency? Explain non-loss decomposition. (7 Marks)
10) What is normalization? What is the need for normalization? (4 Marks)
11) What is normalization? What is redundancy? Compare 1NF and 2NF with example.
(7 Marks)
12) What is normalization? Explain 1NF, 2NF & 3NF. (7 Marks)
13) Why should normalization be performed on a table and what are its benefits. Explain 3NF
and BCNF. (7 Marks)
14) Explain 1NF, 2NF, 3NF and BCNF. (7 Marks)
15) Explain 2NF with example. (3 Marks)
16) Explain 3NF with example. (3 Marks
17) What is the advantage of using 3NF? Explain with example. (5 Marks)
18) Explain BCNF with example. (3 Marks)
19) Why do we need normalization? Explain 4NF & 5NF. (7 Marks)
20) What is non-loss decomposition in database? How it is useful in database?
(3 Marks)
PDBMS [CE/CSE/IT]

21) Normalize (decompose) following relation into lower to higher normal form.
(From 1NF to 4NF). (Mar. 2010 – 8 Marks)

22) What is canonical cover? Consider following set F of functional dependencies on schema
R(A,B,C) and compute canonical cover for F. (6 Marks)
{ A  BC, B  C, A  B, AB  C }
23) Consider a relation R with five attributes A, B, C, D, E having following dependencies:
A  B, BC  E and ED  A (7 Marks)
a. List all Keys for R
b. In which normal form table is, justify your answer.
24) Given relation R with attributes A, B, C, D, E, F and set of FDs as A BC, E CF, B  E
and CDEF. Find out closure {A, B}+ of the set of attributes. (3 Marks)
25) Consider table R(A, B, C, D, E) with FDs as AB, BCE and EDA. The table is in
which normal form? Justify your answer. (7 Marks)

Unit 3:
Transaction Management
1) What is a transaction? Explain the ACID properties. (7 Marks)
2) What is a transaction? Explain its four important properties. (4 Marks)
3) Why concurrency control is needed? (3 Marks)
4) What is concurrency? What are the three problems due to concurrency? How the problems
can be avoided, explain for one of the three problems. (5 Marks)
5) Consider schedule S with transaction T1 and T2. T1 transfer Rs. 150 from account A to C
and T2 adds Rs. 50 into account A. Prepare concurrent schedule with two phase locking
protocol.
6) Explain briefly the meaning of serializability of transactions. (2 Marks)
7) Explain both the forms of serializability & relation between the two. (5 Marks)
8) Explain conflict serializability with example. (4 Marks)
9) Explain view serializability with example. (4 Marks)
10) What is deadlock? When it is occurs and how to avoid it? (7 Marks)
11) What is deadlock? Explain Wait-For-Graph. (2 Marks)
12) Explain deadlock detection mechanism. (4 Marks)
13) Explain various deadlock prevention methods. (4 Marks)
PDBMS [CE/CSE/IT]

14) Explain Two-Phase Locking protocol. (4 Marks)

15) Explain Strict two phase locking with advantages & disadvantages. (5 Marks)
16) Define Locking. Explain two phase locking protocol. (6 Marks)
17) Explain Locked based protocol. (5 Marks)
18) Explain Two phase commit protocol.
19) What is System recovery? Explain two phase commit protocol. (6 Marks)
20) Explain immediate database modification log based recovery method. Also explain role of
check point in log base. (5 Marks)
21) Explain log based recovery and mention all its types. (3 Marks)
22) Explain system recovery procedure with check point record concept.
(4 Marks)

Unit 4:
SQL Concepts
1) Define: (1)Primary key (2)Foreign key (3)Unique Key (4)Not null (5)Commit (6)Candidate
key (7)Rollback.
2) Define: (1)Weak entity (2)Data Dictionary (3)Substring( ) (4)Dual (5)Alter (6)Truncate
(7)Drop.
3) What is ON DELETE CASCADE in SQL? Explain clearly with example.
(4 Marks)
4) What is a join? Explain various types of joins with example.
(4 Marks)
5) Explain natural join operation with example. (3 Marks)
6) Explain DDL, DML, DCL with example. (4 Marks)
7) Write with example various built in string functions. (5 Marks)
8) Explain any two string functions in SQL. (2 Marks)
9) Explain any two aggregate functions of SQL. (2 Marks)
10) What is View? Give the advantages of View. (2 Marks)
11) Explain what is NULL? (2 Marks)
12) We have following relations:
Supplier ( S#, sname, status, city)
Parts ( P#, pname, color, weight, city)
SP ( S#, P#, quantity)
(a) Answer the following queries in SQL: (7 Marks)
(i) Find name of supplier for city = ‘Delhi’.
(ii) Find suppliers whose name start with ‘AB’
(iii) Find all suppliers whose status is 10, 20 or 30.
(iv) Find total number of city of all suppliers.
(v) Find s# of supplier who supplies ‘red’ part.
PDBMS [CE/CSE/IT]

(vi) Count number of supplier who supplies ‘red’ part.

(vii) Sort the supplier table by sname.
(b) Answer the following queries in SQL:
(i) Delete records in supplier table whose status is 40. (1 Mark)
(ii) Add one field in supplier table. (1 Mark)
(iii) Explain commit command. (2 Marks)
(iv) Explain Curser in PL/SQL. (3 Marks)
(c) Answer the following queries in SQL: (7 Marks)
(i) Find name of parts whose color is ‘red’
(ii) Find parts name whose weight less than 10 kg.
(iii) Find all parts whose weight from 10 to 20 kg.
(iv) Find average weight of all parts.
(v) Find S# of supplier who supply part ‘p2’
(vi) Find name of supplier who supply maximum parts.
(vii) Sort the parts table by pname.
(d) Answer the following queries in SQL:
(i) Delete records in parts table whose color is ‘blue’. (1 Mark)
(ii) Drop one field in parts table. (1 Mark)
13) Consider following schema and write SQL for given statements.
Student ( rollno, name, branch ) (8 Marks)
Exam ( rollno, subject_code, obtained_marks , paper_code )
Papers ( paper_code, paper_satter_name, university )
(i) Display name of student who got first class in subject ‘130703’.
(ii) Display name of all student with their total mark.
(iii) Display list number of student in each university.
(iv) Display list of student who has not given any exam.
14) Write down the query for the following table where primary keys are underlined.
Person (ss#, name, address) (8 Marks)
Car (license, year, model)
Accident (date, driver, damage-amount)
Owns (ss#, license)
Log (license, date, driver)
(i) Find the total number of people whose cars were involved in accidents in 2009.
(ii) Find the number of accidents in which the cars belonging to “S.Sudarshan”.
(iii)Add a new customer to the database.
(iv) Add a new accident recorded for the Santro belonging to “KORTH”
15) Consider the employee data. Give an expression in SQL for the following query:
Employee (employee-name, street,city) (8 Marks)
Works (employee-name, company-name,salary)
Company (company-name, city)
PDBMS [CE/CSE/IT]

Manages (employee-name, manager-name)

(i) Find the name of all employees who work for State Bank.
(ii) Find the names and cities of residence of all employees who work for State Bank.
(iii)Find all employee in the database who do not work for State Bank.
(iv) Find all employee in the database who earn more than every employee of UCO Bank.
16) Consider following schema and write SQL for given statements. (8 Marks)
Student (Rollno, Name, Age, Sex, City)
Student_marks (Rollno, Sub1, Sub2, Sub3, Total, Average)
Write query to
(i) Calculate and store total and average marks from Sub1, Sub2 & Sub3.
(ii) Display name of students who got more than 60 marks in subject Sub1.
(iii)Display name of students with their total and average marks.
(iv) Display name of students who got equal marks in subject Sub2.
17) Implement following relation using SQL query.
(i) Student (stud_no, stud_name, sub1, sub2, totalmark, percentage) (5 Marks)
Create the table, add 5 records and display the data.
(ii) Calculate total mark and percentage and also arrange the students on ascending order
of total mark and also make a view of it. (5 Marks)
(iii)Update the mark of sub1 of student_no=111 with 50 and also Calculate total marks
and percentage accordingly. (4 Marks)
18) Implement following relation using SQL query.
Employee (emp_no, emp_name, department, city, salary)
(i) Find all the employee whose emp_no is lessthan 100 and salary more than 25000 and
department is “Account”
(ii) Count the no of employee and Sum the salary of all employee
(iii)Delete the employee having minimum salary.
19) We have following relations:
EMP(empno, ename, jobtitle, managerno, hiredate, sal, comm, deptno)
DEPT(deptno, dname, loc)
Answer the following queries in SQL.
(i) The employees who are getting salary greater than 3000 for those persons belonging
to the department 20
(ii) Employees who are not getting any commission
(iii)Find how many job titles are available in employee table.
(iv) Display total salary spent for each job category.
(v) Display number of employees working in each department and their department
name.
(vi) List ename whose manager is NULL.
(vii) List all employee names and their salaries, whose salary lies between 1500/- and
3500/- both inclusive.
PDBMS [CE/CSE/IT]

20) We have following relations:

EMP(empno, ename, jobtitle, managerno, hiredate, sal, comm, deptno)
DEPT(deptno, dname, loc)
Answer the following queries in SQL.
(i) Find the Employees working in the department 10, 20, 30 only.
(ii) Find Employees whose names start with letter A or letter a.
(iii)Find Employees along with their department name.
(iv) Find Employees whose manager is KING.
(v) Find the Employees who are working in Smith's department
(vi) Find the Employees who get salary more than Allen’s salary.
(vii) Display employees who are getting maximum salary in each department
21) Write queries for the following tables.
T1 ( Empno, Ename , Salary, Designation),
T2 ( Empno, Deptno.)
(i) Display all rows for salary greater than 5000.
(ii) Display the deptno for the ename=’syham’.
(iii)Add a new column deptname in table T2.
(iv) Change the designation of ename=’ram’ from ‘clerk’ to ‘senior clerk’.
(v) Find the total salary of all the rows.
(vi) Display Empno, Ename, Deptno and Deptname.
(vii) Drop the table T1.
22) Consider following schema and write SQL for given statements.
Student ( RollNo, Name, Age, Sex, City )
Student_marks ( RollNo, Sub1, Sub2, Sub3, Total, Average )
Write the query to
(i) Display name and city of students whose total marks are greater than 225.
(ii) Display name of students who got more than 60 marks in each subject.
(iii)Display name of city from where more than 10 students come from.
(iv) Display a unique pair of male and female students.

PL/SQL Concepts
1) Explain the advantages of PL/SQL. (3 Marks)
2) Explain commit and rollback command. (5 Marks)
3) Write Note on Cursor and its types. (6 Marks)
4) Write short note on database triggers in PL/SQL. (5 Marks)