PROJEKT LOGICZNY

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.

• T1 - USER
• T2 - LOG
• T3 - AUTHOR
• T4 - PUBLICATION
• T5 - COPERNICUS
• T6 - ACTION

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)
);

Tabele po denormalizacji do 3NF ( każdy atrybut wtórny zależy bezpośrednio od klucza głównego):

3NF:

3NF:

3NF:

3NF:

3NF:

3NF:

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ą.

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

SELECT * FROM publication WHERE points > próg

SELECT * FROM publication WHERE points = próg

SELECT * FROM publication WHERE points < próg

Lista autorów:

SELECT * FROM author

Konkretny autor:

SELECT * FROM author WHERE author_id = szukane_id

Lista publikacji w zależności od roku wydania:

SELECT * FROM publication WHERE YEAR > szukany_rok

SELECT * FROM publication WHERE YEAR <= szukany_rok

Lista publikacji w danym piśmie naukowym:

SELECT * FROM publication WHERE issn = szukany_issn

Logowanie:

INSERT INTO log(id, log_date, HOUR, user_id) VALUES ( 231, '2012-12-03, 12, 4324 );

Edytuj publikacje:

UPDATE publication SET journal='my journal' WHERE id = 45
DELETE * FROM publication WHERE author = 'Albert Einstein'

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