1

1

Projektowanie bazy danych ms-mot

Projektowanie bazy danych ms-mot

(opis tworzonej bazy, tworzenie bazy danych,

(opis tworzonej bazy, tworzenie bazy danych,

tworzenie, usuwanie i modyfikacja obiektów w bazie

tworzenie, usuwanie i modyfikacja obiektów w bazie

danych, indeksy)

danych, indeksy)

Prezentacja do ćwiczenia 3

Prezentacja do ćwiczenia 3

2

2

Opis tworzonej Bazy Danych

Baza danych „MS-MOT” tworzona jest dla fikcyjnej firmy z

Baza danych „MS-MOT” tworzona jest dla fikcyjnej firmy z

branży motoryzacyjnej. Firma ta zajmuje się wypożyczeniem trzech

branży motoryzacyjnej. Firma ta zajmuje się wypożyczeniem trzech

typów pojazdów:

typów pojazdów:

•

Motocykli

Motocykli

•

Skuterów

Skuterów

•

Quadów

Quadów

Baza danych będzie przechowywać informację o pracownikach

Baza danych będzie przechowywać informację o pracownikach

zatrudnionych w firmie oraz o posiadanych przez firmę pojazdach.

zatrudnionych w firmie oraz o posiadanych przez firmę pojazdach.

Najistotniejszą sprawą jednak jest zbieranie informacji o

Najistotniejszą sprawą jednak jest zbieranie informacji o

wypożyczeniach czyli kto, komu, jaki pojazd wypożyczył, kiedy go

wypożyczeniach czyli kto, komu, jaki pojazd wypożyczył, kiedy go

wypożyczył i oraz na jak długo. Dalsze aplikacje korzystające z

wypożyczył i oraz na jak długo. Dalsze aplikacje korzystające z

informacji zawartych w bazie danych, oblicza ile klient musi zapłacić

informacji zawartych w bazie danych, oblicza ile klient musi zapłacić

za wypożyczenie pojazdu.

za wypożyczenie pojazdu.

3

3

Wprowadzenie

W celu stworzenia bazy danych ms-mot posłużymy się językiem SQL
a uściślając warstwą DDL (Data Definition Language) języka SQL

Etap tworzenia bazy danych za pomocą języka SQL można
podzielić na kilka kroków:

• krok 1 – utworzenie pustej bazy danych

• krok 2 – utworzenie kolejnych tabel, przechowujących
odpowiednie typy danych w odpowiednich kolumnach (wybór
odpowiednich typów danych w zależności od przechowywanych
informacji), wybieranie oraz definicja kluczy głównych

• krok 3 - ustalenie relacji (związków) pomiędzy tabelami, dodanie
kluczy obcych

• krok 4 – definicja ewentualnych indeksów

4

4

Aby wykonać polecenie języka SQL posłużymy się narzędziem
„SQL Query Analyzer” jest ono standardowo dostępne w RDBMS
Microsoft SQL Server 2000
Aby stworzyć bazę danych posługujemy się poleceniem:
Create database nazwa_bazy;

Rysunek 2

Aby wykonać
polecenie
(kwerendę)
wciskamy
klawisz „F5”
lub naciskamy
zaznaczony
kolorem
czerwonym
przycisk

5

5

Celem jest stworzenie Bazy Danych o następującym schemacie ERD

(ERD = Diagram Związków Encji)

Rysunek 1

Diagram ERD:
- pokazuje logiczne związki

pomiędzy różnymi
encjami;

- obiekty, o których

informacje są istotne z
punktu widzenia
realizacji celów
strategicznych;

- atrybuty obiektów;
- związki pomiędzy

obiektami.

Wyodrębnione obiekty

mogą być rzeczywiste
lub mogą być pojęciami
abstrakcyjnymi. Obiekty
mające te same
atrybuty łączy się w
typy obiektów np.
Pracownik, Klient,
Pojazdy, Wypożyczenie.

6

6

Rysunek 3

W dolnej części okna służącego do wprowadzania zapytań
widzimy wynik wykonania zapytania ten na rysunku 2
informuje nas o pomyślnym utworzeniu bazy danych

Tworzenie baz danych - SQL

7

7

Aby uruchomić aplikacje Enterprise Manager wybieramy: menu start

Aby uruchomić aplikacje Enterprise Manager wybieramy: menu start

-> programy -> Microsoft SQL Server -> Enterprise menager

-> programy -> Microsoft SQL Server -> Enterprise menager

Poniższy rysunek przedstawia okno „SQL Server Enterprise Manager”.

Poniższy rysunek przedstawia okno „SQL Server Enterprise Manager”.

Aplikacja służy do zarządzania oraz administrowania bazą danych

Aplikacja służy do zarządzania oraz administrowania bazą danych

SQL Enterprise Menager

Rysunek 4

8

8

Tworzenie Bazy danych w Enterprise Manager

Tworzenie Bazy danych w Enterprise Manager

Aby utworzyć nową bazę danych za pomocą MS Enterprise Manager ze

Aby utworzyć nową bazę danych za pomocą MS Enterprise Manager ze

struktury drzewa wybierz serwer (w tym przypadku „local”) wciśnij

struktury drzewa wybierz serwer (w tym przypadku „local”) wciśnij

prawy przycisk myszy i następnie opcję „New Database”

prawy przycisk myszy i następnie opcję „New Database”

Rysunek 5

9

9

W okienku

W okienku

„Database

„Database

Properties w polu

Properties w polu

name wpisz nazwę

name wpisz nazwę

bazy danych

bazy danych

(msmot)

(msmot)

następnie kliknij

następnie kliknij

„ok”.

„ok”.

Rysunek 6

10

10

W kolejnym kroku definiujemy tabele, jak już wiemy do utworzenia

W kolejnym kroku definiujemy tabele, jak już wiemy do utworzenia

tabeli służy polecenie: „create table nazwa_tabeli”

tabeli służy polecenie: „create table nazwa_tabeli”

Aby wybrać przed chwilą utworzoną bazę danych posługujemy się

Aby wybrać przed chwilą utworzoną bazę danych posługujemy się

poleceniem:

poleceniem:

Use msmot;

Use msmot;

W następujący sposób tworzymy tabelę z pojazdami:

W następujący sposób tworzymy tabelę z pojazdami:

Create table [pojazdy]

Create table [pojazdy]

(

(

[id_pojazdy] int Identity (1,1) NOT NULL,

[id_pojazdy] int Identity (1,1) NOT NULL,

[marka] Varchar(50) NOT NULL,

[marka] Varchar(50) NOT NULL,

[model] Varchar(50) NOT NULL,

[model] Varchar(50) NOT NULL,

[typ] Varchar(5) NOT NULL,

[typ] Varchar(5) NOT NULL,

[rok_prod] int NOT NULL,

[rok_prod] int NOT NULL,

[pojemnosc] int NOT NULL,

[pojemnosc] int NOT NULL,

[przebieg] bigint NOT NULL,

[przebieg] bigint NOT NULL,

[nr_nadwozia] Varchar(10) NOT NULL,

[nr_nadwozia] Varchar(10) NOT NULL,

Primary Key ([id_pojazdy])

Primary Key ([id_pojazdy])

)

)

Tworzenie tabel – (Kwerenda SQL w Query

Tworzenie tabel – (Kwerenda SQL w Query

Analyzer)

Analyzer)

11

11

Aby utworzyć nową tabelę wybierz utworzoną bazę msmot

Aby utworzyć nową tabelę wybierz utworzoną bazę msmot

następnie element

następnie element

„

„

tables” prawy klawisz myszy „new table”

tables” prawy klawisz myszy „new table”

Tworzenie tabel - Enterprise Menager

Tworzenie tabel - Enterprise Menager

Rysunek 7

12

12

W kolejnym okienku (rys. 8)

W kolejnym okienku (rys. 8)

Definiujemy odpowiednie

Definiujemy odpowiednie

kolumny wraz z typem

kolumny wraz z typem

przechowywanych przez nie

przechowywanych przez nie

Danych. Dodatkowo aby

Danych. Dodatkowo aby

zdefiniować klucz główny

zdefiniować klucz główny

wciskamy prawy klawisz

wciskamy prawy klawisz

Myszy wybieramy opcję „Set

Myszy wybieramy opcję „Set

Primary Key” ustawiając

Primary Key” ustawiając

daną kolumną jako klucz

daną kolumną jako klucz

główny (rys. 9). Po

główny (rys. 9). Po

wprowadzeniu wszystkich

wprowadzeniu wszystkich

danych na pytanie czy

danych na pytanie czy

zapisać zmiany w tabeli

zapisać zmiany w tabeli

odpowiadamy tak oraz

odpowiadamy tak oraz

zapisujemy tabelę pod

zapisujemy tabelę pod

określoną nazwą (rys. 10) .

określoną nazwą (rys. 10) .

Tworzenie tabel - Enterprise

Tworzenie tabel - Enterprise

Menager

Menager

Rysunek 8

Rysunek 9

Rysunek 10

Primary Key

Primary Key

(

(Klucz podstawowy): jest polem, które jednocześnie

identyfikuje dany rekord w tabeli. Klucz podstawowy jest
unikalny, to znaczy, że w tabeli każdy klucz występuje tylko raz

13

13

Tworzenie indeksów oraz ich typy

Tworzenie indeksów oraz ich typy

Aby przyśpieszyć wyszukiwanie danych z tabel zakładamy indeksy na

Aby przyśpieszyć wyszukiwanie danych z tabel zakładamy indeksy na

kolumny, które najczęściej będą kryterium wyszukiwania, czyli

kolumny, które najczęściej będą kryterium wyszukiwania, czyli

klientów będziemy wyszukiwać po nazwiskach w tym celu definiujemy

klientów będziemy wyszukiwać po nazwiskach w tym celu definiujemy

indeks.

indeks.

Indeksy definiujemy następująco:

Indeksy definiujemy następująco:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX

nazwa_indexu ON nazwa_tabeli (nazwa_kolumny_slużącą_za index)

nazwa_indexu ON nazwa_tabeli (nazwa_kolumny_slużącą_za index)

Opis opcji polecenia

Opis opcji polecenia

CREATE INDEX

CREATE INDEX

:

:



UNIQUE

UNIQUE

- Indeks ma przyjmować wartości unikatowe. Przed

- Indeks ma przyjmować wartości unikatowe. Przed

utworzeniem unikatowego indeksu należy usunąć powtarzające się

utworzeniem unikatowego indeksu należy usunąć powtarzające się

dane w danej kolumnie.

dane w danej kolumnie.



CLUSTERED

CLUSTERED

- Kolejność danych w bazie danych będzie zgodna z

- Kolejność danych w bazie danych będzie zgodna z

indeksem.

indeksem.



NONCLUSTERED

NONCLUSTERED

- Kolejność danych w bazie danych będzie zgodna z

- Kolejność danych w bazie danych będzie zgodna z

ich wprowadzaniem. Domyślnie przyjmowana jest opcja

ich wprowadzaniem. Domyślnie przyjmowana jest opcja

NONCLUSTERED

NONCLUSTERED

.

.

Więc dla naszej bazy będzie następująco:

Więc dla naszej bazy będzie następująco:

Create Index idx_nazwisko ON klient (nazwisko)

Create Index idx_nazwisko ON klient (nazwisko)

14

14

Tworzenie indeksów - Enterprise Menager

Tworzenie indeksów - Enterprise Menager

W celu zdefiniowania określonej kolumny

W celu zdefiniowania określonej kolumny

jako pole typu „indeks” w widoku projektu

jako pole typu „indeks” w widoku projektu

tabeli (rys. 8) wciskamy prawy klawisz

tabeli (rys. 8) wciskamy prawy klawisz

myszy i wybieramy opcję „Indexes/Keys”

myszy i wybieramy opcję „Indexes/Keys”

Rysunek 11

Rysunek 12

W kolejny oknie
wybieramy zakładkę
„indexes/keys” po czym
klikamy guzik „new”

15

15

Tworzenie indeksów - Enterprise Menager

Tworzenie indeksów - Enterprise Menager

W kolejnym okienku w polu „Index

W kolejnym okienku w polu „Index

name” podajemy nazwę indeksu

name” podajemy nazwę indeksu

Z listy rozwijanej w polu „column

Z listy rozwijanej w polu „column

name” wybieramy na której

name” wybieramy na której

kolumnie

kolumnie

indeks ma zostać założony.

indeks ma zostać założony.

Parametr

Parametr

order określa jak sortujemy indeksy:

order określa jak sortujemy indeksy:

-

Ascending w kierunku rosnącym

Ascending w kierunku rosnącym

-

Descending natomiast w

Descending natomiast w

kierunku malejącym.

kierunku malejącym.

Klikamy guzik ok utworzony zostaje

Klikamy guzik ok utworzony zostaje

indeks.

indeks.

Rysunek 12

16

16

Modyfikacja, usuwanie struktury oraz

Modyfikacja, usuwanie struktury oraz

obiektów w bazie danych

obiektów w bazie danych

Aby zmienić strukturę istniejącej tablicy stosujemy instrukcje ALTER

Aby zmienić strukturę istniejącej tablicy stosujemy instrukcje ALTER

Na przykład poniższe polecenie dodaje do tabeli klienci kolumnę test

Na przykład poniższe polecenie dodaje do tabeli klienci kolumnę test

przechowującą zmienną (maksymalnie do 255) ilość znaków:

przechowującą zmienną (maksymalnie do 255) ilość znaków:

•

ALTER TABLE

ALTER TABLE

klient

klient

ADD test varchar(255);

ADD test varchar(255);

Usuwanie:

Usuwanie:

•

Kolumn:

Kolumn:

•

ALTER TABLE klient DROP COLUMN test;

ALTER TABLE klient DROP COLUMN test;

•

Indeksów:

Indeksów:

•

DROP INDEX nazwa_tabeli.nazwa_indexu;

DROP INDEX nazwa_tabeli.nazwa_indexu;

•

Tabel:

Tabel:

•

DROP TABLE nazwa_tabeli;

DROP TABLE nazwa_tabeli;

•

Bazy danych:

Bazy danych:

•

DROP DATABASE nazwa_bazy_danych;

DROP DATABASE nazwa_bazy_danych;

Usuwanie nieodwracalnie niszczy dane zawarte w kolumnie,

Usuwanie nieodwracalnie niszczy dane zawarte w kolumnie,

tabeli bądź bazie danych !

tabeli bądź bazie danych !

17

17

W celu utworzenia klucza głównego posługujemy się instrukcją:

W celu utworzenia klucza głównego posługujemy się instrukcją:

Alter table wypozyczenie add PRIMARY key(id_wypozyczenie);

Alter table wypozyczenie add PRIMARY key(id_wypozyczenie);

Lub przy tworzeniu kolumny wybieramy,

Lub przy tworzeniu kolumny wybieramy,

które pole ma być kluczem

które pole ma być kluczem

Aby utworzyć relację pomiędzy dwoma tablicami należy je powiązać za

Aby utworzyć relację pomiędzy dwoma tablicami należy je powiązać za

pomocą klucza głównego z jednej tabeli oraz klucza obcego z drugiej

pomocą klucza głównego z jednej tabeli oraz klucza obcego z drugiej

tabeli.

tabeli.

Klucz obcy :

Klucz obcy :

nieunikatowe pole w tabeli, która nie jest powiązana z

nieunikatowe pole w tabeli, która nie jest powiązana z

kluczem głównym innej tabeli. W związkach jeden do wielu klucz

kluczem głównym innej tabeli. W związkach jeden do wielu klucz

główny (podstawowy) znajduję się w tabeli „jeden”, natomiast klucz

główny (podstawowy) znajduję się w tabeli „jeden”, natomiast klucz

obcy – w tabeli „wielu”.

obcy – w tabeli „wielu”.

Relacje klucze główne oraz klucze obce

Relacje klucze główne oraz klucze obce

Create table pojazdy

Create table pojazdy

(

(

id_

id_

pojazdy

pojazdy

int Identity (1,1) NOT NULL,

int Identity (1,1) NOT NULL,

Primary Key (

Primary Key (

id_

id_

pojazdy)

pojazdy)

)

)

18

18

Ćwiczenia

Ćwiczenia

1

1

.

.

Zmień strukturę istniejącej tabeli dodając do niej klucz obcy:

Zmień strukturę istniejącej tabeli dodając do niej klucz obcy:

Alter table wypozyczenie add foreign key(id_klienta) references

Alter table wypozyczenie add foreign key(id_klienta) references

klient (id_klienta);

klient (id_klienta);