/
BBLABS.sql
138 lines (124 loc) · 3.43 KB
/
BBLABS.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
Use Master
Go
If Exists (Select * from sys.databases where name = 'BBLABS')
DROP DATABASE BBLABS
Go
Create Database BBLABS
Go
Use BBLABS
Go
-- --------------------------------------------------------
--
-- Table structure for table `bb_user`
--
--
-- Table structure for table contact
--
CREATE TABLE tblBLOB
(
BLOBID int IDENTITY PRIMARY KEY,
BLOBData IMAGE NOT NULL,
BLOBName varchar(30) DEFAULT NULL
);
CREATE TABLE contact (
CONTACT_ID int IDENTITY PRIMARY KEY,
STUDENT_NUMBER varchar(8) DEFAULT NULL,
PERSONAL_NUMBER varchar(13) DEFAULT NULL,
ID_NUMBER varchar(13) DEFAULT NULL,
CELL_PHONE varchar(10) DEFAULT NULL,
EMAIL varchar(40) DEFAULT NULL,
TEL_NUMBER varchar(10) DEFAULT NULL,
ADDRESS varchar(100) DEFAULT NULL
);
-- --------------------------------------------------------
CREATE TABLE bb_user (
USER_ID int IDENTITY PRIMARY KEY,
USERNAME varchar(30) NOT NULL,
PASSWORD varchar(20) NOT NULL,
ACCESS_LEVEL int NOT NULL DEFAULT 1,
TYPE int NOT NULL DEFAULT 1,
NAME varchar(30) NOT NULL,
SURNAME varchar(45) NOT NULL,
STATUS int NOT NULL DEFAULT 1
);
-- --------------------------------------------------------
--
-- Table structure for table device
--
CREATE TABLE device (
DEVICE_ID int IDENTITY PRIMARY KEY,
CATEGORY int NOT NULL,
IMIE_NUMBER varchar(30) NULL,
DEVICE_NAME varchar(30) NOT NULL,
SERIAL varchar(30) NULL,
DEVICE_PIN varchar(30) NULL,
DEVICE_TEL varchar(10) NULL,
DEVICE_EMAIL varchar(30) NULL,
DEVICE_PASSWORD varchar(30) NULL,
MODEL_NUMBER varchar(30) NULL,
DEVICE_STATUS int NOT NULL DEFAULT 1,
BLOBID int references tblBLOB(BLOBID),
DATE_ADDED date NOT NULL
);
-- --------------------------------------------------------
--
-- Table structure for table `audit_log`
--
CREATE TABLE audit_log
(
LOG_ID int IDENTITY PRIMARY KEY,
USER_ID int NOT NULL references bb_user(USER_ID),
ACTION varchar(200) NULL,
AUDIT_DATE date NOT NULL,
IMIE_NUMBER varchar(30) NULL
);
-- --------------------------------------------------------
--
-- Table structure for table event
--
CREATE TABLE event (
EVENT_ID int IDENTITY PRIMARY KEY,
CREATOR int NOT NULL references bb_user(USER_ID),
LAB_USER int NOT NULL references bb_user(USER_ID),
EVENT_NAME varchar(30) NOT NULL,
COMMENTS varchar(500) NOT NULL,
CREATION_DATE date NOT NULL,
EVENT_DATE date NOT NULL,
VENUE varchar(50) NOT NULL,
PARTICIPANTS varchar(500) DEFAULT NULL,
EVENT_STATUS int NOT NULL DEFAULT 1
);
-- --------------------------------------------------------
--
-- Table structure for table locker
--
CREATE TABLE locker (
LOCKER_ID int IDENTITY PRIMARY KEY,
KEY_ONE_USER int references bb_user(USER_ID) NULL,
KEY_TWO_USER int references bb_user(USER_ID) NULL,
LOCKER_STATUS int NOT NULL DEFAULT 1,
LOCKER_NAME varchar(30) NOT NULL,
DATE_FROM date NULL,
DATE_TO date NULL
);
CREATE TABLE lockerItem (
LOCK_ITEM_ID int IDENTITY PRIMARY KEY,
LOCKER_ID int references locker(LOCKER_ID),
DEVICE_ID int references device(DEVICE_ID),
LOCK_ITEM_STATUS int NOT NULL DEFAULT 1
);
CREATE TABLE announcement (
ANN_ID int IDENTITY PRIMARY KEY,
USER_ID int NOT NULL references bb_user(USER_ID),
DATE_POSTED date NOT NULL,
HEADING varchar(30) DEFAULT NULL,
MESSAGE varchar(1500) DEFAULT NULL
);
CREATE TABLE closedLockerItem
(
closedLockID int IDENTITY PRIMARY KEY,
DEVICE_ID int references device(DEVICE_ID),
USER_ID int references bb_user(USER_ID) NULL,
DATE_FROM date NULL,
DATE_TO date NULL
);