CREATE TABLE student (
    student_uco NUMBER(10) NOT NULL,
    jmeno VARCHAR2(50) NOT NULL,
    prijmeni VARCHAR2(50) NOT NULL,
    datum_narozeni DATE,
    pohlavi VARCHAR2(1) NOT NULL
);

CREATE TABLE vyuka (
    student_uco NUMBER(10) NOT NULL,
    predmet_id NUMBER(10) NOT NULL
);


CREATE TABLE predmet (
    predmet_id NUMBER(10) NOT NULL,
    ucitel_uco NUMBER(10),
    nazev_predmetu VARCHAR2(50) NOT NULL
);

CREATE TABLE ucitel (
    ucitel_uco NUMBER (10) NOT NULL,
    jmeno VARCHAR(50) NOT NULL,
    prijmeni VARCHAR(50) NOT NULL
);

ALTER TABLE student ADD 
    CONSTRAINT pk_student
    PRIMARY KEY (student_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 ucitel ADD 
    CONSTRAINT pk_ucitel
    PRIMARY KEY (ucitel_uco);
     
ALTER TABLE predmet ADD 
    CONSTRAINT fk_predmet_ucitel
    FOREIGN KEY (ucitel_uco)
     REFERENCES ucitel (ucitel_uco);
     
ALTER TABLE vyuka ADD 
    CONSTRAINT fk_vyuka_student
    FOREIGN KEY (student_uco)
     REFERENCES student (student_uco);
         
ALTER TABLE vyuka ADD 
    CONSTRAINT fk_vyuka_predmet
    FOREIGN KEY (predmet_id)
    REFERENCES predmet (predmet_id);
    
    
INSERT INTO ucitel (ucitel_uco, jmeno, prijmeni) VALUES (11180, 'Daniel', 'Klimeš');
INSERT INTO ucitel (ucitel_uco, jmeno, prijmeni) VALUES (158, 'Mirek', 'Dušín');
INSERT INTO ucitel (ucitel_uco, jmeno, prijmeni) VALUES (150, 'Severus', 'Snape');

INSERT INTO predmet (predmet_id, ucitel_uco, nazev_predmetu) VALUES (1, 11180, 'Databáze');
INSERT INTO predmet (predmet_id, ucitel_uco, nazev_predmetu) VALUES (2, 158, 'Etika');
INSERT INTO predmet (predmet_id, ucitel_uco, nazev_predmetu) VALUES (3, 150, 'Černá magie');
INSERT INTO predmet (predmet_id, ucitel_uco, nazev_predmetu) VALUES (4, 150, 'Lektvary');
INSERT INTO predmet (predmet_id, nazev_predmetu) VALUES (5, 'Penězokazectví');



----------------

CREATE TABLE patients (
    patient_id VARCHAR2 (15 char) NOT NULL,
    date_of_birth DATE,
    sex VARCHAR2(1) 
);

CREATE TABLE patient_study
(
    patient_id VARCHAR2(15 char) NOT NULL,
    study_id NUMBER (11) NOT NULL,
    study_site NUMBER(11),
    date_of_enrollment DATE
);

CREATE TABLE studies
(
  study_id         NUMBER(11)                  NOT NULL,
  study_name       VARCHAR2(50 CHAR)            NOT NULL,
  study_title      VARCHAR2(255 CHAR)           NOT NULL,
  description      VARCHAR2(500 CHAR),
  locked           NUMBER(1)                    DEFAULT 0,
  date_start       DATE,
  is_active        NUMBER(1)                    DEFAULT 1,
  manager          VARCHAR2(30 CHAR),
  date_terminated  DATE,
  web_url          VARCHAR2(255 BYTE)
);

CREATE TABLE sites
(
    site_id NUMBER(11) NOT NULL,
    site VARCHAR2(255 CHAR) NOT NULL
 );
 
 CREATE TABLE studies_sites
 (
    study_id NUMBER(11) NOT NULL,
    site_id NUMBER(11) NOT NULL
 );