Kod SQL tworzący kompletną strukturę danych wykorzystywaną w projekcie wygląda następująco:
SET client_encoding = 'UTF8'; SET standard_conforming_strings = off; SET check_function_bodies = FALSE; SET client_min_messages = warning; SET escape_string_warning = off; SET search_path = public, pg_catalog; -- -- Name: dblink_pkey_results; Type: TYPE; Schema: public; Owner: rewersy -- CREATE TYPE dblink_pkey_results AS ( "position" INTEGER, colname text ); ALTER TYPE public.dblink_pkey_results OWNER TO rewersy; -- -- Name: clean_emp(); Type: FUNCTION; Schema: public; Owner: rewersy -- CREATE FUNCTION clean_emp() RETURNS INTEGER AS $$ SELECT numer_pokoju FROM pokoje; $$ LANGUAGE SQL; ALTER FUNCTION public.clean_emp() OWNER TO rewersy; SET default_tablespace = ''; SET default_with_oids = FALSE; -- -- Name: autorzy; Type: TABLE; Schema: public; Owner: rewersy; Tablespace: -- CREATE TABLE autorzy ( imie CHARACTER VARYING(45) NOT NULL, nazwisko CHARACTER VARYING(45) NOT NULL, id INTEGER NOT NULL ); ALTER TABLE public.autorzy OWNER TO rewersy; -- -- Name: autorzy_id_seq; Type: SEQUENCE; Schema: public; Owner: rewersy -- CREATE SEQUENCE autorzy_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER TABLE public.autorzy_id_seq OWNER TO rewersy; -- -- Name: autorzy_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: rewersy -- ALTER SEQUENCE autorzy_id_seq OWNED BY autorzy.id; -- -- Name: autorzy_id_seq; Type: SEQUENCE SET; Schema: public; Owner: rewersy -- SELECT pg_catalog.setval('autorzy_id_seq', 161, TRUE); -- -- Name: ksiazki; Type: TABLE; Schema: public; Owner: rewersy; Tablespace: -- CREATE TABLE ksiazki ( id INTEGER NOT NULL, tytul CHARACTER VARYING(45) NOT NULL, rok_wydania INTEGER, login CHARACTER VARYING(45), wydawnictwo CHARACTER VARYING(30) ); ALTER TABLE public.ksiazki OWNER TO rewersy; -- -- Name: ksiazki_id_seq; Type: SEQUENCE; Schema: public; Owner: rewersy -- CREATE SEQUENCE ksiazki_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER TABLE public.ksiazki_id_seq OWNER TO rewersy; -- -- Name: ksiazki_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: rewersy -- ALTER SEQUENCE ksiazki_id_seq OWNED BY ksiazki.id; -- -- Name: ksiazki_id_seq; Type: SEQUENCE SET; Schema: public; Owner: rewersy -- SELECT pg_catalog.setval('ksiazki_id_seq', 169, TRUE); -- -- Name: napisali; Type: TABLE; Schema: public; Owner: rewersy; Tablespace: -- CREATE TABLE napisali ( id_autora INTEGER NOT NULL, id_ksiazka INTEGER NOT NULL ); ALTER TABLE public.napisali OWNER TO rewersy; -- -- Name: przetrzymuje; Type: TABLE; Schema: public; Owner: rewersy; Tablespace: -- CREATE TABLE przetrzymuje ( data_wypozyczenia DATE, id_ksiazka INTEGER NOT NULL, login CHARACTER VARYING(45) NOT NULL ); ALTER TABLE public.przetrzymuje OWNER TO rewersy; -- -- Name: rezerwuje; Type: TABLE; Schema: public; Owner: rewersy; Tablespace: -- CREATE TABLE rezerwuje ( id_ksiazka INTEGER NOT NULL, login CHARACTER VARYING(45) NOT NULL ); ALTER TABLE public.rezerwuje OWNER TO rewersy; -- -- Name: uzytkownik; Type: TABLE; Schema: public; Owner: rewersy; Tablespace: -- CREATE TABLE uzytkownik ( login CHARACTER VARYING(45) NOT NULL, imie CHARACTER VARYING(45) NOT NULL, nazwisko CHARACTER VARYING(45), e_mail CHARACTER VARYING(50), haslo CHARACTER VARYING(32), budynek CHARACTER VARYING(10), pokoj CHARACTER VARYING(10) ); ALTER TABLE public.uzytkownik OWNER TO rewersy; -- -- Name: zaprosil; Type: TABLE; Schema: public; Owner: rewersy; Tablespace: -- CREATE TABLE zaprosil ( zaproszony CHARACTER VARYING(45) NOT NULL, login CHARACTER VARYING(45) ); ALTER TABLE public.zaprosil OWNER TO rewersy; -- -- Name: zaproszony; Type: TABLE; Schema: public; Owner: rewersy; Tablespace: -- CREATE TABLE zaproszony ( id INTEGER NOT NULL, login_polecil CHARACTER VARYING(45), hash text, imie CHARACTER VARYING(45), nazwisko CHARACTER VARYING(45), email CHARACTER VARYING(50) ); ALTER TABLE public.zaproszony OWNER TO rewersy; -- -- Name: zaproszony_id_seq; Type: SEQUENCE; Schema: public; Owner: rewersy -- CREATE SEQUENCE zaproszony_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER TABLE public.zaproszony_id_seq OWNER TO rewersy; -- -- Name: zaproszony_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: rewersy -- ALTER SEQUENCE zaproszony_id_seq OWNED BY zaproszony.id; -- -- Name: zaproszony_id_seq; Type: SEQUENCE SET; Schema: public; Owner: rewersy -- SELECT pg_catalog.setval('zaproszony_id_seq', 58, TRUE); -- -- Name: id; Type: DEFAULT; Schema: public; Owner: rewersy -- ALTER TABLE autorzy ALTER COLUMN id SET DEFAULT NEXTVAL('autorzy_id_seq'::regclass); -- -- Name: id; Type: DEFAULT; Schema: public; Owner: rewersy -- ALTER TABLE ksiazki ALTER COLUMN id SET DEFAULT NEXTVAL('ksiazki_id_seq'::regclass); -- -- Name: id; Type: DEFAULT; Schema: public; Owner: rewersy -- ALTER TABLE zaproszony ALTER COLUMN id SET DEFAULT NEXTVAL('zaproszony_id_seq'::regclass); -- -- Data for Name: autorzy; Type: TABLE DATA; Schema: public; Owner: rewersy -- -- -- Name: autorzy_pkey; Type: CONSTRAINT; Schema: public; Owner: rewersy; Tablespace: -- ALTER TABLE ONLY autorzy ADD CONSTRAINT autorzy_pkey PRIMARY KEY (id); -- -- Name: ksiazki_pkey; Type: CONSTRAINT; Schema: public; Owner: rewersy; Tablespace: -- ALTER TABLE ONLY ksiazki ADD CONSTRAINT ksiazki_pkey PRIMARY KEY (id); -- -- Name: napisali_pkey; Type: CONSTRAINT; Schema: public; Owner: rewersy; Tablespace: -- ALTER TABLE ONLY napisali ADD CONSTRAINT napisali_pkey PRIMARY KEY (id_autora, id_ksiazka); -- -- Name: przetrzymuje_pkey; Type: CONSTRAINT; Schema: public; Owner: rewersy; Tablespace: -- ALTER TABLE ONLY przetrzymuje ADD CONSTRAINT przetrzymuje_pkey PRIMARY KEY (id_ksiazka, login); -- -- Name: rezerwuje_pkey; Type: CONSTRAINT; Schema: public; Owner: rewersy; Tablespace: -- ALTER TABLE ONLY rezerwuje ADD CONSTRAINT rezerwuje_pkey PRIMARY KEY (id_ksiazka, login); -- -- Name: uzytkownik_pkey; Type: CONSTRAINT; Schema: public; Owner: rewersy; Tablespace: -- ALTER TABLE ONLY uzytkownik ADD CONSTRAINT uzytkownik_pkey PRIMARY KEY (login); -- -- Name: zaprosil_pkey; Type: CONSTRAINT; Schema: public; Owner: rewersy; Tablespace: -- ALTER TABLE ONLY zaprosil ADD CONSTRAINT zaprosil_pkey PRIMARY KEY (zaproszony); -- -- Name: zaproszony_pkey; Type: CONSTRAINT; Schema: public; Owner: rewersy; Tablespace: -- ALTER TABLE ONLY zaproszony ADD CONSTRAINT zaproszony_pkey PRIMARY KEY (id); -- -- Name: ksiazki_login_fkey; Type: FK CONSTRAINT; Schema: public; Owner: rewersy -- ALTER TABLE ONLY ksiazki ADD CONSTRAINT ksiazki_login_fkey FOREIGN KEY (login) REFERENCES uzytkownik(login) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: napisali_id_autora_fkey; Type: FK CONSTRAINT; Schema: public; Owner: rewersy -- ALTER TABLE ONLY napisali ADD CONSTRAINT napisali_id_autora_fkey FOREIGN KEY (id_autora) REFERENCES autorzy(id) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: napisali_id_ksiazka_fkey; Type: FK CONSTRAINT; Schema: public; Owner: rewersy -- ALTER TABLE ONLY napisali ADD CONSTRAINT napisali_id_ksiazka_fkey FOREIGN KEY (id_ksiazka) REFERENCES ksiazki(id) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: przetrzymuje_id_ksiazka_fkey; Type: FK CONSTRAINT; Schema: public; Owner: rewersy -- ALTER TABLE ONLY przetrzymuje ADD CONSTRAINT przetrzymuje_id_ksiazka_fkey FOREIGN KEY (id_ksiazka) REFERENCES ksiazki(id) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: przetrzymuje_login_fkey; Type: FK CONSTRAINT; Schema: public; Owner: rewersy -- ALTER TABLE ONLY przetrzymuje ADD CONSTRAINT przetrzymuje_login_fkey FOREIGN KEY (login) REFERENCES uzytkownik(login) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: rezerwuje_id_ksiazka_fkey; Type: FK CONSTRAINT; Schema: public; Owner: rewersy -- ALTER TABLE ONLY rezerwuje ADD CONSTRAINT rezerwuje_id_ksiazka_fkey FOREIGN KEY (id_ksiazka) REFERENCES ksiazki(id) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: rezerwuje_login_fkey; Type: FK CONSTRAINT; Schema: public; Owner: rewersy -- ALTER TABLE ONLY rezerwuje ADD CONSTRAINT rezerwuje_login_fkey FOREIGN KEY (login) REFERENCES uzytkownik(login) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: zaprosil_login_fkey; Type: FK CONSTRAINT; Schema: public; Owner: rewersy -- ALTER TABLE ONLY zaprosil ADD CONSTRAINT zaprosil_login_fkey FOREIGN KEY (login) REFERENCES uzytkownik(login) ON UPDATE RESTRICT ON DELETE RESTRICT; -- -- Name: zaproszony_login_polecil_fkey; Type: FK CONSTRAINT; Schema: public; Owner: rewersy -- ALTER TABLE ONLY zaproszony ADD CONSTRAINT zaproszony_login_polecil_fkey FOREIGN KEY (login_polecil) REFERENCES uzytkownik(login) ON UPDATE RESTRICT ON DELETE RESTRICT;
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
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.
* Zwraca imię zalogowanego użytkownika::
SELECT imie FROM uzytkownik WHERE login= '$_SESSION["user"]'
* Zwraca nazwisko zalogowanego użytkownika::
SELECT nazwisko FROM uzytkownik WHERE login= '$_SESSION["user"]'
* Zwraca email użytkownika::
SELECT e_mail FROM uzytkownik WHERE login= '$_SESSION["user"]'
* Zwraca hasło zakodowane w md5::
SELECT haslo FROM uzytkownik WHERE login= '$_SESSION["user"]'
* Zwraca budynek::
SELECT budynek FROM uzytkownik WHERE login= '$_SESSION["user"]'
* Zwraca pokój::
SELECT pokoj FROM uzytkownik WHERE login= '$_SESSION["user"]'
* Zmienia imię użytkownika::
UPDATE uzytkownik SET imie= '$new_name' WHERE login= '$_SESSION["user"]'
* Zmienia nazwisko::
UPDATE uzytkownik SET nazwisko= '$new_surname' WHERE login= '$_SESSION["user"]'
* Zmienia email::
UPDATE uzytkownik SET e_mail= '$email' WHERE login= '$_SESSION["user"]'
* Zmienia hasło::
UPDATE uzytkownik SET haslo= '$pass' WHERE login= '$_SESSION["user"]'
* Zmienia budynek::
UPDATE uzytkownik SET budynek= '$building' WHERE login= '$_SESSION["user"]'
* Zmienia pokój::
UPDATE uzytkownik SET pokoj= '$room' WHERE login= '$_SESSION["user"]'
* Dodaje książkę::
INSERT INTO ksiazki (tytul, rok_wydania, wydawnictwo, login) VALUES ( '$title', '$year', '$publisher', '$_SESSION["user"]' )"
* Pobiera listę książek zalogowanego użytkownika::
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
* Usuwa książkę o podanym ID::
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')
* Zwraca dane książki o podanym ID::
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'
* Uaktualnij profil książki::
UPDATE ksiazki SET tytul= '$title' , rok_wydania= '$year', wydawnictwo= '$publisher' WHERE login= '$_SESSION["user"]' AND id= '$id';
* Usuwa nieużywanych autorów (tych, do których nie ma przypisanych żadnych książek)::
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'))
* Pobiera listę książek zalogowanego użytkownika::
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
* zwraca login jeśli książka jest pożyczona, jeśli nie to zwraca empty:
SELECT login FROM przetrzymuje WHERE id_ksiazka= 'id'
* zwraca login jeśli książka jest zarezerwowana, jeśli nie to zwraca empty:
SELECT login FROM rezerwuje WHERE id_ksiazka= '$id'
* dokonuje rezerwacji książki o $id na osobę $login:
INSERT INTO rezerwuje (id_ksiazka, login) VALUES ('$id', '$login')
* usuwa rezerwację książki o $id na osobę $login:
DELETE FROM rezerwuje WHERE login= '$login' AND id_ksiazka= '$id'
* zwraca date pozyczenia książki:
SELECT data_wypozyczenia FROM przetrzymuje WHERE id_ksiazka= '$id'
* Zwraca imię i nazwisko loginu:
SELECT imie, nazwisko FROM uzytkownik WHERE login= '$login'
* zwraca pokoj i budynek w ktorym przesiaduje $login:
SELECT pokoj, budynek FROM uzytkownik WHERE login= '$login'
* zwraca email uzytkownika o loginie $login:
SELECT e_mail FROM uzytkownik WHERE login= '$login'
* oznacza książkę o id $id jako pożyczoną komuś o loginie login:
INSERT INTO przetrzymuje (id_ksiazka, login, data_wypozyczenia) VALUES (".$id." , '$login', 'date("Y-m-d")')
* usuwa książkę z bazy pozyczonych:
DELETE FROM przetrzymuje WHERE id_ksiazka= '$id'
* Czy w bazie jest osoba o danym loginie true/false::
SELECT * FROM uzytkownik WHERE login= '$login'
* Czy w bazie jest osoba o podanym imieniu i nazwisku , tak - zwraca login::
SELECT login FROM uzytkownik WHERE imie= '$name' AND nazwisko= '$surname'
* zwraca listę wszystkich książek dostępnych w bazie oprócz posiadanych przez zalogowanego użytkownika::
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;
* SQL pozwalający na usunięcie użytkownika przez tego kto go zaprosił, razem z powiązaniami i książkami::
DELETE FROM rezerwuje WHERE login= '$login'
DELETE FROM rezerwuje WHERE id_ksiazka IN (SELECT id FROM ksiazki WHERE login= '$login'
DELETE FROM przetrzymuje WHERE login= '$login'
DELETE FROM zaproszony WHERE login_polecil= '$login'
DELETE FROM zaprosil WHERE zaproszony= '$login' UPDATE zaprosil SET login='$_SESSION["user"]' WHERE login='$login'
SELECT id FROM ksiazki WHERE login='$login'
DELETE FROM uzytkownik WHERE login='$login'
* Dodaje nowego użytkownika::
INSERT INTO uzytkownik(login, imie, nazwisko, e_mail, haslo, budynek, pokoj) VALUES('$login','$name','$surname','$youremail','md5($password)','$building','$room')
