DROP TABLE SportDetails CASCADE CONSTRAINTS PURGE;
DROP TABLE EventDetails CASCADE CONSTRAINTS PURGE;
DROP TABLE CompetitorDetails CASCADE CONSTRAINTS PURGE;
DROP TABLE CompetitorEventDetails CASCADE CONSTRAINTS PURGE;

CREATE TABLE SportDetails
(
    sport_id    NUMBER(2),
    sport_name  VARCHAR2(20) NOT NULL UNIQUE,

CONSTRAINT SportD_PK PRIMARY KEY (sport_id)
);

CREATE TABLE EventDetails
(
    event_id    NUMBER(2),
    event_name  VARCHAR2(40) NOT NULL,
    event_date  DATE NOT NULL,
    sport_id    NUMBER(2) NOT NULL,

CONSTRAINT Event_PK PRIMARY KEY (event_id),
CONSTRAINT Sport_Event_FK FOREIGN KEY (sport_id) REFERENCES
SportDetails (sport_id)
);

CREATE TABLE CompetitorDetails
(
    competitor_id    NUMBER(2),
    competitor_name  VARCHAR2(25) NOT NULL,
    competitor_email VARCHAR2(50) NOT NULL UNIQUE,
    competitor_position NUMBER(1) NOT NULL,

CONSTRAINT Competitor_PK PRIMARY KEY (competitor_id),
CONSTRAINT Email_Check CHECK (competitor_email LIKE '%_@__%.__%'),
CONSTRAINT Position_Constraint CHECK(competitor_position BETWEEN 1 AND 8)
);

CREATE TABLE CompetitorEventDetails
(
    competitor_id NUMBER(2) NOT NULL,
    event_id NUMBER(2) NOT NULL,
    CONSTRAINT comp_fk FOREIGN KEY (competitor_id) REFERENCES
    CompetitorDetails (competitor_id),
    CONSTRAINT event_fk FOREIGN KEY (event_id) REFERENCES
    EventDetails (event_id)
);

INSERT INTO SportDetails (sport_id, sport_name)
VALUES (1, 'Athletics');
INSERT INTO SportDetails (sport_id, sport_name)
VALUES (2, 'Swimming');

INSERT INTO EventDetails (event_id, event_name, event_date, sport_id)
VALUES (1, 'Mens 100M Final', TO_DATE('2016/08/14', 'YYYY/MM/DD'), 1);
INSERT INTO EventDetails (event_id, event_name, event_date, sport_id)
VALUES (2, 'Womens 100M Final',TO_DATE('2016/08/13', 'YYYY/MM/DD'), 1);
INSERT INTO EventDetails (event_id, event_name, event_date, sport_id)
VALUES (3, 'Mens 100M Freestyle Final', TO_DATE('2016/08/10', 'YYYY/MM/DD'), 2);

INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
VALUES (1, 'Usain Bolt', 'UB@jam.com', 1);
INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
VALUES (2, 'Justin Gatlin', 'JG@usa.com', 2);
INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
VALUES (3, 'Andre De Grasse', 'ADG@cam.com', 3);
INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
VALUES (4, 'Elaine Thompson', 'ET@jam.com', 1);
INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
VALUES (5, 'Tori Bowie', 'TB@usa.com', 2);
INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
VALUES (6, 'Shelly-Ann Fraser-Price', 'SAFP@jam.com', 3);
INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
VALUES (7, 'Kyle Chambers', 'KC@aus.com', 1);
INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
VALUES (8, 'Peter Timmers', 'PT@bel.com', 2);
INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
VALUES (9, 'Nathan Adrian', 'NA@usa.com', 3);

INSERT INTO Competitor_EventDetails (competitor_id, event_id)
VALUES (1, 1);
INSERT INTO Competitor_EventDetails (competitor_id, event_id)
VALUES (2, 1);
INSERT INTO Competitor_EventDetails (competitor_id, event_id)
VALUES (3, 1);
INSERT INTO Competitor_EventDetails (competitor_id, event_id)
VALUES (4, 2);
INSERT INTO Competitor_EventDetails (competitor_id, event_id)
VALUES (5, 2);
INSERT INTO Competitor_EventDetails (competitor_id, event_id)
VALUES (6, 2);
INSERT INTO Competitor_EventDetails (competitor_id, event_id)
VALUES (7, 3);
INSERT INTO Competitor_EventDetails (competitor_id, event_id)
VALUES (8, 3);
INSERT INTO Competitor_EventDetails (competitor_id, event_id)
VALUES (9, 3);
commit;