Choosing the Right Database System: SQL vs. NoSQL

When choosing a database system, it is crucial to select either a relational (SQL) or non-relational (NoSQL) database system depending on the requirements of the project. These systems both offer different benefits depending on the kind of data that is managed and the specific needs of the system.

Here are some key considerations for SQL option:

1. Structured Data: Ideal for applications requiring structured data with predefined schemas.

An example of an application that requires structured data is an inventory management system.

In the Inventory Management System, the following data may be applicable:

• Products Table: Stores product information with fields like ProductID, ProductName, Category, SupplierID, UnitPrice, and UnitsInStock.
• Suppliers Table: Stores supplier information including SupplierID, SupplierName, ContactName, Address, City, PostalCode, Country, and Phone.

The benefits of structuring data in the inventory management system:

Structured Data: Organizes product and supplier data in a predefined, structured format.

Data Integrity: Enforces data consistency and relationships through primary and foreign key constraints.

2. ACID compliance is a set of properties that ensure reliable processing of database transactions. It is critical for systems where data integrity and reliability is important. ACID stands for Atomicity, Consistency, Isolation, and Durability. Here’s a brief explanation of each:

· Atomicity: Ensures that each transaction is treated as a single unit, which either succeeds completely or fails completely. If any part of the transaction fails, the entire transaction is rolled back, and the database remains unchanged.

· Consistency: Ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants. After a transaction completes, all data must be in a consistent state.

· Isolation: Ensures that concurrent transactions do not interfere with each other. Transactions are executed in isolation, meaning the intermediate state of a transaction is not visible to other transactions until the transaction is complete.

· Durability: Ensures that once a transaction has been committed, it will remain so, even in the event of a system failure. The changes made by the transaction are permanently recorded in the database.

Example: using inventory management system — Order Processing Scenario

Transaction Steps:

1. Check Product Availability: Verify that there is enough stock of the product.
2. Deduct Stock: Subtract the ordered quantity from the inventory.
3. Update Order Records: Record the order in the system.
4. Commit Transaction: Confirm that all steps have been completed successfully.

ACID Properties Applied:

Atomicity:

The entire process of checking stock, deducting inventory, and updating order records should be treated as a single transaction. If any part of this process fails (e.g an issue with stock deduction or order record update), all changes should be rolled back. This ensures that either all changes are applied or none at all, preventing partial updates.

Consistency:

The system should maintain data consistency. For instance, if a customer is ordering 10 units of a product, the system should ensure that only 10 units are deducted from inventory if the order is confirmed. If the stock level was 50 before the order, it should be 40 after the transaction. The order record should also accurately reflect the order details.

Isolation:

If multiple orders are being processed simultaneously, each transaction should operate independently of others. For example, if two orders are being processed at the same time, the system should ensure that one order does not affect the stock level that another order is checking or updating until both are completed. This prevents issues like overselling.

Durability:

Once the transaction is committed, all changes should be permanent, even if the system crashes immediately afterward. For instance, the deduction in stock and the order record should be preserved in the database, ensuring that no data is lost.

3. Relationships: In relational databases, relationships define how different tables are connected to each other. Understanding these relationships is crucial for designing efficient databases and performing accurate queries. Here’s an overview of the main types of relationships in relational databases:

· One-to-One (1:1) : Each record in one table corresponds to exactly one record in another table.

Example: A product table and a supplier table. In this setup, each SupplierID in the Product table refers to a unique supplier in the Supplier table. Conversely, each SupplierID in the Supplier table corresponds to a unique product in the Product table.

Implementation: Usually involves a primary key in one table that is also a foreign key in another table.

· One-to-Many (1)

· Description: A single record in one table is associated with multiple records in another table.

· Example: A Customer table and an Order table. Each customer can place multiple orders, but each order is placed by only one customer.

· Implementation: The primary key of the “one” side table is used as a foreign key in the “many” side table.

· Many-to-Many (M)

Description: Multiple records in one table are associated with multiple records in another table.

Example: In this setup:

• ProductID 1 is supplied by both SupplierID 1 and SupplierID 2.
• ProductID 2 is supplied by SupplierID 1.

The ProductSupplier table facilitates the many-to-many relationship by linking ProductID and SupplierID.

Implementation: Requires a junction table (also known as a bridge or associative table) that holds foreign keys referencing the primary keys of the two tables.

Examples of relational(SQL) databases:

1. MySQL

• Overview: An open-source relational database management system (RDBMS) known for its speed and reliability.
• Features: Supports a wide range of storage engines, robust security, high performance, and scalability.

2. PostgreSQL

• Overview: An open-source RDBMS known for its advanced features and standards compliance.
• Features: Supports complex queries, full-text search, JSON data types, and advanced indexing.

3. Microsoft SQL Server

• Overview: A commercial RDBMS developed by Microsoft with comprehensive data management tools.
• Features: Integration with Microsoft tools, high availability, advanced analytics, and business intelligence (BI) capabilities.

4. Oracle Database

• Overview: A commercial RDBMS known for its scalability, performance, and enterprise features.
• Features: Advanced data security, multi-model database capabilities, and extensive support for transaction processing.

5. SQLite

• Overview: A lightweight, file-based RDBMS that is embedded into applications.
• Features: Self-contained, serverless, and zero-configuration with a small footprint.

6. IBM Db2

• Overview: A commercial RDBMS developed by IBM, known for its reliability and high performance.
• Features: Supports both traditional and modern data workloads, including machine learning and analytics.

7. MariaDB

• Overview: An open-source RDBMS that is a fork of MySQL, developed by the original MySQL developers.
• Features: Focuses on performance, security, and open-source licensing.

8. Amazon Aurora

• Overview: A managed relational database service by Amazon Web Services (AWS) that is compatible with MySQL and PostgreSQL.
• Features: High availability, automatic backups, and scalable performance.

9. Google Cloud SQL

• Overview: A fully-managed database service on Google Cloud Platform (GCP) that supports MySQL, PostgreSQL, and SQL Server.
• Features: Automated backups, high availability, and seamless integration with other GCP services.