--prohlédněte si skript a nastudujte vložené komentáře
--nakopirujte do pg admin a spusťte


CREATE TABLE vyuka (

    student_uco NUMERIC(10) NOT NULL,
    predmet_id NUMERIC(10) NOT NULL

);





CREATE TABLE predmet (

    predmet_id NUMERIC(10) NOT NULL,
    teacher_uco NUMERIC(10),
    nazev_predmetu VARCHAR(50) NOT NULL

);



CREATE TABLE teacher (

    teacher_uco NUMERIC (10) NOT NULL,
    firstname VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL

);


--vytvoření primárních klíčů
ALTER TABLE student ADD 

    CONSTRAINT pk_student

    PRIMARY KEY (uco);

 

ALTER TABLE vyuka ADD 

    CONSTRAINT pk_vyuka

    PRIMARY KEY (student_uco, predmet_id);

    

ALTER TABLE predmet ADD 

    CONSTRAINT pk_predmet

    PRIMARY KEY (predmet_id);

    

ALTER TABLE teacher ADD 

    CONSTRAINT pk_teacher

    PRIMARY KEY (teacher_uco);

     
--vytvoření vazeb
ALTER TABLE predmet ADD 

    CONSTRAINT fk_predmet_teacher

    FOREIGN KEY (teacher_uco)

    REFERENCES teacher (teacher_uco);

     

ALTER TABLE vyuka ADD 

    CONSTRAINT fk_vyuka_student

    FOREIGN KEY (student_uco)

     REFERENCES student (uco);

         

ALTER TABLE vyuka ADD 

    CONSTRAINT fk_vyuka_predmet

    FOREIGN KEY (predmet_id)

    REFERENCES predmet (predmet_id);
	
	
--naplnění tabulky učitelé	
INSERT INTO teacher (teacher_uco, firstname, lastname) VALUES (11180, 'Daniel', 'Klimeš');
INSERT INTO teacher (teacher_uco, firstname, lastname) VALUES (1, 'Daniel', 'Schwarz');
INSERT INTO teacher (teacher_uco, firstname, lastname) VALUES (2, 'Jiří', 'Holčík');
INSERT INTO teacher (teacher_uco, firstname, lastname) VALUES (3, 'Tomáš', 'Pavlík');
INSERT INTO teacher (teacher_uco, firstname, lastname) VALUES (4, 'Ondřej', 'Májek');
INSERT INTO teacher (teacher_uco, firstname, lastname) VALUES (5, 'Severus', 'Snape');

   

--naplnění tabulky predmet
INSERT INTO predmet (teacher_uco, nazev_predmetu, predmet_id) VALUES (11180, 'Databáze v biomedicíně', 1);
INSERT INTO predmet (teacher_uco, nazev_predmetu, predmet_id) VALUES (5, 'Lektvary', 2);
INSERT INTO predmet (teacher_uco, nazev_predmetu, predmet_id) VALUES (NULL, 'Černá magie', 10);