Module 4:-
Introduction to MySQL & Relational
Databases
Module 4
MySQL and PHP Integration
What is a Database?
Definition
A structured collection of data stored and managed systematically.
Designed to support efficient storage, retrieval, and manipulation.
Purposes
Store information securely
Serve many users at once
Maintain data integrity and consistency
Examples
Bank transactions
E-commerce products
Student records
Healthcare patient details
Types of Databases
Relational Databases (RDBMS)
Stores data in tables
Supports SQL
Examples: MySQL, Oracle, PostgreSQL
Non-Relational (NoSQL)
Stores data in documents, key-value pairs, graphs
Examples: MongoDB, Redis, Cassandra
In-Memory Databases
Very fast storage (RAM based)
Example: Redis
Why Relational Databases?
Advantages
Structured and predictable
Supports ACID properties
Ensures high security
Eliminates redundancy with normalization
Strong relationship modeling
• Used In
• Banking
• Inventory systems
• ERP & CRM
• Web applications
What is MySQL?
• Open-source RDBMS
• Multi-threaded and highly
scalable
• Used by Facebook, YouTube,
Twitter
• Supports millions of records
• Offers replication & clustering
Relational Algebra Basics
A mathematical system for working with data
All SQL operations are built from relational algebra concepts like:
• Selection
• Projection
• Join
• Union
• Intersection
• Cartesian product
What is a Table?
A table represents a real-world object
Customers
Orders
Books
Employees
Each table has:
Columns (attributes)
Rows (data entries)
Analogy:
A table is like an Excel sheet containing organized
Understanding Columns
Columns represent:
A specific type of information
Must have a unique name
Must follow data type rules
Examples of Columns:
Name → VARCHAR
Price → DECIMAL(10,2)
DOB → DATE
Quantity → INT
Understanding Rows
Rows represent:
Individual instances of data
Each row has the same columns
Example:
One customer
One product
One booking
Rows must obey constraints and keys defined by the table.
Data Types in MySQL
– INT, VARCHAR, DATE, DECIMAL,
BOOLEAN.
Type Description Example
INT Whole numbers 100
VARCHAR(n) Variable text "Sujju Rao"
CHAR(n) Fixed-length text "Y"
DECIMAL(a,b) Numbers with decimal 123.45
DATE Calendar date 2025-05-31
DATETIME Timestamp 2025-05-31 10:30
BOOLEAN True/False TRUE
What are Constraints?
Constraints enforce rules on data:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
DEFAULT
CHECK
Purpose
Prevent incorrect data
Maintain reliability
Primary Key – In Depth
Why primary keys are essential:
• Guarantee unique rows
• Used for indexing
• Used for relationships
• Prevent duplicate records
Good Primary Key Characteristics:
• Short
• Numeric
• Auto-generated
• Never changes
Auto Increment Explained
• MySQL can automatically generate numbers:
CustomerID INT AUTO_INCREMENT
PRIMARY KEY;
Benefits:
• No manual tracking
• Prevents duplicates
• Faster inserts
Foreign Key – In Depth
Foreign Key Purpose:
• Connect tables
• Enforce referential integrity
• Real-world Example:
• An order must belong to an existing customer
• Cannot create Order(CustomerID=99) if
CustomerID does not exist
Referential Integrity
Prevents:
• Orphan records
• Invalid references
• Accidental deletion of important
parent data
Relationship Types
• One-to-One
Rare
Example: Person Passport
• One-to-Many
Most common
Customer Orders
• Many-to-Many
Requires junction table
Books Orders
Junction Table Explained
Order_Items table breaks many-to-many:
OrderID ISBN Quantity
1 06723 2
1 06728 1
Advantages:
Removes duplicates
Enables quantity tracking
Supports multiple books per order
What is a Schema?
• Document showing table layout
• Shows primary keys and foreign keys
• Shows relationships
• Helps in database planning and
development
• Schema is like a blueprint of a building.
Schema Notation
• Example:
• Customers(CustomerID, Name, Address, City)
• Orders(OrderID, CustomerID, Amount, Date)
• Conventions:
• Underlined → Primary Key
• Italic → Foreign Key
Good Database Design
Must Avoid:
• Repetition
• Inconsistency
• Null-heavy columns
• Storing multiple values in one field
Must Include:
• Normalization
• Clear keys
• Efficient relationships
• Atomic attributes
Atomicity Explained
• A field should contain only one value.
• Bad:
Phone = "9988776655, 8877665544"
• Good:
Separate table:
CustomerPhones(CustomerID, Phone)
Normalization Overview
Normalization ensures:
• Reduced redundancy
• No anomalies
• Better performance
Includes:
• 1NF
• 2NF
• 3NF
• BCNF
Update/Insert/Delete Anomalies
• Insert Anomaly
Cannot insert customer without order.
• Update Anomaly
Change address in one row but forget in
others.
• Delete Anomaly
Deleting last order removes customer too.
Web Database Architecture
Path:
• Browser → Web Server → PHP Engine → MySQL
→ PHP → Browser
Each role:
• Browser: initiates request
• Server: delivers pages
• PHP: interacts with DB
• MySQL: processes queries
What is SQL?
SQL is used for:
• Defining tables (DDL)
• Managing data (DML)
• Controlling access (DCL)
• Transactions (TCL)
SQL Categories
DDL
• CREATE
DCL
• ALTER
• DROP • GRANT
• REVOKE
DML
• INSERT
TCL
• SELECT
• UPDATE • COMMIT
• DELETE • ROLLBA
CK
CRUD Operations Explained
C → Create
• Createing new records
R → Read
• Retrieve records
U → Update
• Modify existing records
D → Delete
• Remove records
• CRUD is the foundation of all DB operations.
CREATE Operation Details
Types of CREATE:
• Create table
• Create database
• Create user
INSERT rules:
• Values must match type
• Strings must be quoted
• NULL allowed only if permitted
INSERT Examples
• Examples with and without column list:
INSERT INTO Customers VALUES
(NULL,'John','Street','City');
INSERT INTO Customers (Name, City)
VALUES ('Rahul','Mumbai');
Multi-Row Insert
Why needed?
• Faster
• Reduces server load
Example:
INSERT INTO Books VALUES
('1','A','Title1',300),
('2','B','Title2',200);
INSERT Modifiers
• IGNORE
Skips duplicates
ON DUPLICATE KEY UPDATE
• Updates instead of error
• INSERT INTO Books VALUES(‘101’,…)
• ON DUPLICATE KEY UPDATE
Price=Price+10;
SELECT Operation – Deep Theory
SELECT retrieves:
• Specific columns
• Rows matching conditions
• Joined tables
• Aggregated values
Wildcard (*)
• Use cases:
• For debugging
• When needing all columns
• Avoid in production for performance
WHERE Clause
The WHERE clause filters rows.
Operators:
• Comparison
• Logical (AND, OR, NOT)
• Pattern matching (LIKE)
• Range checking (BETWEEN)
• Set checking (IN)
LIKE Pattern Matching
• Uses:
Search names
Case-insensitive matching
• Flexible filtering
Examples:
• Name LIKE ‘A%’ -- starts with A
• Name LIKE ‘%th’ -- ends with th
• Name LIKE ‘_a%’ -- second letter is a
ORDER BY Clause
– Sorts results ascending or descending.
Introduction to Joins
• Why joins?
To combine related information:
• Customers + Orders
• Orders + Books
• Employees + Departments
DELETE Operation
DELETE removes rows only.
• Rules:
• WHERE is required
• Cannot drop structure
• Triggers may fire
TRUNCATE vs DELETE
TRUNCATE:
• Faster
• Resets auto-increment
• No WHERE
• Cannot be rolled back easily
• DELETE:
• Slower
• Logs each row
• Supports WHERE
PHP & MySQL Connection
Steps:
• Validate input
• Connect to MySQL
• Prepare query
• Bind parameters
• Execute query
• Display results
Importance of Validation
Reasons:
• Prevent SQL injection
• Prevent empty fields
• Prevent malformed data
SQL
• CREATE TABLE users (
• id INT AUTO_INCREMENT PRIMARY
KEY,
• name VARCHAR(50),
• email VARCHAR(100)
• );
HTML
<!DOCTYPE html>
<html>
<body>
<form method="post" action="[Link]">
Name: <input type="text" name="name"
required><br><br>
Email: <input type="email" name="email"
required><br><br>
<input type="submit" value="Submit">
</form>
</body>
</html>
PHP
<?php
$conn = new mysqli("localhost", "root", "", "testdb");
if ($conn->connect_error) {
die("Connection failed");
}
$name = $_POST['name'];
$email = $_POST['email'];
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if ($conn->query($sql) === TRUE) {
echo "Data saved successfully";
} else {
echo "Error saving data";
}
$conn->close();
?>