bd-lab2

Instrukcja do przedmiotu Bazy danych – laboratorium (zajęcia 2)

Na poprzednich zajęciach poznaliśmy podstawy korzystania z SQL Server 2005 za pomocą SQL Server

Management Studio. W tej chwili powinniście już umieć korzystać z Eksploratora Obiektów w celu wyświetlania

dostępnych baz danych, wyświetlania i przeglądania tabel znajdujących się w bazie itp. Dzisiejsze zajęcia

rozpoczniemy od mocnego uderzenia (utworzenie nowej bazy danych), a później napięcie będzie tylko rosło

(utworzenie tabeli i wypełnienie jej danymi).

Polecenie 2.1: Uruchom SQL Server Management Studio i zaloguj się do serwera.

WAŻNE!

Przypominam, że maszyna, na której pracujecie, wykorzystywana jest przez kilka grup, a wszystkie utworzone

w niej obiekty znajdują się na lokalnym dysku twardym i działają w tej samej instancji serwera. Dla świętego

spokoju (a konkretniej, żeby uniknąć konfliktu nazw), do nazw wybranych obiektów tworzonych w SQL Server

należy dodawać przyrostek określający numer sekcji osoby, która ten obiekt utworzyła. W dalszej części tekstu

możecie natknąć się na polecenie typu "utwórz obiekt o nazwie jakasnazwa_z2XsY". Za każdym razem w

nazwie tworzonego obiektu należy podstawić pod X numer grupy, a pod Y numer sekcji. Dla naszego przykładu

dla osób z grupy Z25s1 nazwa obiektów powinna brzmieć jakasnazwa_z25s1.Przeczytaj uważnie poniższą

instrukcję i wykonaj wszystkie zawarte w niej polecenia.

Tworzenie bazy danych

Polecenie 2.2: Kliknij prawym przyciskiem myszy na gałąź Databases w Eksploratorze Obiektów i wybierz New
Database...

Po chwili powinno pojawić się okno dialogowe, w którym możemy ustalić parametry tworzonej bazy danych.
Omówimy pokrótce kilka z nich.

Database name: nazwa bazy (możliwe jest stosowanie w nazwie polskich znaków, ale zdecydowanie zaleca się ich
unikania)

Owner: właściciel bazy (domyślnie właścicielem jest twórca bazy, ale można to zmienić)

Poniżej znajdują się charakterystyki plików, z jakich składa się baza danych. Domyślnie, każda baza składa się z
dwóch plików. W pliku bazy danych przechowywane są wszystkie zatwierdzone dane. W pliku dziennika (tzw.

Instrukcja do przedmiotu Bazy danych – laboratorium (zajęcia 2)

log) przechowywane są wszystkie bieżące, niezatwierdzone zmiany (pamiętacie czerwone wykrzykniki pojawiające
się przy wprowadzaniu danych? – oznaczały one, że zmiany nie zostały jeszcze wprowadzone). Takie rozwiązanie
pozwala przy pomyłce lub jakiejkolwiek awarii na wycofanie wszystkich zmian, które miały miejsce, a nie powinny
być może zostać zapisane w bazie.
Ma  to  związek  z  tzw.  transakcjami  (transakcja  to  niepodzielny  ciąg  operacji  –  jeśli  jakieś  operacje  tworzą
transakcję, tzn. że wykonają się albo wszystkie, albo żadna z nich, innej możliwości nie ma). Jako przykład może
posłużyć  transfer  jakiejś  kwoty  z  jednego  konta  na  drugie.  W  takim  przypadku  należy  wykonać  (w  dużym
uproszczeniu) dwie operacje: zmienić stan konta, z którego dokonuje się transfer oraz dokonać aktualizacji stanu
konta, na które wpływają pieniądze. W momencie wystąpienia awarii, po wykonaniu pierwszej operacji a przed
wykonaniem drugiej, zmiany wynikające z tej pierwszej powinny być wycofane.

Opcje związane z plikami:
Logical Name: logiczne nazwy plików, uzupełniane są automatycznie po wpisaniu nazwy bazy danych, choć można
je później zmienić

File Type: typ pliku

File Group: istotne w przypadku, gdy chcemy, aby dane przechowywane były w kilku plikach (np. w celu
zwiększenia szybkości wyszukiwania), można wtedy podzielić te pliki na grupy plików podstawowych (PRIMARY)
lub drugorzędnych (SECONDARY); w SQL Server 2005 istnieje zawsze tylko jedna podstawowa grupa plików, grup
plików drugorzędnych można zdefiniować aż 255

Initial Size: początkowy rozmiar plików

Autogrowth:  sposób  przyrostu  wielkości  plików  (jeśli  początkowe  wielkości  okażą  się  niewystarczające);  istnieją
dwa sposoby, w jaki SQL Server może powiększać pliki bazy danych: o stałą wartość (np. zawsze o 10 MB) lub o
wartość  procentową  (np.  o  20%  aktualnego  rozmiaru);  możliwe  jest  również  wyłączenie  możliwości  przyrostu
wielkości  plików  (należy  odznaczyć  opcję  Enable  Autogrowth)  lub  ograniczenie  maksymalnego  rozmiaru  pliku
(zaznaczenie opcji Restricted File Growth i wpisanie maksymalnej wartości w MB)

Path: miejsce, w którym będą przechowywane pliki bazy danych

File name: rzeczywista nazwa pliku na dysku twardym (parametr opcjonalny)

Menu po lewej stronie okna dialogowego pozwala na dostęp do wielu innych, bardziej szczegółowych opcji bazy
danych oraz na tworzenie nowych grup plików. Wszystko to może być jednak zmienione już po utworzeniu samej
bazy.

Polecenie 2.3: Utwórz bazę danych o nazwie test_z2XsY. Początkowy rozmiar pliku bazy danych ustal na 3 MB.
Dodatkowo zmień sposób przyrostu wielkości pliku dziennika na stałą wartość równą 1 MB oraz ogranicz
maksymalny rozmiar wielkości plików do odpowiednio: plik bazy - 200 MB, plik dziennika - 10 MB. W
pozostałych opcjach zostaw wartości domyślne.

Jeśli nie pojawią się żadne nieprzewidziane trudności w oknie Eksploratora Obiektów powinien pojawić się efekt
twoich działań.

Gratuluję! Utworzyłeś/aś swoją pierwszą bazę danych. Przygoda właśnie się zaczyna...

Baza danych bez danych jest jak student bez… każdy może sam dopowiedzieć sobie bez czego ;) Aby wprowadzić
jakieś dane do bazy test_z2XsY należy jednak najpierw utworzyć odpowiednie tabele, w których te dane będą
przechowywane.

Instrukcja do przedmiotu Bazy danych – laboratorium (zajęcia 2)

Tworzenie tabel

Polecenie  2.4:  W  Eksploratorze  Obiektów  rozwiń  tabele  bazy  danych  mebelek.  Kliknij  prawym  przyciskiem
myszy na tabelę Kierowcy i wybierz Design.

Pojawią  się  kolejną  dwa  ostrzeżenia,  które  informują,  że  nie  jesteś  właścicielem  bazy  danych  oraz  nie  masz
uprawnień  do  modyfikacji  tabeli  Kierowcy.  Kiedy  już  się  z  nimi  uporasz,  powinna  ukazać  się  tabelka  z
właściwościami poszczególnych kolumn tabeli Kierowcy.

Tabelka ta składa się z trzech kolumn.

•

W pierwszej znajdują się nazwy kolumn tabeli Kierowcy (kolejno: id, imie, nazwisko itd.).

•

W drugiej przedstawiono typ każdej z tych kolumn (bardziej szczegółowo jest o tym odrobinę niżej).

•

W trzeciej znajduje się informacja, czy dana kolumna może pozostać pusta podczas wstawiania nowego
rekordu (jak być może pamiętacie z poprzednich zajęć, kolumna imie nie mogła pozostać pusta - pole Allow
Nulls w jej przypadku nie jest zaznaczone).

Klucz główny

Kluczyk przy po lewej stronie wiersza z kolumną id informuje, że dana kolumna pełni rolę klucza w tabeli.

Kolumna  kluczowa  to  taka  kolumna,  której  wartości  nie  mogą  się  powtarzać.  Jako  kolumnę  kluczową  można
wybrać  jakiś  atrybut  obiektów  przechowywanych  w  tabeli.  Np.  w  przypadku  kierowców  można  posłużyć  się
numerem  dowodu  osobistego  albo  peselem,  ale  nie  nazwiskiem  -  nazwiska  różnych  osób  po  prostu  mogą  być
identyczne. Najczęściej stosuje się jednak dodatkową kolumnę (o nazwie id, ident, numer itp.), w której sztucznie
nadawane są numery poszczególnym rekordom w taki sposób, aby były one unikalne.

Każda tabela powinna posiadać kolumnę kluczową (jest to prawda w 99% przypadków). Tabela może mieć tylko
jeden  klucz,  ale  klucz  ten  może  składać  się  z  kilku  kolumn  (np.  możemy  sobie  wyobrazić,  że  na  klucz  w  jakiejś
tabeli  składają  się  imię,  nazwisko,  imię  ojca  i  data  urodzenia  -  każda  z  tych  wartości  indywidualnie  może  się
powtarzać, ale identyczna kombinacja wszystkich wartości jest bardzo mało prawdopodobna). Rozwiązanie takie
stosuje się najczęściej tylko w przypadku specyficznych tabel, tzw. tabel łącznikowych.

Jak pewnie pamiętacie, wartości kolumny id były uzupełniane automatycznie. Odpowiada za to właściwość
Identity.

Polecenie 2.5: Kliknij na pierwszy wiersz (z kolumną id), a następnie w oknie Column Properties znajdującym się
w dolnej części głównego okna (zob. rysunek poniŜej) znajdź wartość Identity Specification i rozwiń ją.

Instrukcja do przedmiotu Bazy danych – laboratorium (zajęcia 2)

Pole Is Identity informuje, czy wartości w danej kolumnie będą wstawiane automatycznie. Identity Increment
ustala wielkość, o którą będzie wzrastała wartość kolumny przy wstawieniu kolejnego wiersza, natomiast Identity
Seed to początkowa wartość.

Typy kolumn

Typ  kolumny określa,  jakie  dane  mogą  być  w  niej  przechowywane.  W  SQL  Server  2005  istnieje olbrzymia liczba
typów danych, które można nadać kolumnom, w praktyce wykorzystuje się jedynie kilka z nich. Część typów różni
się  tylko  zakresem  wartości,  które  można  w  nich  umieszczać  (w  przypadku  liczb  jest  to  maksymalna  wielkość
wprowadzonej liczby itp.).

Polecenie 2.6: PoniŜej zamieszczono pięć typów kolumn. Twoim zadaniem jest ich przyporządkowanie do opisów
zamieszczonych w tabeli poniŜej (uzupełnij kolumnę "nazwa typu"). Aby wykonać to polecenie moŜesz przeglądać
dane  lub  projekt  (Design)  dowolnych  tabel  z  bazy  mebelek.  Jeśli  nie  uda  ci  się  przyporządkować  wszystkich
typów,  spróbuj  wydedukować  prawidłowe  rozwiązanie.  Po  wykonaniu  zadania  pochwal  się  rezultatem
prowadzącemu zajęcia.

varchar(n)

int

float

datetime

char(n)

nazwa typu

opis

dane czasu i daty

liczba zmiennoprzecinkowa (np. 3,14 itp.)

dane znakowe o ustalonej długości n (wartości krótsze są uzupełniane spacjami)

liczba całkowita (np. 5 itp.)

dane znakowe o zmiennej długości, przy czym maksymalna długość moŜe wynosić n

W tym momencie wiecie już wszystko, co potrzebne i możemy przejść do stworzenia pierwszej tabeli. Będzie to
prosta tabela o nazwie Osoby przechowująca informacje o osobach z pewnej grupy studenckiej.

Polecenie 2.7: W Eksploratorze Obiektów kliknij prawym przyciskiem myszy na folder Tables w bazie danych
test_z2XsY i wybierz New Table...

Pojawi się okno, w którym możemy definiować wszystkie kolumny i ich właściwości. W nazwach kolumn,
podobnie jak w przypadku nazwy bazy danych, dopuszczalne jest stosowanie polskich znaków (a nawet spacji),
jednak zdecydowanie się tego odradza - zamiast spacji można używać znaku podkreślenia "_".

Polecenie 2.8: Utwórz następujące kolumny oraz ustal dla nich odpowiedni typ:
id – liczba całkowita,
imie – tekst o zmiennej długości (ustal odpowiedni twoim zdaniem maksymalny rozmiar),
nazwisko – tekst o zmiennej długości (ustal odpowiedni twoim zdaniem maksymalny rozmiar),
data_urodzenia – dane daty i czasu, tylko ta kolumna moŜe pozostawać pusta (Null)
miasto – tekst o zmiennej długości (ustal odpowiedni twoim zdaniem maksymalny rozmiar),
nr_indeksu – tekst o stałej długości równej 6

Instrukcja do przedmiotu Bazy danych – laboratorium (zajęcia 2)

Polecenie 2.9:

W okienku Properties po prawej stronie (jeśli nie jest ono widoczne, w menu View wybierz

Properties Window lub wciśnij klawisz F4) zmień nazwę tabeli (właściwość Name) zamiast Table_1 wpisując
Osoby.

Polecenie 2.10:

Kliknij w ikonę dyskietki znajdującą się na pasku narzędzi i zapisz utworzoną tabelę. Następnie

zamknij okno z projektem tabeli.

Pierwsza tabela w bazie test_z2XsY została właśnie utworzona. To jednak nie wszystko – przydałoby się jeszcze
sprawić, aby wartości w kolumnie id przy wstawianiu danych były ustalane automatycznie oraz ustawić tę
kolumnę jako kluczową.

Modyfikacja tabel

Polecenie 2.11: W Eksploratorze Obiektów kliknij prawym przyciskiem myszy na tabelę Osoby i wybierz Design.
Następnie zmień właściwość kolumny id tak, aby jej wartości były uzupełniane automatycznie (Identity),
zwiększały się co 1 zaczynając od jedynki.

Polecenie 2.12: Kliknij prawym przyciskiem myszy na wiersz z kolumną id i wybierz Set Primary Key. Następnie
zapisz wprowadzone zmiany i zamknij okno z projektem tabeli.

W tym momencie możesz sobie pogratulować. Tabela Osoby jest gotowa na przyjęcie danych.

Polecenie 2.13: W tabeli Osoby wprowadź dane przynajmniej siedmiu osób z twojej grupy (zaczynając od siebie).
Oczywiście nie wszystkie wartości (np. data urodzenia czy numer indeksu) muszą być prawdziwe.

Tabele w relacyjnej bazie danych można tworzyć również za pomocą języka SQL.

Instrukcja do przedmiotu Bazy danych – laboratorium (zajęcia 2)

Polecenie 2.14: W Eksploratorze Obiektów kliknij prawym przyciskiem myszy na tabelę Osoby i wybierz Edit.

Otworzy  się  okienko  zapytań,  w  którym  znajduje  się  kod  tworzący  tabelę  Osoby  oraz  kilka  dodatkowych  rzeczy
ustalających  wartości  pewnych  zmiennych  globalnych  (czyli  ustawień  bazy  danych).  Fragment  tworzący  tabelę
rozpoczyna  się  od  słów  CREATE  TABLE,  później  podawana  jest  nazwa  tabeli,  następnie  wymieniane  są  po
przecinku  wszystkie  nazwy  kolumn  wraz  z  ich  typami  i  ewentualnymi  dodatkowymi  właściwościami.  Na  samym
końcu znajdują się deklaracje ograniczeń na kolumny (CONSTRAINT) - w tym wypadku jedynym ograniczeniem jest
klucz główny nałożony na kolumnę id (klucz główny stanowi ograniczenie w tym sensie, że wartości tej kolumny
nie mogą się powtarzać).

SQL – podstawy c.d.

Agregaty (funkcje agregujące)

Polecenie 2.15: Kliknij prawym przyciskiem myszy na bazę mebelek i wybierz New Query. Następnie wykonaj
kolejno następujące zapytania, za kaŜdym razem analizując otrzymany rezultat:

SELECT COUNT(*) FROM Klienci
SELECT COUNT(*) FROM Towary
SELECT COUNT(*) FROM Klienci WHERE id > 3
SELECT SUM(cena) FROM Towary
SELECT SUM(cena) FROM Towary WHERE id_dost = 1
SELECT AVG(cena) FROM Towary
SELECT AVG(cena) FROM Towary WHERE id_dost = 1
SELECT MAX(data_zatr) FROM Kierowcy
SELECT MIN(data_zatr) FROM Kierowcy

Polecenie 2.16: Odpowiedz na następujące pytania:
1.

Za pomocą jakiej funkcji moŜna wyświetlić liczbę rekordów z danej tabeli?

W jaki sposób policzyć liczbę rekordów spełniających dane kryterium?

Jak funkcja zwraca sumę wartości z danej kolumny?

Jaka funkcja zwraca średnią z wartości z danej kolumny?

Za pomocą jakich funkcji moŜna wyświetlić minimalną lub maksymalną wartość z danej kolumny?

Polecenie 2.17: Za pomocą poleceń SQL spróbuj:
1.

Policzyć liczbę zamówień.

Policzyć liczbę sztuk zakupionego towaru.

Policzyć średni rocznik cięŜarówek.

Podać datę najwcześniejszego zamówienia.

Podać datę ostatniego zamówienia.

(dla ambitnych) Uwaga! PoniewaŜ kolumna rocznik z tabeli Ciezarowki ma typ całkowitoliczbowy, więc
średnia wartości z tej kolumny równieŜ zostanie zamieniona na liczbę całkowitą. Aby tak się nie stało naleŜy
dokonać konwersji na typ zmiennoprzecinkowy np. za pomocą funkcji CONVERT w następujący sposób:

CONVERT(float, rocznik)

Polecenie 2.18: Kliknij prawym przyciskiem myszy na bazę mebelek i wybierz New Query i wykonaj następujące
zapytanie:

SELECT id_dost, COUNT(*) FROM Towary GROUP BY id_dost

Polecenie 2.19: Spróbuj odpowiedzieć na następujące pytania:
1.

Jak zinterpretować wynik zwrócony przez to zapytanie (porów. z wynikiem drugiego zapytania z polecenia 15)?

Co spowodowało uŜycie klauzuli GROUP BY? Jaki wynik dostalibyśmy bez jej uŜycia?

Instrukcja do przedmiotu Bazy danych – laboratorium (zajęcia 2)

Polecenie  2.20:  Korzystając  z  klauzuli  GROUP  BY  spróbuj  wykonać  zapytanie  do  tabeli  Osoby  w  bazie
test_z2XsY zwracające nazwy miast oraz liczbę osób pochodzących z kaŜdego z nich.

Polecenie  2.21  (dla  ambitnych  i  pracowitych,  możliwa  praca  w  parach):  W  bazie  danych  test_z2XsY  utwórz
dodatkową  tabelę.  Tabela  ta  moŜe  słuŜyć  do  przechowywania  dowolnych  informacji  (np.  o  filmach,  ksiąŜkach,
piosenkach,  zwierzętach,  umowach  najmu  itd.).  Zastanów  się  najpierw,  jakie  kolumny  (i  jakiego  typu)  powinny
znaleźć się w takiej tabeli. NałóŜ na odpowiednią kolumnę klucz i wprowadź przykładowe dane. Po zakończeniu,
zawołaj prowadzącego i zaprezentuj rezultat swoich wysiłków.

Pytania kontrolne:

W jaki sposób utworzyć nową bazę danych za pomocą SQL Server Management Studio?

Z jakich plików składa się baza danych w SQL Server 2005?

Do czego służy plik dziennika?

Co to jest transakcja?

W jaki sposób utworzyć tabelę w bazie danych?

O czym decyduje typ kolumny?

Jakie dane przechowywane są w kolumnach następujących typów: varchar(n), int, float, datetime, char(n)?

Co oznacza opcja Allow Nulls w projekcie tabeli?

Co to jest klucz główny i w jaki sposób nałożyć go na kolumnę w tabeli?

10.

W jaki sposób sprawić, aby wartości wybranej kolumny były automatycznie uzupełniane liczbami?

11.

Jak w języku SQL nazywa się ograniczenie nałożone na kolumny?

Po tych zajęciach powinieneś/powinnaś umieć:

•

tworzyć bazy danych (wraz z ustaleniem pewnych ich parametrów),

•

tworzyć i edytować tabele (typy kolumn, klucz główny, Identity, nazwa tabeli),

•

wykonywać proste zapytania typu SELECT przy wykorzystaniu funkcji agregujących: SUM, COUNT, AVG, MAX,

MIN,

•

korzystać z klauzuli GROUP BY do grupowania danych z tabeli według wybranego kryterium.