Projektowanie baz danych

Question 1

Tabela KURSY ma kolumny: id_kursu (klucz podstawowy), nazwa_kursu oraz uczestnicy, w której zapisano wartość 'Nowak, Kowalski, Wisniewska'. Którą postać normalną narusza taka tabela?

Accepted Answer

Pierwsza postać normalna wymaga atomowości komórki: jedno pole to jedna wartość. Lista nazwisk uniemożliwia zapytanie o pojedynczego uczestnika, więc sprawdzanie dalszych postaci jest bezcelowe. Odpowiedź o 2NF kusi, ale naruszenie 2NF występuje wyłącznie przy kluczu złożonym, a tu klucz jest jednokolumnowy.

Question 2

Tabela POZYCJE_ZAMOWIEN ma klucz podstawowy złożony (id_zamowienia, id_produktu) oraz kolumny nazwa_produktu, cena_katalogowa i ilosc. Nazwa produktu i cena katalogowa wynikają wyłącznie z id_produktu. Która postać normalna jest naruszona?

Accepted Answer

To podręcznikowa zależność częściowa: znając samo id_produktu, znasz nazwę i cenę, bez patrzenia na numer zamówienia. Naprawa polega na wyprowadzeniu tych kolumn do tabeli PRODUKTY, przy czym kolumna ilosc słusznie zostaje, bo zależy od obu części klucza. Wskazanie 3NF myli zależność częściową z przechodnią.

Question 3

Tabela UCZNIOWIE ma klucz podstawowy id_ucznia oraz kolumny nazwisko, id_klasy, nazwa_klasy i wychowawca, przy czym nazwa klasy oraz wychowawca wynikają z id_klasy. Jaki jest stan normalizacji tej tabeli?

Accepted Answer

Wszystkie komórki są atomowe, a klucz jednokolumnowy daje 2NF z automatu. Łamie się dopiero 3NF: wychowawca zależy od id_klasy, które nie jest kluczem tej tabeli, więc dane wychowawcy powielają się przy każdym uczniu. Odpowiedź o 3NF kusi, bo uczniowie mylą sam brak klucza złożonego z pełną normalizacją.

Question 4

Tabele PRODUKTY i ZAMOWIENIA łączy związek wiele-do-wielu. Ile tabel i jakie klucze są potrzebne, aby zapisać go poprawnie w modelu relacyjnym?

Accepted Answer

Model relacyjny nie potrafi zapisać N:M wprost, dlatego dokłada się tabelę łączącą, która rozbija związek na dwie relacje jeden-do-wielu i przechowuje atrybuty samego powiązania, np. ilość. Wariant z jednym kluczem obcym w tabeli produktów pozwoliłby przypisać produkt tylko do jednego zamówienia, a UNIQUE realizuje związek 1:1.

Question 5

Kolumna id_klienta w tabeli wypozyczenia jest kluczem obcym wskazującym na klienci(id_klienta), silnik InnoDB. Co zrobi serwer przy próbie dodania wypożyczenia z wartością id_klienta równą 77, gdy w tabeli klienci nie ma takiego numeru?

Accepted Answer

Integralność referencyjna wymaga, aby wartość klucza obcego wskazywała na istniejący wiersz tabeli nadrzędnej albo była równa NULL. Wartość 77 nie spełnia żadnego z tych warunków, więc InnoDB przerywa operację błędem. Wiara w automatyczne utworzenie rekordu nadrzędnego to częsty błąd: klucz obcy tylko sprawdza dane, nigdy ich nie dopisuje.

Question 6

Klucz obcy id_wypozyczenia w tabeli pozycje_wypozyczenia zdefiniowano z akcją ON DELETE CASCADE. Operator usuwa jeden wiersz z tabeli wypozyczenia. Jaki będzie skutek?

Accepted Answer

CASCADE przenosi operację na rekordy podrzędne, więc kasowanie wypożyczenia zabiera ze sobą jego pozycje. Stosuje się je tam, gdzie rekord podrzędny nie ma sensu bez nadrzędnego. Blokada to zachowanie RESTRICT, a wstawienie NULL to SET NULL, które i tak jest tu niemożliwe, bo kolumna wchodzi w skład klucza podstawowego.

Question 7

Projektant chce, aby po usunięciu reżysera jego filmy pozostały w bazie, tylko bez przypisanego twórcy. Jak musi wyglądać kolumna id_rezysera w tabeli filmy i jej akcja referencyjna?

Accepted Answer

SET NULL zeruje powiązanie, zostawiając sam rekord podrzędny, ale wymaga kolumny przyjmującej NULL, więc zestawienie tej akcji z NOT NULL jest sprzeczne i baza odrzuci taką definicję. RESTRICT w ogóle nie pozwoliłby skasować reżysera, a CASCADE usunąłby jego filmy, czyli zrobiłby dokładnie odwrotnie niż wymaga zadanie.

Question 8

Które typy MySQL dobrać kolejno do kolumn pesel, cena_brutto i czy_oplacone w tabeli rozliczeń?

Accepted Answer

PESEL ma zawsze 11 znaków i bywa z wiodącym zerem, więc typ liczbowy je gubi, a stała długość przemawia za CHAR zamiast VARCHAR. Kwoty wymagają typu dokładnego, bo FLOAT zapisuje wartości w przybliżeniu i sumy przestają się zgadzać o grosze. Flaga to BOOLEAN, czyli w MySQL alias jednobajtowego TINYINT(1).

Question 9

W której sytuacji założenie dodatkowego indeksu najprawdopodobniej pogorszy pracę bazy zamiast ją przyspieszyć?

Accepted Answer

Indeks opłaca się przy wysokiej selektywności i przewadze odczytów. Kolumna o trzech wartościach niczego nie zawęża, więc optymalizator i tak wybierze pełne przeglądanie, a każdy INSERT, UPDATE i DELETE musi dodatkowo odświeżyć strukturę indeksu. Pozostałe warianty opisują wzorce, dla których indeks powstaje właśnie po to, by pomóc.

Question 10

Nieznormalizowana tabela EWIDENCJA trzyma w jednym wierszu dane wypożyczenia i dane klienta. Pracownik kasuje jedyne wypożyczenie Piotra Kowalskiego i bezpowrotnie traci jego numer telefonu. Jak nazywa się ten problem?

Accepted Answer

Anomalia usuwania polega na utracie informacji, której nikt nie kazał kasować, bo dwa niezależne fakty dzielą jeden wiersz. Anomalia wstawiania blokuje dodanie klienta bez wypożyczenia, a aktualizacji prowadzi do sprzecznych kopii tej samej danej. Lekarstwem na wszystkie trzy jest rozbicie tabeli na KLIENCI i WYPOZYCZENIA.

To nie koniec powtórki