Lovely Professional University,Punjab

Format For Instruction Plan [for Courses with Lectures and Labs

Course No CSE351

Cours Title DATABASE SYSTEMS

Course Planner 14858 :: Monica Sood

Lectures Tutorial Practical Credits 4 0 0 4

Text Book:

1 H. F. Korth , S. Sudarshan, A. Silverschatz,Database System Concepts5th Ed., Tata McGraw Hill, New Delhi,2006

Other Specific Book:

2 .Elmasri&Navathe, Fundamentals of Database systems, Addison &Weisely, New Delhi 3 C. J. Date, Database Systems, Prentice Hall of India, New Delhi 4 Ivan Bayross, SQL, PL/SQL the Programming Language of Oracle, BPB Pu 5 S.K. Singh, Database System Concepts,Design,application,Pearson education

Other Reading Sr No Jouranls atricles as compulsary readings (specific articles, Complete reference)

Relevant Websites Sr. No. (Web adress) (only if relevant to the courses) 6 http://www.databasejournal.com/ Salient Features database information

7 http://resources.mha.dk/FileLib/dbsql/Brief_Introduction_to_R RDBMS elational_DBs.pdf

Detailed Plan For Lectures

Approved for Spring Session 2011-12

Week Number Lecture Number Lecture Topic

Chapters/Sections of Pedagogical tool Textbook/other Demonstration/case reference study/images/anmatio n ctc. planned

Part 1
Week 1 Lecture 1 Lecture 2 Lecture 3 Lecture 4 Week 2 Lecture 5 Lecture 6 Lecture 7 Lecture 8 Week 3 Lecture 9 Lecture 10 Lecture 11 Lecture 12 Week 4 Lecture 13 Introduction, Database system applications & Purpose, Comparison with File Mgt System,Database System Structure , Architecture, Instances & Schemas Data Independence, Database Users & Administrator Data Models,Concept & its types, Conceptual data modelling using E-R data model Database design, entities, attributes, relationships ->Reference :1,ch-1 1.1 and 1.2 http://www.nos.org/htm/ dbase1.htm ->Reference :1,Ch11.3 http://en.wikipedia.org/wi 1.111.12 ki/Data_independence ->Reference :1,1.8 1.9 and 1.12 ->Reference :1,1.3 ->Reference :1,1.4 1.5 1.6

Simple practical problems based on E-R data model ->Reference :1,6.1-6.5 having relations Generalization, specialization, specifying constraints ->Reference :1,6.6-6.7 Advanced practical problems based on E-R data model having relations Relational Model Structure of Relational Databases Relational Databases,Def of relation, keys Relational model integrity rules Relational Algebra., relational model operators ->Reference :1,6.8 ->Reference :1,2.1.1 ->Reference :1,2.1.2 ->Reference :1,2.1.3 ->Reference :1,2.2-2.4 ->Reference :1,2.2-2.4 http://en.wikipedia.org/wi ki/Relational_algebra

Part 2
Week 4 Lecture 14 Lecture 15 Lecture 16 Week 5 Lecture 17 Problems based on Relational Algebra Tuple relational calculus Class Test and Discussion Concept of Indexing ->Reference :1,2.5-2.6 ->Reference :1,5.1 ->Reference :1,ch 12 12.1-12.2 ->Reference :1,ch 12.6 to 12.8

Approved for Spring Session 2011-12

Week 5

Lecture 18 Lecture 19 Lecture 20

Concept of Hashing

->Reference :1,3.1 to 3.3 ->Reference :1,Ch8 8.7 8.8 ->Reference :1,1Ch7 7.1 ->Reference :1,1Ch7 7.2 ->Reference :1,1Ch7 7.3 ->Reference :1,1Ch7 7.4 7.5 ->Reference :1,1Ch7 7.6 7.7 ->Reference :1,1Ch7 7.6 7.7 ->Reference :1,1Ch7 7.7 ->Reference :1,1Ch7

Integrity threats, IntegrityConstraints, Integrity rules, ->Reference :1,Ch8 8.6 Security threats, Authorization & Authentication Functional Dependancy, finding Minimal Cover Finding candidate key and problems based on this Normalization- 1NF, 2NF, 3NF and BCNF Decomposition Example Multi valued dependency and 4NF Join dependency and 5NF Problems based on database design

Week 6

Lecture 21 Lecture 22 Lecture 23 Lecture 24

Week 7

Lecture 25 Lecture 26 Lecture 27 Lecture 28

MID-TERM Part 3
Week 8 Lecture 29 Lecture 30 Lecture 31 Lecture 32 Week 9 Lecture 33 Lecture 34 Lecture 35 Lecture 36 SQL: Basic Structure, DDL Set Operations, Aggregate functions, DML Nested Queries, Joins Complex Queries DCL, PL/SQL Subprograms introduction, Packages introduction Cursors introduction Triggers introduction ->Reference :1,Ch3 3.1 -3.3 ->Reference :1,Ch3 3.1 -3.4 3.6 ->Reference :1,Ch3 3.1 -3.7 ->Reference :1,Ch3 3.1 1 ->Reference :2,Ch 9 9.1 ->Reference :2,Ch 9 9.1 ->Reference :2,Ch 9 9.1 ->Reference :2,Ch 8 8.7

Approved for Spring Session 2011-12

Week 10

Lecture 37 Lecture 38 Lecture 39

Problems based on PL/SQL Problems based on PL/SQL Transaction Mgt,Transaction concept & State

->Reference :2,->Reference 2Ch 9 9.4 ->Reference :1,15.115.4

Part 4
Week 10 Week 11 Lecture 40 Lecture 41 Lecture 42 Lecture 43 Lecture 44 Week 12 Lecture 45 Lecture 46 Lecture 47 Lecture 48 Week 13 Lecture 49 Lecture 50 Lecture 51 Lecture 52 Class Test and Discussion Atomicity & durability, Serializability Concurrency Control: Lock Based Time stamp based, & validation based Protocols Multiple granularity, Deadlock Recovery &Automicity Log based Recovery, Shadow Paging Query Processing & Optimization Class Test and Discussion Object Oriented Databases Object Relational Databases Object Relational Databases revision ->Reference :1,9.1 and 9.2 ->Reference :1,9.9 ->Reference :1,22.1 22.2 ->Reference :1,22.3 ->Reference :1,15.515.8 ->Reference :1,16.1 ->Reference :1,16.216.3 ->Reference :1,16.416.5 ->Reference :1,17.117.3 ->Reference :1,17.417.5 ->Reference :1,13.1 and 13.2

Spill Over
Week 14 Lecture 53 Lecture 54 MS SQL Server Parallel Databases ->Reference :1,29.1 ->Reference :1,21.1

Details of homework and case studies

Approved for Spring Session 2011-12

Homework No.

Objective

Topic of the Homework

Nature of homework (group/individuals/field work Individual Individual

Evaluation Mode

Allottment / submission Week 3/6 8 / 12 3 / 11

Test 1 Test 2

creative learning creative learning

lecture 1 to lecture 20 lecture 29-lecture 44

manual handwritten handwritten

Design problem 1 To make student aware of how concepts of database are applied in real life

Complete normalized database design along with security, Individual concurrency and recovery policies of any organization working on multi user client server environment

Scheme for CA:out of 100*

* In ENG courses wherever the total exceeds 100, consider x best out of y components of CA, as explained in teacher's guide available on the UMS List of suggested topics for term paper[at least 15] (Student to spend about 15 hrs on any one specified term paper) Sr. No. Topic 1 1 application on any bank 2 application on any hospital 3 application on any university/school 4 application on any retail shop 5 application on any hotel/restaurant 6 application on railway reservation 7 application on airline reservation 8 application on cinema hall booking 9 application on any organization maintaining employees record 10 application on library 11 application on irrigation system 12 application on bus reservation 13 application on Laborarory 14 application on any front office 15 application on any accounting system

Approved for Spring Session 2011-12

Approved for Spring Session 2011-12