-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcrebas.sql
171 lines (136 loc) · 6.36 KB
/
crebas.sql
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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
/*==============================================================*/
/* DBMS name: Sybase SQL Anywhere 12 */
/* Created on: 15/10/2020 12:03:46 */
/*==============================================================*/
--drop database if exists BookFace
--drop table if exists "COMMENT";
--drop table if exists DEVICE;
--drop table if exists FRIENSHIP;
--drop table if exists INTERACTION;
--drop table if exists POST;
--drop table if exists TYPE;
--drop table if exists "USER";
create database BookFace
go
use BookFace
go
/*==============================================================*/
/* Table: "COMMENT" */
/*==============================================================*/
create table "COMMENT"
(
COMMENTID int IDENTITY(1,1) not null,
USERID int not null,
POSTID int not null,
DEVICEID int not null,
DEVICEIP varchar(15) not null,
COMMENTDATETIME datetime not null,
COMMENTCONTENT varchar(200) not null,
ACTIVESTATUS bit default 0 not null,
constraint PK_COMMENT primary key clustered (COMMENTID)
);
/*==============================================================*/
/* Table: DEVICE */
/*==============================================================*/
create table DEVICE
(
DEVICEID int IDENTITY(1,1) not null,
DEVICENAME varchar(50) not null,
constraint PK_DEVICE primary key clustered (DEVICEID)
);
/*==============================================================*/
/* Table: FRIENDSHIP */
/*==============================================================*/
create table FRIENDSHIP
(
FRIENDSHIPID int IDENTITY(1,1) not null,
USERID int not null,
FRIENDID int not null,
constraint PK_FRIENDSHIP primary key clustered (FRIENDSHIPID)
);
/*==============================================================*/
/* Table: INTERACTION */
/*==============================================================*/
create table INTERACTION
(
INTERACTIONID int IDENTITY(1,1) not null,
USERID int not null,
POSTID int not null,
DEVICEID int not null,
DEVICEIP varchar(15) not null,
INTERACTIONDATETIME datetime not null,
ISLIKE bit not null,
constraint PK_INTERACTION primary key clustered (INTERACTIONID)
);
/*==============================================================*/
/* Table: POST */
/*==============================================================*/
create table POST
(
POSTID int IDENTITY(1,1) not null,
USERID int not null,
TYPEID int not null,
DEVICEID int not null,
DEVICEIP varchar(15) not null,
POSTDATETIME datetime not null,
POSTCONTENT varchar(200) not null,
constraint PK_POST primary key clustered (POSTID)
);
/*==============================================================*/
/* Table: TYPE */
/*==============================================================*/
create table TYPE
(
TYPEID int IDENTITY(1,1) not null,
TYPEPOST varchar(50) not null,
constraint PK_TYPE primary key clustered (TYPEID)
);
/*==============================================================*/
/* Table: "USER" */
/*==============================================================*/
create table "USER"
(
USERID int IDENTITY(1,1) not null,
FIRSTNAME varchar(50) not null,
MIDDLENAME varchar(50) null,
FIRSTLASTNAME varchar(50) not null,
SECONDLASTNAME varchar(50) null,
EMAILADDRESS varchar(50) UNIQUE not null,
BIRTHDATE date not null,
MAXFRIENDS int default 50 not null,
REGISTRATIONDATE datetime default GETDATE() not null,
constraint PK_USER primary key clustered (USERID)
);
alter table "COMMENT"
add constraint FK_COMMENT_REFERENCE_DEVICE foreign key (DEVICEID)
references DEVICE (DEVICEID)
alter table "COMMENT"
add constraint FK_COMMENT_REFERENCE_POST foreign key (POSTID)
references POST (POSTID)
alter table "COMMENT"
add constraint FK_COMMENT_REFERENCE_USER foreign key (USERID)
references "USER" (USERID)
alter table FRIENDSHIP
add constraint FK_FRIENDSHIP_REFERENCE_USER foreign key (USERID)
references "USER" (USERID)
alter table FRIENDSHIP
add constraint FK_FRIENDSHIP_REFERENCE_FRIEND foreign key (FRIENDID)
references "USER" (USERID)
alter table INTERACTION
add constraint FK_INTERACT_REFERENCE_POST foreign key (POSTID)
references POST (POSTID)
alter table INTERACTION
add constraint FK_INTERACT_REFERENCE_USER foreign key (USERID)
references "USER" (USERID)
alter table POST
add constraint FK_POST_REFERENCE_DEVICE foreign key (DEVICEID)
references DEVICE (DEVICEID)
alter table POST
add constraint FK_POST_REFERENCE_USER foreign key (USERID)
references "USER" (USERID)
alter table POST
add constraint FK_POST_REFERENCE_TYPE foreign key (TYPEID)
references TYPE (TYPEID)
alter table INTERACTION
add constraint FK_INTERACT_REFERENCE_DEVICE foreign key (DEVICEID)
references DEVICE (DEVICEID)