DB - Database Transactions
A transaction groups a set of related database manipulations
together into a single unit. If any operation within the transaction fails, the
entire transaction fails, and any changes made by the transaction are abandoned
(rolled back). Conversely, if all the operations succeed, then all the changes
are committed together as a group.
DB - Four properties of a transaction : ACID
Atomicity — The
database system guarantees that either all operations within the transaction
succeed or else they all fail.
Consistency — The
transaction must ensure that the database is in a correct, consistent state
at the start and the end of the transaction. No referential
integrity constraints can be broken,
for example.
Isolation — All
changes to the database within a transaction are isolated from all other
queries
and transactions until the transaction is committed.
Durability — When
committed, changes made in a transaction are permanent. The database
system must have some way to recover from crashes and other
problems so that the current
state of the database is never lost.
DB - Normalization and Denormalization
Normalization:
designed to minimize redundancy.
Denormalization: designed to optimize read time by adding
redundant data, commonly used to create
highly scalable systems.
cons:
·
updates and inserts are more expensive.
·
Data may be inconsistent.
·
Data redundancy uses more storage.
pros:
·
Retrieving data is faster since we do fewer
joins.
·
Queries to retrieve can be simpler.
DB – Different types of joins
Inner join:
the result would contain only data where the criteria match.
outer join:
left join, right
join, full outer join
SQL and NoSQL
SQL has predefined schema. For Large organizations, the relationships and tables can be numbered in millions, combine those relationships can suffer major performance issue.
NoSQL databases scale up horizontally, adding more servers to deal with larger loads. NoSQL is much more flexible, not using schemas defined beforehand. This allows users to add information and make changes at any time without disrupting the system or needing to transfer a large amount of data.