Forums PostgreSQL.fr

drop table if exists musiciens, labels, groupes, formations, membres, contrats, evenements cascade;
set datestyle to 'european';

drop domain if exists D_TYPE_PRODUCTION, D_INSTRUMENT cascade;

create domain D_TYPE_PRODUCTION as varchar(20)
	check(D_TYPE_PRODUCTION similar to '(Me|Pu|Ro|Bl|Re|*)%'
		  and length(D_TYPE_PRODUCTION) <= 6);

create domain D_INSTRUMENT as varchar(20)
	check(D_INSTRUMENT similar to '(G|D|B|V|P|G-R|G-B|D-R|D-B|B-R|B-B|V-R|V-B|P-R|P-B|)%'
		  and length(D_INSTRUMENT) <= 3);

-- TABLE labels
create table labels (
	id_label serial	primary key,
	nom char(50) not null,
	type_production D_TYPE_PRODUCTION default 'NC'
);

-- TABLE groupes
create table groupes (
	id_groupe serial primary key,
	nom char(60) not null,
	date_creation date not null,
	date_fin date,
	label integer references labels(id_label) on delete cascade on update cascade
);

-- TABLE musiciens
create table musiciens ( 
	id_musicien serial primary key,
	prenom char(50) not null,
	nom char(50) not null,
	date_naissance date not null check(date_naissance < date_deces),
	date_deces date null,
	instrument D_INSTRUMENT not null
);

alter table musiciens add check(age(date_deces, date_naissance) >= interval '16 years');

-- TABLE formations
create table formations (
	id_formation serial primary key,
	date_creation date not null check(date_creation < date_fin),
	date_fin date null,
	groupe integer not null,
	foreign key(groupe) references groupes(id_groupe)
);

-- TABLE membres
create table membres (
	musicien integer not null,
	formation integer not null,
	date_debut_participation date not null check(date_debut_participation < date_fin_participation),
	date_fin_participation date null,
	primary key(musicien, formation),
	foreign key(musicien) references musiciens(id_musicien),
	foreign key(formation) references formations(id_formation)
);

-- TABLE contrats
create table contrats (
	id_contrat integer not null primary key,
	date_contrat date not null
);

alter table contrats add column cachet integer not null default 1000;

-- TABLE evenements
create table evenements (
	id_evenement integer not null primary key,
	nom char(60) not null,
	lieu char(60) not null,
	nombre_spectateur integer null,
	formation integer references formations(id_formation),
	contrat integer references contrats(id_contrat)
);