Systmes dInformation et Application Anne Universitaire 2017 2018

Systèmes d’Information et Application Année Universitaire 2017 -2018 SIA TD 2 Exercice de SQL LDD : Langage de Définition des Données Emmanuel Fruchart

Exercice : bd. Affair Proposer un script (ensemble de commandes SQL) de création de la base de données bd. Affair.

Exercice (correction 1/4) create database bd. Affair; create table client ( nocli integer not null, nomcli char(50), ruecli char(50), cpcli char(5), villecli char(50), cacli float, constraint pk_client primary key(nocli), constraint ck_cpcli check (cpcli between ‘ 00000’ and ‘ 99999’) ) ;

Exercice (correction 2/4) create table materiel ( nomat integer not null, libmat char(50), qtemat integer, pvmat float, constraint pk_materiel primary key(nomat), constraint ck_qtemat check (qtemat between 0 and 99999) ) ;

Exercice (correction 3/4) create table affaire (noaff integer not null, nocli integer not null, nomat integer not null, dataff date, nbmat integer, nbliv integer, constraint pk_aff primary key(noaff), constraint fk_aff_nocli foreign key(nocli) references client(nocli), constraint fk_aff_nomat foreign key(nomat) references materiel(nomat), constraint ck_nbmat check (nbmat between 0 and 99999), constraint ck_nbliv check (nbliv between 0 and 99999)) ;

Exercice (correction 4/4) create table livraison ( nobl integer not null, noaff integer not null, datexp date, datliv date, qteliv integer, constraint pk_livraison primary key(nobl), constraint fk_livraison_noaff foreign key(noaff) references affaire(noaff), constraint ck_qteliv check (qteliv between 0 and 99999) ) ;