-
Notifications
You must be signed in to change notification settings - Fork 0
/
HeThongBanVeMayBay.sql
142 lines (142 loc) · 4.27 KB
/
HeThongBanVeMayBay.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
CREATE DATABASE QLBANVEMAYBAY
GO
USE QLBANVEMAYBAY
GO
CREATE TABLE CHUCVU
(
ID INT IDENTITY (1,1) NOT NULL,
IDChucVu NCHAR(10) NOT NULL,
TenChucVu NVARCHAR(MAX) NOT NULL,
IsAdmin VARCHAR(5) NOT NULL,
PRIMARY KEY CLUSTERED (IDChucVu ASC)
)
CREATE TABLE SANBAY
(
ID INT IDENTITY (1,1) NOT NULL,
IATA VARCHAR(10) NOT NULL,
TenSB NVARCHAR(50) NOT NULL,
Status NVARCHAR(MAX),
PRIMARY KEY CLUSTERED (IATA ASC)
)
CREATE TABLE HANGBAY
(
ID INT IDENTITY (1,1) NOT NULL,
IDHangBay NVARCHAR(10) NOT NULL,
TenHangbay VARCHAR(20) NOT NULL,
ImageAviation NVARCHAR (MAX) NULL,
Status NVARCHAR(MAX),
PRIMARY KEY CLUSTERED (IDHangBay ASC)
)
CREATE TABLE MAYBAY
(
ID INT IDENTITY (1,1) NOT NULL,
IDMayBay VARCHAR(10) NOT NULL,
SoDangBa VARCHAR(10) NOT NULL,
LoaiMayBay VARCHAR(MAX) NOT NULL,
HangBay NVARCHAR(10) NOT NULL,
SucChuaToiDa INT NOT NULL,
Status NVARCHAR(MAX),
PRIMARY KEY CLUSTERED (IDMayBay ASC),
FOREIGN KEY (HangBay) REFERENCES HANGBAY(IDHangBay)
)
CREATE TABLE CHUYENBAY
(
ID INT IDENTITY (1,1) NOT NULL,
IDChuyenBay VARCHAR(10) NOT NULL,
HangBay NVARCHAR(10) NOT NULL,
IDSanBayDi VARCHAR(10) NOT NULL,
IDSanBayDen VARCHAR(10) NOT NULL,
GiaTien INT NOT NULL,
NgayBay DATETIME NOT NULL,
GioBay Time NOT NULL,
ThoiGianToiDuKien TIME NOT NULL,
SoGheHang1 INT NOT NULL,
SoGheHang2 INT NOT NULL,
Status NVARCHAR(MAX),
PRIMARY KEY CLUSTERED (ID ASC),
FOREIGN KEY (IDSanBayDi) REFERENCES SANBAY(IATA),
FOREIGN KEY (IDSanBayDen) REFERENCES SANBAY(IATA),
FOREIGN KEY (IDChuyenBay) REFERENCES MAYBAY(IDMayBay),
FOREIGN KEY (HangBay) REFERENCES HANGBAY(IDHangBay)
)
--CREATE TABLE CHI_TIET_LICH_CHUYEN_BAY
--(
-- ID INT IDENTITY (1,1) NOT NULL,
-- IDChiTietChuyenBay VARCHAR(10) NOT NULL,
-- IDChuyenBay VARCHAR(10) NOT NULL,
-- IDSanBayTrungGian VARCHAR(10) NULL,
-- ThoiGianDung TIME NULL,
-- GhiChu NTEXT,
-- PRIMARY KEY CLUSTERED (IDChiTietChuyenBay ASC),
-- FOREIGN KEY (IDSanBayTrungGian) REFERENCES SANBAY(IATA),
-- FOREIGN KEY (IDChuyenBay) REFERENCES CHUYENBAY(IDChuyenBay)
--)
CREATE TABLE HANHKHACH
(
ID INT IDENTITY (1,1) NOT NULL,
UserName VARCHAR(MAX) NULL,
Pass VARCHAR(MAX) NULL,
Email VARCHAR(MAX) NOT NULL,
CMND VARCHAR(12) NOT NULL,
TenHanhKhach NVARCHAR(50) NOT NULL,
NgaySinh DATE NOT NULL,
GioiTinh NVARCHAR(10) NOT NULL,
DienThoai VARCHAR(10) NOT NULL,
ChucVu NCHAR(10) NOT NULL,
Status NVARCHAR(MAX),
PRIMARY KEY CLUSTERED (CMND ASC),
FOREIGN KEY (ChucVu) REFERENCES CHUCVU(IDChucVu)
)
CREATE TABLE VECHUYENBAY
(
ID INT IDENTITY (1,1) NOT NULL,
IDVeChuyenBay VARCHAR(20) NOT NULL,
IDChuyenBay INT NOT NULL,
CMND VARCHAR(12) NOT NULL,
GiaTien INT NOT NULL,
LoaiVe NVARCHAR(10) NOT NULL,
Status NVARCHAR(MAX),
PRIMARY KEY CLUSTERED (ID),
FOREIGN KEY (IDChuyenBay) REFERENCES CHUYENBAY(ID),
FOREIGN KEY (CMND) REFERENCES HANHKHACH(CMND)
)
CREATE TABLE PHIEUDATCHO
(
ID INT IDENTITY (1,1) NOT NULL,
IDDatCho VARCHAR(10) NOT NULL,
IDChuyenBay INT NOT NULL,
CMND VARCHAR(12) NOT NULL,
GiaTien INT NOT NULL,
LoaiVe NVARCHAR(10) NOT NULL,
Status NVARCHAR(MAX),
PRIMARY KEY CLUSTERED (ID ASC),
FOREIGN KEY (IDChuyenBay) REFERENCES CHUYENBAY(ID),
FOREIGN KEY (CMND) REFERENCES HANHKHACH(CMND)
)
CREATE TABLE PHONGBAN
(
ID INT IDENTITY (1,1) NOT NULL,
IDPhongBan NCHAR(10) NOT NULL,
TenPhongBan NVARCHAR(MAX) NOT NULL,
Status NVARCHAR(MAX),
PRIMARY KEY CLUSTERED (IDPhongBan ASC)
)
CREATE TABLE NHANVIEN
(
ID INT IDENTITY (1,1) NOT NULL,
UserName VARCHAR(50) NOT NULL,
Pass VARCHAR(MAX) NOT NULL,
Email VARCHAR(MAX) NOT NULL,
TenNV NVARCHAR(30) NOT NULL,
GioiTinh NVARCHAR(10) NOT NULL,
NgaySinh DATETIME NOT NULL,
NgayVaoLam DATETIME NOT NULL,
NgayNghiLam DATETIME,
ChucVu NCHAR(10) NOT NULL,
BoPhan NCHAR(10) NOT NULL,
ImageEmp NVARCHAR (MAX) NULL,
Status NVARCHAR(MAX),
PRIMARY KEY CLUSTERED (UserName ASC),
FOREIGN KEY (BoPhan) REFERENCES PHONGBAN(IDPhongBan),
FOREIGN KEY (ChucVu) REFERENCES CHUCVU(IDChucVu)
)