Skip to content

Project prepared for database course (Wrocław University of Science and Technology)

Notifications You must be signed in to change notification settings

bognaj/vet_clinic

 
 

Repository files navigation

Autorzy

Wstęp

Bazę tworzymy i wypełniamy skryptowo. Przed przystąpieniem do dalszych części należy przejść do pliku user_manual.md i zrealizować zawarte w niej polecenia.

Część 1 - projekt i utworzenie schematu (10p)

  • analiza potrzebnych danych
    • na zasadzie burzy mózgów i researchu
  • utworzenie potrzebnego schematu za pomocą narzędzia dbdiagram.io

Schemat

  • wygenerowanie potrzebnych tabel przu użyciu main.py, SQLowy kod znajduje się w db_schema/db_creation.sql

Część 2 - skryptowe wypełnienie bazy (15p)

  • wygenerowanie cząstkowej bazy użytkownikow Facebooka

    • oczyszczenie bazy
    • zrandomizowanie wartości (imiona, nazwiska, numery tel, pochodzenie)
    • zapisanie do data/users_randomized.csv przy pomocy python pandas
  • zapisanie typowych leków, sprzętów itd do plików .xlsx i .csv

  • tworzenie bazy przy użyciu main.py

  • uzupełnianie bazy przy użyciu main.py (kod znajduje się w tables_fulfill.py)

    • employees

      • pracownicy
        • księgowa
        • recepcjonistka
        • szef
        • 3 weterynarzy
        • osoba sprzątająca
      • zarobki - na podstawie strony wynagrodzenia.pl
      • imiona i nazwiska na podstawie zrandomizowanych wartości z bazy użytkowników Facebooka
    • rooms

      • gabinety weterynarzy

      • gabinet zabiegowy

      • recepcja

      • pomieszczenie socjalne

      • zaplecze

    • equipment

      • lista, z której bierzemy informacje znajduje się w pliku meds.xlsx

      • wygenerowano losowo dane liczbowe

      • pomieszczenie na podstawie informacji o pomieszczeniach

    • meds

      • lista, z której bierzemy informacje znajduje się w pliku drugs.csv
      • wygenerowane losowo dane liczbowe
    • owners

      • losowanie z zakresu, każdemu przypisujemy od 1 do kilku zwierząt wg prawdopodobiństwa
    • pets

      • psy: regresja liniowa na podstawie pliku dogs.xlsx

      • waga, wzrost oraz czas życia wszystkich zwierząt na podstawie informacji dostępnych w internecie

      • wygenerowana losowo data urodzenia

    • visits

      • po 20 dziennie, część odwołana, wg prawdopodobieństwa

      • za odwołaną wizytę płaci się opłatę manipulacyjną w wysokośći 50pln

      • przypisanie zwierzęcia do lekarza prowadzącego

    • meds_prescribed

      • na podstawie wizyty
    • cash flow

      • na podstawie wizyt i pracowników
  • przykładowe wyniki, dla poszczególnych tabel, przy użyciu zapytania:

    SELECT * FROM `nazwa_tabeli` LIMIT 10
  • employees

tabela1

  • rooms

tabela2

  • equipment

tabela3

  • meds

tabela3

  • owners

tabela3

  • pets

tabela3

  • visits

tabela3

  • meds prescribed

tabela3

  • cash flow

tabela3

tabela3

Część 3 - analiza danych (20p)

  • wykres przedstawiający liczbę wizyt każdego dnia.
  • wykres przedstawiający bilans zysków i strat kliniki.
  • lista zwierzaków najdłużej czekających na wizytę.
  • rozkład wagi zwierząt
  • zarobki lekarzy w stosunku do liczby wizyt
  • najczęściej przypisywane leki
  • procentowy podział strat

Część 4 - raport (10p)

Po wypełnieniu poleceń z user_manual.md (czyli też uruchomieniu main.py) powinien pojawić się plik analiza.html, czyli raport.

Część 5 - dokumentacja (15p)

  • spis użytych technologii

    • Pycharm 2020.1.3

    • dbdiagram.io

    • Windows 10

    • MariaDB server 10.5

    • Python 3.9 z paczkami z pliku requirements.txt

  • lista plików i ich zawartości

    • analiza.html -> plik z analizą z #3 i #4 w formie .html

    • main.py -> skypt generujący i wypełniający bazę oraz tworzący raport

    • user_manual.md -> instrukcja inicjacyjna

    • requirements.txt -> potrzebne pythonowe paczki

    • README.md -> sprawozdanie z całości projektu

    • data

      • dogs.xlsx -> informacje o psach
      • drugs.csv -> informacje o lekach
      • users_randomized -> zrandomizowana baza danych osobowych
    • db_schema

      • db_creation.sql -> kod tworzacy bazę danych
      • schema.vuerd.json -> schemat bazy danych
    • final_code

      • schema_creaction.py -> plik zawierający funkcje potrzebne do połączenia z bazą
      • tables_fulfill.py -> plik zawierający funkcje potrzebne do wypełnienia bazy
      • report -> folder zawierający wykresy do raportu
    • generate

      • pliki z różnymi danymi dotyczącymi klinik weterynaryjnych
        • zarobki, zwierzęta, proste regresji, medykamenty, itd.
    • resources

      • notebooki do czyszczenia danych, wersje testowe, obrazki do README.md, itd.
  • kolejność i sposób uruchamiania plików, aby uzyskać gotowy projekt

    • znajduje się w user_manual.md
  • schemat projektu bazy danych

    • znajduje się w na początku README.md (część #1)
  • dla każdej relacji listę zależności funkcyjnych z wyjaśnieniem

    • meds
      • klucze kandydujące: drugID, name
      • klucz główny: drugID
      • zależności funkcyjne: trywialne (np. ordered -> ordered), drugID -> pozostałe pozdbiory atrybutów relacji, name -> pozostałe podzbiory atrybutów relacji
      • komentarz: drugID jest oczywiście unikatowym kluczem głównym. name jest unikatowym atrybutem. w naszej bazie danych leki określamy jako substancje czynne - stąd każda wartość jest unikatowa.
    • meds_prescribed
      • klucze kandydujące: (drugID, name) <- klucz kompozytowy
      • klucz główny: (drugID, name)
      • zależności funkcyjne: trywialne, (drugID, name) -> pozostałe pozdbiory atrybutów relacji
      • komenatarz: klucz główny jest kluczem kompozytowym, gdyż w ciągu jednej wizyty możemy przypisać wiele leków, a jeden lek może być przypisany wielu wizytom
    • employees
      • klucze kandydujące: employeeID
      • klucz główny: employeeID
      • zależności funkcyjne: trywialne, employeeID -> pozostałe pozdbiory atrybutów relacji
      • komenatarz: klucz główny relacji jest atrybutem unikatowym
    • pets
      • klucze kandydujące: petID
      • klucz główny: petID
      • zależności funkcyjne: trywialne, petID -> pozostałe pozdbiory atrybutów relacji
      • komenatarz: klucz główny relacji jest atrybutem unikatowym
    • owners
      • klucze kandydujące: ownerID
      • klucz główny: ownerID
      • zależności funkcyjne: trywialne, ownerID -> pozostałe pozdbiory atrybutów relacji
      • komenatarz: klucz główny relacji jest atrybutem unikatowym. adres email i numer telefonu nie są unikatowe, ponież są to tylko dane kontektowe, wartości te nie są wykorzystywane do logowania do systemu. przykładowo mąż i żona mogą posiadać ten sam numer telefonu (domowy) oraz podać tę samą skrzynkę mailową.
    • rooms
      • klucze kandydujące: roomID, number
      • klucz główny: roomID
      • zależności funkcyjne: trywialne, roomID -> pozostałe pozdbiory atrybutów relacji, number -> pozostałe pozdbiory atrybutów relacji
      • komenatarz: klucz główny relacji jest atrybutem unikatowym. każdy pokój posiada swój unikatowy numer (unikoatowy w ramach kliniki). nie użyliśmy numeru pokoju jako klucza głównego, ponieważ czasami numery pokojów zostają zmienione, a zmienienie kluczy głównych w całej bazie danych jest bardzo niepożądaną operacją.
    • equipment
      • klucze kandydujące: eqID, (eqName, roomID, status)
      • klucz główny: eqID
      • zależności funkcyjne: trywialne, eqID -> pozostałe pozdbiory atrybutów relacji, (eqName, roomID, status) -> pozostałe podzbiory atrybutów relacji
      • komenatarz: klucz główny relacji jest atrybutem unikatowym. drugi z kluczy kandydujących również jednoznacznie identyfikuje krotkę.
    • visits
      • klucze kandydujące: visitID
      • klucz główny: visitID
      • zależności funkcyjne: trywialne, visitID -> pozostałe pozdbiory atrybutów relacji
      • komenatarz: klucz główny relacji jest atrybutem unikatowym
    • cash_flow
      • klucze kandydujące: cfid
      • klucz główny: cfid
      • zależności funkcyjne: trywialne, cfid -> pozostałe pozdbiory atrybutów relacji
      • komenatarz: klucz główny relacji jest atrybutem unikatowym
  • uzasadnienie, że baza jest w EKNF

    • jak wykazaliśmy w poprzednim podpunkcie, każda nietrywialna zależność funkcyjna albo zaczyna się od nadklucza albo kończy się atrybutem elementarnym. Oznacza to, że baza jest w EKNF.
  • opis, co było najtrudniejsze podczas realizacji projektu

    • uzasadnienie, że baza jest w EKNF - musieliśmy zmienić nieco sam schemat bazy, by baza taka się stała.
    • wyeliminowanie problemów technicznych związanych z generowaniem skryptowym i wypełnianiem bazy

About

Project prepared for database course (Wrocław University of Science and Technology)

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages

  • HTML 98.3%
  • Jupyter Notebook 1.5%
  • Python 0.2%