DATABASE MANAGEMENT SYSTEMS Diploma in Computer Science & Engineering

Govt. of Karnataka, Department of Technical Education

Diploma in Computer Science & Engineering

Fourth Semester

Subject: DATABASE MANAGEMENT SYSTEMS

 

Contact Hours / week: 4                                               Total hours: 64   

 

 

Table of Contents

 

Sl. No.	Topic	No. of Hrs	Marks
	Section I
1	Databases and database users	04	10
2	Database System Concepts and Architecture	04	10
3	Data Modeling Using the Entity-Relationship(ER) Model	06	15
4	Relational Data Model and Relational Database Constraints	04	10
	Section II
5	The Relational Algebra	08	22
6	SQL:  Schema Definition, constraints, queries and views	12	30
	Section III
7	Functional Dependencies and normalization for relational databases	08	22
8	Introduction to transaction processing concepts and theories	04	10
9	Concurrency Control Techniques	03	08
10	Database Recovery Techniques	03	08
	Seminars and Guest lectures from Industry and Institute	05
	Tests	03
	Total hours	64	145

 

 

 

 

DETAILS OF CONTENTS
1. Databases and database users
1.1	Introduction
1.2	An Example
1.3	Characteristics of the database approach
1.4	Actors on the scene
1.5	Workers behind the scene
1.6	Advantages of using the DBMS Approach
1.7	A Brief History of Database Applications
1.8	When Not to use a DBMS
2. Database System Concepts and Architecture
2.1	Data Models, Schemas , and Instances
2.2	Three-Schema Architecture and Data Independence
2.3	Database Languages and Interfaces
2.4	The Database System Environment
2.5	Centralized and Client /Server Architectures for DBMSs.
2.6	Classification of database Management System.
3. Data Modeling Using the Entity-Relationship(ER) Model
3.1	Using High-Level Conceptual Data Models for Database Design
3.2	An example Database Application
3.3	Entity Types, Entity Sets, attributes and keys
3.4	Relation Types, Relationship Sets, roles and structural constraints
3.5	Weak Entity Types
3.6	Refining the ER Design for the Company Database
3.7	ER Diagrams, naming, conventions and design issues
3.8	Relationship Types of Degree Higher Than Two
4.  Relational Data Model and Relational DataBase Constraints
4.1	Relational Model concepts
4.2	Relational Model Constraints and relational database schemas
4.3	Update Operation, Transaction and Dealing with constraints violations
5. The Relational Algebra
5.1	Unary Relational Operations: SELECT and PROJECT
5.2	Relational algebra operations from Set theory
5.3	Binary Relational Operations: JOIN and DIVISION
5.4	Additional Relational Operations
5.5	Examples of Queries in relational algebra
6. SQL:  Schema Definition, constraints, queries and views
6.1	SQL Data Definition and data types
6.2	Specifying constraints in SQL
6.3	Schema Change statement in SQL
6.4	Basic queries in SQL
6.5	More Complex SQL queries
6.6	INSERT, DELETE and UPDATE statements in SQL
6.7	Specifying constraints and Assertions and Triggers
6.8	Views(Virtual Tables) in SQL
7. Functional Dependencies and normalization for relational databases
7.1	Informal Design guidelines for relation schemas
7.2	Functional dependencies
7.3	Normal forms based on primary keys
7.4	General definition of second and third normal forms
7.5	Boyce-codd Normal form
8. Introduction to transaction processing concepts and theories
8.1	Introduction to transaction processing
8.2	Transaction and system concepts
8.3	Desirable properties of transaction
8.4	Characterizing schedules based on recoverability
8.5	Characterizing schedules based on serializablity
8.5.1	Serial, non-serial and conflict serializable schedules
9.  Concurrency Control Techniques
9.1	Two-Phase Locking Techniques for Concurrency Control
9.1.1	Types of locks and system log tables
9.1.2	Guaranteeing Serializability by Two-Phase locking
9.1.3	Dealing with Dead Lock and Starvation
10. Database Recovery Techniques
10.1	Recovery Concepts
10.1.1	Recovery outline and categorization of  recovery algorithms
10.1.2	Caching (Buffering) of disk blocks
10.1.3	Write-ahead logging, steal/no-steal and force/ no-force
10.1.4	Checkpoints in the system log and fuzzy checkpointing
10.1.5	Transaction rollback

 

                                   

 

General Objectives:

 

1.                  Understand the database concepts, their benefits and advantages

2.                  Understand the Database architecture

3.                  Understand the concepts of E-R diagrams & E-R modeling

4.                  Understand relational algebra

5.                  Comprehend the different aspects of SQL

6.                  Understand the concepts of normalization

7.                  Understand the concepts of transaction processing

8.                  Understand the techniques of concurrency control

9.                  Comprehend the concepts & techniques of backup & recovery of database

 

 

Specific Objectives:

 

1	Databases and database users
	Define Database system
	Learn the characteristics of database approach
	Learn the duties of database administrators, database designers and end users
	Learn the responsibilities of system analyst, application programmers and implementers
	Learn Benefits of using database approach
	Learn when not to use a DBMS
2	Database System Concepts and Architecture
	Define Data models, database schemas and database instances
	Learn the categories of data models
	Learn the three-schema architecture and data independence
	Generalize Database languages and interfaces
	Learn about Database system environment
	Compare classification of DBMS
	Data Modeling Using the Entity-Relationship(ER) Model
	Learn Conceptual Data Models for Database Design
	Define Entity Types, Entity Sets, attributes and keys
	Define Relation Types, Relationship Sets, roles and structural constraints
	Define Weak Entity Types
	Design ER model for the Company Database
	Know the notations of ER Diagrams, naming conventions and design issues
	Relational Data Model and Relational Database Constraints
	Learn the concepts of Relational Model
	Explain Relational Model Constraints and relational database schemas
	Explain Update Operations on relations
	The Relational Algebra
	Learn Unary Relational Operations: SELECT and PROJECT
	Learn Relational algebra operations from Set theory
	Learn Binary Relational Operations: JOIN and DIVISION
	Learn Additional Relational Operations : Aggregate function
	Compare different JOIN operations
	Write queries in relational algebra
	SQL:  Schema Definition, constraints, queries and views
	Learn about Data definition and data types in SQL
	Learn to specify constraints in SQL
	Learn SQL statements to change the database schema
	Use  Insert and Update statements in SQL
	Learn to Formulate queries in SQL
	Create Views in SQL
	Learn to Specify indexes in SQL
	Functional Dependencies and normalization for relational databases
	Generalize the Design guide lines for relation schemas
	Refine the database model using functional dependencies
	Learn Normal forms based on primary keys
	Learn second and third normal forms
	Apply Boyce - Codd normal form to the database
	Introduction to transaction processing concepts and theories
	Analyse the basic concepts of transaction processing
	Appraise the properties of transactions
	Characterize schedules based on recoverability and serializability
	Concurrency Control Techniques
	Learn two-phase locking techniques
	Database Recovery Techniques
	Learn the concepts and the recovery techniques

 

 

 

 

 

Text book:

1.   Fundamentals of Database Systems, 5^th edition, Ramez Elmasri, Shamkan B.    Navathe, Pearson Education, ISBN- 9788131716250

 

Reference:

      1.   Database Management Systems – By Nandagopalan, Sapna Publications

2. Fundamemtals of Database Management Systems, Mark L. Gillenson, 2009, Wiley India
3. Database Management Systems – Raghu Ramakrishnan and Johannes Gehrke – 3rd Edition, McGraw-Hill, 2003
4. DBMS a practical approach , by E R Rajiv Chopra, S Chand publications.