Zestaw procedur, funkcji i wyzwalaczy w wybranej tematyce Kina

Projekt: opracowanie bazy danych (wraz z wypełnieniem w wybranej tematyce oraz przygotowanie zestawu procedur, funkcji i wyzwalaczy obsługujących daną bazę danych.

1. Opis bazy danych

Baza składa się z 8 tabel:
– Archiwum ewidencji – tabela zawierające zarchiwizowane dane dotyczące zarobków w poszczególnych miesiącach/latach, dane do tej tabeli wprowadzane są poprzez czyszczenie innej, wpisy są tutaj rozdzielane na poszczególne miesiące. Tabela dodatkowo posiada statystyki odnośnie ilości sprzedanych rodzajów biletów w danym miesiącu.
– Kina – lista kin w poszczególnych miastach wraz z adresem.
– Pracownicy – lista pracowników wraz z ich danymi kontaktowymi, wynagrodzeniem itd. Dodatkowo każdy z pracowników posiada swoje przypisanie do kina.
– Filmy – lista aktualnie wyświetlanych filmów, gdzie każdy posiada swoją cenę bazową i przelicznik który jest dynamiczny i na podstawie którego obliczana jest cena rzeczywista biletu. Dodatkowo każdy film posiada swój status a także datę premiery.
– Ewidencja – lista zakupionych aktualnie biletów, w tabeli widnieją wpisy które nie zostały jeszcze zarchiwizowane, dane muszą zostać przeprocesowane przez odpowiednią procedurę a dopiero potem mogą zostać zarchiwizowane.
– Faktury – lista faktur wystawionych na osoby które posiadają karty stałego klienta oraz są one zarejestrowane na firmę, w tabeli są zawarte dodatkowo sumy oraz daty wystawienia.
– Karty stałego klienta – lista stałych użytkowników, na których zarejestrowane są karty, każda z nich posiada swoje dane, punkty za które można sobie zakupić przedmioty, typ konta który różni się od ilości zakupionych biletów, a także same statystyki jak np. w/w ilość obejrzanych filmów.
– Produkty marketingowe – lista produktów na które można wymieniać punkty z kart stałego klienta, dodatkowo każdy produkt posiada swoje statystyki, ilość sprzedanych egzemplarzy itd.

2. Procedury

a. Usuwanie starych kont

– usun_stare_konta – procedura z parametrem oznaczającym ilość dni, procedura usuwa karty stałego klienta starsze niż wprowadzona wartość, istnieją jednak warunki które pozwalają opóźnić usunięcie konta dając tym samym użytkownikowi szansę na odratowanie, otóż jeśli użytkownik posiada wyższą rangę na swoim koncie ORAZ (co jest warunkiem koniecznym) konto jest „zaparkowane” tzn. zatrzymane, wraz z uruchomieniem procedury jedynie jego ranga spada, do czasu aż osiągnie możliwie najniższą, po czym konto zdejmuje blokadę i przy następnym uruchomieniu procedury konto jest usuwane.

Przykładowe uruchomienie:

– exec USUN_STARE_KONTA(30);

Po uruchomieniu procedury usuwane są konta które starsze niż podana ilość dni podczas wywoływania procedury.

Wyjątkiem są wiersze z „kontem zaparkowanym” tj. wartością 1 oraz konta z wyższą rangą (niż GRAY), są one stopniowo degradowane aż osiągną najniższą rangę i tak jak pozostałe są usuwane.

Po uruchomieniu procedury dla wartości „500”:

b. Raport ewidencji

– raport_ewidencji – warunkiem koniecznym przed przystąpieniem do wykonania backupu ewidencji, jest wykonanie raportu, dopiero te wiersze które zostały wcześniej zaraportowane mogą zostać zarchiwizowane, raporty takie pozwalają w przyszłości na sprawdzenie popytu na dany film.

Funkcjonalność: procedura wybiera wszystkie wiersze które jeszcze nie zostały zarchiwizowane po czym wylicza ilość wpisów przypadających na dany film. Dla każdego filmu zliczana jest ilość istniejącej ewidencji (wierszy). Po czym dane te zostają wypisane na ekran. Następnie procedura we wszystkich tych wierszach zmienia wartość kolumny „raportowane” na 1 co pozwala wykryć możliwość backupu.

Przykładowe uruchomienie:

– SET SERVEROUTPUT ON;

– exec raport_ewidencji;

Dane po uruchomieniu procedury na pierwszy rzut oka wyglądają na nietknięte, modyfikowana jest jedynie kolumna „RAPORTOWANE” która pozwala na ich późniejsze zarchiwizowanie.

Dodatkowo wyświetlony zostaje raport który jest zasadniczym zastosowaniem tej procedury:

c. Archiwizuj ewidencje

– archiwizacja_ewidencji – kursor wyszukuje dane które zostały zaraportowane ale jeszcze nie zarchiwizowane, zyski z tych wpisów doliczane są do archiwum tym samym powiększając bazę zysków ogólnych w danym miesiącu/roku, po doliczeniu wszystkiego dane są kasowane, tabela ta jest wymogiem w tego typu bazach gdyż sama tabela ewidencja gdzie zawarte są wszystkie transakcje czyt. Kupione bilety zapełniła by się w bardzo szybkim tempie.

Przykładowe uruchomienie:

– exec archiwizuj_ewidencje;

Dane z tej tabeli jeśli posiadają wartość pola „RAPORTOWANE” = 1, zostają poddane archiwizacji, która na podstawie daty przydziela do wydzielonego wiersza w archiwum.

Statystyki idą w górę jak i doliczany jest zysk.

3. Funkcje

a. Zysk z miesiąca

– zysk_z_miesiaca – funkcja bierze wartość z archiwum ewidencji (zysk z miesiąca) oraz zlicza wartości wszystkich bieżących ewidencji, które nie zostały jeszcze zarchiwizowane, zlicza zarobki z danego miesiąca w danym roku (dane podawane przy wywoływaniu), wyjątkiem jest jeśli wybrany jest miesiąc aktualny, wtedy na podstawie aktualnych zarobków w danym miesiącu przewidywany jest zarobek z dalszej części miesiąca, proste działanie proporcjonalne.

Przykładowe uruchomienie:

– select distinct zysk_z_miesiaca(1,2017) from archiwum_ewidencji;

Zysk z miesiąca obliczany jest na podstawie danych zawartych w archiwum ORAZ danych w ewidencji które nie zostały jeszcze zarchiwizowane, jak widać na przykładzie styczeń 2016 posiada zysk 74 jednostki (złote)

b. Przewidywany zysk

– przewidywany_zysk – obliczany jest na podstawie istniejących w magazynie produktów oraz na wcześniejszym popycie danych przedmiotów, obliczane są wartości dzięki którym daje się określić przewidywany zysk z pozostałych przedmiotów marketingowych.

Przykładowe uruchomienie:

– select distinct przewidywany_zysk from produkty_marketingowe;

4. Wyzwalacze

a. Aktualizacja cen

– aktualizacja_cen – wyzwalacz pozwalający na ruchome określanie cen dla danego filmu, wraz z premierą nowego, ceny pozostałych są obniżane, oraz po usunięciu filmu z repertuaru analogicznie ceny pozostałych idą w górę

b. Aktualizacja tabeli do archiwizacji

– aktualizacja_tabeli – wyzwalacz kontrolujący strukturę tabeli, podczas archiwizacji danych, kwoty z tabeli ewidencja są dodawane do tabeli archiwizacja_ewidencji dla danego miesiąca w danym roku, pierw muszą jednak powstać w tym celu tabele, aby dane mogły zostać dodane (zarchiwizowane, a następnie usunięte)

Skrypt czuwa nad spójnością tabeli, tzn. w momencie gdyby np. usunięty zostałby styczeń 2017 roku. Trigger odnalazłby „najnowszą datę” po czym dorobił wszystkie miesiące aż do bieżącego (w tym również lata jeśli była by taka konieczność)

c. Aktualizacja typu konta

– aktualizacja_typu_konta – trigger wykonywany po to aby doliczać zakupione bilety na filmy dla danego użytkownika jeśli ten posiada kartę stałego klienta, dodatkowo wyzwalacz ustawia typ konta w zależności od ilości obejrzanych filmów (zakupionych biletów)

d. Nadanie podwyżki

– daj_podwyzke – wyzwalacz w którym zostało zostosowane „for each row”, wykonywany jest podczas archiwizacji ewidencji, dla każdego usuniętego wiersza, dodaje pracownikowi (który dany bilet sprzedał) drobną podwyżkę w postaci 0.5 jednostki

Wg powyższego zdjęcia, podwyżki otrzymaliby pracownicy o ID = 1,2,3 gdzie 1 dostałby 1,5 zł, drugi 1 zł, trzeci 1 zł.

Bazy danych

Poprzedni Następny