DBMS

Syllabus
Text Book	Reference Book

---------------------------------------------------------------------------------------------------------------

Introduction: [Basic concepts, Advantages of DBMS over file-processing systems, Data abstraction, Data models and data independence, Components of DBMS and overall structure of DBMS]

 Introduction (PPT) 

Question Bank

Data modeling, entities, attributes, relationships, constraints, Keys, E-R diagrams, and components of the E-R model

Entity-Relationship Model (study Material and PPT)

--------------------------------------------------------------------------------------------------------------

Relational Model: Basic concepts, Attributes and domains, Concept of integrity and referential constraints, Schema diagram, Relational query languages, Relational Algebra and Relational Calculus: Tuple relational and domain relational calculus

• Unit 2: Relational Model Overview

  Unit 2: Integrity Constraints 

  Unit 2: Keys 

  Unit 2: Relational algebra

  Unit 2: Relational Calculus : Tuple Relational calculus, Domain relational calculus 

• Unit 2: Query Languages

• candidate Key Question Bank

• ----------------------------------------------------------------------------------------------------------

• Introduction to SQL, characteristics, advantages, data types, and literals
• DDL, creating, modifying, and deleting tables, Views: Creating, Dropping, Updation using views, DML, Operators, SQL DML queries, SELECT query and clauses. Structured Query Language- II: Set operations, Predicates and joins, Set membership, Tuple variables, Set comparison, Ordering of tuples, Aggregate functions, Nested queries, Database modification using SQL Insert, Update and Delete queries, Dynamic and SQL and concept of stored procedures, Queryby-example

1. Introduction to SQL	ppt
2. Intermediate SQL	ppt
3. Advanced SQL	ppt

-------------------------------------------------------------------------------------------------------

UNIT IV: Relational Database Design and Storage Systems

• Notion of normalized relations
• Functional dependency, decomposition, and properties
• • Functional Dependency 
    Classification of functional Dependencies  
    More questions on functional dependency 
    More questions on functional dependency
  • Questions [Explain trivial dependency? 2 marks
  • What is meant by the closure of functional dependencies? Illustrate with an

    example.
  • What is Functional Dependency? Explain types and properties of FD’s.
  • ]
• Normalization using functional dependency, multi-valued dependency, and join dependency
• Normalizations ( 1 nf,2nf,3nf,bcnf,4nf,5nf) and examples
• Questions:
• What is normalization? Explain its need.
• Explain insertion, deletion and modification anomalies with suitable examples.
• State BCNF. How does it differ from 3NF?
• State 1NF, 2NF & 3NF and explain with examples.

extra questions[What is normalization? What is need for normalization. Define data Anomalies.

Define 1nf.Define 2nf.Define 3 nf. Define BCNF. Define 5nf. List the decomposition properties. Define lossless joins (or) What is non additive property. Explain BCNF with example(or) How to convert a relation into BCNF. Why must multivalued dependencies exist in pairs?nComparison of BCNF and 3NF.Why 4NF in Normal Form is more desirable than BCNF?]

• Storage systems: secondary storage, RAID,
• 1. physical storage in database
  2.magnetic-hard-disk-mechanism
  3. Hard disk drive interface
  4. RAID
• 
  
• file organization :
Click here
• 
  
• Indices, static and dynamic hashing
• click here
• Distinguish between: i) Primary and Secondary indexing. ii) Ordered indexing and hashing.
• 
  
• B-Trees and B+ Trees

Examples and Questions

-------------------------------------------------------------------------------------------------------------

UNIT V: Query Processing and Transaction Management

• Measures of query cost
• Selection operation, sorting, and join operation

click Here

Question [Outline the steps involved in query processing.]

• Transaction concept, components of transaction management

click here

• Concurrency and recovery system
• Concurrency control protocols (timestamps, locking), validation
• click here
• Multiple granularity, deadlock handling
• click here
• 
  
• Crash recovery methods (log-based recovery, shadow-paging)
• Buffer management and
• 
  
• remote backup system
• Click here
• [Question : Write in detail about Remote Backup systems?]

MCQs (Unit 1 to 3)

DBMS LAB

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Exercise 5

Exercise 6

Exercise 7

Exercise 8

Exercise 9 

Syllabus: DBMS

 Course Objectives:

1. Understand database management systems architecture and functioning.
2. Learn the relational model.
3. Use structured query language (SQL) and its syntax, transactions, database recovery, and query optimization techniques.
4. Acquaint with various normalization forms and query processing.
5. Learn indexing methods.

Course Outcomes: After completing the course, students should be able to:

1. Explain the need for database management.
2. Design and implement a database schema for a given problem domain.
3. Normalize a database.
4. Create and query a database using SQL DML/DDL commands, stored procedures, and functions.
5. Declare and enforce integrity constraints on a database.
6. Demonstrate an understanding of indexing methods.

Course Contents: UNIT I: Introduction and Entity-Relationship Model

• Basic concepts, advantages of DBMS over file-processing systems
• Data abstraction, data models, and data independence
• Components and overall structure of DBMS
• Data modeling, entities, attributes, relationships, constraints
• Keys, E-R diagrams, and components of the E-R model

UNIT II: Relational Model and SQL-I

• Basic concepts of the relational model
• Attributes, domains, integrity and referential constraints
• Schema diagram, relational query languages
• Relational algebra and relational calculus
• Tuple and domain relational calculus
• Introduction to SQL, characteristics, advantages, data types, and literals
• DDL, creating, modifying, and deleting tables

UNIT III: Views and SQL-II

• Creating, dropping, and updating views
• DML operators, SQL DML queries, SELECT query and clauses
• Set operations, predicates, and joins
• Set membership, tuple variables, set comparison
• Ordering of tuples, aggregate functions
• Nested queries, database modification using SQL
• Insert, update, and delete queries
• Dynamic SQL and stored procedures
• Query-by-example

UNIT IV: Relational Database Design and Storage Systems

• Notion of normalized relations
• Functional dependency, decomposition, and properties
• Normalization using functional dependency, multi-valued dependency, and join dependency
• Storage systems: secondary storage, RAID, file organization
• Indices, static and dynamic hashing
• B-Trees and B+ Trees

UNIT V: Query Processing and Transaction Management

• Measures of query cost
• Selection operation, sorting, and join operation
• Transaction concept, components of transaction management
• Concurrency and recovery system
• Concurrency control protocols (timestamps, locking), validation
• Multiple granularity, deadlock handling
• Crash recovery methods (log-based recovery, shadow-paging)
• Buffer management and remote backup system

Text Books:

1. Abraham Silberschatz, Henry F. Korth, and S. Sudarshan, "Database System Concepts," McGraw Hill Education, 6th Edition, 2011.
2. Ramez Elmasri and Shamkant B. Navathe, "Fundamental Database Systems," Pearson Education, 7th Edition, 2015.
3. Raghu Ramkrishnan, Johannes Gehrke, "Database Management Systems," McGraw Hill Education, 3rd Edition, 2007.

Reference Books:

1. Carlos Coronel, Steven Morris, "Database Systems: Design Implementation and Management," Cengage Learning Press, 11th Edition, 2014.
2. J. Murach, "Murach's MySQL," Shroff Publication, 2nd Edition, 2016.
3. J. Murach, "Murach's Oracle SQL and PL/SQL: Works with All Versions Through 11g," Shroff Publication, 2008.