Tomasz Krużel, Krzysztof Lelito, Janusz Lisiecki

Projekt bazy danych wykorzystanej w stronie internetowej http://www.gdziepopiwo.pl.

Celem projektu jest stworzenie strony internetowej na której będzie można znaleźć najtańsze piwo w okolicy. Nasza strona będzie oparta o rozbudowany system CMS. Użytkownik mógłby w łatwy sposób wyszukać gdzie w jego okolicy można dostać konkretne piwo i w jakiej cenie. Baza cen, asortymentu i sklepów byłaby modyfikowana przez samych zarejestrowanych użytkowników portalu. Integracja z API Google Maps ułatwi lokalizację sklepów.

Jeśli chodzi o polski rynek, to w momencie projektowania, nie było rozwiązania takiego jak nasze. Istnieją jedynie portale dotyczące innych produktów, np. strona dotyczący cen paliwa http://www.dobaku.pl/ adresowana do kierowców gdzie możemy znaleźć najtańszą stację z paliwem w Krakowie. Jedyne rozwiązanie związane z piwem jakie udało nam się znaleźć to http://www.twenga.pl/ które jest portalem o ogólnej tematyce wyszukującym ceny jedynie spośród sklepów internetowych. Obie z powyższych stron zarabiają na reklamach wystawianych na stronie. Kolejny powiązany z tematem portal to http://napiwo.org/ oferująca mapę Krakowa wraz z naniesionymi klubami i cenami piwa, witryna ta ma bardzo wąski zakres funkcjonalności i jest stworzona w celach całkowicie niekomercyjnych.

Główne funkcjonalności (Must have):

• rejestracja użytkowników, aktywacja, ostrzeżenia za łamanie regulaminu, banowanie, przypomnienia hasła.
• wyszukiwanie piwa według lokalizacji, przedziału cenowego, nazwy, opakowania.
• statystyki odwiedzin strony.
• integracja z API Google Maps, wyświetlenie mapy z zaznaczonymi sklepami i ich cenami.

Dodatkowe funkcjonalności (Could have):

• integracja z portalami społecznościowymi (wykop, facebook, blip, nasza klasa).
• informacje o promocjach.

1. Scenariusze dla gościa:
   1. Rejestracja
   2. Wyszukiwanie piwa
   3. Przeglądanie informacji o sklepie
2. Scenariusze dla użytkownika:
   1. Dostęp do serwisu:
      • Logowanie
      • Wylogowanie
      • Przypomnienie hasła
   2. Zarządzanie kontem:
      • Zmiana danych użytkownika
   3. Edycja bazy sklepów i asortymentu:
      • Dodawanie sklepów
      • Dodawanie piw
      • Edycja piw
      • Usuwanie piw
   4. Wyszukiwanie piwa
      • Wyszukiwanie z użyciem podstawowej wyszukiwarki
      • Wyszukiwanie z użyciem zaawansowanej wyszukiwarki
   5. Przeglądanie informacji o sklepie
3. Scenariusze dla administratora:
   1. Zarządzanie piwami
      • Dodawanie piwa
      • Edycja piwa
      • Usuwanie piwa
   2. Zarządzanie piwami w sklepach
      • Dodawanie piwa do sklepu
      • Edycja korelacji piwa ze sklepem
      • Usuwanie piwa ze sklepu
   3. Zarządzanie miastami
      • Dodawanie miasta
      • Edycja miasta
      • Usuwanie miasta
   4. Zarządzanie korporacjami
      • Dodawanie korporacji
      • Edycja korporacji
      • Usuwanie korporacji
   5. Zarządzanie promocjami
      • Dodawanie promocji
      • Edycja promocji
      • Usuwanie promocji
   6. Zarządzanie sklepami
      • Dodawanie sklepu
      • Edycja sklepu
      • Usuwanie sklepu
   7. Zarządzanie użytkownikami:
      • Zmiana danych użytkownika
      • Banowanie użytkownika
   8. Przeglądanie ostrzeżeń
      • Usuwanie ostrzeżeń
   9. Przeglądanie statystyk

Diagram przypadków użycia:

Część administracyjna:

• Logowanie/wylogowywanie z panelu administracyjnego
• Dodawanie,modyfikacja,usuwanie piw
• Dodawanie,modyfikacja,usuwanie piw w sklepach
• Dodawanie,modyfikacja,usuwanie miast
• Dodawanie,modyfikacja,usuwanie korporacji handlowych
• Dodawanie,modyfikacja,usuwanie promocji
• Dodawanie,modyfikacja,usuwanie sklepów
• Dodawanie,modyfikacja,usuwanie, banowanie, aktywowanie użytkowników
• Usuwanie ostrzeżeń użytkowników
• Statystki odwiedzi
  • ilość wszystkich wejść,
  • wejsc unikalnych,
  • typu przeglądarki internautów

Część publiczna:

• Logowanie/wylogowywanie z serwisu
• Rejestracja użytkownika, modyfikacja danych, przypominanie hasła
• Wyszukiwanie piwa wg ceny, marki, miasta, opakowania
• Zaawansowane wyszukiwanie piwa wg lokalizacji geograficznej
• Dodawanie sklepów, uaktualnianie asortymentu, zapisywanie ulubionych sklepów
• Dodawanie promocji, dodawanie piwa do sklepu, edycja cen, raportowanie innych użytkowników
• Integracja z portalami spolecznosciowymi

FHD - Functional Hierarchy Diagram

Diagram kontekstowy

Diagramy systemowe

BEERS

• id int PK
• type enum('can','bottle')
• name text
• volume enum('0.33','0.5','0.65','1.0','1.5')

BEER_STORE

• beer_id int FK
• store_id int FK
• user_id int FK
• price double(4,2)
• last_update int

BOOKMARKS

• user_id int FK
• store_id int FK

CITIES

• id int
• name varchar(64)

CORPORATIONS

• id int
• name text

PROMOTIONS

• id int
• beer_id int FK
• corporation_id int FK
• description text
• date_start int
• date_finish int

STATISTICS

• id int
• browser varchar(128)
• ip varchar(15)
• date int
• user_id int FK

STORES

• id int
• corporation_id int FK
• name text
• city_id int FK
• address text
• description text
• latitude float(6,4)
• longitude float(6,4)
• open text
• open24h tinyint
• location point
• user_id int FK

USERS

• id int
• login text
• md5sum text
• email text
• privilage int
• last_change int
• last_delete int

WARNINGS

• id int
• user_id int FK
• warning text

Specyfikacja słownika danych. Specyfikacja dziedzin i ograniczeń. Robimy to w ogóle ?

CREATE SCHEMA IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8 COLLATE utf8;
USE mydb;
 
CREATE  TABLE IF NOT EXISTS mydb.cities (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  name VARCHAR(64) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
 
 
CREATE  TABLE IF NOT EXISTS mydb.users (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  login TEXT NOT NULL ,
  md5sum TEXT NOT NULL ,
  email TEXT NOT NULL ,
  privilage INT(11) NOT NULL ,
  last_change INT(11) NOT NULL ,
  last_delete INT(11) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
 
 
CREATE  TABLE IF NOT EXISTS mydb.corporations (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  name TEXT NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
 
 
CREATE  TABLE IF NOT EXISTS mydb.stores (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  corporation_id INT(11) NOT NULL ,
  name TEXT NOT NULL ,
  city_id INT(11) NOT NULL ,
  address TEXT NOT NULL ,
  description TEXT NOT NULL ,
  latitude FLOAT NOT NULL ,
  longitude FLOAT NOT NULL ,
  OPEN TEXT NOT NULL ,
  open24h TINYINT(4) NOT NULL DEFAULT '0' ,
  location POINT NOT NULL ,
  user_id INT(11) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_city_id (city_id ASC) ,
  INDEX fk_user_id (user_id ASC) ,
  INDEX fk_corporation_id (corporation_id ASC) ,
  CONSTRAINT fk_city_id
    FOREIGN KEY (city_id )
    REFERENCES mydb.cities (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_corporation_id
    FOREIGN KEY (corporation_id )
    REFERENCES mydb.corporations (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
 
 
CREATE  TABLE IF NOT EXISTS mydb.beer_store (
  beer_id INT(11) NOT NULL ,
  store_id INT(11) NOT NULL ,
  user_id INT(11) NOT NULL ,
  price DOUBLE NOT NULL ,
  last_update INT(11) NOT NULL ,
  INDEX fk_beer_id (beer_id ASC) ,
  INDEX fk_store_id (store_id ASC) ,
  INDEX fk_user_id (user_id ASC) ,
  CONSTRAINT fk_beer_id
    FOREIGN KEY (beer_id )
    REFERENCES mydb.beers (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_store_id
    FOREIGN KEY (store_id )
    REFERENCES mydb.stores (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
 
 
CREATE  TABLE IF NOT EXISTS mydb.bookmarks (
  user_id INT(11) NOT NULL ,
  store_id INT(11) NOT NULL ,
  INDEX fk_user_id (user_id ASC) ,
  INDEX fk_store_id (store_id ASC) ,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_store_id
    FOREIGN KEY (store_id )
    REFERENCES mydb.stores (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
 
 
CREATE  TABLE IF NOT EXISTS mydb.promotions (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  beer_id INT(11) NOT NULL ,
  corporation_id INT(11) NOT NULL ,
  description TEXT NOT NULL ,
  date_start INT(11) NOT NULL ,
  date_finish INT(11) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_corporation_id (corporation_id ASC) ,
  INDEX fk_beer_id (beer_id ASC) ,
  CONSTRAINT fk_corporation_id
    FOREIGN KEY (corporation_id )
    REFERENCES mydb.corporations (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_beer_id
    FOREIGN KEY (beer_id )
    REFERENCES mydb.beers (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
 
 
CREATE  TABLE IF NOT EXISTS mydb.statistics (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  browser VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  ip VARCHAR(15) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  DATE INT(11) NOT NULL ,
  user_id INT(11) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_user_id (user_id ASC) ,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
 
 
CREATE  TABLE IF NOT EXISTS mydb.warnings (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  user_id INT(11) NOT NULL ,
  warning TEXT NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_user_id (user_id ASC) ,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id )
    REFERENCES mydb.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;

Wydaje nam się, że baza danych spełnia założenia 3NF. Wynika to z faktu, że przechowywane w niej dane są istotnie atomiczne - z punktu widzenia implementacji projektu. W każdej tabeli atrybuty są w pełni bezpośrednio zależne od klucza głównego. Generalnie baza była projektowana tak by przy możliwie maksymalnej funkcjonalności tabel, zapewnić trzecią postać normalną.

SELECT b.*, bs.price AS price, concat(s.name,crp.name) AS store, s.id AS store_id, c.name AS city, s.latitude, s.longitude
FROM beer AS b
LEFT JOIN beer_store AS bs ON b.id = bs.beer_id
JOIN stores AS s ON bs.store_id = s.id
LEFT JOIN corporation AS crp ON s.corporation_id = crp.id
LEFT JOIN city AS c ON s.city_id = c.id
 
SELECT s.*, c.name AS corporation, t.name AS city, u.login
FROM stores AS s
LEFT JOIN city AS t ON s.city_id = t.id
LEFT JOIN corporation AS c ON s.corporation_id = c.id
LEFT JOIN USER AS u ON s.user_id = u.id
WHERE s.id = $id
 
$sql_part = "GeomFromText('Polygon((".($x-$distance)." ".($y-$distance).",".($x-$distance)." ".($y+$distance).",".($x+$distance)." ".($y+$distance).",".($x+$distance)." ".($y-$distance).",".($x-$distance)." ".($y-$distance)."))')";
 
 
SELECT * FROM stores
 
WHERE Contains('.$sql_part.' , location )

• Rejestracja nowego użytkownika

• Dodawanie nowego sklepu przez użytkownika

• Dodawanie promocji do sklepu przez użytkownika

• Dodawanie piwa przez użytkownika

• Edytowanie ceny piwa przez użytkownika

• Wyszukiwanie podstawowe

• Wyszukiwanie zaawansowane

• Panel administracyjny - lista wszystkich piw

• Panel administracyjny - dodawanie piwa

Podział prac na 3 osoby dał możliwość szybkiej realizacji pracy. Każda osoba była odpowiedzialna za swoją część i sumiennie wywiązała się z zadania. Nie wiem co tu można lepszego napisać. Przecież nikt nie liczył mandaysów.

• http://www.spatial.cs.umn.edu/Book/labs/vania/spatialQueries.html
• http://postgis.refractions.net
• http://wb.mysql.com