Transcript Introduction to Database Systems
1
Advance Database Systems
Credit Hours: 3+0 BIT-7 By: Aatif Kamal Dated : 3 rd March 2008
About the Instructor…
Aatif Kamal [email protected]
Course Discussion Group http://groups.google.com/group/dbms-niit
Group email:
Academic Block II, First Floor.
051- 9280658 Ext 137 2
Grading Policy
Assignments [10%] .
No late submissions will be allowed. 10/15-Minute Quizzes [10%]
All surprise quizzes . retake of quizzes.
NO Makeup quizzes . No
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 .
3
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 4
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.
5
A simplified diagram to illustrate the main phases of database design.
6
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 7
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 8
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 9
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 10
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 11
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 12
Contents
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 Cont… 13
Contents
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 Cont… 14
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 15
Computer Usage
IBM DB2 SERVER (Relational Database Management System) MS Visio Professional 2002 and Rational Data Modler (A CASE Tool for Modeling) 16
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 .
17 Advance Database's Concepts
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.
18 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
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.
20 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
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.
24 Advance Database's Concepts
25
Thanks
Advance Database's Concepts