Good patterns that Deutsch Bank talked about.
- Do as much in memory as possible 2. Make the most of SQL syntax available
Bad patterns that Deutsch Bank talked about.
- To many SQL hints in code 2. Over complex SQL 3. PLSQL when SQL will suffice 4. Repeated selecting of the same data
Query Optimisation Rule 1.
Rule 1: Cascade of selection
2 Query Processing Approaches
- Heuristic rules 2. Cost estimation
Functions of DBMS
Data Storage, Retrieval and Update; Transaction Support; Recovery Services; Authorization Services; Concurrency Control Services; Integrity Services…
Properties of transactions - ACID
Atomicity - all or nothing of transaction is completed - can’t break down into a smaller unit - recovery system
Consistency - transform database from one consistent state to another
Isolation - partial effects of incomplete transactions not visible to other transactions - shouldn’t affect each other, not even visible
Durability - effects of committed transactions are permanent - once committed then permanent - recovery system
ANSI Isolation levels
Serialisable - keeps read and write lock
Repeatable reads - release read and write at the end of the transaction
Read committed - release write lock at the end of the transaction and release the read lock right after the SELECT operation
Read uncommitted - dirty reads allowed, no-yet-commited changes can be seen by other T.
9 Steps of designing a Data Warehouse
CCIC SRC TD
- Choosing the process
- Choosing the grain
- Identifying and conforming the dimension
- Choosing the measures
- Storing pre-calculation in the fact table
- Running out the dimension table
- Choosing the duration of the database
- Tracking slowly changing dimensions
- Deciding the query priorities and the query mode
4 Query Processing Steps
- Query decomposition 2. Query optimisation 3. Code generartion 4. Runtime query execution
Database connector
Used for application code running on the web server to access the database server. It allows requests to be sent and response received.
