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);
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_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_id, INT SERIAL PRIMARY KEY - klucz główny, unikalne ID kategorii
name, varchar(255) NOT NULL - nazwa kategorii
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
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
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_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)
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.
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.
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.
SELECT `User`.* FROM `User` WHERE (login='Wacek') AND (password='#PasswordHash#');
insert into User(login, email, password, active) values('Wacek', 'wacek@amorki.pl', '#password_hash#', false);
update User set about = 'moja historia', notify_subscr = true where User.id = 11;