=====-. Projekt logiczny ===== ====-. Projekt bazy danych (SQL) ====



DROP TABLE IF EXISTS Users CASCADE;
DROP TABLE IF EXISTS Categories CASCADE;
DROP TABLE IF EXISTS Albums CASCADE;
DROP TABLE IF EXISTS Photos CASCADE;
DROP TABLE IF EXISTS TrustedUsers CASCADE;
DROP TABLE IF EXISTS Comments CASCADE;
DROP TABLE IF EXISTS Subscriptions CASCADE;
DROP TABLE IF EXISTS Activations CASCADE;
DROP TABLE IF EXISTS Votes CASCADE;

CREATE TABLE Users
(
  user_id serial primary key,
  login character varying(255) NOT NULL,
  password character varying(255) NOT NULL,
  email character varying(255) NOT NULL,
  activation_code char(32) DEFAULT NULL,
  date_of_birth date DEFAULT NULL,
  about text DEFAULT NULL,
  notify_comment boolean NOT NULL,
  notify_photo boolean NOT NULL,
  notify_subscr boolean NOT NULL
);


CREATE TABLE Categories
(
	category_id serial primary key,
	name varchar(255) NOT NULL
);


CREATE TABLE Albums
(
	album_id serial primary key,
	user_id integer NOT NULL REFERENCES Users, 
	category_id integer NOT NULL REFERENCES Categories,
	name varchar(255) NOT NULL,
	description text,
	rating smallint,
	views integer default 0,
	next_notification timestamp,
	public boolean NOT NULL,
	password varchar(255),
	comments_allow boolean NOT NULL,
	comments_auth boolean NOT NULL,
	notification_period integer
);

CREATE INDEX album_user_idx on Albums (user_id);
CREATE INDEX album_cat_idx on Albums (category_id);
CREATE INDEX album_rat_idx on Albums (rating);


CREATE TABLE Photos
(
	photo_id serial primary key,
	album_id integer NOT NULL REFERENCES Albums,
	date_taken timestamp NOT NULL,
	description text,
	file_path text NOT NULL,
	--location geography(point,4326)
	loc_latitude numeric(10,7) CHECK (loc_latitude >= -90 AND loc_latitude <= 90),
	loc_longitude numeric(10,7) CHECK (loc_longitude >= -180 AND loc_longitude <= 180)
);

CREATE INDEX photo_album_idx on Photos (album_id);
CREATE INDEX photo_date_idx on Photos(date_taken);


CREATE TABLE TrustedUsers
(
	album_id integer REFERENCES Albums,
	user_id integer REFERENCES Users,
	primary key(user_id, album_id)
);


CREATE TABLE Comments
(
	comment_id serial primary key,
	album_id integer NOT NULL REFERENCES Albums,
	user_id integer NOT NULL REFERENCES Users,
	date_posted timestamp NOT NULL,
	"body" varchar(1000) NOT NULL, 	
	accepted boolean NOT NULL,
);

CREATE INDEX comment_user_idx on Comments (user_id);
CREATE INDEX comment_album_idx on Comments (album_id);



CREATE TABLE Subscriptions
(
	user_id integer REFERENCES Users,
	album_id integer REFERENCES Albums,
	primary key(user_id, album_id)
);	



CREATE TABLE Votes
(
	user_id integer REFERENCES Users,
	album_id integer REFERENCES Albums,
	primary key(user_id, album_id),
	up boolean NOT NULL
);	
CREATE INDEX vote_album_idx on Votes (album_id);

====-. Słowniki danych ==== * User - tabela przechowująca dane na temat użytkowników aplikacji user_id, INT SERIAL PRIMARY KEY - klucz główny, unikalne ID użytkownika\\ login, varchar(255) NOT NULL - login użytkownika w serwisie\\ password, varchar(255) NOT NULL - hasło zahashowane\\ email, varchar(255) NOT NULL - adres email użytkownika\\ active, boolean NOT NULL - czy konto jest aktywne\\ date_of_birth, date - data urodzenia\\ about, TEXT - informacje dodatkowe u użytkowniku\\ notify_comment, boolean - czy użytkownik ma być powiadamiany mailowo o komentarzach\\ notify_photo, boolean - czy wysyłane jest przypomnienie o zrobieniu nowego zdjęcia\\ notify_subscr, boolean - czy użytkownik ma być powiadamiany mailowo o nowych zdjęciach w obserwowanych albumach\\ notification_period, smallint - ilość dni, co jaką wysyłane jest w/w przypomnienie o zrobieniu zdjęcia\\ * Album - tabela przechowująca dane na temat albumów ze zdjęciami album_id, INT SERIAL PRIMARY KEY - klucz główny, unikalne ID albumu\\ user_id, INT NOT NULL - klucz obcy do tabeli User, oznacza właściciela albumu\\ category_id, INT NOT NULL - klucz obcy do tabeli Category, oznacza kategorię, do której należy album\\ name, varchar(255) NOT NULL - nazwa albumu\\ description, text - opis albumu\\ rating, smallint - ocena albumu (wyrażona w skali 1-10)\\ views, integer - liczba odsłon albumu\\ next_notification, timestamp - kiedy ma być wysłane najbliższe powiadomienie do użytkownika o konieczności zrobienia zdjęcia do albumu\\ public, boolean NOT NULL - czy album jest publiczny, czy widoczny tylko dla użytkownika\\ password, varchar(255) NOT NULL - hasło zahashowane\\ comments_allow, boolean NOT NULL - czy możliwe jest dodawanie komentarzy do albumu\\ comments_auth, boolean NOT NULL - czy komentarze wymagają moderacji autora\\ * Category- tabela przechowująca dane na temat kategorii dla albumów category_id, INT SERIAL PRIMARY KEY - klucz główny, unikalne ID kategorii\\ name, varchar(255) NOT NULL - nazwa kategorii\\ * Photo - tabela przechowująca dane na temat fotografii photo_id, INT SERIAL PRIMARY KEY - klucz główny, unikalne ID fotografii\\ album_id, INT NOT NULL - klucz obcy do tabeli Album, oznacza album do którego należy zdjęcie\\ date_taken, timestamp NOT NULL - data zrobienia zdjęcia\\ description, text - opis zdjęcia\\ file_path, text NOT NULL - scieżka do pliku ze zdjęciem\\ loc_latitude, numeric(10,7) - szerokość geograficzna, na której zostało zrobione zdjęcie\\ loc_longitude, numeric(10,7) - długość geograficzna, na której zostało zrobione zdjęcie * TrustedUser - tabela łącznikowa przechowująca zaufanych użytkowników powiązanych z albumami album_id, INT PRIMARY KEY - klucz główny, klucz obcy do tabeli Album, określa album do którego dostęp ma użytkownik\\ user_id, INT PRIMARY KEY - klucz główny, klucz obcy do tabeli User, określa użytkownika, który ma dostęp do albumu\\ * Subscription - tabela łącznikowa przechowująca informacje o subskrypcji albumów przez użytkowników album_id, INT PRIMARY KEY - klucz główny, klucz obcy do tabeli Album, określa subskrybowany album\\ user_id, INT PRIMARY KEY - klucz główny, klucz obcy do tabeli User, określa użytkownika, który subskrybuje album\\ * Comment - tabela przechowująca dane na temat komentarzy comment_id, INT SERIAL PRIMARY KEY - klucz główny, unikalne ID komentarza\\ album_id, INT NOT NULL - klucz obcy do tabeli Album, oznacza album do którego należy komentarz\\ user_id, INT NOT NULL - klucz obcy do tabeli User, oznacza autora komentarza\\ date_posted, timestamp NOT NULL - data opublikowania komentarza\\ body, varchar(1000) NOT NULL - treść komentarza\\ accepted, boolean NOT NULL - czy komentarz został zaakceptowany i może być wyświetlony * Votes - tabela łącznikowa przechowująca informacje o głosach oddanych na albumy \\ album_id, INT PRIMARY KEY - klucz główny, klucz obcy do tabeli Album, określa na który album został oddany głos\\ user_id, INT PRIMARY KEY - klucz główny, klucz obcy do tabeli User, określa użytkownika, który głosował na album\\ up, boolean NOT NULL - określa czy głos był pozytywny (true) czy negatywny (false) ====-. Analiza zależności funkcyjnych i normalizacja tabel ==== * **1NF - pierwsza postać normalna** //__Warunki__//\\ Tabele w bazie spełniają warunek pierwszej postaci normalnej, jeśli każda z tych tabel spełnia następujące warunki:\\ -opisuje jeden obiekt,\\ -wartości atrybutów są elementarne (atomowe, niepodzielne) - każda kolumna jest wartością skalarną (atomową), a nie macierzą lub listą czy też czymkolwiek, co posiada własną strukturę,\\ -nie zawiera kolekcji (powtarzających się grup informacji),\\ -posiada klucz główny,\\ -kolejność wierszy może być dowolna (znaczenie danych nie zależy od kolejności wierszy).\\ //__Analiza bazy danych__//\\ Baza jest w pierwszej postaci formalnej, gdyż spełnia w/w warunki. * **2NF - druga postać normalna** //__Warunki__//\\ Baza jest w drugiej postaci normalnej, wtedy gdy spełniona jest 1NF oraz gdy wartości nie-kluczowe są zależne od klucza głównego.\\ //__Analiza bazy danych__//\\ Następujące tabele posiadają klucz prosty, więc ich atrybuty zależą od całego klucza głównego:\\ User\\ Album\\ Photo\\ Comment\\ Category\\ Następujące tabele mają klucze złożone:\\ Subscription\\ TrustedUser\\ Tabele te nie posiadają atrybutów niekluczowych. Baza jest w 2NF. * **3NF - trzecia postać normalna** //__Warunki__//\\ Baza jest w trzeciej postaci normalnej, gdy spełniona jest 2NF oraz gdy wszystkie wartości niekluczowe zależą bezpośrednio od wartości klucza głównego. //__Analiza bazy danych__//\\ Wartości niekluczowe we wszystkich tabelach zależą bezpośrednio od wartości klucza głównego.\\ Zależności przechodnie (transitive dependencies), które łamią tę zasadę, zostały wyeliminowane poprzez rozbicie danych na osobne tabele. \\ Przykład: \\ Gdyby w tabeli 'Album' było pole z nazwą kolekcji, tabela nie spełniałaby 3NF. Dzięki stworzeniu osobnej tabeli z kolekcjami i umieszczeniu do niej odwołania w tabeli 'Album', tabela spełnia 3NF. ====-. Projektowanie operacji na danych ==== * Logowanie użytkownika


SELECT `User`.* FROM `User` WHERE (login='Wacek') AND (password='#PasswordHash#');

* Rejestracja użytkownika


insert into User(login, email, password, active) values('Wacek', 'wacek@amorki.pl', '#password_hash#', false);

* Aktualizacja profilu użytkownika


update User
set about = 'moja historia',
notify_subscr = true
where User.id = 11;