Create.sql

-- SQL Script for Creating Tables with Constraints
-- This script contains SQL statements to create tables for the MarketFlash project,
-- including primary keys, foreign keys, and other constraints as defined in the ERD.

CREATE TABLE departments (
    department_id VARCHAR(25) PRIMARY KEY,  -- Primary Key for unique department ID
    department VARCHAR(50) NOT NULL         -- Name of the department
);

CREATE TABLE channels (
    channel_id VARCHAR(25) PRIMARY KEY,  -- Primary Key for unique channel ID
    name VARCHAR(35) NOT NULL            -- Name of the channel (e.g., Facebook, Instagram)
);

CREATE TABLE campaign_types (
    campaign_type_id VARCHAR(25) PRIMARY KEY,  -- Primary Key for unique campaign type ID
    campaign_type VARCHAR(35) NOT NULL         -- Name of the campaign type (e.g., Sales, Sign Up)
);

CREATE TABLE locations (
    location_id VARCHAR(25) PRIMARY KEY,  -- Primary Key for unique location ID
    location VARCHAR(25) NOT NULL         -- Name of the location (e.g., USA, Europe)
);

CREATE TABLE employees (
    employee_id VARCHAR(25) PRIMARY KEY,             -- Unique identifier for each employee
    employee_name VARCHAR(35) NOT NULL,              -- Name of the employee
    address VARCHAR(255) NOT NULL,                   -- Address of the employee
    email VARCHAR(255) UNIQUE NOT NULL,              -- Email of the employee, unique to prevent duplicates
    phone VARCHAR(20) NOT NULL,                      -- Phone number of the employee
    supervisor_id VARCHAR(25),                       -- Supervisor's employee ID, self-referencing within employees table
    department_id VARCHAR(50) NOT NULL,              -- Foreign key referencing department ID
    
    FOREIGN KEY (supervisor_id) REFERENCES employees(employee_id),   -- Self-referencing foreign key for supervisors
    FOREIGN KEY (department_id) REFERENCES departments(department_id) -- References the departments table
);

CREATE TABLE clients (
    client_id VARCHAR(25) PRIMARY KEY,            -- Unique identifier for each client
    company_name VARCHAR(35) NOT NULL,            -- Name of the client's company
    address VARCHAR(255) NOT NULL,                -- Address of the client
    email VARCHAR(255) UNIQUE NOT NULL,           -- Email address of the client, unique to prevent duplicates
    phone VARCHAR(20) NOT NULL,                   -- Phone number of the client
    contact_person VARCHAR(35) NOT NULL           -- Name of the primary contact person for the client
);

CREATE TABLE campaigns (
    campaign_id VARCHAR(25) PRIMARY KEY,              -- Unique identifier for each campaign
    start_date DATE NOT NULL,                         -- Start date of the campaign
    end_date DATE NOT NULL,                           -- End date of the campaign
    channel_id VARCHAR(25) NOT NULL,                  -- Foreign key referencing the Channels table
    client_id VARCHAR(25) NOT NULL,                   -- Foreign key referencing the Clients table
    campaign_type_id VARCHAR(25) NOT NULL,            -- Foreign key referencing the Campaign_Types table
    audience VARCHAR(55) NOT NULL,                    -- Target audience description
    likes INT NOT NULL,                               -- Number of likes generated by the campaign
    clicks INT NOT NULL,                              -- Number of clicks generated by the campaign
    conversion INT NOT NULL,                          -- Number of conversions generated by the campaign
    expense FLOAT NOT NULL,                           -- Total expense of the campaign
    views INT,                                        -- Optional field for number of views
    engagement INT,                                   -- Optional field for number of engagements
    total_sales FLOAT,                                -- Optional field for total sales generated
    location_id VARCHAR(25) NOT NULL,                 -- Foreign key referencing the Locations table
    executive_id VARCHAR(25) NOT NULL,                -- Foreign key referencing the Employees table for the executive

    FOREIGN KEY (channel_id) REFERENCES channels(channel_id),         -- Foreign key relationship with Channels
    FOREIGN KEY (client_id) REFERENCES clients(client_id),             -- Foreign key relationship with Clients
    FOREIGN KEY (campaign_type_id) REFERENCES campaign_types(campaign_type_id),  -- Foreign key relationship with Campaign_Types
    FOREIGN KEY (location_id) REFERENCES locations(location_id),       -- Foreign key relationship with Locations
    FOREIGN KEY (executive_id) REFERENCES employees(employee_id)       -- Foreign key relationship with Employees
);