AIwiki

Diagram ERD:

Kod SQL:

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;

• 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).

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.

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:

 INSERT INTO users VALUES ("login","hasło","Imię","Nazwisko","a@a.pl","1966-02-02","2011-06-02","2011-06-02");

2. Logowanie do systemu:

 INSERT INTO sessions VALUES (1234, "login" , "127.0.0.1", TRUE);
 UPDATE users SET logdate = "2011-06-03" WHERE login = "login");

3. Wylogowanie z systemu:

 DELETE FROM sessions WHERE sessionID = "login"; 

4. Edycja danych użytkownika (profilu użytkownika):

 UPDATE users SET password = "nowehasło" , name = "Noweimię" , surname = "Nowenazwisko" , email = "nowymail@nowymail.pl" , city = "Nowemiasto", birthdate = "1966-02-07" WHERE login = "login"; 

5. Pobieranie danych aktualnie zalogowanego użytkownika:

 SELECT login , password, name, surname, email, city, birthdate, logdate, regdate FROM users NATURAL JOIN sessions WHERE sessions.sessionID = 1234; 

6. Tworzenie nowej grupy rozliczeniowej:

 INSERT INTO groups(groupname, groupowner, autooptymalization)  VALUES("nazwagrupy", "login", FALSE);
 INSERT INTO groupmembers VALUES("login",12);

7. Usuwanie grupy rozliczeniowej:

 DELETE FROM groupmembers WHERE groupID = 12;
 DELETE FROM groups WHERE groupID = 12;

8. Pobieranie informacji o grupach roliczeniowych:

 SELECT groupID, groupname, groupowner, autooptymalization FROM groups  WHERE groupowner = "login";
 SELECT name, surname, login FROM groupmembers NATURAL JOIN users WHERE groupmembers.groupID = 12;

9. Tworzenie nowego zaproszenia / prośby o dołączenie do grupy:

 INSERT INTO groupjoin(groupjoinowner, groupID, login, invitation)  VALUES("login", 12, "loginkogos", TRUE);

10. Anulowanie zaproszenia / prośby o dołączenie do grupy:

 DELETE FROM groupjoin WHERE groupjoinID = 123;

11. Akceptacja zaproszenia / prośby o dołączenie do grupy:

 INSERT INTO groupmembers VALUES("login",12);
 DELETE FROM groupjoin WHERE groupjoinID = 123;

12. Pobieranie danych o otrzymanych zaproszeniach (analogicznie wysłanych zaproszeniach, otrzymanych prośbach, wysłanych prośbach):

 SELECT groupname, name, surname, groupjoinID FROM groupjoin NATURAL JOIN groups NATURAL JOIN users WHERE groupjoin.invitation = TRUE AND groupjoin.groupjoinowner <> "login";

13. Tworzenie nowego zobowiązania:

 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);

14. Usuwanie zobowiązania:

 DELETE FROM commitments WHERE commitmentID = 5;

15. Zmiana statusu zobowiązania:

 UPDATE commitments SET status = 2 WHERE commitmentID = 5;

16. Pobieranie danych o otrzymanych zobowiązaniach:

 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");