Question

CREATE TABLE Gender (
gender CHAR(1),
description VARCHAR(10),
PRIMARY KEY (gender)
);

CREATE TABLE People (
ID INT,
name VARCHAR(50),
gender CHAR(1),
height FLOAT,
PRIMARY KEY (ID),
FOREIGN KEY (gender) REFERENCES Gender (gender)
);

CREATE TABLE Sports (
ID INT,
name VARCHAR(50),
record FLOAT,
PRIMARY KEY (ID),
UNIQUE (name)
);

CREATE TABLE Competitions (
ID INT,
place VARCHAR(50),
held DATE,
PRIMARY KEY (ID)
);

CREATE TABLE Results (
peopleID INT NOT NULL,
competitionID INT NOT NULL,
sportID INT NOT NULL,
result FLOAT,
PRIMARY KEY (peopleID, competitionID, sportID),
FOREIGN KEY (peopleID) REFERENCES People (ID),
FOREIGN KEY (competitionID) REFERENCES Competitions (ID),
FOREIGN KEY (sportID) REFERENCES Sports (ID)
);

CREATE TABLE Accounts (
peopleID int PRIMARY KEY,
sum varchar(255),
last_updated date,
FOREIGN KEY (peopleID) REFERENCES People (id)
);

Question:

Solution have to be in SQL.

Create a trigger CreateAccountForAthlete that when a new athlete is created, this trigger should
create an account for the athlete. The initial amount on the account should be 0 and don’t forget
to update the column last_updated.

Answer 1

TRIGGER:

create trigger CreateAccountForAthlete

after INSERT

on

People

for each row

Insert into Accounts (peopleID, sum, last_updated) values ((select max(ID) from people), 0, select CURDATE());