create database VolAvion2

--fichier données
on primary (
name='VolAvion2_Data',
filename='C:\Users\EL ABOUSSI\Desktop\Projects\My Projects\Les Base De Donnees\DB_VolAvion2.mdf',
size=10MB,
maxsize=50MB,
filegrowth=5%)


--fichier journal

log on(
name='VolAvion2_Log',
filename='C:\Users\EL ABOUSSI\Desktop\Projects\My Projects\Les Base De Donnees\DB_VolAvion2.ldf',
size=10MB,
maxsize=50MB,
filegrowth=5%)

use volavion2

create table AffectVol(
NumPas smallint not null,
vol smallint not null,
Datevol datetime not null,
NumPlace smallint not null,
Prix money,
constraint pk_affectvol primary key(NumPas,vol)
);


create table Avion(
NumAv smallint not null,
Marque varchar(20)not null,
TypeAvion varchar(20)not null,
Capacite smallint not null,
Localisation varchar(20)not null,
DateMiseEnService datetime,
constraint pk_avion primary key (NumAv)
);

create table Passager(
NumPas int not null,
Nom varchar(20)not null,
Prenom varchar(20)not null,
Ville varchar(20) not null,
constraint pk_passager primary key (NumPas)
);

create table Pilote(
NumPil smallint not null,
Nompil varchar(20)not null,
CodePostal char(5)not null,
Villepil varchar(20)not null,
DateDeNaiss datetime,
DateDbActivite datetime,
DateFinActivite datetime,
SalaireBrut money,
constraint pk_pilote primary key (NumPil)
);

create table Vol(
NumVol smallint not null,
NumAv smallint not null,
NumPil smallint not null,
VilleDepart varchar(20)not null,
VilleArrivee varchar(20) not null,
HeureDprt decimal(5,2)not null,
HeureArrivee decimal(5,2) not null,
constraint pk_vol primary key (NumVol),
constraint fk_vol_avion foreign key (NumAv)references Avion(NumAv),
constraint fk_vol_pilote foreign key (NumPil)references Pilote(NumPil)
);