Create table Klient
(
	nomer_klienta int not Null Primary Key ,
	familia varchar(20),
	imya varchar(20),
	otchestvo varchar(20)
);

Create table Materials
(	
	Kod_materiala int not Null Primary Key,
	Kod_Type_materiala int not Null,
	Nazvanie_materiala varchar(20) not Null,
	CONSTRAINT FK_Materials_Type_materiala 
		FOREIGN KEY (Kod_type_materiala) REFERENCES Type_materiala(Kod_type_materiala)
);

Create table Type_materiala 
(
	Kod_type_materiala int not Null Primary Key,
	Nazvanie_type_materiala varchar(20) not Null default 'pesok',
	UNIQUE (Nazvanie_type_materiala ),
	CHECK (Nazvanie_type_materiala IN ('pesok', 'kamen'))
);

Create table Sdelki
(
	Kod_sdelki int not Null Primary Key,
	nomer_klienta int not Null,
	CONSTRAINT FK_Sdelki_Klient
		FOREIGN KEY (nomer_klienta ) REFERENCES Klient(nomer_klienta ),
	kolichestvo real CHECK (kolichestvo>0),

	Kod_materiala int not Null,
	CONSTRAINT FK_Sdelki_Materials
		FOREIGN KEY (Kod_materiala ) REFERENCES Materials(Kod_materiala ),

	Kod_type_materiala int not Null,
	CONSTRAINT FK_Sdelki_Kod_type_materiala 
		FOREIGN KEY (Kod_type_materiala ) REFERENCES Materials(Kod_type_materiala ),

	napravlenie_sdelki varchar(10) not Null default 'long' 
		CHECK (napravlenie_sdelki IN ('long', 'short')),
	zena_otkrytiya money CHECK (zena_otkrytiya>0),
	zena_zakrytiya money CHECK (zena_zakrytiya>0),
	pribyl as 
		case 
			when napravlenie_sdelki = 'long' then 
				(zena_zakrytiya-zena_otkrytiya)*kolichestvo
				else (zena_otkrytiya-zena_zakrytiya)*kolichestvo
		end
);