Deck 1

Question 1

Good patterns that Deutsch Bank talked about.

Answer 1

1. Do as much in memory as possible 2. Make the most of SQL syntax available

Question 2

Bad patterns that Deutsch Bank talked about.

Answer 2

1. To many SQL hints in code 2. Over complex SQL 3. PLSQL when SQL will suffice 4. Repeated selecting of the same data

Question 3

Query Optimisation Rule 1.

Answer 3

Rule 1: Cascade of selection

Question 4

2 Query Processing Approaches

Answer 4

1. Heuristic rules 2. Cost estimation

Question 5

Functions of DBMS

Answer 5

Data Storage, Retrieval and Update; Transaction Support; Recovery Services; Authorization Services; Concurrency Control Services; Integrity Services…

Question 6

Properties of transactions - ACID

Answer 6

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

Question 7

ANSI Isolation levels

Answer 7

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.

Question 8

9 Steps of designing a Data Warehouse

Answer 8

CCIC SRC TD

1. Choosing the process
2. Choosing the grain
3. Identifying and conforming the dimension
4. Choosing the measures
5. Storing pre-calculation in the fact table
6. Running out the dimension table
7. Choosing the duration of the database
8. Tracking slowly changing dimensions
9. Deciding the query priorities and the query mode

Question 9

4 Query Processing Steps

Answer 9

1. Query decomposition 2. Query optimisation 3. Code generartion 4. Runtime query execution

Question 10

Database connector

Answer 10

Used for application code running on the web server to access the database server. It allows requests to be sent and response received.