Both sides previous revision
Poprzednia wersja
Nowa wersja
|
Poprzednia wersja
|
pl:dydaktyka:sbd:2009:projekty:rewersy:logiczny [2010/08/30 11:49] sbd09 |
pl:dydaktyka:sbd:2009:projekty:rewersy:logiczny [2019/06/27 15:50] (aktualna) |
==== -. Projektowanie tabel. ==== | ==== -. Projektowanie tabel. ==== |
Kod SQL tworzący kompletną strukturę danych wykorzystywaną w projekcie wygląda następująco: | Kod SQL tworzący kompletną strukturę danych wykorzystywaną w projekcie wygląda następująco: |
<code> | <code sql> |
SET client_encoding = 'UTF8'; | SET client_encoding = 'UTF8'; |
SET standard_conforming_strings = off; | SET standard_conforming_strings = off; |
| |
==== -. Słownki danych. ==== | ==== -. Słownki danych. ==== |
| === autorzy: === |
| * Klucz podstawowy (id) |
| * **__id__** - typ: serial, niepusty, **Klucz podstawowy tabeli autorzy** |
| * **__imie__** - typ: character varying(45), **Imię autora książki** |
| * **__nazwisko__** - typ: character varying(45), **Nazwisko autora książki** |
| |
| |
| === ksiazki: === |
| * Klucz podstawowy (id) |
| * **__id__** - typ: serial, niepusty, **Klucz podstawowy tabeli ksiażki** |
| * **__tytul__** - typ: character varying(45), ** Tytuł książki, która jest dostępna w systemie** |
| * **__rok_wydania__** - typ: integer, **Rok wydania książki** |
| * **__login__** - typ: character varying(45), klucz obcy - tabela 'uzytkownik' **Login użytkownika, który jest właścicielem książki** |
| * **__wydawnictwo__** - typ: character varying(30), **Wydawnictwo, które wydało książkę** |
| |
| === napisali: === |
| * klucz podstawowy: (id_autora, id_ksiazka) |
| * **__id_autora__** - typ: integer, niepusty, klucz obcy z tabeli 'autorzy', **Identyfikator autora** |
| * **__id_ksiazka__** - typ: integer, niepusty, klucz obcy z tabeli 'ksiazki', **Identikator książki** |
| |
| === uzytkownik: === |
| * Klucz podstawowy (login) |
| * **__login__** - typ: character varying(45), niepusty, Klucz podstawowy ** ** |
| * **__imie__** - typ: character varying(45), niepusty, **Imię użytkownika** |
| * **__nazwisko__** - typ: character varying(45), **Nazwisko użytkownika** |
| * **__e_mail__** - typ: character varying(50), **E-mail użytkownika** |
| * **__haslo__** - typ: character varying(32), ** hasło użytkownika do systemu, trzymane w bazie w postaci zakodowanej** |
| * **__budynek__** - typ: character varying(10), **Nazwa budynku AGH, w którym pracuje użytkownik systemu** |
| * **__pokoj__** - typ: character varying(10), **Pokój na uczelni AGH, gdzie użytkownik pracuje użytkownika systemu** |
| |
| === przetrzymuje: === |
| * podstawowy tabeli to para (id_ksiazka, login) |
| * **__data_wypozyczenia__** - typ: date, **Data wypożyczenia książki** |
| * **__id_ksiazka__** - typ: integer, niepusty, Klucz obcy z tabeli 'ksiazki' **Identyfikator książki, która została pożyczona** |
| * **__login__** - typ: character varying(45), niepusty, klucz obcy z tabeli 'uzytkownik' **Login użytkownika, który wypożyczył daną książkę** |
| |
| |
| === rezerwuje: === |
| * Klucz podstawowy (id_ksiazka, login) |
| * **__id_ksiazka__** - typ: integer, niepusty, **Id zarezerwowanej książki** |
| * **__login__** - typ: character varying(45), niepusty, **Login użytkonika, który książkę zarezerwował** |
| |
| === zaprosil: === |
| * Klucz podstawowy (zaproszony). Tabela w celu prowadzenia drzewa zaproszeń, tzn "kto-kogo zaprosił" |
| * **__zaproszony__** - typ: character varying(45), niepusty, **Login użytkownika zaproszonego** |
| * **__login__** - typ: character varying(45), **Login użytkownika, który zaproszał** |
| |
| === zaproszony: === |
| * Klucz podstawowy (id). Tabela to rejestracja użytkowników zaproszonych (tzn. tych do których został wysłany email z zaproszeniem) ale jeszcze nie zarejestrowanych |
| * **__id__** - typ: serial, **Klucz podstawowy** |
| * **__login_polecil__** - typ: character varying(45), **Login użytkownika, który polecił nowego użytkownika** |
| * **__hash__** - typ: text, **unikalny, automatycznie generowany kod dołączany to URL zawierającego formularzem rejestracyjnym** |
| * **__imie__** - typ: character varying(45), **Imię zaproszonej osoby** |
| * **__nazwisko__** - typ: character varying(45), **Nazwisko zaproszonej osoby** |
| * **__email__** - typ: character varying(50), **E-mail zaproszonej osoby** |
| |
==== -. Analiza zależności funkcyjnych i normalizacja tabel. ==== | ==== -. Analiza zależności funkcyjnych i normalizacja tabel. ==== |
| |
| Wszystkie tabele bazy danych spełniają co najmniej normę 3NF. Nie przewidujemy więc normalizacji. |
| Jedyną wątpliwością jaka może się pojawić przy dokładniejszym przyjrzeniu się strukturze bazy danych dotyczy tabeli 'uzytkownik'. |
| Wydaje się, że kolumny "pokoj" i "budynek" są redundantne. Możnaby stworzyć tabelę 'Miejsca_pracy'(Kolumny: pokoj i budynek) oraz relację jeden-do-wielu pomiędzy tabelą 'uzytkownik' a tabelą 'miejsca_pracy'. Byłoby to korzystne pod jednym względem: użytkownicy systemu mogliby wpisać do swojego profilu wiele miejsc pracy, jako że niektórzy pracownicy AGH mają do dyspozycji więcej niż jeden pokój. Postanowiliśmy jednak zrezygnować z tego udogodnienia, zezwalając użytkownikom systemu REWERSY na wpisanie tylko jednej lokalizacji. Przez ten prosty zabieg nasz system stał się bardziej przejrzysty, a baza danych - mniej skomplikowana |
| |
==== -. Denormalizacja struktury tabel. ==== | ==== -. Denormalizacja struktury tabel. ==== |
| |
| Nie przewidujemy denormalizacji tabel. Archiwizacja danych systemu jak i raporty i zestawienia są na tyle nieskomplikowane, że nie potrzebujemy robić dodatkowych złączeń, a także tworzyć redundantnych danych. |
| |
==== -. Projektowanie operacji na danych. ==== | ==== -. Projektowanie operacji na danych. ==== |
| |
| * //Zwraca imię zalogowanego użytkownika://: |
| <code sql>SELECT imie FROM uzytkownik WHERE login= '$_SESSION["user"]'</code> |
| * //Zwraca nazwisko zalogowanego użytkownika://: |
| <code sql>SELECT nazwisko FROM uzytkownik WHERE login= '$_SESSION["user"]'</code> |
| * //Zwraca email użytkownika://: |
| <code sql>SELECT e_mail FROM uzytkownik WHERE login= '$_SESSION["user"]'</code> |
| * //Zwraca hasło zakodowane w md5://: |
| <code sql>SELECT haslo FROM uzytkownik WHERE login= '$_SESSION["user"]'</code> |
| * //Zwraca budynek://: |
| <code sql>SELECT budynek FROM uzytkownik WHERE login= '$_SESSION["user"]'</code> |
| * //Zwraca pokój://: |
| <code sql>SELECT pokoj FROM uzytkownik WHERE login= '$_SESSION["user"]'</code> |
| \\ |
| \\ |
| \\ |
| \\ |
| * //Zmienia imię użytkownika://: |
| <code sql>UPDATE uzytkownik SET imie= '$new_name' WHERE login= '$_SESSION["user"]'</code> |
| * //Zmienia nazwisko://: |
| <code sql>UPDATE uzytkownik SET nazwisko= '$new_surname' WHERE login= '$_SESSION["user"]'</code> |
| * //Zmienia email://: |
| <code sql>UPDATE uzytkownik SET e_mail= '$email' WHERE login= '$_SESSION["user"]'</code> |
| * //Zmienia hasło://: |
| <code sql>UPDATE uzytkownik SET haslo= '$pass' WHERE login= '$_SESSION["user"]'</code> |
| * //Zmienia budynek://: |
| <code sql>UPDATE uzytkownik SET budynek= '$building' WHERE login= '$_SESSION["user"]'</code> |
| * //Zmienia pokój://: |
| <code sql>UPDATE uzytkownik SET pokoj= '$room' WHERE login= '$_SESSION["user"]'</code> |
| \\ |
| \\ |
| \\ |
| \\ |
| * //Dodaje książkę://: |
| <code sql>INSERT INTO ksiazki (tytul, rok_wydania, wydawnictwo, login) VALUES ( '$title', '$year', '$publisher', '$_SESSION["user"]' )"</code> |
| * //Pobiera listę książek zalogowanego użytkownika://: |
| <code sql>SELECT tytul, rok_wydania, wydawnictwo, imie, nazwisko, ksiazki.id FROM ksiazki |
| |
| INNER JOIN napisali ON ksiazki.id=napisali.id_ksiazka |
| |
| INNER JOIN autorzy ON autorzy.id=napisali.id_autora |
| |
| WHERE login= '$_SESSION["user"]' ORDER BY tytul, id</code> |
| * //Usuwa książkę o podanym ID://: |
| <code sql>DELETE FROM autorzy WHERE id IN (SELECT DISTINCT id FROM autorzy WHERE id NOT IN |
| |
| (SELECT DISTINCT id_autora FROM napisali INNER JOIN autorzy ON id_autora= 'id')</code> |
| * //Zwraca dane książki o podanym ID://: |
| <code sql>SELECT tytul, rok_wydania, wydawnictwo, imie, nazwisko FROM ksiazki |
| |
| INNER JOIN napisali ON ksiazki.id=napisali.id_ksiazka |
| |
| INNER JOIN autorzy ON autorzy.id=napisali.id_autora |
| |
| WHERE login= '$_SESSION["user"]' AND ksiazki.id= '$id'</code> |
| * //Uaktualnij profil książki://: |
| <code sql>UPDATE ksiazki SET tytul= '$title' , rok_wydania= '$year', |
| wydawnictwo= '$publisher' WHERE login= '$_SESSION["user"]' AND id= '$id';</code> |
| * //Usuwa nieużywanych autorów (tych, do których nie ma przypisanych żadnych książek)://: |
| <code sql>DELETE FROM autorzy WHERE id IN (SELECT DISTINCT id FROM autorzy WHERE id NOT IN |
| |
| (SELECT DISTINCT id_autora FROM napisali INNER JOIN autorzy ON id_autora= 'id'))</code> |
| * //Pobiera listę książek zalogowanego użytkownika://: |
| <code sql>SELECT DISTINCT tytul, rok_wydania, wydawnictwo, imie, nazwisko, ksiazki.id, ksiazki.login FROM ksiazki |
| |
| INNER JOIN napisali ON ksiazki.id=napisali.id_ksiazka |
| |
| INNER JOIN autorzy ON autorzy.id=napisali.id_autora |
| |
| INNER JOIN przetrzymuje ON przetrzymuje.login= '$SESSION["user"]' |
| |
| WHERE przetrzymuje.id_ksiazka=ksiazki.id ORDER BY tytul</code> |
| * //zwraca login jeśli książka jest pożyczona, jeśli nie to zwraca empty//: |
| <code sql>SELECT login FROM przetrzymuje WHERE id_ksiazka= 'id'</code> |
| * //zwraca login jeśli książka jest zarezerwowana, jeśli nie to zwraca empty//: |
| <code sql>SELECT login FROM rezerwuje WHERE id_ksiazka= '$id'</code> |
| * //dokonuje rezerwacji książki o $id na osobę $login//: |
| <code sql>INSERT INTO rezerwuje (id_ksiazka, login) VALUES ('$id', '$login')</code> |
| * //usuwa rezerwację książki o $id na osobę $login//: |
| <code sql>DELETE FROM rezerwuje WHERE login= '$login' AND id_ksiazka= '$id'</code> |
| * //zwraca date pozyczenia książki//: |
| <code sql>SELECT data_wypozyczenia FROM przetrzymuje WHERE id_ksiazka= '$id'</code> |
| * //Zwraca imię i nazwisko loginu//: |
| <code sql>SELECT imie, nazwisko FROM uzytkownik WHERE login= '$login'</code> |
| * //zwraca pokoj i budynek w ktorym przesiaduje $login//: |
| <code sql>SELECT pokoj, budynek FROM uzytkownik WHERE login= '$login'</code> |
| * //zwraca email uzytkownika o loginie $login//: |
| <code sql>SELECT e_mail FROM uzytkownik WHERE login= '$login'</code> |
| * //oznacza książkę o id $id jako pożyczoną komuś o loginie login//: |
| <code sql>INSERT INTO przetrzymuje (id_ksiazka, login, data_wypozyczenia) VALUES (".$id." , '$login', 'date("Y-m-d")')</code> |
| * //usuwa książkę z bazy pozyczonych//: |
| <code sql>DELETE FROM przetrzymuje WHERE id_ksiazka= '$id'</code> |
| * //Czy w bazie jest osoba o danym loginie true/false://: |
| <code sql>SELECT * FROM uzytkownik WHERE login= '$login'</code> |
| * //Czy w bazie jest osoba o podanym imieniu i nazwisku , tak - zwraca login://: |
| <code sql>SELECT login FROM uzytkownik WHERE imie= '$name' AND nazwisko= '$surname'</code> |
| * //zwraca listę wszystkich książek dostępnych w bazie oprócz posiadanych przez zalogowanego użytkownika://: |
| <code sql>SELECT DISTINCT tytul, rok_wydania, wydawnictwo, imie, nazwisko, ksiazki.id, login FROM ksiazki |
| |
| INNER JOIN napisali ON ksiazki.id=napisali.id_ksiazka |
| |
| INNER JOIN autorzy ON autorzy.id=napisali.id_autora |
| |
| WHERE login'' '$_SESSION["user"]' ORDER BY tytul, ksiazki.id;</code> |
| * //SQL pozwalający na usunięcie użytkownika przez tego kto go zaprosił, razem z powiązaniami i książkami://: |
| * //usunięcie rezerwacji//: |
| * <code sql>DELETE FROM rezerwuje WHERE login= '$login'</code> |
| * //usunięcie zarezerwowanych książek//: |
| * <code sql>DELETE FROM rezerwuje WHERE id_ksiazka IN (SELECT id FROM ksiazki WHERE login= '$login'</code> |
| * //usunięcie przetrzymywanych książek//: |
| * <code sql>DELETE FROM przetrzymuje WHERE login= '$login'</code> |
| * //usunięcie zaproszeń//: |
| * <code sql>DELETE FROM zaproszony WHERE login_polecil= '$login'</code> |
| * //przepięcie zaproszeń (żeby nie było luki w drzewie zaproszeń)//: |
| * <code sql>DELETE FROM zaprosil WHERE zaproszony= '$login' |
| UPDATE zaprosil SET login='$_SESSION["user"]' WHERE login='$login'</code> |
| * //usunięcie książek//: |
| * <code sql>SELECT id FROM ksiazki WHERE login='$login'</code> |
| * //usunięcie użytkownika z bazy//: |
| * <code sql>DELETE FROM uzytkownik WHERE login='$login'</code> |
| * //Dodaje nowego użytkownika://: |
| <code sql>INSERT INTO uzytkownik(login, imie, nazwisko, e_mail, haslo, budynek, pokoj) |
| |
| values('$login','$name','$surname','$youremail','md5($password)','$building','$room')</code> |