


/* the "rink" table, with primary key */
create table rink (name char(20) primary key, phone char(10), capacity int)



/* the team table, with primary key, foreign keys */
create table team (tname char(10), city char(10), color char(6), wins smallint, 
	losses smallint, tie smallint, rname char(20) references rink(name),
	primary key (tname, city)) 



/* the player table, with primary key, foreign keys */
create table player (id int primary key, pname char(10), num smallint, 
	pos char(2), tname char(10), tcity char(10), 
	foreign key (tname, tcity) references team (tname, city))



/* a check constraint, just for fun:  player num=9 -> pos='G' */
alter table player add constraint np_check1 check ((num<>9) or (pos='G'))




/* Maintain the IC (FD) city-> capacity */
create trigger city_cap_tr
on rink
for update
as
if update (capacity)
    if (exists
	(select * 
	 from (select * from rink UNION select * from inserted) as r, team t
	 where r.name = t.rname
	 group by t.city
	 having (count (distinct r.capacity) > 1)))
	rollback tran


create trigger city_cap_tr2
on team
for insert, update
as
if update (city) or update (rname)
    if (exists
	(select * 
	 from (select * from team UNION select * from inserted) as t, rink r
	 where r.name = t.rname
	 group by t.city
	 having (count (distinct r.capacity) > 1)))
	rollback tran