Databases Notes

calendar Sep 2, 2018 · 8 min read  · databases sql notes

What is a database

The purpose of a database is to store and retrieve information about records. A database is a mechanism that allows for the reading and updating of information. It’s a set of data. A rock or a piece of paper can be a database, or a CSV file could be considered a low-end database.

As the amount of information that could be stored grew bigger over time, people slowly realized that it wasn’t good enough. The mathematical abstraction of relational algebra — in which different values had certain relationships — was the theoretical foundation for relational databases using Structured Query Language (SQL), and it was developed for this purpose. The most common databases are relational databases, and communicating with a database is done using SQL. Not all databases however can “speak” the same SQL. Dialects differ from vendor to vendor, but there are major overlaps.

A relational database is a data structure that allows you to link information from different tables or different types of data buckets. A relational database will have rows (a.k.a. Record) and columns (a.k.a. Fields).

The CAP theorem

Databases can only perform well in two of the three areas regarded by the CAP theorem (consistency, availability and partition-tolerance). Relational database systems prefer consistency and availability; however, they are not partition-tolerant, which means their scalability is somewhat limited. If consistency is the most critical, then it’s recommended to go with a relational database.

Queries

A query is what is used to read, write, update, or delete data from the database. In a relational database the most common query you’ll run is a SELECT query, which instructs the database to retrieve data and send it to the program that the user is running, the output of which will be put into a more readable format.

SELECT * FROM users WHERE active = 1;

Indexes

The database server will, based on the query, calculate many equivalent ways of searching through the data in order to determine which is the quickest way to do so. When searching through data, a quick way to get a result is through the use of an index.

An index, broadly speaking, makes searching faster. Indexes for a database work like indexes for a book. If you don’t have an index and want to know all the passages that talk about a certain topic you need to read the entire book to find them. An index will list which page numbers to look at for a given topic. It gives you a quick lookup for different values so you don’t have to search through all of the data in the database to find what you are looking for.

CREATE INDEX idx_users_email ON users (email);

Relational Database Management Systems (RDBMS)

Relational Database Management Systems (RDBMS) such as Postgres, MySQL, Neo4j and others are a piece of software (not hardware) that allows you to manage 1 or more databases. The difference being that a database management system is a system designed to allow you to query the data that is in the database and make modifications to the database. Most database management systems can even manage multiple databases in the same instance.

Graph databases

Relational databases are not the only way to store data and query data. Graph databases present the data to the external world differently than relational databases. While a relational database is built primarily on tables as rows (records) and columns (fields) and use SQL for data manipulation, a graph database will make use of a vertex and an edge which can tie a second vertex or the same vertex. A vertex can represent a device, node, a bus stop, an intersection, a person and so on — essentially a central point of interest — whereas an edge is a line that in essence describes a relation. For instance: a person knows person B, device A has a link to device B, two intersections are linked by a road, and so on.

Vertices and edges can have properties or even nested properties. It’s also important to note that storing a graph database inside a relational database is entirely possible (Facebook makes use of TAO — The Objects and Associations API — to build social graphs, a system they built on top of RDBMS and a caching solution to manage their distributed data storage. For more information on TAO see the 2013 USENIX presentation).

The query languages are also different. Most popular ones are:

  • Cypher / OpenCypher — used in database products like SAP HANA Graph, Redis, AgensGraph and Neo4j.
  • Gremlin — an Apache Software Foundation query language and as such can be used by any graph system (Titan, Neo4j, OrientDB and more). It is not bound to a particular vendor.

Gremlin is both an imperative language and a declarative one, while Cypher is declarative. An imperative language will let you specify what will be performed, while the declarative one describes the wished result and lets the query optimizer actually translate it to the operations necessary to retrieve the data.

Document databases

Aside from a graph database we also have document databases, which can be thought of as a graph database without relationships. They don’t have the notion of tables with columns (fields) and rows (records), good for storing sparse data in general. They have no definition of what constitutes a document and may assume data is encoded in some standard JSON or JSON-like format, and also no requirement to adhere to a standard schema. Documents can represent products, customers, PDFs, office files, etc. They are not required to adhere to a standard schema.

Schemas

A schema (schema on write) can be thought of as a blueprint on how the database is constructed. In a relational database a schema is associated with a particular database user and consists of a collection of schema objects such as:

  • Tables (consisting of columns and rows)
  • Views (which are virtual tables)
  • Sequences (used for producing unique values such as IDs)
  • Procedures (functions, snippets of SQL code which can be run or called)
  • Indexes (for allowing faster retrieval of data)

As data enters the database it will need to conform itself to the plan (schema) laid out.

The contrary to the above is a schema on read. It refers to the data to which a plan or schema is applied as it is pulled out of a stored location, rather than as it goes in. Data that had to be applied to a plan or schema when it was going into the database in order to enforce consistency is now at the responsibility of the application to worry about the structure enforcement instead of the database itself. Schema on write is more suited for getting clean and consistent data sets as opposed to schema on read, where you knowingly allow for collections of unclean or inconsistent data.

Data models

Good database design starts with a conceptual data model. It describes at a high level the sorts of data that will be used and how it is related. For an e-commerce website, this might include products, orders, invoices, customer accounts, etc.

Once you have the conceptual model, you can start on the logical data model (aka logic schema). In a relational database, it describes the specific tables and columns, what will be your primary keys (they are unique for each record), what will be your foreign key constraints (a constraint is a link that is created between two tables when the column or columns that hold the primary key value for one table is referenced by the column or columns in another table), and all the other details about data tables and how they are related.

It does not include implementation details that are specific to any one database. This is where you would decide details like whether a customer table will have a column for middle name, or whether to store zip code as a number or an index to a reference table. Once the logical model is created, it can be implemented in a database, or in code (Python, PHP, C, etc.) that is accessing the database. A good logical model is not tied to any specific database and should lend itself to a design that can work on any standard relational database.

The logical model also ties in with how loose or tight a system can be made, revolving around how to query the database. It’s essentially the degree of direct knowledge that one component has of another one. And in this case the implementation details (business rules) can either be implemented at the RDBMS level or be more database-agnostic, independent of specific implementations that might be specific to that system alone, at the application level.

End of Part I

Bibliography:

https://www.reddit.com/r/explainlikeimfive/

https://www.reddit.com/r/sql/

https://en.wikipedia.org/wiki/Relational_database

https://www.facebook.com/notes/facebook-engineering/tao-the-power-of-the-graph/10151525983993920/?pnref=story

https://developer.ibm.com/dwblog/2017/overview-graph-database-query-languages/

https://neo4j.com/blog/why-database-query-language-matters/

Padhy, R. P., Patra, M. R., & Satapathy, S. C. (2011). RDBMS to NoSQL: reviewing some next-generation non-relational database’s. International Journal of Advanced Engineering Science and Technologies, 11(1), 15-30.

Holzschuher, Florian, and René Peinl. “Performance of graph query languages: comparison of cypher, gremlin and native access in Neo4j.” Proceedings of the Joint EDBT/ICDT 2013 Workshops. ACM, 2013.

Moniruzzaman, A. B. M., and Syed Akhter Hossain. “Nosql database: New era of databases for big data analytics-classification, characteristics and comparison.” arXiv preprint arXiv:1307.0191 (2013).