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');

Performance

• Tier:
• In presentation tier, business tier and integration tier, use load balancing.
• In business tier, use a stateless session Bean pooling mechanisms.
• In SessionFaca control level, choose ReadCommited transaction to improve the transaction processing speed and avoid the dirty data.
• In JMS implementation, use asynchronous communication, store-and-forward mechanism.
• Using BusinessDelegate and set up the cache, according to the shooting, especially to the offer and the product.
• Use the ServiceLocator, conducive to the JNDI lookup.
• Layer:
• Use high-performance servers, increase the number of CPUs to equal or more than four.
• Do pressure test to find out whether the servers meet the performance requirements
• Find out performance bottlenecks if the performance requirement is not satisfied.

Availability

• Tier:
• Set up load balancing and use Session Copy inn presentation tier, business tier and resource tier.
• Use Oracle/RAC technology in resource tier.
• Redundent network connections

Reliability

• Tier:
• Using JMS asynchronous communication technology, control the transaction in the SessionFaca.
• Choose ReadCommited transaction level, improve things to deal with both speed and avoid the dirty data.
• Use cluster failover in presentation tier and business tier.
• In the resource layer using RAC, RAC is a mature product to provide good support for reliability.

Scalability

• Tier:
• Set load balancing for each tier.
• In business tier, use a stateless session Bean pooling mechanisms.
• Use Session Affinity and Session Copy on web server
• Use Off-Load Shared Resources on application server

Security

• Tier:
• In the Web and EJB tier, use JAAS.
• Set the roles of seller, buyer and Auction Market etc., assign relative permissions to each role.
• Use UID and password for logging in.
• Set resource access control in the tiers.
• 
  Layer:
• The Lower Playform layer use Linux operating system, and set up the firewall and SSL.

•  Replication    

•  Load balance

•  Failover          

•  Off-load shared resources     

• Forward cache

 • Request prefetch

• Request short circuit

• Session affinity