create.sql

-- Created by Redgate Data Modeler (https://datamodeler.redgate-platform.com)
-- Last modification date: 2025-11-29 18:05:01.178

-- tables
-- Table: Administrator
CREATE TABLE Administrator (
    aid int  NOT NULL,
    name text  NOT NULL,
    email text  NOT NULL,
    role text  NOT NULL,
    CONSTRAINT Administrator_pk PRIMARY KEY (aid)
);

-- Table: Brand
CREATE TABLE Brand (
    brand_id int  NOT NULL,
    brand_name text  NOT NULL,
    description text  NOT NULL,
    CONSTRAINT Brand_pk PRIMARY KEY (brand_id)
);

-- Table: Card
CREATE TABLE Card (
    method_id int  NOT NULL,
    card_number int  NOT NULL,
    card_network text  NOT NULL,
    pin int  NOT NULL,
    owner_name text  NOT NULL,
    CONSTRAINT Card_pk PRIMARY KEY (method_id)
);

-- Table: Category
CREATE TABLE Category (
    category_id int  NOT NULL,
    name text  NOT NULL,
    description text  NOT NULL,
    CONSTRAINT Category_pk PRIMARY KEY (category_id)
);

-- Table: CategoryBrand
CREATE TABLE CategoryBrand (
    category_id int  NOT NULL,
    brand_id int  NOT NULL,
    CONSTRAINT CategoryBrand_pk PRIMARY KEY (category_id,brand_id)
);

-- Table: Customer
CREATE TABLE Customer (
    cid int  NOT NULL,
    name text  NOT NULL,
    email text  NOT NULL,
    phone int  NOT NULL,
    registration_date date  NOT NULL,
    status text  NOT NULL CHECK (status IN ('inactive', 'active')),
    CONSTRAINT Customer_pk PRIMARY KEY (cid)
);

-- Table: Gift
CREATE TABLE Gift (
    gift_id int  NOT NULL,
    sender_id int  NOT NULL,
    receiver_id int  NOT NULL,
    offer_id int  NOT NULL,
    CONSTRAINT noteq CHECK (sender_id <> receiver_id) NOT DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT Gift_pk PRIMARY KEY (gift_id)
);

-- Table: Loyalty_Account
CREATE TABLE Loyalty_Account (
    method_id int  NOT NULL,
    amount decimal(100,2)  NOT NULL,
    expiry_date date  NOT NULL,
    CONSTRAINT Loyalty_Account_pk PRIMARY KEY (method_id)
);

-- Table: Merchant
CREATE TABLE Merchant (
    mid int  NOT NULL,
    aid int  NOT NULL,
    name text  NOT NULL,
    status text  NOT NULL CHECK (status IN ('inactive', 'active')),
    registration_date date  NOT NULL,
    business_type text  NOT NULL,
    pin_code int  NOT NULL,
    address text  NOT NULL,
    contact_number int  NOT NULL,
    CONSTRAINT Merchant_pk PRIMARY KEY (mid)
);

-- Table: Offer
CREATE TABLE Offer (
    offer_id int  NOT NULL,
    mid int  NOT NULL,
    title text  NOT NULL,
    description text  NOT NULL,
    discount_value decimal(100,2)  NOT NULL,
    original_price decimal(100,2)  NOT NULL,
    status text  NOT NULL CHECK (status IN ('inactive', 'active')),
    valid_from date  NOT NULL,
    valid_to date  NOT NULL,
    max_redemptions int  NOT NULL,
    total_redemptions int  NOT NULL,
    max_per_customer int  NOT NULL,
    daily_cap int  NOT NULL,
    terms_and_conditions text  NOT NULL,
    CONSTRAINT Offer_pk PRIMARY KEY (offer_id)
);

-- Table: OfferCategory
CREATE TABLE OfferCategory (
    offer_id int  NOT NULL,
    category_id int  NOT NULL,
    CONSTRAINT OfferCategory_pk PRIMARY KEY (offer_id,category_id)
);

-- Table: Payment_method
CREATE TABLE Payment_method (
    method_id int  NOT NULL,
    customer_id int  NOT NULL,
    CONSTRAINT Payment_method_pk PRIMARY KEY (method_id)
);

-- Table: Redemption
CREATE TABLE Redemption (
    rid int  NOT NULL,
    merchant_id int  NOT NULL,
    customer_id int  NOT NULL,
    offer_id int  NOT NULL,
    savings_amount decimal(100,2)  NOT NULL,
    redemption_date date  NOT NULL,
    status text  NOT NULL CHECK (status IN ('pending', 'completed', 'cancelled')),
    pin_entered int  NOT NULL,
    CONSTRAINT Redemption_pk PRIMARY KEY (rid)
);

-- Table: Save
CREATE TABLE Save (
    offer_id int  NOT NULL,
    customer_id int  NOT NULL,
    CONSTRAINT Save_pk PRIMARY KEY (offer_id,customer_id)
);

-- foreign keys
-- Reference: Brand_CategoryBrand (table: CategoryBrand)
ALTER TABLE CategoryBrand ADD CONSTRAINT Brand_CategoryBrand
    FOREIGN KEY (brand_id)
    REFERENCES Brand (brand_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Category_CategoryBrand (table: CategoryBrand)
ALTER TABLE CategoryBrand ADD CONSTRAINT Category_CategoryBrand
    FOREIGN KEY (category_id)
    REFERENCES Category (category_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Category_OfferCategory (table: OfferCategory)
ALTER TABLE OfferCategory ADD CONSTRAINT Category_OfferCategory
    FOREIGN KEY (category_id)
    REFERENCES Category (category_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Customer_Payment_method (table: Payment_method)
ALTER TABLE Payment_method ADD CONSTRAINT Customer_Payment_method
    FOREIGN KEY (customer_id)
    REFERENCES Customer (cid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Customer_Redemptions (table: Redemption)
ALTER TABLE Redemption ADD CONSTRAINT Customer_Redemptions
    FOREIGN KEY (customer_id)
    REFERENCES Customer (cid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Gift_Customer1 (table: Gift)
ALTER TABLE Gift ADD CONSTRAINT Gift_Customer1
    FOREIGN KEY (receiver_id)
    REFERENCES Customer (cid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Gift_Customer2 (table: Gift)
ALTER TABLE Gift ADD CONSTRAINT Gift_Customer2
    FOREIGN KEY (sender_id)
    REFERENCES Customer (cid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Manager (table: Merchant)
ALTER TABLE Merchant ADD CONSTRAINT Manager
    FOREIGN KEY (aid)
    REFERENCES Administrator (aid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Offer_Gift (table: Gift)
ALTER TABLE Gift ADD CONSTRAINT Offer_Gift
    FOREIGN KEY (offer_id)
    REFERENCES Offer (offer_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Offer_OfferCategory (table: OfferCategory)
ALTER TABLE OfferCategory ADD CONSTRAINT Offer_OfferCategory
    FOREIGN KEY (offer_id)
    REFERENCES Offer (offer_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Offers (table: Offer)
ALTER TABLE Offer ADD CONSTRAINT Offers
    FOREIGN KEY (mid)
    REFERENCES Merchant (mid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Payment_method_Card (table: Card)
ALTER TABLE Card ADD CONSTRAINT Payment_method_Card
    FOREIGN KEY (method_id)
    REFERENCES Payment_method (method_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Payment_method_Loyalty_Account (table: Loyalty_Account)
ALTER TABLE Loyalty_Account ADD CONSTRAINT Payment_method_Loyalty_Account
    FOREIGN KEY (method_id)
    REFERENCES Payment_method (method_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Redemed_Offers (table: Redemption)
ALTER TABLE Redemption ADD CONSTRAINT Redemed_Offers
    FOREIGN KEY (offer_id)
    REFERENCES Offer (offer_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Redemptions (table: Redemption)
ALTER TABLE Redemption ADD CONSTRAINT Redemptions
    FOREIGN KEY (merchant_id)
    REFERENCES Merchant (mid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Save_Customer (table: Save)
ALTER TABLE Save ADD CONSTRAINT Save_Customer
    FOREIGN KEY (customer_id)
    REFERENCES Customer (cid)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: Save_Offer (table: Save)
ALTER TABLE Save ADD CONSTRAINT Save_Offer
    FOREIGN KEY (offer_id)
    REFERENCES Offer (offer_id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- End of file.