Introduction to Databases — SQL with Dutta

Key Concepts

What a database actually is (a shared, logically related collection of data)
The 4 main use-cases of databases: storage, analysis, record keeping, web apps
CRUD — the four basic operations every database supports
5 properties of an ideal database: Integrity, Availability, Security, Independence, Concurrency
5 major database types: Relational, NoSQL, Column, Graph, Key-Value
OLTP vs OLAP — the difference between transactional and analytical databases

1. What are Databases?

Definition: A Database is a shared collection of logically related data (and a description of that data), designed to meet the information needs of an organization.

Note: the word shared is important — a database is meant to be accessed by multiple users/applications at once, and logically related means the data isn't random — it's organized so relationships between pieces of data make sense (e.g., a customer and their orders).

Why we use Databases (4 core purposes)

Purpose	What it means
Data Storage	Stores large amounts of structured data, making it easily accessible, searchable, and retrievable.
Data Analysis	Used to perform complex data analysis, generate reports, and provide insights into the data.
Record Keeping	Keeps track of important records — e.g., financial transactions, customer information, inventory levels.
Web Applications	Databases power dynamic content and user management for most web apps.

CRUD Operations

Every database (regardless of type) supports four fundamental operations, remembered by the acronym CRUD:

C — Create   (add new data)
R — Retrieve (read/query existing data)
U — Update   (modify existing data)
D — Delete   (remove data)

💡 Tip: Whenever you interact with any database (SQL, NoSQL, etc.), what you're doing under the hood is almost always one of these four operations.

2. Properties of an Ideal Database

An ideal database should have these 5 properties:

Integrity — data should be accurate and consistent (i.e., no contradictory or corrupted data).
Availability — the database should be accessible 24×7.
Security — protect data from unauthorized access.
Independent of Application — the database shouldn't be tightly coupled to one single application; it should be usable by multiple applications/services independently.
Concurrency — the database should handle multiple users/processes accessing/modifying data at the same time without conflicts or corruption.

Diagram: "Independent of Application" concept

The instructor's sketch showed one central database being accessed by multiple separate applications (Web, Mobile, iOS), illustrating that a well-designed database is not owned by a single app — many apps/services can read/write to it independently.

flowchart TB DB((Central Database)) Web[Web] Mobile[Mobile] IOS[iOS] Web --> DB Mobile --> DB IOS --> DB

Things to Remember:

— Integrity → Accuracy + Consistency
— Availability → 24×7 uptime
— A single DB should be reusable across multiple independent applications (not hard-coded to just one app)

3. Types of Databases

There are 5 major types of databases:

#	Type	Also Known As	Best Suited For	Examples
1	Relational Databases	SQL databases	Structured data organized into tables (rows & columns); everyday transactional apps	MySQL, PostgreSQL, Microsoft Access
2	NoSQL Databases	—	Large amounts of unstructured/semi-structured data — documents, images, videos	MongoDB
3	Column Databases	—	Data warehousing & analytical applications (OLAP)	Amazon Redshift, Google BigQuery
4	Graph Databases	—	Graph-structured data — social network connections, recommendation systems	Neo4j, Amazon Neptune
5	Key-Value Databases	—	Caching & simple data storage needs (fast lookups)	Redis, Amazon DynamoDB

3.1 Relational Databases (SQL)

Also known as SQL databases.
Use a relational model — organize data into tables made up of rows and columns.
Data in different tables is connected via relationships (typically Primary Key → Foreign Key links).

Example sketched in class: two tables — Sportsperson and Cars — connected via a relationship (e.g., a foreign key linking a sportsperson to a car they own).

erDiagram SPORTSPERSON ||--o{ CARS : owns SPORTSPERSON { int sportsperson_id PK } CARS { int car_id PK int sportsperson_id FK }

Note: the exact column names weren't legible in the handwritten sketch (only "pk" and general relationship arrows were visible) — the key takeaway is that relational databases link separate tables together through key relationships, rather than storing everything in one giant table.

3.2 NoSQL Databases

Designed to handle large amounts of unstructured or semi-structured data.
Good for storing things like documents, images, or videos.
Example: MongoDB.

3.3 Column Databases

Store data in columns rather than rows.
Well suited for data warehousing and analytical applications.
Examples: Amazon Redshift, Google BigQuery.

3.4 Graph Databases

Store and query graph-structured data.
Ideal for social network connections or recommendation systems.
Examples: Neo4j, Amazon Neptune.

3.5 Key-Value Databases

Store data as a simple collection of keys and values.
Well suited for caching and simple/fast data storage needs.
Examples: Redis, Amazon DynamoDB.

4. Relational (OLTP) vs Column (OLAP) Databases

This was a key comparison drawn out in class — Relational databases and Column databases solve different problems, even though both are "structured":

flowchart LR subgraph Relational_DB [Relational DB] direction TB A1[OLTP] A2[Stores data by ROWS] A3[Used by: Website / App] end subgraph Column_DB [Column DB] direction TB B1[OLAP] B2[Stores data as Datawarehouse] B3[Used by: Analyst] end

Aspect	Relational DB	Column DB
Type	OLTP (Online Transaction Processing)	OLAP (Online Analytical Processing)
Data organized by	Rows	Columns
Typical consumer	Website / application (day-to-day operations)	Analyst (reporting, analytics, business intelligence)

💡 Tip (how to remember):
— OLTP = Transactions → think "website placing an order" → row-based, relational.
— OLAP = Analytics → think "analyst running reports on years of sales data" → column-based, data warehouse.

Things to Remember

A database = a shared, logically related collection of data + its description.
4 main uses: Storage, Analysis, Record Keeping, Web Apps.
CRUD = Create, Retrieve, Update, Delete — the 4 basic operations on any database.
5 properties of an ideal database: Integrity (accurate + consistent), Availability (24×7), Security, Independence from a single application, Concurrency.
5 database types: Relational (SQL), NoSQL, Column, Graph, Key-Value.
Relational = OLTP (rows, used by apps/websites) vs Column = OLAP (columns, used by analysts, data warehousing).

Quick Revision (1–2 min read)

A database is a shared, organized collection of data used for storage, analysis, record-keeping, and powering web apps. Every database supports CRUD operations. A good database should be accurate & consistent (Integrity), always available (24×7), secure, independent of any single application, and able to handle concurrent access.

There are 5 main database types: Relational (SQL — tables of rows/columns, e.g. MySQL, PostgreSQL), NoSQL (unstructured data, e.g. MongoDB), Column (analytics/data warehousing, e.g. Redshift, BigQuery), Graph (relationships/networks, e.g. Neo4j), and Key-Value (fast caching, e.g. Redis, DynamoDB).

The most important practical distinction: Relational databases are OLTP (row-based, used by live applications for transactions) while Column databases are OLAP (column-based, used by analysts for reporting and data warehousing).