Savitribai Phule Pune University

Third Year of E & Tc Engineering (2019 Course)

Database Management
Teaching Scheme: Credit Examination Scheme:
Theory: 03 hrs. / week 03 In-Sem (Theory): 30 Marks
End Sem (Theory): 70 Marks
Prerequisite Courses, if any: Data Structures
Companion Course, if any:
Course Objectives:
• To understand fundamental concepts of database from its design to its implementation.
• To analyze database requirements and determine the entities involved in the system and with
one another.
• To manipulate database using SQL Query to create, update and manage Database.
• Be familiar with the basic issues of transaction processing and concurrency control.
• To learn and understand Parallel Databases and its Architectures.
• To learn and understand Distributed Databases and its applications.
Course Outcomes: On completion of the course, learner will be able to -

CO1: Ability to implement the underlying concepts of a database system.

CO2: Design and implement a database schema for a given problem-domain using data model.
CO3: Formulate, using SQL/DML/DDL commands, solutions to a wide range of query and update
problems.
CO4: Implement transactions, concurrency control, and be able to do Database recovery.
CO5: Able to understand various Parallel Database Architectures and its applications.
CO6: Able to understand various Distributed Databases and its applications.

Course Contents
Unit I Introduction to DBMS (7 Hrs)
Introduction to Database Management Systems, Purpose of Database Systems, Database-System
Applications, Data Abstraction and Database System Structure.
Relational Model: Structure of relational databases, Domains, Relations, Relational algebra –
fundamental operators and syntax, relational algebra queries, tuple relational calculus.
Entity-Relationship model: Basic Concepts, Entity Set, Relationship Sets and Weak Entity Sets,
Mapping Cardinalities, Keys, E-R diagrams, Design Issues, Extended E-R Features, Converting E-R &
EER diagram into tables.
Mapping of Course CO1: Ability to implement the underlying concepts of a database system.
Outcomes for Unit I

Unit II Relational Database Design (6 Hrs)

Basic concepts, CODD's Rules, Relational Integrity: Domain, Referential Integrities, Enterprise
Constraints, Database Design: Features of Good Relational Designs, Normalization, Atomic Domains
and First Normal Form, Decomposition using Functional Dependencies, Algorithms for
Decomposition, 2NF, 3NF, 4NF and BCNF.
Mapping of Course CO2: Design and implement a database schema for a given problem-domain
Outcomes for Unit II using data model.

Unit III Basics of SQL (7 Hrs)

DDL, DML, DCL, Structure: Creation, Alteration, Defining constraints – Primary key, Foreign key,
Unique key, Not null, Check, IN operator, Functions - Aggregate Functions, Built-in Functions –
Numeric, Date, String Functions, Set operations, sub-queries, correlated subqueries, Use of group by,
having, order by, join and its types, Exist, Any, All, view and its types. Transaction control
commands: Commit, Rollback, Save-point PL/SQL Concepts: Cursors, Stored Procedures, Stored
Function, Database Triggers.
Mapping of Course CO3: Formulate, using SQL/DML/DDL commands, solutions to a wide range
Outcomes for Unit of query and update problems.
III

Unit IV Database Transactions Management (7 Hrs)

Basic concepts of a Transaction, Transaction Management, Properties of Transactions, Concept of
Schedule, Serial Schedule, Serializability: Conflict and View, Cascaded Aborts, Recoverable and Non-
recoverable Schedules, Concurrency Control: Need, Locking Methods, Deadlock handling and Time-
stamp based Protocols.
Mapping of Course CO4: Implement transactions, concurrency control, and be able to do
Outcomes for Unit IV Database recovery.

Unit V Parallel Databases (6 Hrs)

Introduction to Database Architectures: Multi-user DBMS Architectures, Case study- Oracle
Architecture. Parallel Databases: Performance Parameters for Parallel Databases, Types of Parallel
Database Architecture, Evaluating Parallel Query in Parallel Databases and Virtualization on Multicore
processors.
Introduction to Database CO5: Able to understand various Parallel Database Architectures and its
Architectures: Multi-user
DBMS Architectures, applications.
Case study- Oracle
Architecture. Parallel
Databases: Speedup and
Scale up, Architectures
of Parallel Databases.
Distributed Databases:
Architecture of
Distributed Databases,
Distributed Database
Design, Distributed Data
Storage, Distributed
Transaction: Basics,
Failure modes, Commit
Protocols, Concurrency
Control in Distributed
Database.Mapping of
Course Outcomes for
Unit V

Unit VI Distributed Databases (7 Hrs)

Distributed Databases: Distributed Database Management System, Factors Encouraging DDBMS,
Advantages of Distributed Databases, Types of Distributed Databases, Architecture of Distributed
Databases, Distributed Database Design, Distributed Data Storage, and Distributed Transaction:
Basics, Failure modes, Commit Protocols, Concurrency Control in Distributed Database.
Mapping of Course CO6: Able to understand various Distributed Databases and its applications.
Outcomes for Unit VI

Learning Resources
Text Books:
1. Database System Concepts, Abraham Silberschatz, Henry F. Korth& S. Sudarshan, McGraw
Hill
2. An introduction to Database Systems, C J Date, Addition-Wesley
Reference Books:
1. Understanding SQL by Martin Gruber, BPB
2. SQL- PL/SQL by Ivan Bayross
3. S.K.Singh, ―Database Systems : Concepts, Design and Application‖, Pearson, Education, ISBN
978-81-317-6092-5
MOOC / NPTEL Courses:

1. NPTEL Course “Database Management System ”

Link of the Course: https://nptel.ac.in/courses/106/106/106106220/

Savitribai Phule Pune University

Third Year of E & Tc Engineering (2019 Course)
Database Management Lab
Teaching Scheme: Credit Examination Scheme:
Practical: 02 hrs. / week 01 Oral: 25 Marks

Prerequisite Courses, if any:

Companion Course, if any: Database Management System

List of Laboratory Experiments

Group A- Database Programming Languages – SQL

1. Study of Open Source Relational Databases : MySQL

2. Design and develop at SQL DDL statements which demonstrate the use of SQL
objects such as Table, View, Index, Sequence and Synonym.

3.
Design and develop at least 5SQL queries for suitable database application using SQL
DML statements: Insert and Select with operators and functions.

4. Design and develop at least 5 SQL queries for suitable database application using SQL
DML statements: Update and Delete with operators and functions.

5. Design and develop at least 5 SQL queries for suitable database application using SQL
DML statements: all types of Join and Sub-Query.

Group B- Database Programming Languages – PL/SQL

6. Write a PL/SQL block of code for the following requirements:-
Schema:
1. Borrower(Rollin, Name, DateofIssue, NameofBook, Status)
2. Fine(Roll_no, Date, Amt)
• Accept roll_no & name of book from user.
• Check the number of days (from date of issue), if days are between 15 to 30 then
fine amount will be Rs 5per day.
 • If no. of days>30, per day fine will be Rs 50 per day & for days less than 30, Rs. 5
per day.
• After submitting the book, status will change from I to R.
• If condition of fine is true, then details will be stored into fine table.

Frame the problem statement for writing PL/SQL block in line with above
statement.
Problem Statement: Consider table Stud(Roll, Att,Status)

Write a PL/SQL block for following requirement and handle the exceptions. Roll no. of
student will be entered by user. Attendance of roll no. entered by user will be checked in
Stud table. If attendance is less than 75% then display the message “Term not granted”
and set the status in stud table as “D”. Otherwise display message “Term granted” and set
the status in stud table as “ND”

7. Cursors: (All types: Implicit, Explicit, Cursor FOR Loop, Parameterized Cursor)
Write a PL/SQL block of code using parameterized Cursor that will merge the data
available in the newly created table N_RollCall with the data available in the table
O_RollCall. If the data in the first table already exist in the second table then that data
should be skipped.
Frame the separate problem statement for writing PL/SQL block to implement all
types of Cursors in line with above statement. The problem statement should clearly
state the requirements.

8. PL/SQL Stored Procedure and Stored Function.

Write a Stored Procedure namely proc_Grade for the categorization of student. If marks
scored by students in examination is <=1500 and marks>=990 then student will be placed
in distinction category if marks scored are between 989 and900 category is first class, if
marks 899 and 825 category is Higher Second Class
Write a PL/SQL block for using procedure created with above requirement.
Stud_Marks(name, total_marks) Result(Roll,Name, Class).
Frame the separate problem statement for writing PL/SQL Stored Procedure and
function, in line with above statement. The problem statement should clearly state
the requirements.

9. Database Trigger (All Types: Row level and Statement level triggers, Before and After
Triggers). Write a database trigger on Library table. The System should keep track of the
records that are being updated or deleted. The old value of updated or deleted records
should be added in Library_Audit table.
Frame the problem statement for writing Database Triggers of all types, in-line with
above statement. The problem statement should clearly state the requirements.

Group C- Mini Project : Database Project Life Cycle

10. Implement MYSQL/Oracle database connectivity with PHP/python/Java Implement
Database navigation operations (add, delete, edit,) using ODBC/JDBC.

11. Using the database concepts covered in Group A & Group B & connectivity concepts
covered in Group C, students in group are expected to design and develop database
 application with following details:
Requirement Gathering and Scope finalization
Database Analysis and Design:
• Design Entity Relationship Model, Relational Model, Database Normalization
• Implementation :
• Front End : Java/Perl/PHP/Python/Ruby/.net
• Backend : MYSQL/Oracle
• Database Connectivity : ODBC/JDBC

Testing : Data Validation

Group of students should submit the Project Report which will be consist of
documentation related to different phases of Software Development Life Cycle: Title of
the Project, Abstract, Introduction, scope, Requirements, Data Modeling features, Data
Dictionary, Relational Database Design, Database Normalization, Graphical User
Interface, Source Code, Testing document, Conclusion. Instructor should maintain
progress report of mini project throughout the semester from project group and assign
marks as a part of the term work.

Virtual LAB Links:

1. Lab Name: Database Lab

Link of the Virtual Lab: http://vlabs.iitb.ac.in/vlabs-dev/labs/dblab/index.php