SoccerLeagueDB

create table if not exists League (

   lid int primary key auto_increment,   
   lyear int not null,
   season varchar(10) not null,
   title varchar(30) not null
);

create table if not exists tb_test
(
    id int primary key auto_increment,
    name varchar(200) not null,
    salary float(11,2),
    birthday date,
    sex varchar(5),
    sleep time,
    ts timestamp,
    description text,
    picture blob
);

create table if not exists Player (
   pid int primary key auto_increment,
   name varchar(30) not null,
   address varchar(30) not null,
   city varchar(20) not null,
   email varchar(40) not null
);


--
-- This table represents the relationship between leagues and players.
--
create table if not exists Registration (
   lid int not null,
   pid int not null,
   division varchar(20) not null,
   league_lid int not null,
   player_pid int not null,
   primary key (lid, pid)
);

create table if not exists AdminUser (
   uid int primary key auto_increment,
   username varchar(20) not null,
   password varchar(20) not null
);

-- Create the initial set of leagues.
INSERT INTO League (lid, lyear, season, title) VALUES (1, 2008, ‘Spring‘, ‘Soccer League (Spring ‘‘08)‘);
INSERT INTO League (lid, lyear, season, title) VALUES (2, 2008, ‘Summer‘, ‘Summer Soccer Fest 2008‘);
INSERT INTO League (lid, lyear, season, title) VALUES (3, 2008, ‘Fall‘, ‘Fall Soccer League (2008)‘);
INSERT INTO League (lid, lyear, season, title) VALUES (4, 2009, ‘Spring‘, ‘Soccer League (Spring ‘‘09)‘);
INSERT INTO League (lid, lyear, season, title) VALUES (5, 2009, ‘Summer‘, ‘The Summer of Soccer Love 2009‘);
INSERT INTO League (lid, lyear, season, title) VALUES (6, 2009, ‘Fall‘, ‘Fall Soccer League (2009)‘);

-- Insert the basic AdminUser.
INSERT INTO AdminUser (uid, username, password) VALUES (100, ‘admin‘, ‘admin‘);
INSERT INTO AdminUser (uid, username, password) VALUES (101, ‘jack‘, ‘admin‘);