=======PROJEKT LOGICZNY=======

===== 1. Projektowanie tabel, kluczy, kluczy obcych, powiązań między tabelami, indeksów, etc. w oparciu o zdefiniowany diagram konceptualny ERD: =====
Klucze obce są oznaczone czerwonym rombem na diagramie ERD w punkcie 2. Ich nazwa wskazuje do której tabeli się odnoszą.

Indeksy zostały utworzone dla wszystkich kluczy obcych. Dodatkowo indeksowana jest kolumna issn w encji Copernicus, ze względu na wyszukiwanie przy dodawaniu do bazy publikacji, czy czasopismo, w którym był opublikowany artykuł znajduje się na liście punktowanych czasopism. Indeksowana jest również kolumna username w encji User ze względu na korzystanie z niej za każdym razem gdy następuje logowanie użytkownika. 

Wszystkie indeksy są wypisane na diagramie ERD, u dołu odpowiednich encji.

===== 2. Diagram ERD w notacji Barkera/Oracle=====
{{:pl:dydaktyka:sbd:2012:projekty:bibliografia:logiczny.png|}}
===== 3. Tablica krzyżowa =====
{{:pl:dydaktyka:sbd:2012:projekty:bibliografia:krzyz.png|}}
  *T1 - USER		
  *T2 - LOG
  *T3 - AUTHOR
  *T4 - PUBLICATION
  *T5 - COPERNICUS
  *T6 - ACTION

===== 4. Projekt bazy w języku SQL=====
<code sql>
CREATE SEQUENCE id_copernicus
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE  TABLE IF NOT EXISTS copernicus (
  id INT(11) DEFAULT NEXTVAL('id_copernicus') PRIMARY KEY,
  title VARCHAR(128) NOT NULL ,
  issn INT(11) NOT NULL ,
  points INT(11) NOT NULL ,
  UNIQUE INDEX issn_UNIQUE ON copernicus(issn ASC)
);

CREATE SEQUENCE id_publication
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE  TABLE IF NOT EXISTS publication (
  id INT(11) DEFAULT NEXTVAL('id_publication') PRIMARY KEY,
  title VARCHAR(128) NOT NULL ,
  year YEAR NOT NULL ,
  author VARCHAR(128)  NOT NULL ,
  journal VARCHAR(128)  NOT NULL ,
  issn INT(11) NULL ,
  points INT(11) NULL ,
  bibtex_key VARCHAR(128) NOT NULL ,
  volume INT(11) NULL ,
  number INT(11) NULL ,
  pages INT(11) NULL ,
  month INT(11) NULL ,
  note TEXT NULL ,
  key VARCHAR(128) NOT NULL ,
  INDEX fk_publication_copernicus ON publication(issn ASC);
  CONSTRAINT fk_publication_copernicus FOREIGN KEY (issn) REFERENCES copernicus(issn),
  CHECK(month>0),
  CHECK(month<13)
);
	
CREATE SEQUENCE id_author
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE  TABLE IF NOT EXISTS author (
  id INT(11) DEFAULT NEXTVAL('id_author') NOT NULL PRIMARY KEY  ,
  name VARCHAR(128) NOT NULL ,
  surname VARCHAR(128) NOT NULL ,
  university VARCHAR(128) NOT NULL ,
  faculty VARCHAR(45) NOT NULL ,
  degree VARCHAR(45) NOT NULL ,
  publication_count INT NULL ,
 );

CREATE SEQUENCE id_user
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE  TABLE IF NOT EXISTS user (
  id INT(11) DEFAULT NEXTVAL('id_user') NOT NULL PRIMARY KEY ,
  username VARCHAR(128) NOT NULL ,
  password VARCHAR(128) NOT NULL ,
  email VARCHAR(128) NOT NULL ,
  user_type INT(11) NOT NULL ,
  salt TINYINT NOT NULL,
  author_id INT(11) NULL ,
  UNIQUE INDEX username_UNIQUE ON user(username),
  INDEX fk_user_author ON user(author_id ASC),
  CONSTRAINT fk_user_autor FOREIGN KEY (author_id) REFERENCES author(id)
);

CREATE SEQUENCE id_action
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE  TABLE IF NOT EXISTS action(
  id INT(11) DEFAULT NEXTVAL('id_action') NOT NULL PRIMARY KEY ,
  module VARCHAR(45) NULL ,
  controller VARCHAR(45) NULL ,
  action VARCHAR(45) NULL ,
  is_secure TINYINT(1)  NULL DEFAULT 0 ,
);

CREATE SEQUENCE id_log
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE  TABLE IF NOT EXISTS log (
  id INT(11) DEFAULT NEXTVAL('id_log') NOT NULL PRIMARY KEY ,
  log_date DATE NOT NULL ,
  hour TIME NOT NULL ,
  user_id INT(11) NOT NULL ,
  INDEX fk_log_user ON log(user_id ASC),
  CONSTRAINT fk_log_user FOREIGN KEY (user_id) REFERENCES user (id)
);


CREATE  TABLE IF NOT EXISTS publication_has_author (
  publication_id INT(11) NOT NULL PRIMARY KEY,
  author_id INT(11) NOT NULL PRIMARY KEY,
  INDEX fk_publication ON user(publication_id ASC),
  INDEX fk_author ON user(author_id ASC),
  CONSTRAINT fk_publication FOREIGN KEY (publication_id) REFERENCES publication(id)
  CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES author(id)
);

CREATE  TABLE IF NOT EXISTS action_has_user (
  action_id INT(11) NOT NULL PRIMARY KEY,
  user_id INT(11) NOT NULL PRIMARY KEY,
  INDEX fk_action ON user(action_id ASC),
  INDEX fk_user ON user(user_id ASC),
  CONSTRAINT fk_action FOREIGN KEY (action_id ) REFERENCES action(id)
  CONSTRAINT fk_user FOREIGN KEY (user_id ) REFERENCES user(id)
);
</code>
===== 5. Słowniki danych=====
^Nazwa tabeli	              ^Nazwa kolumny	  ^Typ danych	^Rozmiar	^Wymagana wartość             ^Primary ^ Ograniczenia ^
|Author	                      |Id	          |Integer	|11	        |Tak	                      |Tak     |0-9|
|Author	                      |Name	          |Varchar	|128	|Tak	                     |Nie	|a-zA-Z|
|Author	                      |Surname	          |Varchar	|128	|Tak	                     |Nie	|a-zA-Z|
|Author	                      |University	  |Varchar       |128	|Tak	                     |Nie	|a-zA-Z|
|Author	                      |Faculty	          |Varchar	|45	|Tak	                     |Nie	|a-zA-Z|
|Author	                      |Degree	          |Varchar	|45	|Tak	                     |Nie	|a-zA-Z|
|Author	                      |Publication_count  |Integer      |1	|Nie	                     |Nie	|0-9|
|User	                      |Id	          |Varchar	|128	|Tak 	                     |Tak	|a-zA-Z|
|User	                      |Username	          |Varchar	|128	|Tak 	                     |Nie	|a-zA-Z|
|User	                      |Password	          |Varchar	|128	|Tak	                     |Nie	|a-zA-Z|
|User	                      |Email	          |Varchar	|128	|Tak 	                     |Nie	|[[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}]|
|User	                      |User_type	          |Integer	|11	|Tak	                     |Nie |0-9|	
|User	                      |Author_id	          |Integer	|11	|Nie	                     |Nie |0-9|	
|Log	                      |Id	          |Integer	|11	|Tak	                     |Tak	|0-9|
|Log	                      |Log_date 	          |Date	        |1	|Tak	                     |Nie |YYYY-MM-DD|	
|Log	                      |Hour	         | TIME	        |1	|Tak	                     |Nie| HH:MM:SS|	
|Log	                      |User_id	         | Integer	|11	|Tak 	                    | Nie|0-9|	
|Publication_has_author	      |Publication_id	  |Integer	|11	|Tak	                    | Tak|0-9|	
|Publication_has_author	      |Author_id	          |Integer	|11	|Tak	                     |Tak|0-9|
|Publication	              |Id	          |Integer	|11	|Tak 	                    | Tak|0-9|	
|Publication	              |Title	          |Varchar	|128	|Tak	                    | Nie|a-zA-Z|	
|Publication	              |Year	          |Year	        |1	|Tak 	                    | Nie|0-9|	
|Publication	              |Author	          |Varchar	|128	|Tak	                    | Nie|a-zA-Z|		
|Publication	              |Journal	          |Varchar	|128	|Tak 	                    | Nie|a-zA-Z|	
|Publication	              |Issn	         | Integer	|11	|Nie 	                    | Nie|0-9|	
|Publication	              |Points	          |Integer	|11	|Nie	                    | Nie|0-9|	
|Publication	              |Bibtex_key	  |Varchar	|128	|Tak	                    | Nie|a-zA-Z|		
|Publication	              |Volume	          |Integer	|11	|Nie	                     |Nie|0-9|	
|Publication	              |Number	          |Integer	|11	|Nie	                     |Nie|0-9|	
|Publication	              |Pages	          |Integer	|11	|Nie	                     |Nie|0-9|	
|Publication	              |Month	          |Integer	|11	|Nie	                     |Nie|0-9|	
|Publication 	              |Note	          |Text	        |1 	|Nie	                     |Nie|0-9a-zA-Z|	
|Publication	              |Key	          |Varchar	|128	|Nie	                     |Nie|a-zA-Z|		
|Copernicus	              |Id	          |Integer	|11	|Tak	                     |Tak|0-9|	
|Copernicus	              |Title 	          |Varchar	|128	|Tak 	                     |Nie |a-zA-Z|	
|Copernicus	              |Issn	          |Integer	|11	|Tak	                     |Nie |0-9|	
|Copernicus	              |Points	          |Integer	|11	|Tak	                     |Nie |0-9|
|Action 	              |Id	          |Integer	|11	|Tak	                     |Tak|0-9|	
|Action                       |Module             |Varchar	|128	|Tak 	                     |Nie |a-zA-Z|
|Action                       |Controller         |Varchar	|128	|Tak 	                     |Nie |a-zA-Z|
|Action                       |Action             |Varchar	|128	|Tak 	                     |Nie |a-zA-Z|
|Action                       |is_secure          |Tinyint	|128	|Tak 	                     |Nie |0-9|	
===== 6. Analiza zależności funkcyjnych i normalizacja tabel=====
Tabele po denormalizacji do 3NF ( każdy atrybut wtórny zależy bezpośrednio od klucza głównego):

==== Tabela user ====

^ id ^ username ^ password ^ email ^ user_type ^ author_id ^

3NF:

^ user_id ^ username ^ password ^ email ^ user_type ^

^ user_id ^ password ^ salt ^

==== Tabela log ====

^ id ^ log_date ^ hour ^ user_id ^

3NF:

^ user_id ^ log_date ^ hour ^

==== Tabela author ====

^ id ^ name ^ surname ^ university ^ faculty ^ degree ^ publication_count ^

3NF:

^ id ^ name ^ surname ^ degree ^ university ^ publication_count ^

^university ^ faculty ^

==== Tabela copernicus ====

^ id ^ issn ^ title ^ points ^

3NF:

^ id ^ issn ^

^ issn ^ title ^ points ^

==== Tabela publication ====

^ id ^ title ^ points ^ bibtex_key ^ date_of_publication ^ surname ^ firstname ^ publishing ^

3NF:

^id^title^points^bibtex_key^author^

^bibtex_key^title^year^author^journal^volume^number^pages^month^note^key^

^id^author_id^author^


==== Tabela action ====

^id^module^controller^action^is_secure

3NF:

^id^module^controller^action^is_secure

Dekompozycja do BCNF - spełniona.
Dekompozycja do 4NF  - spełniona.
Dekompozycja do 5NF - spełniona.

Dekomponując tabele do poszczególnych form powinniśmy utworzyć w bazie nowe tabele, które spełniały by ich założenia. W naszej aplikacji uznaliśmy, że jednak wygodniejszym rozwiązaniem będzie pozostawienie tabel w takiej postaci, w jakiej są.

===== 7. Projektowanie operacji na danych =====

Publikacje w zależności od liczby punktów:

<code sql>
SELECT * FROM publication where points > próg
</code>
<code sql>
SELECT * FROM publication where points = próg
</code>
<code sql>
SELECT * FROM publication where points < próg
</code>

Lista autorów:
<code sql>
SELECT * FROM author
</code>

Konkretny autor:
<code sql>
SELECT * FROM author WHERE author_id = szukane_id
</code>

Lista publikacji w zależności od roku wydania:
<code sql>
SELECT * FROM publication WHERE year > szukany_rok
</code>
<code sql>
SELECT * FROM publication WHERE year <= szukany_rok
</code>

Lista publikacji w danym piśmie naukowym:
<code sql>
SELECT * FROM publication where issn = szukany_issn
</code>

Logowanie:
<code sql>
INSERT INTO log(id, log_date, hour, user_id) VALUES ( 231, '2012-12-03, 12, 4324 );
</code>

Edytuj publikacje:
<code sql>
UPDATE publication SET journal='my journal' where id = 45
DELETE * FROM publication WHERE author = 'Albert Einstein'
</code>

Lista będzie uaktualniona dokładnie, gdy zadecydujemy o kryteriach filtrowania w aplikacji.