/*
  Small Example Database: NFL as of October 27, 2005
*/

Create Database NFL;

use NFL;

Create Table Teams (
  name    varchar(20),
  city    varchar(20) not null,
  wins    int,
  losses  int,
  ties    int,
 Primary Key (name)
);

Create Table Players (
  teamName     varchar(20) not null,
  jerseyNumber int not null,
  name         varchar(20),
  position     char(2),
  DOB          date,
 Primary Key (teamName, jerseyNumber),
 Foreign Key (teamName) references Teams(name)
);

Create Table Coaches (
  cid      int         not null,
  name     varchar(20) not null,
  teamName varchar(20),
  position varchar(10),
 Primary Key (cid),
 Foreign Key (teamName) references Teams(name)
);

/* Teams Data */
insert into Teams(name, wins, losses, ties, city)
values ('Jets', 2, 5, 0, 'New York');

insert into Teams(name, wins, losses, ties, city)
values ('Giants', 4, 2, 0, 'New York');

insert into Teams(name, wins, losses, ties, city)
values ('Cowboys', 4, 3, 0, 'Dallas');

insert into Teams(name, wins, losses, ties, city)
values ('49ers', 1, 5, 0, 'San Francisco');


/* Players Data */
insert into Players(teamName, jerseyNumber, name, position, DOB)
values ('Jets', 80, 'Chrebet', 'WR', '1980-10-8');

insert into Players(teamName, jerseyNumber, name, position, DOB)
values ('Jets', 12, 'Namath', 'QB', '1950-10-8');

insert into Players(teamName, jerseyNumber, name, position, DOB)
values ('Giants', 10, 'Eli', 'QB', '1990-10-8');

insert into Players(jerseyNumber, name, position, DOB,          teamName)
values             (21,          'Tiki', 'RB',    '1985-10-8', 'Giants');

insert into Players(teamName, jerseyNumber, name, position, DOB)
values ('Cowboys', 11, 'Bledsoe', 'QB', '1970-10-8');

insert into Players(teamName, jerseyNumber, name, position, DOB)
values ('49ers', 16, 'Montana', 'QB', '1960-10-8');

/* Coaches Data */
insert into Coaches(cid, name, teamName, position)
values (1, 'Coughlin', 'Giants', 'head');

insert into Coaches(cid, name, teamName, position)
values (2, 'Lewis', 'Giants', 'assistant');

insert into Coaches(cid, name, teamName, position)
values (3, 'Parcells', 'Cowboys', 'head');

insert into Coaches(cid, name, teamName, position)
values (4, 'Edwards', 'Jets', 'head');

insert into Coaches(cid, name, position)
values (5, 'Weis', 'assistant');

insert into Coaches(cid, name, teamName, position)
values (6, 'Handley', null, null);

/* This one should fail.  Copy it into a Query Window to see for yourself.
insert into Players(teamName, jerseyNumber, name, position, DOB)
values ('Bears', 16, 'Montana', 'QB', '1960-10-8');
*/