Mikrozadanie 9

back

Treść

Zaproponuj zestaw tabel realizujący poniższy diagram UML z uwzględnieniem następujących dodatkowych informacji.

• Jako pośrednik może wystąpić wyłącznie studio, z którym artysta ma podpisany cyrograf na wyłączność na całe życie. Jeśli artysta ma podpisany taki cyrograf z jakimś studiem, to jest ono pośrednikiem w każdym kontrakcie artysty. Za realizacje kontraktu z innym studiem artysta musi wypłacić prowizję, ujętą w cyrografie.

• Wszystkie kontrakty dotyczące jednego nagrania są realizowane w jednym studiu.

Tabele nie muszą dokładnie odpowiadać diagramowi, ale nie mogą gubić informacji; więzy (UNIQUE, REFERENCES, itp.) pełnią ważną rolę. Jeśli to możliwe bez utraty zależności funkcyjnych, to należy zapewnić, żeby tabele były w postaci BCNF. Jeśli to niemożliwe (uzasadnić), to powinny być w 3NF.

Wzorcówka

Treść wspomina o Cyrografie, który jest relacją między Autorem a Studiem i ma związaną ze sobą prowizję. Powinna być encją, więc najpierw modyfikujemy diagram pojęciowy.

Wypisujemy sobie zależności funkcyjne:

• Kontrakt $\rightarrow$ Artysta, Nagranie, rola, poczatek, koniec, wynagrodzenie
• Artysta $\rightarrow$ Cyrograf, imie, nazwisko
• Cyrograf $\rightarrow$ Artysta, StudioBiorąceDuszę, prowizja
• Nagranie $\rightarrow$ StudioNagrywające, tytul, gatunek
• Studio $\rightarrow$ nazwa, adres

I tworzymy schemat:

-- {Artysta -> imie, nazwisko}
CREATE TABLE Artysta (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	imie VARCHAR(255) NOT NULL,
	nazwisko VARCHAR(255) NOT NULL
)

-- {Nagranie -> StudioNagrywające, tytul, gatunek}
CREATE TABLE Nagranie (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	tytul VARCHAR(255) NOT NULL,
	gatunek VARCHAR(255) NOT NULL,
	id_studio INTEGER NOT NULL REFERENCES Studio -- realizuje kontrakty
)

-- {Studio -> nazwa, adres}
CREATE TABLE Studio (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	nazwa VARCHAR(255) NOT NULL,
	adres VARCHAR(1023) NOT NULL
)

-- {Artysta -> Studio, prowizja}
CREATE TABLE Cyrograf (
	id_artysta INTEGER NOT NULL REFERENCES Artysta PRIMARY KEY, -- podpisuje
	id_studio INTEGER NOT NULL REFERENCES Studio -- zdobywa duszę
	prowizja DECIMAL NOT NULL,
)

-- {Kontrakt -> Artysta, Nagranie, rola, poczatek, koniec, wynagrodzenie}
CREATE TABLE Kontrakt (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	rola VARCHAR(255) NOT NULL,
	poczatek DATE NOT NULL,
	koniec DATE NOT NULL,
	wynagrodzenie DECIMAL NOT NULL,
	id_artysta INTEGER NOT NULL REFERENCES Artysta, -- realizuje
	id_nagranie INTEGER NOT NULL REFERENCES Nagranie -- dotyczy
)

Zależności funkcyjne w danej tabeli są wypisane w komentarzu. We wszystkich przypadkach jest to po prostu zależność od klucza do reszty tabeli, a więc tabele są w BCNF.

Uwagi

1. Największą pułapką jest relacja pośredniczenia. Musi ona zostać zawarta w relacji Nagranie, a nie Kontrakt, bo w przeciwnym wypadku narusza 3NF: istnieje zależność Artysta $\rightarrow$ Pośrednik.
2. Treść wyraźnie mówi, że prowizja jest związana z Cyrografem: “Za realizacje kontraktu z innym studiem artysta musi wypłacić prowizję, ujętą w cyrografie.” To, że na diagramie jest ona w Kontrakcie, to zmyłka.
3. Kilka osób nie tworzyło oddzielnej tabeli z Cyrografem, tylko inline’owała te dane w tabeli Artysta, dodając kolumny id_pośrednika i prowizja. Nie narusza to BCNF, ale jest nieeleganckie. Przede wszystkim trzeba zadbać o to, że id_pośrednika jest NULLem wtedy i tylko wtedy, gdy prowizja też jest NULLem. O wiele ładniej zrobić drugą tabelę. Nie jest to zależność funkcyjna, więc nie psuje postaci normalnych, ale jest brzydkie.
4. Rozwiązanie można uzyskać na dwa sposoby: myśląc, albo licząc. Rozwiązanie z myśleniem jest prostsze – dzielimy schemat tak, żeby różne obiekty były w różnych tabelach, nie duplikujemy informacji i z reguły wychodzi coś sensownego. Potem wystarczy tylko powiedzieć, że tabele są w BCNF. Jeśli ktoś nie lubi myśleć, a lubi liczyć, to można odpalić algorytm na BCNF.
5. Większość tabel, które są na tyle ważne, że pojawiają się na diagramie pojęciowym, powinno mieć klucz główny w postaci liczbowego identyfikatora. Czasami istnieje naturalny klucz wynikający z modelu, który będzie dobry, ale nie zawsze. Klucz powinien być mały i najlepiej zawsze rosnący z kolejnymi rekordami wkładanymi do bazy. Adres studia to bardzo zły klucz. To będzie lepiej wytłumaczone, gdy będziemy mówić o indeksach.

back