MANUEL RADOVANOVIĆ : Relacije između tabela u bazama podataka

Relationships – relacije, veze ili sve češće se nazivaju zavisnosti su u suštini doprinos integritetu bazama podataka. U matematici su relacije podskup; koji nije prazan; Dekratovog proizvoda skupova i zbog toga se naziv relacije manje upotrebljavaju ukoliko ne pričate sa strancima. Inače, jedno je sigurno, relacije u matematici i u MSSQL-u su dve različite stvari iako imaju sličnosti. Umesto recimo da pišete sve podatke u jednu tabelu, vi možete napraviti više tabela i povezati ih relacijama. To ima ogromne prednosti kad je u pitanju integritet podataka jer pre svega na taj način minimizirate prazna polja u tabelama i ponavljanje istih. Najbolji administratori baza podataka ali i programeri imaju jednu zlatno pravilo. “Jednom unesi podatke, ali ih koristi koliko god hoćeš puta.“ Misli se na podatke u bazama podataka. Npr. zamislite da imena i prezimena klijenata morate da pišete u više tabela; to bi vam oduzimalo previše vremena dok bi vaše tabale bile robusnije. Zahvaljujući relacijama između tabela vi faktički jednom unesete ime i prezime klijenta i tu informaciju koristite u drugim tabelama gde su te informacije potrebne. Na taj način manje kucate na tastaturi, posao obavljate brže i profesionalnije, vaše tabele su manje, manje je praznih polja u tabelama, manje ponavljate iste podatke dok je vaša baza podataka lakša za održavanje i skladištenje na hosting serverima bez obzira što kreirate više tabela. Naravno, na taj način olakšavate i život programerima i svima koji koriste vašu bazu podataka. Zbog toga je mnogo bitno iskustvo i talenat znati što bolje i preciznije poslovnu logiku pretočiti u tabele i veze između njih. Postoje baze podataka poput Northwind ili Advantureworks koji služe kao najbolji primer i za relacije. Ukoliko ste početnik u pravljenju baza podataka obavezno instalirajte ove baze podataka za primer i učenje. Za početak pogledajte relacije između tabela u ovim bazama podataka. Kao programeri ne možete uvek računati da će se administratori baza podataka baviti kreiranjem vaših tabela. Vi to morate znati sami da radite.

( Relacije se najlakše kriraju kroz alatku Database Diagram )

Relacije između tabela mogu biti:

One to one – jedan prema jedan – 1:1
One to many – jedan pram više - 1:N
Many to many – N:N

Nije svejedno koju relaciju će te uspostaviti između tabela. Postoje jasna pravila kad se koristi koja relacija. Za razliku od Microsoft Access-a aplikacije za upravljanje relacionom bazom podataka; u Microsoft SQL Server-ima se održavanje i kreiranje relacija koristi isključivo alatkom Database Diagrams. Zato je neophodno da uvek imate u bazi kreiran barem jedan dijagram preko koga se mogu videti sve tabele i njihove relacije ili više njih koncentrisani na određenu poslovnu logiku. Kako se pravi dijagram, kako se dodaju u njega tabele, kako se prave, menjaju ili brišu relacije možete videti u sledećim primerima u nastavku. Naravno možete praviti relacije između tabela i sa T-SQL jezikom baza podataka koje takođe možete videti u nastavku.

Relacija jedan prema jedan

Relacija one-to-one – jedan prema jedan je relacija između tabela u kojoj se kolona jedne tabele povezuje sa kolonom druge tabele gde su podaci isti. To znači da obadve tabele moraju imati kolone koje imaju iste podatke. Ili još lakše rečeno, to vam je isto kao kad hoćete jednu tabelu da podelite na dve tabele. Relacija jedan prema jedan se retko koristi osim u specijalnim slučajevima. Ukoliko se bavite analizom ogromnih podataka ili testovima kao što to radi NASA; u tom slučaju jedna vaša tabela može da ima čak i više od 1024 kolone što je maksimum kolona koje možete imati u jednoj tabeli. Tada morate napraviti dve tabele i povezati ih relacijom jedan prema jedan da obe tabele predstavljaju jednu tabelu. Drugi slučaj je recimo ako radite sa mnogo klijenata i u tabeli imate polja poput Ime, SrednjeIme i Prezime. Pretpostavimo da mnogi vaši klijenti nemaju srednje ime jer su iz Srbije; tada će u vašoj tabeli kolona SrednjeIme biti poprilično prazna. To znači i da će vaša tabela klijenata imati dosta praznih polja. Da bi ste to izbegli bolje je da umesto jedne tabele klijenti imate još jednu koju će te nazvati SrednjaImena i povezati je relacijom jedan prema jedan. Svaki klijent može imati jedno srednje ime i svako srednje ime u tabeli pripada jednom klijentu. Na ovaj način će te imati mnogo bolje performanse. I treći jedan od najvažnijih razloga što bi imali tabele spojene jedan prema jedan relaciji jeste iz bezbednosnih razloga. Neke sigurnosne informacije nikada ne stoje u istoj tabeli sa klijentima ili zaposlenima. Zato najbolje da napravimo jedan primer kako se koristi relacija jedan na jedan zbog sigurnosnih razloga. Pokrenite SSMS – Microsoft SQL Server Management Studio, konektujte se na vaš MSSQL server. Kliknite na dugme New Query u Toolbar-u; zatim kliknite na dugme Save i sačuvajte *.sql datoteku na hard disku. Prvo ćemo napraviti bazu podataka i nazvaćemo je Corporation. Otkucajte sledeći code, selektujte ga i pokrenite pritiskom na dugme !Execute.

-- Create Corporation database, tables and one-to-one relationship between tables
-- 12-Mar-2020 The script by Manuel Radovanovic

-- Create database Corporation
USE master
CREATE DATABASE Corporation
GO

Zatim napravimo dve tabele; jednu za zaposlene Employees i jednu za sigurnosne podatke o zaposlenima Security.

-- Create table Employees
USE Corporation
GO
CREATE TABLE Employees
(
ID_Employee int PRIMARY KEY IDENTITY NOT NULL,
FirstName nvarchar(20),
LastName nvarchar(25)
)

-- Create table Security
USE Corporation
GO
CREATE TABLE [Security]
(
ID_Security int PRIMARY KEY,
[Password] nvarchar(20),
Employee int UNIQUE
)

Ukoliko vam code nije jasan, pročitajte ovaj post koji vas uči kako se prave tabele. Za razliku od primarnog ključa tabele Employees čiji je primarni ključ IDENTITY; znači svojstvo Identity Specification mu je podešeno na Yes; znači automatski pravi ID uvećan za jedan kod dodavanja novih zaposlenih; u tabeli Security to nije slučaj. Možda se pitate, zašto su nazivi u tabeli Security nekih kolona u velikim zagradama [ ] ? Zato što tabela Security koristi identične nazive kao ključnih reči Microsoft SQL Server-a. Ove zagrade takođe koristite kada imate više reči u nazivu kolone i hoćete da su odvojene. Npr. SecurityNumber odvojeno se može napisati ovako [Security Number]. Tada će vam naziv kolone biti čitljivije ispisan u tabeli Security Number. To neće uticati na pravljenje relacije. Ja to nisam radio jer imam naviku da to ispravljam u programerskom code-u gde se takav prikaz očekuje prikazati krajnjem korisniku. Sad pravimo relaciju jedan prema jedan između navedenih tabela i kolona ID_employee i Employee.

-- Create relationship one-to-one between tables

USE Corporation

ALTER TABLE [Security]

ADD CONSTRAINT FK_Security_Employess

FOREIGN KEY (Employee) REFERENCES [Employees](ID_Employee)

Kako izgledaju spojene tabele, pogledajte na sledećoj slici.

( One-to-one relacija spajanja tabela )

Kada smo spojili tabele relacijom jedan na jedan možemo da ubacimo i nešto podataka u tabele kako bi smo testirali naše tabele.

-- Insert some data into Employee table

USE Corporation

INSERT INTO Employees

VALUES ('Manuel','Radovanovic'),

('Demi','Moore')

-- Insert some data into Security table

USE Corporation

INSERT INTO [Security]

VALUES (101,'1234',1),

(102,'4321',2)

Dodajte i ovaj dodatni kod da vidite rezultat:

SELECT * FROM Employees
SELECT * FROM [Security];

Možda ne znate šta je Views u Microsoft SQL Server-u, o tome ću pisati sledeći post vezan za SQL ali za sada ćemo ga ipak koristiti kako bi videli rezultate našeg rada. Pogledajte sledeću sliku.

( Views u Microsoft SQL Server-u )

Kako to sve izgleda možete pogledati i na dva video-a. Prvi video pokazuje kako se pravi relacija jedan prema jedan lakše i bez upotrebe T-SQL jezika baza podataka dok drugi video prikazuje upravo što smo prethodno radili. Pogledajte video-a:

( SQL Tutorial - 10. How To Make the Relationship One-To-One using SSMS )

( SQL Tutorial - 11. How To Make the Relationship One-To-One using T - SQL )

Relacije jedan prema više

Relacije one-to-many – jedan prama više su relacije koje će te najčešće koristiti od svih drugih relacija. Relaciju jedan prema više koristite kada imate potrebu da jedna stavka u jednoj tabeli može imati više stavki u drugoj tabeli istih kolona. Postoji milion primera u poslovnoj logici kada se ovaj vid relacija koristi. Npr. svaki zaposleni ili klijent može imati više brojeva telefona, veb sajtova ili email-ova; svaki klijent može imati više narudžbi ili kupiti više proizvoda. Jedino je bitno da osoba ili stavka u tabeli koja je jedinstvena bude ili ima primarni ključ sa kojim se povezuje na drugu tabelu. U sledećem primeru kreiramo novu Corporation bazu podataka. Ukoliko želite da sačuvate prethodnu, možete ovu nazvati drugim imenom.

-- Create the database Corporation

USE master

CREATE DATABASE Corporation

Ovaj put kreiramo dve tabele Companies i CompanyPhones. U praksi vi možete kod klijenta imati samo jedan broj telefona koji je validan, ili samo jedan email jer vam možda te informacije nisu toliko bitne ali kada govorimo o korporaciji i kompanijama tada definitivno trebate imati više brojeva telefona. Zato u ovom slučaju koristimo relaciju jedan prema više. Pogledajte sami koje kolone sadrže tabele u code-u.

-- Create the table Companies

USE Corporation

CREATE TABLE Companies

(

ID_Company int PRIMARY KEY IDENTITY NOT NULL,

Company nvarchar(30)

)

-- Create the table CompanyPhones

USE Corporation

CREATE TABLE CompanyPhones

(

ID_CompanyPhones int PRIMARY KEY IDENTITY NOT NULL,

Company int,

CompanyPhone varchar(15)

)

Povezujemo relacijom jedan prema više tabelu Companies i CompanyPhone tako da nam je primarni ključ kod kompanije dok u tabeli CompanyPhones mora postojati kolona koja je takođe istog tipa, u ovom slučaju Integer-a. Na ovaj način jedna kompanija može imati 2 147 483 647 brojeva telefona, vi možete sve njihove brojeve telefona smestiti u tabelu. Bez relacije jedan prema više bili bi ste ograničeni na samo jedan broj telefona svake kompanije u bazi podataka.

-- Create relationship one-to-many between Companies and CompanyPhone

USE Corporation

ALTER TABLE CompanyPhones

ADD CONSTRAINT FK_CompanyPhones_Company

FOREIGN KEY (Company) REFERENCES Companies(ID_Company)

Ubacimo nešto podataka u tabele:

-- Insert some data into Company table

USE Corporation

INSERT INTO Companies

VALUES ('McDonald''s'),

('Coca Cola')

-- Insert some data into Company table

USE Corporation

INSERT INTO CompanyPhones

VALUES (1,'012 345 6789'),

(1,'012 345 6800'),

(2,'987 654 3210')

Pogledajte sliku i kao što vidite McDonald’s ima dva broja telefona, dok kompanija Coca Cola ima samo jedan.

( Rezultat one-to-many relacije )

( SQL Tutorial - 12. How To Make The Relationship One-To-Many using SSMS )

( SQL Tutorial - 13. How To Make The Relationship One-To-Many using T-SQL )

Relacije više prema više

Relacija many-to-many više prema više je uobičajena situacija kada jedno dete može imati više roditelja dok jedan roditelj može imati više dece ili kad jedna narudžba može imati više proizvoda dok jedan proizvod može imati više narudžbi. Međutim, za kreiranje relacije više prema više nisu dovoljne dve tabele već se između njih mora kreirati treća tabela koja će povezivati obe vaše tabele. Takva tabela koja ima samo svrhu da omogući kreiranje relacije više prema više se naziva Junction table – tabela spajanja.

U sledećem primeru pravimo bazu podataka za knjižaru koju ćemo nazvati Bookstore.

-- Create the database Bookstore

USE master

CREATE DATABASE Bookstore

Zatim kreiramo tri tabele, Authors, Books i tabelu spajanja AuthorsBooks. Prema poslovnoj logici jedan pisac može napisati više knjiga ali i knjiga može imati više pisaca. Zato je ovo idealna situacija kada koristimo relaciju više prema više. Tabela o piscima sadrži podatke, tabela o knjigama sadrži podatke o knjigama ali tabela spajanja sadrži samo ključeve preko koje se tabele spajaju.

-- Create the table Authors

USE Bookstore

CREATE TABLE Authors

( ID_Author int PRIMARY KEY IDENTITY NOT NULL,

FirstName nvarchar(20),

LastName nvarchar(25)

)

-- Create the table Books

USE Bookstore

CREATE TABLE Books

( ID_Book int PRIMARY KEY IDENTITY NOT NULL,

Title nvarchar(50)

)

-- Create the table AuthorsBooks

USE Bookstore

CREATE TABLE AuthorsBooks

( ID_Author int,

ID_Book int

PRIMARY KEY (ID_Author, ID_Book)

)

Obratite pažnju da tabele pisaca i knjiga imaju primarne ključeve ali tabela spajanja nema što bi ste pomislili dva primarna ključa. Jedna tabela može imati samo jedan primarni ključ. Pa se možete pitati kako se ovo pravi da izgleda da tabela ima dva ključa. Pogledajte sliku.

( Jedan primarni ključ primljenjen na dve kolone )

Ovo vam je u stvari jedan primaran ključ ali obuhvata dve kolone. To se postiže tako što prvo selektujete dve kolone i onda dodate primarni ključ. Ili to uradite u T-SQL code-u kao što smo to mi uradili. Zatim kreiramo dve relacije koje spajaju i tabelu pisaca i tabelu knjiga preko tabele spajanja.

-- Create relationship many-to-many between Authors, Books and AuthorsBooks tables

USE Bookstore

ALTER TABLE AuthorsBooks

ADD CONSTRAINT FK_AuthorBooks_Authors

FOREIGN KEY(ID_Author) REFERENCES Authors(ID_Author)

USE Bookstore

ALTER TABLE AuthorsBooks

ADD CONSTRAINT FK_AuthorBooks_Books

FOREIGN KEY(ID_Book) REFERENCES Books(ID_Book)

Kako izgleda relacija više prema više u našem primeru pogledajte sliku:

( Relacija many-to-many prikazana u Database Diagrams prozoru )

Zatim unosimo podatke u sve tri tabele.

-- Insert some data into Authors table

USE Bookstore

INSERT INTO Authors

VALUES ('Manuel','Radovanovic'),

('Marrisa',' Powell')

-- Insert some data into Books table

USE Bookstore

INSERT INTO Books

VALUES ('C# for Beginners'),

('Microsoft SQL 2016 for Beginners')

-- Insert some data into AuthorsBooks table

USE Bookstore

INSERT INTO AuthorsBooks

VALUES (1,1),

(1,2),

(2,2)

Kada kreiramo View za naše tabele možete videti na slici rezultate. Manuel Radovanović je napisao dve knjige dok je Marrisa Powel napisala jednu. Knjigu C# za početnike je napisao Manuel Radovanović dok knjigu Microsoft SQL 2016 su napisali i Manuel Radovanović i Marrisa Powell. Zahvaljujući relaciji više prema više knjižara može da pretražuje knjige i prema piscima i prema knjigama dok je integritet podataka u bazi podataka sačuvan.