constraint PKnbrVol primary key (No_vol) ); DROP TABLE segmentdevol CASCADE CONSTRAINTS; CREATE TABLE segmentdevol( numeric(9) NOT NULL,

(1)

--- c’est une base de doonnees qui permet de gerer--- -- les vols d’une compagnie aerienne---Victor Kimenyi, 13042806--- Petar Zubac, 1407772213042806---13042806---13042806---13042806---13042806---13042806---13042806---13042806---13042806---13042806---13042806---13042806--- 14077722--- ---drop table aeroport cascade constraints;

create table aeroport (

codeAeroport char(4) NOT NULL, ville VARCHAR(10) NOT NULL, etat varchar(10) NOT NULL, nom varchar(50) NOT NULL,

CONSTRAINT PKaeroportCode PRIMARY KEY (codeAeroport), CONSTRAINT Uairport unique (codeAeroport,ville,etat,nom) );

DROP TABLE Vol cascade constraints; create table Vol (

No_Vol VARCHAR(8),

typeDeVol VARCHAR(9) NOT NULL,

constraint PKnbrVol primary key (No_vol) );

DROP TABLE segmentDeVol CASCADE CONSTRAINTS; CREATE TABLE segmentDeVol(

No_vol numeric(9) NOT NULL , No_Segment numeric(9) NOT NULL, Code_aeroport_Depart VARCHAR(4),

Code_aeroport_arrivee VARCHAR(4),

Date_heure_depart TIMESTAMP NOT NULL, Date_heure_arrivee TIMESTAMP NOT NULL,

constraint Usegment UNIQUE (No_vol,No_Segment,Code_aeroport_Depart, Code_aeroport_arrivee,Date_heure_depart, Date_heure_arrivee),

constraint FKnbrVoL foreign key (no_Vol) REFERENCES Vol );

DROP TABLE classe CASCADE CONSTRAINTS; CREATE TABLE classe (

No_Class numeric(2), Description VARCHAR(12),

CONSTRAINT PK_classe primary KEY (No_Class),

- 0.5

check ...

- 4

not null

manque les contraintes check sur date et code aeroport

manque les fk pour code aeroport check noSegment > 0

- 19.5

not null

- 2

(2)

CONSTRAINT U_la_class unique (No_Class,Description) );

DROP TABLE classeVol CASCADE CONSTRAINTS; CREATE TABLE classeVol(

No_Class numeric(2), No_Vol VARCHAR(8), Prix_de_vol numeric(7,2)

constraint FK_vol_class foreign key (no_vol) references vol; constraint FK_class foreign key (no_class) references classe, constraint U_class_prix unique (no_class,prix_de_vol);

);

DROP TABLE Avion CASCADE CONSTRAINTS; CREATE TABLE Avion(

No_Avion numeric(8), No_Modele numeric(9), date_Achat date not null,

CONSTRAINT PK_Avion primary KEY (No_Avion), CONSTRAINT Uplane unique (No_Avion,No_Modele) );

DROP TABLE AvionVol CASCADE CONSTRAINTS; CREATE TABLE AvionVol (

No_Vol VARCHAR(8), No_Avion numeric(8),

CONSTRAINT FKAvionVol_1 foreign key (No_Vol) REFERENCES vol, CONSTRAINT FKAvion_2 foreign key (No_Avion) REFERENCES Avion, constraint U_vol_avion unique (no_vol,no_avion)

);

insert into aeroport (CODEAEROPORT,ville,etat,NOM) values (’yul’,’montreal’,’qc’,’trudeau’);

insert into aeroport (CODEAEROPORT,ville,etat,NOM) values(’yyz’,’toronto’,’on’,’pearson’);

insert into aeroport (CODEAEROPORT,ville,etat,NOM) values(’cdg’,’paris’,’fr’,’charles-de-gaulle’); insert into avion (NO_AVION,NO_MODELE,DATE_ACHAT) values (1,’boeing 747’,’2010-09-08’);

insert into avion (NO_AVION,NO_MODELE,DATE_ACHAT) values (2,’AIRBUS A380’,’2011-09-01’);

insert into avion (NO_AVION,NO_MODELE,DATE_ACHAT) values (3,’AIRBUS A340’,’2012-08-01’);

insert into VOL (NO_VOL,TYPEDEVOL) values (’AC2001’,’regulier’);

erreur de synthaxe (manque , après (7,2) et ; en trop avant la ) : - 1

not null

- 5.5

- 3

manque les accents et majuscules dans les valeurs : - 4

erreur d'insert, format de date et pour l'attribut modèle (le type) - 1

(3)

insert into VOL (NO_VOL,TYPEDEVOL) values (’AC2002’,’NOLISE’);

insert into SEGMENTDEVOL (NO_VOL,NO_SEGMENT,CODE_AEROPORT_DEPART,CODE_AEROPORT_ARRIVEE, DATE_HEURE_DEPART,DATE_HEURE_ARRIVEE)

values (’AC2001’,2,’yyz’,’cdg’,timestamp’2014-01-02 14:00:00’,timestamp’2014-01-03 07:00:00’); insert into SEGMENTDEVOL (NO_VOL,NO_SEGMENT,CODE_AEROPORT_DEPART,CODE_AEROPORT_ARRIVEE,

DATE_HEURE_DEPART,DATE_HEURE_ARRIVEE)

values (’AC2001’,1,’YUL’,’YYZ’,timestamp’2014-01-02 13:00:00’,timestamp’2014-01-02 14:00:00’); insert into classe (NO_CLASS,DESCRIPTION)

values(1,’affaire’);

insert into classe (NO_CLASS,DESCRIPTION) values(2,’economique’);

insert into classevol (NO_CLASS,NO_VOL,prix_de_vol) values(1,’AC2001’,1010.02);

insert into classevol (NO_CLASS,NO_VOL,prix_de_vol) values(2,’AC2001’,300.03);

insert into AVIONVOL (NO_VOL,NO_AVION) values(’AC2001’,1);

insert into AVIONVOL (NO_VOL,NO_AVION) values(’AC2002’,2); update aeroport set etat=’quebec’ where etat=’qc’; update CLASSEVOL set prix_de_vol=PRIX_DE_VOL*2 where no_class = 1;

delete from AVION

where no_modele=’AIRBUS A340’;

2 pts

(4)

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

début de test-tp1.sql

PL/SQL procedure successfully completed. drop table aeroport cascade constraints *

ERROR at line 1:

ORA-00942: table or view does not exist

Table created.

DROP TABLE Vol cascade constraints *

ERROR at line 1:

Table created.

DROP TABLE segmentDeVol CASCADE CONSTRAINTS *

ERROR at line 1:

constraint FKnbrVoL foreign key (no_Vol) REFERENCES Vol *

ERROR at line 11:

ORA-02267: column type incompatible with referenced column type

DROP TABLE classe CASCADE CONSTRAINTS *

(5)

ERROR at line 1:

Table created.

DROP TABLE classeVol CASCADE CONSTRAINTS *

ERROR at line 1:

constraint FK_vol_class foreign key (no_vol) references vol *

ERROR at line 5:

ORA-02253: constraint specification not allowed here

SP2-0734: unknown command beginning "constraint..." - rest of line ignored. SP2-0734: unknown command beginning "constraint..." - rest of line ignored. SP2-0042: unknown command ")" - rest of line ignored.

DROP TABLE Avion CASCADE CONSTRAINTS *

ERROR at line 1:

Table created.

DROP TABLE AvionVol CASCADE CONSTRAINTS *

ERROR at line 1:

Table created.

1 row created.

(6)

1 row created.

values (1,’boeing 747’,’2010-09-08’) *

ERROR at line 2:

ORA-01722: invalid number

values (2,’AIRBUS A380’,’2011-09-01’) *

ERROR at line 2:

values (3,’AIRBUS A340’,’2012-08-01’) *

ERROR at line 2:

1 row created.

insert into SEGMENTDEVOL (NO_VOL,NO_SEGMENT,CODE_AEROPORT_DEPART,CODE_AEROPORT_ARRIVEE, *

ERROR at line 1:

insert into SEGMENTDEVOL (NO_VOL,NO_SEGMENT,CODE_AEROPORT_DEPART,CODE_AEROPORT_ARRIVEE, *

ERROR at line 1:

1 row created.

(7)

insert into classevol (NO_CLASS,NO_VOL,prix_de_vol) *

ERROR at line 1:

insert into classevol (NO_CLASS,NO_VOL,prix_de_vol) *

ERROR at line 1:

insert into AVIONVOL (NO_VOL,NO_AVION) *

ERROR at line 1:

ORA-02291: integrity constraint (IFT187_99.FKAVION_2) violated - parent key not found

insert into AVIONVOL (NO_VOL,NO_AVION) *

ERROR at line 1:

ORA-02291: integrity constraint (IFT187_99.FKAVION_2) violated - parent key not found

1 row updated. update CLASSEVOL *

ERROR at line 1:

0 rows deleted.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options