Database Systems - lab
Procedures
Objective:
To understand the concept of stored procedures in database systems.
To create and use stored procedures in a MySQL database using phpMyAdmin.
To improve database efficiency and maintainability through stored procedures.
Prerequisites:
A running MySQL server with phpMyAdmin installed.
Basic knowledge of SQL and database concepts.
Procedure:
1. Create a Database and Table:
Log into phpMyAdmin.
Create a new database (e.g., functions_lab).
Create a table within the database with the following structure:
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100),
price DECIMAL(10, 2),
quantity INT,
description TEXT
);
2. Insert Sample Data:
Insert some sample data into the products table. You can use the following SQL
query or manually enter data through phpMyAdmin:
INSERT INTO products (product_name, price, quantity,
description) VALUES
('Laptop', 999.99, 10, '15-inch laptop with 8GB RAM and
256GB SSD'),
('Smartphone', 499.99, 20, '6.5-inch smartphone with 6GB
RAM and 128GB storage');
3. Create a Stored Procedure for Insertion:
Create a stored procedure to insert a new product into the products table:
DELIMITER //
Page 1 of 7
Database Systems - lab
CREATE PROCEDURE insert_products(
IN product_name VARCHAR(100),
IN price DECIMAL(10, 2),
IN quantity INT,
IN description TEXT
)
BEGIN
INSERT INTO products (product_name, price, quantity,
description)
VALUES (product_name, price, quantity, description);
END //
DELIMITER ;
5. Create a Stored Procedure for Updating:
Create a stored procedure to update an existing product:
DELIMITER //
CREATE PROCEDURE update_product(
IN product_id INT,
IN product_name VARCHAR(100),
IN price DECIMAL(10, 2),
IN quantity INT,
IN description TEXT
)
BEGIN
UPDATE products
SET product_name = product_name,
price = price,
quantity = quantity,
description = description
WHERE product_id = product_id;
END //
DELIMITER ;
6. Call the Stored Procedure:
Call the stored procedure to update a product:
CALL update_product(1, 'Laptop', 1099.99, 3, 'Updated laptop
with enhanced specifications');
Page 2 of 7
Database Systems - lab
7. Create a Stored Procedure for Deletion:
Create a stored procedure to delete a product:
DELIMITER //
CREATE PROCEDURE delete_product(
IN product_id INT
)
BEGIN
DELETE FROM products
WHERE products.product_id = product_id;
END //
DELIMITER ;
8. Call the Stored Procedure:
Call the stored procedure to delete a product:
CALL delete_product(2);
Page 3 of 7
Database Systems - lab
Triggers
Objective:
To understand the concept of triggers in database systems.
To create and use triggers in a MySQL database using phpMyAdmin.
To apply triggers to a products table to enforce data integrity and automate tasks.
Prerequisites:
A running MySQL server with phpMyAdmin installed.
Basic knowledge of SQL and database concepts.
Procedure:
1. Create a Database and Table:
Log into phpMyAdmin.
Create a new database (e.g., functions_lab).
Create a table within the database with the following structure:
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100),
price DECIMAL(10, 2),
quantity INT,
description TEXT
);
2. Insert Sample Data:
Insert some sample data into the products table. You can use the following SQL
query or manually enter data through phpMyAdmin:
INSERT INTO products (product_name, price, quantity,
description) VALUES
('Laptop', 999.99, 10, '15-inch laptop with 8GB RAM and
256GB SSD'),
('Smartphone', 499.99, 20, '6.5-inch smartphone with 6GB
RAM and 128GB storage');
3. Create a Trigger for Price Validation:
Create a trigger that prevents negative prices from being inserted or updated:
DELIMITER //
Page 4 of 7
Database Systems - lab
CREATE TRIGGER before_products_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Price cannot be negative';
END IF;
END //
DELIMITER ;
4. Test the Trigger:
Attempt to update a product's price to a negative value. You should receive an error
message.
UPDATE products
SET price = -10.00
WHERE product_id = 1;
5. Create a Trigger for Quantity Tracking:
Create a trigger that updates a total_quantity column in another table (e.g.,
inventory) whenever a product's quantity changes:
CREATE TABLE inventory (
product_id INT,
total_quantity INT,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
insert into `inventory` (product_id, total_quantity) SELECT
product_id, quantity FROM products;
Page 5 of 7
Database Systems - lab
DELIMITER //
CREATE TRIGGER after_products_update_1
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
UPDATE inventory
SET total_quantity = NEW.quantity
WHERE product_id = OLD.product_id;
END //
DELIMITER ;
6. Test the Trigger:
Update a product's quantity and verify that the total_quantity in the inventory
table is updated accordingly.
UPDATE products
SET quantity = 50
WHERE product_id = 1;
7. Create a Trigger for Auditing:
Create a trigger that logs changes to the products table in an audit table:
CREATE TABLE product_audit (
audit_id INT PRIMARY KEY AUTO_INCREMENT,
product_id INT,
old_price DECIMAL(10, 2),
new_price DECIMAL(10, 2),
old_quantity INT,
new_quantity INT,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER //
CREATE TRIGGER after_products_update_2
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
INSERT INTO product_audit (product_id, old_price,
new_price, old_quantity, new_quantity)
VALUES (OLD.product_id, OLD.price, NEW.price,
OLD.quantity, NEW.quantity);
END //
DELIMITER ;
Page 6 of 7
Database Systems - lab
8. Test the Trigger:
Update a product's price or quantity and verify that an audit entry is created in the
product_audit table.
UPDATE products
SET price = 1000.00, quantity = 12
WHERE product_id = 1;
Page 7 of 7
