Notes/Abstract 4 Database

Index:
** 0x01 Legacy Databases
** 0x02 Naive Data List
** 0x03 Relational Database
** 0x04 Modern databases
** 0x05 References

0x01 Legacy Databases(ref [4])

Flat-file(Configure) databases
- Simple Data Structure for organizaing small amounts of local data
- Example: /etc/passwd
- Disadvantage:
  - Very limited in the level of complexity they can handle
  - Can not make easy connections between the data represented
Hierarchical databases
- Using parent-child relationships to map data into trees
- Example: FileSystems/DNS/LDAP directories
- Disadvantage:
  - Limit ability to organize most data
  - overhead of accessing data by traversing the hierarchy
Network databases
- Mapping more flexible connections with non-hierarchical links
- Example: IDMS
- Disadvantage:
  - Access data still needed to follow the network paths
  - The parent-child relationship also affected the way that item could connect to one another

0x02 Naive Data List(ref [1])

Redundancy Problems
- Multiple Themes(Business Concept)
- Modification Issues
  - Deletion Anomalies
    - Column Value Missing
  - Update Anomalies
    - Missing Sigle Update
  - Insertion Anomalies
    - Redudancy Value

0x03 Relational Database(ref [1])

Database NormalForm (Solve List Problems, ref[3])
- First NormalForm
  - The value in each column must be atomic
    - if not split it
- Second NormalForm
  - Primary key dependencies only, no partial key dependencies
    - if not split it
- Third NormalForm
  - No Transitive dependencies
    - if not split it with foreign key
SQL
- DDL(Data Define Language)
  - Create/Drop/Alter
    - fieldname-type-nullable-defaultvalue-constrains
- DML(Data Manipulation Language)
  - Insert/Update/Delete/Select
  - SubQuery/Join(Left/Inner/Right)
  - Windows function(ref [2])
    - sum/count with group by
    - sum/avg/... with over(...)
  - null is not a value
    - true>unknown>false
  - CTE, recursive select
- DCL(Data Control Language)
  - Grant/Deny/Rovoke
Key
- Unique Key
  - Candidate Key
  - Composite Key
  - Primary Key
  - Surrogate Key(Constrains Unique)
- Non-Unique Key
  - Foreign key
ER
- Entity
- Relation
  - 1:1
  - 1:N
  - N:M
Concurrency
- Lost Update Problem
  - Dirty Read
  - Inconsistent Read
  - Phantom Read
- Lock
  - Optimistic Lock
    - Lock On Modify
    - Compare And Swap (CAS)
  - Passimisitic Lock
    - Full Lock/Unlock, Rollback if failed
- Lock Granularity
  - Table
  - Row
  - Column
  - Cell
- Transaction(ACID)
  - A: Atom
    - Do All
    - Do Nothing
  - C: Consistance
    - Lock Granularity
    - 2PC
  - I: Isolate
    - Read UnCommitted (Dirty Read)
    - Read Committed (InConsistent Read)
    - Repeatable Read (Phantom Read)
    - Serializable
  - D: Durable
    - Committed changes are permanent
- Cursors
  - Forwardable only Cursor
  - Backwardable/Forwardable Cursor
    - Static Cursor: Update/Insert/Delete invisible
    - KeySet Cursor: Insert invisible
    - Dynamic Cursor: consume more resources
Security
- User-Role Mode
- ACL
Backup/Recovery
- UNDO mode
- REDO mode
- BIN log
Distributed Database
- Master/Slave
- Partition Database
- Partition Table
- Raft
- NewSQL
Database SQL Layer
- SQL Parser
- SQL Query Analyzer
- SQL Optimizer
- SQL Executor

0x04 Modern databases (ref [4])

Key-value databases
- Simple, dictionary-style lookups for basic storage and retrieval
- Example: Redis/memcached/etcd
- Advantage:
  - Store Application Configuration
  - Store Application variables and flags
  - Fast
Document databases
- Storing all of an item's data in flexible, self describing structures
- Example: MongoDB/RethinkDB/Couchbase
- Advantage:
  - Change data properties without alerting existing structures or data
- Disadvantage:
  - You are responsible for maintaining the consistency and structure of your data
Graph databases
- Mapping relationships by focusing on how connections between data and meaningful
- Example: Neo4j/JanusGraph/Dgraph
- Advantage:
  - "relational" in RMDB refers to the ablitity to tie information in different tables together.
  - On the other hand, with graph database, the primary purpose is defining and managing relationships themselves.
Column-family databases
- Databases with flexible columns to bridge the gap between relational and document databases.
- Example: Cassandra/HBase
- Advantage:
  - Great performance for row-based operations and highy scalability
  - All of data and metadata of an entry is accessible with a single row identifier, no computaionally expensive joins are required to find and pull information.
- Disadvantage:
  - If you have highly relational data that requires joins, this is not the right type of database for your application.
NewSQL databases
- Brining modern scalability and performance to the traditional releation pattern
- Example: MemSQL/VoltDB/Spanner/Calvin/CockroachDB/FaunaDB/yugabyteDB/TiDB
- Advantage:
  - Good fit for relational datasets that require scaling beyound what conventional relational databases can offer.
  - Implement the relational abstraction and privide SQL interfaces, transitioning to a NewSQL database is often more straightforward then moving to s NoSQL alternative
- Disadvantage:
  - Keep in mind that although they mostly seek to replicate the conventional relational envirounments, there are differences that may affect your deployments