Tfe

Old stuff/ecole_etude_fac_de_pau/licence_2/bd/table.sql~

 CREATE TABLE interprete (
    code_int  varchar(4) NOT NULL, 
    nom varchar(50) NOT NULL,
    type varchar(15) NOT NULL,
    date_naiss date NOT NULL,
    CONSTRAINT pk_interprete PRIMARY KEY (code_int)  ,
    CONSTRAINT ck_type CHECK (type in ('solo','groupe'))
    );


CREATE TABLE Album(
	code_album varchar(4) NOT NULL,
	titre varchar(30) NOT NULL,
	date_sortie date NOT NULL,
	maison_disque varchar(40) NOT NULL,
	genre varchar(10) NOT NULL,
	code_int varchar(4),
	CONSTRAINT pk_album PRIMARY KEY (code_album), 
	CONSTRAINT fk_album foreign key (code_int) references interprete,
	CONSTRAINT ck_genre CHECK (genre in  ('rock','pop','variété','metal','jazz','autres'))
);

	
CREATE TABLE chanson (
	num_c varchar(4) not null,
	titre_CHAN varchar(50) not null,
	auteur varchar(25) not null,
	compositeur varchar(25) not null,
	duree number(4) not null,
	constraint pk_chanson primary key(num_c),
	constraint ck_duree CHECK(duree between 15 and 3000 ) 
);
		

CREATE TABLE compose(
	code_album varchar(4) NOT NULL,
	num_c	varchar(4) NOT NULL,
	position number(2) NOT NULL,
	CONSTRAINT pk_compose PRIMARY KEY (code_album,num_c),
	CONSTRAINT fk_c_album FOREIGN KEY (code_album) REFERENCES Album,
	CONSTRAINT fk_c_num   FOREIGN KEY (num_c) REFERENCES chanson,
	CONSTRAINT ck_position CHECK ( position BETWEEN 1 and 25)
);