Untitled

DROP TABLE coach CASCADE CONSTRAINTS;

DROP TABLE T_Groups CASCADE CONSTRAINTS;

DROP TABLE foul CASCADE CONSTRAINTS;

DROP TABLE goal CASCADE CONSTRAINTS;

DROP TABLE T_Matches CASCADE CONSTRAINTS;

DROP TABLE players CASCADE CONSTRAINTS;

DROP TABLE referee CASCADE CONSTRAINTS;

DROP TABLE t_round CASCADE CONSTRAINTS;

DROP TABLE stadium CASCADE CONSTRAINTS;

DROP TABLE team CASCADE CONSTRAINTS;

CREATE TABLE referee (
    referee_id           VARCHAR2(20) NOT NULL,
    R_name              VARCHAR2(20),
    R_role                VARCHAR2(20),
    nationality         VARCHAR2(20),
    salary              NUMBER(10),
    photo               BLOB
);

CREATE TABLE stadium (
    stadium_id       VARCHAR2(20) NOT NULL,
    S_name          VARCHAR2(20),
    S_location        VARCHAR2(20),
    S_capacity        NUMBER(8),
    photo           BLOB
);

CREATE TABLE coach (
    coach_id         VARCHAR2(20) NOT NULL,
    C_name          VARCHAR2(20),
    salary          NUMBER(10),
    age             NUMBER(2),
    nationality     VARCHAR2(20),
    photo           BLOB
);

CREATE TABLE t_round (
    t_round_id        VARCHAR2(20) NOT NULL,
    t_round_type       VARCHAR2(20)
);


CREATE TABLE T_Matches (
    match_id          VARCHAR2(20),
    team_1_id         VARCHAR2(20),
    team_2_id         VARCHAR2(20),
    match_date        DATE,
    referee_id         VARCHAR2(20),
    t_round_id        VARCHAR2(20),
    stadium_id         VARCHAR2(20)
);

CREATE TABLE foul (
    foul_id          VARCHAR2(20) NOT NULL,
    player_id        VARCHAR2(20),
    card_type         VARCHAR2(20),
    match_id            VARCHAR2(20),
    team_id              VARCHAR2(20)
);


CREATE TABLE goal (
    goal_id              VARCHAR2(20) NOT NULL,
    team_id              VARCHAR2(20),
    player_id_goal      VARCHAR2(20),
    player_id_assist    VARCHAR2(20),
    match_id            VARCHAR2(20)
);

CREATE TABLE players (
    player_id       VARCHAR2(20) NOT NULL,
    P_name          VARCHAR2(20),
    jersey_no       NUMBER(2),
    team_id          VARCHAR2(20),
    age              NUMBER(2),
    height           NUMBER(3),
    salary           NUMBER(10),
    market_value      NUMBER(10),
    sponsor          VARCHAR2(20),
    current_club      VARCHAR2(20),
    total_goal        NUMBER(3),
    total_assist      NUMBER(3),
    photo            BLOB
);

CREATE TABLE T_Groups (
    Group_name         CHAR(1)
);

ALTER TABLE T_Groups
ADD CONSTRAINT pk_groups PRIMARY KEY (Group_name);

ALTER TABLE referee
ADD CONSTRAINT pk_referee PRIMARY KEY (referee_id);

ALTER TABLE stadium
ADD CONSTRAINT pk_stadium PRIMARY KEY (stadium_id);

ALTER TABLE coach
ADD CONSTRAINT pk_coach PRIMARY KEY (coach_id);

ALTER TABLE t_round
ADD CONSTRAINT pk_t_round PRIMARY KEY (t_round_id);

ALTER TABLE team
ADD CONSTRAINT pk_team PRIMARY KEY (team_id);

ALTER TABLE T_Matches
ADD CONSTRAINT pk_T_Matches PRIMARY KEY (match_id);

ALTER TABLE foul
ADD CONSTRAINT pk_foul PRIMARY KEY (foul_id);

ALTER TABLE goal
ADD CONSTRAINT pk_goal PRIMARY KEY (goal_id);

ALTER TABLE players
ADD CONSTRAINT pk_players PRIMARY KEY (player_id);

--FK
ALTER TABLE T_Matches
ADD CONSTRAINT fk1_T_Matches FOREIGN KEY (t_round_id) REFERENCES t_round(t_round_id);

ALTER TABLE T_Matches
ADD CONSTRAINT fk2_T_Matches FOREIGN KEY (stadium_id) REFERENCES stadium(stadium_id);

ALTER TABLE T_Matches
ADD CONSTRAINT fk3_T_Matches FOREIGN KEY (referee_id) REFERENCES referee(referee_id);

ALTER TABLE players
ADD CONSTRAINT fk_players FOREIGN KEY (team_id) REFERENCES team(team_id);

ALTER TABLE goal
ADD CONSTRAINT fk_goal FOREIGN KEY (match_id) REFERENCES T_Matches(match_id);

ALTER TABLE goal
ADD CONSTRAINT fk2_goal FOREIGN KEY (team_id) REFERENCES team(team_id);

ALTER TABLE foul
ADD CONSTRAINT fk_foul FOREIGN KEY (match_id) REFERENCES T_Matches(match_id);

ALTER TABLE foul
ADD CONSTRAINT fk2_foul FOREIGN KEY (team_id) REFERENCES team(team_id);

ALTER TABLE team
ADD CONSTRAINT fk1_team FOREIGN KEY (coach_id) REFERENCES coach(coach_id);

ALTER TABLE team
ADD CONSTRAINT fk2_team FOREIGN KEY (Group_name) REFERENCES T_Groups(Group_name);