-
Notifications
You must be signed in to change notification settings - Fork 0
/
OnsetDB.sql
152 lines (130 loc) · 4.83 KB
/
OnsetDB.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
CREATE DATABASE ONSETDB
USE ONSETDB
CREATE TABLE EMPLOYEES (
[EMPLOYEE ID] INT IDENTITY (1, 1) PRIMARY KEY,
[EMPLOYEE FIRSTNAME] NVARCHAR (50) NOT NULL,
[EMPLOYEE LASTNAME] NVARCHAR (50) NOT NULL,
[EMPLOYEE EMAIL] NVARCHAR (320) UNIQUE NOT NULL,
[EMPLOYEE DESIGNATION] NVARCHAR (50) NOT NULL,
[EMPLOYEE ORGANIZATION] NVARCHAR (100) NOT NULL,
[EMPLOYEE PHONE] VARCHAR (20) NOT NULL,
[EMPLOYEE GENDER] VARCHAR (8) NOT NULL,
[EMPLOYEE DATEOFBIRTH] DATE NOT NULL,
[EMPLOYEE LOCATION] NVARCHAR (200) NOT NULL,
[EMPLOYEE REGISTRATIONDATE] DATE NOT NULL
)
CREATE TABLE MANAGERS (
[MANAGER ID] INT IDENTITY (1, 1) PRIMARY KEY,
[MANAGER FIRSTNAME] NVARCHAR (50) NOT NULL,
[MANAGER LASTNAME] NVARCHAR (50) NOT NULL,
[MANAGER EMAIL] NVARCHAR (320) UNIQUE NOT NULL,
[MANAGER DESIGNATION] NVARCHAR (50) NOT NULL,
[MANAGER ORGANIZATION] NVARCHAR (100) NOT NULL,
[MANAGER PHONE] VARCHAR (20) NOT NULL,
[MANAGER GENDER] VARCHAR (8) NOT NULL,
[MANAGER DATEOFBIRTH] DATE NOT NULL,
[MANAGER LOCATION] NVARCHAR (200) NOT NULL,
[MANAGER REGISTRATIONDATE] DATE NOT NULL
)
CREATE TABLE ADMINS (
[ADMIN ID] INT IDENTITY (1, 1) PRIMARY KEY,
[ADMIN NAME] NVARCHAR (100) NOT NULL,
[ADMIN EMAIL] NVARCHAR (320) UNIQUE NOT NULL,
[ADMIN PASSWORD] VARCHAR (50) NOT NULL
)
CREATE TABLE TASKS (
[TASK ID] INT IDENTITY (1, 1) PRIMARY KEY,
[TASK NAME] NVARCHAR (500) NOT NULL,
[TASK CATEGORY] NVARCHAR (200) NOT NULL,
[TASK DESCRIPTION] NVARCHAR (1000) NOT NULL,
[TASK DEADLINE] DATETIME NOT NULL
)
CREATE TABLE PROGRESSES (
[PROGRESS ID] INT IDENTITY (1, 1) PRIMARY KEY,
[PROGRESS DESCRIPTION] NVARCHAR (1000) NOT NULL,
[PROGRESS PERCENTAGE] FLOAT (24) NOT NULL
)
CREATE TABLE EVENTS (
[EVENT ID] INT IDENTITY (1, 1) PRIMARY KEY,
[EVENT NAME] NVARCHAR (100) NOT NULL,
[EVENT CATEGORY] NVARCHAR (200) NOT NULL,
[EVENT DESCRIPTION] NVARCHAR (1000) NOT NULL,
[EVENT DEADLINE] DATETIME NOT NULL
)
CREATE TABLE REPORTS (
[REPORT ID] INT IDENTITY (1, 1) PRIMARY KEY,
[REPORT BODY] NVARCHAR (1000) NOT NULL
)
CREATE TABLE ROOMS (
[ROOM ID] INT IDENTITY (1, 1) PRIMARY KEY,
[ROOM NAME] NVARCHAR (255) NOT NULL
)
CREATE TABLE USERS (
[USER ID] INT IDENTITY (1, 1) PRIMARY KEY,
[USER EMAIL] NVARCHAR (350) UNIQUE NOT NULL,
[USER PASSWORD] VARCHAR (50) NOT NULL,
[USER TYPE] VARCHAR (20) NOT NULL
)
CREATE TABLE USERBIOS (
[USERBIO ID] INT IDENTITY (1, 1) PRIMARY KEY,
[USER ID] INT FOREIGN KEY REFERENCES USERS ([USER ID]) NOT NULL,
[USER BIO] NVARCHAR (1000) NOT NULL
)
CREATE TABLE USERPICS (
[USERPIC ID] INT IDENTITY (1, 1) PRIMARY KEY,
[USER ID] INT FOREIGN KEY REFERENCES USERS ([USER ID]) NOT NULL,
[USER PIC] NVARCHAR (1000) NOT NULL
)
CREATE TABLE TASKMANAGERS (
[TASKMANAGER ID] INT IDENTITY (1, 1) PRIMARY KEY,
[TASK ID] INT FOREIGN KEY REFERENCES TASKS ([TASK ID]) NOT NULL,
[MANAGER ID] INT FOREIGN KEY REFERENCES MANAGERS ([MANAGER ID]) NOT NULL
)
CREATE TABLE TASKEMPLOYEES (
[TASKEMPLOYEE ID] INT IDENTITY (1, 1) PRIMARY KEY,
[TASK ID] INT FOREIGN KEY REFERENCES TASKS ([TASK ID]) NOT NULL,
[EMPLOYEE ID] INT FOREIGN KEY REFERENCES EMPLOYEES ([EMPLOYEE ID]) NOT NULL
)
CREATE TABLE TASKFILES (
[TASKFILE ID] INT IDENTITY (1, 1) PRIMARY KEY,
[TASK ID] INT FOREIGN KEY REFERENCES TASKS ([TASK ID]) NOT NULL,
[FILES] NVARCHAR (500) NOT NULL
)
CREATE TABLE TASKPROGRESSES (
[TASKPROGRESS ID] INT IDENTITY (1, 1) PRIMARY KEY,
[TASK ID] INT FOREIGN KEY REFERENCES TASKS ([TASK ID]) NOT NULL,
[PROGRESS ID] INT FOREIGN KEY REFERENCES PROGRESSES ([PROGRESS ID]) NOT NULL
)
CREATE TABLE PROGRESSEMPLOYEES (
[PROGRESSEMPLOYEE ID] INT IDENTITY (1, 1) PRIMARY KEY,
[PROGRESS ID] INT FOREIGN KEY REFERENCES PROGRESSES ([PROGRESS ID]) NOT NULL,
[EMPLOYEE ID] INT FOREIGN KEY REFERENCES EMPLOYEES ([EMPLOYEE ID]) NOT NULL
)
CREATE TABLE PROGRESSFILES (
[PROGRESSFILE ID] INT IDENTITY (1, 1) PRIMARY KEY,
[PROGRESS ID] INT FOREIGN KEY REFERENCES PROGRESSES ([PROGRESS ID]) NOT NULL,
[FILES] NVARCHAR (500) NOT NULL
)
CREATE TABLE EVENTUSERS (
[EVENTUSER ID] INT IDENTITY (1, 1) PRIMARY KEY,
[EVENT ID] INT FOREIGN KEY REFERENCES EVENTS ([EVENT ID]) NOT NULL,
[USER ID] INT FOREIGN KEY REFERENCES USERS ([USER ID]) NOT NULL
)
CREATE TABLE REPORTUSERS (
[REPORTUSER ID] INT IDENTITY (1, 1) PRIMARY KEY,
[REPORT ID] INT FOREIGN KEY REFERENCES REPORTS ([REPORT ID]) NOT NULL,
[USER ID BY] INT FOREIGN KEY REFERENCES USERS ([USER ID]) NOT NULL,
[USER ID FOR] INT FOREIGN KEY REFERENCES USERS ([USER ID]) NOT NULL
)
CREATE TABLE CHATROOMS (
[CHATROOM ID] INT IDENTITY (1, 1) PRIMARY KEY,
[ROOM ID] INT FOREIGN KEY REFERENCES ROOMS ([ROOM ID]) NOT NULL,
[USER ID] INT FOREIGN KEY REFERENCES USERS ([USER ID]) NOT NULL,
[ISREAD] INT NOT NULL
)
CREATE TABLE CHATMESSAGES (
[CHATMESSAGE ID] INT IDENTITY (1, 1) PRIMARY KEY,
[ROOM ID] INT FOREIGN KEY REFERENCES ROOMS ([ROOM ID]) NOT NULL,
[USER ID] INT FOREIGN KEY REFERENCES USERS ([USER ID]) NOT NULL,
[MESSAGE] NVARCHAR (1000) NOT NULL
)