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

0x05 References

[1] youtube: Database Lesson(1-8)
[2] SQL Window Functions
[3] wiki: Database normalization
[4] How Database Types Evolved to Meet Different Needs

Notes/Abstract 4 Database

原文：https://www.cnblogs.com/math/p/note-db.html