BP101T Unit 5 – Statistical Quality Control & Database Integration
Unit 5 covers statistical quality control (SQC) standards in pharmaceutical manufacturing, automated tablet weight variation testing under Indian Pharmacopoeia limits, content uniformity analysis, Student's t-test and ANOVA for batch comparison, and SQLite database management for pharmacy batch records.
📋 Prescribed Syllabus Topics
📖 Detailed Lecture Notes & Solved Examples
11. Tablet Weight Variation Testing & IP/USP Compliance Script
Dedicated Webpage➔The Indian Pharmacopoeia (IP) mandates that for solid oral dosage forms (tablets/capsules), 20 individual units must be weighed randomly from a batch. The mean weight is calculated, and individual weights are compared against permissible percentage deviations. To pass the IP test: not more than 2 individual tablets may deviate from the average weight by more than the percentage limit, and no tablet may deviate by more than twice that percentage.IP ke mutabiq batch se 20 tablets randomly toli jaati hain aur unka average nikala jata hai. Rule ye hai ki: (1) 2 se jyada tablets tay percentage limit se bahar nahi honi chahiye, aur (2) koi bhi tablet double limit se bahar nahi honi chahiye. Tablets >= 250mg ke liye limit ±5% hoti hai.
▶Automated IP Tablet Weight Variation Checker
Evaluating 20 sample Paracetamol 500mg tablets (average weight ~650mg) against IP standards.
import numpy as np
# Sample weights of 20 tablets in milligrams
sample_tablets = np.array([
648, 652, 645, 655, 649, 651, 650, 647, 658, 642,
650, 653, 646, 654, 649, 651, 655, 644, 650, 652
], dtype=float)
avg_wt = np.mean(sample_tablets)
# Determine IP percentage tolerance limit based on average weight
if avg_wt <= 80.0:
limit_pct = 10.0
elif avg_wt < 250.0:
limit_pct = 7.5
else:
limit_pct = 5.0 # For tablets >= 250 mg
dev_1x = avg_wt * (limit_pct / 100.0)
dev_2x = avg_wt * ((limit_pct * 2.0) / 100.0)
deviations = np.abs(sample_tablets - avg_wt)
count_out_1x = np.sum(deviations > dev_1x)
count_out_2x = np.sum(deviations > dev_2x)
passes_ip = (count_out_1x <= 2) and (count_out_2x == 0)
print(f"Average Tablet Weight: {avg_wt:.2f} mg")
print(f"IP Tolerance (±{limit_pct}%): ±{dev_1x:.2f} mg | Double Limit: ±{dev_2x:.2f} mg")
print(f"Tablets outside ±{limit_pct}%: {count_out_1x} (Max allowed: 2)")
print(f"Tablets outside ±{limit_pct*2}%: {count_out_2x} (Max allowed: 0)")
print(f"IP WEIGHT VARIATION RESULT: {'PASSED' if passes_ip else 'FAILED'}")
| IP Average Tablet Weight | Permissible Deviation (%) | Max Allowed Outside Limit (out of 20) | Max Allowed Outside Double Limit |
|---|---|---|---|
| 80 mg or less | ± 10.0% | 2 tablets | 0 tablets |
| More than 80 mg and less than 250 mg | ± 7.5% | 2 tablets | 0 tablets |
| 250 mg or more | ± 5.0% | 2 tablets | 0 tablets |
- •IP Weight Limits: $\le 80\text{ mg} \rightarrow \pm 10\%$; $80\text{ mg to } 250\text{ mg} \rightarrow \pm 7.5\%$; $\ge 250\text{ mg} \rightarrow \pm 5.0\%$.
- •USP Limits follow identical three-tier weight thresholds.
- •Relative Standard Deviation (%RSD): $\%RSD = \frac{\text{Standard Deviation}}{\text{Mean}} \times 100$ (Must generally be $< 2.0\%$ for tablet weight uniformity).
22. Statistical Hypothesis Testing: Student's t-test for Batch Comparison
Dedicated Webpage➔When changing raw material suppliers or modifying tableting machine speeds, quality control departments must prove that the new formulation batch exhibits identical drug content compared to reference standards. The two-sample independent Student's t-test evaluates whether the difference in mean assay between two batches is statistically significant or merely due to random experimental variance.Jab company naya raw material vendor badalti hai, toh purane aur naye batch ko compare karne ke liye Student's t-test lagaya jata hai. Agar p-value 0.05 se badi aati hai (p > 0.05), toh iska matlab dono batch me koi significant difference nahi hai aur naya raw material pass hai.
▶Two-Batch Assay Statistical Comparison Script
Using SciPy to execute an independent two-sample t-test at 95% confidence level (alpha = 0.05).
from scipy import stats
import numpy as np
# Assay percentages across 10 sample tablets from Supplier A and Supplier B
supplier_a = np.array([99.4, 98.8, 100.2, 99.1, 99.8, 100.5, 98.9, 99.6, 100.1, 99.3])
supplier_b = np.array([98.9, 99.2, 99.0, 98.7, 99.5, 99.1, 98.8, 99.3, 99.0, 98.9])
t_stat, p_val = stats.ttest_ind(supplier_a, supplier_b)
print(f"Supplier A Mean: {np.mean(supplier_a):.2f}% | Supplier B Mean: {np.mean(supplier_b):.2f}%")
print(f"Calculated t-statistic: {t_stat:.4f}")
print(f"Two-tailed p-value: {p_val:.4f}")
if p_val > 0.05:
print("Conclusion: No statistically significant difference between suppliers (p > 0.05). Both are interchangeable.")
else:
print("Conclusion: Statistically significant difference observed (p <= 0.05).")
- •Null Hypothesis ($H_0$): Mean drug content of Batch A is identical to Batch B ($\mu_A = \mu_B$).
- •Alternative Hypothesis ($H_1$): Mean drug content differs significantly ($\mu_A \ne \mu_B$).
- •Significance Threshold ($p < 0.05$): If p-value is less than 0.05, reject null hypothesis.
33. SQLite Relational Database Fundamentals for Pharmacy Inventory
Dedicated Webpage➔Enterprise pharmacy stores and quality assurance laboratories require permanent, structured relational database storage. SQLite is a serverless, self-contained, zero-configuration SQL database engine built into Python's standard library via the `sqlite3` module. It allows creating tables, inserting batch monographs, executing queries, updating stock counts, and tracking expiry dates with ACID compliance.SQLite ek lightweight database engine hai jo Python me pehle se installed rehta hai. Isme hum tables banate hain, batch number aur expiry date save karte hain, aur SQL query chala kar aane wale mahino me expire hone wali dawaiyon ki list turant nikal sakte hain.
▶Complete Pharmacy Inventory & Expiry Tracker Database Script
Creating a SQLite table, inserting records, and querying for expiring drug stocks.
import sqlite3
# Connect to database file (or memory)
conn = sqlite3.connect(":memory:") # In-memory for testing
cursor = conn.cursor()
# 1. Create Pharmacy Inventory Table
cursor.execute("""
CREATE TABLE IF NOT EXISTS inventory (
item_id INTEGER PRIMARY KEY AUTOINCREMENT,
drug_name TEXT NOT NULL,
batch_no TEXT UNIQUE,
quantity_boxes INTEGER,
mfg_date TEXT,
exp_date TEXT,
unit_price REAL
)
""")
# 2. Insert Batch Records
sample_batches = [
("Amoxicillin 500mg", "AMX2601", 150, "2026-01-10", "2028-01-10", 120.50),
("Paracetamol 650mg", "PCM2604", 500, "2026-02-15", "2029-02-15", 35.00),
("Azithromycin 500mg", "AZI2602", 80, "2025-06-01", "2026-12-01", 240.00)
]
cursor.executemany("INSERT INTO inventory (drug_name, batch_no, quantity_boxes, mfg_date, exp_date, unit_price) VALUES (?, ?, ?, ?, ?, ?)", sample_batches)
conn.commit()
# 3. Query records expiring before 2027
cursor.execute("SELECT drug_name, batch_no, exp_date, quantity_boxes FROM inventory WHERE exp_date < '2027-01-01'")
expiring_soon = cursor.fetchall()
print("--- URGENT: Batches Expiring in 2026 ---")
for row in expiring_soon:
print(f"Drug: {row[0]} | Batch: {row[1]} | Expiry: {row[2]} | Stock: {row[3]} boxes")
conn.close()
- •SQL Commands: `CREATE TABLE`, `INSERT INTO`, `SELECT ... FROM ... WHERE`, `UPDATE`, `DELETE`.
- •Parameterized queries (`?` placeholders) prevent SQL injection attacks.
- •`conn.commit()` persists modifications to disk; `conn.close()` frees database file locks.
- Memorize the 3 IP weight variation percentage limits: 10% (<=80mg), 7.5% (80-250mg), 5% (>=250mg).
- Be prepared to write a complete Python script verifying 20 tablet weights for a 10-mark practical question.