Architecture¶
Definition Database¶
- Collection of related data
- Represents aspects of real world (Universe of Discourse)
- Logically coherent
- Provided for intended group of users/applications
- Logical interface
- Controlled redundancy
- Data consistency + integrity constraints
- Integration of data
- Effective and secure data sharing
- Backup + Recovery
Definition DBMS¶
- Collection of programs to maintain database
- Definition data and structure
- Physical construction
- Manipulation
- Sharing/Protecting
- Persistence/Recovery
File Systems as Database¶
- File system as Database?
- No, file system is physical interface
- Advantage:
- Fast + easy access
- Disadvantage:
- Uncontrolled redundancy
- Inconsistent data
- Limited data sharing and access rights
- Poor enforcement of standards
- Excessive data and access path maintenance
Characterstics of DB¶
- Control redundancy (Data stored once, redundancy only for speed)
- Problems uncontrolled redundancy: Difficulties in consistently updating data, waste of storage
- Well-structured (Catalog with meta-data, define structure of data)
- Efficient manipulation of data (Physical tuning, indexes, optimized query plans)
- Isolation between application and data (data abstraction, application on conceptual model, strictly typed)
- Support multiple views (virtual data, different perspectives)
- Sharing of data / Support atomic multi-user transactions (concurrency control, multiple users, atomic + isolated transactions)
- Persistence of data / Disaster recovery (no data loss, persistent all the time)
Data Models¶
- Data model:
- Describe data objects, operations an their effects
- DDL: Data definition language
- DML: Data manipulation language
- Conceptual model:
- ER / Semantic data / UML
- Logical model:
- Relational data model / Network model / Object model
- Physical model:
- How data is stored, access paths
DBMS Meta-data¶
- Schema:
- Describe structure of stored data, intensional database
- tables, attributes with types, views, integrity constraints, relationships
- Should be stable
- System Catalog:
- Collection of schemas
- Cluster:
- Collection of catalogs (maximal query scope)
- Instance:
- Extensional database (actually stored data)
ANSI/SPARC 1975 Architecture¶
- Internal schema
- Physical layer, physical model
- describe physical storage + access path
- Conceptual schema
- Logical layer, logical model
- describe structure of DB
- No physical details
- External schema
- Presentation layer, conceptual model
- Describe part of DB structure for user group as views
- Goal:
- Change schema of one level without changing others
- Physical data independence
- Supported by declarative query language, physical tuning important maintenance task
- Logical data independence
- Change conceptual schema without touching external schema)
DBMS System Structure¶
- —Image—
- Query Processor
- Query optimization
- Query planning
- Storage manager
- access path
- Physical sets / pages / buffers
- Accesses disk through OS
Components¶
- Storage manager:
- Provide interface between stored data and database
- Responsibilties: Interaction with file manager, efficient storing, retrieving, updating
- Tasks: Storage access, file organization, indexing, hashing
- Query Processor:
- Parse queries, optimize query plans, evaluate queries
- Cost estimation (cost differences between different plans)
- Use statistical information
- Transaction manager
- Transaction: Collection of operations that perform a single logical function
- Task: Ensure database remains in correct state, concurrency control
DBMS Quality (TPPC)¶
- Metrics:
- Transactions per seconds
- Transaction elapse time
- $/tps)
- TPPC: Standard data sets, schemas, queries
- TPC-D:
- Decision support (long complex read-only queries, infrequent updates, until 1999)
- TPC-C:
- OLTP
- Regular business
- Update intensive
- Short transactions
- New-order transactions
- Payment transaction
- Order-status transactions
- Delivery transaction
- Stock-level transaction
- TPC-E: New OLTP, brokerage film
- TPC-H: Ad-hoc, decision support