-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.sql
145 lines (113 loc) · 3.95 KB
/
db.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
SET FOREIGN_KEY_CHECKS = 1;
SET SQL_SAFE_UPDATES = 0;
SET default_storage_engine=InnoDB;
drop database if exists staduim;
create database staduim ;
use staduim;
create table role(roleId int primary key auto_increment,
role nvarchar(30));
create table job(jobId int primary key auto_increment,
job nvarchar(25)
);
create table employee(EmpId int primary key auto_increment,
fName nvarchar(30),
lName nvarchar(30),
gender nvarchar(6),
jobId int,
dob date,
hiredDate date,
address nvarchar(200),
tel nvarchar(20),
email nvarchar(30) default '',
photo nvarchar(200),
salary int,
foreign key (jobId) references job(jobId),
unique key(fName,lName,dob)
);
create table user(userId int primary key auto_increment,
username nvarchar(30),
password nvarchar(30),
roleId int,
is_active smallint,
empid int,
foreign key(empId) references employee(empId) on delete cascade,
foreign key(roleId) references role(roleId),
unique key(username));
create table league(leagueId int primary key auto_increment,
league nvarchar(30) unique,
photo nvarchar(200)
);
create table club(clubId int primary key auto_increment,
club nvarchar(50) unique,
nickname nvarchar(10) unique,
photo nvarchar(200),
leagueId int,
foreign key (leagueId) references league(leagueId)
);
create table section(sectionId int primary key auto_increment,
section nvarchar(30));
create table chairType(chairTypeId int primary key auto_increment,
chairType nvarchar(30));
create table chair(chairId int primary key auto_increment,
chairTypeId int,
sectionId int,
chairNum nvarchar(30),
foreign key (chairtypeId) references chairtype(chairTypeId) on delete cascade,
foreign key (sectionId) references section(sectionId) on delete cascade);
create table competition(competitionId int primary key auto_increment,
dateTimeStart timestamp,
dateTimeEnd timeStamp,
createdBy int,
description nvarchar(200),
status int,
foreign key(createdBy) references employee(empId));
create table competitionClub(competitionClubId int primary key auto_increment,
competitionId int,
club1 int,
club2 int,
description nvarchar(200),
foreign key (competitionId) references competition(competitionId) on delete cascade,
foreign key (club1) references club(clubId),
foreign key (club2) references club(clubId)
);
create table competitionDetail(competitionDetailid int primary key auto_increment,
competitionId int,
chairTypeId int,
price float,
foreign key (competitionId) references competition(competitionid) on delete cascade,
foreign key (chairTypeId) references chairtype(chairtypeid)
);
create table book(bookId int primary key auto_increment,
dateTimeCreated timestamp,
empId int,
total float,
phone nvarchar(20),
chairTypeId int,
competitionId int,
foreign key (empId) references employee(empid),
foreign key (competitionId) references competition(competitionid) on delete cascade
);
create table bookDetail(bookDetailId int primary key auto_increment,
bookId int,
chairTypeId int,
qty int,
subTotal float,
foreign key (chairTypeId) references chairType(chairTypeid),
foreign key (bookId) references book(bookid) on delete cascade
);
create table bookChair(bookChairId int primary key auto_increment,
bookDetailId int,
chairId int,
ticketNum nvarchar(30),
foreign key (bookDetailid) references bookdetail(bookdetailid) on delete cascade,
foreign key (chairId) references chair(chairid));
create table expense(expenseId int primary key auto_increment,
dateCreated timestamp,
total float,
empId int,
foreign key (empId) references employee(empid));
create table expenseDetail(expenseDetailId int primary key auto_increment,
expenseid int,
description nvarchar(200),
amount float,
foreign key (expenseId) references expense(expenseid) on delete cascade);