poultry_schema.sql

CREATE TABLE IF NOT EXISTS `Cages`
(
    cageId INT NOT NULL AUTO_INCREMENT,
    noOfBirds INT DEFAULT 0,
    PRIMARY KEY (`cageId`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `Customer`
(
    `customerId` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT NULL,
    `age` INT DEFAULT NULL,
    `businessType` VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (`customerId`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `Employee`
(
    `employeeId` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT NULL,
    `age` INT DEFAULT NULL,
    `joinedDate` DATE DEFAULT NULL,
    `salary` FLOAT NOT NULL,
    `position` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`employeeId`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `Sales`
(
    `id` INT NOT NULL AUTO_INCREMENT,
    `customerId` INT NOT NULL,
    `employeeId` INT NOT NULL,
    `noOfEggsSold` INT NOT NULL,
    `price` FLOAT NOT NULL,
    `date` DATE DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`customerId`) REFERENCES Customer(`customerId`),
    FOREIGN KEY (`employeeId`) REFERENCES Employee(`employeeId`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `Products`
(
    `productId` INT NOT NULL AUTO_INCREMENT,
    `productBought` VARCHAR(100) NOT NULL,
    `unitPrice` FLOAT NOT NULL,
    `isDiscount` TEXT DEFAULT NULL,
    `quantity` INT NOT NULL,
    `date` DATE NOT NULL,
    PRIMARY KEY (`productId`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `Inventory`
(
    `id` INT NOT NULL AUTO_INCREMENT,
    `productId` INT NOT NULL,
    `type` VARCHAR(20) DEFAULT NULL,
    `date` DATE NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`productId`) REFERENCES Products(`productId`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `DailyEggCount` 
(
    `id` INT NOT NULL AUTO_INCREMENT,
    `date` DATE NOT NULL,
    `feedQuantity` FLOAT NOT NULL,
    `eggsLayed` INT DEFAULT NULL,
    `noOfCracked` INT DEFAULT NULL,
    `cageId` INT NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`cageId`) REFERENCES Cages(`cageId`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;