Relational Databases

How do you store vast collections of constantly changing data on disks (non-volatile storage) that enable different users to retrieve, edit, and add data simultaneously.

Answer is with a database, a collection of data organized in a way that allows for efficient storage, retrieval, and manipulation of data.

• A database management system (DBMS) is used to manage databases.

Motivation to use a database:

• efficiently store, retrieve, and manipulate large collections of constantly changing data.
• enable different users to retrieve, edit, and add data simultaneously.
• ensure data integrity and prevent data inconsistencies.

Providers include:

• open source: MySQl, PostgreSQL, Sqlite
• commercial: Microsoft SQL Server, Oracle Database
• Cloud Databases: AWS RDS, Azure SQL Database, Google Cloud SQ

Models

Relational database avoids duplicate information and data inconsistencies. Most used and created since the 1960s.

The relational data model looks like:

• table organizes data
• rows represent records, which is a data entry
• columns, also known as fields, define specific properties that each record has. E.g., BookID, Data Type, Constraints

A table schema is the organization of a database table by its fields and restrictions that it enforces. This means that it won't accept a row into a table if it violates the table's schema.

• Columns, or fields, have a specific data type and constraints. E.g., non-empty, unique, etc.
• Pros: fixed schema ensures data is valid, when working with data of homogeneous structure.
• Cons: when the characteristics of the data vary too much, fitting the data to a fixed schema is troublesome

Entity-Relationship Diagrams

ER Diagrams are graphical representations of the logical structure of a database. They use various symbols to represent entities, attributes, and the relationships between them. Database designers use these to visualize the structure of the database and to understand how data will be stored, connected, and accessed.

Example of an ERD for a BOOK table. 1. entity: an entity is the table. BOOK is the entity and is represented as a rectangle. 2. attributes: properties or characteristics of an entity. They are represented as ovals connected to their respective entity by a straight line. 3. data type: beside each attribute, the data type is mentioned to indicate what type of data the attribute will hold (e.g., int for integer, string for textual data).

Relationships

Relationships address duplicated information. For example, if you store a table of invoices, you'll have duplicated customer information when they make multiple orders.

To avoid duplicated data, the relational models splits related information into different tables. You can refer to the same customer as part of many orders.

Create complex relationships between separate sets of data by using primary and foreign keys.

• Each table has a unique identification field called the ID
• The ID field of a table is known as the primary key
• A foreign key is a field that records references to the "IDs" of other rows.

In an ERD, model relationships of various types: one-to-one, one-to-many, many-to-one, and many-to-many.

• One-to-One (1:1) relationship: a driver has one car, and the car is associated with only one driver
• One-to-Many (1:N) relationship: a driver can have multiple cars, but each car is associated with only one driver.
• Many-to-many (M:N) relationship: a driver can drive multiple cars, and each car can be driven by multiple drivers.

Normalization

Normalization is the process of organizing a database to reduce redundancy and ensure data integrity. A normalized database minimizes duplicated data in various tables, which lead to easier maintenance and reduced storage costs.

First Normal Form (1NF)

• each table should represent a single entity
• each column should contain atomic (indivisible) values.
• each row should be uniquely identifiable (no duplicates)
• no repeating groups or arrays

Second Normal Form (2NF):

• all rules of 1NF
• every non-key attribute should be functionally dependent on the entire primary key
• remove partial dependencies of any column on the composite primary key.

Third Normal Form (3NF):

• all rules of 2NF
• no transitive dependencies of non-key attributes

Denormalization

Introduce redundancy into a table by incorporating data from related table. This is used to improve read performance.

• trade off between data duplication and faster queries

Schema Migration

Database structure (or schema) often needs to change

You can create a schema migration script which automates schema upgrades and data transformation. This is normally reversible, which allows for easy rollback.

Tools exist to handle these various migrations

• these ensure consistent database versions across development environments
• facilitates easy rollbacks to specific working versions
• essential for large projects with frequent database changes.

SQL

Structured Query Language is a query language that works with almoste very relational DBMS.

Joins a JOIN operation combines rows from two or more tables based on a related column

• It allows you to fetch related data from different tables to create a comprehensive dataset.
• Pros: flexible querying, fetches data from multiple tables in a single query
• Cons: expensive computation, unfeasible for large tables, may consider every combination of rows from the tables (cross join).

View VIEW creates a virtual table based on the result-set of an SQL statement

• contains rows and columns but doesn't store data useful for providing a specific view of the data, hiding complexity, or securing data access by limiting the data users can access.

A stored PROCEDURE is a pre-written SQL query or a set of queries saved in the database.

• Executed whenever without rewriting the entire SQL
• Benefits: efficient because it reduces the amount of redundant SQL, secure because it can limit user access to underlying data, and increases integrity because it enforces data consistency across operations.

Object-Relational Mapping

ORM is a programming technique that lets you interact with your database, like you would with SQL, but you use high-level objected-oreinted APIs instead. This allows developers to use their programming language of choice to communicate with a relational databse

It converts data between incompatible system, e.g., objected-oriented languages and relational databases.

Some common ORMs include:

• Java: Hibernate, EclipseLink, MyBatis
• Python: SQLAlchemy, Django ORM, Peewee
• JS: Sequelize, TypeORM, Prisma

Indexing

For a primary key to be useful, we must quickly retrieve a data entry when given its ID value. DBMS builds an auxiliary index, that maps row IDs to their respective addresses in memory.

• an index is essentially a self-balancing binary search tree where each row corresponds to a node in the tree

Normally, a DBMS creates an index for each primary key in the database as well as fields that have the uniqueness constraint. If we need to find records by searching other fields, we can instruct the DBMS to create additional indexes for these fields as well.

Indexes improve read operations but can slow down write operations. Every index consumes additional storage space. Index maintenance can increase the time it takes to perform INSERT, UPDATE, and DELETE operations.

• We must balance read and write efficiencies

Requirements

Transaction need (OLTP). Here, data is frequently updated/added. It's designed for high transaction rates and real-time data processing.

Reporting need (OLAP). Used mainly for reading. Data may not be up-to-date but its stable for queries. Often used for business analytics.

Computation need: Facilitates heavy calculations involving complex algorithms and mathematical computations.

Transactions ensure sequences of operations execute as a single logical unit. E.g., in a bank transfer, the deduction and addition must execute together. Problems arise if a query happens or system crashes between these operations

ACID Transactions ensure reliable database processing

• atomicity: all transaction succeed, or none do
• consistency: database remains consistent post-transaction
• isolation: concurrent transactions execute if sequential
• durability: committed transactions withstand system failures.

Security

SQL Injection: code injection technique where malicious SQL code is inserted into a query. Prevent this by using parameterized queries or prepared statements. Use ORM frameworks to handle this.

Access control: limit who can access the database, or limit what they can do

Data encryption: protect data by encrypting it at rest and in transit.