Projekt WBD-3

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

PROJEKT WBD Przedsiębiorstwo - dostawca telewizji kablowej

Spis treści 1. Zakres i cel projektu 2. Definicja systemu 2.1 Perspektywy użytkowników 3. Model konceptualny 3.1 Definicja zbiorów encji określonych w projekcie oraz określenie atrybutów i ich dziedzin 3.2 Ustalenie związków i ich typów między encjami 3.3 Klucze kandydujące i główne 3.4 Schemat ER na poziomie konceptualnym 4. Model logiczny 4.1 Charakterystyka modelu relacyjnego 4.2 Usunięcie pól wielowartościowych 4.3 Usunięcie związków typu „wiele do wielu” 4.4 Pułapki szczelinowych i wachlarzowych 4.5 Normalizacja 4.6 Model logiczny 4.7 Więzy integralności 4.8 Denormalizacja 5. Faza fizyczna 5.1 Strojenie bazy danych – dobór indeksów 5.2 Skrypt SQL zakładający bazę danych 5.3 Przykłady transakcji na bazie danych 6. Bibliografia

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

1. Zakres i cel projektu Celem projektu jest zaprojektowanie, na poziomie konceptualnym oraz logicznym, a także fizyczna implementacja relacyjnej bazy danych. Baza danych obsługuje przedsiębiorstwo zajmujące się dostarczaniem telewizji kablowej oraz innych usług z tym związanych na obszarze miasta. Utworzona baza danych będzie oparta o rozwiązanie firmy Oracle. Oprogramowanie użyte podczas realizacji projektu:   

Oracle Database 11g Toad Data Modeler SQL Developer

Założenia funkcjonalne Przedsiębiorstwo sprzedaje usługi (dostarcza): telewizji, Internetu i telefonii. Oferta obejmuje pakiety, w skład których, w zależności od pakietu, wchodzi telewizja, Internet lub telefon oraz kombinacje tych usług. W pakietach może znajdować się różna liczba kanałów, pochodzących od różnych dostawców, Internet może mieć różną przepływność łącza, a w usłudze telefonii stacjonarnej może być różna liczba darmowych minut do sieci komórkowych i połączeń krajowych. Cena pakietu nie musi być arytmetyczną sumą kosztów poszczególnych usług, może być inną wartością, w zależności od polityki fiskalnej i marketingowej firmy. Przedsiębiorstwo prowadzi sieć placówek w różnych lokalizacjach w mieście, w których obsługiwani są klienci. Do placówek mogą, lecz nie muszą, być przypisani poszczególni pracownicy. Firma posiada wykaz dostawców poszczególnych usług. Wyróżniamy trzy grupy zawodowe: konsultanci obsługujący klientów, managerowie oraz pracownicy techniczni. Pracownicy wykonują zlecenia, które mogą być: podłączeniem usługi u klienta, reklamacją, odłączeniem usługi, zmianą usługi. W bazie znajduje się historia wynagrodzeń pracowników, premii i odprowadzanych podatków dochodowych.

2. Definicja systemu 2.1 Perspektywy uż ytkownikow Wyróżniono przykładowe funkcjonalności systemu i uprawnienia grup użytkowników do ich wykonania : Uprawnienie 1. Podgląd danych personalnych pracowników 2. Podgląd danych placówek 3. Modyfikacja/dodawanie/ usuwanie

Właściciel

Technik

Konsultant

Manager

Klient

+

-

-

+

-

+ +

+ -

+ -

+ +

+ -

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16 danych personalnych pracowników 4. Podgląd danych personalnych klientów 5. Modyfikacja/dodawanie/ usuwanie danych personalnych klientów 6. Podgląd wynagrodzenia każdego z pracowników 7. Dodawanie/usuwanie/ modyfikacja wynagrodzenia każdego z pracowników 8. Modyfikacja/dodawanie/ usuwanie danych o placówkach oraz firmie 9. Modyfikacja/dodawanie / usuwanie danych pakietów oraz ich cen 10. Podgląd danych pakietów 11. Uprawnienia do modyfikacji struktury bazy danych 12. Modyfikacja/ dodawanie/usuwanie i podgląd zleceń 13. Modyfikacja/ dodawanie/usuwanie i podgląd danych o dostawcach

+

-

+

+

-

+

-

+

+

-

+

-

-

+

-

+

-

-

+

-

+

-

-

-

-

+

-

-

+

-

+

-

+

+

+

+

-

-

-

-

+

+

+

+

-

+

-

-

+

-

2.2.1 Włas ciciel Ma dostęp do pełnej funkcjonalności systemu, łącznie z modyfikacją struktury bazy.

2.2.2 Technik Ma możliwość dostępu do informacji o placówkach, jest to potrzebne gdy np. musi udać się do placówki w której nie jest nominalnie zatrudniony i nie wie gdzie ona się znajduje. Ponadto ma dostęp do zleceń.

2.2.3 Konsultant Może przeglądać dane o placówkach, przeglądać i modyfikować dane klientów, a także ma wgląd w dostępne pakiety oraz ma dostęp do zleceń, gdyż to on ma bezpośredni kontakt z klientami i przekłada życzenia abonentów na konkretne zlecenia.

2.2.4 Manager Ma dostęp do niemal pełnej funkcjonalności, oprócz modyfikacji struktury bazy danych i danych o placówkach i samej firmie, gdyż nie wchodzi to w zakres jego kompetencji.

2.2.5 Klient Klient może podejrzeć dostępne pakiety oraz dane o placówkach, żeby mógł łatwo trafić do wybranej przez siebie.

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

3. Model konceptualny 3.1 Definicja żbiorow encji okreslonych w projekcie oraż okreslenie atrybutow i ich dżiedżin

Encja Firma Atrybuty tej encji przedstawiają dane dotyczące firmy, jej nazwę, NIP, adres. Nazwa atrybutu

Typ, dziedzina

Id_firma Nazwa

bigInt Varchar(30)

Forma_prawna

VarChar(40)

NIP

char(10)

Miasto

VarChar(50)

Ulica Nr_budynku Nr_lokalu

VarChar(50) VarChar(10) VarChar(10)

Kod_pocztowy Nr_telefonu

Char(6) VarChar(15)

Email

VarChar(30)

Opcjonalny/ obowiązkowy Obowiązkowy Obowiązkowy, bo firma prawnie musi się nazywać Obowiązkowy, bo przepisy prawa Obowiązkowy, bo przepisy Obowiązkowy, bo firma musi mieć gdzieś siedzibę j.w. j.w. Opcjonalny, bo może być samodzielnym budynkiem Obowiązkowy Opcjonalny, bo małe firmy często preferują komunikację elektroniczną Obowiązkowy, bo żyjemy w XXI wieku

Opis/uwagi Klucz główny Nazwa firmy

Oznacza np. czy jest to spółka akcyjna czy komandytowa Numer Identyfikacji Podatkowej, przyporządkowany firmie w celu rozliczeń podatkowych Nazwa miasta, w którym znajduje się firma Nazwa ulicy z siedzibą firmy Nr budynku siedziby firmy Nr lokalu siedziby firmy

Kod pocztowy siedziby firmy Nr telefonu firmy

Adres email do komunikacji elektronicznej z klientami i partnerami biznesowymi

Encja Placowka Ta encja zawiera podstawowe dane dotyczące jednego oddziału (placówki) przede wszystkim dane kontaktowe. Nazwa atrybutu

Typ, dziedzina

Id_placówka

Bigint

Opcjonalny/ obowiązkowy Obowiązkowy, bo taki

Opis/uwagi Identyfikator placówki w bazie

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

Nazwa_placówki Miasto

Varchar(30) VarChar(50)

Ulica Nr_budynku Nr_lokalu

VarChar(50) VarChar(10) VarChar(10)

Kod_pocztowy Nr_telefonu

Char(6) VarChar(15)

Email

VarChar(30)

model bazy danych Obowiązkowy Obowiązkowy Obowiązkowy Obowiązkowy Opcjonalny, bo może być samodzielnym budynkiem Obowiązkowy Opcjonalny, bo małe oddziały często preferują komunikację elektroniczną Obowiązkowy, bo żyjemy w XXI wieku

danych Nazwa placówki Nazwa miasta, w którym znajduje się placówka Nazwa ulicy Nr budynku siedziby placówki Nr lokalu siedziby placówki

Kod pocztowy siedziby placówki Nr telefonu placówki

Adres email do komunikacji elektronicznej z klientami i partnerami biznesowymi

Encja Dostawca W encji Dostawca znajdują się dane kontaktowe dostawcy usług dostępu do Internetu, telefonii i telewizji. Nazwa atrybutu Id_dostawca

Typ, dziedzina Bigint

Opcjonalny/obowiązkowy Obowiązkowy

Nazwa_dostawcy Miasto

Varchar(30) VarChar(50)

Obowiązkowy Obowiązkowy

Ulica Nr_budynku Nr_lokalu

VarChar(50) VarChar(10) VarChar(10)

Kod_pocztowy Nr_telefonu

Char(6) VarChar(15)

Obowiązkowy Obowiązkowy Opcjonalny, bo może być samodzielnym budynkiem Obowiązkowy Opcjonalny, bo nie zawsze znamy taki numer

Opis/uwagi Identyfikator dostawcy w bazie danych Nazwa dostawcy Nazwa miasta, w którym znajduje się siedziba dostawcy Nazwa ulicy Nr budynku siedziby dostawcy Nr lokalu siedziby dostawcy

Kod pocztowy dostawcy Nr telefonu siedziby dostawcy

Encja Pracownik Reprezentuje dane personalne pracownika firmy. Nazwa atrybutu Id_pracownik

Typ, dziedzina Opcjonalny/obowiązkowy Bigint Obowiązkowy

Imię_i_nazwisko Miasto

VarChar(50) VarChar(50)

Obowiązkowy Obowiązkowy

Opis/uwagi Identyfikator pracownika w bazie danych Imię i nazwisko pracownika Nazwa miasta, w którym mieszka

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

Ulica Nr_budynku

VarChar(50) VarChar(10)

Obowiązkowy Obowiązkowy

Nr_lokalu

VarChar(10)

Kod_pocztowy Nr_telefonu

Char(6) VarChar(15)

Opcjonalny, bo może być samodzielnym budynkiem Obowiązkowy Opcjonalny, bo pracownik może go nie mieć

pracownik Nazwa ulicy zamieszkania Nr budynku zamieszkania pracownika Nr lokalu zamieszkania pracownika

Kod pocztowy pracownika Nr telefonu do pracownika

Encja Konsultant Zawiera podstawowe dane związane z osobą pracującą na stanowisku konsultant, która to kontaktuje się z klientami. Nazwa atrybutu

Typ, dziedzina Biurko Integer Znajomość_języka_angielskiego Boolean

Opcjonalny/obowiązkowy

Opis/uwagi

Obowiązkowy Obowiązkowy

Numer biurka Czy konsultant potrafi komunikować się w języku angielskim

Encja Manager Zawiera dane dotyczące osoby pracującej na stanowisku managera. Nazwa atrybutu Zainteresowania Staż Biurko

Typ, dziedzina Varchar(200) Integer Integer

Opcjonalny/obowiązkowy Opcjonany Obowiązkowy Obowiązkowy

Opis/uwagi Wykaz zainteresowań Rok zatrudnienia Numer biurka

Encja Technik Składa się z danych dotyczących pracownika technicznego. Nazwa atrybutu Uprawnienia Badania lekarskie

Typ, dziedzina

Opcjonalny/obowiązkowy

Opis/uwagi

Varchar(200) Integer

Opis uprawnień technika Numer badania lekarskiego

Skrzynka

Integer

Obowiązkowy Opcjonalny, wymagane są tylko dla pracowników mających styczność z zasilaniem energetycznym Opcjonalny, może nosić narzędzia w spodniach

Numer skrzynki narzędziowej

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

Encja Klient Ta encja gromadzi dane klienta oraz informację, jaki pakiet wykupił. Nazwa atrybutu Id_klient Imię_i_nazwisko Miasto

Typ, dziedzina Bigint VarChar(50) VarChar(50)

Opcjonalny/obowiązkowy Obowiązkowy Obowiązkowy Obowiązkowy

Ulica Nr_budynku Nr_lokalu

VarChar(50) VarChar(10) VarChar(10)

Kod_pocztowy Nr_telefonu

Char(6) VarChar(15)

j.w. j.w. Opcjonalny, bo może być samodzielnym budynkiem Obowiązkowy Opcjonalny, bo klient może nie mieć telefonu

Opis/uwagi Identyfikator klienta w bazie danych Imię i nazwisko klienta Nazwa miasta, w którym jest zameldowany klient Nazwa ulicy zameldowania Nr budynku klienta Nr lokalu klienta

Kod pocztowy klienta Nr telefonu klienta

Encja Wynagrodżenie Zawiera dane dotyczące stałego wynagrodzenia przyznawanego pracownikowi, a także wysokości podatku odprowadzanego do Skarbu Państwa. Nazwa atrybutu

Typ, dziedzina Id_wynagrodzenie Bigint

Opcjonalny/obowiązkowy

Opis/uwagi

Obowiązkowy

Pensja

Float

Premia

Float

Podatek

Float

Data

Date

Obowiązkowy, bo każdy dostaje, nawet jeśli 0zł Obowiązkowy, jeśli brak premii wpisujemy 0zł Obowiązkowy, bo zawsze płacimy Obowiązkowy

Identyfikator wynagrodzenia w bazie danych Wysokość pensji Wysokość premii Wysokość podatku od pensji Data przesłania pensji

Encja Zlecenie Gromadzi dane opisujące to, czego dotyczy zlecenie, a także szczegóły rozliczeń pieniężnych i stan realizacji zlecenia. Nazwa atrybutu Id_zlecenie

Typ, dziedzina

Opcjonalny/obowiązkowy

Opis/uwagi

Bigint

Obowiązkowy

Identyfikator zlecenia w bazie danych

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16 Koszt

Integer

Obowiązkowy

Opłata

Integer

Obowiązkowy, jeśli klient nie płaci to wpisujemy 0zł

Prace

VarChar(1000)

Status

VarChar(30)

Obowiązkowy, bo zawsze trzeba wiedzieć czego dotyczy zlecenie Obowiązkowy

Wysokość kosztów operacyjnych ponoszonych przez nas Wysokość opłaty jednorazowej ponoszonej przez klienta za wykonanie zlecenia Opis słowny tego, co powinno być wykonane w zleceniu Aktualny stan zlecenia

Encja Pakiet Zawiera dane odnośnie pakietu (pakietem jest zespół usług telefonii, telewizji i Internetu). Nazwa atrybutu Id_pakiet Koszt

Typ, dziedzina

Opcjonalny/obowiązkowy

Opis/uwagi

Bigint Integer

Obowiązkowy Obowiązkowy

Identyfikator pakietu w bazie danych Wysokość miesięcznej opłaty za pakiet

Encja Internet Gromadzi dane dotyczące danego wariantu usługi Internet, takie jak dopuszczalna prędkość transferu i cena samej usługi Internet. Nazwa atrybutu Id_internet Koszt

Typ, dziedzina

Opcjonalny/obowiązkowy

Opis/uwagi

Bigint Integer

Uplink

Integer

Identyfikator wariantu w bazie danych Wysokość miesięcznej opłaty za usługę Określenie maksymalnej przepustowości od klienta do ISP

Downlink

Integer

FastNight

Boolean

Obowiązkowy Obowiązkowy, bo zawsze kosztuje, nawet jeśli 0zł Opcjonalny, łącza korporacyjne mogą nie mieć limitu Opcjonalny, łącza korporacyjne mogą nie mieć limitu Obowiązkowy

Określenie maksymalnej przepustowości od klienta do ISP Określa dostępność usługi przyśpieszającej transfer danych poza godzinami szczytu

Encja Telefon Składa się z danych dotyczących usługi telefonicznej, określających koszt usługi i jej parametry związane z daną ofertą. Nazwa atrybutu

Typ, dziedzina

Opcjonalny/obowiązkowy

Opis/uwagi

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16 Id_telefon Koszt

Bigint Integer

Krajowe

Integer

Komórkowe

Integer

Poczta

Boolean

Obowiązkowy Obowiązkowy, bo zawsze kosztuje, nawet jeśli 0zł Opcjonalny, najdroższe taryfy mogą nie mieć limitu Opcjonalny, łącza korporacyjne mogą nie mieć limitu Obowiązkowy

VoIP

Boolean

Obowiązkowy

Identyfikator wariantu w bazie danych Wysokość miesięcznej opłaty za usługę Określenie ilości darmowych minut do sieci krajowych stacjonarnych zawartych w cenie pakietu Określenie ilości darmowych minut do sieci krajowych komórkowych zawartych w cenie pakietu Określa, czy w danym planie taryfowym usługa poczty głosowej jest dostępna Określa, czy w danym planie taryfowym użytkownik ma prawo do nielimitowanych połączeń z klientami usługi Voip, np. Skype

Encja Telewiżja Zawiera dane związane z danym kanałem telewizyjnym, potrzebne m.in. do celów organizacyjnych (informacja o koszcie i szyfrowaniu), sprzętowych (częstotliwość). Nazwa atrybutu Id_telewizja Koszt

Typ, dziedzina

Opcjonalny/obowiązkowy

Opis/uwagi

Bigint Integer

Częstotliwość Szyfrowanie

Integer Boolean

HD

Boolean

Obowiązkowy Obowiązkowy, bo zawsze kosztuje, nawet jeśli 0zł Obowiązkowy Obowiązkowy, Booleanowski Obowiązkowy, Booleanowski

Identyfikator wariantu w bazie danych Wysokość miesięcznej opłaty za usługę dostępności kanału Częstotliwość kanału Określa, czy dany kanał jest szyfrowany (kodowany) Określa czy kanał jest w wysokiej rozdzielczości

Encja Podłącżenie_usługi Określa parametry związane z podłączeniem usługi u nowego lub istniejącego klienta, który ponownie zdecydował się na podpisanie umowy. Zawiera między innymi opis prac, dzięki czemu technicy przed wyjazdem mogą zaopatrzyć się w wymagany ekwipunek. Nazwa atrybutu Urządzenie

Typ, dziedzina VarChar(200)

Piętro

Integer

Opcjonalny/obowiązkowy Opis/uwagi Obowiązkowy Określa model routera, dekodera lub innego urządzenia do zainstalowania Opcjonalny Ułatwia technikom trafienie do klienta

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

Encja Reklamacja Gromadzi dane związane z procedurą reklamacyjną, zawierające model urządzenia do wymiany (opcjonalnie) oraz to, jaka usługa jest nieosiągalna Nazwa atrybutu Urządzenie

Typ, dziedzina VarChar(200)

Brak_internetu

Boolean

Brak_telefonu

Boolean

Brak_telewizji

Boolean

Awaria_sprzętowa Boolean

Opcjonalny/obowiązkowy Opis/uwagi Opcjonalny Określa model routera, dekodera lub innego urządzenia które powinno być wymienione Obowiązkowy Jeśli klient zgłasza nieobecność usługi Internet Obowiązkowy Jeśli klient zgłasza nieobecność usługi telefon Obowiązkowy Jeśli klient zgłasza nieobecność usługi telewizja Obowiązkowy Prawdziwy jeśli uszkodzony jest sprzęt

Encja Odłącżenie_usługi Specjalizacja encji Zlecenie, przedstawiająca sytuację, gdy trzeba odłączyć usługę. Nazwa atrybutu Pobór_opłaty

Typ, dziedzina Integer

Piętro

Integer

Na_zlecenie

VarChar(50)

Opcjonalny/obowiązkowy Opis/uwagi Obowiązkowy Ile klient powinien zapłacić technikom za interwencję Opcjonalne Ułatwia technikom trafienie do klienta Obowiązkowy Opis przyczyny zaprzestania świadczenia usług

Encja Zmiana_usługi Nazwa atrybutu Typ

Typ, dziedzina VarChar(10)

Opcjonalny/obowiązkowy Opis/uwagi Obowiązkowy Typ zmiany usługi

Funkcja

VarChar(200)

Obowiązkowy

Powód

VarChar(50)

Obowiązkowy

Nazwa funkcji do zmiany/usunięcia/dodania Opis przyczyny zmiany usługi

3.2 Ustalenie żwiążkow i ich typo w  Relacja FIRMA-PLACÓWKA –zakładamy istnienie jednej firmy, która jednak może mieć wiele placówek-oddziałów, w których klienci mogą zakupywać lub zmieniać usługi i dokonywać reklamacji; początkowo liczba placówek może być równa zero.

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16  Relacja PLACÓWKA-PRACOWNIK – dość podobnie wygląda relacja placówka-pracownik; jedna placówka może zatrudniać wielu pracowników, lub, w momencie jej powstania – żadnego.  Relacja FIRMA-ZLECENIE – ponieważ wykonanie wielu zleceń nie dotyczy konkretnej placówki, a do jego wykonania może być potrzebna interwencja różnych pracowników (wielu), sensowne jest przypisanie zlecenia do firmy. W szczególności możliwe jest, że do firmy nie ma przypisanych żadnych zleceń.  Relacja FIRMA-KLIENT – klient zostaje w tej relacji przypisany do firmy. Takie rozwiązanie jest elastyczniejsze niż przypisanie klienta do placówki, dzięki czemu unika się niebezpieczeństwa pułapki szczelinowej. W szczególności firma, na przykład na początku istnienia, może nie mieć żadnych klientów.  Relacja FIRMA-PAKIET – zakładamy, że firma oferuje pewien wachlarz pakietów, taki sam w każdej placówce. Nie da się różnicować oferty w ramach pojedynczych placówek. W szczególności liczba pakietów może wynieść 0.  Relacja FIRMA-PRACOWNIK – rozwiązanie to pozwala na uniknięcie pułapki polegającej na tym, że w razie braku istnienia tej relacji, pracownik musiałby być przyporządkowany do konkretnej placówki. Pracownicy mogą wykonywać swoją pracę w kilku placówkach, a także możliwa jest sytuacja taka, że firma nie ma żadnej placówki terenowej.  Relacja FIRMA-DOSTAWCA – jest reprezentacją umowy łączącej firmę z dostawcami usług. Możliwe jest, żeby na początku działalności liczba dostawców była równa 0.  Relacja PAKIET-INTERNET – reprezentuje wariant usługi Internet przypisany do pakietu. W szczególności może nie być przypisany żaden (usługa telefon lub telefon+TV lub samo TV czy sama telefonia).  Relacja PAKIET-TELEFON – reprezentuje wariant usługi telefonicznej zawarty w pakiecie. Może nie być żadnego (klient chce tylko TV i/lub internet).  Relacja PAKIET-TELEWIZJA – odpowiada przypisaniu odpowiednich grup kanałów do danego pakietu. W szczególności może reprezentować zbiór pusty (klient chce wariant bez TV).  Relacja PRACOWNIK-WYNAGRODZENIE – przedstawia wynagrodzenie i szczegóły rozliczenia (jak podatek) przypisane do danego pracownika.  Relacja DOSTAWCA-TELEWIZJA – przedstawia zbiór kanałów telewizyjnych, które są zapewniane przez danego dostawcę. W szczególności może być brak kanałów (dostawca innych usług niż TV).  Relacja DOSTAWCA-TELEFON – reprezentuje powiązanie dostawcy z dostarczanymi przez niego wariantami usługi telefonii. Może nie być przyporządkowany ani jeden wariant telefonu do dostawcy, jeśli dostawca jest dostawcą treści telewizyjnych lub jest ISP.  Relacja DOSTAWCA-INTERNET – zapewnia odwzorowanie przyporządkowania dostawcy zewnętrznego do danego wariantu usługi Internet Relacje n do n, do „zbrydżowania”:  Relacja PAKIET-TELEWIZJA  Relacja ZLECENIE-PRACOWNIK

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

Tabela określająca typy relacji i ich krotności: Relacja FIRMA-PLACÓWKA PLACÓWKA-PRACOWNIK FIRMA-ZLECENIE FIRMA-KLIENT FIRMA-PAKIET FIRMA-PRACOWNIK FIRMA-DOSTAWCA PAKIET-INTERNET PAKIET-TELEFON PAKIET- TELEWIZJA PRACOWNIKWYNAGRODZENIE PRACOWNIK-ZLECENIE DOSTAWCA-TELEWIZJA DOSTAWCA-TELEFON DOSTAWCA-INTERNET

Typ związku 1:n 1:n 1:n 1:n 1:n 1:n 1:n n:1 n:1 n:n 1:n

Typ uczestnictwa

Stopień związku

obowiązkowa-opcjonalna opcjonalna -opcjonalna obowiązkowa-opcjonalna obowiązkowa-opcjonalna obowiązkowa-opcjonalna obowiązkowa-opcjonalna obowiązkowa-opcjonalna opcjonalna-opcjonalna opcjonalna -opcjonalna opcjonalna -opcjonalna obowiązkowa-opcjonalna

binarny binarny binarny binarny binarny binarny binarny binarny binarny binarny binarny

n:n 1:n 1:n 1:n

opcjonalna -opcjonalna obowiązkowa-opcjonalna obowiązkowa-opcjonalna obowiązkowa-opcjonalna

binarny binarny binarny binarny

3.3 Klucże kandydujące i głowne Kluczem głównym w każdej z wymienionych encji jest sztuczny klucz ID. Klucze kandydujące przedstawia tabela. Nazwa encji Firma Placówka Dostawca Pracownik Klient Wynagrodzenie Zlecenie Pakiet Internet Telefon Telewizja

Potencjalny klucz główny NIP, nazwa Nazwa Nazwa Częstotliwość

3.4 Schemat ER na pożiomie konceptualnym Schemat konceptualny ER znajduje się w załączniku nr. 1.

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

4. MODEL LOGICZNY 4.1 Charakterystyka modelu relacyjnego W celu wygenerowania modelu logicznego musieliśmy usunąć niekompatybilności modelu konceptualnego – związki wiele do wielu. Mieliśmy dwie takie sytuacje w naszym projekcie. Ponadto usunęliśmy pola wielowartościowe Imie_i_nazwisko.

4.2 Usunięcie po l wielowartos ciowych Przy przejściu z modelu konceptualnego na logiczny usunęliśmy pola segmentowe Imię_nazwisko, występujące w encjach Klient i Pracownik. Nazwa

Typ Zamieniono na:

Imię_i_nazwisko

Varchar(50)

Nazwa Imię Nazwisko

Typ Varchar2(20) Varchar2(30)

4.3 Usunięcie żwiążkow typu „wiele do wielu” W modelu mieliśmy dwa związki „wiele do wielu”: Pakiet <-> Telewizja, gdyż jeden pakiet może zawierać wiele kanałów telewizyjnych od wielu dostawców, a te same kanały telewizyjne mogą znaleźć się w wielu pakietach oraz Zlecenie <-> Pracownik, ponieważ jeden pracownik może wykonywać wiele zleceń, a także wielu pracowników może wykonywać jedno zlecenie. W tym celu dodaliśmy dwie nowe tabele: Zlecenie_do_pracownik oraz TV_do_pakiet.

Pakiet <-> Telewizja

Powstały dwa nowe związki „jeden do wielu” z tą tabelą: (Pakiet) Zawiera_TV i (Telewizja) jest_zawarta_w_pakiecie

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

Zlecenie <-> Pracownik

Powstały dwa nowe związki „jeden do wielu” z tą tabelą: (Zlecenie) Jest_wykonywane i (Pracownik) wykonuje

4.4 Pułapki wachlarżowe i sżcżelinowe Przykładowe pułapki szczelinowe: Firma nie mogła mieć klientów, jeśli nie posiadała żadnych oddziałów (a możemy łatwo wyobrazić sobie firmę bez oddziałów terenowych). Żeby tego uniknąć powiązaliśmy klientów bezpośrednio z firmą. Utworzyliśmy związek Posiada_klientów wiążący encję Klient z encją Firma. Przykładowe pułapki wachlarzowe: Encja Firma jest powiązana z encją Pracownik związkiem Zatrudnia. Ponadto encja Firma Posiada Placówki. Mimo że mamy pracowników i placówki, to nie mogliśmy stwierdzić, w jakiej placówce pracuje pracownik. Dlatego stworzyliśmy związek Pracuje_w_niej łączący pracownika z placówką.

4.5 Normaliżacja W celu normalizacji:    

Pole Imie_i_nazwisko musieliśmy rozbić na pola Imie i Nazwisko w sposób opisany wyżej Atrybut kod_pocztowy ze wszystkich encji przenieśliśmy do osobnej tabeli Atrybut Wynagrodzenie z encji Pracownik przenieśliśmy do osobnej tabeli Wynagrodzenia Pole wyliczeniowe Języki w specjalizacji Konsultanci encji Pracownicy zastąpiliśmy polem Znajomość_języka_angielskiego

4.6 Schemat logicżny Schemat logiczny został załączony jako załącznik nr 2.

4.7 Więży integralnos ci Zadbaliśmy żeby wszystkie pola były polami atomowymi. Wszystkie klucze oznaczone są jako UNIQUE. Wszystkie pola oznaczone są jako NOT_NULL, oprócz tych, które nie mają dużego znaczenia pod kątem integralności i przydatności bazy danych. Zapobiega to niewłaściwym i błędnym modyfikacjom bazy danych.

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

4.8 Denormaliżacja Nie stwierdziliśmy możliwości denormalizacji bazy danych, która w znaczący sposób przyśpieszyłaby dostęp do niej.

5 Faża fiżycżna 5.1 Strojenie baży – dobo r indeksow Przewidujemy najczęściej zadawane zapytania do bazy, w związku z czym zakładamy indeksy na bazie.

5.1.1 Wyszukiwanie pakietów CREATE INDEX idx_pakiety ON Pakiety(Id_pakiet)

5.1.2 Wyszukiwanie klientów w danym mieście CREATE INDEX idx_miasto_klienci ON Klienci(Miasto)

5.1.3 Wysżukiwanie żleceń wg statusu CREATE INDEX idx_status_zlecenia ON Zlecenia(Status)

5.1.4 Wysżukiwanie placówek w danym mieście CREATE INDEX idx_miasto_placówki ON Placówki(Miasto)

5.1.5 Wysżukiwanie usług telewiżyjnych w jakości HD CREATE INDEX idx_HD_telewizje ON Telewizje(HD)

5.2 Skrypt żakładający bażę Skrypt uzyskany z programu Toad Data Modeler został zamieszczony w załączniku nr 3

5.3 Prżykłady transakcji na bażie danych 5.3.1 Wylistowanie wykonywanych żleceń prżeż danego pracownika SELECT Pracownicy.Imię, Pracownicy.Nazwisko, Zlecenia.Status, Zlecenia.Koszt FROM Pracownicy JOIN Zlecenie_do_Pracownik ON Zlecenie_do_Pracownik .Id_pracownik = Pracownicy.Id_pracownik JOIN Zlecenia ON Zlecenia.Id_zlecenie = Zlecenie_do_Pracownik.Id_zlecenie WHERE Pracownicy.Nazwisko = ‘Lalacki’

Dominik Januszewicz & Jacek Jabłoński, zima 2015/16

5.3.2 Wylistowanie pracowników danej placówki SELECT Pracownicy.Imię, Pracownicy.Nazwisko, Pracownicy.Miasto FROM Placówki JOIN Pracownicy ON Placówki .Id_placówka = Pracownicy.Id_placówka WHERE Placówki.Nazwa_placówki = ‘Oddział 1’

5.3.3 Wylistowanie abonentów z usługą pocżty głosowej SELECT Klienci.Imię, Klienci.Nazwisko, Klienci.Miasto FROM Telefony JOIN Pakiety ON Pakiety.Id_telefon = Telefon.Id_telefon JOIN Klienci ON Pakiety.Id_pakiet = Klienci.Id_pakiet WHERE Telefony.Poczta = ‘1’

5.3.4 Sprawdzenie jacy dostawcy dostarcżają Internet do posżcżególnych pakietów SELECT Dostawcy.Nazwa_dostawcy, Dostawcy.Miasto FROM Pakiety JOIN Internety ON Pakiety.Id_internet = Internety.Id_internet JOIN Dostawcy ON Internety.Id_dostawca = Dostawca.Id_dostawca

6 Bibliografia Slajdy wykładowe do przedmiotu „Wstęp do baz danych” autorstwa dr inż. Marcina Kowalczyka