Wykład 3

(1)

Wykład 3

• SQL - język operacji na bazach danych

• Schemat przykładowej bazy danych “Uczelnia”

• Skrypt SQL - utworzenie bazy “Uczelnia”

• Polecenia selekcji i projekcji

• Interakcyjny dostęp do bazy danych

(2)

SQL - język operacji na bazach danych

Do opisu operacji na bazach danych stworzono specjalny język SQL (ang.

Structured Query Language).

Jego składnia i znaczenie jest określone odpowiednimi standardami międzynarodowymi.

Wyrażenia w SQL są w większości SZBD (w szczególności w systemach

wykorzystujących architekturę klient - serwer) formą pośrednią pomiędzy interface programu użytkowego klienta (tzw. front - end) a serwerem bazy danych (tzw. back - end).

W języku tym są zapisywane (często automatycznie, bez wiedzy użytkownika)

polecenia (tzw. zapytania) do serwera bazy danych.

(3)

W SQL wyróżniono kilka podzbiorów wyrażeń języka o różnym przeznaczeniu i funkcjach:

• Podzbiór DDL (Data Definition Language) - wyrażenia z tego podzbioru służą do definiowania obiektów schematu bazy (tabel, kluczy, powiązań, funkcji, procedur pamiętanych, wyzwalaczy, widoków)

• Podzbiór DML (Data Manipulation Language) - służy do czytania danych, poprawiania, usuwania, tworzenia krotek – wierszy tabel

• Podzbiór DCL (Data Control Language) - sterowanie dostępem do danych

i przepływem danych

(4)

S T U D E N C I n u m _ s t u d

n a z w i s k o i m i e a d r e s d a t a _ u r

p l e c

P R A C O W N I C Y n u m _ p r a c n a z w i s k o _ p r a c

i m i e _ p r a c a d r e s t e l e f o n _ d o m

s t a n o w i s k o w y n a g r o d z e n i e

n r _ p o k o j u

P U N K T Y n u m _ s t u d

k u r s n u m _ z a l i c z

p u n k t y

R E J E S T R n u m _ s t u d

k u r s s e k c j a

S E K C J A k u r s s e k c j a n u m _ p r a c

P O K O J E n r _ p o k o j u

t y p p o j e m n o s c

Z A L I C Z E N I A k u r s n u m _ z a l i c z

w a g a k o m e n t a r z

d a t a

K U R S Y k u r s w s p o l c z y n n i k

l i c z _ g o d z i n k o m e n t a r z

L A B O R A T O R I A k u r s

l a b o r a t o r i u m

G R U P Y k u r s s e k c j a

d z i e n c z a s l i c z _ g o d z i n

(5)

Definicje tabel bazy Student

/* Table: Studenci */

create table DBA.Studenci (

num_stud integer not null default autoincrement, nazwisko char(25) not null,

imie char(25) null,

adres char(40) null,

data_ur date null,

plec char not null );

alter table DBA.Studenci

alter plec add constraint ASA69 check (plec in ('M','K','m','k'));

add constraint ASA70 primary key (num_stud);

add constraint ASA71 unique (num_stud);

/* Index: NazwiskaStud */

create index NazwiskaStud on DBA.Studenci ( nazwisko ASC,

imie ASC

(6)

/* Table: Pokoje */

create table DBA.Pokoje (

nr_pokoju char(4) not null, typ char(4) not null,

pojemnosc smallint not null

);

alter table DBA.Pokoje

add constraint ASA72 primary key (nr_pokoju);

(7)

/* Table: Pracownicy */

create table DBA.Pracownicy (

num_prac integer not null,

nazwisko_prac char(25) not null, imie_prac char(25) not null, adres char(40) null, telefon_dom char(16) null, wynagrodzenie numeric(6,2) null,

stanowisko char(8) not null default 'Profesor', nr_pokoju char(4) not null

);

alter table DBA.Pracownicy

alter stanowisko add constraint ASA73 check (stanowisko in ('Asystent','Adiunkt','Profesor'));

add constraint ASA74 primary key (num_prac);

/* Index: NazwiskaPrac */

create index NazwiskaPrac on DBA.Pracownicy ( nazwisko_prac ASC

);

%% Klucze obce

add constraint Pokoje foreign key (nr_pokoju) references DBA.Pokoje (nr_pokoju)

on update restrict on delete restrict;

(8)

/* Table: Kursy */

create table DBA.Kursy (

kurs char(5) not null, wspolczynnik numeric(3,2) not null,

licz_godzin smallint not null,

komentarz char(80) not null );

alter table DBA.Kursy

add constraint ASA75 check (wspolczynnik in( 1.0,.75,.5,.25));

alter table DBA.Kursy

add constraint ASA76 primary key (kurs);

(9)

/* Table: Zaliczenia */

create table DBA.Zaliczenia (

kurs char(5) not null,

num_zalicz smallint not null,

waga numeric(5,2) not null, komentarz char(25) null,

data date null

);

alter table DBA.Zaliczenia

add constraint ASA77 primary key (kurs, num_zalicz);

%% Klucz obcy

alter table DBA.Zaliczenia

add constraint Kursy foreign key (kurs) references DBA.Kursy (kurs)

(10)

/* Table: Sekcja */

create table DBA.Sekcja (

num_prac integer not null,

sekcja smallint not null

);

alter table DBA.Sekcja

add constraint ASA79 primary key (kurs, sekcja);

/* Klucze obce */

add constraint Pracownicy foreign key (num_prac) references DBA.Pracownicy (num_prac)

add constraint kursy foreign key (kurs) references DBA.Kursy (kurs)

(11)

/* Table: Grupy */

create table DBA.Grupy (

sekcja smallint not null,

dzien char(3) not null, czas char(5) not null,

licz_godzin smallint not null,

nr_pokoju char(4) not null );

alter table DBA.Grupy

add constraint ASA80 primary key (kurs, sekcja, dzien, czas);

add constraint ASA81 unique (dzien, czas, nr_pokoju);

%% Klucze obce

add constraint Pokoje foreign key (nr_pokoju) references DBA.Pokoje (nr_pokoju)

add constraint Sekcja foreign key (kurs, sekcja) references DBA.Sekcja (kurs, sekcja)

(12)

/* Table: Rejestr */

create table DBA.Rejestr (

num_stud integer not null,

sekcja smallint not null

);

alter table DBA.Rejestr

add constraint ASA82 primary key (num_stud, kurs);

/* Klucze obce */

add constraint Sekcja foreign key (kurs, sekcja) references DBA.Sekcja (kurs, sekcja)

add constraint Studenci foreign key (num_stud) references DBA.Studenci (num_stud)

on update cascade on delete cascade;

(13)

/* Table: Punkty */

create table DBA.Punkty (

num_stud integer not null,

num_zalicz smallint not null,

punkty smallint not null

);

alter table DBA.Punkty

alter punkty add constraint ASA83 check (punkty between 0 and 100);

add constraint ASA84 primary key (num_stud, kurs, num_zalicz);

%% Klucze obce

add constraint Rejestr foreign key (num_stud, kurs) references DBA.Rejestr (num_stud, kurs)

on update cascade on delete cascade;

add constraint Zaliczenia foreign key (kurs, num_zalicz) references DBA.Zaliczenia (kurs, num_zalicz)

on update restrict

(14)

/* User: DBA */

grant connect to DBA identified by "";

grant connect to admin identified by "tutaj_hasło";

% Ustawienie uprawnien do przegladania tablic dla uzytkownika admin grant select on Studenci to admin;

grant select on Pokoje to admin;

grant select on Pracownicy to admin;

grant select on Kursy to admin;

grant select on Zaliczenia to admin;

grant select on Laboratoria to admin;

grant select on Sekcja to admin;

grant select on Grupy to admin;

grant select on Rejestr to admin;

grant select on Punkty to admin;

commit work; % zatwierdzenie zmian

(15)

Polecenia selekcji i projekcji danych

Wszystkie wiersze i kolumny tablicy można obejrzeć jako wynik zapytania:

**SELECT * FROM Studenci;**

SQL dopuszcza użycie, zarówno do zapisu słów kluczowych języka, jak i nazw tablic (relacji) i kolumn (atrybutów) małe i duże litery.

Odpowiedzią na powyższe zapytanie jest zawartość całej tablicy Studenci. Dane te

będą wyświetlane w zupełnie przypadkowej kolejności.

(16)

(17)

(18)

(19)

Do interakcyjnego dostępu do bazy danych można wykorzystać np. narzędzia MS SQLServer'a "zanurzone" w MS Visual Studio 2015.

Sposób nawiązania połączenia z przykładową bazą Student (plik bazowy

Student.mdb wraz z plikiem logu Student.ldb) pokazano niżej

(20)

Projekcja danych

(21)

Projekcja i porządkowanie danych

(22)

(23)

Warunki wyboru:

(24)

(25)

(26)

(27)

Dopasowanie do wzorca

(28)

(29)

(30)