| 1 | DROP TABLE IF EXISTS ProfileMarketingRequest;
|
|---|
| 2 | DROP TABLE IF EXISTS PostMarketingRequest;
|
|---|
| 3 | DROP TABLE IF EXISTS Post;
|
|---|
| 4 | DROP TABLE IF EXISTS SocialMediaProfile;
|
|---|
| 5 | DROP TABLE IF EXISTS ServiceProvider;
|
|---|
| 6 | DROP TABLE IF EXISTS User;
|
|---|
| 7 |
|
|---|
| 8 | -- Create User table
|
|---|
| 9 | CREATE TABLE User (
|
|---|
| 10 | UserID SERIAL PRIMARY KEY,
|
|---|
| 11 | Username VARCHAR(255) NOT NULL,
|
|---|
| 12 | Email VARCHAR(255) NOT NULL,
|
|---|
| 13 | Password VARCHAR(255) NOT NULL,
|
|---|
| 14 | Contact_Details VARCHAR(255) NOT NULL
|
|---|
| 15 | );
|
|---|
| 16 |
|
|---|
| 17 | -- Create Social Media Profile table
|
|---|
| 18 | CREATE TABLE SocialMediaProfile (
|
|---|
| 19 | ProfileID SERIAL PRIMARY KEY,
|
|---|
| 20 | Platform VARCHAR(255) NOT NULL,
|
|---|
| 21 | User_Name VARCHAR(255) NOT NULL,
|
|---|
| 22 | Account_Type VARCHAR(50) NOT NULL,
|
|---|
| 23 | Followers_Count INT NOT NULL,
|
|---|
| 24 | UserID INT NOT NULL,
|
|---|
| 25 | FOREIGN KEY (UserID) REFERENCES User(UserID) ON DELETE CASCADE
|
|---|
| 26 | );
|
|---|
| 27 |
|
|---|
| 28 | -- Create Post table
|
|---|
| 29 | CREATE TABLE Post (
|
|---|
| 30 | PostID SERIAL PRIMARY KEY,
|
|---|
| 31 | Content TEXT,
|
|---|
| 32 | Post_Date TIMESTAMP,
|
|---|
| 33 | URL VARCHAR(255) NOT NULL,
|
|---|
| 34 | Likes_Count INT NOT NULL,
|
|---|
| 35 | Comments_Count INT NOT NULL,
|
|---|
| 36 | ProfileID INT NOT NULL,
|
|---|
| 37 | FOREIGN KEY (ProfileID) REFERENCES SocialMediaProfile(ProfileID) ON DELETE CASCADE
|
|---|
| 38 | );
|
|---|
| 39 |
|
|---|
| 40 | -- Create Profile Marketing Request table
|
|---|
| 41 | CREATE TABLE ProfileMarketingRequest (
|
|---|
| 42 | RequestID SERIAL PRIMARY KEY,
|
|---|
| 43 | Target_Followers INT NOT NULL,
|
|---|
| 44 | Timeline TIMESTAMP NOT NULL,
|
|---|
| 45 | ProfileID INT NOT NULL UNIQUE,
|
|---|
| 46 | Date_Created TIMESTAMP NOT NULL,
|
|---|
| 47 | Status VARCHAR(50) NOT NULL,
|
|---|
| 48 | UserID INT NOT NULL,
|
|---|
| 49 | ProviderID INT,
|
|---|
| 50 | FOREIGN KEY (ProfileID) REFERENCES SocialMediaProfile(ProfileID) ON DELETE CASCADE,
|
|---|
| 51 | FOREIGN KEY (UserID) REFERENCES User(UserID) ON DELETE CASCADE,
|
|---|
| 52 | FOREIGN KEY (ProviderID) REFERENCES ServiceProvider(ProviderID) ON DELETE SET NULL
|
|---|
| 53 | );
|
|---|
| 54 |
|
|---|
| 55 | -- Create Post Marketing Request table
|
|---|
| 56 | CREATE TABLE PostMarketingRequest (
|
|---|
| 57 | RequestID SERIAL PRIMARY KEY,
|
|---|
| 58 | Target_Likes INT NOT NULL,
|
|---|
| 59 | Target_Comments INT NOT NULL,
|
|---|
| 60 | Timeline TIMESTAMP NOT NULL,
|
|---|
| 61 | PostID INT NOT NULL UNIQUE,
|
|---|
| 62 | Date_Created TIMESTAMP NOT NULL,
|
|---|
| 63 | Status VARCHAR(50) NOT NULL,
|
|---|
| 64 | UserID INT NOT NULL,
|
|---|
| 65 | ProviderID INT,
|
|---|
| 66 | FOREIGN KEY (PostID) REFERENCES Post(PostID) ON DELETE CASCADE,
|
|---|
| 67 | FOREIGN KEY (UserID) REFERENCES User(UserID) ON DELETE CASCADE,
|
|---|
| 68 | FOREIGN KEY (ProviderID) REFERENCES ServiceProvider(ProviderID) ON DELETE SET NULL
|
|---|
| 69 | );
|
|---|
| 70 |
|
|---|
| 71 | -- Create Service Provider table
|
|---|
| 72 | CREATE TABLE ServiceProvider (
|
|---|
| 73 | ProviderID SERIAL PRIMARY KEY,
|
|---|
| 74 | Name VARCHAR(255) NOT NULL,
|
|---|
| 75 | Availability_Status BOOLEAN NOT NULL,
|
|---|
| 76 | Pricing FLOAT NOT NULL,
|
|---|
| 77 | Services_Offered TEXT NOT NULL,
|
|---|
| 78 | Contact_Email VARCHAR(255) NOT NULL
|
|---|
| 79 | );
|
|---|
