Course outlines Advanced Database Systems Introduction Objectives A

Course outlines } } } “Advanced Database Systems” Introduction Objectives A Data. Base Management System DBMS Structure of a DBMS File and data models – an overview Evolution of Database Technology } Advanced database contents/topics Centralized database system related issues Distributed database system related issues Introduction to Database system adapted architectures Modern database systems Database Security and Authorization Database development applications

“Advanced Database Systems”- Introduction Objectives } To understand the fundamental concepts and advanced technology underlying database systems: Ê database management systems roles and Ê Advanced database fundamentals Ê Modern database systems: Ê Object-relational databases Ê Spatial databases Ê Active databases Ê Etc. Ê Physical data organizations Ê Query processing and optimization Ê Transaction management Ê concurrence control Ê crash recovery Ê Security and authorization Ê Distributed database system issues Ê Parallel and system architectures Ê OLAP, data mining and data warehouse } To gain hand-on experience with database application systems Ê developing a small application system using MS SQLSERVER Ê Java/Vis…ual. Basic and database-backed web-sites 2

“Advanced Database Systems”- Introduction A Data. Base Management System DBMS } What is DBMS? Ê Need for information management Ê A very large, integrated collection of data. Ê Models real-world enterprise. Ê DMBS contains information about a particular enterprise Ê A Database Management System (DBMS) is a software package designed to store and manage databases. Ê DBMS provides an environment that it both convenient and efficient to use } Why Use a DBMS? Purpose of Database Systems Database management systems were developed to handle the following issues/difficulties of typical file-processing systems supported by conventional operating systems: Ê Ê Ê Ê Ê Data independence and efficient access. Difficulty in accessing data Data integrity and security. Data redundancy and inconsistency Uniform data administration. Concurrent access, recovery from crashes. Data isolation – multiple files and formats Replication control Reduced application development time. 3

“Advanced Database Systems”- Introduction Structure of a DBMS (1/2) } A typical DBMS has a layered architecture. } The figure does not show the concurrency control and recovery components. } This is one of several possible architectures; each system has its own variations. These layers must consider concurrency control and recovery Query Optimization and Execution Relational Operators Files and Access Methods Buffer Management Disk Space Management DB 4

“Advanced Database Systems”- Introduction The enclosed figure shows the structure (with some simplification) of a typical DBMS based on the relational data model Structure of a DBMS (2/2) Web Forms Application Front Ends SQL Statements Plan Executor Parser Operator Evaluator Optimizer Transacti on Manager Lock Manager Files and Access Methods Concurren cy control Disk Space Manager Buffer Manager SQL Interface Query Evaluatio n Engine Recove ry Manage r DBM S Index Files. Data Files System Database catalog 5

“Advanced Database Systems”- Introduction File and data models – an overview } Data Models Ê A data model is a collection of concepts for describing data. Ê A schema is a description of a particular collection of data, using the a given data model. } History of File Organizations: Ê Sequential search Ê index sequential Ê B-tree Ê Hashing } Classification of Database Models: Ê Entity-relationship Various database Ê Network Ê Hierarchical models provide logical Ê Relation and physical data Ê Object-oriented Ê Deductive independence to Ê Object-relational separate simple logical Ê Semi-structured data XML database structures and complicated 6

“Advanced Database Systems”- Introduction Evolution of Database Technology 1960 s: Hierarchical (IMS) & network (CODASYL) DBMS. 1970 s: Relational data model, relational DBMS implementation. 1980: RDBMS rules the earth 1985: Advanced data models (extended-relational, OO, deductive, etc. ) Application-oriented DBMS (spatial, scientific, engineering, etc. ). 1990 s: ORDB, OLAP, Data mining, data warehousing, multimedia databases, and network databases. 7

“Advanced Database Systems”- Introduction Advanced database contents/topics } Centralized database system related issues Ê Storage and File Structure Ê Indexing and Hashing Ê Query Processing Ê Transaction Fundamentals Ê Concurrency Control Techniques Ê Database Recovery } Distributed database system related issues Ê Distributed Databases “Fundamentals” Ê Distributed Transactions-Commit Protocols Ê Distributed Databases Concurrency Control Ê Heterogeneous Distributed Databases Ê Advanced and modem database systems } Introduction to Database system adapted architectures } Modern database systems Ê Object-Oriented Databases Ê Spatial and Geographic Databases Ê Data Mining and Data Warehousing: Concepts and Techniques } Database Security and Authorization } Database development applications } Recap mini project 8

Centralized database system related issues File Organizations and indexing } Storage and File Structure Ê Physical Storage Media - we introduce the different types of storage media and technologies, conversed topics are: Ê Volatile and nonvolatile storage Ê Storing devices and Magnetic Disks Ê Performance Measures Ê Introduction to the RAID technology Ê Storage and File Organization - in this section we speak about the following topics: Ê Storage Access : Buffer Manager, Buffer-Replacement Policies Ê File Organization: Organization of Records in Files, Un ordered, Ordered files, Hashed files Ê Data-Dictionary Storage } Indexing and Hashing Ê Introduction Ê Basic Concepts Ê Ordered Indexes-Clustered / Unclustered Ê Multi-level Indexes Ê Index Update: Deletion / Insertion Ê B+-Tree Index Files, B-Tree Index Files Ê Example of a B+-tree - Insert / Delete Ê Static Hashing / Dynamic Hashing Ê Example of Hash Index Ê Hashing vs. Other Schemes Ê Grid Files Ê Bitmap Indices Ê Index Definition in SQL 9

Centralized database system related issues Query Processing } Basic Steps in Query Processing – an overview } Measures of Query Cost } Query Processing- Several algorithms ÊSelection Operation ÊJoin Operation: different algorithms to implement joins ÊNested-loop join - Block nested-loop join ÊIndexed nested-loop join ÊOther Operations – an overview } Query Optimization using Heuristics ÊQuery tree, Graph tree ÊTransformation of Relational Expressions ÊEquivalence Rules ÊPushing Selections, Join Ordering, etc. ÊChoice of Evaluation Plans ÊStructure of Query Optimizers } Evaluation of Expressions – Materialization, Pipelining } Statistics for Cost Estimation 10

Centralized database system related issues Transaction and concurrency control } Transaction Fundamentals Ê Transaction Concept Ê Transaction - ACID properties Ê Transaction States Ê Concurrent Executions – Schedules Ê Ê Ê Ê } Scheduling Transactions – Serializability - Conflict Serializability Testing for conflict Serializability - Precedence graph Serializability - View Serializability Recoverability: Why recovery is needed? Cascading rollback Concurrency Control – an overview Levels of Consistency - Levels of Consistency in SQL-92 Transaction Definition in SQL Database Concurrency Control Techniques Ê Ê Purpose of Concurrency Control Lock-Based Protocols - Pitfalls and serializability issues The Two-Phase Locking (2 PL) Protocol Timestamp-Based Protocols - Recoverability and Cascade Freedom Ê Deadlock Handling Ê Deadlock Prevention Strategies Ê Deadlock Detection – graph based strategy, Deadlock Recovery Ê Locking and Insert, Delete Operations Ê Other protocols and schemes - an overview Ê Ê Graph-Based Protocols Validation-Based Protocol Granularity of data items , Intention Lock Modes Multi-version Schemes, Index Locking Protocol 11

Centralized database system related issues Database Recovery } Database Recovery – an overview Ê Failure Classification Ê Algorithms/techniques and Storage Structures } Data Access } Recovery and Atomicity } Log-Based Recovery Ê Deferred Database Modification Ê Immediate Database Modification } Checkpoints – an overview Ê Checkpoints recovery steps - example Ê Recovery With Concurrent Transactions } Buffer Management - Log Record Buffering } Failure with Loss of Nonvolatile Storage } Shadow Paging 12

Distributed database system related issues Distributed Databases “Fundamentals” } Distributed Database concepts Ê Distributed Database System – an overview Ê Data Distribution – Advantages and benefits } Types of Distributed Databases Ê Heterogeneous and Homogeneous Databases } Distributed DBMS Architectures } Distributed Data Storage Ê Data Replication Ê Data Fragmentation } Distributed Catalog Management } Data transparency - Naming of Data Items } Transparency and updates 13

Distributed database system related issues Distributed Transactions and Concurrency control } Distributed Transactions- Commit Protocols Ê Distributed Transactions - Overview Ê System Failure Modes Ê Commit Protocols - Two Phase Commit Protocol (2 PC) Ê Phase 1: Obtaining a Decision Ê Phase 2: Recording the Decision Ê Handling of Failures Ê Recovery and Concurrency Control Ê Alternative Models - Persistent messaging systems Ê Error Conditions with Persistent Messaging Ê Persistent Messaging and Workflows Ê Implementation of Persistent Messaging Ê Annex - Three Phase Commit (3 PC), Transactional Workflows } Distributed Databases Concurrency Control Ê Concurrency Control – an overview Ê Centralized: Single-Lock-Manager Approach Ê Distributed Lock Manager Ê Ê Ê - Primary copy- Majority protocol Ê - Biased protocol- Quorum consensus Time-stamping Replication with Weak Consistency Multi-master Lazy Replication Deadlock Handling Ê Prevention strategies Ê Centralized Approach Ê Distributed Query Processing Ê Simple Join Processing / Possible strategies, Semijoin Strategy Ê Join Strategies that Exploit Parallelism 14

Distributed database system related issues Heterogeneous Distributed Databases } Motivation, Database Integration } Data. Base Management Systems “DBMS”– An overview } Old-School Approaches } Database integration - Problem Dimensions } Important Types of Integrated Database Systems Ê Multidatabases Ê Federated Databases Schema Architecture ÊHandling Integration Conflicts ÊA Generic Federated DBMS Architecture ÊWrapper- Architecture & Tasks, Example ÊSchema Integration ÊQuery Processing ÊWeb as a Loosely Coupled Federated Database Ê The Mediator - Mediator Systems Ê Data Warehousing 15

Database System Architectures } Centralized Systems } Client-Server Systems ÊTransaction Servers ÊData Servers } Parallel DBMS ÊInterconnection Network Architectures ÊArchitecture Issue: Shared What? ÊParallel DBMS Techniques and different Types ÊData Partitioning ÊParallel processing 16

Object-Oriented Databases (1/2) } Motivation Ê Introduction – Motivating Example Ê Why Object Databases “ODBs”? Ê Need for Complex Data Types } Engineering Database Design-overview Ê Database Design Process Ê Logical/Physical database design } Object-oriented concepts Ê ODBs are more Natural & Direct Ê Object-oriented terminologies – an overview Ê Investigation and analysis - RDBs vs. ORDBs , RDBs vs. ODBs. , etc. } The Object-Oriented Data Model - An example of a class in UML } Object-Oriented Data Modelling - rapid overview } OO Data Modelling: Example 17

Object-Oriented Databases (1/2) } } Object Persistence - Introduction Persistent Programming Languages } } } Persistent C++ Systems Object Persistence – Using database The ODMG Standard for Object Databases } ODMG-OQL for querying the database } ODMG- Binding languages } } Persistent Java Systems Exercise Reconsidering the University schema… Object vs. Relational Seminar - ODB Design (Self-Study) Ê Specifying Object Persistence via Naming and Reachability Ê Persistent Objects – Storage and Access issues Ê OODBMS Features, OODB Evolution, Strategies for building OODBMS Ê ODMG Object Database Standard, and Components ÊAn Architecture for OODBMS ÊObject Model, Object Definition Language (ODL) ÊMapping Class Diagrams into ODL Ê Simple OQL Queries, Retrieving Objects – an example Ê Database Entry Points Ê Retrieving data from multiple objects Ê Unnesting and Nesting Collections Ê Objects network sample - OQL vs. SQL Ê ODMG Types Ê C++ ODL, OMT, OQL 18

Spatial and Geographic Databases } Fundamentals of GIS - Overview Ê Spatial and Geographic Data(bases) Ê Why Study GIS? What is GIS? What’s in a GIS? } GIS vs. Other Systems - How GIS differs from Related Systems } GIS System-Architecture and Components Ê GIS Spatial Data Model ÊGIS Spatial and Attribute Data ÊHow a GIS Organizes Spatial Data? ÊRaster and Vector data Model - Spaghetti & Topologic Vector Data Model Representing Surfaces – DEM, TIN, Contour (isolines) Lines File Formats for Raster and Vector data models Ê Spatial Database Management? Ê Querying Data & Indexing of Spatial Data } Sources of Geographic Data 19

Data Mining and Data Warehousing } Motivation } Evolution of Database Technology – overview } Why Data Mining? — Potential Applications } What Is Data Mining? Data Mining: A KDD Process } Data Mining: On What Kind of Data? } What is a Data Warehouse? Ê Data Warehouse vs. other systems, OLTP vs. OLAP Ê Conceptual Modeling of Data Warehouses Ê Defining a Snowflake Schema in Data Mining Query Language DMQL Ê Multi-Tiered Architecture - Approaches to Building OLAP Server Ê Indexing OLAP Data: Bitmap Index Ê Data Warehouse Back-End Tools and Utilities Ê From OLAP to On Line Analytical Mining OLAM, An OLAM Architecture } } Data Mining Functionalities Are All the “Discovered” Patterns Interesting? Market-Basket Data; typical case, Frequent Pairs in SQL, A-Priori Algorithm 20

Database Security and Authorization } Introduction to DB Security } Access Controls } Database Security and the DBA } Discretionary Access Control Ê The privileges at the account/relation levels Ê Granting and revoking of relation privileges Ê Views and Security Ê Propagation of Privileges Ê Role-Based Authorization } Mandatory Access Control Ê Access Control for Multilevel Security Ê Multilevel Relations } Discretionary Access Control vs. Mandatory Access Control } Introduction to Statistical Database Security 21

Database Application Development } Database Programming Ê Embedded SQL ÊDynamic SQL ÊEmbedded SQL in Java Ê Database APIs: Alternative to embedding ÊEmbedded SQL in Java using SQLJ - an example ÊDatabase Stored Procedures ÊSQL Persistent Stored Modules (SQL/PSM) } Client-Server a Modern Database Architectures Ê Client-Server Computing Ê Two-Tier Architecture Ê Multiple-Tier Architecture } Active Database Concepts and Triggers – an introduction Ê Generalized Model for Active DB 22

Mini-Projects, Recapitulation } University Education Information System Ê Object Oriented database design Ê Ê Relational / Object mapping Ê Relational / Object Replication issues } Private University distributed Information System Ê Many relational sites, repository central site Ê Database design issues Ê Issues related to data sharing Ê SQL queries for internet access Ê Website implementing and using the SQL queries 23