SRM INSTITUTE OF SCIENCE & TECHNOLOGY
Ramapuram, Chennai - 600 089.
Faculty of Engineering and Technology
Department of Information Technology
Sub Code: 21CSC203P-Advanced Programming Practice
CLASS: II yr/III Sem-‘A&B&C’ Sec Date:
Surprise Test-1
Q.No Mark
Question CO BL PI
. s
1. Scenario:1 Real-Time Data Validation and
Transformation
Question: You are working on a retail system
where product data is loaded in bulk from multiple
sources. Some data is incomplete or incorrect, like
prices being set to NULL or product descriptions
containing HTML tags. You need to create a
solution where, before data is inserted into the
products table, prices are set to a default value if
NULL, and HTML tags are stripped from the
descriptions. How would you achieve this using
triggers, and how would Python assist in managing
these operations? 10 5 3 4.2.1
Scenario: 2 Implementing Business Logic for
Inventory Management
Question: In an inventory management system,
each product’s stock level should be updated
whenever a new order is placed. If the stock for a
product falls below a certain threshold, an email
alert needs to be sent to the admin for reordering.
Design a solution using database triggers for stock
update and Python to handle the email
notifications.
Prepared by Approved by
SRM INSTITUTE OF SCIENCE & TECHNOLOGY
Ramapuram, Chennai - 600 089.
Faculty of Engineering and Technology
Department of Information Technology
Sub Code: 21CSC203P-Advanced Programming Practice
CLASS: II yr/III Sem-‘A&B&C’ Sec Date:
Surprise Test-1
Q.No Mark
ANSWER CO BL PI
. s
1 Scenario: 1 Real-Time Data Validation and 10 5 3 4.2.1
Transformation
Question: You are working on a retail system where
product data is loaded in bulk from multiple sources.
Some data is incomplete or incorrect, like prices being
set to NULL or product descriptions containing HTML
tags. You need to create a solution where, before data is
inserted into the products table, prices are set to a default
value if NULL, and HTML tags are stripped from the
descriptions. How would you achieve this using triggers,
and how would Python assist in managing these
operations?
Answer:
To solve this scenario, you can use a BEFORE INSERT
trigger to ensure data validation and transformation
before it gets inserted into the products table.
Trigger in SQL: You would create a BEFORE
INSERT trigger to check for NULL prices and
replace them with a default value, say 0.00.
Similarly, for descriptions, you can strip the
HTML tags before insertion.
Python's Role: In this case, Python can be used to
handle bulk loading of data and interact with the
database trigger. You could use a Python library
(like psycopg2 for PostgreSQL or PyMySQL for
MySQL) to load data. Additionally, Python can
handle the string manipulation of stripping
HTML tags using libraries like beautifulsoup4
before sending data for insertion.
Conceptually, Python would handle:
Fetching the product data.
Cleaning the descriptions if necessary.
Passing the cleaned data to the database, where
the trigger ensures any remaining validation.
Example SQL Trigger:
CREATE OR REPLACE FUNCTION
clean_product_data()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.price IS NULL THEN
NEW.price := 0.00;
SRM INSTITUTE OF SCIENCE & TECHNOLOGY
Ramapuram, Chennai - 600 089.
Faculty of Engineering and Technology
Department of Information Technology
END IF;
-- Assuming you have a PL/pgSQL function to strip
HTML tags, or you can do it in Python beforehand
NEW.description := regexp_replace(NEW.description,
'<[^>]*>', '', 'g');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER product_data_cleaner
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION clean_product_data();
Scenario: Implementing Business Logic for Inventory
Management
Question: In an inventory management system, each product’s
stock level should be updated whenever a new order is placed.
If the stock for a product falls below a certain threshold, an
email alert needs to be sent to the admin for reordering. Design
a solution using database triggers for stock update and Python
to handle the email notifications.
Answer:
To implement this solution:
SQL Trigger: You would use an AFTER INSERT
trigger on the orders table to update the stock levels in
the inventory table whenever a new order is placed.
Python for Email Notifications: When stock levels
fall below the threshold, Python can handle the email
alert by listening to the database changes. This could
be done by:
o Polling the inventory table regularly.
o Using a messaging system that is triggered
by the database.
o Directly triggering a Python script using a
database notification system like
PostgreSQL’s LISTEN/NOTIFY.
Steps:
1. Trigger for Stock Update: The trigger will adjust the
stock level in the inventory table each time a new
order is inserted.
2. Python Script for Alerts: The trigger would either
call a NOTIFY event (in PostgreSQL) or store events
in a separate table, which Python will monitor.
Example SQL Trigger:
CREATE OR REPLACE FUNCTION
update_inventory_stock()
RETURNS TRIGGER AS $$
BEGIN
UPDATE inventory
SET stock = stock - NEW.quantity
SRM INSTITUTE OF SCIENCE & TECHNOLOGY
Ramapuram, Chennai - 600 089.
Faculty of Engineering and Technology
Department of Information Technology
WHERE product_id = NEW.product_id;
IF (SELECT stock FROM inventory WHERE
product_id = NEW.product_id) < 10 THEN
PERFORM pg_notify('low_stock',
NEW.product_id::text);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_order_update_stock
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION update_inventory_stock();
Example Python Code to Handle Notifications:
import psycopg2
import select
import smtplib
# Establish database connection
conn = psycopg2.connect(dbname="mydb", user="user",
password="pass", host="localhost")
cur = conn.cursor()
# Listen for low stock notifications
cur.execute("LISTEN low_stock;")
print("Waiting for low stock alerts...")
while True:
# Check for notifications
conn.poll()
while conn.notifies:
notify = conn.notifies.pop(0)
product_id = notify.payload
print(f"Low stock alert for product {product_id}")
# Send email notification
with smtplib.SMTP('localhost') as server:
server.sendmail(
'admin@example.com',
'warehouse@example.com',
f'Subject: Low Stock Alert\n\nProduct ID
{product_id} is low on stock!')
