Lecture 6 Flashcards
CASE example
SELECT *
CASE
.WHEN condition THEN text
.WHEN condition THEN text
.ELSE text
END
AS QuantityText
from OrderDetails;
CASE can also be used to…
Update
Transactions
- Make state transitions independent from atomic operations.
Ending Transitions
- You commit or rollback the transaction
- The server shuts down, this rolls back the transaction when the server is restarted.
- You use a schema statement, your transition is committed and a new one started.
- You use another start transaction, your transition is committed and a new one started.
- A deadlock is detected.
Transactions and ACID - A
Atomicity
- Transactions are often composed of multiple statements.
- Each transaction is treated as a single unit, which either succeeds completely, or fails completely.
Transactions and ACID - C
Consistency
- A transaction can only bring the database from one valid state to another valid state.
(FK, Unique Constraints, Cascades, Triggers)
Transactions and ACID - I
Isolation
- Concurrent transactions should not interfere with each other.
- Avoid dirty read/write
- Avoid non repeatable read
- Avoid phantom read
Race condition (definition)
- Undesirable situation that occurs when a device or system attempts to perform two operations at the same time, but operations must be done in a proper sequence.
Multi-user transactions - Locks intro
We use locks to prevent access to data undergoing transactions.
- Write/ Read lock
Locking can also be:
- Table, page or row locks
OLTP
Online Transaction Processing
- High volume of transactions
- Fast processing
- Normalised data
- Many tables
OLAP
Online Analytical Processing
- High volume of data
- Slow queries
- Denormalised data
- Fewer tables
Database files
A database file contains descriptions of how input data is to be presented to a program from internal storage and how output data is to be presented to internal storage from a program.
Pages
- Every page contains a header of metadata about the pages contents.
Slotted ot log structured pages
NSM
N-ARY Storage Model
- DBMS stores all attributes for a single row contiguously in a page.
NSM Advantages and Disadvantages
A:
- Fast inserts, updates, and deletes.
- Good for queries that need the entire tuple.
Drawbacks:
- Not good for scanning large portions of the table and/or a subset of the attributes.
DSM
Decomposition Storage Model
- The DBMS stores the values of a single attribute for all tuples contiguously in a page.
- Also known as a column store.
DSM Advantages and disadvantages
A:
- Reduces the amount wasted I/O because the DBMS only reads the data that it needs.
- Better query processing and data compression.
D:
- Slow for point queries, inserts, updates, and deletes because of tuple splitting/ stitching.
NO SQL - Flexibility
Business needs changes over time.
- We can add data without altering the schema.
- The application is required to handle old vs new data
- If you have all control of the data and application this is not an issue.
Aggregated-Oriented Databases
- When modelling things we often combine concepts together into natural aggregates.
Schemaless Transaction
But document stores do not have this concept since we cannot isolate based on a schema.
SQL Guidelines
- Strict Schema
- Relations
- Distribution of data over tables
- Difficult horizontal scaling
- Vertical scaling is easier than horizontal scaling
NO SQL Guidelines
- No schema limitations
- No relations
- Aggregated data
- Handles horizontal and vertical scaling well
- High data volume
- Large data storage
MongoDB
- Document store
- Stores them as BSON (Binary JSON)
- Querying power of SQL
- Indexing
- Replication
- Load balancing
Redis
- Key-value store
- In memory database
- Simple to use, supports replication
- Open source
Uses of Redis:
Gaming, queues, messaging, caching, streaming …
InfluxDB
- Column Store
- Specific for time series
- Automatic timing
- Optimised for inserting ordered data
- No optimised for updates/deletes
- Streaming
UApplications
