CREATE TABLE Employees (
Emp_ID INT PRIMARY KEY,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Address VARCHAR(255),
Email VARCHAR(255),
Phone VARCHAR(20),
Supervisor INT,
FOREIGN KEY (Supervisor) REFERENCES Employees(Emp_ID)
); --Employee table
CREATE TABLE Clients (
Client_ID INT PRIMARY KEY,
CompanyName VARCHAR(255),
Address VARCHAR(255),
Email VARCHAR(255),
Phone VARCHAR(20),
ContactPerson VARCHAR(255)
); --Clients table
CREATE TABLE Campaigns (
Campaign_ID INT PRIMARY KEY,
Campaign_Name VARCHAR(255),
Manager_ID INT,
StartDate DATE,
EndDate DATE,
Budget DECIMAL(10,2),
Client_ID INT,
FOREIGN KEY (Manager_ID) REFERENCES Employees(Emp_ID)
FOREIGN KEY (Client_ID) REFERENCES Clients(Client_ID)
); --Campaigns table
CREATE TABLE Platforms (
Platform_ID INT PRIMARY KEY,
Platform_Name VARCHAR(255),
URL VARCHAR(255),
Contact_Person VARCHAR(255),
Contact_Phone VARCHAR(20),
Contact_Email VARCHAR(255),
Campaign_ID INT,
FOREIGN KEY (Campaign_ID) REFERENCES Campaigns(Campaign_ID)
); --Platforms table
CREATE TABLE Advertisements (
Ad_ID INT PRIMARY KEY,
Ad_Name VARCHAR(255),
Type VARCHAR(255),
Duration INT,
Budget DECIMAL(10,2),
Platform_ID INT,
Campaign_ID INT,
FOREIGN KEY (Platform_ID) REFERENCES Platforms(Platform_ID),
FOREIGN KEY (Campaign_ID) REFERENCES Campaigns(Campaign_ID)
); --Advertisements table
CREATE TABLE Influencers (
Influencer_ID INT PRIMARY KEY,
Influencer_Name VARCHAR(255),
Social_Handle VARCHAR(255),
Follower_Count BIGINT,
Email VARCHAR(255),
Phone VARCHAR(20),
Campaign_ID INT,
FOREIGN KEY (Campaign_ID) REFERENCES Campaigns(Campaign_ID)
); --Influencers table
CREATE TABLE Metrics (
Metric_ID INT PRIMARY KEY,
Impressions INT,
Clicks INT,
Engagement INT,
Conversion_Rate DECIMAL (5,2),
Campaign_ID INT,
FOREIGN KEY (Campaign_ID) REFERENCES Campaigns(Campaign_ID)
); --Metrics table
CREATE TABLE Contents (
Content_ID INT PRIMARY KEY,
Title VARCHAR(255),
Description VARCHAR(255),
Media_Type VARCHAR(255),
Creation_Date DATE,
Campaign_ID INT,
FOREIGN KEY (Campaign_ID) REFERENCES Campaigns(Campaign_ID)
); --Contents table
CREATE TABLE Payments (
Payment_ID INT PRIMARY KEY,
Date DATE,
Amount DECIMAL (10,2),
Payment_Type VARCHAR(255),
Campaign_ID INT,
FOREIGN KEY (Campaign_ID) REFERENCES Campaigns(Campaign_ID)
); --Payments table