Credit Hours: 3+0
By: Aatif Kamal
Dated : 3rd March 2008
About the Instructor…
email@example.com ; firstname.lastname@example.org
Course Discussion Group
Academic Block II, First Floor.
051- 9280658 Ext 137
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%]
Final Test [40%]
It is mandatory to maintain at least 75% class attendance
to be allowed to sit in Final Test.
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
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
Text Book & Reference Books
Fundamentals of Database Systems
by R. Elmasri and S. Navathe, 4th Edition 2006,
Database Systems Concepts
By Abraham Silberschatz (Fifth Edition)
Modern Database management ,
Eight Edition, By Jefrey A. Hoffer & Mary B. Prescott.
Chapter 04 - Enhanced ER
Overview of ER
Constraints & Characteristics of Hierarchies
Data Abstractions, UNIONS
Mapping of EER
Chapter 10 - Functional Dependencies and Normalization for
Informal Design Guidelines for Relation Schemas
Normal Forms Based on Primary Keys
General Definitions of Second and Third Normal Forms
Boyce-Codd Normal Form
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
Other Dependencies and Normal Forms
Chapter 13 - Disk Storage, Basic File Structures, and
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)
Other Primary File Organizations
Parallelizing Disk Access Using RAID Technology
New Storage Systems
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
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
Chapter 15 - Algorithms for Query Processing
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
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
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
Chapter 19 - Database Recovery Techniques
Recovery Techniques Based on Deferred Update
Recovery Techniques Based on Immediate Update
The ARIES Recovery Algorithm
Recovery in Multidatabase Systems
Database Backup and Recovery from Catastrophic Failures
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
For this we will using IBM RedBoooks
IBM DB2 SERVER
(Relational Database Management System)
MS Visio Professional 2002 and Rational Data Modler
(A CASE Tool for Modeling)
Essence of Database Systems
Essential part of an education in computer science,
Evolved from a specialized computer application to a
central component of a modern computer
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
Data – Facts that can be recorded and have implicit
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
Characteristics – I
Existence of a catalog.
Self-describing nature of a database system.
Meta data in catalog describes the structure of the primary
A general purpose DBMS can be used by any application.
program-data & Program-operation independence.
Insulation between Program and Data.
Data abstraction – conceptual representation.
19 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.
20 Advance Database's Concepts
Duplication of efforts.
Wastage of storage space.
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.
21 Advance Database's Concepts
Providing multiple user interfaces.
Programming language interface.
Representing complex relationships among data.
Enforcing integrity constraints.
Providing backup and recovery.
22 Advance Database's Concepts
Storing data, metadata, indexes, logs.
Buffer manager – keeps data in main memory.
Optimizes queries by selecting a cost-effective plan.
Executes the plan on the stored data.
23 Advance Database's Concepts
Components - II
Logging database changes in order to recover from a system
Concurrent execution of transactions in a way to assures
24 Advance Database's Concepts
25 Advance Database's Concepts