K9Z4KYEw

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_gestiune int,
95id_User int,
96id_plecare int,
97constraint PK_Rezervari primary key NONCLUSTERED(id_rezervare)
98);
99create table if not exists Bagaje(
100id_Bagaj int auto_increment,
101Greutate float,
102id_rezervari int,
103constraint pk_Bagaje primary key nonclustered(id_Bagaj)
104);
105 
106alter table Bagaje add(
107constraint fk_bagaje_rezervari foreign key(id_rezervari) references rezervari(id_rezervare));
108 
109alter table Rezervari add(
110constraint FK_Rezervari_User_Aeroport foreign key(id_User) references User_aeroport(id_User),
111constraint fk_Rezervari_Gestiune foreign key(id_gestiune) references Gestiune(id_gestiune));
112 
113drop table if exists Sosiri;
114create table Sosiri(
115id_sosiri int primary key auto_increment,
116id_gestiune int,
117de_la varchar(45),
118data_sosire date,
119ora_sosire time);
120 
121 
122alter table Sosiri add
123constraint fk_Gestiune_Sosiri foreign key(id_gestiune) references Gestiune(id_gestiune);
124 
125create table if not exists Contact(
126id_aeroport int,
127mail varchar(45),
128numar_telefon bigint(10),
129constraint pk_Contact primary key nonclustered(id_aeroport)
130 
131);
132create table if not exists Inchiriaza_Masina(
133idMasina int primary key,
134id_aeroport int,
135nr_locuri int,
136Marca varchar(45),
137pret_zi int,
138an_fabricatie int,
139statuss varchar(45),
140id_user int
141);
142alter table Inchiriaza_Masina add(
143constraint fk_Aeroport_Inchiriaza_Masina foreign key(id_aeroport) references Aeroport(id_aeroport) );
144alter table Contact add 
145constraint fk_Aeroport_Contact foreign key(id_aeroport) references Aeroport(id_aeroport);
146
147-- Inserarile
148insert into aeroport values
149(1, 'Avram Iancu', 'Cluj-Napoca', 'Romania', 'international', 3);
150
151insert into contact values 
152(1, 'aeroportinternational@yahoo.com', 0364457);
153
154insert into admin_aeroport values
155 (1,1, 'Admin', 'Aeroport', 'admin', 'avramiancu');
156 
157 insert into inchiriaza_masina values
158 (1,1,5, 'Audi', 100, 2013, 'neinchiriat', null),
159 (2,1,7, 'Ford', 60,2008,'neinchiriat', null),
160 (3,1,5, 'BMW', 150, 2015,'neinchiriat', null);
161 
162 insert into aeronava values
163(1, 'British Airlines','Boeing 707', 120, 2008),
164(2, 'Tarom', 'Airbus 10', 80, 2005),
165(3, 'Turkish Airlines','Antonotov', 300, 2020),
166(4, 'Wizzair','Boeing 707', 240, 2018),
167(5, 'Wizzair', 'Boeing 707', 140, 2019),
168(6, 'Qatar', 'Airbus 100', 30, 2020);
169insert into personal values
170 (1, 2, 'Maria', 'Enescu', 20, '2020-06-06', 'stewardesa'),
171(2, 3, 'Lucian', 'Echim', 34, '2016-10-06', 'pilot'),
172(3, 1, 'Marian', 'Enescu', 25, '2018-06-29', 'steward'),
173(4, 5, 'Andreea', 'Bucur', 45, '2008-03-18', 'pilot'),
174(5, 6, 'Antonia', 'Andreica', 55, '2008-03-28', 'casierita'),
175(6, 4, 'Constantin', 'Senila', 30, '2019-07-12', 'politist');
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, 1),
207 (2, 1, 2, 2);
208 
209 insert into bagaje values
210 (1, 30,1),
211 (2,  40,2);
212 
213
214 
215 
216
217 
218