LrDQsFrz

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