BwfLzb3z

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  
40create table Personal(
41id_personal int auto_increment,
42id_aeroport int,
43nume varchar(45),
44prenume varchar(45),
45varsta int,
46data_angajarii date,
47functie varchar(45),
48id_aeronava int,
49constraint pf_Personal primary key nonclustered(id_personal)
50);
51
52alter table Personal add(
53constraint fk_Aeroport_Personal foreign key(id_aeroport) references Aeroport(id_aeroport),
54constraint fk_Personal_Aeronava foreign key(id_aeronava) references Aeronava(id_aeronava));
55 
56 
57alter table Plecari add(
58constraint FK_Aeroport_Plecari foreign key(id_aeroport) references Aeroport(id_aeroport));
59
60 
61create table Aeronava(
62id_aeronava int primary key,
63id_aeroport int,
64Nume varchar(45),
65capacitate int,
66an_fabricatie int
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 InchiriazaMasina add(
134constraint fk_Aeroport_InchiriazaMasina 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, 1, 'Maria', 'Enescu', 20, '2020-06-06', 'stewardesa',1),
149(2, 1, 'Lucian', 'Echim', 34, '2016-10-06', 'pilot',1),
150(3, 1, 'Marian', 'Enescu', 25, '2018-06-29', 'steward',2),
151(4, 1, 'Andreea', 'Bucur', 45, '2008-03-18', 'stewardesa',2),
152(5, 1, 'Eugen', 'Popescu', 38, '2019-08-29', 'pilot');
153
154insert into sosiri values
155(1, 'Frankfurt', 1, '2020-12-14', '04:30',2),
156(2, 'Dublin',1,  '2020-12-14', '07:45', 1),
157(3,' Bucuresti', 1,  '2020-12-14', '08:55', 3),
158(4, 'Bruxeless',1, '2020-12-14', '14:55', 4);
159
160
161insert into plecari values
162(1, 1, 'Lisabona', '2020-12-14', '13:20','nu', 'Bucuresti',2),
163(2, 1, 'Madrid', '2020-12-14', '13:45', 'da', null, 1),
164(3, 1, 'Roma', '2020-12-14', '15:40', 'da', null, 3),
165(4, 1, 'Tokyo', '2020-12-14', '18:00', 'nu', 'Paris',4),
166(5, 1, 'Londra', '2020-12-14', '20:30', 'da', null,5);
167
168insert into contact values 
169(1, 'aeroportinternational@yahoo.com', 0364457);
170
171insert into user_aeroport values
172(1, 'Popa', 'Mihaela', 'popa120', 'popa', 'popamihaela@yahoo.com', 07459858302),
173(2, 'Miron', 'Alina', 'miron120', 'miron', 'mironalina@yahoo.com', 07459758302),
174 (3, 'Mateescu', 'Bogdan', 'mateescu120', 'mateescu', 'mateescubogdan@yahoo.com', 07459858766);
175 
176 insert into bagaje values
177 (1, 1, 30),
178 (2, 2, 40),
179 (3, 3, null);
180 
181 insert into rezervari values
182 (1, 1, 1, 1),
183 (2, 1, 2, 2),
184 (3, 1, 3, 4);
185 
186 insert into admin_aeroport values
187 (1,1, 'Admin', 'Aeroport', 'admin', 'avramiancu');
188 
189 insert into inchiriazamasina values
190 (1,1,5, 'Audi', 100, 2013, 'neinchiriat', null),
191 (2,1,7, 'Ford', 60,2008,'neinchiriat', null),
192 (3,1,5, 'BMW', 150, 2015,'neinchiriat', null);
193 
194
195
196
197
198
199
200
201
202
203
204
205
206 
207
208
209
210