Database Management Course Content

Unit 1: Introduction to Database and DBMS

• Lesson 1.1: Introduction to Database

1.What is a Database?

A database is an organized collection of data that is stored and accessed electronically. It is designed to efficiently store, retrieve, and manage data.

2.Key Characteristics of a Database:

• Structured: Data is organized into tables, rows, and columns.

• Persistent: Data is stored permanently (until explicitly deleted).

• Accessible: Allows authorized users and applications to access and manipulate data.

• Secure: Protects data from unauthorized access or corruption.

3.Why Use a Database?

• To manage large volumes of data efficiently.

• To ensure data consistency and accuracy.

• To allow multiple users to access and update data simultaneously.

• To provide data security and backup/recovery options.

4.Types of Databases:

1. Relational Database (RDBMS) – Data is stored in tables (e.g., MySQL, PostgreSQL, Oracle).

2. NoSQL Database – Non-tabular, used for unstructured data (e.g., MongoDB, Cassandra).

3. In-Memory Database – Data is stored in memory for fast access (e.g., Redis).

4. Cloud Database – Hosted on cloud platforms (e.g., Amazon RDS, Firebase).

5.Common Database Terminology:

• Table: A collection of related data entries.

• Row (Record): A single, structured data item in a table.

• Column (Field): A single piece of data for each record.

• Primary Key: A unique identifier for each record.

• Query: A request to access or manipulate data (e.g., using SQL).

6.Example Scenario:

A student management system stores data such as:

• Student ID

• Name

• Date of Birth

• Courses Enrolled

This data would be stored in a database for easy access and management.

Conclusion:

Databases are essential for modern computing. Whether it's an app, website, or enterprise system, databases play a crucial role in handling and organizing information effectively.

Files and Links

• 🔗 URL: YOU TUBE

• 📂 Slides: PRESENTATION

• ❓ Quiz: QUIZ

• Lesson 1.2: Introduction to DBMS and Relational Data Model

1. What is a DBMS?

A Database Management System (DBMS) is software that interacts with users, applications, and the database itself to capture and analyze data. It allows for the creation, retrieval, updating, and deletion of data in a database.

Functions of a DBMS:

• Data Storage, Retrieval, and Update

• User Access Control

• Data Security

• Data Integrity

• Backup and Recovery

Examples of DBMS:

• MySQL

• Oracle

• Microsoft SQL Server

• PostgreSQL

• MongoDB (NoSQL)

---

2. Components of a DBMS:

• Hardware – Physical devices.

• Software – DBMS software.

• Data – Raw data stored in the system.

• Users – Database administrators, developers, end-users.

• Procedures – Instructions and rules for database usage and management.

---

3. Types of DBMS:

1. Hierarchical DBMS

2. Network DBMS

3. Relational DBMS (RDBMS)

4. Object-Oriented DBMS

5. NoSQL DBMS

4. What is an RDBMS?

A Relational Database Management System (RDBMS) is a type of DBMS that stores data in tables (relations). Each table consists of rows and columns.

Key Features of RDBMS:

• Data stored in tables (relations)

• Uses SQL (Structured Query Language) for data operations

• Supports data integrity and normalization

• Allows relationships between tables using keys

Examples of RDBMS:

• MySQL

• Oracle

• SQL Server

• PostgreSQL

---

5. Relational Data Model

The Relational Data Model is based on the concept of relations (tables). It was introduced by Dr. E.F. Codd in 1970.

Key Concepts:

• Relation (Table): Collection of tuples (rows).

• Tuple (Row): A single record.

• Attribute (Column): A property of the entity.

• Domain: The possible values an attribute can take.

• Primary Key: Uniquely identifies each tuple in a table.

• Foreign Key: Establishes a relationship between tables.

Example Table: Student

StudentID	Name	Age	Course
101	Alice	20	B.Sc
102	Bob	22	B.Tech
103	Carol	21	B.Com

• StudentID is the Primary Key

• Each row is a tuple

• Each column is an attribute

---

6. Advantages of RDBMS:

• Easy to understand and use

• Reduces data redundancy

• Maintains data integrity

• Supports powerful query languages (like SQL)

• Allows multiple users to access the database simultaneously

---

Conclusion:

A DBMS is the backbone for managing data efficiently, while the Relational Data Model provides a logical and structured way of organizing data using tables. Together, they form the basis for most modern applications that rely on structured data.

Unit 2: SQL Statements to Create Table and Insert Data

• Lesson 2.1: Introduction to SQL and MySQL

1. What is SQL?

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases.

Purpose of SQL:

• To create and modify database structures (tables, schemas, etc.)

• To insert, retrieve, update, and delete data

• To manage user access and permissions

Common SQL Commands (CRUD Operations):

• CREATE – Create databases and tables

• INSERT – Add new data

• SELECT – Retrieve data

• UPDATE – Modify existing data

• DELETE – Remove data

---

2. Features of SQL:

• High-level language – Easy to learn and use

• Standardized – Supported by all major RDBMSs

• Declarative – Focuses on what to do, not how to do it

• Portable – Works across different systems with minimal changes

---

3. What is MySQL?

MySQL is a popular open-source Relational Database Management System (RDBMS) that uses SQL as its query language.

Key Features of MySQL:

• Free and Open-source

• Cross-platform – Runs on Windows, Linux, macOS, etc.

• Fast and Reliable – Efficient for small to large-scale applications

• Supports Client-Server Architecture

• Widely used – In websites, apps, and enterprise systems

---

4. SQL vs MySQL:

Feature	SQL	MySQL
Type	Language	Software (RDBMS)
Usage	Used to interact with databases	Uses SQL to manage its databases
Developer	ANSI/ISO standard	Originally developed by MySQL AB
Functionality	Only a language	Full database engine

---

5. Basic SQL Example in MySQL:

-- Create a table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

-- Insert data
INSERT INTO Students (StudentID, Name, Age)
VALUES (101, 'Alice', 20);

-- Retrieve data
SELECT * FROM Students;

-- Update data
UPDATE Students SET Age = 21 WHERE StudentID = 101;

-- Delete data
DELETE FROM Students WHERE StudentID = 101;

---

6. Applications of MySQL:

• Web Development (e.g., WordPress, PHP apps)

• Data Warehousing

• Content Management Systems

• E-commerce platforms

• Analytics & Reporting Tools

---

Conclusion:

SQL is the foundational language for working with databases, and MySQL is one of the most widely used platforms for implementing SQL-based solutions. Understanding both is essential for developers, data analysts, and database administrators.

• Lesson 2.2: Creating a Table and Inserting Data

1. Creating a Table in SQL

To create a table, we use the CREATE TABLE command. A table consists of columns (also called fields), each with a specific data type.

Syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

Example: Creating a Student Table

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Course VARCHAR(50)
);

Explanation:

• StudentID: Integer, set as Primary Key (unique identifier).

• Name: Text up to 50 characters.

• Age: Integer.

• Course: Text up to 50 characters.

---

2. Inserting Data into a Table

To add records to the table, use the INSERT INTO statement.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example: Insert Data into Students Table

INSERT INTO Students (StudentID, Name, Age, Course)
VALUES (101, 'Alice', 20, 'B.Sc');

INSERT INTO Students (StudentID, Name, Age, Course)
VALUES 
(102, 'Bob', 22, 'B.Tech'),
(103, 'Carol', 21, 'B.Com');

---

3. View the Data

Use the SELECT statement to view inserted records:

SELECT * FROM Students;

Output:

StudentID	Name	Age	Course
101	Alice	20	B.Sc
102	Bob	22	B.Tech
103	Carol	21	B.Com

---

4. Data Types Used in Table Creation

Data Type	Description
INT	Integer numbers
VARCHAR(n)	Variable-length text up to n chars
DATE	Stores dates (e.g., 2025-06-07)
FLOAT	Decimal numbers
BOOLEAN	TRUE or FALSE values

---

5. Common Mistakes to Avoid

• Forgetting commas between columns.

• Not matching values to columns correctly.

• Inserting string values without quotes.

• Violating primary key constraints (e.g., duplicate StudentID).

---

Conclusion:

Creating tables and inserting data are foundational skills in SQL. They allow you to define the structure of your database and begin working with real information.

Unit 3: Basic Query Formulation with SQL SELECT Query

• Lesson 3.1: Retrieving Data from a Table

1. Introduction to SELECT Statement

To retrieve data from a table in SQL, we use the SELECT statement.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name;

To select all columns:

SELECT * FROM table_name;

* means “all columns.”

---

2. Example Table: Students

StudentID	Name	Age	Course
101	Alice	20	B.Sc
102	Bob	22	B.Tech
103	Carol	21	B.Com

---

3. Examples of SELECT Queries

a) Select all data:

SELECT * FROM Students;

b) Select specific columns:

SELECT Name, Course FROM Students;

c) Rename column output using AS:

SELECT Name AS Student_Name, Course AS Program FROM Students;

---

4. Using WHERE Clause to Filter Data

Syntax:

SELECT column1, column2
FROM table_name
WHERE condition;

Examples:

• Get students older than 20:

SELECT * FROM Students
WHERE Age > 20;

• Get students enrolled in B.Sc:

SELECT * FROM Students
WHERE Course = 'B.Sc';

---

5. Using Comparison Operators

Operator	Description	Example
=	Equals	Age = 20
>	Greater than	Age > 20
<	Less than	Age < 25
!= or <>	Not equal	Course != 'B.Com'
BETWEEN	Range check	Age BETWEEN 20 AND 22
IN	Match any in list	Course IN ('B.Sc', 'B.Tech')

---

6. Sorting Results with ORDER BY

Syntax:

SELECT column1, column2
FROM table_name
ORDER BY column_name [ASC|DESC];

Example:

• Sort students by Age (ascending):

SELECT * FROM Students
ORDER BY Age ASC;

• Sort by Name (descending):

SELECT * FROM Students
ORDER BY Name DESC;

---

7. Limiting Results with LIMIT

SELECT * FROM Students
LIMIT 2;

This shows only the first 2 rows.

---

Conclusion:

The SELECT statement is the most frequently used SQL command. It allows you to retrieve exactly the data you need from one or more tables, optionally filtered, sorted, and formatted.

• Lesson 3.2: Retrieving Data Based on Conditions

Lesson Objective:

By the end of this lesson, you will be able to:

• Use the WHERE clause to filter data

• Apply comparison and logical operators

• Write SQL queries to extract records based on specific conditions

---

1. The WHERE Clause

The WHERE clause is used to filter rows in a table based on specific conditions.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

---

2. Comparison Operators

Operator	Description	Example
=	Equal to	Age = 20
>	Greater than	Age > 20
<	Less than	Age < 25
>=	Greater than or equal	Age >= 18
<=	Less than or equal	Age <= 30
!= / <>	Not equal to	Course != 'B.Sc'

---

3. Logical Operators

Operator	Description	Example
AND	All conditions must be true	Age > 18 AND Course = 'B.Tech'
OR	At least one condition is true	Course = 'B.Sc' OR Course = 'B.Com'
NOT	Reverses the condition	NOT Course = 'B.Sc'

---

4. Example Table: Students

StudentID	Name	Age	Course
101	Alice	20	B.Sc
102	Bob	22	B.Tech
103	Carol	21	B.Com
104	David	19	B.Tech
105	Eva	22	B.Sc

---

5. Query Examples

a) Students older than 20:

SELECT * FROM Students
WHERE Age > 20;

b) Students enrolled in B.Tech:

SELECT * FROM Students
WHERE Course = 'B.Tech';

c) Students aged between 20 and 22:

SELECT * FROM Students
WHERE Age BETWEEN 20 AND 22;

d) Students in B.Sc or B.Com:

SELECT * FROM Students
WHERE Course IN ('B.Sc', 'B.Com');

e) Students NOT in B.Tech:

SELECT * FROM Students
WHERE NOT Course = 'B.Tech';

f) Students older than 20 and in B.Sc:

SELECT * FROM Students
WHERE Age > 20 AND Course = 'B.Sc';

---

6. Tips & Common Mistakes

• Always use single quotes for string values: 'B.Sc', not B.Sc.

• Use = for comparing values, not ==.

• SQL is case-insensitive, but it's good practice to write keywords in UPPERCASE.

---

Conclusion:

Using WHERE, comparison, and logical operators allows you to filter the exact data you need. Mastering these basics is essential for building complex and powerful SQL queries.

• Lesson 3.3: Sorting the SELECT Query Result

Lesson Objective:

Learn how to sort the data retrieved from a database using the ORDER BY clause in SQL.

---

1. Introduction to ORDER BY

The ORDER BY clause is used to sort rows in the result set of a SELECT query.

• By default, it sorts in ascending order (ASC).

• You can also sort in descending order (DESC).

---

2. Syntax of ORDER BY

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC | DESC];

• ASC → Ascending (lowest to highest, A–Z) (default)

• DESC → Descending (highest to lowest, Z–A)

---

3. Example Table: Students

StudentID	Name	Age	Course
101	Alice	20	B.Sc
102	Bob	22	B.Tech
103	Carol	21	B.Com
104	David	19	B.Tech
105	Eva	22	B.Sc

---

4. Example Queries

a) Sort by Age (ascending):

SELECT * FROM Students
ORDER BY Age;

b) Sort by Age (descending):

SELECT * FROM Students
ORDER BY Age DESC;

c) Sort by Name (alphabetically):

SELECT * FROM Students
ORDER BY Name ASC;

d) Sort by Course then by Age:

SELECT * FROM Students
ORDER BY Course, Age;

This first sorts by Course. If two students are in the same course, it sorts those by Age.

---

5. Sorting with WHERE

You can combine WHERE and ORDER BY:

SELECT * FROM Students
WHERE Age > 20
ORDER BY Name DESC;

---

6. Common Mistakes

• Writing ORDER instead of ORDER BY

• Forgetting to specify ASC or DESC when needed

• Using column names that don’t exist

---

Conclusion:

Sorting results with ORDER BY helps organize your output in a meaningful way. It's useful for reports, data analysis, and visual presentation.

• Lesson 3.4: Select Query with Aggregate Functions Completion requirements

1. What Are Aggregate Functions?

Aggregate functions perform calculations on a set of values and return a single summary value.

Common aggregate functions include:

Function	Description
COUNT()	Counts the number of rows
SUM()	Adds up numeric values
AVG()	Calculates the average value
MIN()	Finds the minimum value
MAX()	Finds the maximum value

---

2. Key Characteristics

• Aggregate functions ignore NULL values when performing calculations.

• Often used with the GROUP BY clause to perform aggregation per group/category.

---

3. Example Queries

Assuming an Orders table:

OrderID	Customer	Amount
1	Alice	250
2	Bob	300
3	Alice	200
4	Carol	500

a) Count total rows in Orders:

SELECT COUNT(*) FROM Orders;

b) Calculate total sum of Amount:

SELECT SUM(Amount) FROM Orders;

c) Calculate average Amount:

SELECT AVG(Amount) FROM Orders;

d) Find minimum and maximum Amount:

SELECT MIN(Amount), MAX(Amount) FROM Orders;

e) Count orders per customer:

SELECT Customer, COUNT(*)
FROM Orders
GROUP BY Customer;

---

4. Additional Usage: Filtering Groups with HAVING

You can filter aggregated groups using HAVING:

-- Customers with total amount over 400
SELECT Customer, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY Customer
HAVING SUM(Amount) > 400;

---

5. Completion Requirements

To successfully complete this lesson, learners should:

• Understand the purpose and usage of each aggregate function: COUNT(), SUM(), AVG(), MIN(), and MAX().

• Write SQL queries using at least three different aggregate functions.

• Use the GROUP BY clause to group data and perform aggregations per group.

• Use the HAVING clause to filter groups based on aggregate values.

• Pass a quiz with a minimum score of 70%.

• Submit a practical assignment demonstrating queries with aggregate functions.

• Provide query output screenshots or exports as evidence.

---

6. Quick Reference Table

Function	Description	Example Query
COUNT()	Counts rows	SELECT COUNT(*) FROM Orders;
SUM()	Adds numeric values	SELECT SUM(Amount) FROM Orders;
AVG()	Calculates average	SELECT AVG(Amount) FROM Orders;
MIN()	Finds minimum value	SELECT MIN(Amount) FROM Orders;
MAX()	Finds maximum value	SELECT MAX(Amount) FROM Orders;

Unit 4: Update Data or Deleting a Record Using SQL Statements

• Lesson 4.1: Updating Data in a Table

Lesson Objective:

Learn how to modify existing records in a database table using the UPDATE statement.

---

1. What is the UPDATE Statement?

The UPDATE statement changes the values of one or more columns for existing rows in a table.

---

2. Syntax of UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• table_name: The name of the table to update.

• SET: Specifies the column(s) and new value(s).

• WHERE: Defines which rows should be updated. Without WHERE, all rows will be updated!

---

Important:

Always use a WHERE clause to avoid updating all rows unintentionally.

---

3. Example Table: Students

StudentID	Name	Age	Course
101	Alice	20	B.Sc
102	Bob	22	B.Tech
103	Carol	21	B.Com

---

4. Example Queries

a) Update a single student’s course:

UPDATE Students
SET Course = 'M.Sc'
WHERE StudentID = 101;

b) Update multiple columns for a student:

UPDATE Students
SET Age = 23, Course = 'MBA'
WHERE StudentID = 102;

c)Update all students older than 21 to a new course:

UPDATE Students
SET Course = 'Ph.D'
WHERE Age > 21;

---

5. Confirming Updates

After updating, use a SELECT query to check the changes:

SELECT * FROM Students WHERE StudentID = 101;

---

6. Common Mistakes

• Forgetting the WHERE clause and updating all rows.

• Using wrong column names or data types.

• Not checking if the update affected the intended rows.

---

Summary

• Use UPDATE to modify existing data.

• Always specify WHERE to limit affected rows.

• Multiple columns can be updated at once.
  

• Lesson 4.2: Deleting a Record from a Table

Lesson Objective:

Learn how to remove one or more records from a database table using the DELETE statement.

1. What is the DELETE Statement?

The DELETE statement is used to remove existing rows from a table.

---

2. Syntax of DELETE

DELETE FROM table_name
WHERE condition;

• table_name: The name of the table from which you want to delete rows.

• WHERE: Specifies which rows to delete.
  Without WHERE, all rows in the table will be deleted!

---

Important:

Always use a WHERE clause unless you want to delete all records in the table.

---

3. Example Table: Students

StudentID	Name	Age	Course
101	Alice	20	B.Sc
102	Bob	22	B.Tech
103	Carol	21	B.Com

---

4. Example Queries

a) Delete a single record:

DELETE FROM Students
WHERE StudentID = 102;

b) Delete all students older than 21:

DELETE FROM Students
WHERE Age > 21;

---

5. Confirming Deletions

Use a SELECT query to verify records are deleted:

SELECT * FROM Students;

---

6. Common Mistakes

• Forgetting the WHERE clause and deleting all rows accidentally.

• Deleting records without backing up important data.

• Deleting with incorrect conditions leading to unintended rows removed.

---

Summary

• DELETE removes rows from a table.

• Always use WHERE to avoid deleting all rows unintentionally.

• Confirm deletions with SELECT statements after execution.

In conclusion, understanding database management is essential for anyone working with digital systems. Whether you're a student, developer, or business professional, learning how to organize, store, retrieve, and manage data efficiently will give you a strong advantage. I hope this article helped you grasp the fundamentals of database management systems. If you found this helpful, feel free to leave a comment, share it with your friends, or explore more topics on my blog. Thank you for reading, and stay tuned for more valuable content!   

                       THANK YOU !