RelationalDesign: ddlScript.sql

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