01_CREATE_TABLE.sql

-- Pharmaceutical Manufacturing Process Analytics
-- Database Schema and Sample Data

-- Create database
CREATE DATABASE Pharma;
GO

USE Pharma;
GO

-- Create main table for pharma manufacturing process
CREATE TABLE Pharma_Process (
    BatchID INT NOT NULL,
    ProductName VARCHAR(50) NOT NULL,
    Stage VARCHAR(20) NOT NULL,
    InputWeight_kg DECIMAL(6,2) NOT NULL,
    OutputWeight_kg DECIMAL(6,2) NOT NULL,
    Duration_hr DECIMAL(3,1) NOT NULL,
    RoomTemp_C DECIMAL(4,1),
    Humidity DECIMAL(4,1),
    Operator VARCHAR(20) NOT NULL,
    MachineID VARCHAR(10) NOT NULL,
    DeviationType VARCHAR(50),
    Date DATE NOT NULL,
    Remarks VARCHAR(50),
    
    -- Add constraints
    CONSTRAINT PK_Pharma_Process PRIMARY KEY (BatchID, Stage),
    CONSTRAINT CHK_Weight CHECK (OutputWeight_kg <= InputWeight_kg),
    CONSTRAINT CHK_Stage CHECK (Stage IN ('Granulation', 'Compression', 'Coating')),
    CONSTRAINT CHK_Temp CHECK (RoomTemp_C BETWEEN 20 AND 30),
    CONSTRAINT CHK_Humidity CHECK (Humidity BETWEEN 35 AND 60)
);
GO

-- Create indexes for better query performance
CREATE INDEX IDX_Product ON Pharma_Process(ProductName);
CREATE INDEX IDX_Stage ON Pharma_Process(Stage);
CREATE INDEX IDX_Operator ON Pharma_Process(Operator);
CREATE INDEX IDX_Machine ON Pharma_Process(MachineID);
CREATE INDEX IDX_Date ON Pharma_Process(Date);
GO

-- Sample data insert (first 30 records)
INSERT INTO Pharma_Process (BatchID, ProductName, Stage, InputWeight_kg, OutputWeight_kg, Duration_hr, RoomTemp_C, Humidity, Operator, MachineID, DeviationType, Date, Remarks)
VALUES
(4001, 'Vitamin C 100mg', 'Granulation', 190.6, 188.9, 3.1, 24, NULL, 'Frank', 'C-01', 'Capping', '2025-09-23', 'Normal run'),
(4001, 'Vitamin C 100mg', 'Compression', 157.1, 156.3, 5.4, 22.8, 53, 'Alice', 'C-01', 'Weight variation', '2025-09-20', 'Slight delay'),
(4001, 'Vitamin C 100mg', 'Coating', 169.5, 168.2, 5, 25.5, 44, 'Alice', 'CT-03', 'Uneven coating', '2025-09-10', 'Deviated yield'),
(4002, 'Omeprazole 20mg', 'Granulation', 136.9, 135.5, 4.8, 25.5, 50, 'Frank', 'CT-01', 'Capping', '2025-10-01', 'Deviated yield'),
(4002, 'Omeprazole 20mg', 'Compression', 197.9, 194.5, 5.3, 25.2, 44, 'Bob', 'C-03', 'Sticking', '2025-09-17', 'Deviated yield'),
(4002, 'Omeprazole 20mg', 'Coating', 134.9, 130.7, 3.7, 22.9, 53, 'Frank', 'CT-03', 'Sticking', '2025-09-27', 'Minor loss'),
(4003, 'Metformin 500mg', 'Granulation', 197.9, 194.5, 3.1, 23.7, NULL, 'Alice', 'G-02', 'Peeling', '2025-09-30', 'Humidity issue'),
(4003, 'Metformin 500mg', 'Compression', 156.3, 155.4, 4.7, 23.7, NULL, 'Henry', 'CT-01', 'Weight variation', '2025-09-30', 'Humidity issue'),
(4003, 'Metformin 500mg', 'Coating', 125, 122.5, 4.1, 26, NULL, 'David', 'G-01', 'Uneven coating', '2025-09-20', 'Minor loss'),
(4004, 'Amoxicillin 250mg', 'Granulation', 168.4, 162.8, 5.4, 23.3, 41, 'Bob', 'C-03', 'Weight variation', '2025-09-23', 'Within spec'),
(4004, 'Amoxicillin 250mg', 'Compression', 178.6, 176.6, 3.3, 25.5, 40, 'David', 'CT-01', 'Spillage', '2025-09-17', 'Rework required'),
(4004, 'Amoxicillin 250mg', 'Coating', 126.7, 124.8, 3.9, 24, 48, 'Isaac', 'G-03', 'Spillage', '2025-09-15', 'Humidity issue'),
(4005, 'Ibuprofen 200mg', 'Granulation', 187.3, 181.3, 5.5, 22.2, NULL, 'Isaac', 'G-03', 'Peeling', '2025-09-12', 'Minor loss'),
(4005, 'Ibuprofen 200mg', 'Compression', 150.6, 149, 5, 25.5, NULL, 'Alice', 'C-01', 'Capping', '2025-09-07', 'Within spec'),
(4005, 'Ibuprofen 200mg', 'Coating', 115.3, 114.3, 3.5, 23.1, 52, 'Charlie', 'C-03', 'Uneven coating', '2025-09-27', 'Within spec'),
(4006, 'Cetirizine 10mg', 'Granulation', 165.3, 163.3, 4.7, 25.3, NULL, 'Grace', 'CT-02', 'None', '2025-09-11', 'Normal run'),
(4006, 'Cetirizine 10mg', 'Compression', 124.4, 123.5, 3.6, 24.2, 52, 'Isaac', 'CT-03', 'Capping', '2025-09-30', 'Slight delay'),
(4006, 'Cetirizine 10mg', 'Coating', 180.4, 176.7, 5, 24.6, 45, 'Charlie', 'G-01', 'Peeling', '2025-09-28', 'Humidity issue'),
(4007, 'Paracetamol 500mg', 'Granulation', 198.1, 191.8, 3.4, 24.4, 43, 'David', 'CT-04', 'Sticking', '2025-09-01', 'Deviated yield'),
(4007, 'Paracetamol 500mg', 'Compression', 171.9, 170.3, 4, 25.5, 50, 'Henry', 'G-03', 'None', '2025-09-24', 'Rework required'),
(4007, 'Paracetamol 500mg', 'Coating', 128.5, 127, 3.4, 23.8, 43, 'Frank', 'G-01', 'Uneven coating', '2025-09-20', 'Within spec'),
(4008, 'Ibuprofen 200mg', 'Granulation', 187, 183.3, 4.1, 22.9, 50, 'Grace', 'G-03', 'Capping', '2025-09-18', 'Deviated yield'),
(4008, 'Ibuprofen 200mg', 'Compression', 147.2, 142.4, 5.5, 24.3, 45, 'Alice', 'CT-04', 'None', '2025-09-12', 'Deviated yield'),
(4008, 'Ibuprofen 200mg', 'Coating', 172.1, 170.8, 5.2, 25.8, NULL, 'Alice', 'CT-01', 'Weight variation', '2025-10-01', 'Within spec'),
(4009, 'Cetirizine 10mg', 'Granulation', 124.1, 122, 3.9, 22.6, 54, 'Henry', 'G-02', 'Weight variation', '2025-09-03', 'Normal run'),
(4009, 'Cetirizine 10mg', 'Compression', 173, 168.8, 4.4, 22.5, 46, 'Isaac', 'G-01', 'Sticking', '2025-09-11', 'Slight delay'),
(4009, 'Cetirizine 10mg', 'Coating', 171.4, 170.4, 3.8, 23.3, NULL, 'Isaac', 'G-01', 'Sticking', '2025-09-17', 'Slight delay'),
(4010, 'Omeprazole 20mg', 'Granulation', 130.1, 126.9, 3.6, 22.7, 41, 'Isaac', 'CT-02', 'None', '2025-09-10', 'Minor loss'),
(4010, 'Omeprazole 20mg', 'Compression', 130.7, 129, 3.5, 25.9, NULL, 'Bob', 'CT-04', 'Spillage', '2025-09-16', 'Normal run'),
(4010, 'Omeprazole 20mg', 'Coating', 136.7, 135.2, 3.3, 23.4, 41, 'David', 'CT-01', 'None', '2025-09-02', 'Humidity issue');
GO

-- Note: To load the complete dataset, use BULK INSERT or import from Pharma.csv file
-- Example:
-- BULK INSERT Pharma_Process
-- FROM 'e:\Ready projects\SQL\Pharma\Pharma.csv'
-- WITH (
--     FIRSTROW = 2,
--     FIELDTERMINATOR = ',',
--     ROWTERMINATOR = '\n'
-- );