Skip to content

DB terminology summary

Jump to bottom
abk edited this page Nov 26, 2019 · 1 revision

Database terminology & Essential information.

Relational (SQL) database * Structured data (schema) * Data is normalized and spread across multiple tables. * SQL query lang * ACID (Atomicity, Consistency, Isolation, Durablility) * Vertical scaling * Two types of Relational databases * OLTP (insert, update etc) * OLAP (lookup, analytics, running queries etc). * Oracle, SQL server, MySQL, Postgre SQL

NoSQL database * Semi-structured data (schema less) * Horizontal scalable * Flat table structure * API queries * High transaction rates * Four types : * column : Stores by column (really fast for querying) * key-value : Imagine JSON * document : * graph : Social media DB, relationships. * MongoDB, CouchDB, HBase, Cassandra * No concept of join of tables.

AWS managed database services.

Amazon RDS DyanamoDB Amazon Elsaticache Neptune Hbase on EMR Document key-value Graph Column

Amazon Redshift : OLAP , datawarehouse.

DB Fundamentals

RDBMS Positives

  • Normalization - why it was developed
  • Minimize the duplication of data on storage
  • Every table has predefined schema - rigid data structure.
  • Can have multiple tables
    • Table can have single primary key or multiple composite keys
    • Schema also stores relationship between different tables.
  • Query Language - SQL
  • If the data has relationships, and don’t change - relational database

Negatives

  • Struggle with unrelated data
  • 


NON-SQL Comes with 4 different types of engines.

  • Key-value databases (DynamoDB) * 0001: Roffle * 0002: Penny * 0003: Winkie

    • Great to store unstructured data
    • Great to use inmemory databases
    • Schema is non-existing.
    • High velocity read or write operations.
    • Scale really well
    • DynamoDB - is mix of key-value and documentDB.


  • Document DB (MongoDB / DocumentDB)

    • Data is stored as structured key and value pairs called documents.
    • Operations on documents are highly performant
    • Patient records, Content management, User profiles etc.
    • Varying set of information to be stored.
    • MongoDB or DocumentDB

  • Column based DB (Redshift)

    • Similar to row based database.
    • Data is stored by columns instead of row.
    • Great for data warehousing or analytics
    • RedShift

  • Graph based (Neo4j)

    • Designed for dynamic relationships
    • Social media sites.
    • Ideal for human related data and relationship.
Clone this wiki locally