Advance Database Systems

1. AdvanceDatabase Systems By: Aatif Kamal Dated : 3rd March 2008 Credit Hours: 3+0 BIT-7

2. About the Instructor… • Aatif Kamal • aatif@niit.edu.pk ; aatif.kamal@gmail.com • Course Discussion Group • http://groups.google.com/group/dbms-niit • Group email:   • dbms-niit@googlegroups.com • Academic Block II, First Floor. • 051- 9280658 Ext 137

3. Grading Policy • Assignments [10%] .No late submissions will be allowed. • 10/15-Minute Quizzes [10%] All surprise quizzes. NO Makeup quizzes. No retake of quizzes. • Two One-Hour Tests (OHTs) [30%] • Project [10%] • Final Test [40%] • It is mandatory to maintain at least 75% class attendance to be allowed to sit in Final Test.

4. Goals and Objectives • The course goals are to introduce students to the Advance Topics of Databases Systems. • DBMS are at the heart of modern commercial application development. • use extends beyond this to many applications • Large amounts of data Storage with efficient update and retrieval.  • We will study • File organization and indexing, • Relational model and query languages: relational algebra and SQL. • Query and transaction processing and Optimization • Concurrency and Data Recovery

5. Text Book & Reference Books • Text Book • Fundamentals of Database Systems by R. Elmasri and S. Navathe, 4th Edition 2006, Benjamin/Cummings • Database Systems Concepts By Abraham Silberschatz (Fifth Edition) • Reference Books • Modern Database management , Eight Edition, By Jefrey A. Hoffer & Mary B. Prescott.

6. A simplified diagram to illustrate the main phases of database design.

7. Contents Cont… • PART ONE • Chapter 04 - Enhanced ER • Overview of ER • Specialization/ Generalization • Constraints & Characteristics of Hierarchies • Data Abstractions, UNIONS • Mapping of EER Chapter 10 - Functional Dependencies and Normalization for Relational Databases • 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

8. Contents Cont… • Chapter 11 - Relational Database Design Algorithms and Further Dependencies • 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

9. Contents Cont… • PART TWO • Chapter 13 - Disk Storage, Basic File Structures, and Hashing • Secondary Storage Devices • Buffering of Blocks • Placing File Records on Disk • Operations on Files • Files of Unordered Records (Heap Files) • Files of Ordered Records (Sorted Files) • Hashing Techniques • Other Primary File Organizations • Parallelizing Disk Access Using RAID Technology • New Storage Systems

10. Contents Cont… • Chapter 14 - Indexing Structures for Files • 14.1 Types of Single-Level Ordered Indexes • 14.2 Multilevel Indexes • 14.3 Dynamic Multilevel Indexes Using B-Trees and B+-Trees • 14.4 Indexes on Multiple Keys • 14.5 Other Types of Indexes • Chapter 6 - The Relational Algebra and Relational Calculus • 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

11. Contents Cont… • Chapter 15 - Algorithms for Query Processing and Optimization • Translating SQL Queries into Relational Algebra • Algorithms for External Sorting • Algorithms for SELECT and JOIN Operations • Algorithms for PROJECT and SET Operations • Implementing Aggregate Operations and OUTER JOINS • Combining Operations Using Pipelining • Using Heuristics in Query Optimization • Using Selectivity and Cost Estimates in Query Optimization • Overview of Query Optimization in Oracle • Semantic Query Optimization • Chapter 16 - Physical Database Design and Tuning • Physical Database Design in Relational Databases • An Overview of Database Tuning in Relational Systems

12. Contents Cont… • PART THREE • Chapter 17 - Introduction to Transaction Processing Concepts and Theory • Introduction to Transaction Processing • Transaction and System Concepts • Desirable Properties of Transactions • Characterizing Schedules Based on Recoverability • Characterizing Schedules Based on Serializability • Transaction Support in SQL

13. Contents Cont… • Chapter 18 - Concurrency Control Techniques • Two-Phase Locking Techniques for Concurrency Control • Concurrency Control Based on Timestamp Ordering • Multiversion Concurrency Control Techniques • Validation (Optimistic) Concurrency Control Techniques • Granularity of Data Items and Multiple Granularity Locking • Using Locks for Concurrency Control in Indexes • Other Concurrency Control Issues

14. Contents Cont… • Chapter 19 - Database Recovery Techniques • Recovery Concepts • Recovery Techniques Based on Deferred Update • Recovery Techniques Based on Immediate Update • Shadow Paging • The ARIES Recovery Algorithm • Recovery in Multidatabase Systems • Database Backup and Recovery from Catastrophic Failures

15. Lab Work • Although course got no Labs but still • We will have Five special sessions in Lab to get hands on experience of Database Systems advance administration • We will using IBM – DB2 for practice. • This give you exposure to another world leading DBMS • For the lab IBM Pakistan has arranged Certified DB2 Administrator, who will be also sharing his industry experience • For this we will using IBM RedBoooks

16. Computer Usage • IBM DB2 SERVER • (Relational Database Management System) • MS Visio Professional 2002 and Rational Data Modler • (A CASE Tool for Modeling)

17. Essence of Database Systems • Essential part of an education in computer science, Why? • Evolved from a specialized computer application to a central component of a modern computer environment. • To name a few applications: • Banking, Ticket reservation, Student registration, Credit and transaction, Telecommunication, Finance, Sales, Manufacturing, Human resources, Bioinformation, Astronomy. • Active, temporal, Multimedia & Web database . Advance Database's Concepts

18. Basic Definitions • Data – Facts that can be recorded and have implicit meaning. • Database – a collection of related data, preserved over a long period, organized for access and modification. • Data Base Management Systems (DBMS) – a collection of programs that enables users to create and maintain a database. • Defining, constructing and manipulating databases. Advance Database's Concepts

19. Characteristics – I • Existence of a catalog. • Self-describing nature of a database system. • Meta data in catalog describes the structure of the primary databases. • A general purpose DBMS can be used by any application. • program-data & Program-operation independence. • Insulation between Program and Data. • Data abstraction – conceptual representation. Advance Database's Concepts

20. Characteristics – II • Support of multiple user views. • View: a subset of data or virtual data. • Sharing of data among multiple transactions. • Sharing among multi-user. • Concurrency control. Advance Database's Concepts

21. Advantages -I • Controlling Redundancy. • Duplication of efforts. • Wastage of storage space. • Inconsistence data. • Replication. • Restricting unauthorized access. • Security and authorization subsystem. • Providing persistent storage for program objects and data structure. • Permitting inferrencing and actions by using rules. • Deductive database systems. • Active database systems. Advance Database's Concepts

22. Advantages -II • Providing multiple user interfaces. • Query languages. • Programming language interface. • Form-style interface. • Menu-driven interface. • Representing complex relationships among data. • Enforcing integrity constraints. • Providing backup and recovery. Advance Database's Concepts

23. Components -I • Storage Manager. • Storing data, metadata, indexes, logs. • Buffer manager – keeps data in main memory. • Query Processor. • Parses queries. • Optimizes queries by selecting a cost-effective plan. • Executes the plan on the stored data. Advance Database's Concepts

24. Components - II • Transaction Processor. • Logging database changes in order to recover from a system crash. • Concurrent execution of transactions in a way to assures • Atomicity. • Isolation. Advance Database's Concepts

25. Thanks Advance Database's Concepts