Babu Banarasi Das National Institute of Technology and Management Department of Computer Applications Course Outline & Lecture Delivery Schedule Masters of Computer Applications (MCA) NEW Syllabus (Affiliated to G. B. Technical University, Lucknow.) III Semester MCA313: Database Management System A. Introduction: Computers have already had a considerable impact on many aspects of our society. Medicine, Law enforcement, Government, Banking, Education, Transportation, Planning, and Business – these are only some of the field in which Computers are already playing a highly significant role. Over the next few years we can expect a vast increase in the range of Computer Applications and a corresponding increase in the effect Computers will have on our daily lives. The two areas of Computer Technology that will make the new applications possible – indeed, in most cases they are absolutely fundamental – are Telecommunication and the Integrated Database. Thus Database Management system has evolved from a specialized computer application to a central component of a modern Computing environment. As such, knowledge about Database System has become an essential part of an education in Computer Science. B. Objective: The purpose of this course is to present the fundamental concepts of Database Management. The concepts include aspects of Database Design, Database Languages and Database-System implementation with respect to Relational Database Management System. The course also presents the concepts of High-level Conceptual Data Modeling using Entity-Relationship Model. A part of this course covers the concepts of some advance topics in Database Management System viz Transaction Processing System Concepts, Concurrency control, Recovery and Reliability. After going through the course, the students will find themselves in a position to develop and implement any database application as well as they will be able to manage the Database successfully and comfortably. C. Students Performance Evaluation Scheme: External Semester Examination : 100 Marks Internal Performance Assessment : 50 Marks 3 Sessional Tests : 30 Marks Attendance : 10 Marks Teacher Assessment : 10 Marks Based on Assignments, Seminars, Group Discussions & Class Participation D. Readings: I. Text Books : T1. Silberschatz, Korth and Sudarshan “Database System concepts”, Mc-Graw Hill Publications Website: - http:///www.bell-labs.com/topic/dbbook T2. Elmasari Navathe , “Fundamentals of Database Systems”, Addison-Wesley Publications T3. C. J. Date , “An Introduction to Database Systems”, Narosa Publishing House T4. Connolly & Begg , “Database Systems”, Pearson Education Asia T5. Raghu Ramkrishnan, “Database Management System”, Tata Mc-Graw Hill T6. R. S. Despandey, “SQL/PL SQL for Oracle” T7. Evan Rayros, “SQL/PL SQL for Oracle”
T8. Bipin C.Desai "An Introduction to DBMS", Galgotia Publication T9 Majumdar & Bhattacharya “Database Management system”, TMH II. References : R1. Rob & Cornell, “Database Systems”, Galgotia Publication R2. Alforso F. Cardenas, “Database Management Systems 2/e”, Allyn & Bacon R3. S. M. Deen, “Principle & Practice of Database System”, Macmillan R4. Naphtali Rishe, “Database Design Fundamantals”, Prentice-Hall Englewood R5. P.A. Bernstein, V. Hadzilacos, N. Goodman, “Concurrency Control and Recovery in Database Systems”, Addison Wesly Publication R6. Chao-Chin Yang, “Relational Databases”, Prentice-Hall Englewood R7. Jeffry A. Hoffer, Mary B. Prescott & Fred R. McFadden, “Modern Database Management”, Pearson Education Asia R8. David M. Kroenke, “Database Processing”, Prentice-Hall of India R9. Gio Wiedrhorld, “Database Design”, McGraw Hill
LECTURE DELIVERY SCHEDULE: Lecture Session
UNIT – I 1. 2. 3.
4.
5.
6.
7.
8.
Topics
References
INTRODUCTION TO DATABSE MANAGEMENT SYSTEM Overview of Database Management System Concepts of Data, Database and Database Management System Characteristic of the Database Database users and their roles File-oriented approach versus Database-oriented approach to data management (Disadvantages of file oriented approach) Disadvantages of using DBMS Database System Concepts and Architecture Data models, Schemas and Instances Categories of Data Models Schemas, Instances and Database State DBMS Architecture and Data Independence The Three-Level Schema Architecture (ANSI/SPARC Architecture) The Internal Level The Conceptual Level The External Level Data Independence Logical Data Independence Physical Data Independence Database Languages and Interfaces DBMS Languages Data Definition Language (DDL) Data Control Language (DCL) Data Manipulation Language (DML) High Level or Nonprocedural DML Low Level or Procedural DML DBMS Interfaces Menu-Based Interface for Browsing Form-Based Interface Graphical-User Interface Natural Language Interface Interfaces for Parametric Users Interfaces for the DBA Functions of a DBMS Components of a DBMS
T4,Chapter T1,Chapter T4,Chapter T1,Chapter
1(1.1) 1(1.1) 1(1.1) 1(1.1)
T4, Chapter1 (1.2) T2, Chapter1 (1.2) T4, Chapter1 (1.4)
T2, Chapter2 R6 Chapter1 (1.4.1)
T2, Chapter 3 T1 Chapter1 (1.5.1,1.5.2) R6 Chapter1 (1.4.1)
T2, Chapter 2 (2.3.2)
T2, Chapter 2 (2.3.3) T4, Chapter 4 (4.1)
UNIT II : DATA MODELING USING THE ENTITY-RELATIONSHIP MODEL
Overview and using High-Level conceptual data models for Database Design An example Database Application Entity types, Entity sets, Attributes and Keys Composite versus simple (atomic) attributes Single valued versus Multivalued attribute Stored versus Derived attribute 10. Null values 12.Complex attributes Value sets (Domain) of attributes Key attribute of an Entity type – concept of Super Key Relationships, Relationship Types, Roles, and Structural Constraints 11. Relationship types, Sets and Instances Relationship Degree, Role names and Recursive relationships Relationships as attributes 2. Constraints on Relationship types Cardinality ratio for Binary Relationship 12. Participation constraint and Existence Dependencies 3. Attributes of Relationship Types Weak entity type and its representation in E-R model E-R Diagrams, Naming Conventions and Design Issues Summary of notations for E-R diagrams Proper naming of schema construct 13. Design choice for E-R conceptual design Alternative notations for E-R diagrams UNIT III : ENHANCED ENTITY-RELATIONSHIP & OBJECT MODELING 9.
Subclass, Super class and Inheritance Specialization and Generalization Constraints and Characteristics of Specialization and Generalization 14. Predicate defined (or Condition defined) User defined Disjoint ness and completeness constraint An example of University EER schema Conceptual Object Modeling using UML class diagrams Relationship types of a Degree Higher than two Choosing between Binary and Ternary (or Higher degree) 15. Relationship Constraints on Ternary (or Higher Degree) Relationship Aggregation and Association Unit IV: RELATIONAL DATA MODEL AND LANGUAGE Relations Attribute Domain 16. Instance Domain constraint Degree (arity) and cardinality of a Relation Intension and Extension of a Relation Integrity Constraints Key constraint (Entity Integrity) Foreign Key Constraint (Referential Integrity) 17. General Constraints Enforcing Integrity Constraints Domain Constraints RELATIONAL ALGEBRA AND CALCULUS
T2, Chapter 4(4.2.1)
T2, Chapter 4 (4.2.2)
T2, Chapter 3 (3.4.1)
T2, Chapter 3 (3.4.2)
T2, Chapter 4 (4.1)
T2, Chapter 4 (4.2)
T2, Chapter 4 (4.3)
T3Chapter 4(4.1.1)
T3Chapter 4(4.1.2)
18.
19.
Relational Algebra Union compatibility condition Traditional set operations UNION INTERSECTION Relational Algebra (contd..) DIFFERENCE CARTESIAN PRODUCT Relational Algebra (contd..) Special Relational Algebra Operations SELECTION PROJECTION JOIN Theta, Equi, Natural, and Outer Joins DIVISION
20.
Relational Calculus Tuple Relational Calculus
21.
Relational Calculus (contd..) Domain Relational Calculus
Transaction Concepts Desirable Properties of Transaction Atomicity 22. Consistency Isolation Durability Transactions, Read and Write Operations, and DBMS Buffers STRUCTURED QUERY LANGUAGE (SQL) Basic Structure The Select Clause The Where Clause 23. The From Clause The Rename Operation
24.
25.
26.
27.
28.
29.
Tuple Variables String Operations Ordering the Display of Tuples Duplicates Set Operations The Union Operations The Intersect Operation The Except Operation Aggregate Functions Null Values Nested Sub queries Set Membership Set Comparison Test for Empty Relations Test for the Absence of Duplicate Tuples Views Complex queries Derived Relations The with Clause Modification of the Database Deletion Insertion Updates Update of a View Transactions Joined Relation Join Types and Conditions Data-Definition Language
T3 Chapter 5(5.1.1) T4 Chapter 4(4.1)
T3 Chapter 5(5.1.2, 5.1.3) T4 Chapter 4(4.1.2, 4.1.3) T3 Chapter 5(5.2) T4 Chapter 4(4.2) T3 Chapter 5(5.2) T4 Chapter 4(4.2)
T1 Chapter 15(15.5.1)
T1 Chapter 4(4.1.1) T7 Chapter 3(3.1) T1 Chapter 4(4.1.2) T7 Chapter 3(3.1)
T1 Chapter 4(4.2.2) T7 Chapter 3(3.2)
T1 Chapter 4(4.2.3) T7 Chapter 3(3.3.1)
T1 Chapter 4(4.3.1) T7 Chapter 3(3.3.2)
T1 Chapter 4(4.4.1) T7 Chapter 3(3.4.1)
T1 Chapter 4(4.4.2) T7 Chapter 3(3.4.2)
Domain Types in SQL Schema Definition in SQL Embedded SQL Dynamic SQL 30. Other SQL Features Assertions, Cursers, Trigers DATABASE DESIGN AND NORMAL FORMS
31.
32.
33.
Introduction to Schema Refinement Problems caused by redundancy Redundant Storage Update Anomalies Insertion Anomalies Deletion Anomalies Use of Decomposition Problems related to Decomposition Functional Dependencies Closure of a set of Functional Dependencies Attribute closure Transitive Dependencies Full Functional Dependency Normal Forms (Based on Primary/Candidate Key) 1st Normal Form 2nd Normal Form
34.
Normal Forms (Based on Primary/Candidate Key) 3rd Normal Form Boyce-Codd Normal Form
35.
Decompositions Lossless-Join Decomposition Dependency Preserving Decomposition
36.
Multivalued Dependency and Fourth Normal Form
Join Dependency and 5th Normal Form Inclusion Dependency Transaction Processing Concepts Serializability 38. Conflict Serializability View Serializability Recoverability 39. Recoverable Schedule Cascede Schedule Testing for Serializability 40. Test for Conflict Serializability Test for View Serializability Concurrency Control Lock Based Protocols Locks Shared 41. Exclusive Lock Compatibility Granting of Locks The Two Phase Locking Protocols 42. Growing Phase Shrinking Phase Timestamp-based Protocols Timestamps 43. The Timestamp-Ordering Protocol Thomas Write Rule 44. Validation based Protocol 37.
T1 Chapter 4(4.4.3) T7 Chapter 3(3.4.3)
T3 Chapter 2(2.1.1) T5 Chapter (2.1)
T3 Chapter 2(2.1.2) T5 Chapter (2.2) T3 Chapter 2(2.2.1) T5 Chapter (2.3.1) T2 Chapter14(14.4) T3 Chapter 2(2.1.1) T5 Chapter (2.3.2) T2 Chapter14(14.4,14.5) T3 Chapter 2(2.1.1) T5 Chapter (2.4.1) T2 Chapter15(15.2) T2 Chapter15(15.3) T2 Chapter15(15.4)
T1 Chapter 15(15.5.1) T1 Chapter 15(15.6.1) T1 Chapter 15(15.6.2)
T1 Chapter 16(16.1)
T1 Chapter 16(16.2)
T1 Chapter 16(16.2.1) T1 Chapter 16(16.5.1)
Multiple Granularity Multiversion Schemes 45. Multiverson Timestamp Ordering Multiverson Two-Phase Locking RECOVERY SYSTEM Recovery System Failure classification Transaction Failuer Logical Error 46. System Error System Crash Disk Failure Recovery System (contd..) Storage Structure Storage types Volatile Storage 47. Nonvolatile Storage Stable Storage Stable Storage Implementation Data Access Recovery & Atomicity Log-Based Recovery 48. Deferred Database Modification Immediate Database Modification Checkpoints 49. Shadow Paging Recovery with Concurrent Transaction Interaction with Concurrency Control 50. Transaction Rollback Checkpoints Restart Recovery DISTRIBUTED DATABASE
Homogeneous and Heterogeneous Databases Distributed Data Storage Data Replication Data Fragmentation Transparency
Distributed Transaction System Structure System failure Modes Commit Protocols Two-Phase Commit Three-Phase Commit Concurrency Control in Distributed Databases
51.
53.
54.
Locking Protocols o Single Lock-Manager Approach o Distributed Lock Manager o Primary Copy o Majority Protocol o Biased Protocol Time stamping
55.
56. 57. 58.
Deadlock Handling
Distributed Query Processing Query Transformation Simple Join Processing Semi join Strategy
T1 Chapter 16(16.5)
T1 Chapter 17((17.4.1))
T1 Chapter 17((17.4.1))
T1 Chapter 17((17.4.1))
T1 Chapter 17(17.4.2)
T9 Chapter11 (11.1,11.2.1)
T9 Chapter11 (11.1,11.2.1)
T9 Chapter11 (11.7.2)
T9 Chapter11 (11.7)
T9 Chapter11 (11.7.3, 11.7.4) T9 Chapter11 (11.3) T9 Chapter11 (11.4,11.4.1)