----------------------
--test
---------------------------
connect to g2;




-----------------------------------------------------
-----game
-----------------------------------------------------

CREATE TYPE field_t  AS
(

Field_Width double ,

Field_Height double,

Goal_Width double ,

Goal_Height double,

Gravity double,

Friction double

) MODE DB2SQL;



create table game

(

GID varchar(16) NOT NULL,

Field field_t NOT NULL,

PRIMARY KEY (GID)

);


-----------------------------------------------------
-----team
-----------------------------------------------------

create table team
(

Team_Name varchar(16) NOT NULL,

current_score smallint with default 0,

primary key(Team_Name)
);


-----------------------------------------------------
-----player ball mass
-----------------------------------------------------
CREATE TYPE mass_t AS
(

md_mass double,
m_dX double,
m_dY double,
m_dZ double,
m_dDX double,
m_dDY double,
m_dDZ double,
m_dD2X double,
m_dD2Y double,
m_dD2Z double,
m_dFx double,
m_dFy double,
m_dFz double,
m_dFriction double

)MODE DB2SQL;


create table player
(

Player_Name varchar(16) NOT NULL,

Player_Radius double NOT NULL,

Player_Height double NOT NULL,


primary key(Player_Name)

);

create table ball
(
ball_Name varchar(16) not null,

Radius double,

primary key(ball_Name)

);

-----------------------------------------------------
-----member of 
-----------------------------------------------------

create table member_of
(
Player_name varchar(16) NOT NULL,

Team_Name varchar(16) NOT NULL,

primary key(Team_Name,Player_Name),

foreign key(Player_Name) references player(Player_Name),

foreign key(Team_Name) references team(Team_Name)
);

-----------------------------------------------------
-----snap shot 
-----------------------------------------------------

create table snapshot

(

GID varchar(16) NOT NULL,

Time_Stamp BIGINT NOT NULL,

PRIMARY KEY (GID,Time_Stamp),

foreign key (GID) references game(GID)

);


-----------------------------------------------------
-----event
-----------------------------------------------------

create table event

(

GID varchar(16) NOT NULL,

Time_Stamp BIGINT NOT NULL,

event_msg varchar(16),

PRIMARY KEY (GID,Time_Stamp),

foreign key (GID) references game(GID)

);

-----------------------------------------------------
-----position ball
-----------------------------------------------------

create table c_pos_ball
(

GID varchar(16) NOT NULL,

Time_Stamp BIGINT NOT NULL,

ball_name varchar(16) not null,

mass mass_t,

primary key (GID,Time_Stamp ,ball_name),


foreign key (GID,Time_Stamp) references snapshot(GID,Time_Stamp),

foreign key (ball_name) references ball(ball_name)
);


-----------------------------------------------------
-----position player 
-----------------------------------------------------

create table c_pos_player
(

GID varchar(16) NOT NULL,

Time_Stamp BIGINT NOT NULL,

Player_Name varchar(16) NOT NULL,

mass mass_t,

primary key (GID,Time_Stamp ,Player_Name),


foreign key (GID,Time_Stamp) references snapshot(GID,Time_Stamp),

foreign key (Player_Name) references player(Player_Name)
);


-----------------------------------------------------
-----play in
-----------------------------------------------------
create table play_in
(

GID varchar(16) NOT NULL,

Player_Name varchar(16) NOT NULL,

primary key (GID ,Player_Name),

foreign key (GID) references game(GID),

foreign key (Player_Name) references player(Player_Name)

);

-----------------------------------------------------
-----use
-----------------------------------------------------



create table use
(

GID varchar(16) NOT NULL,

ball_name varchar(16) not null,

primary key (GID,ball_name),

foreign key (GID) references game(GID),

foreign key (ball_name) references ball(ball_name)
);



-----------------------------------------------------
-----rivarly
-----------------------------------------------------

create table rivarly
(

GID varchar(16) NOT NULL,

Team_Name_1 varchar(16) NOT NULL,

Team_Name_2 varchar(16) NOT NULL,

primary key (GID),

foreign key (GID) references game(GID)

);

-----------------------------------------------------
connect reset;