## 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