L1jqWgQ5

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