DDL:
CREATE DATABASE IF NOT EXISTS L2;
DROP TABLE IF EXISTS TRAVELER; CREATE TABLE TRAVELER(
NoTR INT PRIMARY KEY,
LastName VARCHAR(20)UNIQUE NOT NULL, FirstName
BirthDat DATE,
VARCHAR(20)NOT NULL,
Gender SET ('M', 'F') DEFAULT 'M'
);
DROP TABLE IF EXISTS FLIGHT; CREATE TABLE FLIGHT (
NoFL INT PRIMARY KEY,
Destination SET ('Algiers', 'Bejaia', 'Constantine', 'Oran', 'Ouargla',
'Sétif') DEFAULT 'Algiers',
FlightDat DATE, DepartTime TIME,
NbrPlaces INT DEFAULT 100,
CONSTRAINT Limit_Nbr_Pl CHECK (NbrPlaces>=100)
);
DROP TABLE IF EXISTS RESERVATION; CREATE TABLE RESERVATION
( NoTR INT,
NoFL INT,
CONSTRAINT FK_1 FOREIGN KEY (NoTR) REFERENCES TRAVELER
PRIMARY KEY(NoTR,NoFL),
(NoTR) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT FK_2 FOREIGN KEY (NoFL) REFERENCES FLIGHT(NoFL)
);
ON UPDATE CASCADE ON DELETE RESTRICT
ALTER TABLE TRAVELER DROP COLUMN GENDER;
ALTER TABLE TRAVELER ADD COLUMN GENDER SET ('M', 'F')
DEFAULT 'M';
ALTER TABLE FLIGHT ALTER NbrPlaces DROP DEFAULT;
DML:
INSERT INTO FLIGHT VALUES (1000,'Algiers','2024-03-01','80:00',200); INSERT
INTO FLIGHT VALUES (1001,'Oran','2024-03-01','13:00',150); INSERT INTO
FLIGHT VALUES (1002, Default,'2024-03-02','12:00',120); INSERT INTO FLIGHT
VALUES (1003,'Ouargla','2024-03-03','80:00',100); INSERT INTO FLIGHT
VALUES (1004,'Bejaia','2024-03-05','20:00',180); INSERT INTO FLIGHT VALUES
(1005,'Sétif','2024-03-06','06:00',200);
INSERT INTO TRAVELER (NoTR, LastName, FirstName, BirthDat, Gender)
VALUES (1, 'Aissani', 'Mohamed', '1970-01-01', 'M');
INSERT INTO TRAVELER (NOTR, LastName, FirstName, BirthDat, Gender)
VALUES (2, 'Mohamedi', 'Mustafa', '1980-07-05', default);
INSERT INTO TRAVELER (NoTR, LastName, FirstName, BirthDat, Gender)
VALUES (3, 'Yahiaoui', 'Djamila', '1987-04-02', 'F');
INSERT INTO TRAVELER VALUES (4, 'Smaili', 'Faiza', '1990-09-18', 'F');
INSERT INTO TRAVELER VALUES (5,'Brahimi', 'Mohamed', '1976-12-23',
Default); INSERT INTO TRAVELER VALUES (6,'Mostefaoui', 'Kamel', '1990-12-
12', Default);
INSERT INTO RESERVATION VALUES (1,1000); INSERT INTO RESERVATION VALUES
(2,1000); INSERT INTO RESERVATION VALUES (3,1001);
INSERT INTO RESERVATION VALUES (4,1002); INSERT INTO RESERVATION
VALUES (1,1002); INSERT INTO RESERVATION VALUES (5,1002); INSERT INTO
RESERVATION VALUES (3,1003);
INSERT INTO TRAVELER (NoTR, LastName, FirstName, BirthDat,
Gender) VALUES (1, 'Aissani', 'Mohamed', '1970-01-01', 'M'), (2,
'Mohamedi', 'Mustafa', '1980-07-05', 'M'), (3, 'Yahiaoui', 'Djamila', '1985-
04-02', 'F'), (4, 'Smaili', 'Faiza', '1990-09-18', 'F'), (5, 'Brahimi',
'Mohamed', '1976-12-23', 'M'), (6, 'Mostefaoui', 'Kamel', '1990-12-12',
'M');
INSERT INTO FLIGHT (NoFL, Destination, FlightDat, DepartTime,
NbrPlaces) VALUES (1000, 'Algiers', '2024-03-01', '08:00', 200), (1001,
'Oran', '2024-03-01', '13:00', 150), (1002, 'Algiers', '2024-03-02', '12:00',
120), (1003, 'Ouargla', '2024-03-03', '08:00', 100), (1004, 'Bejaia', '2024-
03-05', '20:00', 180), (1005, 'Sétif', '2024-03-06', '06:00', 200);
INSERT INTO RESERVATION (NoTR, NoFL) VALUES (1, 1000), (2,
1001), (3, 1002), (4, 1003), (5, 1004), (6, 1005);
UPDATE FLIGHT SET Destination = 'Constantine' WHERE NoFL =
1002;
UPDATE FLIGHT SET DepartTime = '08:00';
UPDATE FLIGHT SET NbrPlaces = NbrPlaces + 50 WHERE NoFL =
1000;
UPDATE FLIGHT SET NbrPlaces = NbrPlaces * 2;
UPDATE TRAVELER SET Gender = NULL;
DELETE FROM RESERVATION;
DELETE FROM FLIGHT WHERE NoFL = 1005;
DELETE FROM TRAVELER WHERE Gender = 'F';
DELETE FROM RESERVATION WHERE NoTR IN (SELECT NoTR
FROM TRAVELER WHERE BirthDat > DATE_SUB('2024-01-01',
INTERVAL 40 YEAR));
DELETE FROM TRAVELER WHERE BirthDat > DATE_SUB('2024-01-
01', INTERVAL 40 YEAR);
DROP TABLE RESERVATION;
DROP
