`

Savitribai Phule Pune University

2019 Pattern
( As Per New Revised Syllabus )

ScoreBooster

For

Database Management
System

Semester- V
Computer Engineering
 -: Syllabus Topic :-
:-

Unit I Introduction to Database Management

Syllabus Topics :
Systems and ER Model
Introduction, Purpose of Database Systems, Database-System Applications, View of
Data, Database Languages, Database System Structure, Data Models. Database Design
and ER Model: Entity, Attributes, Relationships, Constraints, Keys, Design Process, Entity
Relationship Model, ER Diagram, Design Issues, Extended E-R Features, converting ER
and EER diagram into tables.

Unit II SQL and PL/SQL

Syllabus Topics :
SQL: Characteristics and Advantages, SQL Data Types and Literals, DDL, DML, DCL, TCL,
SQL Operators. Tables: Creating, Modifying, Deleting, [Link] DML Queries:
SELECT Query and clauses, Index and Sequence in SQL. Views: Creating, Dropping,
Updating using Indexes, Set Operations, Predicates and Joins, Set membership, Tuple
Variables, Set comparison, Ordering of Tuples, Aggregate Functions, SQL Functions,
Nested [Link]/SQL: Concept of Stored Procedures and Functions, Cursors, Triggers,
Assertions, Roles and Privileges.
 -: Imp, Expected & Pyq’s Question :-
:-

Unit 1 Introduction to Database Management

Imp, Expected & Pyq’s Question :

1. Compare DBMS and File processing system in terms of data isolation, data
redundancy, data inconsistency, data integrity.

2. List the main characteristics of the database approach and explain how it differs
from the traditional file system.

3. For the database system to be usable, it must retrieve data efficiently. The need
of efficiency has led designers to use complex data structures to represent data in
the database. Developers hide this complexity from the database system users
through several levels of abstraction. Explain those levels of abstraction in detail
with example.

4. Draw the neat diagram of Database System Structure and explain its components in
detail.

5. Draw architecture of DBMS system and explain function of following components:

i) Storage manager
ii) Query Processor

6. Explain various data models used in DBMS.

7. What are different types of database languages? Give examples.

8. Draw an ER diagram for the banking system. Assume the banking requirements are
as given below:
a. The bank is organized into branches. Each branch is located in a particular
city.
b. The bank offers two types of accounts: saving and current.
 c. Accounts can be held by more than one customer and a customer can have
more than one account.
d. A loan originates at a particular branch and can be held by one or more
customers.
e. Identify the relationship among the entities along with the mapping
cardinalities, keys in the E.R. diagram.
f. Construct appropriate tables for E-R diagram designed with above
requirements.

9. Construct an ER Diagram for Company having following details:

a. Company organized into DEPARTMENT. Each department has unique name and
a particular employee who manages the department. Start date for the
manager is recorded. Department may have several locations.
b. A department controls a number of PROJECT. Projects have a unique name,
number and a single location.
c. Company’s EMPLOYEE name, ssno, address, salary, sex and birth date are
recorded. An employee is assigned to one department, but may work for
several projects (not necessarily controlled by her dept). Number of
hours/week an employee works on each project is recorded; The immediate
supervisor for the employee.
d. Employee’s DEPENDENT are tracked for health insurance purposes
(dependent name, birthdate, relationship to employee).
e. Identify the relationship among the entities along with the mapping
cardinalities, keys in the E.R. diagram.

10. A reputed general hospital has decided to computerize their operations. In the
hospital, many doctors are working. Personal information of doctors is maintained to
get them fixed salary per month.
- The patients are admitted to the hospital into the room. They are treated by
various doctors.
- Sometimes patients perform certain pathological tests which are carried out
into the labs.
 - Identify the relationship among the entities along with the mapping
cardinalities, keys in the E.R. diagram.
- Construct appropriate tables for E-R diagram designed with above
requirements.

11. A post office intends to create a database to keep track of registered letters.
- Every letter has a sender, an origin post office, a destination post office, date
of registration, date of arrival, receiver and a status.
- Every sender/receiver has a name and address.
- Every postman has a designated area, area consists of streets, buildings.
- Letter statuses can be: not yet taken for delivery, delivered, redirected, etc.
- Identify the relationship among the entities along with the mapping
cardinalities, keys in the E.R. diagram.
- Construct appropriate tables.

12. Explain the concept of candidate key and primary key, foreign key. Identify above
listed key for the following schema:
Person (driver_id, name, address, contactno)
Car (licence, model, year)
Owns (driver_id, licence)

13. A weak entity set can always be made into a strong entity set; by adding to its
attributes the primary key attributes of its identifying entity set. Outline what
sort of redundancy will result if we do so while converting to tables.

14. What is Specialization and generalization in Extended E-R diagram?, What is the
difference between them?, Why do we not display the difference in schema
diagram?

15. What are common design issues in ER modeling?

16. What are common pitfalls in ER design? How can they be avoided?
17. Consider the following schema:
Student_fee_details (rollno, name, fee_deposited, date)
Write a trigger to preserve old values of student fee details before updating in
the table.

18. What is view and how to create it? Can you update view? If yes, how? If not, why
not?

19. Consider the following schema [5]

Hotels (hotel_no, hotel_name, city)
Rooms (room_no, hotel_no, price, type)
Write a PL/SQL procedure to list the price & type of all rooms at the hotel ‘TAJ’

20. Define stored procedure. Explain the creating and calling stored procedure with
example. [5]

21. Write a PL/SQL procedure to list the price & type of all rooms at the hotel 'TAJ'.
[5]

22. What is a trigger? How to create it? Discuss various types of triggers. [5]

23. Consider following schema. Student_fee_details (rollno, name, fee_deposited,

date). Write a trigger to preserve old values of student fee details before updating
in the table. [5]

24. Consider the following schemes:

Supplier(SNO, Sname, Status, City)
Parts (PNO, Pname, Color, Weight, City)
Shipments(SNO, PNO, QTY)

Write SQL queries for the following:

i) Find shipment information (SNO, Sname, PNO, Pname, QTY) for those having
quantity less than 157.
 ii) List SNO, Sname, PNO, Pname for those suppliers who made shipments of parts
whose quantity is larger than the average quantity.
iii) Find aggregate quantity of PNO 1692 of color green for which shipments made
by supplier number who residing Mumbai.

25. Consider the following schemas:

Emp(Emp_no, Emp_name, Dept_no)
Dept(Dept_no, Dept_name)
Address(Dept_name, Dept_location)
a. Write SQL queries for the following:
i) Display the location of department where employee 'Ram' is working.
ii) Create a view to store total number of employees working in each
department in ascending order.
iii) Find the name of the department in which no employee is working.

26. Consider the following relation schema:

MOVIES(Mov_Id, Mov_Title, Mov_Year, Dir_Id)
DIRECTOR(Dir_Id, Dir_Name)
RATING(MOV_Id, Rev_Stars)
a. Write the SQL queries for the following:
i) List the title of all the movies directed by 'RAJ KAPOOR'.
ii) Find the name of movies and number of stars for each movie. Sort the
results on movies title and from higher stars to least stars.
iii) Assign the rating of all movies directed by 'Steven Spielberg' to 9.

27. Consider the following relation schema:

EMPLOYEE(Emp_id, Emp_name, Dept_id),
DEPARTMENT(Dept_id, Dept_name),
LOCATION(Dept_name, City)
a. Write the SQL queries for the following:
i) Display the city where employee 'Anita' is working.
ii) Create a view to store the total number of employees in each department,
ordered by department name.
 iii) Find the names of departments that have no employees.

28. Consider the following relation schema:

BOOKS(Book_Id, Title, Author_Id, Year)
AUTHORS(Author_Id, Author_Name)
REVIEWS(Book_Id, Rating)
a. Write the SQL queries for the following:
i) List the titles of all books written by 'Chetan Bhagat'.
ii) Find the title and average rating of each book. Sort the results by title and
from highest rating to lowest.
iii) Update the rating of all books written by 'J.K. Rowling' to 10.

29. Consider the following relation schema:

STUDENTS(Student_Id, Student_Name, Course_Id)
COURSES(Course_Id, Course_Name)
RESULTS(Student_Id, Marks)
a. Write the SQL queries for the following:
i) Display the course name in which student 'Amit Sharma' is enrolled.
ii) Create a view showing number of students in each course, sorted in
descending order of student count.
iii) Find the course names in which no students are currently enrolled.

30. Consider the following relation schema:

FLIGHTS(Flight_Id, Flight_Name, Airline_Id)
AIRLINES(Airline_Id, Airline_Name)
TICKETS(Flight_Id, Passenger_Name, Fare)
a. Write the SQL queries for the following:
i) List all the flight names operated by 'IndiGo'.
ii) Display flight names and average fare for each flight. Sort the results by
flight name and descending fare.
iii) Update the fare of all flights operated by 'Air India' to 5000.
31. What is an index? What are the advantages and disadvantages of using index on a
table?

32. What is the importance of creating constraints on the table? Explain with example
any 4 constraints that can be specified when a database table is created.

33. Explain the concept of Referential and Entity Integrity constraint with example.
 Unit II SQL and PL/SQL

Imp, Expected & Pyq’s Question :

1. What are SQL data types and literals? Explain with examples.

2. What are SQL operators and how are they used in queries? [5]

3. Explain various SQL clauses with examples like WHERE, ORDER BY, GROUP BY,
HAVING. [5]

4. Discuss set operations (UNION, INTERSECT, EXCEPT) in SQL with examples. [5]

5. What are SQL joins? Explain types of joins with examples. [5]

6. What is set membership and set comparison in SQL? [5]

7. What are aggregate functions in SQL? Explain with examples. [5]

8. Discuss built-in SQL functions. Give examples for numeric, string and date functions.
[5]

9. What are nested queries? How are tuple variables used in nested queries? [5]

10. Write a PL/SQL block of code which accepts the rollno from user. The attendance
of rollno entered by user will be checked in student_attendance(RollNo, Attendance)
table and display on the screen. [5]

11. Explain different types of cursors in PL/SQL. [5]

12. What are assertions in SQL? Give example. [5]

13. What are roles and privileges in SQL? Explain how they are managed. [5]
 Unit 1 Introduction to Database Management

(Introduction and Purpose of DBMS)

Pyq Question:
 Compare DBMS and File processing system in terms of data isolation, data
redundancy, data inconsistency, data integrity. Using table with 5 point.
 List the main characteristics of the database approach and explain how it differs
from the traditional file system.
 For the database system to be usable, it must retrieve data efficiently. The need
of efficiency has led designers to use complex data structures to represent data in
the database. Developers hide this complexity from the database system users
through several levels of abstraction. Explain those levels of abstraction in detail
with example.

Introduction:
 A Database Management System (DBMS) is a software that allows users to
efficiently store, modify, and extract information from a database.
 A File Processing System is a traditional way of storing data in flat files, where
each application has its own separate data files.
 DBMS solves the limitations of file-based systems and offers a more structured
and flexible way to manage large amounts of data.

Definition:
 File Processing System: It is a method of storing and organizing data in individual
files where each file is managed by a separate application program.
 DBMS: It is a collection of programs that manage the database structure and
control access to the data stored in a database.

Difference between DBMS and File System:

Comparison Table:
Factor DBMS File Processing System
Data Redundancy - Low redundancy due to data - High redundancy due to
normalization separate files
 Data Isolation - Easy integration across tables - Difficult to access data from
multiple files
Data - Less inconsistency due to - More inconsistency due to
Inconsistency centralized control duplication
Data Integrity - High integrity through - Integrity is hard to enforce
constraints manually
Data Security - Multiple levels of access control - Security must be coded in
application

Characteristics of DBMS Approach:

 It provides a centralized and uniform way to manage data, reducing unnecessary
repetition and inconsistency.
 It supports data abstraction, which helps users interact with data without worrying
about how it is stored.
 It enables concurrent access by multiple users without data loss or conflicts.
 It ensures security by allowing access permissions at different user levels.
 It maintains data integrity by enforcing rules like unique keys, foreign keys, and
not-null constraints.
 It allows backup and recovery of data in case of failure or corruption.
 It provides a systematic way to access data using query languages like SQL.
 It reduces maintenance cost as changes to the data structure can be handled at
the database level.
 Unlike the file system, DBMS uses schema to define the structure, relationships,
and constraints of data.
 It avoids data duplication by storing data only once and referencing it using keys.

Levels of Abstraction in DBMS:

 In a DBMS, abstraction helps hide the complex internal structure of data and
presents it in a simple and understandable way.
 There are three levels of abstraction that divide data representation logically:

Physical Level:
 It defines how the data is physically stored in storage devices like disks or SSDs.