Database Management Systems

 

                                                                    LESSON PLAN 

Subject : DATABASE MANAGEMENT SYSTEM
Year : II B-Tech	Semester : I	Branch : IT
Faculty : G. Durvasi	Reg : R20	Academic Year : 2021-20

Course Outcomes:

Factual	Able to Describe a relational database, object-oriented database and Database Architectures.
Conceptual	Able to Describe ER model and basic form of SQL Queries.
Procedural	·   Able to Create, maintain and manipulate a relational database using SQL. ·   Understand the role and issues in management of data such as efficiency, privacy,  security, ethical responsibility, and strategic advantage ·   Able to Examine issues in data storage and query processing and can formulate appropriate solutions.
Applied	Able to Design and build database system for a given real world problem using normalization for database design

 

 

Text book References:

1)      Data base Management Systems, 3/e, Raghurama Krishnan, Johannes Gehrke, TMH.

2)      Data base System Concepts,5/e, Silberschatz, Korth, TMH.

 

·   Text book-1: Introduction to Database Systems, CJ Date, Pearson

·    Text book-2: Data base Management Systems, Raghurama Krishnan, Johannes Gehrke, TATA McGraw Hill 3rd Edition

·   Text book-3: Database Systems - The Complete Book, H G Molina, J D Ullman, J Widom Pearson

·   Text book-4: Fundamentals of Database Systems, Elmasri Navrate Pearson Education

Contents/Activities:

1	Factual: Factual knowledge consists of the basic elements students must know to be acquainted with a discipline	·   What is Database, Database System, Data Independence, The Three Levels of Architecture, Client/Server Architecture (UNIT-I). ·   Introduction to Database Design and ER Diagrams-Entities Attributes, and Entity Sets (UNIT-III). ·   Purpose of Normalization or schema refinement, concept of functional dependency (UNIT-IV). ·   Transaction, properties of transactions, transaction log (UNIT-V). ·   Overview of Storages and Indexing (UNIT-V).
2	Conceptual: Conceptual knowledge consists of the interrelations among the basic elements within a larger structure	·   Relationship and Relationship Sets-Conceptual Design with the ER Models (UNIT-II). ·   The Form of Basic SQL Query (UNIT-II). ·   1NF, 4NF (UNIT-IV). ·   Transaction management with SQL using commit rollback and save point, Concurrency control for lost updates, uncommitted data, inconsistent retrievals and the Scheduler (UNIT-V). ·   File Organization and Indexing (UNIT-V).
3	Procedural: methods of inquiry, and criteria for using skills, algorithms, techniques, and methods.	·   The Relational Model Integrity Constraints over Relations- Key Constraints –Foreign Key Constraints-General Constraints, Relational Algebra and Calculus, Relational Algebra- Selection and Projection- Set Operation, Renaming – Joins- Division (UNIT-II). ·   Union, Intersect, and Except, Nested Queries, Aggregate Operators, Null Values, Complex Integrity Constraints in SQL, (UNIT-III). ·   1NF, 2NF, 3 NF, 4NF, BCNF, Lossless join and dependency preserving decomposition (UNIT-IV). ·   Concurrency control with locking methods : lock granularity, lock types, two phase locking for ensuring serializability, deadlocks, Concurrency control, Database Recovery management : Transaction recovery (UNIT-V). ·   Hash-Based Indexing – Tree-Based Indexing (UNIT-V).
4	Applied: awareness of one's own learning, control and regulation of cognitive processes, self-knowledge, contextual knowledge, and conditional learning	·   Able to create Database Objects like Tables, Views, Procedures, Functions (UNIT-II).

Schedule and Sequence:

 

Day	Topic	Objectives	Before Class-Videos, e-Books, Case Studies	In-Class-Activities, Quiz	Post-Class-Assignment, Discussion Forum
UNIT 1
1	Introduction- What is Database	Able to Describe a database management system, architecture of DBMS, applications, and advantages.	Refer the Text book -1	·         Revise previous class ·         Asking Questions ·         Quiz	·         Assignment ·         Discussion in Forum ·         Review on the topic ·         Share the material on the topic
2	Classification of Databases		Refer the Text book -1
3	What is Database System		Refer the Text book -1
4	Why Database		Refer the Text book -1
5	Application of Databases		Refer the Text book -1
6	Schema, Instance, Data Independence		Refer the Text book -1
7	Relation Systems and Others- Summary		Refer the Text book -1
8	The Three Levels of Architecture		Refer the Text book -1
9	the Database Administrator , DB System Structure		Refer the Text book -1
10	The DBMS- Client/Server Architecture		Refer the Text book -1
UNIT 2
11	Introduction to relational model, Concepts of domain, attribute, tuple, relation,	·      Able to describe relation data model ·      Able to describe  constraints on relational model ·      Able to  implement a table using sql ·      Able perform DML operations using SQL ·      Able query using where clause	Refer the Text book -1	·         Revise previous class ·         Asking Questions ·         Quiz	·         Assignment ·         Discussion in Forum ·         Review on the topic ·         Share the material on the topic
12	Importance  of null values		Refer the Text book -1
13	Constraints  (Domain, Key constraints, integrity constraints) and their importance		Refer the Text book -1
14	Sample database schema, data types		Refer the Text book -1
15	Table definitions (create, alter)		Refer the Text book -1
16	Different  DML operations (insert, delete, update),		Refer the Text book -1
17	Basic SQL querying (select and project) using where clause, arithmetic & logical operations		Refer the Text book -1
18	SQL functions(Date and Time, Numeric, String conversion)		Refer the Text book -1
UNIT 3
19	Introduction to ER model for database design	·   Able to design a database using ER model ·      Able create a database using SQL ·      Able to retrieve the data using SELECT command	Refer the Text book -1	·         Revise previous class ·         Asking Questions ·         Quiz	·         Assignment ·         Discussion in Forum ·         Review on the topic ·         Share the material on the topic
20	ER Diagrams-Entities Attributes, and Entity Sets-Relationship and Relationship Sets		Refer the Text book -1
21	Constraints on ER model		Refer the Text book -1
22	Representations of Sub  classes, super class, inheritance, specialization, generalization using ER Diagrams		Refer the Text book -1
23	Creating tables with relationship, implementation of key and integrity constraints,		Refer the Text book -1
24	Nested queries, sub queries, grouping, aggregation, ordering,		Refer the Text book -1
25	Implementation  of different types of joins, view(updatable and non-updatable), relational set operations		Refer the Text book -1
UNIT IV
26	Purpose of Normalization or schema refinement	·   Able to Describe Normalization of a database design.	Refer the Text book-1	·         Revise previous class ·         Asking Questions ·         Quiz	·         Assignment ·         Discussion in Forum ·         Review on the topic ·         Share the material on the topic
27	concept of functional dependency, normal forms based on functional		Refer the Text book-1
28	1NF, 2NF and 3 NF concept of surrogate key, Boyce-codd normal form(BCNF), Lossless join		Refer the Text book-1
29	Dependency preserving decomposition, 4NF,5NF		Refer the Text book-1
UNIT V
30	Transaction State, Implementation of Atomicity and Durability, Concurrent Executions, Serializability, Recoverability	·   Able to Understand the concept of transaction management ·   Able to describe how the data is stored and organized in a physical memory	Refer the Text book-1	·         Revise previous class ·         Asking Questions ·         Quiz	·         Assignment ·         Discussion in Forum ·         Review on the topic ·         Share the material on the topic
31	Implementation of Isolation		Refer the Text book-1
32	Testing for Serializability		Refer the Text book-1
33	Failure Classification		Refer the Text book-1
34	Storage, Recovery and Atomicity, Recovery algorithm.		Refer the Text book-1
35	B+ Trees: Search, Insert, Delete algorithms		Refer the Text book-1
36	File Organization and Indexing,  Cluster Indexes, Primary and Secondary Indexes, Index data Structures		Refer the Text book-1
37	Hash Based Indexing: Tree base Indexing		Refer the Text book-1
38	Comparison of File Organizations,		Refer the Text book-1
39	Indexes and Performance Tuning		Refer the Text book-1