135 lines
3.2 KiB
SQL
135 lines
3.2 KiB
SQL
//1
|
|
DROP TABLE Personnel;
|
|
DROP TABLE Peut_Atterrir;
|
|
DROP TABLE Avion;
|
|
DROP TABLE Type_Avion;
|
|
DROP TABLE Vol;
|
|
DROP TABLE Aeroport;
|
|
|
|
CREATE TABLE Aeroport (
|
|
codeAeroport varchar2(15) NOT NULL Primary Key,
|
|
nom varchar2(40) NOT NULL UNIQUE,
|
|
ville varchar2(40) NOT NULL UNIQUE,
|
|
pays varchar2(40) NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE Vol (
|
|
numVol INT Primary Key NOT NULL,
|
|
Compagnie varchar2(40) NOT NULL,
|
|
periodeVol varchar2(30) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE Type_Avion (
|
|
nomType varchar2(40) NOT NULL Primary Key,
|
|
maxSieges INT NOT NULL,
|
|
nomConstructeur varchar2(40) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE Avion (
|
|
idAvion INT NOT NULL Primary Key,
|
|
totalSieges INT NOT NULL,
|
|
nomTypeAvion varchar2(40) REFERENCES Type_Avion
|
|
);
|
|
|
|
CREATE TABLE Peut_Atterrir (
|
|
nomTypeAvion varchar2(40) REFERENCES Type_Avion,
|
|
codeAeroport varchar2(15) REFERENCES Aeroport
|
|
);
|
|
|
|
CREATE TABLE Personnel(
|
|
idPersonne INT NOT NULL Primary Key,
|
|
nom varchar2(40) NOT NULL,
|
|
prenom varchar2(40) NOT NULL,
|
|
fonction varchar2(40) NOT NULL
|
|
);
|
|
|
|
//2
|
|
|
|
INSERT INTO Aeroport VALUES('C15RF', 'Charles De Gaulle', 'Roissy-en-France', 'Paris');
|
|
INSERT INTO Aeroport VALUES('MM4', 'Charles De Gaulle', 'Roissy-en-France', 'Paris');
|
|
INSERT INTO Aeroport VALUES('JM3630LP', NULL, NULL, NULL);
|
|
|
|
INSERT INTO Type_Avion VALUES('RAFALE', 2, 'Dassault Aviation');
|
|
|
|
INSERT INTO Avion VALUES(0455632, 2, 'RAFALE');
|
|
|
|
INSERT INTO Peut_Atterrir VALUES('RAFALE', 'C15RF');
|
|
|
|
DELETE FROM Aeroport
|
|
WHERE codeAeroport = 'C15RF';
|
|
|
|
//3
|
|
DROP TABLE Hotesse_Equipage;
|
|
DROP TABLE Equipage;
|
|
DROP TABLE Instance_Escale;
|
|
DROP TABLE Escale;
|
|
|
|
CREATE TABLE Escale(
|
|
numVol INT REFERENCES Vol,
|
|
numEscale INT NOT NULL CONSTRAINT NumLim CHECK(numEscale BETWEEN 0 and 20),
|
|
aeroportDepart varchar2(15) REFERENCES Aeroport,
|
|
aeroportArrivee varchar2(15) REFERENCES Aeroport,
|
|
CONSTRAINT Diff CHECK(aeroportDepart != aeroportArrivee),
|
|
heureDepartPrevue INT NOT NULL CONSTRAINT CoheureDepartPrevue CHECK(heureDepartPrevue BETWEEN 0 and 23),
|
|
minuteDepartPrevue INT NOT NULL CONSTRAINT CominuteDepartPrevue CHECK(minuteDepartPrevue BETWEEN 0 and 59),
|
|
heureArriveePrevue INT NOT NULL CONSTRAINT CoheureArriveePrevue CHECK(heureArriveePrevue BETWEEN 0 and 23),
|
|
minuteArriveePrevue INT NOT NULL CONSTRAINT CominuteArriveePrevue CHECK(minuteArriveePrevue BETWEEN 0 and 59),
|
|
Primary Key(numVol, numEscale)
|
|
);
|
|
|
|
CREATE TABLE Instance_Escale (
|
|
numVol INT,
|
|
numEscale INT,
|
|
dateEscale DATE Primary Key,
|
|
nbrSieges INT NOT NULL,
|
|
idAvion INT NOT NULL,
|
|
dateDepartEffectuee DATE NOT NULL,
|
|
dateArriveeEffectuee DATE NOT NULL,
|
|
CONSTRAINT DiffDate CHECK(dateDepartEffectuee < dateArriveeEffectuee),
|
|
idEquipe INT NOT NULL,
|
|
FOREIGN Key(numVol, numEscale) REFERENCES Escale
|
|
);
|
|
|
|
CREATE TABLE Equipage(
|
|
numVol INT,
|
|
numEscale INT,
|
|
dateEscale DATE,
|
|
idEquipe INT Primary Key,
|
|
idPilote INT,
|
|
idCoPilote INT,
|
|
idChefCabine INT,
|
|
FOREIGN Key(numVol, numEscale, dateEscale) REFERENCES Instance_Escale,
|
|
FOREIGN Key(idPilote, idCoPilote, idChefCabine) REFERENCES Personnel
|
|
);
|
|
|
|
CREATE TABLE Hotesse_Equipage(
|
|
idEquipe INT REFERENCES Equipage,
|
|
idPersonne INT REFERENCES Personnel
|
|
);
|
|
|
|
//4
|
|
|
|
|
|
|
|
//5
|
|
|
|
|
|
|
|
//6
|
|
|
|
//TP5
|
|
|
|
//1
|
|
|
|
|
|
CREATE TABLE AUTEUR(
|
|
NumAuteur number Primary Key,
|
|
NomAuteur varchar2(20)
|
|
);
|
|
|
|
CREATE TABLE EDITEUR(
|
|
codeAuteur number Primary Key,
|
|
RaisonSociale varchar2(20)
|
|
);
|
|
|
|
//2 |