create table cat_tag
(
cat_id int not null,
tag varchar(255) not null,

constraint pk_cat_tag primary key( cat_id, tag ),
constraint un_cat_tag unique( tag, cat_id ),
constraint fk_cat_IN_cat_tag foreign key (cat_id) reference (cat)
);

create table catalogue
(
cat_id int not null,
cat_name varchar(255) not null,
-- ...                               
constraint pk_catalogue primary key( cat_id )
)

create table cat_tag
(
cat_id int not null,
tag varchar(255) not null,

constraint pk_cat_tag primary key( cat_id, tag ),
constraint un_cat_tag unique( tag, cat_id ),
constraint fk_cat_IN_cat_tag foreign key (cat_id) references catalogue ( cat_id );
);

-- эта таблица НЕ НУЖНА, если 
create table tag
(
tag varchar(255) not null,
-- .. tag attributes:
use_frequency_count int not null default 0,
-- end tag attributes
constraint pk_tag primary key( tag )
);


create procedure tag_catalogue ( @cat_id int, @tag varchar(255) )
begin

   -- только если нужна таблица TAG
   if not exists( select * from tag where tag = @tag ) then
     insert into TAG( tag, use_frequency_count )
     values ( @tag, 0 );
   end if;

   insert into cat_tag ( cat_id, tag )
   values ( @cat_id, @tag );

end;
/

create procedure get_catalogue_by_tag1 ( @tag varchar(255) )
begin

   select c.cat_id, cat_name
   from CAT c
   join cat_tag ct1 on ct1.cat_id = c.cat_id and cat1.tag = @tag;

end;
/

create procedure get_catalogue_by_tag2 ( @tag1 varchar(255), @tag2 varchar(255) )
begin

   select c.cat_id, cat_name
   from CAT c
   join cat_tag ct1 on ct1.cat_id = c.cat_id and cat1.tag = @tag1
   join cat_tag ct2 on ct2.cat_id = c.cat_id and cat2.tag = @tag2;

end;
/

create procedure get_catalogue_by_tag3 ( @tag1 varchar(255), @tag2 varchar(255), @tag3 varchar(255) )
begin

   select c.cat_id, cat_name
   from CAT c
   join cat_tag ct1 on ct1.cat_id = c.cat_id and cat1.tag = @tag1
   join cat_tag ct2 on ct2.cat_id = c.cat_id and cat2.tag = @tag2
   join cat_tag ct3 on ct3.cat_id = c.cat_id and cat3.tag = @tag3
;

end;
/

constraint fk_cat_IN_cat_tag foreign key (cat_id) reference (cat)