/* the "rink" table, with primary key and indices */
create table rink (name char(20) primary key nonclustered, phone char(10), capacity int)
create clustered index capacities_index on rink (capacity)

/* the team table, with primary key, foreign keys, and index */
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, and indices */
create table player (id int primary key nonclustered, pname char(10), num smallint, 
	pos char(2), tname char(10), tcity char(10), 
	foreign key (tname, tcity) references team (tname, city))
create clustered index playerpos_index on player (pos)

/* rebuild playerpos_index in an attempt to improve performance */
create clustered index playerpos_index on player (pos) with drop_existing

/* create some new statistics to improve query perf. on team relation */
create statistics team_col_stats on team (wins, losses, tie) with fullscan

/* update all statistics (newly-created column stats. and old index stats.) for team,
	by sampling half of team's tuples */
update statistics team with sample 50 percent

/* create the newplayer table (denormalization) */
select p.*, t.rname
into newplayer
from player p, team t
where p.tname = t.tname and p.tcity = t.city

/* create proper indices, keys on newplayer */
create clustered index newplayerpos_index on newplayer (pos)
alter table newplayer add primary key nonclustered (id)
alter table newplayer add foreign key (tname, tcity) references team (tname, city)
alter table newplayer add foreign key (rname) references rink (name)

/* to ensure that (tname, tcity) -> rname holds in newplayer (as it already does 
	in team): */
	
/* 1.  on updates to newplayer.(tname,tcity,rname) (including insertions), check
	newplayer.rname against proper value from team.rname */
create trigger np_update_tr
on newplayer
for update, insert
/* note that this trigger won't fire if a FK constraint is violated */
as
Declare @oldrinkname char(20), @newrinkname char(20)
IF update (tname) or update (tcity) or update (rname)
	begin
	/* get proper rname and new rname */
	select @oldrinkname = t.rname, @newrinkname = i.rname
	from team t, inserted i
	where t.tname = i.tname and t.city = i.tcity
	/* check new rname */
	if @oldrinkname <> @newrinkname
		rollback tran	
	end

/* 2.  on updates to team.rname, propagate to newplayer.rname */
create trigger t_update_tr
on team
for update
as
IF update (rname)
	update newplayer set rname = i.rname
	from newplayer n, inserted i
	where i.tname = n.tname and i.city = n.tcity

/* drop player and re-create it as a view */
drop table player

create view player as 
select id, pname, num, pos, tname, tcity
from newplayer

/* Vertical decomposition:  create rink_phone, rink_cap, with keys and indices */
select r.name, r.phone
into rink_phone
from rink r

select r.name, r.capacity
into rink_cap
from rink r

alter table rink_phone add primary key (name)
alter table rink_cap add primary key nonclustered (name)
create clustered index cap_index on rink_cap (capacity)

/* If a rink_phone tuple is inserted, insert a corresponding rink_cap tuple if one
	doesn't already exist */
create trigger rp_insert_tr
on rink_phone
for insert
as
	/* Only allow 1 new tuple at a time to make DBA's life easier */
	if @@RowCount > 1
		rollback tran
	else if not exists (select * from rink_cap where rink_cap.name IN 
				(select name from inserted))
		insert into rink_cap select i.name, NULL from inserted i

/* If a rink_cap tuple is inserted, insert a corresponding rink_phone tuple if one
	doesn't already exist */
create trigger rc_insert_tr
on rink_cap
for insert
as
	if @@RowCount > 1
		rollback tran
	else if not exists (select * from rink_phone where rink_phone.name IN 
				(select name from inserted))
		insert into rink_phone select i.name, NULL from inserted i

/* adjust FKs from newplayer, team to point to rink_phone */
sp_fkeys rink  /* gives info. on FKs referencing "rink" */
alter table newplayer drop constraint FK__newplayer__rname__1BFD2C07
alter table newplayer add foreign key (rname) references rink_phone (name)
sp_fkeys rink
alter table team drop constraint FK__team__rname__0CBAE877
alter table team add foreign key (rname) references rink_phone (name)

/* drop "rink" and re-create it as a view to maintain the external schema */
drop table rink

create view rink as 
select rp.name, rp.phone, rc.capacity
from rink_phone rp, rink_cap rc
where rp.name = rc.name

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

/* Maintain the IC (FD) city-> capacity */
create trigger city_cap_tr
on rink_cap
for insert, update
as
if update (capacity)
    if (exists
	(select * 
	 from (select * from rink_cap 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
