-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsql.sql
More file actions
77 lines (69 loc) · 2.31 KB
/
sql.sql
File metadata and controls
77 lines (69 loc) · 2.31 KB
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
IF OBJECT_ID('dbo.[bookups]', 'U') IS NOT NULL
DROP TABLE dbo.[bookups]
GO
IF OBJECT_ID('dbo.[trips]', 'U') IS NOT NULL
DROP TABLE dbo.trips
GO
IF OBJECT_ID('dbo.[passenger_information]', 'U') IS NOT NULL
DROP TABLE dbo.passenger_information
GO
IF OBJECT_ID('dbo.[destinations]', 'U') IS NOT NULL
DROP TABLE dbo.destinations
GO
IF OBJECT_ID('dbo.[airplanes]', 'U') IS NOT NULL
DROP TABLE dbo.[airplanes]
GO
IF OBJECT_ID('dbo.[airplane_type]', 'U') IS NOT NULL
DROP TABLE dbo.[airplane_type]
GO
CREATE TABLE [airplane_type] (
id integer NOT NULL IDENTITY PRIMARY KEY,
airplane_type varchar(50) NOT NULL
)
GO
CREATE TABLE [airplanes] (
id integer NOT NULL IDENTITY PRIMARY KEY,
name varchar(50),
airplane_type_id integer NOT NULL,
capacity integer NOT NULL
)
GO
CREATE TABLE [destinations] (
id integer NOT NULL IDENTITY PRIMARY KEY,
country varchar(50) NOT NULL
)
GO
CREATE TABLE [trips] (
id integer NOT NULL IDENTITY PRIMARY KEY,
airplane_id integer NOT NULL,
destination_id integer NOT NULL,
arrival_id integer NOT NULL,
date datetime NOT NULL DEFAULT GETDATE()
)
GO
CREATE TABLE [passenger_information] (
id integer NOT NULL IDENTITY PRIMARY KEY,
name varchar(50) NOT NULL,
phone varchar(50) NOT NULL UNIQUE CHECK(phone LIKE '+375[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
GO
CREATE TABLE [bookups] (
trip_id integer NOT NULL,
passenger_id integer NOT NULL,
date datetime NOT NULL DEFAULT GETDATE()
)
ALTER TABLE bookups ADD CONSTRAINT bookups_pk PRIMARY KEY (trip_id, passenger_id, date);
ALTER TABLE airplanes ADD CONSTRAINT airplanes_fk_airplane_type FOREIGN KEY ([airplane_type_id]) REFERENCES [airplane_type]([id]);
ALTER TABLE trips ADD CONSTRAINT trips_fk_airplane FOREIGN KEY (airplane_id) REFERENCES [airplanes]([id]);
ALTER TABLE trips ADD CONSTRAINT trips_fk_destination FOREIGN KEY (destination_id) REFERENCES [destinations]([id]);
ALTER TABLE trips ADD CONSTRAINT trips_fk_arrival FOREIGN KEY (arrival_id) REFERENCES [destinations]([id]);
ALTER TABLE bookups ADD CONSTRAINT bookups_fk_trip FOREIGN KEY (trip_id) REFERENCES [trips]([id]);
ALTER TABLE bookups ADD CONSTRAINT bookups_fk_passenger FOREIGN KEY (passenger_id) REFERENCES [passenger_information]([id]);
GO
CREATE NONCLUSTERED INDEX trips_date
ON trips(date);
GO
GO
CREATE NONCLUSTERED INDEX passenger_information
ON passenger_information(name);
GO