DROP TABLE Result;
DROP TABLE Participant;
DROP TABLE Competition;

CREATE TABLE Participant(
       pnumber INT NOT NULL, 
       name VARCHAR(50) NOT NULL,
       CONSTRAINT pk_participant PRIMARY KEY (pnumber) 
);

CREATE TABLE Competition(
       cdate DATE,
       place VARCHAR(20), 
       CONSTRAINT pk_competition PRIMARY KEY (cdate, place)
);

	
CREATE TABLE Result(
       pnumber INT,
       cdate DATE,
       place VARCHAR(20),
       points INT,
       CONSTRAINT pk_result PRIMARY KEY (pnumber, cdate, place),
       CONSTRAINT fk_resParticipant FOREIGN KEY (pnumber) REFERENCES 
Participant(pnumber),
       CONSTRAINT fk_resCompetition FOREIGN KEY (cdate, place) REFERENCES Competition(cdate, place)
);

-- Inserting tuples

-- into Participant


INSERT INTO Participant VALUES (113, 'Alice');
INSERT INTO Participant VALUES (115, 'Bob');
INSERT INTO Participant VALUES (120, 'Chris');

-- into Competition

INSERT INTO Competition VALUES ('16-Feb-09', 'Nottingham');
INSERT INTO Competition VALUES ('17-Feb-09', 'London');
INSERT INTO Competition VALUES ('16-Feb-09', 'Birmingham');

-- into Result

INSERT INTO Result VALUES (113, '16-Feb-09', 'Nottingham', 20);
INSERT INTO Result VALUES (115, '16-Feb-09', 'Nottingham', 30);
