This course immerses the learner into the design of systems and makes them ready for any new design problems.

sys-design.tar.gz

basic

This course provides an alternate perspective to system design where we learn about building blocs first and then use them to build bigger, more capable systems.

Copy-System Design-The Missing Pieces

A **database** is an organized collection of data that can be managed and accessed easily. Primarily, databases are divided into two types; **Relational** and **#key# Non-Relational:   Non-relational databases are also called NoSQL databases, stands for Not-Only SQL. #key#** databases.



# Relational databases

**Relational databases** adhere to particular schemas before storing the data. The data stored in relational databases has prior structure. Mostly, this model organizes data into one or more relations (also called tables), with a unique key for each tuple (instance). Each entity of the data consists of instances and attributes where instances are stored in rows and the attributes of each instance are stored in columns. Since each tuple has a unique key, therefore, a tuple in one table can be linked to a tuple in other tables by storing the _primary keys_ in other tables generally known as _foreign keys_. 

A Structure Query Language (SQL) is used for manipulating the database; insertion, deletion, and retrieval of data. 

There are various reasons for the popularity and dominance of relational databases which include simplicity, robustness, flexibility, performance, #key# scalability: Traditional databases are vertically scalable. Vertical scaling has limits.  One might reach a point when more compute, memory, storage, or networking capability could not be added to a single node.  #key#, and compatibility in managing generic data. Relational databases provide the Atomicity, Consistency, Isolation, and Durability [(ACID)](https://www.educative.io/edpresso/what-are-acid-properties-in-a-database) properties to maintain the integrity of the database. ACID is a powerful abstraction that simplifies complex interactions with the data and hides many anomalies (like dirty reads, dirty writes, read skew, lost updates, write skew, phantom reads) behind a simple transaction abort. 

But ACID is like a big hammer (by design so that it is generic enough for all the problems), and if some specific application only needs to deal with a few anomalies, there is a window of opportunity to use a custom solution for higher performance (but added complexity).

Let's discuss ACID in detail:

* **A**tomicity: A transaction is considered an atomic unit. Hence, all the statements within a transaction will successfully execute, or none of them will execute. If a statement fails within a transaction; it should be aborted and rollback. 

* **C**onsistency: At any given time the database should be in a consistent state, and it should remain in a consistent state after every transaction. For example, if multiple users want to view a record from the database it should return a similar result each time.  

* **I**solation: In the case of multiple transactions running concurrently, they should not be affected by each other. The final state of the database should be the same as the transactions were executed sequentially. 

* **D**urability: The system should guarantee that completed transactions will survive permanently in the database even in system failure events. 

There are various database management systems (DBMS) used to define relational database schema along with other operations such as to store, retrieve, and run SQL queries on data.  Some of the popular DBMS are as follows:

* MySQL
* Oracle Database
* Microsoft SQL Server
* IBM DB2
* Postgres
* SQLite

## Why relational database
Relational databases are the default choices of software professionals for structured data storage. There are a number of advantages of these databases. One of the greatest power of the relational database is its abstractions of ACID transactions and related programming semantics that make it very convenient for the end-programmer to use a relational database. Let's revisit some important features of relational databases. 

### Flexibility

In the context of SQL, #key# Data Definition Language (DDL):  DDL is a computer language used to create and modify the structure of database objects in a database. #key# provides us the flexibility to modify the database, including tables, columns, renaming the tables, and other changes. DDL even allows us to modify schema while other queries are happening and the database server is running.   

### Reduced redundancy
One of the biggest advantages of the relational database is that it eliminates data redundancy.  The information related to a specific entity appears in one table while the relevant data to that specific entity appears in the other tables linked through _foreign keys_. This process is called normalization which has the additional benefit of removing an #key# inconsistent dependency: Inconsistent dependencies arise when a user searches for some data in the irrelevant table or the data is missing in the relevant table. For example, to find the customer's address it should be searched in the customer table instead of the product table. #key#.    

### Concurrency

Concurrency is an important factor while designing an enterprise database. In such a case, the data is read and written by many users at the same time. We need to coordinate such interactions to avoid inconsistency in data for example double booking of hotel rooms. Concurrency in a relational database is handled through transactional access to the data. As explained earlier, a transaction is considered an atomic operation so it also works in error handling to either roll back or commit a transaction on successful execution.  

### Integration

The process of aggregating data from multiple sources is a common practice in enterprise applications. A common way to perform this aggregation is to integrate a shared database where multiple applications store their data. This way all the applications can easily access each others' data while the concurrency control measures handle the access of multiple applications. 

### Backup and disaster recovery

Relational databases guarantee the state of data is consistent at any time. The export and import operations make backup and restoration easier. Most commonly cloud-based relational databases perform continuous mirroring to avoid loss of data and make the restoration process easier and quicker. 


## Drawback
### Impedance mismatch

Impedance mismatch is the difference between the relational model and the in-memory data structures. The relation model organizes data into the tabular structure; relations and tuples. SQL operation on this structured data yields relations aligned with relational algebra; however, it has some limitations. In particular, the values in a table take simple values which can't be a structure or a list. The case is different for in-memory, where a complex data structure can be stored. To make the complex structures compatible with the relations, a translation of the data in light of relational algebra would be needed. Hence, the impedance mismatch -- requiring translation between two representations, as denoted in the following figure. 

Understand various types of databases and their use cases in the system design.

Types of Databases [backup]

Relational databases