Relacja ResTemplate-ResInstance (1:n) z jednego schematu może wywodzić się wiele instancji
Klucz obcy ResTemplate_ID (w ResInstance) - instancja badania zawiera klucz szablony, z którego się wywodzi

Relacja Experiment - Client (n:n)- reprezentowana przez encję Entity1, przyporządkowującą klienta i obserwowane przez niego badanie
Klucz obcy client_ID (w Entity1)
Klucz obcy experiment_ID (w Entity1)

Relacja Labgeek - Experiment (1:n) - laborant może stworzyć wiele eksperymentów
Klucz obcy labgeek_ID (w Experiment) - oznacza laboranta, który stworzył dany eksperyment

Relacja ResIntance- Experiment (1:n) - każda instancja badania składa się z wielu eksperymentów
Klucz obcy Resintance_ID (w Experiment) - oznacza instancję badania, do której należy

Relacja Experiment - Experiment (1:1) - obrazuje kolejność następowania po sobie eksperymentów w badaniu
Klucz obcy next_experiment_ID (w Experiment) - prowadzi do ewentualnego kolejnego eksperymentu

Relacja ResTemplate - ExpScheme (1:n) - w szablonie tworzone jest wiele możliwych schematycznych eksperymentów
Klucz obcy Restemplate_ID (w ExpScheme) - oznacza szablon, z którego wywodzi sie eksperyment

Relacja ExpScheme - ResGroup (1:1) - połączenie schematu eksperymentu z grupą jego możliwych wyników
Klucz obcy ExpScheme_ID (w ResGroup) - oznacza schemat, do którego należy grupa wyników

Relacja ResGroup - Result (1:n) - wiele wyników składa się na grupę wyników dla danego schematu
Klucz obcy resgroup_ID (w Result) - oznacza grupę wyników, do której należy konkretny wynik

Relacja Result - ExpScheme (1:1) - połączenie wyniku z sugerowanym na jego podstawie schematem następnego eksperymentu
Klucz obcy next_expscheme_ID (w Result) - oznacza następny sugerowany schemat eksperymentu

Relacja Experiment - LabBoard (1:n) - do danego eksperymentu może 'należeć' wiele płytek laboratoryjnych
Klucz obcy experiment_ID (w LabBoard) - oznacza eksperyment, do którego należy płytka

Relacja LabBoard - Sample (n:n) - realizowana przez pośrednią encję PlaceOnBoard, określającą nie tylko, która próbka należy w danym momencie do której płytki, lecz także określa współrzędne położenia próbki na płytce
Klucz obcy board_ID (w PlaceOnBoard) - oznacza płytkę laboratoryjną, na której umieszczana jest próbka
Klucz obcy sample_ID (w PlaceOnBoard) - oznacza próbkę umieszczaną na konkretnym miejscu płytki

DROP DATABASE lab;
CREATE DATABASE lab
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_polish_ci;
USE lab;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
CREATE TABLE IF NOT EXISTS ADMINISTRATOR
(
	admin_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	login VARCHAR(20) UNIQUE NOT NULL,
	surname  VARCHAR(30),
	name  VARCHAR(30),
	password  VARCHAR(30) NOT NULL,
	CHECK(LEN(password) >= 8)
);
CREATE INDEX ADMIN_login ON ADMINISTRATOR(login);
 
CREATE TABLE IF NOT EXISTS  LABGEEK 
(
	labGeek_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	login VARCHAR(20) UNIQUE NOT NULL,
	surname  VARCHAR(30),
	name  VARCHAR(30),
	password  VARCHAR(30) NOT NULL,
	CHECK(LEN(password) >= 8)
);
CREATE INDEX LABGEEK_login ON LABGEEK(login);
 
CREATE TABLE IF NOT EXISTS  CLIENT 
(
	client_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	login VARCHAR(20) UNIQUE NOT NULL,
	surname  VARCHAR(30),
	name  VARCHAR(30),
	password  VARCHAR(30) NOT NULL,
	CHECK(LEN(password) >= 8)
);
CREATE INDEX CLIENT_login ON CLIENT(login);
 
CREATE TABLE IF NOT EXISTS  ENTITY1 (
	client_ID INTEGER NOT NULL,
	experiment_ID INTEGER NOT NULL
);
 
CREATE TABLE IF NOT EXISTS  EXPERIMENT (
	experiment_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(50),
	description VARCHAR(200),
	result_ID INTEGER,
    labGeek_ID INTEGER,
    expScheme_ID INTEGER,
    resInstance_ID INTEGER,
	FOREIGN KEY (labGeek_ID) REFERENCES LABGEEK(labGeek_ID),
	FOREIGN KEY (expScheme_ID) REFERENCES EXPSCHEME(expScheme_ID),-- FK 
	FOREIGN KEY (resInstance_ID) REFERENCES RESINSTANCE(resInstance_ID)-- FK
);
CREATE INDEX experiment_name ON EXPERIMENT(name);
 
CREATE TABLE IF NOT EXISTS  ADNOTATION 
(
	adnotation_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30),
	content VARCHAR(200),
    experiment_ID INTEGER,
	FOREIGN KEY (experiment_ID) REFERENCES EXPERIMENT(experiment_ID) 
);
 CREATE INDEX adnotation_name ON ADNOTATION(name);
 
CREATE TABLE IF NOT EXISTS  ATTACHMENT 
(
	attachment_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30),
	content VARCHAR(200), 
    experiment_ID INTEGER,
	FOREIGN KEY (experiment_ID) REFERENCES EXPERIMENT(experiment_ID) 
);
CREATE INDEX attachment_name ON ATTACHMENT(name);
 
CREATE TABLE IF NOT EXISTS  LABBOARD (
	board_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	sizeX INTEGER NOT NULL,
	sizeY INTEGER NOT NULL,
    experiment_ID INTEGER, 
    state_ID INTEGER, 
	FOREIGN KEY (experiment_ID) REFERENCES EXPERIMENT(experiment_ID),
	FOREIGN KEY (state_ID) REFERENCES BOARDSTATE(state_ID)
);
 
CREATE TABLE IF NOT EXISTS  SAMPLE (
	sample_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	description VARCHAR(200),
	isOnBoard BOOLEAN,
    experiment_ID INTEGER,
	FOREIGN KEY (experiment_ID) REFERENCES EXPERIMENT(experiment_ID) 
);
 
CREATE TABLE IF NOT EXISTS  PLACEONBOARD (
	place_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	placeX INTEGER,
	placeY INTEGER,
    board_ID INTEGER,
    sample_ID INTEGER,
	FOREIGN KEY (board_ID) REFERENCES LABBOARD(board_ID),
	FOREIGN KEY (sample_ID) REFERENCES SAMPLE(sample_ID)
);
 
CREATE TABLE IF NOT EXISTS  BOARDSTATE (
	state_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	board_state_enum ENUM('przetwarzanie','oczekiwanie', 'wykonane')
);
 
CREATE TABLE IF NOT EXISTS  RESINSTANCE (
	resInstance_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	resTemplate_ID INTEGER,
	FOREIGN KEY (resTemplate_ID) REFERENCES RESTEMPLATE(resTemplate_ID)
);
 
CREATE TABLE IF NOT EXISTS  RESTEMPLATE (
	resTemplate_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
        description VARCHAR(200)
);
 
 
CREATE TABLE IF NOT EXISTS  EXPSCHEME (
	expScheme_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30),
	description VARCHAR(200),
	resTemplate_ID INTEGER,
	resGroup_ID INTEGER,
	FOREIGN KEY (resTemplate_ID)REFERENCES RESTEMPLATE(resTemplate_ID),
	FOREIGN KEY (resGroup_ID)REFERENCES RESGROUP(resGroup_ID)
);
CREATE INDEX expScheme_name ON EXPSCHEME(name);
 
CREATE TABLE IF NOT EXISTS  RESGROUP(
	resGroup_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
);
 
CREATE TABLE IF NOT EXISTS  RESULT_ (
	result_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	resNumber INTEGER,
	description VARCHAR(200),
	resGroup_ID INTEGER,
	expScheme_ID INTEGER,
	FOREIGN KEY (resGroup_ID) REFERENCES RESGROUP(resGroup_ID),
	FOREIGN KEY (expScheme_ID) REFERENCES EXPSCHEME(expScheme_ID)
);

Administrator / LabGeek (laborant) / Client

• login- nazwa użytkownika - łańcuch maksymalnie 20 znaków,
• surname - nazwisko - łańcuch maksymalnie 30 znaków,
• name - imię - łańcuch maksymalnie 30 znaków,
• password - hasło - łańcuch, minimalnie 8, maksymalnie 30 znaków.

Experiment

• name - nazwa eksperymentu - łańcuch maksymalnie 50 znaków,
• description - opis eksperymentu - łańcuch maksymalnie 200 znaków.

Adnotatation

• name - tytuł - łańcuch maksymalnie 30 znaków,
• content - zawartość tekstowa -łańcuch znaków,

Attachment

• name - nazwa załącznika - łańcuch maksymalnie 30 znaków,
• content - zawartość - łańcuch znaków,

Labboard - płytka laboratoryjna

• sizeX - szerokość - liczba typu integer,
• sizeY - wysokość - liczba typu integer,

PlaceOnBoard

• placeX - położenie na płytce na osi x - liczba typu integer,
• placeY - położenie na płytce na osi y - liczba typu integer,

Sample - próbka

• description - opis - łańcuch maksymalnie 200 znaków,
• isOnBoard - flaga, czy aktualnie próbka jest umieszczona na jakiejś płytce - boolean,

Expscheme - ramowy schemat eksperymentu, używany przy tworzeniu drzewa decyzyjnego

• name - nazwa - łańcuch maksymalnie 30 znaków,
• description - opis - łańcuch maksymalnie 200 znaków,
• next_expscheme - id nastepnego sugerowanego eksperymentu

Result - wynik

• resNumber - wynik przedstawiony numerycznie- INTEGER,
• description - opis - łańcuch maksymalnie 200 znaków,

BoardState

• board_state_enum - etap przetwarzania, na którym znajduje się płytka - zmienna wyliczeniowa('przetwarzanie','oczekiwanie', 'wykonane')

• Pierwsza postać normalna 1NF: wszystkie dane są atomiczne. Warunek spełniony.
• Druga postać normalna 2NF: wszystkie atrybuty niekluczowe są w pełni funkcjonalnie zależne od klucza głównego danej tabeli. Warunek spełniony.
• Trzecia postać normalna 3NF: wszystkie atrybuty niekluczowe są bezpośrednio zależne od klucza głównego danej tabeli. Warunek spełniony.

 INSERT INTO ADMINISTRATOR (login, surname, name, password) 
					VALUES 	('andrzejA', 'M', 'Andrzej', 'andrzej12345');

INSERT INTO LABGEEK (login, surname, name, password) 
             VALUES ('andrzejL', 'M', 'Andrzej', 'andrzej12345'), 

INSERT INTO CLIENT (login, surname, name, password) 
             VALUES ('andrzejK', 'M', 'Andrzej', 'andrzej12345'),

UPDATE CLIENT SET surname='Melua', name='Katie' WHERE client_ID = 15 AND password='sprzedamopla';

INSERT INTO EXPERIMENT (name, description, result_ID, labGeek_ID, expScheme_ID, resInstance_ID) 
             VALUES ('exp1', 'exp1descr', 1, 1,1,1);

INSERT INTO ENTITY1	(client_ID, experiment_ID) 
			VALUES	(1,1);

INSERT INTO ADNOTATION (name, content, experiment_ID) 
             VALUES ('adn1', 'adn', 1);

INSERT INTO ATTACHMENT (name, content, experiment_ID) 
             VALUES ('att1', 'adn', 1);

INSERT INTO LABBOARD (sizeX, sizeY, experiment_ID, state_ID)  
		VALUES	(50, 50, 1, 1);

INSERT INTO SAMPLE (description, isOnBoard, experiment_ID)
			VALUES 	('a', 't',1);

INSERT INTO PLACEONBOARD(placeX, placeY, board_ID, sample_ID)
				VALUES	(1,1,1,1);

INSERT INTO EXPSCHEME (name, description, resTemplate_ID, resGroup_ID)
				VALUES	('n1','d',1,1);

INSERT INTO RESULT_ (resNumber, description, resGroup_ID, expScheme_ID)
			VALUES	(1,'d',1,1);

UPDATE PLACEONBOARD SET placeX = 7, placeY = 5 WHERE place_ID = 144; 

DELETE FROM ATTACHMENT WHERE id = 100;