Introduction

Learn the fundamentals of databases, DBMS, relational databases, SQL, and relationships.

Introduction to DBMS & SQL

Almost every modern application stores data. Whether it is a banking application, an e-commerce website, a hospital management system, or a social media platform, all of them rely on databases to efficiently store and retrieve information.

A Database Management System (DBMS) provides a systematic way to create, manage, retrieve, and update this data.

Throughout these notes, we will use an Employee Management System as our example database.

What is Data?

Data is a collection of raw facts and figures.

Examples:

• Rahul
• 25
• ₹50,000
• Hyderabad

Individually, these values have little meaning.

Practice

1. Which of the following is an example of data?

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

Solution

Answer: D. All of the above

Data represents raw facts and figures before they are processed into meaningful information.

What is Information?

Information is processed and organized data that has meaning.

Example

Employee ID	Name	Salary
101	Rahul	50000

This is meaningful information because it describes an employee.

Practice

1. What is the difference between data and information?

Solution

• Data consists of raw facts and figures.
• Information is processed data that has meaning.

What is a Database?

A database is an organized collection of related data stored electronically so that it can be accessed, updated, and managed efficiently.

Examples:

• Student Management System
• Banking System
• Hospital Management System
• Employee Management System
• Library Management System

Why do we need a Database?

Without databases, data would often be stored in files or spreadsheets, making it difficult to:

• Search large amounts of data
• Update records
• Prevent duplicate entries
• Share data among multiple users
• Maintain data consistency

A database solves these problems by organizing data efficiently.

Practice

1. Which of the following is an example of a database?

• A. Employee Records
• B. Calculator
• C. Keyboard
• D. Printer

Solution

Answer: A. Employee Records

A database stores related information such as employee details, customer records, hospital data, etc.

What is DBMS?

A Database Management System (DBMS) is software that allows users and applications to create, store, retrieve, update, and delete data from a database.

Examples of DBMS:

• SQLite
• MySQL
• PostgreSQL
• Oracle Database
• Microsoft SQL Server

Responsibilities of a DBMS

• Stores data
• Retrieves data quickly
• Updates records
• Deletes records
• Controls multiple users
• Maintains security
• Performs backup and recovery

Practice

1. What is the primary purpose of a DBMS?

Solution

A DBMS is used to efficiently store, organize, retrieve, update, and manage data.

Advantages of DBMS

Compared to storing information in files, a DBMS provides several advantages.

• Faster data retrieval
• Reduced data redundancy
• Better security
• Data consistency
• Concurrent access
• Backup and recovery
• Easier maintenance

Practice

1. Which feature of a DBMS helps reduce duplicate data?

Solution

Reduced Data Redundancy

A DBMS stores data in an organized manner to avoid unnecessary duplication.

Types of Databases

Databases can be broadly classified into two categories.

Relational Databases (SQL Databases)

A Relational Database stores data in tables consisting of rows and columns.

Examples:

• SQLite
• MySQL
• PostgreSQL
• Oracle
• SQL Server

Suitable for:

• Banking
• College Management
• Employee Management
• Inventory Systems

NoSQL Databases

NoSQL databases store data in formats other than tables, such as documents, key-value pairs, graphs, or columns.

Examples:

• MongoDB
• Redis
• Cassandra
• Neo4j

Suitable for:

• Social Media
• Chat Applications
• Big Data
• IoT Systems

SQL vs NoSQL

SQL	NoSQL
Stores data in tables	Stores data as documents, key-value pairs, graphs, etc.
Uses SQL	Does not necessarily use SQL
Fixed Schema	Flexible Schema
Best for structured data	Best for unstructured data

Practice

1. Which type of database stores data in tables?

Solution

Relational Database (SQL Database)

What is SQL?

SQL (Structured Query Language) is the standard language used to communicate with relational databases.

Using SQL, we can:

• Create tables
• Insert records
• Retrieve data
• Update records
• Delete records

Example

SELECT * FROM employees;

The above query retrieves all employee records from the employees table.

Practice

1. Which language is used to communicate with relational databases?

Solution

SQL (Structured Query Language)

SQL Dialects & ORMs

Although SQL is a standardized language (ANSI SQL), different database management systems implement their own variations, known as SQL Dialects.

While basic queries like SELECT and WHERE work similarly across most systems, other commands (such as auto-incrementing fields, string functions, or pagination) have slightly different syntax.

Examples of Dialect Differences

Here is how three of the most widely used open-source databases define an auto-incrementing primary key:

• SQLite:
  CREATE TABLE users (
      user_id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT
  );
  
• PostgreSQL:
  CREATE TABLE users (
      user_id SERIAL PRIMARY KEY,
      username VARCHAR(100)
  );
  
• MySQL:
  CREATE TABLE users (
      user_id INT PRIMARY KEY AUTO_INCREMENT,
      username VARCHAR(100)
  );
  

Introducing ORMs

Writing raw SQL queries in your application code makes the code tightly coupled to a specific database dialect. If you decide to migrate your app from SQLite (often used for local development) to PostgreSQL (commonly used in production), you would need to manually rewrite parts of your SQL queries.

To avoid handling these database-specific syntax differences at the programming level, we use ORMs (Object-Relational Mappers).

An ORM allows you to write database queries using Python code instead of raw SQL. The ORM automatically translates your Python commands into the correct SQL dialect for whatever database engine you are currently using, making it easy to swap databases without modifying your application logic.

Relational Database Concepts

A relational database organizes data into tables.

Table

A table stores related information.

Example

Employee ID	Name	Salary
101	Rahul	65000
102	Anitha	55000

Row (Record)

Each row represents one complete record.

Example

|101|Rahul|65000|

This represents one employee.

Column (Field)

Each column represents one attribute.

Examples:

• Employee ID
• Name
• Salary

Practice

1. What does a row represent in a table?

Solution

A row represents one complete record.

Example: One employee.

Primary Key

A Primary Key uniquely identifies every row in a table.

Characteristics

• Unique
• Cannot contain NULL
• One primary key per table

Example

Employee ID	Name
101	Rahul
102	Anitha

Here, Employee ID is the Primary Key.

Practice

1. Can two rows have the same Primary Key value?

Solution

No.

A Primary Key must always contain unique values.

Foreign Key

A Foreign Key creates a relationship between two tables.

It refers to the Primary Key of another table.

Department Table

Department ID	Department Name
1	Engineering
2	HR

Employees Table

Employee	Department ID
Rahul	1
Anitha	2

Here, Department ID in the employees table is a Foreign Key referencing the departments table.

Practice

1. What does a Foreign Key reference?

Solution

A Foreign Key references the Primary Key of another table.

Database Relationships

One-to-One (1:1)

One employee has one passport.

Employee
    │
    └── Passport

One-to-Many (1:N)

One department contains many employees.

Engineering
    ├── Rahul
    ├── Kiran
    └── Ajay

This is the most common relationship in relational databases.

Many-to-Many (M:N)

Many students can enroll in many courses.

This relationship requires a junction table.

Student
    │
Enrollment
    │
Course

Practice

1. Which relationship is commonly used between Departments and Employees?

Solution

One-to-Many (1:N)

One department can have many employees, while each employee belongs to one department.

Sample Database Used Throughout This Guide

Departments

Department ID	Department Name
1	Engineering
2	HR
3	Sales

Employees

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

The employees and departments tables will be used in all SQL examples throughout the following chapters.