nqNd0vGb

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