기말고사 프로젝트 - 1 테이블생성

스타크래프트 게임에 관한 DB 를 만들어보기로 했다

쉽게 개개인의 전적과 승률을 알아보고 관리해주는 그런거...ㅋㅋ

 

4개의 테이블을 사용하기로 했다

개개인의 전적과 해당팀을 볼 수 있는 progamer 테이블과

팀에 관한 내용을 보여주는 team 테이블, 그리고 대회에 관한 league 테이블

마지막으로 전반적인 관리를 해주는 control 테이블이다

 

 

CREATE TABLE team
(team_name varchar2(20) CONSTRAINT team_name_pk PRIMARY KEY,
team_supervisor varchar2(10) NOT NULL,
team_coach varchar2(10),
team_captain varchar2(10) NOT NULL,
team_group varchar2(50) NOT NULL,
team_sponsor varchar2(10),
team_date date,
team_win number(2) NOT NULL,
team_lose number(2) NOT NULL,
team_winner varchar2(100) NOT NULL);

 

CREATE TABLE league
(league_name varchar2(30) CONSTRAINT oneleague_name_pk PRIMARY KEY,
league_location varchar2(30) NOT NULL,
league_sponsor varchar2(10) NOT NULL,
league_final varchar2(10) NOT NULL,
league_final_money NUMBER(8) NOT NULL,
league_semifinal varchar2(10) NOT NULL,
league_semifinal_money NUMBER(8) NOT NULL);

 

CREATE TABLE progamer
(gamer_name varchar2(10) CONSTRAINT gamer_name_pk PRIMARY KEY,
gamer_id varchar2(20) NOT NULL,
gamer_main varchar2(10) NOT NULL,
gamer_age number(2),
gamer_zwin number(2) NOT NULL,
gamer_zlose number(2) NOT NULL,
gamer_twin number(2) NOT NULL,
gamer_tlose number(2) NOT NULL,
gamer_pwin number(2) NOT NULL,
gamer_plose number(2) NOT NULL,
gamer_winner varchar2(100) NOT NULL,
team_name varchar2(20),
CONSTRAINT gamer_team_fk FOREIGN KEY(team_name) REFERENCES team(team_name));

 

 

CREATE TABLE control
(control_manage varchar2(20) CONSTRAINT control_manage_pk PRIMARY KEY,
control_management varchar2(10) NOT NULL,
control_info varchar2(50) NOT NULL);

 

 

*추가사항

 

테이블 더 추가 map 테이블 ( 공식맵 )

전적테이블 추가하기로 함...( 함수만들기 쉽게 하기 위해 도입...^-^;; )

 

 

 

create table map(
map_name varchar2(20) CONSTRAINT map_name_pk PRIMARY KEY,
map_size varchar2(10) NOT NULL,
map_startpoint varchar2(10) NOT NULL,
map_zvp_zwin number(3) NOT NULL,
map_zvp_pwin number(3) NOT NULL,
map_pvt_pwin number(3) NOT NULL,
map_pvt_twin number(3) NOT NULL,
map_tvz_twin number(3) NOT NULL,
map_tvz_zwin number(3) NOT NULL,
map_info varchar2(100));

 

 

create table match(
match_id number(4) CONSTRAINT match_id_pk PRIMARY KEY,
match_date date default SYSDATE,
match_homename varchar(10) NOT NULL,
match_awayname varchar(10) NOT NULL,
map_name varchar(20),
match_time number(5),
match_winner varchar(10) NOT NULL,
match_loser varchar(10) NOT NULL,
CONSTRAINT match_map_fk FOREIGN KEY(map_name) REFERENCES map(map_name));