/*Besoin d'un NOT NULL pour un check IN, sauf si c'est une PK)*/

CREATE TABLE CLIENTS(
Numcli NUMBER(5) constraint client_pk primary key,
nom VARCHAR2(20) not null constraint client_nom_maj check(nom=upper(nom)),
prenom VARCHAR2(20) not null constraint client_prenom_ucf check(prenom=initcap(prenom)),
Nblocation NUMBER(3) not null constraint client_nblocation_val check(Nblocation >= 0 AND Nblocation <= 3)
);

CREATE TABLE VIDEO(
TypeV CHAR(3) constraint video_typev_val check(TypeV IN('DVD', 'USB', 'FIC')),
numero NUMBER(4),
titre VARCHAR2(50),
constraint video_pk primary key(TypeV, numero)
);

CREATE TABLE PRIXLOC(
TypeV CHAR(3) constraint prixloc_typev_val check(TypeV IN('DVD', 'USB', 'FIC')) constraint prixloc_pk primary key,
prix NUMBER(4,2) not null
);

CREATE TABLE LOCATION(
Numcli NUMBER(5),
TypeV CHAR(3) constraint prixloc_typev_val check(TypeV IN('DVD', 'USB', 'FIC')) constraint prixloc_pk primary key,
numero NUMBER(4),
datedeb DATE,
datefin DATE constraint location_datefin_val check(datefin >= datedeb),
constraint location_pk primary key(TypeV,numero,datedeb),
constraint location_fk foreign key(Numcli) references CLIENTS(Numcli)
);

CREATE TABLE FACTURE_LOC(
Numcli NUMBER(5),
Datedeb DATE,
PrixF NUMBER(10,2),
constraint facture_loc_pk primary key(Numcli,Datedeb),
constraint facture_loc_fk foreign key(Numcli) references CLIENTS(Numcli)
);

CREATE TABLE RECAP(
Annee NUMBER(4),
Mois NUMBER(2),
TypeV CHAR(3) constraint recap_typev_val check(TypeV IN('DVD', 'USB', 'FIC')),
NBLOC NUMBER(5) not null constraint recap_nbloc_val check(NBLOC > 0),
constraint recap_pk primary key(Annee,Mois,TypeV)
);