> ## Documentation Index
> Fetch the complete documentation index at: https://fastapi2day.codewithsiva.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Introduction to Databases, DBMS & SQL Fundamentals

> Learn the fundamentals of databases, DBMS, relational vs NoSQL systems, SQL basics, and key concepts like primary and foreign keys.

Every modern application — from a banking portal to a hospital system to a social media feed — relies on a database to store and retrieve information efficiently. Before you write your first SQL query, it's worth understanding what a database actually is, why we need software to manage it, and how relational databases organize data into tables that can be linked together. This page walks you through all of those foundational ideas, illustrated throughout with an **Employee Management System** example that you'll continue using in every chapter that follows.

## Data vs. Information

**Data** is a collection of raw facts and figures that have no meaning on their own.

Examples of raw data:

* Rahul
* 25
* ₹50,000
* Hyderabad

**Information** is data that has been processed and organized so it carries meaning.

| Employee ID | Name  | Salary |
| ----------- | ----- | ------ |
| 101         | Rahul | 50000  |

This table is *information* — you can now see that Rahul is an employee who earns ₹50,000.

<Accordion title="Practice: Data vs. Information">
  **Which of the following is an example of raw data?**

  * A. Rahul
  * B. 25
  * C. ₹50,000
  * D. All of the above

  **Answer: D — All of the above.** Each value is a raw fact with no context on its own.
</Accordion>

## What Is a Database?

A **database** is an organized, electronic collection of related data that can be accessed, updated, and managed efficiently. Examples you encounter every day include:

* Student Management Systems
* Banking Systems
* Hospital Management Systems
* E-commerce Inventory Systems
* Library Management Systems

Without a database, data ends up scattered across spreadsheets and flat files, making it difficult to search, update, or share consistently. A database solves these problems by providing a structured, centralized home for your data.

## What Is a DBMS?

A **Database Management System (DBMS)** is software that sits between your application and the stored data. It handles creating, reading, updating, and deleting records while enforcing rules, controlling simultaneous users, and providing backup and recovery.

Popular DBMS products include:

* **SQLite** — lightweight, serverless, embedded
* **MySQL** — widely used for web applications
* **PostgreSQL** — open-source, feature-rich
* **Oracle Database** — enterprise-grade
* **Microsoft SQL Server** — Windows ecosystem

### Advantages of Using a DBMS

Compared with raw files or spreadsheets, a DBMS gives you:

* Faster data retrieval
* Reduced data redundancy
* Better security and access control
* Data consistency across multiple users
* Concurrent (simultaneous) access
* Automated backup and recovery
* Easier long-term maintenance

## Types of Databases

### Relational Databases (SQL)

A relational database stores data in **tables** made up of rows and columns. Every table represents one type of entity (e.g., employees, departments), and tables can be linked together through shared key columns.

| SQL Databases                     | Best For                                                 |
| --------------------------------- | -------------------------------------------------------- |
| SQLite, MySQL, PostgreSQL, Oracle | Banking, college management, employee records, inventory |

### NoSQL Databases

NoSQL databases store data in formats other than tables — documents, key-value pairs, graphs, or wide columns. They sacrifice some structure in exchange for flexibility and horizontal scalability.

| NoSQL Databases                  | Best For                               |
| -------------------------------- | -------------------------------------- |
| MongoDB, Redis, Cassandra, Neo4j | Social media, chat apps, Big Data, IoT |

### SQL vs. NoSQL at a Glance

| SQL                      | NoSQL                                         |
| ------------------------ | --------------------------------------------- |
| Stores data in tables    | Documents, key-value, graphs, etc.            |
| Uses SQL language        | Varied query languages                        |
| Fixed schema             | Flexible schema                               |
| Best for structured data | Best for unstructured or semi-structured data |

## What Is SQL?

**SQL (Structured Query Language)** is the standard language for communicating with relational databases. Using SQL you can:

* Create and modify table structures
* Insert, update, and delete records
* Query and retrieve data

```sql theme={null}
-- Retrieve all employee records
SELECT * FROM employee;
```

<Note>
  SQL is not case-sensitive for keywords (`SELECT` and `select` are equivalent), but it is a widely adopted convention to write SQL keywords in uppercase.
</Note>

## Relational Database Concepts

### Tables, Rows, and Columns

A **table** stores related information. Each **row** (record) represents one complete entity, and each **column** (field) represents one attribute.

| Employee ID | Name   | Salary |
| ----------- | ------ | ------ |
| 101         | Rahul  | 65000  |
| 102         | Anitha | 55000  |

Here, `Employee ID`, `Name`, and `Salary` are columns. Each row describes one employee.

### Primary Key

A **Primary Key** uniquely identifies every row in a table. It must be:

* Unique across all rows
* Never NULL
* Only one per table

In the employee table above, `Employee ID` is the primary key — no two employees share the same ID.

### Foreign Key

A **Foreign Key** links one table to another by referencing the primary key of a second table. This is how relational databases represent relationships between entities.

**Department table:**

| Department ID | Department Name |
| ------------- | --------------- |
| 1             | Engineering     |
| 2             | HR              |

**Employee table (with foreign key):**

| Employee | Department ID |
| -------- | ------------- |
| Rahul    | 1             |
| Anitha   | 2             |

`Department ID` in the Employee table is a foreign key pointing to the Department table.

<Accordion title="Practice: Primary vs. Foreign Key">
  **Can two rows have the same Primary Key value?**

  No. A Primary Key must always be unique. If two rows shared the same primary key, the database would have no reliable way to tell them apart.

  **What does a Foreign Key reference?**

  A Foreign Key always references the **Primary Key** of another table, creating a verifiable link between the two tables.
</Accordion>

## Database Relationships

Relationships describe how records in one table relate to records in another.

### One-to-One (1:1)

One employee has exactly one passport.

```text theme={null}
Employee ──── Passport
```

### One-to-Many (1:N)

One department contains many employees — the most common relationship type in relational databases.

```text theme={null}
Engineering
  ├── Rahul
  ├── Kiran
  └── Ajay
```

### Many-to-Many (M:N)

Many students can enroll in many courses. This requires a **junction table** to resolve the relationship.

```text theme={null}
Student ──── Enrollment ──── Course
```

<Tip>
  Whenever you spot a Many-to-Many relationship, create a separate junction (or bridge) table that holds the primary keys of both related tables. This keeps the schema clean and avoids duplicated rows.
</Tip>

## Sample Database Used Throughout This Course

All SQL examples in the following chapters use these two tables. Take a moment to familiarize yourself with them now.

**Department:**

| Department ID | Department Name |
| ------------- | --------------- |
| 1             | Engineering     |
| 2             | HR              |
| 3             | Sales           |

**Employee:**

| Employee ID | Employee Name | Salary | City      | Department ID |
| ----------- | ------------- | ------ | --------- | ------------- |
| 101         | Rahul         | 65000  | Hyderabad | 1             |
| 102         | Anitha        | 55000  | Bengaluru | 2             |
| 103         | Kiran         | 72000  | Hyderabad | 1             |
| 104         | Sneha         | 50000  | Chennai   | 3             |
| 105         | Ajay          | 80000  | Hyderabad | 1             |

`Employee ID` is the primary key of the Employee table. `Department ID` in the Employee table is a foreign key referencing the Department table's primary key.

<Note>
  The next page covers **SQLite, DDL, and DML** — you'll learn how to create these tables yourself and insert all the sample rows.
</Note>