Database final touches (single page) + extra credit
BryanAdams
archive.zipdata.sql
insert into Nationality (id, name, abbreviation) VALUES (1,'United States', 'US'), (2,'Canada', 'CA'), (3,'Mexico', 'MX'), (4,'Spain', 'ES'), (5,'Germany', 'DE'); insert into Team (id, name, origin, stadium, coach_first_name, coach_last_name) VALUES (1,'Chicago Fires', 1, 'Toyota Park', 'Frank', 'Yallop'), (2,'Columbus Crew', 1, 'Columbus Crew Stadium', 'Gregg', 'Berhalter'), (3,'D.C. United', 1, 'RFK Stadium', 'Ben', 'Olsen'), (4,'Houston Dynamo', 1, 'BBVA Compass Stadium', 'Wade', 'Barrett'), (5,'New York Red Bulls', 1, 'Red Bull Arena', 'Mike', 'Petke'); insert into Tournament (id, name, type, description) VALUES (1,'Major League', 'L', null), (2,'US Open Cup', 'C', null); insert into Referee (id, first_name, last_name, date_of_birth, nationality) VALUES (1,'Juan', 'Guzman', str_to_date('05/05/1986', 'DD/MM/YYYY'), 1), (2,'Edvin', 'Jurisevic', str_to_date('06/06/1975', 'DD/MM/YYYY'), 1), (3,'Christopher', 'Penso', str_to_date('03/03/1982', 'DD/MM/YYYY'), 1), (4,'Baldomero', 'Toledo', str_to_date('01/01/1970', 'DD/MM/YYYY'), 1), (5,'Ricardo', 'Salazar', str_to_date('02/02/1972', 'DD/MM/YYYY'), 1); insert into Player (id, first_name, last_name, date_of_birth, nationality, height, play_number, field_position, team) VALUES (1,'Alec', 'Kann', str_to_date('08/08/1990', 'DD/MM/YYYY'), 1, 193, 1, 'G', 1), (2,'Hunter', 'Jumper', str_to_date('28/02/1989', 'DD/MM/YYYY'), 1, 188, 3, 'D', 1), (3,'Mike', 'Magee', str_to_date('02/09/1984', 'DD/MM/YYYY'), 1, 175, 9, 'S', 1), (4,'Adam', 'Bedell', str_to_date('01/12/1991', 'DD/MM/YYYY'), 1, 200, 29, 'S', 2), (5,'Chris', 'Pontius', str_to_date('12/05/1987', 'DD/MM/YYYY'), 1, 183, 13, 'S', 3); insert into Match_ (id, tournament, home_team, visitor_team, play_date, home_score, visitor_score) VALUES (1, 1, 1, 2, str_to_date('01/01/2014', 'DD/MM/YYYY'), 0, 0), (2, 2, 1, 2, str_to_date('01/05/2014', 'DD/MM/YYYY'), 1, 1), (3, 1, 3, 1, str_to_date('04/04/2014', 'DD/MM/YYYY'), 0, 2), (4, 1, 2, 1, str_to_date('05/01/2014', 'DD/MM/YYYY'), 1, 0), (5, 1, 2, 3, str_to_date('13/03/2014', 'DD/MM/YYYY'), 0, 0); insert into Match__Referee(id, referee, match_, position) VALUES (1, 1, 1, 'M'), (2, 2, 1, 'R'), (3, 3, 1, 'L'), (4, 4, 1, 'F'), (5, 1, 2, 'M'), (6, 2, 2, 'R'), (7, 3, 2, 'L'), (8, 4, 2, 'F'), (9, 5, 3, 'M'), (10, 4, 3, 'R'), (11, 3, 3, 'L'), (12, 2, 3, 'F'), (13, 4, 4, 'M'), (14, 2, 4, 'R'), (15, 5, 4, 'L'), (16, 3, 4, 'F'), (17, 2, 5, 'M'), (18, 5, 5, 'R'), (19, 1, 5, 'L'), (20, 4, 5, 'F'); insert into Tournament_Team (id, tournament, team) VALUES (1, 1, 1), (2, 2, 1), (3, 1, 2), (4, 2, 2), (5, 1, 3), (6, 2, 3), (7, 1, 4), (8, 2, 4), (9, 1, 5), (10, 2, 5); insert into Penalty (id, player, match_, type, time) VALUES (1, 2, 1, 'Y', 40), (2, 4, 1, 'R', 75), (3, 2, 3, 'Y', 5), (4, 1, 1, 'Y', 90); insert into Goal (id, match_, scorer, assistant, team_received, team_scored, time) VALUES (1, 2, 3, 2, 2, 1, 30), (2, 2, 4, null, 1, 2, 61), (3, 3, 3, null, 3, 1, 12), (4, 3, 2, null, 3, 1, 25), (5, 4, 4, null, 1, 2, 50); commit;
__MACOSX/._data.sql
ddl.sql
create table Nationality ( id int not null AUTO_INCREMENT UNIQUE, name varchar(50) not null, abbreviation char(2) ); alter table Nationality add constraint pk_nationality primary key (id); create table Team ( id int not null, name varchar(100) not null, origin int, stadium varchar(100), coach_first_name varchar(50), coach_last_name varchar(50) ); alter table Team add constraint pk_team primary key (id); alter table Team add constraint fk_team_nationality foreign key (origin) references Nationality (id) on delete set null; create table Tournament ( id int not null, name varchar(100) not null, type char(1), description varchar(256) ); alter table Tournament add constraint pk_tournament primary key (id); alter table Tournament add constraint ck_tournament_type check (type in ('C', 'L')); create table Referee ( id int not null, first_name varchar(30) not null, last_name varchar(30) not null, date_of_birth date, nationality int ); alter table Referee add constraint pk_referee primary key (id); alter table Referee add constraint fk_referee_nationality foreign key (nationality) references Nationality (id) on delete set null; create table Player ( id int not null, first_name varchar(30) not null, last_name varchar(30) not null, date_of_birth date, nationality int, height int, play_number int not null, field_position char(1) not null, team int not null ); alter table Player add constraint pk_player primary key (id); alter table Player add constraint fk_player_nationality foreign key (nationality) references Nationality (id) on delete set null; alter table Player add constraint fk_player_team foreign key (team) references Team (id); alter table Player add constraint ck_player_position check (field_position in ('G', 'D', 'M', 'S')); create table Match_ ( id int not null, tournament int not null, home_team int not null, visitor_team int not null, play_date date not null, home_score int, visitor_score int ); alter table Match_ add constraint pk_Match_ primary key (id); alter table Match_ add constraint fk_Match__tournament foreign key (tournament) references Tournament (id) on delete cascade; alter table Match_ add constraint fk_Match__home foreign key (home_team) references Team (id); alter table Match_ add constraint fk_Match__visitor foreign key (visitor_team) references Team (id); create table Match__Referee ( id int not null, referee int not null, Match_ int not null, position char(1) not null ); alter table Match__Referee add constraint pk_Match__referee primary key (id); alter table Match__Referee add constraint fk_Match__referee_referee foreign key (referee) references Referee (id); alter table Match__Referee add constraint fk_Match__referee_Match_ foreign key (Match_) references Match_ (id) on delete cascade; alter table Match__Referee add constraint ck_Match__referee_position check (position in ('M', 'R', 'L', 'F')); create table Tournament_Team ( id int not null, tournament int not null, team int not null ); alter table Tournament_Team add constraint pk_tournament_team primary key (id); alter table Tournament_Team add constraint fk_tournament_team_tournament foreign key (tournament) references Tournament (id) on delete cascade; alter table Tournament_Team add constraint fk_tournament_team_team foreign key (team) references Team (id) on delete cascade; create table Penalty ( id int not null, player int not null, Match_ int not null, type char(1) not null, time int not null ); alter table Penalty add constraint pk_penalty primary key (id); alter table Penalty add constraint fk_penalty_player foreign key (player) references Player (id); alter table Penalty add constraint fk_penalty_Match_ foreign key (Match_) references Match_ (id) on delete cascade; alter table Penalty add constraint ck_penalty_type check (type in ('R', 'Y')); alter table Penalty add constraint ck_penalty_time check (time between 1 and 128); create table Goal ( id int not null, Match_ int not null, scorer int not null, assistant int, team_received int not null, team_scored int not null, time int not null ); alter table Goal add constraint pk_goal primary key (id); alter table Goal add constraint fk_goal_Match_ foreign key (Match_) references Match_ (id) on delete cascade; alter table Goal add constraint fk_goal_scorer foreign key (scorer) references Player (id); alter table Goal add constraint fk_goal_assistant foreign key (assistant) references Player (id); alter table Goal add constraint fk_goal_team_received foreign key (team_received) references Team (id); alter table Goal add constraint fk_goal_team_scored foreign key (team_scored) references Team (id); alter table Goal add constraint ck_goal_time check (time between 1 and 128);
__MACOSX/._ddl.sql
indexes.sql
create unique index idx_nationality_abbreviation on Nationality (abbreviation); create index idx_team_name on Team (name); create index idx_team_origin on Team (origin); create index idx_tournament_name on Tournament (name); create index idx_referee_name on Referee (first_name, last_name); create index idx_referee_nationality on Referee (nationality); create index idx_player_name on Player (first_name, last_name); create index idx_player_nationality on Player (nationality); create unique index idx_player_team_nr on Player (team, play_number); create index idx_player_dob on Player (date_of_birth); create index idx_Match__tournament on Match_ (tournament); create index idx_Match__team_home on Match_ (home_team); create index idx_Match__team_visitor on Match_ (visitor_team); create index idx_Match__date on Match_ (play_date); create index idx_Match__referee_referee on Match__Referee (referee); create unique index idx_tournament_team on Tournament_Team (tournament, team); create index idx_penalty_player on Penalty (player); create index idx_penalty_Match_ on Penalty (Match_); create index idx_goal_scorer on Goal (scorer); create index idx_goal_assistant on Goal (assistant); create index idx_goal_Match_ on Goal (Match_); create index idx_goal_received on Goal (team_received); create index idx_goal_scored on Goal (team_scored);
__MACOSX/._indexes.sql
integrity.sql
create or replace function check_team_player_count return number is v_count number; begin select count(*) into v_count from Team T where (select count(*) from Player P where P.team = T.id) < 18; return (v_count); end; / --select check_team_player_count from dual; create or replace function check_tournament_team_count return number is v_count number; begin select count(*) into v_count from Tournament T where (select count(*) from Tournament_Team TT where TT.tournament = T.id) < 2; return (v_count); end; / --select check_tournament_team_count from dual; /*create assertion chk_match_tournament check ((select count(*) from `Match` m1 where exists (select 1 from `Match` m2, Tournament t where m2.tournament = m1.tournament and m1.home_team = m2.home_team and m1.visitor_team = m2.visitor_team and t.id = m1.tournament and t.type = 'L')) <= 1); */ alter table Goal add constraint ckt_goal check (assistant <> scorer); create unique index idx_match_referee on Match_Referee (match, position);
__MACOSX/._integrity.sql
queries.sql
-- VI.2.1 List of referees that controlled at least one match_ as Main Referee select distinct R.last_name, R.first_name from Referee r, Match__Referee mr where mr.referee = r.id and mr.position = 'M' order by R.last_name, R.first_name; -- VI.2.2 Number of registered players select count(*) from Player; -- VI.2.3 List of players from tallest one select last_name, first_name, height from Player order by height desc; -- VI.2.4 List of players and their teams with positions select T.name, P.last_name, P.first_name, P.field_position, P.play_number from Player P, Team T where P.team = T.id order by T.name, P.field_position, P.play_number; -- VI.2.5 match_ with the greatest number of goals scored select T.name, H.name, V.name, M.home_score, M.visitor_score from Match_ M, Team H, Team V, Tournament T where M.home_team = H.id and M.visitor_team = V.id and M.tournament = T.id and home_score + visitor_score = (select max(X.home_score + X.visitor_score) from Match_ X); -- VI.2.6 Who is the most penalized player? select PL.id, PL.first_name, PL.last_name, count(*) from Player PL, Penalty PE1 where PE1.player = PL.id group by PL.id, PL.first_name, PL.last_name having count(*) = (select max(T.cnt) from (select PE2.player, count(*) as cnt from Penalty PE2 group by PE2.player) T); -- VI.2.7 Which referee is responsible for the greatest number of penalties at all? select R.id, R.first_name, R.last_name, count(*) from Referee R, Match__Referee MR, Match_ M, Penalty P where R.id = MR.referee and MR.match_ = M.id and P.match_ = M.id and MR.position = 'M' group by R.id, R.first_name, R.last_name having count(*) = (select max(T.cnt) from (select MR2.referee, count(*) as cnt from Match_ M2, Match__Referee MR2, Penalty P2 where MR2.match_ = M2.id and MR2.position = 'M' and P2.match_ = M2.id group by MR2.referee) T); -- VI.2.8 Best of teams at home (the greatest number of wins) select T.name, count(*) from Team T, Match_ M where M.home_team = T.id and M.home_score > M.visitor_score group by T.name order by count(*) desc; -- VI.2.9 List of most recent match_es select T.name, H.name, V.name, M.play_date, M.home_score, M.visitor_score from Tournament T, Match_ M, Team H, Team V where M.home_team = H.id and M.visitor_team = V.id and T.id = M.tournament order by M.play_date desc; -- VI.2.10 Who is the oldest player? select P.first_name, P.last_name from Player P where P.date_of_birth = (select min(date_of_birth) from Player) -- VI.2.11 Which referee was responsible for the greatest number of penalties in a single match_ against a home team? select R.id, R.first_name, R.last_name, count(*) from Referee R, Match__Referee MR, Match_ M, Penalty P, Player PL where R.id = MR.referee and MR.match_ = M.id and P.match_ = M.id and MR.position = 'M' and P.player = PL.id and PL.team = M.home_team group by R.id, R.first_name, R.last_name having count(*) = (select max(t.cnt) from (select MR2.referee, count(*) as cnt from Match_ M2, Match__Referee MR2, Penalty P2, Player PL2 where MR2.match_ = M2.id and MR2.position = 'M' and P2.match_ = M2.id and P2.player = PL2.id and PL2.team = M2.home_team group by MR2.referee) T);
__MACOSX/._queries.sql
security.sql
create role readers; create role writers; create role admins; grant select on Nationality to readers; grant select on Team to readers; grant select on Tournament to readers; grant select on Referee to readers; grant select on Player to readers; grant select on Match to readers; grant select on Match_Referee to readers; grant select on Tournament_Team to readers; grant select on Penalty to readers; grant select on Goal to readers; grant select, insert, update, delete on Nationality to writers; grant select, insert, update, delete on Team to writers; grant select, insert, update, delete on Tournament to writers; grant select, insert, update, delete on Referee to writers; grant select, insert, update, delete on Player to writers; grant select, insert, update, delete on Match to writers; grant select, insert, update, delete on Match_Referee to writers; grant select, insert, update, delete on Tournament_Team to writers; grant select, insert, update, delete on Penalty to writers; grant select, insert, update, delete on Goal to writers; grant all on Nationality to admins; grant all on Team to admins; grant all on Tournament to admins; grant all on Referee to admins; grant all on Player to admins; grant all on Match to admins; grant all on Match_Referee to admins; grant all on Tournament_Team to admins; grant all on Penalty to admins; grant all on Goal to admins;
