CREATE TABLE Country  
    ( 
     country_id NUMBER(3) PRIMARY KEY, 
     country_name VARCHAR(3) UNIQUE NOT NULL, 
     CONSTRAINT check_country_id CHECK (country_id > 0) 
    )

    CREATE TABLE OG_Type  
    ( 
     og_type_id NUMBER(3) PRIMARY KEY, 
     og_type_title VARCHAR(20) UNIQUE NOT NULL 
    )

    CREATE TABLE Olympic_Game  
    ( 
     og_id NUMBER(3) PRIMARY KEY, 
     og_type_id NUMBER(3) NOT NULL, 
     og_year NUMBER(4) NOT NULL, 
     og_website VARCHAR(150), 
     og_cancel VARCHAR(1) NOT NULL, 
     country_id NUMBER(3) NOT NULL, 
     CONSTRAINT check_og_id CHECK (og_id > 0), 
     CONSTRAINT check_og_year_og_type UNIQUE (og_type_id, og_year), 
     CONSTRAINT fk_og_type_id FOREIGN KEY(og_type_id) REFERENCES OG_Type(og_type_id), 
     CONSTRAINT fk_country_id FOREIGN KEY(country_id) REFERENCES Country(country_id) 
    )

    CREATE TABLE Sport  
    ( 
     sport_id NUMBER(3) PRIMARY KEY, 
     sport_title VARCHAR(100) UNIQUE NOT NULL, 
     CONSTRAINT check_sport_id CHECK (sport_id > 0) 
    )

      CREATE TABLE Event  
    ( 
     event_id NUMBER(6) PRIMARY KEY, 
     sport_id NUMBER(3) NOT NULL, 
     og_id NUMBER(3) NOT NULL, 
     event_title VARCHAR(100) NOT NULL, 
     event_team  VARCHAR(1) NOT NULL, 
     no_per_team  NUMBER(2) NOT NULL, 
     event_gender VARCHAR(1) NOT NULL, 
     CONSTRAINT check_event_id CHECK (event_id > 0), 
     CONSTRAINT check_wtf UNIQUE (event_title, sport_id, og_id, event_team, event_gender), 
     CONSTRAINT check_event_team CHECK (event_team IN ('Y','N')), 
     CONSTRAINT check_event_team_no_per_team CHECK ((event_team='N' AND no_per_team=1) OR (event_team='Y' AND no_per_team>1)), 
     CONSTRAINT check_event_gender CHECK (event_gender IN ('M','F')), 
     CONSTRAINT fk_sport_id FOREIGN KEY(sport_id) REFERENCES Sport(sport_id), 
     CONSTRAINT fk_og_id FOREIGN KEY(og_id) REFERENCES Olympic_Game(og_id) 
    )

 create sequence country_seq

 create sequence  og_type_seq

  create sequence og_seq

   create sequence event_seq

   create sequence sport_seq

     insert into country(country_id,country_name) values (country_seq.nextval,'FRA')

    insert into country(country_id,country_name) values (country_seq.nextval,'GBR')

    insert into country(country_id,country_name) values (country_seq.nextval,'GRE')

    insert into country(country_id,country_name) values (country_seq.nextval,'USA')

   insert into og_type(og_type_id, og_type_title) values(og_type_seq.nextval,'Summer')

    insert into og_type(og_type_id, og_type_title) values(og_type_seq.nextval,'Winter')

    insert into og_type(og_type_id, og_type_title) values(og_type_seq.nextval,'Special')

    insert into og_type(og_type_id, og_type_title) values(og_type_seq.nextval,'Youth')

    insert into og_type(og_type_id, og_type_title) values(og_type_seq.nextval,'Senior')

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,1,1896,null,'N',3)

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,1,1900,null,'N',1)

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,1,1904,null,'N',4)

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,1,1908,'op1908.org','N',2)

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,2,1924,null,'N',1)

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,1,1944,null,'Y',2)

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,3,1944,null,'Y',2)

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,4,1944,null,'Y',2)

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,5,1944,null,'Y',2)

    insert into olympic_game(og_id,og_type_id,og_year,og_website,og_cancel,country_id) values(og_seq.nextval,1,2012,'https://www.olympic.org/london-2012','N',2)

    insert into sport(sport_id,sport_title) values(sport_seq.nextval, 'Track and Field')

    insert into sport(sport_id,sport_title) values(sport_seq.nextval, 'Tennis')

    insert into sport(sport_id,sport_title) values(sport_seq.nextval, 'Speed Skating')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,1,1,'100m','N',1,'M')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,2,1,'Double','Y',2,'M')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,1,2,'200m','N',1,'M')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,2,2,'Single','N',1,'M')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,1,3,'400m','N',1,'F')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,1,3,'100m','N',1,'F')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,1,4,'1500m','N',1,'F')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,3,5,'800m','N',1,'F')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,1,10,'100m','N',1,'M')

    insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender) values(event_seq.nextval,1,10,'100m','N',1,'F')

CREATE OR REPLACE TRIGGER TR_event_on_cancelled   
BEFORE INSERT OR UPDATE 
ON Event 
FOR EACH ROW 
DECLARE  
    v_og_cancel Olympic_Game.og_cancel%TYPE; 
BEGIN 
    SELECT og_cancel INTO V_og_cancel  
    FROM Olympic_Game 
    WHERE Olympic_Game.og_id = :NEW.og_id; 
    IF v_og_cancel = 'Y' 
    THEN 
        RAISE_APPLICATION_ERROR(-20001, 'This Olympic Game is cancelled already'); 
    END IF; 
END; 

insert into event(event_id,sport_id,og_id,event_title,event_team,no_per_team,event_gender)  
values(event_seq.nextval,2,9,'Double','Y',2,'M')

select * from olympic_game where og_id = 9