DATABASE MANAGEMENT SYSTEM

GANPAT UNIVERSITY

FACULTY OF ENGINEERING & TECHNOLOGY

Programme

Bachelor of Technology

Branch/Spec.

Computer Engineering,

Information Technology,

Computer Engineering (Artificial Intelligence)

Semester

III

Version

2.0.0.1

Effective from Academic Year

2023-24

Effective for the Batch admitted in

July 2022

Course Code

2CEIT303

Course Name

Database Management System

Teaching Scheme

Examination Scheme (Marks)

(Per week)

Lecture (DT)

Practical (Lab.)

Total

CE

SEE

Total

L

TU

P

TW

Credit

3

0

1

-

4

Theory

40

60

100

Hours

3

0

2

-

5

Practical

30

20

50

Pre-requisites

Basic knowledge of Computer Programming

Course Outcomes

On successful completion of the course, the students will be able to:

CO1

explain preliminaries of database management system concepts, applications and relational model & relational algebra.

CO2

understand transaction-processing system; determine the transaction atomicity, consistency, isolation, and durability.

CO3

construct efficient SQL queries to retrieve and manipulate data as required, query processing & Optimize query’s execution.

CO4

construct an ER model and perform database normalization.

CO5

implement the isolation property, including locking, time stamping based on concurrency control and Serializability of scheduling.

Theory Syllabus

Unit

Content

Hrs.

1

Elementary concepts of DBMS:

Purpose & application of database, Relational model and others, Architecture for Database System, Database administrator and users, DDL, DML, DCL.

04

2

The relational data model & algebra:

Various keys, Basics of the Relational Model, Relational Operations-Extended & Additional Operators of Relational Algebra- Constraints on Relations.  

08

3

Database Design using E-R model:

Various models Elements of the E/R Model-Design Principles, E/R diagrams, The Modelling of Constraints-Weak Entity Sets, Participation constraint and Generalization-Specialization-Aggregation, E/R Diagrams to Relational Designs.

05

4

Database design:

Introduction to normalization, Trivial and nontrivial functional dependencies, Armstrong's axioms for FD's , Closure of a set of attributes and dependencies, first, second and third forms, Boyce / Codd normal form, multivalued dependencies and fourth normal form, join dependencies and fifth normal form, Non loss decomposition, Dependency preservation.

07

5

Data storage and querying:

basic SQL query structure, set operations, nested subqueries, aggregation, database modification, join expressions, Query Processing & Query Optimization basics.

05

6

Transaction processing:

Transactions, ACID Property, transaction atomicity and durability, Schedules & Types, Conflict-Serializability, View-Serializability, Testing of Serializability.         

05

7

Concurrency:

Enforcing Serializability by Locks-Locking Systems with Several Lock Modes-Concurrency Control by Timestamps, two phase locking protocol, Deadlock detection and prevention.       

06

8

Recovery:

Failures and their classification, basics of storage structure, log based recovery-deferred and immediate modification schemes, Checkpoints.

05

Practical Content

Practicals and Tutorials will be carried out based on the syllabus.

Text Books

1

Database System Concepts by Silberschatz, Korth, Sudarshan

2

SQL, PL/SQL the Programming Language of Oracle by Ivan Bayross

Reference Books

1

An introduction to Database Systems by C J Date, Addition-Wesley

2

Database System using Oracle by Nilesh shah, PHI

3

Fundamentals of Database Systems by Ramez Elmasri & Shamkant B. Navathe, Addison-Wesley

ICT/MOOCs Reference

1

https://nptel.ac.in/courses/106104135/

2

https://nptel.ac.in/courses/106106093/

3

https://www.edx.org/course/introduction-to-sql

Mapping of CO with PO and PSO:

PO1

PO2

PO3

PO4

PO5

PO6

PO7

PO8

PO9

PO10

PO11

PO12

PSO1

PSO2

PSO3

CO1

3

3

3

1

1

0

0

0

1

0

0

1

2

2

0

CO2

3

2

1

1

1

0

0

0

1

0

0

1

2

1

0

CO3

3

3

3

2

2

0

1

1

2

0

1

3

2

3

1

CO4

3

3

3

2

1

0

0

0

2

0

1

2

2

2

1

CO5

3

2

2

1

2

0

1

1

2

0

1

2

2

2

0