Babu Banarasi Das National Institute of Technology and Management

Babu Banarasi Das National Institute of Technology and Management Department of Computer Applications Course Outline & Lecture Delivery Schedule Maste...
Author: Aubrey Black
1 downloads 0 Views 384KB Size
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)

Suggest Documents