CSS-475-Final-Project/SQL.sql at master · RilThunder/CSS-475-Final-Project · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
Create Database SocialMedia
Go
Use  SocialMedia
GO

Create Table ACCOUNT
(
	/*
	 SQL Server does not like 40 for PK
	*/
	Fname varchar(40) NOT NULL,
	Lname varchar(40) NOT NULL,
	NickName varchar(40) ,
	Email varchar(40) NOT NULL,
	[Password] varchar(40) NOT NULL,
	Gender varchar(40) NOT NULL,
	PRIMARY KEY (Email)
);

Create Table FRIEND
(
	UserEmail varchar(40) NOT NULL,
	FriendEmail varchar(40) NOT NULL,
	PRIMARY KEY(UserEmail,FriendEmail),
	/*
	SQL Server does not like on update cascade and on delete cascade
	Changed to NO ACTION. Kinda don't make sense if Account Deleted bu Friend still have that email
	*/
	FOREIGN KEY (UserEmail) REFERENCES ACCOUNT(Email)  on update NO ACTION on delete NO ACTION,
	FOREIGN KEY (FriendEmail) REFERENCES ACCOUNT(Email)  on update NO ACTION on delete NO ACTION,
);

create Table POST
(
	PostID int Identity(1001,1),
	Content varchar(120) NOT NULL,
	PostDate date NOT NULL,
	PostTime time NOT NULL,
	UserEmail varchar(40) NOT NULL,
	PRIMARY KEY(PostID),
	FOREIGN KEY(UserEmail) REFERENCES ACCOUNT(Email)  on update cascade on delete cascade
	);

Create Table MEDIA
(

	[Type] varchar(40) NOT NULL,
	Caption varchar(120) NOT NULL,
	PostID int NOT NULL,
	FileName varchar(40) NOT NULL,
	PRIMARY KEY(PostID,Caption),
	FOREIGN KEY (PostID) REFERENCES POST(PostID)  on update cascade on delete cascade
);

Create Table REACTION
(
	[Type] varchar(40) NOT NULL,
	PRIMARY KEY(Type)
);

Create Table POST_REACTION
(

	UserEmail varchar(40) NOT NULL,
	PostID int NOT NULL,
	ReactionType varchar(40) NOT NULL,
	PRIMARY KEY(UserEmail,PostID),
	/*
		Same as above, only accept NO ACTION
	*/
	FOREIGN KEY(UserEmail) REFERENCES ACCOUNT(Email)  on update NO ACTION on delete NO ACTION,
	FOREIGN KEY(PostID) REFERENCES POST(PostID)  on update NO ACTION on delete NO ACTION,
	FOREIGN KEY(ReactionType) REFERENCES REACTION(Type) on update NO ACTION on delete NO ACTION,
);

Create Table POST_COMMENTS
(
	PostID int NOT NULL,

	CommentTime time NOT NULL,
	CommentDate date NOT NULL,

	CommentContent varchar(40) NOT NULL,
	Commenter varchar(40) NOT NULL,
	PRIMARY KEY(PostID, CommentTime,CommentDate, Commenter ) ,
	/*
	Same NO ACTION
	*/
	FOREIGN KEY (PostID) REFERENCES POST(PostID)  on update NO ACTION on delete NO ACTION,
	FOREIGN KEY(Commenter) REFERENCES ACCOUNT(Email) on update NO ACTION on delete NO ACTION,
);

create table MESSAGE_THREAD
(
	ThreadID int Identity(1001,1),
	OwnerEmail varchar(40) NOT NULL,
	PRIMARY KEY(ThreadID),
	FOREIGN KEY(OwnerEmail) REFERENCES ACCOUNT(Email)  on update cascade on delete cascade
);

create table MESSAGE
(
	ThreadID int NOT NULL,
	Sender varchar(40) NOT NULL,
	MsgDate date NOT NULL,
	MsgTime time NOT NULL,
	Content varchar(120) NOT NULL,

	PRIMARY KEY(ThreadID,Sender,MsgDate, MsgTime),
	/*
	Same
	*/
	FOREIGN KEY(ThreadID) REFERENCES MESSAGE_THREAD(ThreadID)  on update NO ACTION on delete NO ACTION,
	FOREIGN KEY(Sender) REFERENCES ACCOUNT(Email)  on update NO ACTION on delete NO ACTION,
);

Create Table THREAD_PARTICIPANT
(
	ThreadID int NOT NULL,
	UserEmail varchar(40) NOT NULL,
	PRIMARY KEY(ThreadID,UserEmail),
	FOREIGN KEY(ThreadID) REFERENCES MESSAGE_THREAD(ThreadID)  on update NO ACTION on delete NO ACTION,
	FOREIGN KEY(UserEmail) REFERENCES ACCOUNT(Email) on update NO ACTION on delete NO ACTION
);