DATABASE MANAGEMENT SYSTEM
SUBJECT CODE: CE 305
Teaching Scheme (Credits and Hours)
Teaching scheme Total Evaluation Scheme
L T P Total Credit Theory Mid Sem CIA Pract. Total
Exam
Hrs Hrs Hrs Hrs Hrs Marks Marks Marks Marks Marks
03 00 02 05 04 03 70 30 20 30 150
Learning Objectives:
The educational Objectives of this Course are:
To learn the fundamentals of data models and to conceptualize and depict a database
system using ER diagram.
To make a study of SQL and relational database design.
To understand the internal storage structures which will help in physical DB design.
To know the fundamental concepts of transaction processing- concurrency
controltechniques and recovery procedure.
To have an introductory knowledge about the Storage and Query processing
techniques
Outline of the Course:
Sr. Title of the Unit Minimum
No Hours
1 Database Management System – Concepts and Architectures 6
2 Database Storage Structures 3
3 Data Models 6
4 Relational Database design 5
5 Transaction Management 8
6 Backup and Recovery Techniques 6
7 Query Processing and Evaluation 5
8 Database Security and Authorization 6
9 SQL/PL Covered
10 Emerging Databases and Case Studies in Lab
Total hours (Theory): 45
Total hours (Lab): 30
Total hours: 75
Detailed Syllabus
Sr. Topic Lecture Weightage
No Hours %
1 Chapter 1. Database Management System – Concepts 6 10
and Architectures
Introduction and Purpose
Database Architectures
o Centralised
o Client-Server
o Server system
Transaction servers
Data servers
Cloud based servers
o Parallel
o Distributed
o Web based system
Web architecture (2 tier , 3 tier, N-tier
Architecture)
Web services – SOAP
2 Chapter 2. Database Storage Structures 3 5
Introduction
Database - Tablespace, Segment, Extent, Block, Data
File
Indexing, Hashing, Clusters
Data Dictionary and Dictionary Views
3 Chapter 3. Data Models 6 5
Introduction to various data models – Record based
& Object based
Cardinality Ratio & Relationships
Representation of entities, attributes, relationship
attributes, relationship set, Generalization,
aggregation
Structure of relational Database and different types
of keys
Codd’s rules and Relational data model & relational
algebra
4 Chapter 4. Relational Database design 5 10
Basic System Development Life Cycle
Database Design – ER to Relational
Functional dependencies
Normalization
Normal forms based on primary keys (1NF, 2NF,
3NF, BCNF, 4NF, 5NF)
Loss less joins and dependency preserving
decomposition
5 Chapter 5. Transaction Management 8 20
Introduction
What is a Transaction?
o Transaction Properties
o Transaction Management with SQL
o The Transaction Log
Concurrency Control
o Concurrency control with Locking Methods
o Types of Locks
o Two-Phase Locking to Ensure Serializablility
o Deadlocks
Concurrency Control Methods
6 Chapter 6. Backup and Recovery Techniques 6 10
Introduction
User Managed Backups
Recovery Manager Backups - RMAN
Transaction Recovery
System Recovery
Media Recovery
7 Chapter 7. Query Processing and Evaluation 5 10
Introduction
Query Interpretation
Equivalence of Expressions
o Selection Operation
o Natural Join Operations
o Projection Operations
o Three-Way Join
Estimation of Query-Processing Costs
Estimation of Costs of Access Using Indices
Structure of Query Optimizer
8 Chapter 8. Database Security and Authorization 6 10
Introduction
Security and Integrity Violations
Managing Users
Authorization-Privileges and Roles
Views
Integrity Constraints
Database Auditing
9 Chapter 9. PL/SQL and RMAN Covered 20
SQL: in Lab
DDL(Data Definition Language) Sessions
DML(Data Manipulation Language)
DRL(Data Retrieval Language)
DCL(Data Control Language)
TCL(Transaction Control Language)
SQL Functions and Aggregate Functions
Join Operations
Views
Audit Trails
Plan Table
PL:
PL/SQL Block
Control Structures
Cursor, Trigger
Procedure
Functions
RMAN Recovery Scenarios
10 Chapter 10. Emerging Databases and Case Studies Part of NA
Limitations of Conventional Databases Lab
Multimedia Database,
Temporal Databases,
Spatial Databases
Cloud Databases
Google Big Table
No SQL
SQLite
TOTAL 45 100
Instructional Method and Pedagogy:
At the start of course, the course delivery pattern, prerequisite of the subject will be
discussed.
Lectures will be conducted with the aid of multi-media projector, black board, OHP
etc.
Attendance is compulsory in lecture and laboratory which carries 10 marks in overall
evaluation.
One internal exam will be conducted as a part of internal theory evaluation.
Assignments based on the course content will be given to the students for each unit
and will be evaluated at regular interval evaluation.
Surprise tests/Quizzes/Seminar/tutorial will be conducted having a share of five marks
in the overall internal evaluation.
The course includes a laboratory, where students have an opportunity to build an
appreciation for the concepts being taught in lectures.
Experiments shall be performed in the laboratory related to course contents.
Learning Outcome:
On successful completion of the course, the student will:
Identify and define the information that is needed to design a database management
system for a business information problem.
Create conceptual and logical database designs for a business information problem.
Build a database management system that satisfies relational theory and provides
users with business queries.
Understand the core terms, concepts, and tools of relational database management
systems.
Create and maintain databases and tables..
Manipulate data in a database using SQL.
Manage transactions and locks to ensure data concurrency and recoverability.
Manage users, privileges and resources
Start up and shut down an Oracle instance and database
Study fundamentals of Recent and Emerging Database Systems in Market.
Reference Books:
1. Principles of Distributed Database Systems by M. TAMER OZSU, Patrick
Valduriez, S. Sridhar (Pearson Publication)
2. Database system concepts', 6th Edition –Abraham Silberschatz, Henry Korth, S,
Sudarshan, (McGraw Hill International )
List of experiments:
Sr. Practical Aim
No.
1 Creating and Manipulating Database objects and Applying Constraints (DDL)
2 Manipulating Data with Database Objects (DML)
3 Retrieving, Restricting and Sorting Data (DRL)
4 SQL Single Row Functions
5 SQL Multiple Row Functions (Aggregate Function)
6 Displaying Data from Multiple Tables (Join)
7 Using Commit and Rollback show Transaction ACID Property.
8 Securing data using Views and Controlling User Access (DCL)
9 Write a join query based on two tables and analyse the query using action plan
and Audit Trails.
10 PL/SQL Block Syntax and DML Operation through PL/SQL Block
11 Control Structures in PL/SQL
12 Working with Cursor
13 Creating Procedures and Functions in PL/SQL
14 Creating Database Triggers
15 Database Recovery Scenarios using Recovery Manager(RMAN)
