COURSE OBJECTIVES:
To learn the fundamentals of data models, relational algebra and SQL
To represent a database system using ER diagrams and to learn normalization techniques
To understand the fundamental concepts of transaction, concurrency and recovery
processing
To understand the internal storage structures using different file and indexing techniques
which will help in physical DB design
To have an introductory knowledge about the Distributed databases, NOSQL and database
security
UNIT I RELATIONAL DATABASES
Purpose of Database System – Views of data – Data Models – Database System Architecture –
Introduction to relational databases – Relational Model – Keys – Relational Algebra – SQL
fundamentals – Advanced SQL features – Embedded SQL– Dynamic SQL
UNIT II DATABASE DESIGN
Entity-Relationship model – E-R Diagrams – Enhanced-ER Model – ER-to-Relational Mapping –
Functional Dependencies – Non-loss Decomposition – First, Second, Third Normal Forms,
Dependency Preservation – Boyce/Codd Normal Form – Multi-valued Dependencies and Fourth
Normal Form – Join Dependencies and Fifth Normal Form
UNIT III TRANSACTIONS
Transaction Concepts – ACID Properties – Schedules – Serializability – Transaction support in SQL
– Need for Concurrency – Concurrency control –Two Phase Locking- Timestamp – Multiversion –
Validation and Snapshot isolation– Multiple Granularity locking – Deadlock Handling – Recovery
Concepts – Recovery based on deferred and immediate update – Shadow paging – ARIES
Algorithm
UNIT IV IMPLEMENTATION TECHNIQUES
RAID – File Organization – Organization of Records in Files – Data dictionary Storage – Column
Oriented Storage– Indexing and Hashing –Ordered Indices – B+ tree Index Files – B tree Index Files
– Static Hashing – Dynamic Hashing – Query Processing Overview – Algorithms for Selection,
Sorting and join operations – Query optimization using Heuristics – Cost Estimation.
UNIT V ADVANCED TOPICS
Distributed Databases: Architecture, Data Storage, Transaction Processing, Query processing and
optimization – NOSQL Databases: Introduction – CAP Theorem – Document Based systems – Key
value Stores – Column Based Systems – Graph Databases. Database Security: Security issues –
Access control based on privileges – Role Based access control – SQL Injection – Statistical
Database security – Flow control – Encryption and Public Key infrastructures – Challenges
UNIT I
1. Relational Algebra
2.SQL Fundamentals (DDL, DML,TCL)
3.Keys
4.Data Model
UNIT II
1.BCNF (Boyce Code Normal form)
2.1NF, 2NF, 3NF, Fourth NF (Multivalued dependencies)
3.ER to Relational Mapping
UNIT III
1. Concurrency, Locking Protocol
2. Deadlock (Full topics)
3. Serializability
4. ARIES Algorithm
UNIT IV
1. RAID
2. Dynamic Hashing, Order indices
3. B and B+ tree
4. Algorithm for Selection, sorting and join
UNIT V
1. Flow control, Decryption and Public key
2. SQL Injection, Statistical data
3. Architecture of Distributed Database