An Introduction to Databases

What are Databases?

Relational Databases

Relational databases use the relational paradigm to organise data into tables with predetermined relationships. They manipulate and retrieve data using Structured Query Language (SQL). Oracle, MySQL, Microsoft SQL Server, and PostgreSQL are examples of popular relational database management systems (RDBMS).Here's a more detailed explanation of relational databases:

• Structure: Relational databases organise data into tables, each of which represents an item or notion. Each table is made up of rows (sometimes called records or tuples) and columns (also called fields or attributes). Rows represent individual data instances, whereas columns specify the data's qualities or properties.
• Key Concept: The idea of keys is used in relational databases to construct relationships between tables. A primary key identifies each row in a table, maintaining its uniqueness and allowing it to be related to other tables. Foreign keys link tables together by referencing the main key of another table.
• Data Integrity: Relational databases enforce data integrity by enforcing rules on table connections. They provide referential integrity by requiring that foreign keys match existing primary keys in related tables. Furthermore, relational databases enable a variety of constraints, such as unique constraints, not-null constraints, and check constraints, to ensure data authenticity and consistency.
• Query Language: Structured Query Language (SQL) is the standard language for communicating with relational databases. SQL gives users the ability to create, change, and retrieve data from a database. It has robust querying capabilities, allowing users to filter, sort, aggregate, and connect data from many tables.
• ACID properties: Relational databases follow the ACID properties: atomicity, consistency, isolation, and durability. These features assure the dependability of database transactions, the integrity of data, and the persistence of committed data.
• Scalability and Performance: For structured data, relational databases provide good scalability and performance. They provide indexing, which speeds up data retrieval and enables query execution plans to be optimised. Relational databases can handle massive volumes of data and sophisticated queries efficiently with proper indexing and database design.

Advantages of Relational Database

Overall, the benefits of relational databases, such as data integrity, structured organisation, flexible querying, relationship management, scalability, and performance, make them a dependable and powerful alternative for storing and managing structured data across a wide range of applications and sectors.

Disadvantages of Relational Database

While relational databases have many advantages, they also have some limitations and drawbacks that should be considered. Here are some of the major drawbacks of relational databases:

• Complexity: Relational databases can be difficult to build and maintain, particularly when working with complicated relationships and big information. Table definition, relationship establishment, and data integrity require careful design and experience. As the database grows in complexity, it becomes increasingly difficult to administer and optimise performance.
• Fixed Schema: Relational databases have a predefined schema, which means that the structure of the database is determined upfront, and any modifications to the schema can be time-consuming and disruptive. Adding or updating columns, tables, or relationships necessitates changes to the database schema, which may have an impact on current data and applications.
• Scalability: While relational databases can manage large volumes of data, they may have scalability issues. Vertically extending a relational database by adding more powerful hardware has constraints, while horizontally scaling across numerous servers can create complications such as data consistency and managing distributed transactions.
• Performance: When dealing with sophisticated queries, especially those involving huge datasets, relational databases may experience performance challenges. Queries using several joins or aggregations can consume a lot of resources and slow down performance. Furthermore, the necessity to maintain data integrity through constraints and relationships might have an influence on write performance.
• Cost: Implementing and maintaining relational databases, particularly enterprise-grade solutions, can be expensive. They frequently necessitate licences, specialised gear, and experienced database administrators. Small firms or initiatives with restricted budgets may face cost problems.

Despite these disadvantages, relational databases continue to be widely used and remain a solid choice for many applications. However, it is critical to examine the project's individual requirements and consider alternate database solutions when the disadvantages of relational databases outweigh their benefits.

Non-Relational Databases

Non-relational databases, often known as NoSQL (Not only SQL) databases, are a type of database system that offers other data types and storage methodologies than standard relational databases. Here's an overview to non-relational databases:

• Flexibility: Non-relational databases provide flexible data models capable of handling unstructured, semi-structured, and structured data. They are intended to manage a wide range of data formats, including key-value pairs, documents, graphs, and columnar data. This adaptability enables efficient storing and retrieval of data with various structures.
• Scalability and Performance: Non-relational databases excel at scalability and performance, especially in large-scale and dispersed environments. They are intended to scale horizontally by adding more servers to the database cluster, allowing for high availability, fault tolerance, and high data volumes and throughput.
• High Availability and Fault Tolerance: Non-relational databases are frequently created with replication and distribution mechanisms built in. Data can be replicated across numerous nodes, resulting in increased availability and fault tolerance. This ensures that even if individual nodes or servers fail, the database remains accessible.

MongoDB (document database), Apache Cassandra (wide-column store), Neo4j (graph database), Redis (key-value store), and Amazon DynamoDB (managed NoSQL database service) are some popular non-relational databases.

Key-Value Databases

Key-value databases are a type of non-relational database that stores data as a collection of key-value pairs. Each data entry consists of a unique key and an associated value. Here's an overview of key-value databases:

• Simple Data Model: The data model of key-value databases is simple and straightforward. Each key-value pair is self-contained and independent, with no preset structure or interactions between pairs. The key serves as the value's unique identifier.
• High Performance: Key-value databases are designed to be fast to read and write. They excel in data retrieval by directly accessing values via the associated keys. As a result, they are useful for use cases requiring high-speed data access, such as caching, session management, and real-time analytics.
• Scalability: Key-value databases are meant to scale horizontally, allowing for distributed storage across numerous servers or nodes. By duplicating data between nodes, they enable high availability and fault tolerance. This allows for seamless scaling to handle massive volumes of data and concurrent operations.
• Flexibility and Dynamic Schema: Key-value databases offer flexibility in terms of data structure. They are schema-less, allowing for dynamic changes to the data model without requiring updates to the entire database. This flexibility makes it easy to accommodate varying data types and structures, making them suitable for scenarios where data is unstructured or changes frequently.
• Caching: Key-value databases are often used as caching layers to improve the performance of applications. By storing frequently accessed data in memory, they can significantly reduce the load on backend systems and speed up response times.

Document Oriented Databases

Document-oriented databases, also known as document stores or document databases, are a type of non-relational database that stores and manages data in the form of semi-structured documents. Here's an overview of document-oriented databases:

• Document Model: Data in document-oriented databases is stored as self-contained documents, generally in JSON or XML formats. Each document represents a single entity or object, and its structure can vary. For logical grouping, documents are often organised into collections or buckets.
• Flexibility: Document-oriented databases provide a flexible schema design. They do not have a set structure or a specified schema, unlike relational databases. Each document in a collection can have its own distinct structure, allowing data models to evolve over time without affecting existing documents.
• Rich Querying: Document-oriented databases offer powerful querying capabilities, frequently supporting a form of the SQL query language or a database-specific query language. These query languages enable querying and filtering depending on the attributes of the document.
• Handling Semi-Structured Data: Document-oriented databases are ideal for storing and managing semi-structured data, the structure of which varies between documents. As a result, they are appropriate for scenarios involving content management systems, product catalogues, user profiles, and applications dealing with data that does not cleanly fit into traditional relational schemas.

Columnar databases

Columnar databases, also known as column-oriented databases, are a type of non-relational database that store and organize data in a columnar format rather than the traditional row-based format. Here's an overview of columnar databases:

• Columnar Storage: Data in columnar databases is stored and organised in columns rather than rows. Each column provides data attribute values such as name, age, or price. Columnar databases are well-suited for analytical and reporting workloads because this storage structure provides for effective compression and retrieval of data at the column level.
• Performance: Columnar databases are optimised for analytical queries involving aggregations, filtering, and working with huge datasets. The columnar storage format enables selective column access, reducing disc reads and improving query performance. Because of the data proximity, aggregations and operations on a single column can be performed more effectively.
• Compression: Columnar databases frequently use powerful compression techniques created specifically for columnar data. Because columns frequently include repeated values, compression methods can take advantage of data similarities, resulting in reduced storage requirements and faster query performance. This compression improves the overall performance of columnar databases.
• Selective Projection: In columnar databases, queries can access only the columns they need, resulting in improved performance and lower I/O overhead. This selective projection option speeds up query execution and reduces the need to scan full rows, which is useful in scenarios with large tables and many columns.

Databases as part of application

After talking about introduction to databases, Let's talk about working with them as part of application. Databases are essential in many applications because they provide a permanent and structured storage option for handling data. Here are some examples of how databases are commonly used as part of an application:

• Data Storage: Databases act as a central repository for application data storage. Applications can assure data integrity, longevity, and efficient retrieval by utilising a database.
• User Authentication and Authorization: Databases are frequently used to store user credentials for authentication reasons, such as usernames and passwords.
• Data Persistence: Databases provide a persistent storage method, allowing applications to retain data that lasts longer than the application session.

Working with Databases

After talking about introduction to databases, Let's talk about working with them. Most database management systems come pre-installed with a command line tool that enables interaction with the database installation. For instance, MySQL has the mysql command line client, PostgreSQL has psql, and MongoDB has the MongoDB Shell. Additionally, there are third-party command line clients available for various DBMSs, such as Redli, which serves as an alternative to Redis's default redis-cli tool and offers additional features.

However, using a command line interface for managing data may not be user-friendly for everyone, which is why graphical database administration tools are available for many open-source DBMSs. Some tools like phpMyAdmin or pgAdmin are web-based, while others like MySQL Workbench or MongoDB Compass are designed to connect to a remote database from a local machine. As an application grows, the amount of data stored in the database increases, potentially causing performance issues. To address this, there are common strategies like replication and sharding. Replication involves synchronizing data across multiple independent databases. Having multiple copies of the data offers redundancy in case of server failures and improves availability, scalability, and read latencies. Replication is a built-in feature in many DBMSs, including MongoDB and MySQL, with multiple methods available for flexibility. Database sharding involves splitting data records that would typically be stored in the same table or collection and distributing them across multiple machines or shards. Sharding is particularly useful when dealing with large data volumes, allowing horizontal scaling by adding more machines as new shards. Both replication and sharding are techniques employed to address scalability and performance concerns as an application deals with growing data volumes in the database.