SQL vs. NoSQL database systems

Recall:

• SQL systems: relational, not necessarily using SQL query language

  • well-know data structures are …

  • … normalized (broken down) to partial, atomic entities (tables) → low data redundancy

  • … guarantee data integrity (constraints, referential integrity, ACID transactions)

  • stored as tables, rows, columns, domains, constraints, indexes

  • flexible client queries require joins (cost: computational overhead)

  • difficult and costly to distribute (scale horizontally) across multiple servers (operational overhead)

• NoSQL systems: non-relational, not necessarily without SQL query language

  • more complex data with flexible structures, loose (dynamic, even at runtime) schema, denormalized (redundant) data

  • consistency and integrity burden passed on to the application

  • queries faster, more efficient, but less flexible

Data models

SQL:

1. identify entities, their attributes and relationships

2. normalize, create ERD

3. map to tables, define constraints, indexes, …

Entities:

Holubová et al., p.90

ERD:

Schema:

sqlcreate table address (
  id          serial primary key,
  street      text not null,
  city        text not null,
  zip         text not null
);

create table customer (
  id          serial primary key,
  name        text not null,
  id_address  int not null references address(id)
);

create table "order" (
  id          serial primary key,
  date        date not null,
  id_customer int not null references customer(id)
);

create table invoice (
  id          serial primary key,
  bankAccount text not null,
  paymentDate date,
  id_order    int not null unique references "order"(id),
  id_address  int not null references address(id)
);

create table product (
  id serial primary key,
  name varchar(255) not null
);

create table order_item (
  id_order    int not null references "order"(id),
  seq         int not null,
  primary key (id_order, seq),
  id_product  int not null references product(id),
  quantity    int not null,
  price       numeric(10, 2) not null
);

NoSQL:

• Think about aggregates (logical groups of entities).

• Aggregated data reside on one cluster node.

ERD:

Entities:

Key entities: Customer, Order

• Customer incl. Address

• Order incl. Order Items and Invoice

• Invoice incl. Address

Schema:

Customer (in a collection of):

json{
  "customerID": 1,
  "name": "Jan Novák",
  "address": {
    "city": "Praha",
    "street": "Krásná 5",
    "ZIP": "111 00"
  }
}

Order:

json{
  "orderNumber": 11,
  "date": "2015-04-01",
  "customerID": 1,
  "orderItems": [
    {
      "productID": 111,
      "name": "Vysavač ETA E1490",
      "quantity": 1,
      "price": 1300
    },
    {
      "productID": 112,
      "name": "Sáček k vysavači ETA E1490",
      "quantity": 10,
      "price": 300
    }
  ]
}

Invoice:

json{
  "invoiceID": 2015003,
  "orderNumber": 11,
  "bankAccount": "17-5364640439/0100",
  "paymentDate": "2015-04-16",
  "address": {
    "city": "Brno",
    "street": "Slunečná 7",
    "ZIP": "602 00"
  }
}

• Denormalized data → redundancy (data overhead: a trade-off).

• Redundancy is sometimes required: a customer changes its address (moves).

The world does not live in a 3rd normal form.

Four types of NoSQL databases

• key-value store: values accessed by key (similar to a dictionary)

• document store: stores structured documents (typically [B]JSON)

• column-oriented/columnar databases:
  

• graph databases: data and its relationships represented as a graph

Key-value databases

Each value is stored under a unique key. The database (usually) searches by key, not by the value's internal structure. The value can be of a simple data type, it can be a complex object, or even a blob. It is opaque to the database engine, the database does not interpret its structure, the client does.

• Associative array (map, dictionary) or a hash table.

• Operations: get(key), put(key, value), delete(key), update(key, value).

• Keys can be grouped in namespaces, e.g. customer:1, customer:2, order:11, order:12, etc.

Redis (REmote DIctionary Server)

In RAM, regularly persisted to disk. Simple and fast.

Document databases

Data is stored as (collections of) semi-structured documents (objects) with named fields. Their structure is self-describing (may contain additional metadata) and can vary between documents. Documents can contain nested objects and arrays. Queriess search by field names and values. JSON, XML, BSON, YAML, etc.

Good for hierarchical data (trees), lists, associative arrays, etc.

MongoDB ("humongous")

To store massive amounts of data. Every document has a unique _id field. Queries can be performed on any field, including nested fields (with optional indexes).

Column-oriented databases

Data model:

• rows, indexed by a unique key

• named columns, grouped into column families (efficient simultaneous access)

• each column has a name and a value

• each row can have a different set of columns (column families)

Associative arrays, good for sparse data.

Bigtable, HBase, Cassandra

• Origin: Google Bigtable^🌐, proprietary. ▶ ACM 2008 article

• Open-source:

  • Apache HBase

  • Facebook→Apache Cassandra

Graph databases

Different from the others: nodes represent entities and edges represent relationships between them. Both nodes and edges can have properties; queries can traverse relationships directly rather than constructing them through table joins. Good for complex relationships, networks of various types: social networks, expert systems (recommendations) etc. Good for solving graph problems like shortest path, centrality, clustering, etc.

Why graph databases and not SQL?

• Efficient traversal of the graph (relationships) without expensive joins.

• Flexible schema (nodes and edges can have different properties).

Neo4j ("new for Java")

Neo4j

• the Cypher query language

• read replicas for high availability

• graphs are not sharded