fbtest/FB.sql at master · opgrsankkar/fbtest · 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
-- 1
CREATE TABLE USERS(USER_ID NUMERIC(20) PRIMARY KEY,
	PASSWORD VARCHAR(25),
	EMAIL VARCHAR(25) UNIQUE,
	FNAME VARCHAR(25),
	LNAME VARCHAR(25),
	DOB DATE,
	DP VARCHAR(30));
-- 2
CREATE TABLE POST(POST_ID NUMERIC(20) PRIMARY KEY,
	USER_ID NUMERIC(20),
	ADD_DATE DATETIME,
	CONTENT VARCHAR(1000),
	VIEWERSHIP VARCHAR(25),FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID) ON DELETE CASCADE);
-- 3
CREATE TABLE EVENTS(EVENT_ID NUMERIC(20) PRIMARY KEY,
	USER_ID NUMERIC(20),
    EVENT_NAME VARCHAR(100),
	ADD_DATE DATETIME,
	START_DATE DATETIME,
	END_DATE DATETIME,
    EVENT_DESC VARCHAR(1000),
	VIEWERSHIP VARCHAR(25),FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID) ON DELETE CASCADE);
-- 4
CREATE TABLE PHOTOS(PHOTO_ID NUMERIC(20) PRIMARY KEY,
	POST_ID NUMERIC(20),
	USER_ID NUMERIC(20),
	ADD_DATE DATETIME,
	LINK VARCHAR(40),
	VIEWERSHIP VARCHAR(25),FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID),FOREIGN KEY (POST_ID) REFERENCES POST(POST_ID) ON DELETE CASCADE);
-- 5
CREATE TABLE SHOUT(SHOUT_ID NUMERIC(20) PRIMARY KEY,
	USER_ID NUMERIC(20),
	CONTENT VARCHAR(99),
	ADD_DATE DATETIME, FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE);
-- 6
CREATE TABLE FRIENDS(USER_ID1 NUMERIC(20),
	USER_ID2 NUMERIC(20),
	REQUEST_DATE DATETIME,
	ACCEPT_DATE DATETIME,
	PRIMARY KEY (USER_ID1,USER_ID2),
	FOREIGN KEY (USER_ID1) REFERENCES USERS(USER_ID),
	FOREIGN KEY (USER_ID2) REFERENCES USERS(USER_ID));
-- 7
CREATE TABLE POST_LIKE (USER_ID NUMERIC(20),
	POST_ID NUMERIC(20),
	LIKE_DATE DATETIME,
    PRIMARY KEY (USER_ID,POST_ID),
    FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID),FOREIGN KEY (POST_ID)REFERENCES POST(POST_ID) ON DELETE CASCADE)

CREATE TABLE ATTENDS(USER_ID NUMERIC(20) PRIMARY KEY ,
	EVENT_ID NUMERIC(20) PRIMARY KEY ,
	RSVP VARCHAR(20),FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE,FOREIGN KEY (EVENT_ID)REFERENCES EVENT(EVENT_ID) ON DELETE CASCADE);

CREATE TABLE PHOTO_LIKE (USER_ID NUMERIC(20),
	PHOTO_ID NUMERIC(20),
	PRIMARY KEY (USER_ID,PHOTO_ID),
	DATEP DATE,FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID),FOREIGN KEY (PHOTO_ID) REFERENCES PHOTOS(PHOTO_ID));

CREATE TABLE PHOTO_TAG(USER_ID NUMERIC(20) PRIMARY KEY ,
	PHOTO_ID NUMERIC(20) PRIMARY KEY,FOREIGN KEY (PHOTO_ID)REFERENCES PHOTO(PHOTO_ID),FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID));

CREATE TABLE POST_TAG(USER_ID NUMERIC(20) PRIMARY KEY ,
	POST_ID NUMERIC(20) PRIMARY KEY REFERENCES POST,FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID));