iltdb

/*

DROP TABLE vote;

DROP TABLE team;

DROP TABLE team_member;

DROP TABLE answer;

DROP TABLE question;

*/

CREATE TABLE team_member(
    id_member INT NOT NULL AUTO_INCREMENT,
    fname VARCHAR(20),
    lname VARCHAR(20),
    email VARCHAR(80),
    pass VARCHAR(200),
    CONSTRAINT pk_team_member
        PRIMARY KEY(id_member)
);

CREATE TABLE team (
    id_team INT NOT NULL AUTO_INCREMENT,
    id_member INT,
    name VARCHAR(40),
    CONSTRAINT pk_team
         PRIMARY KEY(id_team),
    CONSTRAINT pk_team_leader
        FOREIGN KEY(id_member) REFERENCES team_member(id_member)
            ON DELETE CASCADE
            ON UPDATE CASCADE
);

CREATE TABLE vote(
    id_team INT,
    id_member INT,
    year_vote YEAR,
    mount INT CHECK (mount>0 AND mount<13),
    CONSTRAINT pk_vote
        PRIMARY KEY(id_team,id_member),
    CONSTRAINT fk_team_vote
        FOREIGN KEY(id_team) REFERENCES team(id_team)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_team_member_vote
        FOREIGN KEY(id_member) REFERENCES team_member(id_member)
            ON DELETE CASCADE
            ON UPDATE CASCADE
);

CREATE TABLE question(
    id_question INT NOT NULL AUTO_INCREMENT,
    question_set TEXT,
    CONSTRAINT pk_question
        PRIMARY KEY(id_question)

);

CREATE TABLE answer(
    id_answer INT NOT NULL AUTO_INCREMENT,
    id_question INT,
    rate INT CHECK (rate>0 AND rate<6),
    CONSTRAINT pk_answer
        PRIMARY KEY(id_answer,id_question),
    CONSTRAINT fk_question_answer
        FOREIGN KEY(id_question) REFERENCES question(id_question)
            ON DELETE CASCADE
            ON UPDATE CASCADE
);


/*  Data    */

/* for team_member  */
insert into team_member (id_member, fname, lname, email, pass) values (1, 'Gwenneth', 'Howis', 'ghowis0@paypal.com', '7hLw356OiH');
insert into team_member (id_member, fname, lname, email, pass) values (2, 'Theodosia', 'Allaker', 'tallaker1@squarespace.com', '4YJ36p');
insert into team_member (id_member, fname, lname, email, pass) values (3, 'Elroy', 'Boeter', 'eboeter2@slashdot.org', 'eIDXfArSlu');
insert into team_member (id_member, fname, lname, email, pass) values (4, 'Bart', 'Peppett', 'bpeppett3@scientificamerican.com', '44GCapy');
insert into team_member (id_member, fname, lname, email, pass) values (5, 'Dill', 'Kaszper', 'dkaszper4@jigsy.com', 'K3WaTR');
insert into team_member (id_member, fname, lname, email, pass) values (6, 'Jason', 'D''Alwis', 'jdalwis5@nymag.com', '4OUXu8w');
insert into team_member (id_member, fname, lname, email, pass) values (7, 'Elianore', 'Paridge', 'eparidge6@meetup.com', 'RpJxxmih');
insert into team_member (id_member, fname, lname, email, pass) values (8, 'Kelsey', 'Buckoke', 'kbuckoke7@purevolume.com', 'BO4JOJ');
insert into team_member (id_member, fname, lname, email, pass) values (9, 'Carolina', 'Mintrim', 'cmintrim8@shop-pro.jp', '4YRnlL91rfln');
insert into team_member (id_member, fname, lname, email, pass) values (10, 'Daffy', 'Petrelluzzi', 'dpetrelluzzi9@blogtalkradio.com', 'g089GMHJ8d9S');


/* for team */
insert into team (id_team, id_member, name) values (1, 1, 'Research and Development');
insert into team (id_team, id_member, name) values (2, 2, 'Training');
insert into team (id_team, id_member, name) values (3, 3, 'Sales');

/* for vote */
insert into vote (id_team, id_member, year_vote, mount) values (1, 1, 2018, 1);
insert into vote (id_team, id_member, year_vote, mount) values (1, 2, 2018, 1);

insert into vote (id_team, id_member, year_vote, mount) values (1, 3, 2018, 1);
insert into vote (id_team, id_member, year_vote, mount) values (1, 4, 2018, 1);
insert into vote (id_team, id_member, year_vote, mount) values (1, 5, 2018, 1);
insert into vote (id_team, id_member, year_vote, mount) values (2, 6, 2018, 2);
insert into vote (id_team, id_member, year_vote, mount) values (2, 7, 2018, 2);
insert into vote (id_team, id_member, year_vote, mount) values (2, 8, 2018, 2);
insert into vote (id_team, id_member, year_vote, mount) values (2, 9, 2018, 3);
insert into vote (id_team, id_member, year_vote, mount) values (2, 10, 2018, 3);
insert into vote (id_team, id_member, year_vote, mount) values (2, 1, 2018, 3);
insert into vote (id_team, id_member, year_vote, mount) values (2, 2, 2018, 3);
insert into vote (id_team, id_member, year_vote, mount) values (3, 3, 2018, 3);
insert into vote (id_team, id_member, year_vote, mount) values (3, 4, 2018, 3);
insert into vote (id_team, id_member, year_vote, mount) values (3, 5, 2018, 3);


/* for questions */
insert into question (id_question, question_set) values (1, 'What was it like working for your manager?');
insert into question (id_question, question_set) values (2, 'What do people most often criticize about you?');
insert into question (id_question, question_set) values (3, 'What motivates you?');
insert into question (id_question, question_set) values (4, 'Why are you moving on?');
insert into question (id_question, question_set) values (5, 'Who was your best manager and who was the worst?');
insert into question (id_question, question_set) values (6, 'Do you prefer to work independently or on a team?');
insert into question (id_question, question_set) values (7, 'How do you plan to achieve those goals?');
insert into question (id_question, question_set) values (8, 'What are you looking for in your next job?');
insert into question (id_question, question_set) values (9, 'What are your salary expectations?');
insert into question (id_question, question_set) values (10, 'What do people most often criticize about you?');

insert into answer (id_answer, id_question, rate) values (1, 1, 1);
insert into answer (id_answer, id_question, rate) values (2, 2, 2);
insert into answer (id_answer, id_question, rate) values (3, 3, 2);
insert into answer (id_answer, id_question, rate) values (4, 4, 4);
insert into answer (id_answer, id_question, rate) values (5, 5, 4);
insert into answer (id_answer, id_question, rate) values (6, 1, 4);
insert into answer (id_answer, id_question, rate) values (7, 2, 3);
insert into answer (id_answer, id_question, rate) values (8, 3, 2);
insert into answer (id_answer, id_question, rate) values (9, 4, 1);
insert into answer (id_answer, id_question, rate) values (10, 5, 5);
insert into answer (id_answer, id_question, rate) values (11, 6, 2);
insert into answer (id_answer, id_question, rate) values (12, 7, 2);
insert into answer (id_answer, id_question, rate) values (13, 8, 3);
insert into answer (id_answer, id_question, rate) values (14, 9, 1);
insert into answer (id_answer, id_question, rate) values (15, 10, 1);
insert into answer (id_answer, id_question, rate) values (16, 10, 1);
insert into answer (id_answer, id_question, rate) values (17, 9, 5);
insert into answer (id_answer, id_question, rate) values (18, 8, 3);
insert into answer (id_answer, id_question, rate) values (19, 7, 4);
insert into answer (id_answer, id_question, rate) values (20, 7, 5);