20761 Querying Data with Transact-SQL

Upoznajte DDL, DML i JOINs

Struktuirani upitni jezik je jedan od osnovnih sastavnih blokova savremene arhitekture baza podataka. Definiše metode koje se koriste da bi se kreirale relacione baze podataka i da bi se sa njima radilo na svim glavnim platformama. Na prvi pogled, jezik izgleda složeno i skoro zastrašujuće, ali uopšte ga nije teško savladati.

O SQL-u

Ispravan izgovor naziva jezika je stalno sporno pitanje u zajednici stručnjaka koji se bave bazama podataka. Američki nacionalni institut za standarde je ustanovio pravilan izgovor imena kao „eskjuel“, međutim, mnogi stručnjaci koji se bave bazama podataka navikli su da izgovaraju „sikvel“, tako da vama prepuštamo izbor.

Postoji nekoliko vrsta SQL-a. Oraklova baza podataka koristi svoju verziju PL/SQL, a SQL server kompanije „Majkrosoft“ koristi Transact-SQL. Sve varijacije se zasnivaju na industrijskom standardu ANSI SQL. Ovaj uvod koristi SQL naredbe usaglašene sa ANSI standardom koji može da se koristi u bilo kom savremenom sistemu baza podataka.

DDL i DML

Naredbe u SQL-u mogu da se podele na dva glavna podjezika. Jezik za definisanje podataka (DDL) sadrži naredbe koje se koriste da bi se kreirale i uništile baze podataka i njihovi objekti. Pošto se uz pomoć tog jezika definiše struktura baze podataka, njeni administratori i korisnici mogu da koriste jezik za manipulisanje podacima (DML) da bi umetali, pronalazili i menjali podatke koji se u njoj nalaze.

Naredbe u jeziku za definisanje podataka

Jezik za definisanje podataka se koristi da bi se kreirale i uništile baze podataka i njihovi objekti. Te naredbe prvenstveno koriste administratori baza podataka tokom faza uspostavljanja ili uklanjanja baza podataka. Predstavićemo vam strukturu i upotrebu četiri osnovne naredbe u jeziku za definisanje podataka.

CREATE. Instaliranje sistema za upravljanje bazama podataka u računar omogućava vam da kreirate i upravljate mnogim nezavisnim bazama podataka. Na primer, želite da kreirate bazu podataka kontakata klijenata za odeljenje prodaje i bazu podataka zaposlenih za kadrovsku službu. Naredba CREATE se koristi da bi se uspostavila bilo koja od navedenih baza podataka na vašoj platformi. Na primer, naredba:

CREATE DATABASE employees

kreira praznu bazu podataka koja se zove „employees“ (zaposleni) na vašem sistemu za upravljanje bazama podataka. Pošto ste kreirali bazu podataka, sledeći korak bi bio kreiranje tabela koje će sadržati podatke. Varijanta naredbe CREATE može se koristi u tom postupku. Naredba:

CREATE TABLE personal_info (first_name char(20) not null, last_name char(20) not null, employee_id int not null)

kreira tabelu pod nazivom „personal_info“ (lični_podaci) u postojećoj bazi podataka. U ovom primeru, tabela sadrži tri atributa: ime, prezime i lični broj zaposlenog (first_name, last_name, and employee_id) zajedno sa još nekim detaljima.

USE. Naredba USE vam omogućava da odredite bazu podataka u kojoj želite da radite u okviru vašeg sistema za upravljanje bazama podataka. Na primer, ako trenutno radite u bazi podataka prodaje i želite da izdate neke naredbe koje će uticati na bazu podataka zaposlenih, pre njih morate iskoristiti sledeću naredbu u SQL-u:

USE employees

Izuzetno je važno da uvek budete svesni u kojoj bazi podataka radite pre nego što izdate naredbe u SQL-u kojima manipulišete podacima.

ALTER. Kad ste kreirali tabelu u okviru baze podataka, desiće se da ćete želeti da izmenite njenu definiciju. Naredba ALTER vam omogućava da izvršite promene u strukturi tabele, a da pri tom ne morate da je obrišete ili ponovo kreirate. Pogledajte sledeću naredbu:

ALTER TABLE personal_info ADD salary money null

U ovom primeru dodali smo novi atribut tabeli personal_info, a to je zarada zaposlenog (employee’s salary). Argument „money“ (novac) određuje da je zarada zaposlenog izražena u formatu dolara i centi. Konačno, ključna reč „null“ (nula) govori bazi podataka da je sasvim u redu ako to polje ne sadrži nikakvu vrednost za bilo kog zaposlenog.

DROP. Poslednja naredba u jeziku za definisanje podataka, DROP, omogućava vam da uklonite celokupne objekte baze podataka iz sistema za upravljanje bazama podataka. Na primer, ako želite da trajno uklonite tabelu personal_info koju ste kreirali, koristite sledeću naredbu:

DROP TABLE personal_info

Slično tome, naredbu koju ćemo navesti koristili bi da uklonite celokupnu bazu podataka zaposlenih (employee):

DROP DATABASE employees

Ovu naredbu bi valjalo pažljivo koristiti jer DROP uklanja celu strukturu podataka iz vaše baze podataka. Ako želite da uklonite pojedinačne zapise, koristite naredbu DELETE u jeziku za manipulisanje podacima.

Naredbe u jeziku za manipulisanje podacima

Jezik za manipulisanje podacima (DML) koristi se da bi se pronalazile, umetale i menjale informacije u bazi podataka. Te naredbe koriste svi korisnici baza podataka tokom rutinskih postupaka u bazi podataka.

INSERT. Naredba INSERT u SQL-u se koristi da bi se dodali zapisi već postojećoj tabeli. Vratimo se primeru personal_info iz prethodnog odeljka i zamislite da naša kadrovska služba treba da doda novog zaposlenog bazi podataka. Za taj postupak možete koristi naredbu sličnu ovoj:

INSERT INTO personal_info values(‘bart’,’simpson’,12345,$45000)

Obratite pažnju na to da su određene četiri vrednosti za ovaj unos. One odgovaraju atributima tabele redosledom kojim su definisani: ime, prezime, lični broj zaposlenog i plata (first_name, last_name, employee_id and salary).

SELECT. Naredba SELECT je najčešće korišćena naredba u SQL-u. Ona omogućava korisnicima baze podataka da pronađu određenu informaciju koju žele iz operativne baze podataka. Pogledajte nekoliko primera u kojima ćemo ponovo koristiti tabelu personal_info iz baze podataka zaposlenih.

Naredba koja je dole prikazana pronalazi sve informacije koje su sadržane u tabeli personal_info. Obratite pažnju na to da se zvezdica u SQL-u koristi kao džoker karta. Naredba doslovno znači „Odaberi sve iz tabele personal_info.“

SELECT *

FROM personal_info

S druge strane, korisnici bi možda želeli da ograniče atribute koji se pretražuju iz baze podataka. Na primer, kadrovska služba traži popis prezimena svih zaposlenih u kompaniji. Sledeća naredna u SQL-u prikazaće samo tu informaciju:

SELECT last_name

FROM personal_info

Klauzula WHERE može se koristiti da bi se ograničili zapisi koji se traže samo na one koji zadovoljavaju određene uslove. Direktor bi možda želeo da pregleda dosijee svih zaposlenih koji imaju visoke plate. Sledeća naredba prikazuje sve podatke koji se nalaze u tabeli personal_info za zapise koji imaju platu veću od 50.000 dolara:

SELECT *
FROM personal_info
WHERE salary > $50000

UPDATE. Naredba UPDATE može se koristiti da bi se izmenile informacije koje se nalaze u tabeli, kako pojedinačno, tako i grupno. Pretpostavimo da je kompanija svim zaposlenima povećala godišnju platu za 3 procenta zbog povećanja troškova života. Sledeća naredba u SQL-u se može koristiti da bi se to brzo primenilo na sve zaposlene koji se nalaze u bazi podataka:

UPDATE personal_info
SET salary = salary * 1.03

Kad novi zaposleni Bart Simpson pokaže da je izuzetno vredan i uzoran radnik, uprava želi da ga nagradi za sva njegova dostignuća tako što mu daje povišicu od 5.000 dolara. Klauzula WHERE bi mogla da se koristi da bi izdvojila Barta koji treba da dobije povišicu:

UPDATE personal_info
SET salary = salary + $5000
WHERE employee_id = 12345

DELETE. Hajde konačno da objasnimo i naredbu DELETE. Videćete da je sintaksa ove naredbe slična sintaksama ostalih naredbi u jeziku za manipulisanje podacima (DML). Nažalost, najnoviji korporativni izveštaj o ostvarenoj zaradi nije ispunio očekivanja i naš siroti Bart je dobio otkaz. Naredba DELETE zajedno sa klauzulom WHERE može se koristiti da se njegov zapis izbriše iz tabele personal_info:

DELETE FROM personal_info
WHERE employee_id = 12345

JOINs

Kad smo savladali osnove SQL-a, vreme je da se upoznamo sa najmoćnijim konceptima koje taj jezik može da ponudi – sa naredbom JOIN. Ona vam omogućava da kombinujete podatke iz većeg broja tabela da biste efikasno obradili velike količine podataka. U tim naredbama leži prava moć baza podataka.

Da bismo istražili upotrebu osnovne operacije JOIN u kombinovanju podataka iz dve tabele, nastavićemo sa primerom koristeći tabelu PERSONAL_INFO i dodaćemo još jednu tabelu u celom tom procesu. Pretpostavimo da imamo tabelu koja se zove DISCIPLINARY_ACTION koja je kreirana pomoću sledeće naredbe:

CREATE TABLE disciplinary_action (action_id int not null, employee_id int not null, comments char(500))

Ta tabela sadrži rezultate disciplinskih mera (disciplinary actions) koje su sprovedene nad zaposlenima u kompaniji. Primetićete da ne sadrži nikakve druge podatke o zaposlenima osim njihovog ličnog broja. Lako je zamisliti mnogobrojne situacije u kojima biste mogli da kombinujete informacije iz tabela DISCIPLINARY_ACTION i PERSONAL_INFO.

Pretpostavimo da ste dobili zadatak da napravite izveštaj u kome ćete popisati sve disciplinske mere sprovedene nad svim zaposlenima koji imaju platu veću od 40.000 dolara. Upotreba operacije JOIN, u tom slučaju, je jasna. Možemo prikazati te informacije pomoću sledeće naredbe:

SELECT personal_info.first_name, personal_info.last_name, disciplinary_action.comments
FROM personal_info, disciplinary_action
WHERE personal_info.employee_id = disciplinary_action.employee_id
AND personal_info.salary > 40000

Napisani kod određuje dve tabele koje želimo da spojimo u klauzuli FROM i zatim uključuje naredbu u klauzuli WHERE da bi ograničio rezultate samo na zapise koji imaju odgovarajuće lične brojeve zaposlenih i odgovaraju našem kriterijumu koji kaže da plata mora biti veća od 40.000 dolara.

Izraz INNER JOIN prikuplja informacije koje se pojavljuju u dve ili više baza podataka

Izrazi INNER JOIN su daleko najčešće korišćena vrsta izraza JOIN u SQL-u. Oni prikupljaju informacije koje se nalaze u dve ili više tabela baza podataka. Uslov spajanja (engl. join) određuje koji će se zapisi sparivati i određen je u naredbi WHERE. Na primer, ako vam je potreban spisak vozača uparenih sa vozilima kojima upravljaju, a da se, pri tom, vozači i vozila nalaze u istom gradu, sledeći upit u SQL-u će vam obaviti taj zadatak:

 

SELECT lastname, firstname, tag

FROM drivers, vehicles

WHERE drivers.location = vehicles.location

 

Rezultati koje ćete dobiti izgledaju ovako:

 

lastname      firstname       tag

———–     ————       —-

Baker          Roland          H122JM

Smythe       Michael         D824HA

Smythe       Michael         P091YF

Jacobs        Abraham       J291QR

Jacobs        Abraham       L990MT

 

Primetićete da smo dobili baš onakav rezultat kakav smo tražili. Moguće je stvoriti još precizniji upit određujući dodatne kriterijume u naredbi WHERE. Pretpostavimo da je prvobitni upit spojio vozače sa vozilima koja ne smeju da voze (vozače kamiona sa automobilima i obrnuto). Možete koristiti sledeći upit da biste rešili taj problem:

 

SELECT lastname, firstname, tag, vehicles.class

FROM drivers, vehicles

WHERE drivers.location = vehicles.location

AND drivers.class = vehicles.class

 

Ovaj primer precizno određuje izvornu tabelu za atribut klase u naredbi SELECT jer je klasa dvoznačna – pojavljuje se u obema tabelama. Kod obično određuje koju kolonu tabele treba obuhvatiti rezultatom upita. U ovom slučaju, ne pravi se razlika jer su kolone identične i povezane su korišćenjem spajanja po jednakosti (EQUIJOIN). Međutim, ako kolone sadrže različite podatke, ova razlika će biti od presudnog značaja. Prikazaćemo vam rezultate ovog upita:

 

lastname     firstname        tag           class

———-      ————       —-           ——

Baker           Roland         H122JM     Car

Smythe        Michael        D824HA    Truck

Jacobs         Abraham      J291QR      Car

 

U redovima koji nedostaju spojen je Michael Smythe sa automobilom i Abraham Jacobs sa kamionom, vozilima kojima ne smeju da upravljaju.

Izraze INNER JOIN možete koristiti da bi kombinovali podatke iz tri ili više tabela.

Struktuirani upitni jezik nudi korisnicima baza podataka moćan i prilagodljiv mehanizam prikupljanja podataka – naredbu SELECT. U ovom odeljku, upoznaćemo se sa opštom formom naredbe SELECT i zajedno ćemo napraviti nekoliko oglednih upita u bazi podataka. Ako je ovo prvi vaš pohod u svet struktuiranog upitnog jezika, možda bi bilo dobro da obnovite osnove SQL-a pre nego što nastavimo. Ako želite da kreirate bazu podataka od samog početka, najbolje bi bilo da naučite kako da kreirate baze podataka i tabele u SQL-u.

Pošto ste utvrdili osnove, hajde da počnemo sa istraživanjem naredbe SELECT. Kao i u prethodnim lekcijama o SQL-u, i ovde ćemo koristiti naredbe koje su u skladu standardom ANSI SQL. Možda biste mogli ponovo da pregledate dokumentaciju vašeg sistema za upravljanje bazama podataka da biste utvrdili da li podržava napredne opcije koje mogu da povećaju efikasnost i delotvornost vašeg koda u SQL-u.

Opšta forma naredbe SELECT

Opšta forma naredbe SELECT izgleda ovako:

SELECT select_list

FROM source

WHERE condition(s)

GROUP BY expression

HAVING condition

ORDER BY expression

Prvi red naredbe upućuje SQL procesor da je ta naredba SELECT i da želimo da prikažemo informacije iz baze podataka. Deo select_list omogućava nam da odredimo vrstu informacije koju želimo da prikažemo. Naredba FROM u drugom redu određuje specifičnu tabelu ili tabele u bazi podataka, a naredba WHERE pruža nam mogućnost da ograničimo rezultate na one zapise koji zadovoljavaju utvrđeni uslov ili uslove – condition(s).

Poslednje tri naredbe predstavljaju napredne odlike koje se nalaze van domena ovog odeljka – istražićemo ih u sledećim člancima o SQL-u.

Najlakše je savladati SQL proučavajući primere. U tom smislu, trebalo bi da počnemo sa pregledanjem nekih upita u bazama podataka. U ovom odeljku ćemo koristiti tabelu zaposlenih koja se nalazi u kadrovskoj službi zamišljene XZY komapanije da bismo ilustrovali sve naše upite.

Prikazaćemo celu tabelu:

 

EmployeeID	LastName	FirstName	Salary	ReportsTo
1	Smith	John	32000	2
2	Scampi	Sue	45000	NULL
3	Kendall	Tom	29500	2
4	Jones	Abraham	35000	2
5	Allen	Bill	17250	4
6	Reynolds	Allison	19500	4
7	Johnson	Katie	21000	3

 

Prikazivanje podataka iz cele tabele

Direktor kadrovske službe u XYZ kompaniji prima mesečni izveštaj o platama zaposlenih i sve potrebne informacije za svakog zaposlenog u kompaniji. Generisanje takvog izveštaja je primer najjednostavnije forme naredbe SELECT. Ona jednostavno prikazuje sve informacije koje su sadržane u tabeli u bazi podataka – u svakom redu i svakoj koloni. Napisaćemo upit koji dovodi do tog rezultata:

SELECT *

FROM employees

Prilično jednostavno, zar ne? Zvezdica (*) koja se pojavljuje u select_list je džoker koji se koristi da obavesti bazu podataka da želimo da prikažemo podatke iz svih kolona u tabeli zaposlenih identifikovanih u naredbi FROM. Pošto smo želeli da prikažemo sve podatke iz baze informacija, nije bilo potrebno da koristimo naredbu WHERE da bismo ograničili redove izabrane iz tabele. Ovako izgledaju rezultati našeg upita:

 

EmployeeID	LastName	FirstName	Salary	ReportsTo
———-	——–	———	——	———
1	Smith	John	32000	2
2	Scampi	Sue	45000	NULL
3	Kendall	Tom	29500	2
4	Jones	Abraham	35000	2
5	Allen	Bill	17250	4
6	Reynolds	Allison	19500	4
7	Johnson	Katie	21000	3

Izraz „relacioni“ koji koristimo kad govorimo o bazama podataka odnosi se na veze koje postoje između tabela.

Oni koji tek počnu da se bave bazama podataka veoma često ne mogu odmah da uoče razliku između baza podataka i tabelarnih proračuna. Uspevaju da vide tabele ispunjene podacima i shvataju da im baze podataka omogućavaju da organizuju i izvlače podatke na drugačije načine, ali ne uspevaju da prepoznaju značaj relacije između podataka zbog čega se tehnologija relacionih baza podataka tako zove.

Relacija vam omogućavaju da opišete veze između različitih tabela baza podataka na izuzetno moćan način. Te relacije mogu da se iskoriste za obavljanje naprednih međutabelarnih upita, koji su poznati kao JOINS, odnosno skup rezultata koji je dobijen povezivanjem informacija iz dve tabela.

Tipovi relacija u okviru baze podataka

Postoje tri različita tipa relacija u okviru baze podataka. Svaka relacija je dobila naziv prema broju redova u tabeli koji bi mogli da uđu u relaciju i svaki od ova tri tipa relacija pojavljuje se između dve tabele.

Relacije „jedan prema jedan“ se javljaju kad se svaki unos u prvoj tabeli uklapa sa jednim i samo jednim zapisom u drugoj tabeli. Relacije „jedan prema jedan“ se retko kad koriste jer je često mnogo jednostavnije staviti sve informacije u jednu tabelu. Neki kreatori baza podataka koriste prednosti ove relacije pri stvaranju tabela koje sadrže podskup podataka iz druge tabele.

Relacije „jedan prema više“ su najčešći tip relacija koji se koristi u bazama podataka. Javlja se onda kad se jedan zapis iz Tabele A uklapa sa jednim ili više zapisa iz Tabele B, ali se, pri tom, svaki zapis iz Tabele B uklapa samo sa jednim zapisom iz Tabele A. Na primer, relacija između tabele Teachers (Nastavnici) i tabele Students (Učenici) u bazi podataka osnovne škole predstavljao bi tip „jedan prema više“ jer svaki učenik ima samo jednog nastavnika, ali svaki nastavnik ima veliki broj učenika. Takav tip relacije „jedan prema više“ onemogućava pojavu dupliranja podataka.

Relacije „više prema više“ javljaju se kad se svaki zapis u Tabeli A poklapa sa jednim ili više zapisa u Tabeli B, a svaki zapis u Tabeli B poklapa se sa jednim ili više zapisa u Tabeli A. Na primer, relacije između tabele Teachers (Nastavnici) i tabele Courses (Predmeti) u osnovnoj školi verovatno će biti tipa „više prema više“ jer svaki nastavnik može da podučava više od jednog predmeta, a više od jednog nastavnika može da podučava jedan predmet.

Rekurzivne relacije: Poseban slučaj

Rekurzivne relacije se pojavljuju kad imamo samo jednu tabelu. Da bi se objasnio taj tip relacije obično se koristi primer tabele Employees (Zaposleni) koja sadrži informacije o šefu svakog radnika. Svaki šef je takođe radnik i ima svog šefa. U tom slučaju govorimo o rekurzivnoj relaciji „jedan prema više“ jer svaki radnik ima jednog šefa, a svaki šef može da ima više od jednog radnika.

Uspostavljanje relacija pomoću stranog ključa

Relacije između tabela uspostavljate pomoću definisanja stranog ključa. Taj ključ pokazuje relacionoj bazi podataka kakav odnos treba uspostaviti između tabela. U mnogim slučajevima, Tabela A sadrži primarne ključeve koji se referenciraju na Tabelu B.

Hajde ponovo da razmotrimo primer tabela Teachers (Nastavnici) i Students (Učenici). Tabela Teachers sadrži samo identifikacioni broj, ime i kolonu u kojoj se nalazi naziv predmeta (Course):

 

 

Tabela Students obuhvata identifikacioni broj, ime i kolonu u kojoj se nalazi strani ključ (Teacher_FK).

 

 

Kolona Teacher_FK u tabeli Students referencira vrednost primarnog ključa nastavnika u tabeli Teachers.

Veoma često kreatori baza podataka koriste “PK“ (primary key – primarni ključ) ili “FK” (foreign key – strani ključ) u nazivu tabele da bi lako prepoznali kolonu primarnog ili stranog ključa.

Obratite pažnju da ove dve tabele ilustruju relaciju „jedan prema više“ između nastavnika i učenika.

Relacije i referencijalni integritet

Kad ste tabeli dodali strani ključ, možete da kreirate ograničenje baze podataka koje uspostavlja referencijalni integritet između dve tabele, a to, s druge strane, održava relaciju između dve tabele nepromenljivom. Kad jedna tabela ima strani ključ druge tabele, koncept referencijalnog integriteta kaže da svaka vrednost stranog ključa u Tabeli B mora da se odnosi na postojeći zapis u Tabeli A.

Uspostavljanje relacija

U zavisnosti od toga kako ste kreirali bazu podataka, relacije između tabela možete uspostaviti na različite načine. Program Access kompanije „Majkrosoft“ vam obezbeđuje čarobnjaka koji vam jednostavno omogućava da povežete tabele i istovremeno vam nameće referencijalni integritet.

Ako pište neposredno u SQL-u, prvo ćete kreirati tabelu Teachers (Nastavnici) i kolonu ID ćete postaviti kao primarni ključ.

CREATE TABLE Teachers (

InstructorID INT AUTO_INCREMENT PRIMARY KEY,
Teacher_Name VARCHAR(100),
Course VARCHAR(100)
);

Kad kreirate tabelu Students (Učenici), postavljate kolonu Teacher_FK kao strani ključ koja se referencira na kolonu InstruktorID u tabeli Teachers.

CREATE TABLE Students (
StudentID INT AUTO_INCREMENT PRIMARY KEY,
Student_Name VARCHAR(100), Teacher_FK INT,
FOREIGN KEY (Teacher_FK) REFERENCES Teachers(InstructorID) )
);

Korišćenje relacija za spajanje tabela

Kad ste kreirali jednu ili više relacija u bazi podataka, možete iskoristiti njihove mogućnosti koristeći upite JOIN u SQL-u da biste kombinovali informacije iz većeg broja tabela. Najuobičajeniji tip povezivanja (JOIN) je SQL-ov INNER JOIN ili jednostavno povezivanje. Taj tip povezivanja prikazuje sve zapise koji zadovoljavaju povezan uslov iz većeg broja tabela. Na primer, uslov JOIN će prikazati Student_Name, Teacher_Name, i Course gde strani ključ u tabeli Student odgovara primarnom ključu u tabeli Teachers:

 

SELECT Students.Student_Name, Teachers.Teacher_Name, Teachers.Course

FROM Students

INNER JOIN Teachers

ON Students.Teacher_FK=Teachers.InstructorID;

Takav izraz kreira tabelu koja izgleda ovako:

 

Returned Table from the SQL Join Statement
Student_Name	Teacher_Name	Course
Lowell Smith	John Doe	English
Brian Short	John Doe	English
Corky Mendez	Jane Schmoe	Math
Monica Jones	John Doe	English

Bez obzira na to da li radite u bazi podataka koja sadrži stotine ili milione zapisa, uvek je izuzetno važno napraviti odgovarajuću koncepciju baze podataka. Osim toga što će vam u tom slučaju preuzimanje podataka biti mnogo jednostavnije, olakšaće vam i moguće buduće proširenje baze podataka. Nažalost, sasvim je lako da se uhvatite u nekoliko zamki koje će vam kasnije otežati život.

Mnogobrojne knjige na tržištu bave se temom normalizacije baza podataka, ali ako budete jednostavno izbegavali greške koje ćemo ovde spomenuti, bićete na pravom putu da uspostavite dobru koncepciju baze podataka.

Prva greška: Ponavljanje polja u tabeli

Osnovno pravilo koje treba poštovati pri stvaranju baza podataka je prepoznavanje ponovljenih podataka i premeštanje kolona koje se ponavljaju u posebnu tabelu. Ponavljanje kolona u tabeli karakteristično je za ljude koji su se uglavnom bavili tabelarnim proračunima, koji su dvodimenzionalni. S druge strane, baze podataka treba da budu relacione, što znači da se iz dvodimenzionalnog prostora prebacujete u trodimenzionalni.

Srećom, lako je uočiti kolone koje se ponavljaju. Pogledajte sledeću tabelu:

 

OrderID	Product1	Product2	Product3
1	Teddy Bears	Jelly Beans
2	Jelly Beans

Šta se dešava kad narudžbina sadrži četiri proizvoda? U tom slučaju potrebno je dodati još jednu kolonu tabeli da bi mogla da obuhvati više od tri proizvoda. Ako bismo za tabelu napravili klijent aplikaciju koja bi nam pomogla pri unosu podataka, možda bismo morali da je izmenimo dodajući novu kolonu za proizvod. Kako pronalazimo sve narudžbine Jellybeans-a u celoj narudžbini? Morali bismo da pravimo upit za svaku kolonu za proizvod u tabeli pomoću SQL-ove naredbe koja bi mogla da izgleda ovako: SELECT * FROM Products WHERE Product1=’Jelly Beans’ OR Product2=’Jelly Beans’ OR Product3=’Jelly Beans’.

Umesto pravljenja samo jedne tabele u koju ćemo natrpati sve informacije, trebalo bi da napravimo tri tabele. U tom slučaju, svaka će sadržati određenu vrstu informacija. U ovom primeru, trebalo bi da napravimo tabelu Orders (porudžbine) u kojoj će se nalaziti podaci o porudžbinama, zatim tabelu Product (proizvod) za sve naše proizvode i konačno, tabelu ProductOrders (naručivanje proizvoda) koja će povezati proizvod sa porudžbinom.

 

OrderID	CustomerID	Order Date	Total
1	7	1/24/17	19.99
2	9	1/25/17	24.99

 

ProductID	Product	Count
1	Teddy Bears	1
2	Jelly Beans	100

 

ProductOrderID	ProductID	OrderID
101	1	1
102	2	1

 

Obratite pažnju na to da svaka tabela ima svoje jedinstvenu kolonu identiteta (ID). To je primarni ključ. Tabele povezujemo koristeći vrednost primarnog ključa kao stranog ključa u drugoj tabeli.

Druga greška: Umetanje tabele u tabelu

Ovo je druga uobičajena greška, ali ne pojavljuje se toliko često koliko se pojavljuju ponovljene kolone. Kad pravite bazu podataka, želite da budete sigurni da svi podaci u tabeli pripadaju jednoj vrsti. To liči na onu dečiju igru u kojoj treba da uočite razlike. Ako imate bananu, jagodu, breskvu i televizor, verovatno je da televizor pripada nekoj drugoj grupi.

Slično tome, ako imate tabelu u kojoj se nalaze prodavci, sve informacije u toj tabeli treba da se odnose na određenog prodavca. Svaka dodatna informacija koja nije jedinstvena za tog prodavca verovatno pripada nekom drugom mestu u vašoj bazi podataka.

 

SalesID	First	Last	Address	PhoneNumber	Office	OfficeNumber
1	Sam	Elliot	118 Main St, Austin, TX	(215) 555-5858	Austin Downtown	(212) 421-2412
2	Alice	Smith	504 2nd Street, New York, NY	(211) 122-1821	New York (East)	(211) 855-4541
3	Joe	Parish	428 Aker St, Austin, TX	(215) 545-5545	Austin Downtown	(212) 421-2412

 

Iako vam se možda čini da se ova tabela odnosi na pojedinačnog prodavca, ona, ipak ima umetnutu tabelu. Obratite pažnju na ponavljanje u kolonama Office i OfficeNumber zajedno sa „Austin Downtown“. Šta bi se dogodilo ako bi se promenio broj telefona u kancelariji? Morali biste da ažurirate ceo skup podataka zbog promene samo jedne informacije, što nikada nije lak posao. Ta polja bi trebalo premestiti u posebnu tabelu.

 

SalesID	First	Last	Address	PhoneNumber	OfficeID
1	Sam	Elliot	118 Main St, Austin, TX	(215) 555-5858	1
2	Alice	Smith	504 2nd Street, New York, NY	(211) 122-1821	2
3	Joe	Parish	428 Aker St, Austin, TX	(215) 545-5545	1

 

OfficeID	Office	OfficeNumber
1	Austin Downtown	(212) 421-2412
2	New York (East)	(211) 855-4541

 

Ovakav koncept vam omogućava da dodate naknadne informacije tabeli Office, a da pri tom ne stvorite haos pretrpavajući tabelu prodavaca (sales person). Zamislite samo koliko bi naporno trebalo da radite kad bi stalno pratili podatke o adresi, gradu, državi i poštanskom broju ako bi sve te informacije bile u tabeli u kojoj se nalaze prodavci!

Treća greška: Unošenje dve ili više informacija u jednu kolonu

Umetanje podataka o kancelariji u tabelu za prodavce predstavlja problem ne samo u toj bazi podataka. Kolona za adresu sadrži tri informacije: naziv ulice, grada i države. Svaka kolona u bazi podataka treba da sadrži samo jednu informaciju. Kad imate više informacija u jednoj koloni, mnogo je teže postaviti upit za podatak u bazi podataka.

Šta će se dogoditi ako, na primer, postavite upit za sve prodavce iz Ostina? Trebalo bi da pretražujete u koloni adresa, što nije samo neefikasno, već vam može dati pogrešne rezultate. Konačno, neko bi mogao da živi u Ulici Ostin u Portlandu u državi Oregon.

Ovako bi trebalo da izgleda tabela:

 

SalesID	First	Last	Address1	Address2	City	State	Zip	Phone
1	Sam	Elliot	118 Main St		Austin	TX	78720	2155555858
2	Alice	Smith	504 2nd St		New York	NY	10022	2111221821
3	Joe	Parish	428 Aker St	Apt 304	Austin	TX	78716	2155455545

 

Ovde bi trebalo obratiti pažnju na to da kolone „Address1 i „Address2“ izgleda pripadaju već pomenutom ponavljanju kolona.

Međutim, u ovom slučaju one se odnose na različite informacije koje se direktno odnose na prodavce, a ne na ponovljene grupe podataka koje bi trebalo smestiti u posebnu tabelu.

Navešćemo još jednu grešku koju treba izbegavati u ovom slučaju. Obratite pažnju kako je napisan broj telefona. Trebalo bi da izbegavate skladištenje broja telefona napisanog u različitim oblicima kad god je to moguće. U slučaju brojeva telefona, ljudi koriste različite metode zapisivanja: 215-555-5858 ili (215) 555-5858, što bi otežalo pretraživanje prodavaca prema njihovom broju telefona ili prema istom pozivnom broju.

Četvrta greška: Korišćenje pogrešnog primarnog ključa

U većini slučajeva, želećete da koristite automatski brojčani priraštaj ili neki drugi generisani broj ili alfanumerički karakter za svoj primarni ključ. Ne bi trebalo da koristite neke konkretne informacije kao primarni ključ iako možda to zvuči kao odličan identifikator.

Na primer, svako od nas ima svoj jedinstveni matični broj i izgleda nam da bi bio odličan primarni ključ u bazi podataka zaposlenih. Iako se retko kad dešava, ipak, matični broj bi mogao da se promeni, a mi smo čvrsto odlučili da ne želimo da menjamo primarni ključ.

U stvari, želimo samo da napomenemo da pravu informaciju ne možemo da koristimo kao primarni ključ jer ona podleže promenama.

Peta greška: Izbegavanje konvencije imenovanja

Možda vam ovo ne izgleda kao ozbiljan problem pri samom stvaranju baze podataka, ali kad počnete da pravite upite za bazu podataka da biste prikupili informacije, konvencija imenovanja vam može pomoći da zapamtite nazive kolona.

Zamislite samo koliko bi ceo proces bio otežan kad biste u jednoj tabeli skladištili podatke kao FirstName, LastName, a u drugoj kao fisrt_name, last_name.

Dve najčešće korišćene konvencije imenovanja su pisanje velikog početnog slova u svakoj reči ili odvajanje reči donjom crtom. Neki programeri koriste drugačije načine imenovanja, recimo, sve reči počinju velikim slovom osim prve: firstName, lastName.

Sami odlučujete i o tome da li ćete koristiti jedninu ili množinu pri imenovanju tabele. Da li ćete napisati Order table ili Orders table pri imenovanju tabele je odluka koju donosite sami kao i u slučaju Customer table or Customers table. Ponovo vas podsećamo da ne napravite grešku i zaglavite se u tabelama koje se zovu Order table i Customers table, dakle da jednom koristite jedninu, a u drugom slučaju množinu.

Nije mnogo važno koju ćete konvenciju imenovanja izabrati, ali izuzetno je važno da se pridržavate konvencije koju ste izabrali.

Šesta greška: Neodgovarajuće indeksiranje

Indeksiranje je jedan od najtežih postupaka, naročito za one koji su prilično neiskusni u stvaranju i radu u bazama podataka. Svi primarni i strani ključevi moraju biti indeksirani jer se njima povezuju tabele. Dakle, bez indeksa, vaša baza podataka će veoma teško i jadno funkcionisati.

Međutim, obično se zaborave ostale kolone, a najčešće kolona „WHERE“. Ako ćete često sužavati pretragu koristeći kolonu u naredbi „WHERE“, onda bi trebalo da indeksirate i tu kolonu. Ne bi trebalo uvoditi mnogo indeksa jer će i to usporiti rad vaše baze podataka.

Koliko je dovoljno? Indeksiranje je deo umetnosti stvaranja baza podataka. Ne postoje stroga ograničenja u broju indeksa koje treba uvesti u tabelu. Prvenstveno treba indeksirati one kolone koje se često koriste u naredbi „WHERE“.

Struktuirani upitni jezik (SQL) je skup naredbi koje se koriste za rad sa relacionom bazom podataka. U stvari SQL je jedini jezik koga razume većina baza podataka. Kad god radite sa nekom bazom podataka, softver prevodi vaše naredbe (bez obzira na to da li ste kliknuli na dugme miša ili ste popunjavali obrazac) u naredbu u SQL-u koju baza podataka zna kako da protumači. SQL ima tri glavne komponente: jezik za manipulisanje podacima (DML), jezik za definisanje podataka (DDL) i jezik za kontrolu pristupa podacima (DCL).

Uobičajene upotrebe SQL-a na mreži

Kao korisnik bilo kog softverskog programa koga pokreću podaci, verovatno koristite SQL iako toga niste ni svesni. Na primer, dinamična veb-stranica koju pokreću baze podataka (kao i većinu veb-sajtova) preuzima unos korisnika kroz unose i klikove i koristi ih da bi stvorila SQL upit koji prikuplja informacije iz baze podataka potrebne da bi se stvorila sledeća veb-stranica.

Uzmimo primer jednostavnog kataloga na mreži koji ima i funkciju pretraživanja. Stranica za pretragu može da se sastoji od jednostavnog obrasca koji sadrži samo okvir za tekst u koji unosite pojam pretraživanja i, zatim, kliknete na dugme za pretraživanje. Kad kliknete na dugme, veb-server prikuplja sve zapise iz baze podataka proizvoda koji sadrže pojam pretraživanja i koristi dobijene rezultate da bi stvorio veb-stranicu koja je specifična za vaš zahtev.

Na primer, ako ste pretraživali proizvode koji sadrže pojam „Irish“, server bi mogao da iskoristi sledeći SQL izjavu da bi pretražio povezane proizvode:

SELECT *
FROM products
WHERE name LIKE ‘%irish%’

Drugim rečima, ova naredba pretražuje sve zapise iz tabele baze podataka koja se zove „proizvod“ i sadrži slova „irish“ bilo gde u nazivu proizvoda.

Jezik za manipulisanje podacima (DML)

Jezik za manipulisanje podacima (DML) sadrži podskup SQL naredbi koje se najčešće koriste – i to one koje jednostavno manipulišu sadržajem baze podataka u nekom obliku. Četiri najčešće korišćene naredbe ovog jezika su: SELECT, koja izvlači informacije iz baze podataka; INSERT, koja dodaje nove informacije bazi podataka; UPDATE, koja modifikuje informacije trenutno uskladištene u bazi podataka i DELETE, koja uklanja informacije iz baze podataka.

Jezik za definisanje podataka (DDL)

Jezik za definisanje podataka (DDL) sadrži naredbe koje se ređe koriste. Naredbe u ovom jeziku modifikuju trenutnu strukturu postojeću baze podataka, a ne njen sadržaj. Primeri uobičajeno korišćenih naredbi ovog jezika obuhvataju one koje se upotrebljavaju da bi se napravila nova tabela baze podataka (CREATE TABLE), menjaju definiciju tabele baze podataka (ALTER TABLE) i brišu tabelu baze podataka (DROP TABLE).

Jezik za kontrolu pristupa podacima (DCL)

Jezik za kontrolu pristupa podacima (DCL) koristi se da bi se upravljalo pristupu bazi podataka. Sastoji se od dve naredbe: naredba GRANT se koristi da bi se korisniku dodelio pristup bazi podataka i naredba REVOKE, koja se koristi da bi se oduzela postojeća dozvola pristupa. Ove dve naredbe predstavljaju srž modela obezbeđenja relacionih baza podataka.

Struktura naredbe u SQL-u

Srećom, posebno za nas koji nismo programeri, naredbe u SQL-u su napravljene tako da imaju sintaksu koja je veoma slična engleskom, odnosno, prirodnom jeziku. One uglavnom počinju imperativom koji nam objašnjava šta treba da uradimo. Posle njega sledi rečenica koja opisuje cilj prethodne naredbe (recimo, određenu tabelu u bazi podataka na koju se odnosi naredba) i, konačno, niz naredbi koje nam obezbeđuju dodatna uputstva.

Veoma često, kad jednostavno naglas pročitamo izjavu u SQL-u, sasvim nam je jasno šta naredba od nas traži da uradimo. Pročitajte sledeći primer izjave u SQL-u:

DELETE
FROM students
WHERE graduation_year = 2014

Možete li da pogodite šta će ova izjava uraditi? Pristupiće tabeli studenata u bazi podataka i izbrisaće sve zapise za studente koji su diplomirali 2014. godine.

Učenje programiranja u SQL-u

U ovom članku smo prikazali samo nekoliko jednostavnih primera u SQL-u, ali SQL je mnogo obimniji i moćniji jezik.

Ako radite sa bazama podataka izvesno vreme, sigurno ćete čuti termin normalizacija. Možda vas je neko i pitao da li je vaša baza podataka normalizovana ili da li ste koristili Bojs-Kodovu normalnu formu (BCNF). Normalizacija se uglavnom ne obavlja redovno jer se smatra da je to postupak mnogo primereniji teoretičarima i naučnicima nego praktičarima. Međutim, poznavanje principa normalizacije i njihova svakodnevna primena u bazi podataka zaista nije mnogo komplikovana i može izuzetno poboljšati funkcionisanje sistema za upravljanje bazama podataka.

Ovde ćemo vam predstaviti koncept normalizacije i ukratko ćemo objasniti najuobičajenije normalne forme.

Šta je normalizacija?

Normalizacija je proces efikasnog organizovanja podataka u bazi podataka. Proces normalizacije se sprovodi da bi se postigla dva cilja. Prvo, da bi se eliminisali suvišni podaci (na primer, skladištenje istih podataka u najmanje dve tabele) i drugo, da bi se obezbedio smisao zavisnosti podataka (skladištenje samo onih podataka u tabeli koji su u relaciji). Oba cilja su izuzetno važna i korisna jer smanjuju prostor koji zauzima baza podataka i obezbeđuju logičnu povezanost podataka koji se nalaze u tabeli.

Normalne forme

Stručnjaci za baze podataka stvorili su niz smernica čijom primenom se obezbeđuje normalizacija baza podataka. Ta uputstva se nazivaju normalne forme i obeležene su brojevima od jedan (najniža forma normalizacije naziva se prva normalna forma ili 1NF) do pet (peta normalna forma ili 5NF). U praktičnoj upotrebi su uglavnom 1NF, 2NF i 3NF i povremeno 4NF. Peta normalna forma se vrlo retko viđa i zbog toga je nećemo ovde razmatrati.

Pre nego što počnemo da vas upoznajemo sa normalnim formama, važno je da istaknemo da one predstavljaju smernice i samo smernice. Povremeno je potrebno odstupiti od njih da bi se zadovoljili praktični zahtevi poslovanja. Međutim, kad god dođe do nekih promena, izuzetno je važno da se procene svi njihovi mogući uticaji na sistem kao i da se utvrde razlozi za pojavu nedoslednosti. Sad bismo mogli da razmotrimo normalne forme.

Prva normalna forma (1NF)

Prva normalna forma (1NF) postavlja najosnovniji skup pravila za organizovanu bazu podataka:

·         Eliminisanje ponovljenih kolona u istoj tabeli.

·         Pravljenje posebnih tabela za svaku grupu podataka koji su u relaciji i identifikovanje svakog reda pomoću posebne kolone ili skupa kolona (primarnog ključa).

Druga normalna forma (2NF)

Druga normalna forma (2NF) još više smanjuje ponavljanje podataka:

·         Tabela mora da zadovoljava kriterijume prve normalne forme.

·         Treba ukloniti sve podskupove podataka koji se primenjuju na veći broj redova u tabeli i smestiti ih u posebne tabele

·         Treba napraviti relacije između tih novih tabela i njihovih prethodnika pomoću stranih ključeva.

Treća normalna forma (3NF)

Treća normalna forma nas vodi jedan veliki korak napred:

·         Tabela mora da zadovoljava kriterijume druge normalne forme.

·         Treba ukloniti kolone koje ne zavise od primarnog ključa.

Bojs-Kodova normalna forma (BCNF ili 3,5NF)

Bojs-Kodova normalna forma naziva se i „treća i po (3,5) normalna forma“ i dodaje još jedan zahtev:

·         Tabela mora da zadovoljava kriterijume treće normalne forme.

·         Sve determinante moraju biti kandidati za ključ.

Četvrta normalna forma (4NF)

Konačno, četvrta normalna forma ima još jedan dodatni element:

·         Tabela mora da zadovoljava kriterijume treće normalne forme.

·         Relacija je u četvrtoj normalnoj formi ako nema nijednu višeznačnu zavisnost.

Ne zaboravite da su smernice normalizacije kumulativne. Da bi baza podataka bila u drugoj normalnoj formi, prvo mora da zadovolji sve kriterijume baze podataka prve normalne forme.

Treba li da izvršim normalizaciju?

Iako je normalizacija baze podataka često odlična ideja, ipak nije neophodna. U stvari, postoje i neki slučajevi u kojima je namerno narušavanje pravila normalizacije ne samo prihvatljivo, već i preporučljivo.

Ako želite da budete sigurni da je vaša baza podataka normalizovana, obavestite se kako da postavite svoju bazu podataka u prvu normalnu formu.

Svaki administrator Microsoft SQL Server baze podataka trebalo bi da upravlja rastom baza podataka na odgovarajući način. Nijedan administrator ne bi želeo da mu program ne radi zbog toga što u bazi podataka nema više prostora, a to se može dogoditi na nekoliko različitih načina. Jedan od njih je postavljanje parametra MAXSIZE za bazu podataka. Kad se proširi do utvrđene MAXSIZE vrednosti, baza se više neće automatski proširivati. Ako u tom slučaju neko pokuša da bazi doda još neki red, prikazaće mu se poruka koja ga obaveštava o grešci kao i da je baza podataka puna.

Postoje sasvim opravdani razlozi da se odredi parametar MAXSIZE: ne želimo da fajlovi baze prilikom automatskih proširenja zauzmu disk do poslednjeg bajta. Takođe uglavnom ne želimo da manuelno proširujemo fajlove baze podataka. U praksi se najčešće postavlja opcija da se fajlovi Microsoft SQL Server baze automatski povećavaju, ali pomoću MAXSIZE ipak ograničavaju na određenu maksimalnu veličinu.

Pomoću ALERT sistema Microsoft SQL Servera jednostavno je poslati email notifikaciju administratoru kada određeni fajl baze podataka prekorači zadatu veličinu i priblliži se zadatoj MAXSIZE vrednosti.

Ako ste tek počeli da radite u Microsoft SQL Server okruženju ili ste nasledili novi server baze podataka, trebalo bi da razmotrite mogućnost nadgledanja baza podataka koje imaju postavljen parametar MAXSIZE.

Naredni jednostavni upit prikazuje sve fajlove svih baza podataka na Microsoft SQL Serveru koji imaju postavljeni MAXSIZE na neku vrednost:

— Datoteke baze podataka koje imaju postavljenu maksimalnu veličinu
SELECT db_name(database_id) DatabaseName,
type_desc,
name,
physical_name,
size,
max_size
FROM sys.master_files
WHERE
max_size <> -1 and max_size <> 268435456;

Svi znamo da su indeksi važni za ubrzanje procesiranja upita, ali da bi SQL Server skladištio i održavao indekse, potrebni su mu prostor na disku i računarski resursi. Ako imate indekse koji se ne koriste, oni vam troše resurse i dovode do toga da vam INSERT UPDATE i DELETE naredbe traju duže jer se tom prilikom moraju ažuriraju i odgovarajući indeksi. Ako ni jedna aplikacija, odnosno upit koji one pokreću, ne koriste dati indeks onda on nije ni potreban.

Povremeno bi trebalo da pregledate statistiku korišćenja indeksa na Microsoft SQL Serveru da biste utvrdili kako se koriste. To možete da uradite koristeći sistemski DMV (Microsoft SQL Server Dynamic Management View) sys.dm_db_index_usage_stats. Taj view će vam omogućiti da vidite da li su indeksi korišćeni u pretraživanju, sortiranju i drugim SQL Server operacijama, kao i da li su indeksi ažurirani. Ne zaboravite da view prate informacije od momenta pokretanja Microsoft SQL Server servisa i zbog toga bi trebalo da koristite ovaj DMV tek pošto je Microsoft SQL Server neprekidno bio u funkciji izvesno vreme.  Na ovaj način ćemo dobiti precizniju sliku o upotrebi indeksa.

Korišćenjem SQL Server sys.dm_db_index_usage_stats možete identifikovati indekse koji nikad nisu bili korišćeni. U navedenom kodu dalje u tekstu postoje dve naredbe SELECT sa operatorom UNION između njih. Prva naredba SELECT identifikuje one indekse koji nikad nisu bili korišćeni u vašoj bazi podataka, ali su redovno ažurirani tokom izmene podataka. Druga naredba SELECT identifikuje one indekse koji nikad nisu bili upotrebljeni niti ažurirani – drugim rečima, podaci u tabelama nad kojima su napravljeni ovi indeksi nisu menjani.

Trebalo bi da razmotrite povremeno pokretanje ovog upita da biste identifikovali indekse koji ne doprinose upitima na Microsoft SQL Serveru, a pri tome bespotrebno zauzimaju prostor na disku i računarske resurse. Kad otkrijete koji se indeksi ne koriste, možete utvrditi koji su vam potrebni, a koji nisu.

— Indeksi koji su ažurirani, ali nisu korišćeni

use YourDatabaseName

GO

select SCHEMA_NAME(o.schema_id) as [schema_name],

OBJECT_NAME(s.object_id) table_name,

i.name index_name,

s.user_seeks,

s.user_scans,

s.user_lookups,

s.user_updates,

‘yes’ Index_updated_but_not_used

from sys.dm_db_index_usage_stats s

join sys.objects o

on s.object_id = o.object_id

join sys.indexes i

on s.index_id = i.index_id

and s.object_id = i.object_id

where

(s.user_seeks = 0

and s.user_scans = 0

and s.user_lookups = 0)

and OBJECTPROPERTY(o.object_id,‘IsUserTable’) = 1

UNION

— indeksi koji se ne koriste i ne ažuriraju se jer se podaci ne menjaju

SELECT

SCHEMA_NAME(o.schema_id) as [schema_name],

OBJECT_NAME(o.object_id) table_name,

i.name index_name,

0  as user_seeks,

0 as user_scans,

0 as user_lookups,

0 as user_updates,

‘no’ as Index_updated_but_not_used

FROM

sys.indexes i

JOIN

sys.objects o

on i.object_id = o.object_id

WHERE  i.index_id NOT IN (

SELECT s.index_id

FROM sys.dm_db_index_usage_stats s

WHERE s.object_id = i.object_id

AND s.index_id = i.index_id

AND s.database_id =  DB_ID(DB_NAME()))

and OBJECTPROPERTY(o.object_id,‘IsUserTable’) = 1

order by Index_updated_but_not_used desc;

Kad postavljate novu Microsoft SQL Server mašinu, treba da odredite veličinu sistemske tempdb baze podataka. Ipak, to nije lak zadatak. Ako joj odredite suviše malu veličinu, u jednom momentu će doći do automatskog povećavanja kako podataka (mdf i ndf data fajlova), tako i datoteka promena (ldf log fajlova)  jer je bazi tempdb potrebno više prostora.

Sistemska baza tempdb je izuzetno značajna za optimalan rad Microsoft SQL Servera, jer je koristi kao privremeni skladišni prostor prilikom izvršavanja kompleksnih upita i za raznorazne sistemske operacije – na primer REBUILD indeksa, kreiranja snapshot-a podataka i slično. SQL server tempdb bazu takođe možemo i mi koristiti za kreiranje lokalnih i globalnih privremenih objekata (tabela, view, stored procedura, itd…) čija imena počinju sa # ili ##.

Microsoft SQL Server će uvek pokušati da tempdb bazu podigne u memoriju kako bi ubrzao rad. Ova baza se svaki put iznova rekreira prilikom restartovanja Microsoft SQL Server servisa i zbog toga nema svrhe raditi njen backup.

Svaki put kad se tempdb proširi, Microsoft SQL Server mora nakratko da zaustavi ili makar uspori rad dok sistem proširuje fajlove tempdb baze. To znači da transakcije u programu moraju da čekaju dok se ne završe događaji automatskog proširenja. S druge strane, ako je vaša baza podataka tempdb suviše velika, onda uzalud trošite prostor na disku.

Da biste bili sigurni da je sistemska Microsoft SQL Server tempdb baza odgovarajuće veličine, trebalo bi da pratite kako ona koristi prostor. Ako se pojave događaji automatskog proširenja pošto ste pokrenuli SQL Server, verovatno ćete se odlučiti za povećanje veličine fajlova tempdb podataka. Ako tempdb nikad ne koristi većinu tempdb prostora, možda bi trebalo da razmislite o smanjenju veličine baze.

Da biste promenili veličinu tempdb baze, možete koristiti naredbu ALTER DATABASE. Ovu naredbu možete da upotrebite i pri određivanju početne veličine tempdb podataka i/ili log fajla. U daljem tekstu je prikazan primer u kome je promenjena početnu veličinu tempdb DATA i LOG datoteke:

ALTER DATABASE tempdb
MODIFY FILE (Name=tempdb_data, filesize = 100MB),
MODIFY FILE (NAME=tempdb_log , filesize = 20MB);
Do promene navedenih veličina neće doći sve dok ne restartujete Microsoft SQL Server servis.

Microsoft SQL Server većinu informacija o konfiguraciji, korisnicima, objektima i generalno meta podacima čuva u sistemskim tabelama. Ako jednostavno i brzo treba dobiti ovakve informacije jedan od načina je zadavanje upita odgovarajućim sistemskim tabelama.

 

Microsoft SQL Server razlikuje dve grupe sistemskih tabela:

• sistemske tabele sistemskog kataloga – tabele koje sadrže informacije o objektima i konfiguraciji SQL Servera i ne odnose se ni na jednu specifičnu bazu podataka.
  U ranim verzijama SQL Servera su se te tabele nalazile u master i msdb sistemskim bazama podataka. Sada su uglavnom zamenjene View objektima i pseudo tabelama koje apstrahuju podatke i omogućavaju kompatibilnost na dole. Delom se zahvataju i podaci iz skrivene read-only Resource sistemske baze podataka
• sistemske tabele database kataloga – kao što ime sugeriše, ove tabele sadrže metapodatke koje se odnose na konkretnu bazu podataka. Svaka baza podataka ima ove sistemske tabele, uključujući master i ostale sistemske baze podataka

 

Tipično se sistemske tabele i view objekti koji ih apstrahuju nalaze u sys šemi u okviru master baze i dbo šemi msdb baze. U novijim verzijama ovi view objekti su implementirani kao pseudo tabele koje se kreiraju po zahtevu – slično in memory view objektima.

 

 

View i apstrakcija podataka iz sistemskih tabela

 

Za demonstraciju gore navedenog ćemo uzeti sistemsku tabelu sistemskog kataloga sys.syslogins

Ova tabela sadrži sve logine Microsoft SQL Servera i ovo vidimo ako zadamo jednostavan upit:

 

USE master

GO

SELECT *

FROM sys.syslogins

 

Brzim uvidom u rezulatat upita jasno je da imamo informacije o imenu logina (SQL login ili Windows login), datum kreiranja, hash šifre (samo kod SQL logina), default bazu podataka i ostale informacije.

 

Ako dublje uđemo u strukturu ove sistemske tabele, videćemo da je to zapravo View:

 

USE master

GO

sp_helptext ‘sys.syslogins’

 

Sistemska stored procedura sp_helptext vraća skript koji je kreirao zadati objekat, u ovom slučaju sys.syslogins. Iz rezultata upita jasno je da je sys.syslogins view:

CREATE VIEW sys.syslogins

AS

SELECT

sid = p.sid,

…

FROM klauzula pokazuje da se view referiše na tabele sys.server_principals i master.sys.sysprivs

Kada dalje probamo sa sledećim:

sp_helptext ‘sys.server_principals’

 

Vidimo da je i ovo view, a ne fizička tabela. View se referiše na više tabela master.sys.sysxlgns, sys.syspalnames i tako dalje…

 

Ako nastavimo dalje u dubinu dobićemo poruku 208 Invalid object name. Sada ulazimo u zabranjenu zonu sistemskih tabela koje ne može da vidi ni sistem admin Microsoft SQL servera.

 

Još par primera sys tabela sistemskog kataloga SQL Servera, (probajte svaku sa SELECT * FROM …, biće odmah jasno šta sadrže):

master.sys.configurations

master.sys.databases

master.sys.sysmessages

 

 

Sistemske tabele database kataloga sadrže metapodatke specifične za svaku bazu podataka. One su smeštene u mdf fizičkom fajlu te baze i njihova struktura, a ponegde i podaci se direktno kloniraju iz model sistemske baze Microsoft SQL servera.

 

Par primera:

USE TSQL — ili bilo koja druga baza uključujući i sistemske

GO

SELECT *

FROM sys.sysfiles

Lista fizičkih fajlova od kojih se sastoji baza, njihova imena, fizičke lokacije i svojstva

 

SELECT *

FROM sys.sysobjects

Lista svih objekata u datoj bazi podataka – table, view, stored procedure,…

Kolona xtype označava tip objekta: S – sistemska tabela, U – korisnička tabela, V – view, P – stored procedura, i tako dalje.

 

SELECT *

FROM sys.sysusers

Svi database korisnici.

 

SELECT *

FROM sys.types

Svi tipovi podataka koje možemo koristiti prilikom kreiranja tabela i/ili pisanja sql skripti.

Na prvi pogled je neobična odluka da raspoloživi tipovi podataka postoje na nivou baze podataka, a ne na nivou Microsoft SQL Servera u tabeli sistemskog kataloga. Međutim setite se da je moguće kreirati korisničke definisane tipove podataka (User Defined Data Types) koji su upravo na nivou baze i onda ova odluka ima smisla.

 

Autor: M.Sc. Miloš Milosavljević