IS698: Database Management

1. IS698: Database Management Min Song IS NJIT

2. Overview • Data Models, Schemas & Instances • Three-Schema Architecture and Data Independence • Database Languages & Interfaces • Database System Environment • Centralized DB & Client/Server Architecture • DBMS Classifications

3. Data Models

4. What is a Data Model? • An abstract representation • Hides low level details • Describes DB structure • Data types • Relationships • Constraints • + (current trend) • Dynamic aspects: E.g. user-defined operations

5. Data Model: Categories (1/3) • High level: from the user perspective • For end user/customer • Entity, attribute, relationships • E-R diagrams

6. Data Model: Categories (2/3) • Low level: how data stored in computer • For computer specialists/DBMS implementer • Physical data model • Record formats, record orderings, access paths

7. Data Model: Categories (3/3) • Representational (implementation) model • Meant for end user • With some of the data organizational information • Can be implemented directly in a computer • E.g. Relational data model • Out of fashion • Network data model • Hierarchical data model

8. Database Schemas

9. What is DB Schema? • A descriptionof the database (meta-data) • Covers only some aspects • E.g. name of record type, data items, some constraints • Not changed frequently • Specify when a new database defined • Meta-data = DBMS Catalog • Schema constructs: Objects in schema • Schema diagram • Convention to display schema

10. Example: University Database DB Schema Schema Constructs A DB Instance

11. Database Instances • Data in the database at a particular time • Instance • = snapshot • = database state • ~= current set of occurrences • DB_state(i) •  insert/delete/update •  DB_state(i+1)

12. Schema, Instances & DB States • Define DB schema  DB_state(0) = empty state • DB_state(0)  populate  initial state • DB_state(i) is avalid state for all I • Satisfies the structure and the constraints defined in the schema • DB Schema = intention of the database • DB state = extension of the Schema

13. Three-Schema Architecture

14. What is Three-Schema Architecture? • Important characteristics of a database • Insulation of programs and data • Support for multiple user views • Use of catalogs for schemas • Three-Schema architecture implements above characteristics

15. Visualization of Three-Schema Architecture

16. What is in each Schema? • Internal Schema • Physical storage structure • data storage details, access path • Conceptual Schema • Structure of the entire database • Entities, data types, relationships, user operations, constraints • External Schema • Part of the database interested to a particular user group

17. Mapping between Database Schemas • Process of transforming requests among different database level (schemas) • Is a overhead in compilation & execution of queries • Time consuming • Only some of the DBMSs implements Three-schema architecture

18. Data Independence

19. What is Data Independence? • Ability to change one level schema without affecting other levels of schema • Three-schema architecture implements concept of data independence • Only the mapping need to be changed • Logical Independence • Capacity to change conceptual schema without affecting external view (application programs) • Physical Independence • Ability to change internal schema without changing the conceptual view

20. Logical Data Independence CONCEPTUAL VIEW 1 CONCEPTUAL VIEW 1 EXTERNAL VIEW

21. Database Languages

22. Purpose of a Database Language • DDL (Data Definition Languages) • Conceptual schema • Internal schemas (when schema separation is not clear) • External schema (in most DBMSs) • SDL (Store Definition Languages) • Internal schema • VDL (View Definition Languages) • External view • DML (Data Manipulation Languages) • To manipulate the populated data • Modern DB  All in one  (E.g. SQL) • Separate SDL

23. Data Manipulation Languages • High-level (nonprocedural) • Set-at-a-time/set oriented • Declarative • Complex DB operations • Interactive/embedded in programming languages • Low-level (procedural) • Record-at-a-time • Embedded in general purpose programming languages

24. Where the DML is Used? • When DML is embedded in program • Programming Language  host language • DML  data sub language • For interactive queries • DML  query language • General users • User friendly interfaces

25. DBMS Interfaces • Menu-Based Web Interfaces • Forms-Based Interfaces • Graphical User Interfaces • Natural Language Interfaces • Interfaces for parametric Users • Interfaces for DBA

26. The Database System Environment

27. Components of DBMS:

28. Relational System Architecture • Databases are BIG pieces of software. Typically very hard to modularize. Lots of system design decisions at the macro and micro scale. Here we focus on macro design. • Disk management choices: • file per relation • big file in file system • raw device • Process Model: • process per user • server • multi-server

29. Relational System Architecture: Continue • Basic modules: • parser • query rewrite • optimizer • query executor • access methods • buffer manager • lock manager • log/recovery manager

30. Relational System Architecture: Continue • Query Rewriter • Flattens views • may change query semantics (constraints, protection, etc.) • Optimizer • large space of equivalent relational plans • pick one that’s going to be "optimal" • produces either an interpretable plan tree, or compiled code • Executor • modules to perform relation operations like joins, sorts, aggregations, etc. • calls Access Methods for operations on base and temporary relations • Access Methods • uniform relational interface (open, get next), a la INGRES AMI, System R's RSS • multiple implementations: heap, B-tree, extensible hashing

31. Relational System Architecture: Continue • Buffer Manager • Intelligent user-level disk cache • must interact with transaction manager & lock manager • Lock Manager • must efficiently support lock table • System R architecture influential: • physical and logical locks treated uniformly • multiple granularity of locks • set intent locks at high levels • we will study this in more detail later (Gray) • deadlock handling: detection

32. Relational System Architecture: Continue • Log/Recovery Manager • "before/after" log on values • checkpoint/restore facility for quick recovery • Redo/Undo on restore • Support soft crashes off disk, hard crashes off tape. • System R’s shadowing is too slow. Use Write-Ahead Logging! (WAL) Hard to get right!

33. Software Components of a DBMS • DBMS Component Modules • Database System Utilities • Tools • Application Environments • Communication Facilities

34. DBMS Component Modules (1/2)

35. DBMS Component Modules (2/2) • Disk access control • Stored Data Manager • OS • Buffer Manager Module • Compilers • DDL Compiler • Query Compiler • DML Compiler • Precompiler/Host language compiler • Handling DB access at runtime • Runtime database processor

36. Database System Utilities • Loading: load/transfer using files • Conversion tools • Backup: copy/dump databases • Failure recovery • Incremental backups • File reorganization: to improve performance • Performance/access Monitoring • Sorting/data compression/interfacing to network

37. Tools • CASE: Computer Aided Software Engineering Tools • DB designers/DBA/users • Design phase • Information repository • Data dictionary (data repository) systems • Catalog/design decisions/usage standards/application program descriptions/user information

38. Application Development Environments • E.g. PowerBuilder/JBuilder • Supports • Development of database applications • Database design • GUI development • Querying and updating

39. Communication Software • Provide access for remote users • DB/DC package • Supports for distributed DBMSs

40. DBMS Architectures

41. Centralized DBMSs

42. Basic Client/Server Architecture • Applied for network with • PCs, workstations, printers, file servers, database servers, web servers, etc • Specialized server with specific functionalities • Many clients access specialized server • Client machine provide interfaces • Utilizes processing power of the clients

43. Client/Server Architecture for DBMSs

44. Two-Tier Architecture • Relational DataBase Management Systems (RDBMS) • QueryServer/TransactionServer/SQLServer • Open DataBase Connectivity (ODBC) • Application Program Interface (API) • Client/server approach in OO-DBMS: Data Server

45. Three-Tier Architecture • User interface • Application rules • Middle tier • Application server/Web server • Data access

46. DBMS Classifications

47. DBMS Classification Based on Data Model • Relational data model • Object data model • Object-relational data model • Extended-relational model • XML Model • Hierarchical data model • Network data model

48. DBMS Classification Based on Number of Users • Single-user systems • Multi-user systems

49. DBMS Classification Based on Number of Sites • Centralized systems • Distributed DBMSs (DDBMS) • Homogeneous DDBMS • Heterogeneous DBMS (Federated DBMS/multi-database systems)

50. DBMS Classification – More Criteria • Cost • Type of access path • Purpose • General purpose • Special purpose • E.g. Airline system (OLTP – Online transaction processing systems)