-- vytvorenie databázy:

CREATE DATABASE biologia
DEFAULT CHARACTER SET = 'utf8'


-- vytvorenie tabuliek:

CREATE TABLE risa
(
  id             INT            NOT NULL       AUTO_INCREMENT,
  regnum         VARCHAR(30)   	NOT NULL,
  risa	 	 VARCHAR(30)   	NOT NULL,
  PRIMARY KEY (id)
)
;

CREATE TABLE podrisa
(
  id             INT            NOT NULL       AUTO_INCREMENT,
  subregnum      VARCHAR(30)    NOT NULL,
  podrisa 		 VARCHAR(30)    NOT NULL,
  idRisa		 INT			NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (idRisa) 
    REFERENCES risa (id)
)
;

CREATE TABLE farbiva
(
  id        INT            NOT NULL          AUTO_INCREMENT,
  farbiva   VARCHAR (80)   NOT NULL,
  PRIMARY KEY (id)
)
;

CREATE TABLE rodozmena
(
  id        INT            NOT NULL          AUTO_INCREMENT,
  rodozmena VARCHAR (60)   NOT NULL,
  PRIMARY KEY (id)
)
;

CREATE TABLE oddelenie
(
  id             INT            NOT NULL       AUTO_INCREMENT,
  divisio        VARCHAR(30) 	NOT NULL,
  oddelenie 	 VARCHAR(30)	NOT NULL,
  idPodrisa		 INT 			NOT NULL,
  idFarbiva		 INT 			NOT NULL,
  idRodozmena	 INT			NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (idPodrisa) 
    REFERENCES podrisa (id),
  FOREIGN KEY (idFarbiva) 
    REFERENCES farbiva (id),
  FOREIGN KEY (idRodozmena) 
    REFERENCES rodozmena (id)
)
;

CREATE TABLE trieda
(
  id             INT            NOT NULL       AUTO_INCREMENT,
  classis        VARCHAR(30)    NOT NULL,
  trieda 		 VARCHAR(30),
  idOddelenie	 INT			NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (idOddelenie) 
    REFERENCES oddelenie (id)
)
;

CREATE TABLE rad
(
  id             INT            NOT NULL       AUTO_INCREMENT,
  ordo	         VARCHAR(30)    NOT NULL,
  rad   		 VARCHAR(30),
  idTrieda  	 INT			NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (idTrieda) 
    REFERENCES trieda (id)
)
;

CREATE TABLE celad
(
  id             INT            NOT NULL       AUTO_INCREMENT,
  familia        VARCHAR(30)    NOT NULL,
  celad 		 VARCHAR(30),
  idRad	   		 INT			NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (idRad) 
    REFERENCES rad (id)
)
;

CREATE TABLE rod
(
  id             INT            NOT NULL       AUTO_INCREMENT,
  genus       	 VARCHAR(30)    NOT NULL,
  rod 			 VARCHAR(30)    NOT NULL,
  idCelad		 INT			NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (idCelad) 
    REFERENCES celad (id)
)
;

CREATE TABLE biotop
(
  id        INT            NOT NULL     	  AUTO_INCREMENT,
  biotop    VARCHAR (50)   NOT NULL,
  PRIMARY KEY (id)
)
;

CREATE TABLE vyznam
(
  id        INT            NOT NULL       	  AUTO_INCREMENT,
  vyznam    VARCHAR (50)   NOT NULL,
  PRIMARY KEY (id)
)
;

CREATE TABLE ochrana
(
  id        INT            		NOT NULL          AUTO_INCREMENT,
  popis     VARCHAR (30)   		NOT NULL,
  PRIMARY KEY (id)
)
;

CREATE TABLE plod
(
  id        INT            		NOT NULL       	  AUTO_INCREMENT,
  plod	    VARCHAR (30)   		NOT NULL,
  PRIMARY KEY (id)
)
;

CREATE TABLE druh
(
  id             INT            NOT NULL			AUTO_INCREMENT,
  idRod		     INT		    NOT NULL,
  species  		 VARCHAR(40)	NOT NULL,
  druh     		 VARCHAR(40),
  latinskyNazov	 VARCHAR(70)	NOT NULL,
  slovenskyNazov VARCHAR(70) 	NOT NULL,
  idBiotop		 INT			NOT NULL,
  idVyznam		 INT 			NOT NULL,
  idOchrana	 	 INT 			NOT NULL,
  idPlod		 INT 			NOT NULL,
  obrazok		VARCHAR(80),
  PRIMARY KEY (id),
  FOREIGN KEY (idRod) 
    REFERENCES rod (id),
  FOREIGN KEY (idBiotop) 
    REFERENCES biotop (id),
  FOREIGN KEY (idVyznam) 
    REFERENCES vyznam (id),
  FOREIGN KEY (idOchrana) 
    REFERENCES ochrana (id),
  FOREIGN KEY (idPlod) 
    REFERENCES plod (id)
)
;