Advanced Database Systems

1. Advanced Database Systems School of Computer Information Engineering SHANDONG FINANCE INSTITUTE

2. Welcome to Advanced Database Systems Lecturers: Pei-yao Nie, Ph.D/Professor in Information Systems e-mail:pynie@sdfi.edu.cn telephone: 261-7619 Office: Room 304 Administration Bld Tutor:

3. This Course will cover • Introduction to Advanced Database Systems • Administrative issues • Introduction to Information System Architectures • Why Study Information System Architectures • Overview of Advanced Database Systems Topics and Modules • What will be covered in this course

4. Course Objectives • To provide an understanding of the issues involved in designing and implementing a large scale information system. • Equip the students with sufficient conceptual and practical knowledge, to be able to recognise the challenges, analyse the appropriateness of the technology and understand the design and implementation complexities.

5. Expected Outcome • Understand the functionality of modern database management systems • Identify and recognize different database implementation models • Distinguish between various information system architectures • Relate different architectures to underlying database technologies • Demonstrate a broad understanding of certain specific application domains, and identify technologies that target those domains • Reason about the suitability of various architectures, given a large information system specification

6. Background Requirements • Assumed background and knowledge • Information Systems Concepts • Relational DB design –> data - ER and process - DFDs • Relational Database Management Systems architecture and main functions: • Query processing, • Transactions, • Concurrency, • Recovery, • Security, • Views, • Views update-ability, • SQL • Information Systems • Relational Database Systems

7. Lectures • 3 Hour Lectures per week • Lecture will be delivered using PowerPoint XP presentation • Lecture Notes will be available online • Download as PPT • Print out will not be produced and distributed • Recommended Reading • Will be identified on last slide of each presentation

8. Tutorials • 1 Hour Tutorial per week, starting in the week 3 • Tutorials will consist of questions, similar to those asked in the mid-term and final exam • Exercises • Review Questions • Tutorial participation is strongly encouraged • Solutions will be made available online – normally each Friday for tutorials in this week

9. Assessment You must pass the final exam to pass this course (i.e. to obtain at least 50%) Mid-semester Exam 40%(week 8) Final Exam 60%

10. Text • Text 1. R.Ramakrishnan, J.Gehrke (2000) Database Management Systems, Second Edition, McGraw-Hill Recommended Additional Readings: • Text 2. Elmasri & Navathe (2000) Fundamentals of Database Systems, 3rd edition, Benjamin/Cummings Publishing Company, Inc. • Text 3. Ozsu, M. T., & Valduriez, P. (1999). Principles of Distributed Database Systems. Prentice Hall.

11. Consultation • Problems with understanding lectures, tutorial exercises • Tutor at the next (any) tutorial session • Continuing Problems • Contact Lecturer during a scheduled consultation time • For time see the web site Consultation page.

12. Notices • All announcements for the course will be put on the course home page, http://pynie.sdfi.edu.cn It contains: • Resources, including lecture notes, tutorials and solutions Students should check this site at least once a week

13. ADS Modules What will be covered in this subject ? • M1 - Fundamental Issues in centralized DBMSs, (MEO) • M2 – Distributed DBs: design, data fragmentation, allocation, query processing, Distributed transaction and concurrency control, (MEO) • M3 - Heterogeneous systems integration, (MEO) • M4 - Data warehousing, (MEO) • M5 – Data mining, (RM) • M6 – Workflows Technology, (RM) • M7 – OO database systems and SQL 3, (RM) • M8 – Spatial Data Structures, (RM)

14. Module 1 Fundamental Issues

15. ADS Introduction Why study information system architectures ? • Basic Terminology • Components of Information Systems • Information System Architectures

16. Information • What is Information ? • Data put into meaningful and useful context and communicated to recipients who use it to make decisions • Quality of Information Information Quality Accuracy Timeliness Relevancy

17. Systems • What is a System ? • A set of components that interact to accomplish some purpose • Nervous system, Legal system, Banking system, Education system …

18. Information Systems • What is an Information System ? • Manages the key resource of business systems • Building blocks Databases Technology Inputs Processes Outputs

19. Architecture • What is Architecture ? • Components of a system • Function of each component • Interrelationships and interactions between the components

20. Preliminary Investigation Requirement Determination Implementation System Testing System Design System Development Classical System Development Life Cycle Information Systems Development

21. Preliminary Investigation Build Prototype Requirement Determination Implementation Present & Evaluate System Design System Testing Proceed with Implementation Revise Prototype System Development Iterative Prototyping Information Systems Development

22. What are we developing ? • Database • DBMS • Tables • Software • System • Communication • Application

23. Databases Technology Inputs Processes Outputs Components • Input • Output • Process • Database • Technology

24. Basic Components • Input & Output • Form Design • Desktop Publishing • Multi-media • Human Computer Interface • Process • Functional Analysis, Data Flow Diagrams • Software Engineering

25. Database Component • A Database is a collection of related facts • A Database Management System (DBMS) is a collection of programs that enables users to create and maintain a database • The database, the DBMS and application programs constitute a Database System

26. Application Programs Interactive Queries DBMS A Database System Database

27. Application Programs Interactive Queries DBMS Database The Database Data Models Relational, Object Oriented, Object Relational, Network, Hierarchical Database Design Entity Relationship diagrams, Functional dependencies, Normalisation Physical Storage Organisation, Hashing, Indexing

28. Application Programs Interactive Queries DBMS Database The DBMS • System Catalog • Query Processing • Interactive queries, Optimisation • Transaction Processing • Failures, Schedules, Recoverability, Serializability • Concurrency Control and Recovery • Locking, Time stamping, Recovery techniques • Security Management • Access Control and Privileges

29. Application Programs Interactive Queries DBMS Database The Application • Interface • View Design • Interactive Querying (SQL) • Human Computer Interaction • Processes • Functional Analysis, Data Flow Diagrams • Software Engineering

30. Application Programs Interactive Queries DBMS Database The Users Database Administrators Database Designers End Users Application Programmers

31. Technology Component • Hardware Devices • CPU, Auxiliary Storage, Input Devices, Printers … • Telecommunications • Transmission media (coaxial or fiber optic cables, radio or satellite transmission, microwave transmission ...) • Topology (Star, Ring, Bus, Meshed …) • Software • Operating Systems (Unix, OS2, Windows ...) • Network Protocols (OSI, TCP/IP, Internet …)

32. Database Systems Technology • Historical Perspective • Data Model Perspective • Architectural Perspective

33. Historical Perspective • Generation 1: File Systems (1960s) • Generation 2: Hierarchical Database Systems (1970s) • Generation 3: CODASYL Database Systems (1975) • Generation 4: Relational Database Systems (1980) (System R) • Generation 5: Beyond business data processing(1990)

34. 2 4 Object-Relational DBMS Relational DBMS Query Object-Oriented DBMS File System No Query 1 3 Simple Data Complex Data Data Model Perspective The DBMS Classification Matrix

35. Users ADS Multiple Single Distributed Centralised Homogeneous Heterogeneous Client-Server Multi-Database Architectural Perspective

36. Users • Number of concurrent users • Single: Restricted to personal use/computers • Multiple: Most applications • Multi-user Systems • Airline Reservation, Banks, Supermarkets … • Basic Principle • Single CPU: Interleaved Concurrency • Multiple CPUs: Parallel Concurrency

37. A A B B Time Time t1 t2 t3 t4 t5 Execution of multi-user processes • Process A and B are interleaved • In DBMSs, A and B can be application programs or interactive queries

38. . . . Number of Users Multiple Number of Sites Distributed Centralised . . . . . . Architectural Perspective

39. Site 4 Site 2 Communications Network Site 3 Site 4 Distributed • A collection of multiple, logically interrelated databases, distributed over a computer network • Beyond Data Independence . . . Distribution Transparency

40. . . . Distributed Homogeneous Heterogeneous Client-Server Multi-Database Architectural Perspective Number of Sites Type of Sites

41. Taxonomy of GIS Solutions • Distributed databases • Federated databases • Multi-databases

42. Global users submitting requests over a global schema which is system maintained Global system has control over local data and processing Local systems are homogeneous Processing can be optimized for global requirements ... ES1 ES2 ESn GCS ... LCS1 LCS2 LCSn ... LIS1 LIS2 LISn Distributed Databases

43. Federated databases • Semi-autonomous, heterogeneous local systems • Participate in the federation through • Import Schemas: Partial Global Schema • Export Schema: Data to be shared by global system

44. Fully autonomous, heterogeneous local systems No global schema Local systems have no concept of entering into a federation MDBMS: A system that manages multi-databases without a global schema Only prototypes exist Need to establish a common interface ... ... ES1 ES2 ESn ... LCS1 LCS2 LCSn ... LIS1 LIS2 LISn Multi-databases

45. Integrating heterogeneous systems • Local databases in heterogeneous environments • Developed independently • Differences in models, representations, languages • Examples: Relational DBMS, Object DBMS, Legacy file management system

46. Interoperability • Interoperability Ability of an application to access multiple distinct systems • Interoperable Systems Beyond database interoperability … • Exchange messages and requests • Receive services and operate as a unit in solving a common goal

47. Interoperable Systems • Features • Use of each other’s functionality • Communication despite incompatible internal detail • Distribution • Supporting Technologies • CORBA (OMG) • COM/OLE (Microsoft)

48. Advanced Applications • Data warehousing • Workflow systems • Special Purpose Databases

49. Data warehousing • Data warehousing is a collection of decision support technologies aimed at enabling the knowledge worker (executive, manager, analyst) to make better and faster decisions • Provide storage, functionality and responsiveness to queries beyond transaction processing systems

50. Workflow systems Workflow technology provides an appropriate platform for consolidating the distributed information resources of an enterprise and providing a global view and understanding of business process models. • A workflow is an automation of a business process • A workflow management system (WFMS) is the software that defines, creates and manages the execution of workflows