===== Projekt logiczny =====

====-. Konstrukcja bazy danych==== 

**__Diagram ERD:__**

{{:pl:dydaktyka:ztb:2011:projekty:rozliczenia:start:erd5.png|}}

**__Kod SQL:__**
<code>
CREATE  TABLE IF NOT EXISTS `users` (
  `login` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `surname` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `birthdate` DATE NOT NULL ,
  `logdate` DATE NULL ,
  `regdate` DATE NULL ,
  PRIMARY KEY (`login`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `groups` (
  `groupID` INT NOT NULL AUTO_INCREMENT ,
  `groupname` VARCHAR(45) NOT NULL ,
  `groupowner` VARCHAR(45) NOT NULL ,
  `autooptymalization` TINYINT(1)  NOT NULL ,
  PRIMARY KEY (`groupID`) ,
  INDEX `fk_groups_users` (`groupowner` ASC) ,
  CONSTRAINT `fk_groups_users`
    FOREIGN KEY (`groupowner` )
    REFERENCES `users` (`login` ))
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `groupmembers` (
  `login` VARCHAR(45) NOT NULL ,
  `groupID` INT NOT NULL ,
  PRIMARY KEY (`login`, `groupID`) ,
  INDEX `fk_groupmembers_users1` (`login` ASC) ,
  INDEX `fk_groupmembers_groups1` (`groupID` ASC) ,
  CONSTRAINT `fk_groupmembers_users1`
    FOREIGN KEY (`login` )
    REFERENCES `users` (`login` ),
  CONSTRAINT `fk_groupmembers_groups1`
    FOREIGN KEY (`groupID` )
    REFERENCES `groups` (`groupID` ))
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `commitments` (
  `commitmentID` INT NOT NULL AUTO_INCREMENT ,
  `commitmentname` VARCHAR(100) NOT NULL ,
  `obligor` VARCHAR(45) NOT NULL ,
  `obligee` VARCHAR(45) NOT NULL ,
  `groupID` INT NOT NULL ,
  `commitmentowner` VARCHAR(45) NOT NULL ,
  `sum` FLOAT NOT NULL ,
  `comment` VARCHAR(200) NULL ,
  `commitmentdate` DATE NOT NULL ,
  `status` INT NOT NULL ,
  PRIMARY KEY (`commitmentID`) ,
  INDEX `fk_commitments_groups1` (`groupID` ASC) ,
  INDEX `fk_commitments_users1` (`obligor` ASC) ,
  INDEX `fk_commitments_users2` (`obligee` ASC) ,
  INDEX `fk_commitments_users` (`commitmentowner` ASC) ,
  CONSTRAINT `fk_commitments_groups1`
    FOREIGN KEY (`groupID` )
    REFERENCES `groups` (`groupID` ),
   CONSTRAINT `fk_commitments_users1`
    FOREIGN KEY (`obligor` )
    REFERENCES `users` (`login` ),
CONSTRAINT `fk_commitments_users2`
    FOREIGN KEY (`obligee` )
    REFERENCES `users` (`login`),
  CONSTRAINT `fk_commitments_users`
    FOREIGN KEY (`commitmentownerID` )
    REFERENCES `users` (`login` ))
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `groupjoin` (
  `groupjoinID` INT NOT NULL AUTO_INCREMENT ,
  `groupjoinowner` VARCHAR(45) NOT NULL ,
  `groupID` INT NOT NULL ,
  `login` varchar(45) NOT NULL ,
  `invitation` TINYINT(1)  NOT NULL ,
  PRIMARY KEY (`groupjoinID`) ,
  INDEX `fk_groupjoin_users1` (`login` ASC) ,
  INDEX `fk_groupjoin_groups1` (`groupID` ASC) ,
  CONSTRAINT `fk_groupjoin_users1`
    FOREIGN KEY (`login` )
    REFERENCES `users` (`login` ),
  CONSTRAINT `fk_groupjoin_groups1`
    FOREIGN KEY (`groupID` )
    REFERENCES `groups` (`groupID` ))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `sessions` (
	`sessionID` BIGINT NOT NULL,
	`login` VARCHAR(45) NOT NULL,
	`userIP` VARCHAR(20) NOT NULL,
	`rememberme` TINYINT(1) NOT NULL,
	PRIMARY KEY (`sessionID`),
	CONSTRAINT `fk_sessions_users`
		FOREIGN KEY(`login`)
		REFERENCES `users` (`login`))
ENGINE = InnoDB;
</code>

====-. Słownik danych====

  * USERS- tabela przechowująca dane o użytkownikach systemu,
      * login - klucz główny, NOT NULL, VARCHAR(45),
      * password - hasło użytkownika, NOT NULL, VARCHAR(45),
      * name - imię użytkownika, NOT NULL, VARCHAR(45),
      * surname - nazwisko użytkownika, NOT NULL, VARCHAR(45),
      * email - NOT NULL, VARCHAR(45),
      * birthdate - data urodzenia użytkownika, NOT NULL, DATE,
      * logdate - data ostatniego logowania, DATE,
      * regdate - data rejestracji użytkownika, DATE.

  * GROUPS - tabela przechowująca dane o grupach rozliczeniowych,
      * groupID - klucz główny, NOT NULL, INT, AUTO_INCREMENT,
      * groupname - nazwa grupy, NOT NULL, VARCHAR(45),
      * groupowner - login użytkownika będącego właścicielem grupy, referencja do tabeli USERS.login, NOT NULL, VARCHAR(45),
      * autooptymalization - wartość logiczna określająca sposób optymalizacji roliczeń w grupie, NOT NULL, TINYINT(1).

  * GROUPMEMBERS - tabela przyporządkowująca użytkowników do grup,
      * login - składowa klucza głównego, referencja do tabeli USERS.login, NOT NULL, VARCHAR(45),
      * groupID - składowa klucza głównego, referencja do tabeli GROUPS.groupID, NOT NULL, INT.

  * COMMITMENTS - tabla przechowująca dane o zawartych zobowiązaniach,
      * commitmentID - klucz główny, NOT NULL, INT, AUTO_INCREMENT,
      * commitmentname - nazwa zobowiązania, NOT NULL, VARCHAR(100),
      * obligor - dłużnik w zobowiązaniu, referencja do tabeli USERS.login, NOT NULL, VARCHAR(45),
      * obligee - wierzyciel w zobowiązaniu, referncja do tabeli USERS.login, NOT NULL, VARCHAR(45),
      * groupID - ID grupy rolizczeniowej, w której zawarte jest zobowiązanie, referencja do tabeli GROUPS.groupID, NOT NULL, INT,
      * commitmentowner - właściciel zobowiązania, referencja do tabeli USERS.login, NOT NULL, VARCHAR(45),
      * sum - kwota zobowiązania, NOT NULL, FLOAT,
      * comment - komentarz dotyczący zobowiązania, VARCHAR(200),
      * commitmentdate - data zawarcia zobowiązania, NOT NULL, DATE,
      * status - status zobowiązania, NOT NULL, INT.

  * GROUPJOIN - tabela przechowująca dane o dołączaniu użytkowników do grup,
      * groupjoinID - klucz główny, NOT NULL, INT, AUTO_INCREMENT,
      * groupjoinowner - właściciel zaproszenia/zapytania dołączenia do grupy, referencja do tabeli USERS.login, NOT NULL, VARCHAR(45),
      * groupID - ID grupy rozliczeniowej, w której zobowiązanie ma być zawarte, referencja do tabeli GROUPS.groupID, NOT NULL, INT,
      * login - login użytkownika, do którego kierowane jest zapytanie / zaproszenie, NOT NULL, INT,
      * invitation - wartość logiczna wskazująca na formę informacji (zaproszenie czy zapytanie), NOT NULL, TINYINT(1).

  * SESSIONS - tabela przechowująca dane o aktualnej sesji,
      * sessionID - klucz główny, NOT NULL, BIGINT,
      * login - login użytkownika aktualnie zalogowanego do systemu, NOT NULL, VARCHAR(45),
      * userIP - adres IP użytkownika w systemie, NOT NULL, VARCHAR(20),
      * rememberme - wartość logiczna określająca chęć zapamiętania logowania użytkownika przez przeglądarkę, NOT NULL, TINYINT(1).
 
====-. Analiza zależności funkcyjnych i normalizacja tabel====

**Pierwsza postać normalna - 1NF**

Wszystkie atrybuty w tabelach bazy danych przyjmują wartość atomiczne. Rozpatrywana baza danych jest zatem w pierwszej postaci normalnej.

**Druga postać normalna - 2NF**

Wszystkie tabele w bazie danych, poza tabelą 'groupmembers', posiadają proste klucze główne. Tabela 'groupmembers' jest tabelą asocjacyjną, nie posiadającą niekluczowych atrybutów. Baza jest zatem w drugiej postaci normalnej.

**Trzecia postać normalna - 3NF**

W zaproponowanym modelu bazy danych wszystkie atrybuty w tabelach zależą w sposób bezpośredni od klucza głównego - nie ma relacji przechodnich. Baza jest zatem w trzeciej postaci normalnej.

====-. Projektowanie operacji na danych====

Dane zaprezentowane w poniższych kwerendach są przykładowe i służą jedynie ustaleniu poprawności kodu SQL. Poza przedstawionymi zapytaniami do bazy danych, w przypadku realizacji większości wymienionych operacji i funkcji, zastosowano również inne mechanizmy (np. przy wykorzystaniu pakietu GWT oraz JDBC).

** 1. Rejestracja użytkownika do systemu: **
<code> INSERT INTO users VALUES ("login","hasło","Imię","Nazwisko","a@a.pl","1966-02-02","2011-06-02","2011-06-02");</code>

** 2. Logowanie do systemu: **
<code> INSERT INTO sessions VALUES (1234, "login" , "127.0.0.1", TRUE);
 UPDATE users SET logdate = "2011-06-03" WHERE login = "login");</code>

** 3. Wylogowanie z systemu: **
<code> DELETE FROM sessions WHERE sessionID = "login"; </code>

** 4. Edycja danych użytkownika (profilu użytkownika): **
<code> UPDATE users SET password = "nowehasło" , name = "Noweimię" , surname = "Nowenazwisko" , email = "nowymail@nowymail.pl" , city = "Nowemiasto", birthdate = "1966-02-07" WHERE login = "login"; </code>

** 5. Pobieranie danych aktualnie zalogowanego użytkownika: **
<code> SELECT login , password, name, surname, email, city, birthdate, logdate, regdate FROM users NATURAL JOIN sessions WHERE sessions.sessionID = 1234; </code>

** 6. Tworzenie nowej grupy rozliczeniowej: **
<code> INSERT INTO groups(groupname, groupowner, autooptymalization)  VALUES("nazwagrupy", "login", FALSE);
 INSERT INTO groupmembers VALUES("login",12);</code>

** 7. Usuwanie grupy rozliczeniowej: **
<code> DELETE FROM groupmembers WHERE groupID = 12;
 DELETE FROM groups WHERE groupID = 12;</code>

** 8. Pobieranie informacji o grupach roliczeniowych: **
<code> SELECT groupID, groupname, groupowner, autooptymalization FROM groups  WHERE groupowner = "login";
 SELECT name, surname, login FROM groupmembers NATURAL JOIN users WHERE groupmembers.groupID = 12;</code>

** 9. Tworzenie nowego zaproszenia / prośby o dołączenie do grupy: **
<code> INSERT INTO groupjoin(groupjoinowner, groupID, login, invitation)  VALUES("login", 12, "loginkogos", TRUE);</code>

** 10. Anulowanie zaproszenia / prośby o dołączenie do grupy: **
<code> DELETE FROM groupjoin WHERE groupjoinID = 123;</code>

** 11. Akceptacja zaproszenia / prośby o dołączenie do grupy: **
<code> INSERT INTO groupmembers VALUES("login",12);
 DELETE FROM groupjoin WHERE groupjoinID = 123;</code>

** 12. Pobieranie danych o otrzymanych zaproszeniach (analogicznie wysłanych zaproszeniach, otrzymanych prośbach, wysłanych prośbach): **
<code> SELECT groupname, name, surname, groupjoinID FROM groupjoin NATURAL JOIN groups NATURAL JOIN users WHERE groupjoin.invitation = TRUE AND groupjoin.groupjoinowner <> "login";</code>

** 13. Tworzenie nowego zobowiązania: **
<code> INSERT INTO commitments (commitmentname, obligor, obligee, groupID, commitmentowner, sum, comment, commitmentdate, status )  VALUES("zobowiazanie", "login_dłużnika", "login", 12, , "login", 125, "Komentarz do zobowiazania", 1);</code>

** 14. Usuwanie zobowiązania: **
<code> DELETE FROM commitments WHERE commitmentID = 5;</code>

** 15. Zmiana statusu zobowiązania: **
<code> UPDATE commitments SET status = 2 WHERE commitmentID = 5;</code>

** 16. Pobieranie danych o otrzymanych zobowiązaniach: **
<code> SELECT commitmentID, commitmentname, obligor, obligee, groupID, commitmentowner, sum, comment, commitmentdate, status FROM commitments WHERE commitments.status = 2 AND commitmentowner <> "login" AND (obligor = "login" OR obligee = "login");</code>