1AVFCwQa

1Drop database if exists ProiectAeroport;
2CREATE SCHEMA IF NOT EXISTS ProiectAeroport;
3USE ProiectAeroport;
4 
5Create table Aeroport(
6id_aeroport int auto_increment,
7Nume Varchar(45),
8Locatie varchar(45),
9tara varchar(45),
10tip varchar(45),
11constraint PK_Aeroport primary key NONCLUSTERED(id_aeroport)
12);
13
14create table if not exists Admin_Aeroport(
15idAdmin int auto_increment,
16id_aeroport int,
17Nume varchar(45),
18prenume varchar(45),
19Username varchar(45),
20parola varchar(45),
21constraint pk_Admin_Aeroport primary key nonclustered(idAdmin, id_aeroport)
22);
23 
24 alter table Admin_Aeroport add
25 constraint fk_admin_aeroport_aeroport1_idx foreign key(id_aeroport) references Aeroport(id_aeroport);
26 
27 
28Create table Plecari(
29id_plecare int auto_increment,
30id_aeroport int,
31destinatie varchar(45),
32data_plecarii date,
33ora_plecarii time,
34zbor_direct varchar(45),
35escala varchar(45),
36id_aeronava int,
37constraint pk_Plecari primary key NONCLUSTERED(id_plecare)
38);
39  create table Aeronava(
40id_aeronava int primary key,
41id_aeroport int,
42Nume varchar(45),
43capacitate int,
44an_fabricatie int
45);
46create table Personal(
47id_personal int auto_increment,
48id_aeroport int,
49nume varchar(45),
50prenume varchar(45),
51varsta int,
52data_angajarii date,
53functie varchar(45),
54id_aeronava int,
55constraint pf_Personal primary key nonclustered(id_personal)
56);
57
58alter table Personal add(
59constraint fk_Aeroport_Personal foreign key(id_aeroport) references Aeroport(id_aeroport),
60constraint fk_Personal_Aeronava foreign key(id_aeronava) references Aeronava(id_aeronava));
61 
62 
63alter table Plecari add(
64constraint FK_Aeroport_Plecari foreign key(id_aeroport) references Aeroport(id_aeroport));
65
66 
67
68alter table Aeronava add
69constraint FK_Aeronava_Aeroport foreign key(id_aeroport) references Aeroport(id_aeroport);
70
71create table if not exists User_Aeroport(
72id_User int primary key,
73Nume varchar(45),
74Prenume varchar(45),
75Username varchar(45),
76Parola varchar(45),
77Email varchar(45),
78Telefon Varchar(45)
79);
80
81create table if not exists Bagaje(
82id_Bagaj int auto_increment,
83id_User int,
84Greutate float,
85constraint pk_Bagaje primary key nonclustered(id_Bagaj)
86);
87
88alter table Bagaje add
89constraint fk_Bagaje_User_Aeroport foreign key(id_User) references User_Aeroport(id_User);
90
91create table if not exists Rezervari(
92id_Rezervare int,
93id_Aeroport int,
94id_User int,
95id_plecare int,
96constraint PK_Rezervari primary key NONCLUSTERED(id_rezervare)
97);
98alter table Rezervari add(
99constraint FK_Rezervari_User_Aeroport foreign key(id_User) references User_aeroport(id_User),
100constraint fk_Rezervari_Aeroport foreign key(id_Aeroport) references Aeroport(id_aeroport),
101constraint fk_Rezervari_Plecari foreign key(id_plecare) references Plecari(id_plecare));
102
103drop table if exists Sosiri;
104create table Sosiri(
105id_sosiri int primary key auto_increment,
106de_la varchar(45),
107Locatie_Sosire int,
108data_sosire date,
109ora_sosire time,
110id_aeronava int);
111
112 
113alter table Sosiri add
114constraint fk_Aeroport_Sosiri foreign key(Locatie_Sosire) references Aeroport(id_aeroport);
115
116create table if not exists Contact(
117id_aeroport int,
118mail varchar(45),
119numar_telefon bigint(10),
120constraint pk_Contact primary key nonclustered(id_aeroport)
121
122);
123create table if not exists Inchiriaza_Masina(
124idMasina int primary key,
125id_aeroport int,
126nr_locuri int,
127Marca varchar(45),
128pret_zi int,
129an_fabricatie int,
130statuss varchar(45),
131id_user int
132);
133alter table Inchiriaza_Masina add(
134constraint fk_Aeroport_Inchiriaza_Masina foreign key(id_aeroport) references Aeroport(id_aeroport) );
135alter table Contact add 
136constraint fk_Aeroport_Contact foreign key(id_aeroport) references Aeroport(id_aeroport);
137
138
139 insert into aeroport values
140(1, 'Avram Iancu', 'Cluj-Napoca', 'Romania', 'international');
141insert into aeronava values
142(1, 1, 'Boeing 707', 120, 2008),
143(2, 1, 'Airbus 10', 80, 2005),
144(3, 1, 'Antonotov', 300, 2020),
145(4, 1, 'Boeing 707', 240, 2018),
146(5, 1, 'Boeing 707', 140, 2019);
147 insert into personal values
148 (1, null, 'Maria', 'Enescu', 20, '2020-06-06', 'stewardesa',1),
149(2, null, 'Lucian', 'Echim', 34, '2016-10-06', 'pilot',1),
150(3, null, 'Marian', 'Enescu', 25, '2018-06-29', 'steward',2),
151(4, null, 'Andreea', 'Bucur', 45, '2008-03-18', 'stewardesa',2),
152(5, 1, 'Antonia', 'Andreica', 55, '2008-03-28', 'casierita',null),
153(6, 1, 'Constantin', 'Senila', 30, '2019-07-12', 'politist',null);
154
155insert into sosiri values
156(1, 'Frankfurt', 1, '2020-12-14', '04:30',2),
157(2, 'Dublin',1,  '2020-12-14', '07:45', 1),
158(3,' Bucuresti', 1,  '2020-12-14', '08:55', 3),
159(4, 'Bruxeless',1, '2020-12-14', '14:55', 4);
160
161
162insert into plecari values
163(1, 1, 'Lisabona', '2020-12-14', '13:20','nu', 'Bucuresti',2),
164(2, 1, 'Madrid', '2020-12-14', '13:45', 'da', null, 1),
165(3, 1, 'Roma', '2020-12-14', '15:40', 'da', null, 3),
166(4, 1, 'Tokyo', '2020-12-14', '18:00', 'nu', 'Paris',4),
167(5, 1, 'Londra', '2020-12-14', '20:30', 'da', null,5);
168
169insert into contact values 
170(1, 'aeroportinternational@yahoo.com', 0364457);
171
172insert into user_aeroport values
173(1, 'Popa', 'Mihaela', 'popa120', 'popa', 'popamihaela@yahoo.com', 07459858302),
174(2, 'Miron', 'Alina', 'miron120', 'miron', 'mironalina@yahoo.com', 07459758302),
175 (3, 'Mateescu', 'Bogdan', 'mateescu120', 'mateescu', 'mateescubogdan@yahoo.com', 07459858766);
176 
177 insert into bagaje values
178 (1, 1, 30),
179 (2, 2, 40),
180 (3, 3, null);
181 
182 insert into rezervari values
183 (1, 1, 1, 1),
184 (2, 1, 2, 2),
185 (3, 1, 3, 4);
186 
187 insert into admin_aeroport values
188 (1,1, 'Admin', 'Aeroport', 'admin', 'avramiancu');
189 
190 insert into inchiriaza_masina values
191 (1,1,5, 'Audi', 100, 2013, 'neinchiriat', null),
192 (2,1,7, 'Ford', 60,2008,'neinchiriat', null),
193 (3,1,5, 'BMW', 150, 2015,'neinchiriat', null);
194 
195
196
197
198
199
200
201
202
203
204
205
206
207 
208
209
210
211