🗄️ Database — Complete Basic Notes¶

Target Audience: Non-technical students who want to understand what databases are, why they exist, and how they work — explained in plain, everyday language.

1. The Three Core Concepts: Data, Database, and DBMS¶

Most beginners confuse these three terms and use them interchangeably. They are related but they are not the same thing. Understanding the difference between them is the very first step.

1.1 Data¶

Data is a collection of raw, unprocessed facts about any object, person, event, or thing.

• Data on its own has no meaning. It is just facts.
• It may or may not be relevant to you.
• Data can be anything: numbers, text, images, dates, symbols.

Examples of raw data:

25, "Alice", "01-01-2000", 98.6, "Rawalpindi", true, 7734

Think of data like individual puzzle pieces scattered on a table. Each piece exists, but it does not show you the full picture yet.

1.2 Information¶

Information is data that has been processed, organized, or given context so that it becomes meaningful and relevant.

• Information is always relevant to a specific question or purpose.
• It is derived from data by adding context.
• Not all data becomes information — only the data that is relevant to your query.

Example: Suppose a hospital database has the following raw data:

"Alice", 25, "01-01-2000", "Rawalpindi", 98.6, "Flu", "Dr. Ahmed", "2024-03-15"

Now, if the doctor asks: "What is Alice's age and diagnosis?" - Information (relevant): Alice is 25 years old and was diagnosed with Flu on 2024-03-15. - Data (not relevant to this query): Her city (Rawalpindi) and body temperature (98.6) are still data — they exist but are not part of the answer to this specific question.

Think of information like a completed puzzle section — you have selected and arranged only the relevant pieces to reveal a meaningful picture.

1.3 The Relationship: Data → Information¶

Raw Facts (Data)
       |
       |  processing + context + relevance
       ↓
Meaningful Output (Information)

1.4 Database (DB)¶

A database is a systematic, organized collection of related data stored so it can be easily accessed, managed, searched, and updated.

• The keyword is systematic — data is not just dumped randomly; it is structured with a purpose.
• A database stores data in a way that allows meaningful information to be retrieved from it.

Real-life examples: - Your phone's contact list is a tiny database (name, number, email stored together). - A school's student records system is a database. - Netflix uses a database to store every movie, user account, and watch history. - Amazon uses a database to store every product, price, and order.

Think of a database like a well-organized library — thousands of books (data) are arranged systematically so you can find what you need (information) quickly.

1.5 DBMS — Database Management System¶

A DBMS (Database Management System) is a software application that enables users to access, create, manage, and manipulate a database.

• You never interact with the raw data files directly.
• The DBMS sits between the user/application and the stored data, acting as an intermediary.
• It handles security, queries, updates, backups, and multi-user access.

         You / Application
               |
               |  (sends a request, e.g., "Find all orders from today")
               ↓
   +---------------------------+
   |          DBMS             |  <-- This is the SOFTWARE (e.g., MySQL, PostgreSQL)
   |  (processes the request)  |
   +---------------------------+
               |
               ↓
   Raw Data stored on Disk / Memory
               |
               ↓
   Result returned to You / Application

What a DBMS does: - Allows users to create databases and tables - Allows users to read, insert, update, delete data - Enforces security (who can access what) - Manages concurrent access (multiple users at once) - Handles backups and recovery

Examples of DBMS Software¶

Common Confusion: Many beginners think MySQL or MongoDB is the database itself. It is not. MySQL is the DBMS — the software engine. The actual database is the collection of data it manages. MySQL is the tool; your data is the database.

2. Why Do We Need Databases?¶

Without a database, data would be stored in loose files (like plain text or Excel sheets), which causes problems:

3. Key Terminology¶

4. Relational vs. Non-Relational Databases (In Depth)¶

This is one of the most important distinctions in database technology. Let's go deep.

4.1 Relational Databases (SQL Databases)¶

A relational database stores data in structured tables made of rows and columns. The tables are related to each other through keys.

Key Characteristics¶

What is OLTP?¶

OLTP (Online Transaction Processing) means the database handles a very large number of short, real-time transactions — like: - A customer placing an order on Amazon - A student registering for a course - A bank processing a money transfer

Relational databases are perfectly built for this because every transaction must be accurate, complete, and reliable.

Example — A School Relational Database¶

Students Table: | StudentID (PK) | Name | Age | ClassID (FK) | |----------------|-------|-----|--------------| | S001 | Alice | 20 | C01 | | S002 | Bob | 22 | C02 |

Classes Table: | ClassID (PK) | ClassName | |--------------|-------------| | C01 | Mathematics | | C02 | Physics |

• Each row is a Tuple (e.g., the entire row S001, Alice, 20, C01 is one tuple)
• Each column is an Attribute (e.g., Name, Age)
• StudentID is the Primary Key of the Students table
• ClassID in the Students table is a Foreign Key referencing the Classes table
• The two tables are related — that's why it's called a relational database

Relational Database Examples & AWS Services¶

The following are the major relational database engines. Amazon offers most of them under a single managed cloud service:

AWS Cloud Services for Relational Databases:

RDS = Relational Database Service. It does not create its own engine — it manages the existing six engines for you in the cloud so you don't have to set up servers manually.

4.2 Non-Relational Databases (NoSQL Databases)¶

NoSQL stands for "Not Only SQL". These databases do not use fixed tables. They store data in flexible, varied formats suited for modern, high-speed applications.

Key Characteristics¶

Why No Universal Query Language?¶

In relational databases, SQL works for all of them because all of them share the same table structure. In NoSQL, each type stores data differently (documents, graphs, key-value pairs, columns) — so each one has its own way of querying data:

4.3 Relational vs. Non-Relational — Side-by-Side¶

5. NoSQL Database Types — Deep Dive¶

NoSQL is not one single type of database. It is a family of four major types, each designed for a different kind of data.

5.1 Columnar Database (Column-Family Database)¶

How it works: Instead of storing data row by row (like relational databases), columnar databases store data column by column. This means when you search or analyze data, you only read the columns you need — not entire rows. This makes it dramatically faster for analytical queries.

Structure:

Row Key  |  Name    |  Age  |  City
---------|----------|-------|----------
R001     |  Alice   |  20   |  Karachi
R002     |  Bob     |  22   |  Lahore

Why it is fast: - You read only the columns relevant to your query - Excellent compression (similar values stored together) - Designed to handle millions to billions of rows

Real-world use cases: - IoT sensor data (millions of readings per second) - Time-series data (stock prices over time) - Large-scale analytics (clickstream data, user behavior tracking) - Social media feeds with massive write throughput

Examples: Apache Cassandra, Apache HBase

AWS Equivalent: Amazon Keyspaces (Managed Cassandra-compatible service)

5.2 Document Database¶

How it works: Data is stored as documents — typically in JSON or BSON format. Each document is a self-contained unit that can store rich, nested, complex data including arrays and sub-objects. Unlike relational tables, documents in the same collection can have completely different fields.

Structure (JSON document):

{
  "userID": "U001",
  "name": "Alice",
  "email": "alice@example.com",
  "address": {
    "city": "Rawalpindi",
    "country": "Pakistan"
  },
  "orders": ["ORD001", "ORD002", "ORD005"]
}

Another document in the same collection can have completely different fields:

{
  "userID": "U002",
  "name": "Bob",
  "phone": "0300-1234567"
}

Why it is useful: - Stores long, complex, nested data naturally - No need to define a fixed schema upfront - Adding a new field to one document does not break others - Great for content that evolves over time

Real-world use cases: - User profiles on social media (each user may have different fields) - Product catalogs (a phone has different attributes than a shirt) - Blog posts, articles, CMS systems - E-commerce catalogs with varying product types

Examples: MongoDB, CouchDB, Firebase Firestore

AWS Equivalent: Amazon DocumentDB (MongoDB-compatible managed service)

5.3 Key-Value Database¶

How it works: This is the simplest type of NoSQL database. Data is stored as pairs of key → value, just like a dictionary or a locker system. You give a key, you get the value. That's it.

Structure:

Key                  | Value
---------------------|----------------------------------
"user:101"           | "Alice"
"session:abc123"     | "{logged_in: true, role: admin}"
"cart:U001"          | "[item1, item2, item3]"
"page:homepage"      | "<html>...cached HTML...</html>"

Why it is fast: - Lookup by key is O(1) — almost instant, regardless of data size - No complex queries, no joins, no schema overhead - Often stored entirely in memory (RAM) for maximum speed

Real-world use cases: - Session management (store logged-in user's session data) - Caching (store frequently accessed pages/results so the app doesn't recompute them) - Leaderboards (real-time game scores) - Shopping cart (temporary cart data before checkout) - Feature flags (on/off switches for app features)

Examples: Redis, Memcached

AWS Equivalents: - Amazon ElastiCache (managed Redis / Memcached) - Amazon DynamoDB (also a key-value store, but more powerful — supports document model too)

5.4 Graph Database¶

How it works: A graph database stores data as a network of nodes and edges: - Node = an entity (a person, a city, a product, a webpage) - Edge = a relationship between two nodes (FRIENDS_WITH, LOCATED_IN, BOUGHT, LINKS_TO) - Properties = attributes attached to nodes or edges

Structure:

(Alice) --[FRIENDS_WITH]--> (Bob)
(Bob)   --[LIVES_IN]------> (Lahore)
(Alice) --[BOUGHT]---------> (iPhone 15)
(BBC)   --[REPORTED_ON]----> (Climate Change)
(Climate Change) --[AFFECTS]--> (Pakistan)

Why it is powerful: - Relationships between data are first-class citizens — stored explicitly, not computed via joins - Traversing relationships ("who are Alice's friends' friends?") is extremely fast - Naturally models complex, interconnected real-world data

Real-world use cases: - Social networks (Facebook's friend recommendations, LinkedIn's "People You May Know") - Fraud detection (finding suspicious patterns of transactions between accounts) - Knowledge graphs (BBC uses a graph database to link news stories, topics, people, and places — e.g., a weather report linking regions, events, and impacts) - Recommendation engines ("Users who bought X also bought Y") - Network and IT infrastructure mapping (how servers connect to each other) - Drug discovery (relationships between molecules, diseases, and proteins)

Examples: Neo4j, Amazon Neptune, ArangoDB

AWS Equivalent: Amazon Neptune (fully managed graph database supporting both Property Graph and RDF)

5.5 NoSQL Types Summary¶

6. What is SQL?¶

SQL (Structured Query Language) is the standard language used to communicate with all relational databases. It is not a programming language — it is a query language designed specifically for data.

Common SQL Commands (CRUD)¶

These four core operations — Create, Read, Update, Delete — are called CRUD and are fundamental to every database application.

7. ACID Properties (Data Reliability)¶

For a database to be reliable and trustworthy (especially for banking, healthcare), it must follow ACID rules:

8. Database vs. Spreadsheet¶

9. Real-World Database Examples¶

10. Summary of Key Concepts¶

• Data = raw, unprocessed facts about anything; may or may not be relevant.
• Information = data that has been given context and is relevant to a specific purpose.
• Database = a systematic, organized collection of related data.
• DBMS = the software (e.g., MySQL, MongoDB) that lets users access and manipulate the database.
• Relational databases use fixed-schema tables, SQL, tuples (rows), attributes (columns), primary keys, and foreign keys; best for OLTP and structured data.
• Non-relational (NoSQL) databases are schema-less, horizontally scalable, and each has its own query language.
• The four NoSQL types are: Columnar, Document, Key-Value, and Graph — each solves a different problem.
• AWS provides managed cloud services for every database type: RDS/Aurora (relational), Keyspaces (columnar), DocumentDB (document), ElastiCache/DynamoDB (key-value), Neptune (graph).

💡 Pro Tip for Beginners: Start by learning MySQL or PostgreSQL (relational) as they teach the core concepts clearly. Once comfortable, explore MongoDB (NoSQL document) to understand the difference. Then look at Redis to appreciate how simple and fast key-value stores are.