1.
Defining Schemas for Applications
What is a Schema in an Application?
A schema defines the logical structure of a database—how data is organized and how
relationships between data are handled. It is designed based on the application's
requirements, such as a school system, library system, e-commerce app, hospital
management, etc.
Purpose of Defining a Schema
• Identify entities (tables) and attributes (columns)
• Define relationships (foreign keys)
• Ensure data integrity
• Support the business logic of the application
Example: E-Commerce Schema
-- Users Table
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100)
);
-- Products Table
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(100),
Price DECIMAL(10,2)
);
-- Orders Table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
UserID INT,
OrderDate DATE,
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
Key Points:
• Use PRIMARY KEY and FOREIGN KEY
• Select proper data types
• Normalize to reduce redundancy
2. Creation of Database
What is a Database?
A database is an organized collection of data that can be easily accessed, managed, and
updated. It stores information in tables (rows and columns) and allows operations like
insertion, retrieval, update, and deletion using SQL (Structured Query Language).
In MySQL:
• A database is also known as a schema.
• It contains tables, views, triggers, stored procedures, etc.
How to Create a Database in MySQL
Syntax:
CREATE DATABASE database_name;
Example:
CREATE DATABASE SchoolDB;
This creates a new database called SchoolDB.
Use the Database
Before creating tables, use the database:
USE SchoolDB;
Show All Databases
SHOW DATABASES;
Delete a Database (if needed)
DROP DATABASE SchoolDB;
Summary:
• Database = container for organizing related data
• Created using CREATE DATABASE command
• Use USE database_name; to start working inside it
3. Writing SQL Queries
1. Create the Table
CREATE TABLE Students (
StudentID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(50),
Marks INT
);
2. Insert Records
INSERT INTO Students (Name, Department, Marks) VALUES
('Alice', 'CSE', 85),
('Bob', 'ECE', 78),
('Charlie', 'CSE', 92);
3. View All Records
SELECT * FROM Students;
4. Select Specific Columns
SELECT Name, Marks FROM Students;
5. Apply WHERE Clause
SELECT * FROM Students WHERE Marks > 80;
6. Update a Record
UPDATE Students
SET Marks = 90
WHERE Name = 'Bob';
7. Delete a Record
DELETE FROM Students
WHERE Name = 'Alice';
8. Sort Results
SELECT * FROM Students
ORDER BY Marks DESC;
9. Count Records
SELECT COUNT(*) FROM Students;
10. Drop the Table
DROP TABLE Students;
Summary:
This complete set covers:
• CREATE, INSERT, SELECT, WHERE, UPDATE, DELETE, ORDER BY, COUNT, and DROP
All done on a single table.
4. Retrieve Information from Database in MySQL
What is Retrieving Data?
Retrieving means fetching or extracting data stored in a database using the SELECT statement
in SQL.
It is the most commonly used operation to view records or get specific information from
one or more tables.
Basic Syntax of SELECT
SELECT column1, column2 FROM table_name;
To get all columns:
SELECT * FROM table_name;
Examples Using Students Table
Table: Students
StudentID Name Department Marks
1 Alice CSE 85
2 Bob ECE 78
3 Charlie CSE 92
1. Retrieve All Data
SELECT * FROM Students;
2. Retrieve Specific Columns
SELECT Name, Marks FROM Students;
3. Retrieve with Condition (WHERE)
SELECT * FROM Students WHERE Department = 'CSE';
4. Sorting Data (ORDER BY)
SELECT * FROM Students ORDER BY Marks DESC;
5. Using Aliases
SELECT Name AS StudentName, Marks AS Score FROM Students;
6. Get Top Scorer (LIMIT)
SELECT * FROM Students ORDER BY Marks DESC LIMIT 1;
Summary:
• Retrieving = Getting data from the database.
• Use SELECT for viewing all or filtered information.
• You can combine with WHERE, ORDER BY, LIMIT, etc., for more specific results.
5. Creating Views in MySQL
What is a View?
A View is a virtual table based on the result of a SELECT query.
• It does not store data physically.
• Used to simplify complex queries, enhance security, and present specific data to
users.
Why Use Views?
• To hide sensitive columns (like passwords)
• To simplify repeated complex joins
• To create customized data output
employee_id name salary
1 Alice 45000.00
2 Bob 60000.00
3 Charlie 55000.00
4 David 40000.00
Query:
CREATE VIEW high_earners AS
SELECT employee_id, name, salary
FROM employees
WHERE salary > 50000;
➤ Use the view:
SELECT * FROM high_earners;
Output:
employee_id name salary
2 Bob 60000.00
3 Charlie 55000.00
What it does:
• Creates a view named high_earners
• It selects only the employees whose salary > 50000 from the employees table
• Now, whenever you do:
SELECT * FROM high_earners;
You get a table showing only high-salary employees — without writing the full SELECT again.
Why use it?
• To simplify complex queries
• To create custom views for specific users
To limit access (e.g., show only selected columns)
6. Creating Triggers
What is a Trigger?
A trigger is a stored operation that runs automatically when a specific action happens on a
table, like:
• AFTER INSERT
• AFTER UPDATE
• BEFORE DELETE, etc.
Goal: Log salary updates to a log table
➤ Create log table:
CREATE TABLE salary_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
updated_at DATETIME
);
➤ Create trigger:
DELIMITER //
CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_log (employee_id, old_salary, new_salary, updated_at)
VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
END;
//
DELIMITER ;
➤ Update salary:
UPDATE employees SET salary = 70000 WHERE employee_id = 2;
Output in salary_log:
log_id employee_id old_salary new_salary updated_at
1 2 60000.00 70000.00 2025-07-14 10:00:00
What it does:
• This trigger is activated AFTER any UPDATE on the employees table
• It captures:
o OLD.salary (before update)
o NEW.salary (after update)
• Then it inserts this info into the salary_log table along with a timestamp (NOW())
Why use it?
• Audit logs (track who changed what)
• Data validation or calculations
• Automatic backups or syncs
What is a Cursor?
A cursor lets you process query results row-by-row, like a loop in programming.
Goal: Add 1000 bonus to each employee
➤ Procedure using a cursor:
DELIMITER //
CREATE PROCEDURE add_bonus()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE emp_id INT;
DECLARE emp_salary DECIMAL(10,2);
DECLARE cur CURSOR FOR SELECT employee_id, salary FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO emp_id, emp_salary;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE employees
SET salary = emp_salary + 1000
WHERE employee_id = emp_id;
END LOOP;
CLOSE cur;
END;
//
DELIMITER ;
➤ Call the procedure:
CALL add_bonus();
Output in employees:
employee_id name salary
1 Alice 41000.00
2 Bob 71000.00
3 Charlie 56000.00
What it does:
1. Declares a cursor cur to select all employee IDs and their salaries
2. Loops through each employee one row at a time
3. In each iteration:
o Adds 1000 to the employee's salary
o Updates the table
4. done is used to break the loop when no more rows are left.
Why use it?
• Useful when row-by-row logic is needed
• Not very efficient for large datasets, but sometimes necessary for complex operations
Stored Procedures
What is a Stored Procedure?
A stored procedure is a block of SQL code saved in the database that can be executed
(called) whenever needed.
Goal: Increase salary by a percentage (e.g., 10%)
➤ Create stored procedure:
DELIMITER //
CREATE PROCEDURE increase_salary(IN percent DECIMAL(5,2))
BEGIN
UPDATE employees
SET salary = salary + (salary * percent / 100);
END;
//
DELIMITER ;
➤ Call the procedure:
CALL increase_salary(10);
Output in employees:
employee_id name salary
1 Alice 45100.00
2 Bob 78100.00
3 Charlie 61600.00
What it does:
• Creates a procedure named increase_salary
• It takes an input parameter percent
• It updates all employee salaries by adding a percentage
Example: Call it with 10%
CALL increase_salary(10);
Every salary increases by 10%!
Why use it?
• To encapsulate logic
• Make your code reusable
• Improve performance for repeated tasks
• Control access to business logic
7. Normalization up to Third Normal Form (3NF)
Normalization is a process in relational database design used to eliminate redundancy and
improve data integrity. There are several normal forms, but the most commonly applied are:
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
Step-by-Step Example: Normalization to 3NF
Unnormalized Table (UNF):
Orders
OrderID | CustomerName | Items
1 | Alice | Pen, Pencil
2 | Bob | Notebook
3 | Alice | Eraser, Pen
Step 1: First Normal Form (1NF)
1NF Rule:
• Eliminate repeating groups
• Each cell must hold a single value
Orders_1NF:
OrderID | CustomerName | Item
1 | Alice | Pen
1 | Alice | Pencil
2 | Bob | Notebook
3 | Alice | Eraser
3 | Alice | Pen
Step 2: Second Normal Form (2NF)
2NF Rule:
• Be in 1NF
• Remove partial dependencies (no attribute should depend on part of a composite key)
Here, CustomerName depends only on OrderID (not on Item). So we split the table.
Orders Table:
OrderID | CustomerName
1 | Alice
2 | Bob
3 | Alice
OrderItems Table:
OrderID | Item
1 | Pen
1 | Pencil
2 | Notebook
3 | Eraser
3 | Pen
Step 3: Third Normal Form (3NF)
3NF Rule:
• Be in 2NF
• Remove transitive dependencies (non-key → non-key)
Let’s say we also had:
CustomerName | CustomerEmail
Here, CustomerEmail depends on CustomerName, not on the primary key (OrderID). So we
move customer info to a separate table.
Customers Table:
CustomerID | CustomerName | CustomerEmail
1 | Alice | alice@example.com
2 | Bob | bob@example.com
Orders Table (now with foreign key CustomerID):
OrderID | CustomerID
1 |1
2 |2
3 |1
OrderItems Table (unchanged):
OrderID | Item
1 | Pen
1 | Pencil
2 | Notebook
3 | Eraser
3 | Pen
MySQL Table Creation (3NF)
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
CustomerName VARCHAR(100),
CustomerEmail VARCHAR(100)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderItems (
OrderItemID INT PRIMARY KEY AUTO_INCREMENT,
OrderID INT,
Item VARCHAR(100),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
Summary Table
Normal Form Rule
1NF No repeating groups, atomic columns
2NF No partial dependencies on composite primary key
3NF No transitive dependencies (non-key attribute depending on another)
8. Use of Host Languages
What is a Host Language?
A host language is a general-purpose programming language (like Python, Java, C, PHP, etc.)
that is used to interact with a database system by embedding or calling SQL queries.
Why Use Host Languages?
SQL by itself cannot:
• Handle user input via UI
• Perform complex logic or loops
• Interact with web servers or external APIs
• Build desktop/web/mobile applications
So we combine SQL with host languages to:
• Fetch data from a database
• Process it using business logic
• Display it in web pages, applications, etc.
Examples of Host Languages and Their Use with SQL
Host Language Use Case
Python Web apps (Django/Flask), data analysis (pandas + SQL)
Host Language Use Case
PHP Server-side web development (e.g., WordPress, Laravel)
Java Enterprise apps, Android apps, JDBC for SQL
C/C++ System-level apps, embedded SQL using libraries
C# Windows/web apps using ADO.NET, Entity Framework
Example: Python (Host Language) + MySQL (Database)
import mysql.connector
# Connect to database
db = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="mydb"
cursor = db.cursor()
# Execute SQL query from Python
cursor.execute("SELECT name, salary FROM employees WHERE salary > 50000")
# Fetch and print results
for row in cursor.fetchall():
print(row)
cursor.close()
db.close()
Example: PHP + MySQL
<?php
$conn = new mysqli("localhost", "root", "", "mydb");
$sql = "SELECT name, salary FROM employees WHERE salary > 50000";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
echo $row["name"] . " - " . $row["salary"] . "<br>";
$conn->close();
?>
Key Benefits
• Dynamic SQL execution
• Application integration (web, mobile, desktop)
• Business logic + data handling
• User interface + database backend connection
9. Interface with Embedded SQL
What is Embedded SQL?
Embedded SQL means writing SQL queries inside a host programming language like C, C++,
Java, or COBOL.
Unlike using libraries like JDBC (Java) or MySQL Connector (Python), embedded SQL directly
integrates SQL statements into the source code using special syntax and a precompiler.
How Does It Work?
1. You write SQL statements inside your host language code.
2. A precompiler processes the SQL before compiling the host language.
3. The SQL is translated into function calls that your program can execute.
Common Host Languages Supporting Embedded SQL
Language Embedded SQL Interface
C/C++ Pro*C, Embedded SQL in ANSI C
COBOL Embedded SQL in COBOL
Java SQLJ (less common now, JDBC preferred)
Example: Embedded SQL in C (Using Pro*C)
#include <stdio.h>
/* Declare host variables */
EXEC SQL BEGIN DECLARE SECTION;
char name[30];
float salary;
EXEC SQL END DECLARE SECTION;
int main() {
/* Connect to database */
EXEC SQL CONNECT TO mydb USER 'root' IDENTIFIED BY 'password';
/* Execute a SQL query */
EXEC SQL SELECT employee_name, salary
INTO :name, :salary
FROM employees
WHERE employee_id = 101;
/* Display result */
printf("Name: %s, Salary: %.2f\n", name, salary);
/* Disconnect */
EXEC SQL COMMIT WORK RELEASE;
return 0;
This code needs a Pro*C precompiler before compiling with gcc or another C compiler.
Embedded SQL Syntax
SQL Keyword Purpose
EXEC SQL Indicates embedded SQL code
INTO Stores SQL result in C variables
:var Refers to host language variables
CONNECT Connects to the database
COMMIT Ends the connection properly
Advantages of Embedded SQL
Fast execution (precompiled)
Cleaner and more readable integration
Tight control over SQL execution
Error handling within host language
Disadvantages
Requires a precompiler (not pure C/C++)
Less portable across systems
Modern alternatives like ODBC, JDBC, ORMs (SQLAlchemy, Hibernate) are more flexible
10. Use of Forms
What is a Form?
A form is an interface element (usually on a web or GUI application) that allows users to input
data which can then be stored in a database.
In database systems, forms are used to:
• Collect data (e.g., user registration, order entry)
• Edit existing records
• Display query results in a structured format
Use of Forms in Database Applications
Purpose Example
Data Entry Insert new customer/order/product
Data Update Edit student marks or profile
Data Deletion Remove employee or item
Data Retrieval Search orders by date or status
Web Form Example (HTML + PHP + MySQL)
HTML Form
<form action="insert.php" method="post">
Name: <input type="text" name="name"><br>
Email: <input type="email" name="email"><br>
<input type="submit" value="Submit">
</form>
PHP Script to Handle Form (insert.php)
<?php
$conn = new mysqli("localhost", "root", "", "mydb");
$name = $_POST['name'];
$email = $_POST['email'];
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
$conn->query($sql);
echo "Data inserted successfully!";
$conn->close();
?>
Use of Forms in Python (Flask Example)
# app.py
from flask import Flask, request, render_template
import mysql.connector
app = Flask(__name__)
@app.route('/', methods=['GET', 'POST'])
def register():
if request.method == 'POST':
name = request.form['name']
email = request.form['email']
conn = mysql.connector.connect(host='localhost', user='root', password='pass',
database='mydb')
cursor = conn.cursor()
cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", (name, email))
conn.commit()
return "Data saved!"
return render_template('form.html')
<!-- form.html -->
<form method="POST">
Name: <input name="name"><br>
Email: <input name="email"><br>
<button type="submit">Submit</button>
</form>
Benefits of Using Forms
Easy for users to input structured data
Prevents SQL injection with validation
Front-end + back-end integration
Can use controls like dropdowns, checkboxes, radios
11. Report Writing in DBMS / MySQL
What is Report Writing?
Report writing in the context of databases refers to retrieving, formatting, and presenting
data in a meaningful and readable way — usually for decision-making, analysis, or record-
keeping.
These reports are typically generated from SQL queries, processed via a host language or
reporting tool, and presented as:
• Tables (HTML, Excel, PDF)
• Charts (bar, pie, line)
• Dashboards
Key Elements of a Report
Element Description
Title What the report is about
Date/Time When it was generated
Header Column names from database
Body Data from the database (SQL result)
Summary Total/sum/average counts if needed
Simple SQL-Based Report Example
Report: Employee Salary Report
SELECT employee_id, name, department, salary
FROM employees
ORDER BY department, salary DESC;
Output:
Employee ID Name Department Salary
101 Alice HR 60000
102 Bob Sales 55000
103 Charlie Sales 50000
104 David Tech 70000
Report with Summary (e.g., Total Salary per Department)
SELECT department, COUNT(*) AS total_employees, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
Output:
Department Total Employees Total Salary
HR 1 60000
Sales 2 105000
Tech 1 70000
Report Writing with a Host Language (Python + MySQL + PDF)
Sample Python Report (Export to PDF)
from fpdf import FPDF
import mysql.connector
# Connect to DB
conn = mysql.connector.connect(host="localhost", user="root", password="",
database="mydb")
cursor = conn.cursor()
cursor.execute("SELECT name, salary FROM employees")
# Generate PDF
pdf = FPDF()
pdf.add_page()
pdf.set_font("Arial", size=12)
pdf.cell(200, 10, txt="Employee Salary Report", ln=True, align='C')
pdf.ln(10)
for (name, salary) in cursor.fetchall():
pdf.cell(200, 10, txt=f"{name} - {salary}", ln=True)
pdf.output("salary_report.pdf")
This creates a basic PDF report with employee salary data.
Report Writing Tools (Alternative to Programming)
Tool Description
MySQL Workbench Run queries, export CSV/Excel
PHPMyAdmin Web-based MySQL admin + report
MS Access GUI for reports over tables
Crystal Reports Powerful report designer (used in companies)
JasperReports Java-based report generator
Summary
Topic Description
Purpose Present data in readable, structured format
Source SQL queries from databases
Tools SQL, Python, PHP, MySQL Workbench, etc.
Output Forms Table, PDF, Excel, Web page
