UNIVERSITY/BOARD VTU,Belgaum

INSTITUTE/IT SCET,Belgaum

DEPARTMENT/SECTION Computer Science & Engineering REF NO: SCET/ACA/COL12 PAGE NO: 1/3 LECTURE DETAILS DAY TIME /11-

COURSE OUTLINE FOR SUBJECT: DATABASE MANAGEMENT SYSTEMS SUBJECT CODE:06CS54 SEMESTER: V SEMESTER START FROM: AUGUST - 2011 END: NOVEMBER 2011 BASIS: VTU SYLLABUS TIME TABLE REF NO: SCET/ACA/TAB -/11-12 DATE:

CLASS LOCATION: CLASS ROOM

FACULTY NAME: Mr. D.Rajesh CELL NO. S.NO. I : 9739604535

CONTACT PH[O]

[R]

E-MAIL: [email protected] COURSE OBJECTIVES & REQUIREMENTS

CONTACT HRS: 9.00am 5.00 pm on All Working Days AVAILABLE AT: Staff Room OBJECTIVES: The Student Shall: a. Learn basics & applications of Database Management Systems. b. Learn to apply these principles to useful. II STATUTORY REQUIREMENTS 1] SCHEME OF INTERNAL EXAMINATION: 3 Internal tests will be conducted During the semester. I Test - 25 marks [Duration 1 hr] II Test - 25 marks [Duration 1 hrs] III Test - 25 marks [Duration 1 hrs] 2] ASSIGNMENTS 4 Individual & 2 Group Assignments will be given with specific topic well in advance. 3] PRESENTATION/ROLE-PLAY: Individual Presentation & Group wise RolePlay activities will be carried out to initiate fun learning practices in the classroom to break monotonous lectures. This practice will help students to develop their stage courage & team spirit, which is very essential to engineering students. PREPARED BY (FACULTY): DRN ISSUE NO: SL. NO. REVISION NO: DATE: /07/11 COURSE OBJECTIVES & REQUIREMENTS 4] CASE-STUDY/ANALYSIS: Mandatory case study & discussion should be Performed in the classroom after competition of each unit. 5] MODEL ANSWER PAPER/BANK: Ultimately after completion of course syllabus, all the students should solve previous years University Examination question paper & the model answer paper should be submitted to the faculty for rectification & the same should be uploaded to KM. CHECKED BY(HOD): APPROVED BY(HOI):

SCHEDULE Sl.No 1 2 3 4 Topics & Reading Introduction: Introduction; An example; Characteristics of Database approach Actors on the screen; Workers behind the scene; Advantages of using DBMS approach A brief history of database applications. when not to use a DBMS 5 Data models, schemas and instances; Three-schema architecture and data independence Database languages and interfaces, The database system environment; Centralized and client-server architectures, Classification of Database Management systems. Entity-Relationship Model: Using High-Level Conceptual Data Models for Database Design; 8 9 10 11 12 13 14 An Example Database Application; Entity Types, Entity Sets, Attributes and Keys Relationship types, Relationship Sets, Roles and Structural Constraints; Weak Entity Types, Refining the ER Design ER Diagrams, Naming Conventions and Design Issues Relationship types of degree higher than two. Relational Model Concepts Relational Model Constraints and Relational Database Schemas Reference text book

15

Update Operations, Transactions and dealing with constraint violations Internal Assessment I Unary Relational Operations. SELECT and PROJECT Relational Algebra Operations from Set Theory Binary Relational Operations : JOIN and DIVISION Additional Relational Operations Examples of Queries in Relational Algebra; Relational Database Design Using ER- toRelational Mapping. SQL Data Definition and Data Types Specifying basic constraints in SQL Schema change statements in SQL; Basic queries in SQL; More complex SQL Queries More complex SQL Queries Insert, Delete and Update statements in SQL Specifying constraints as Assertion and Trigger Views (Virtual Tables) in SQL Additional features of SQL Database programming issues and techniques Embedded SQL, Dynamic SQL; Database stored procedures and SQL /PSM. Internal Assessment II Informal Design Guidelines for Relation Schemas Functional Dependencies Normal Forms Based on Primary Keys General Definitions of Second Normal Forms General Definitions of Third Normal Forms Boyce-Codd Normal Form Properties of Relational Decompositions Algorithms for Relational Database Schema Design Multi valued Dependencies and Fourth Normal Form Join Dependencies and Fifth Normal Form;

16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

33 34 35 36 37 38 39 40 41 42

Inclusion Dependencies; Other Dependencies and Normal Forms 43 44 45 46 Transaction Management: The ACID Properties Transactions and Schedules; Concurrent Execution of Transactions Lock- Based Concurrency Control Performance of locking; Transaction support in SQL Internal Assessment III 47 48 49 50 51 52 Introduction to crash recovery 2PL, Serializability and Recoverability Lock Management; Introduction to ARIES The log; Other recovery-related structures; The write-ahead log protocol; Checkpointing; Recovering from a System Crash; Media Recovery; Other approaches and interaction with concurrency control

*NOTE: Details regarding Assignments, Presentations, Role Play, Case Study, Examination etc should be included in the above index & schedule should be intimated to all the students well in advance.

UNIT :SCET BASIS: NOTIFICATIONS NO : DATE: ENGINEEING FIFTH SEMESTER LESSON PLAN FOR FIFTH SEMESTER/YEAR 2010-11

Record No.: _______ Page No.:______

DEPARTMENT : Computer Science & Engineering SUBJECT : DATABASE MANAGEMENT SYSTEMS COURSE OBJECTIVES -

TOTAL HRS AS PER UNI/BOARD: : 52 Hrs

a. Learn basics & applications of Database Management Systems. b. Learn to apply these principles to useful. Sl. No. Topics Hrs. Allotted Reference Books Teaching Method & Teaching Aids

Unit 1

Introduction: Introduction; An example; Characteristics of Database approach; Actors on the screen; Workers behind the scene; Advantages of using DBMS approach; A brief history of database applications; when not to use a DBMS. Data models, schemas and instances; Threeschema architecture and data independence; Database languages and interfaces; The database system environment; Centralized and client-server architectures; Classification of Database Management systems. Entity-Relationship Model: Using HighLevel Conceptual Data Models for Database Design; An Example Database Application; Entity Types, Entity

Lecture

Lecture

Unit 2

Sets, Attributes and Keys; Relationship types, Relationship Sets, Roles and Structural Constraints; Weak Entity Types; Refining the ER Design; ER Diagrams, Naming Conventions and Design Issues; Relationship types of degree higher than two. Relational Model and Relational Algebra : Relational Model Concepts; Relational Model Constraints and Relational Database Schemas; Update Operations, Transactions and dealing with constraint violations; Unary Relational Operations: SELECT and PROJECT; Relational Algebra Operations from Set Theory; Binary Relational Operations : JOIN and DIVISION; Additional Relational Operations; Examples of Queries in Relational Algebra; Relational Database Design Using ER- to-Relational Mapping. SQL 1: SQL Data Definition and Data Types; Specifying basic constraints in SQL; Schema change statements in SQL; Basic queries in SQL; More complex SQL Queries. SQL 2 : Insert, Delete and Update statements in SQL; Specifying constraints as Assertion and Trigger; Views (Virtual Tables) in SQL; Additional features of SQL; Database programming issues and techniques; Embedded SQL, Dynamic SQL; Database stored procedures and SQL /PSM. 6 Lecture 8 Lecture

Unit 3

Lecture

Unit 4

Unit 5

Database Design 1: Informal Design Guidelines for Relation Schemas; Unit 6 Functional Dependencies; Normal Forms Based on Primary Keys; General Definitions of Second and Third Normal Forms; Boyce-Codd Normal Form Database Design -2: Properties of Relational Decompositions; Algorithms for Relational Database Schema Design; Multivalued Dependencies and Fourth Normal Form; Join Dependencies and Fifth Normal Form; Inclusion Dependencies; Other Dependencies and Normal Forms Transaction Management: The ACID Properties; Transactions and Schedules; Concurrent Execution of Transactions; Lock- Based Concurrency Control; Performance of locking; Transaction support in SQL; Introduction to crash recovery; 2PL, Serializability and Recoverability; Lock Management; Introduction to ARIES; The log; Other recovery-related structures; The write-ahead log protocol; Checkpointing; Recovering from a System Crash; Media Recovery; Other approaches and interaction with concurrency control. CHECKED BY: REVISION NO.:

Lecture

Lecture

Unit 7

Lecture

Unit 8

PREPARED BY: FACULTY (DRN) SIGN/DATE ISSUE NO: APPROVED BY: DATE:

V Semester 06CS54: DATABASE MANAGEMENT SYSTEMS Course Objective: Learn basics & applications of Database Management Systems. Faculty: Mr. D.Rajesh Sl. N o . 1 Topic Break Down Part A - Unit - I
Introduction: Introduction; An example; Characteristics of Database approach; Actors on the screen; Workers behind the scene; Advantages of using DBMS approach; A brief history of database applications; when not to use a DBMS.

Hours/week: 4 Percentage of portion covered 12 Cumulative percentage of portion covered 12

Data models, schemas and instances; Three-schema architecture and data independence; Database languages and interfaces; The database system environment; Centralized and client-server architectures; Classification of Database Management systems.

Part A - Unit II
Entity-Relationship Model: Using High-Level Conceptual Data Models for Database Design; An Example Database Application; Entity Types, Entity Sets, Attributes and Keys; Relationship types, Relationship Sets, Roles and Structural Constraints; Weak Entity Types; Refining the ER Design; ER Diagrams, Naming Conventions and Design Issues; Relationship types of degree higher than two.

12

24

Part A - Unit - III 4

Relational Model and Relational Algebra : Relational Model Concepts; Relational Model Constraints and Relational Database Schemas; Update Operations, Transactions and dealing with constraint violations; Unary Relational Operations: SELECT and PROJECT; Relational Algebra Operations from Set Theory; Binary Relational Operations : JOIN and DIVISION; Additional Relational Operations; Examples of Queries in Relational Algebra; Relational Database Design Using ER- to-Relational Mapping.

16

40

Part A - Unit - IV 6
SQL 1: SQL Data Definition and Data Types; Specifying basic constraintsin SQ L; Schema change statements in SQL; Basic queries in SQL; More complex SQL Queries.

12

52

Part B - Unit - V 7
SQL 2 : Insert, Delete and Update statements in SQL; Specifying constraints as Assertion and Trigger; Views (Virtual Tables) in SQL; Additional features of SQL; Database programming issues and techniques; Embedded SQL, Dynamic SQL; Database stored procedures and SQL /PSM.

12

64

Part B - Unit - VI 8
Database Design 1: Informal Design Guidelines for Relation Schemas; Functional Dependencies; Normal Forms Based on Primary Keys; General Definitions of Second and Third Normal Forms; Boyce-Codd Normal Form

12

76

Part B - Unit - VII 9

Database Design -2: Properties of Relational Decompositions; Algorithms for Relational Database Schema Design; Multivalued Dependencies and Fourth Normal Form; Join Dependencies and Fifth Normal Form; Inclusion Dependencies; Other Dependencies and Normal Forms

12

88

Part B - Unit VIII 10

Transaction Management: The ACID Properties; Transactions and Schedules; Concurrent Execution of Transactions; Lock- Based Concurrency Control; Performance of locking; Transaction support in SQL; Introduction to crash recovery; 2PL, Serializability and Recoverability; Lock Management; Introduction to ARIES; The log; Other recoveryrelated structures; The write-ahead log protocol; Checkpointing; Recovering from a System Crash; Media

12

100

Recovery; Other approaches and interaction with concurrency control.

Prepared by Mr. D. Rajesh

Approved by