Projektowanie baz danych

Question 1

Projektowanie baz danych

Załóżmy, że w tabeli Towary są kolumny IdTowaru (INT PRIMARY KEY), Nazwa (NVARCHAR(50) NOT NULL), IdKategorii (INT NOT NULL), Cena (MONEY NOT NULL). Proszę napisać zdanie w języku SQL, które: (1) wyświetli średnią cenę towarów w kategoriach, (2) przy czym średnie ceny mają być obliczone nie wśród wszystkich towarów, lecz tylko tych, których cena jest większa niż 50. (3) Ponadto interesują nas tylko te kategorie, w których wyliczona średnia jest większa niż 70 (kategorie nie spełniające tego warunku nie mają się znaleźć w zestawieniu). (4) W wyniku chcemy widzieć dwie kolumny, o nazwach odpowiednio IdKategorii oraz ŚredniaCena. (5) Wynik ma być posortowany malejąco według średnich cen.

Answer 1

SELECT IdKategorii, AVG(Cena) AS ŚredniaCena
FROM Towary
WHERE Cena > 50
GROUP BY IdKategorii
HAVING AVG(Cena) > 70
ORDER BY ŚredniaCena DESC;

To zapytanie SQL najpierw filtruje towary o cenie większej niż 50, następnie grupuje wyniki według IdKategorii i oblicza średnią cenę (AVG(Cena)). Następnie zwraca tylko te kategorie, gdzie średnia cena jest większa niż 70 (HAVING AVG(Cena) > 70). Wyniki są posortowane malejąco według średniej ceny.

Question 2

Projektowanie baz danych

Proszę omówić następujące klauzule w języku SQL: SELECT, ORDER BY, GROUP BY, WHERE, HAVING, FROM (z operatorami JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN). Należy podać prawidłową kolejność tych klauzul. Dodatkowo proszę podać kryterium WHERE, które wyświetli te rekordy z tabeli Pracownicy, w których nie wpisano żadnej wartości do kolumny Telefon.

Answer 2

• SELECT: Używana do wybierania kolumn, które mają zostać wyświetlone w wynikowym zestawie danych.
• FROM: Określa źródło tabel lub widoków, z których mają być pobierane dane.
• JOIN / LEFT JOIN / RIGHT JOIN / FULL JOIN: Operatory stosowane do łączenia tabel na podstawie wspólnych kolumn.
• WHERE: Filtruje wyniki na podstawie określonych warunków.
• GROUP BY: Grupuje wyniki na podstawie jednej lub więcej kolumn.
• HAVING: Filtruje grupy utworzone przez GROUP BY na podstawie określonego warunku (używane tam, gdzie
• WHERE nie może być stosowane do agregatów).
• ORDER BY: Sortuje wynikowe dane według jednej lub więcej kolumn.

Prawidłowa kolejność użycia tych klauzul: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY.

Kryterium WHERE dla tabeli Pracownicy i kolumny Telefon:
SELECT * FROM Pracownicy WHERE Telefon IS NULL;

Question 3

Projektowanie baz danych

Proszę scharakteryzować transakcje w bazach danych i podać przykład, w którym uzasadnione będzie utworzenie transakcji zawierającej dwie instrukcje SQL.

Answer 3

Transakcja w bazach danych to sekwencja operacji, które razem formują pojedynczą, logiczną jednostkę pracy. Transakcje są zarządzane przez ACID (Atomicity, Consistency, Isolation, Durability), co oznacza, że muszą być atomowe, spójne, izolowane od innych transakcji i trwałe.

Przykład transakcji: Przy przelewie bankowym musimy odjąć pewną kwotę z jednego konta i dodać ją do drugiego. Obie te operacje muszą być wykonane razem, aby uniknąć niespójności:
~~~
BEGIN TRANSACTION;
UPDATE Konta SET saldo = saldo - 100 WHERE NrKonta = ‘123’;
UPDATE Konta SET saldo = saldo + 100 WHERE NrKonta = ‘456’;
COMMIT;
~~~

Question 4

Projektowanie baz danych

Załóżmy, że w bazie danych są tabele: Lekarze (NrLekarza INT PRIMARY KEY, Nazwisko NVARCHAR(50) NOT NULL, Imię NVARCHAR(50) NOT NULL) oraz Pacjenci(NrPacjenta INT PRIMARY KEY, Nazwisko NVARCHAR(50) NOT NULL, Imię NVARCHAR(50) NOT NULL, NrLekarzaProwadzącego REFERENCES Lekarze(NrLekarza) NULL). Proszę napisać zdanie SQL, które wyświetli wszystkich lekarzy (i TYLKO tych), którzy nie mają jeszcze przypisanego żadnego pacjenta. Wariant a) Proszę w sposób istotny wykorzystać operator NOT IN; Wariant b) Proszę w sposób istotny wykorzystać operator LEFT JOIN.

Answer 4

Wariant a) z NOT IN:
~~~
SELECT NrLekarza, Nazwisko, Imię FROM Lekarze
WHERE NrLekarza NOT IN (SELECT NrLekarzaProwadzącego FROM Pacjenci WHERE NrLekarzaProwadzącego IS NOT NULL);
~~~

Wariant b) z LEFT JOIN:
~~~
SELECT l.NrLekarza, l.Nazwisko, l.Imię
FROM Lekarze l
LEFT JOIN Pacjenci p ON l.NrLekarza = p.NrLekarzaProwadzącego
WHERE p.NrLekarzaProwadzącego IS NULL;
~~~

Question 5

Projektowanie baz danych

Proszę podać podstawowe zasady projektowania tabel w relacyjnych systemach baz danych. Proszę podać przykład tabeli zawierającej redundancję i proszę tę tabelę znormalizować. W wyniku powinniśmy uzyskać dwie tabele, w jednej z nich będzie zdefiniowany klucz obcy, odwołujący się do drugiej.

Answer 5

Podstawowe zasady projektowania tabel to:
* Unikanie redundancji: Dane nie powinny się powtarzać w wielu miejscach bazy danych.
* Używanie kluczy głównych: Każda tabela powinna mieć klucz główny, jednoznacznie identyfikujący każdy wiersz.
* Integralność danych: Dane w tabelach powinny być spójne i pełne.
* Normalizacja danych: Proces projektowania tabel w taki sposób, aby zminimalizować redundancję i zależności.

Przykład normalizacji:

Tabela Zamowienia zawierająca kolumny: NrZamowienia, DataZamowienia, NazwaProduktu, CenaProduktu.

Redundancję eliminujemy przez podzielenie danych na dwie tabeli:

Tabela Zamowienia: NrZamowienia (PK), DataZamowienia, NrProduktu (FK)

Tabela Produkty: NrProduktu (PK), NazwaProduktu, CenaProduktu

NrProduktu w tabeli Zamowienia jest kluczem obcym, który odnosi się do tabeli Produkty.