Database Basics

#sql #postgres #rdbms #database

What is Data?

Data refers to raw, unorganized facts and figures that can be processed to generate meaningful information. A database is a structured system used to store, manage, and retrieve data efficiently for multiple users and applications.

It's important due to following features:

Scalability: Handles large volumes of data efficiently.
Data Integrity: Maintains accuracy using rules and constraints.
Security: Protects data through controlled access and permissions.
Analytics: Helps analyze data to support better decision-making.

Working of Databases

Databases work by organizing and storing information in a structured or unstructured format, allowing easy access, retrieval, and modification. At the core of every database system is the Database Management System (DBMS)- a software layer that acts as an intermediary between users and the raw data.

The DBMS handles tasks like querying, updating, deleting and managing access permissions, without requiring users to know the physical details of where data is stored.
When a user submits a request (such as a search or update), the DBMS processes the query, locates the relevant data, and returns results in a structured format.
DBMSs provide features like backup, recovery, performance optimization and data security to ensure the system runs efficiently and reliably.

Components of a Database
Databases consist of several critical components that work together to store, organize and retrieve data effectively. Here is a detailed explanation of each component:

Data: The actual information stored in the database, such as text, numbers, images, or files.
Schema: The structural blueprint that defines how data is organized—tables, fields, data types, and relationships.
DBMS: The software that manages database operations like storage, retrieval, and security (e.g., MySQL, Oracle).
Queries: Instructions (usually SQL) used to retrieve or manipulate data within the database.
Users: People or systems that interact with the database, each with specific roles and access permissions

Types of Databases

Databases can be classified into two primary types Relational (SQL) and NoSQL Databases. NoSQL is then further divided into four types: Document-oriented, Key-Value, Wide-Column, and Graph databases.

Relational Databases (RDBMS)

Relational databases organize data into tables made up of rows (records) and columns (fields). They use schemas (blueprints) to define how data is structured and how different tables relate to each other.

Strict schema-based structure.
Primary Keys (unique IDs) and Foreign Keys (relationships between tables).
ACID compliant (ensures Atomicity, Consistency, Isolation, and Durability in transactions).
Ideal for structured data.

NoSQL Databases

"NoSQL" stands for "Not Only SQL". These databases are designed to handle unstructured or semi-structured data, such as text, images, videos or sensor data. They don’t rely on the traditional table format.

Flexible data models (no fixed schema).
Scales horizontally for high-volume data.
Often optimized for specific use cases like graphs or time-series data

Document Databases – Store data as JSON-like documents. (Example: MongoDB)
Key-Value Stores – Store simple key–value pairs for fast lookups. (Example: Redis)
Columnar Databases – Store data by columns for analytics. (Example: Apache Cassandra)
Graph Databases – Store nodes & relationships for connected data. (Example: Neo4j)

**ACID **Properties:

ACID stands for Atomicity, Consistency, Isolation, and Durability—four essential principles that ensure your database transactions are reliable, accurate, and secure.

Atomicity: Ensures transactions complete fully or not at all.
Consistency: Ensures the database moves from one valid state to another.
Isolation: Ensures that multiple transactions can happen at the same time without affecting each other.
Durability: Saves changes permanently after completion

Real-World Applications of Databases

Databases are essential part of our life. There are several everyday activities that involve our interaction with databases.