#
#  Copyright 2017 Amazon.com
#
#  Licensed under the Apache License, Version 2.0 (the "License");
#  you may not use this file except in compliance with the License.
#  You may obtain a copy of the License at
#
#    http://www.apache.org/licenses/LICENSE-2.0
#
#  Unless required by applicable law or agreed to in writing, software
#  distributed under the License is distributed on an "AS IS" BASIS,
#  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#  See the License for the specific language governing permissions and
#  limitations under the License.



create table if not exists sport_division
(sport_type_name varchar(15) NOT NULL,
 sport_league_short_name varchar(10) NOT NULL,
 short_name varchar(10) NOT NULL,
 long_name varchar(60),
 description varchar(120),
 constraint sport_division_pk primary key(sport_type_name, sport_league_short_name, short_name),
 constraint sd_sport_type_fk foreign key(sport_type_name) references sport_type(name),
 constraint sd_sport_league_fk foreign key(sport_league_short_name) references sport_league(short_name)
);

insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('baseball','MLB','AL East','American League East','American League East');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('baseball','MLB','AL Central','American League Central','American League Central');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('baseball','MLB','AL West','American League West','American League West');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('baseball','MLB','NL East','National League East','National League East');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('baseball','MLB','NL Central','National League Central','National League Central');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('baseball','MLB','NL West','National League West','National League West');



insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('football','NFL','AFC East','American Football Conference East','American Football Conference East');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('football','NFL','AFC West','American Football Conference West','American Football Conference West');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('football','NFL','AFC North','American Football Conference North','American Football Conference North');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('football','NFL','AFC South','American Football Conference South','American Football Conference South');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('football','NFL','NFC East','National Football Conference East','National Football Conference East');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('football','NFL','NFC West','National Football Conference West','National Football Conference West');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('football','NFL','NFC North','National Football Conference North','National Football Conference North');
insert into sport_division(sport_type_name,sport_league_short_name,short_name,long_name,description)
values('football','NFL','NFC South','National Football Conference South','National Football Conference South');