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
3. Tablica krzyżowa
T1 - USER
T2 - LOG
T3 - AUTHOR
T4 - PUBLICATION
T5 - COPERNICUS
T6 - ACTION
4. Projekt bazy w języku 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)
);
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 |
Tabela log
Tabela author
id | name | surname | university | faculty | degree | publication_count |
3NF:
id | name | surname | degree | university | publication_count |
Tabela copernicus
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 |
Tabela action
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ą.
7. Projektowanie operacji na danych
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.