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, 5th edition, Ramez Elmasri, Shamkan B.    Navathe, Pearson Education, ISBN- 9788131716250

 

Reference:

      1.   Database Management Systems – By Nandagopalan, Sapna Publications

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