Course Objectives:
To introduce about database management systems
To give a good formal foundation on the relational model of data and usage of Relational Algebra
To introduce the concepts of basic SQL as a universal Database language
To demonstrate the principles behind systematic database design approaches by covering conceptual design, logical design through normalization
To provide an overview of physical design of a database system, by discussing Database indexing techniques and storage techniques
Course Outcomes:
By the end of the course, the student will be able to
Describe a relational database and object-oriented database
Create, maintain and manipulate a relational database using SQL
Describe ER model and normalization for database design
Examine issues in data storage and query processing and can formulate appropriate solutions
Outline the role and issues in management of data such as efficiency, privacy, security, ethical responsibility, and strategic advantage
UNIT I Introduction:
Database system, Characteristics (Database Vs File System), Database Users(Actors on Scene, Workers behind the scene), Advantages of Database systems, Database applications. Brief introduction of different Data Models; Concepts of Schema, Instance and data independence; Three tier schema architecture for data independence; Database system structure, environment, Centralized and Client Server architecture for the database.
UNIT II Relational Model:
Introduction to relational model, concepts of domain, attribute, tuple, relation, importance of null values, constraints (Domain, Key constraints, integrity constraints) and their importance BASIC SQL: Simple Database schema, data types, table definitions (create, alter), different DML operations (insert, delete, update), basic SQL querying (select and project) using where clause, arithmetic & logical operations, SQL functions(Date and Time, Numeric, String conversion).
UNIT III Entity Relationship Model:
Introduction, Representation of entities, attributes, entity set, relationship, relationship set, constraints, sub classes, super class, inheritance, specialization, generalization using ER Diagrams. SQL: Creating tables with relationship, implementation of key and integrity constraints, nested queries, sub queries, grouping, aggregation, ordering, implementation of different types of joins, view(updatable and non-updatable), relational set operations.
UNIT IV Schema Refinement (Normalization):
Purpose of Normalization or schema refinement, concept of functional dependency, normal forms based on functional dependency(1NF, 2NF and 3 NF), concept of surrogate key, Boyce-codd normal form(BCNF), Lossless join and dependency preserving decomposition, Fourth normal form(4NF), Fifth Normal Form (5NF).
UNIT V Transaction Concept:
Transaction State, Implementation of Atomicity and Durability, Concurrent Executions, Serializability, Recoverability, Implementation of Isolation, Testing for Serializability, Failure Classification, Storage, Recovery and Atomicity, Recovery algorithm. Indexing Techniques: B+ Trees: Search, Insert, Delete algorithms, File Organization and Indexing, Cluster Indexes, Primary and Secondary Indexes , Index data Structures, Hash Based Indexing: Tree base Indexing ,Comparison of File Organizations, Indexes and Performance Tuning
Text Books:
1) Database Management Systems, 3/e, Raghurama Krishnan, Johannes Gehrke, TMH
2) Database System Concepts,5/e, Silberschatz, Korth, TMH
Reference Books:
1)Introduction to Database Systems, 8/e C J Date, PEA.
2) Database Management System, 6/e Ramez Elmasri, Shamkant B. Navathe, PEA
3) Database Principles Fundamentals of Design Implementation and Management, Corlos Coronel, Steven Morris, Peter Robb, Cengage Learning.
e-Resources: