недеља, 17. септембар 2017.

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 the 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 the table Employees
USE Corporation
GO
CREATE TABLE Employees
(
       ID_Employee int PRIMARY KEY IDENTITY NOT NULL,
       FirstName nvarchar(20),
       LastName nvarchar(25),
       SecurityNumber int NOT NULL
)

-- Create the table Security
USE Corporation
GO
CREATE TABLE [Security]
(
       ID_Security int IDENTITY NOT NULL,
       SecurityNumber int PRIMARY KEY NOT NULL,
       [Password] varchar(20),
       Access tinyint 
)  

Ukoliko vam code nije jasan, pročitajte ovaj post koji vas uči kako se prave tabele. Ovde vidimo da je tabela za zaposlene jednostavna sa jednim primarnim ključem ali sadrži SecurityNumber kololonu koja  takođe identična postoji u tabeli Security koja sadrži primarni ključ. 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. Zašto? Zato što želimo da sigurnosni broj zaposlenog može biti isti i da kroz taj isti broj zaposleni ima pristup određenom spratu ili prostoriji. Međutim taj broj može biti samo jedan i svaka zaposlena osoba u kompaniji može imati samo jedan sigurnosni broj isti i sa istom šifrom. Za ulazna vrata u kompaniji ili prostoriji postoji isti nivo pristupa i ista šifra. Vrata nemaju različite šifre za svakog zaposlenog. Zbog tog razloga je u ovakvoj situaciji najbolje spojiti tabele jedan prema jedan relacijom. 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 SecurityNumber.

-- Create relationship one-to-one between Employees and Security
USE Corporation
GO
ALTER TABLE Employees
ADD CONSTRAINT FK_Security_Employess
FOREIGN KEY (SecurityNumber) REFERENCES [Security](SecurityNumber)
GO

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 Security table
USE Corporation
GO
INSERT INTO [Security]
VALUES (101,'1234',5),
       (102,'4321',1)
GO

-- Insert some data into Employee table
USE Corporation
GO
INSERT INTO Employees
VALUES ('Manuel','Radovanovic', 101),
       ('Demi','Moore', 102)
GO

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
Go

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
Go
CREATE TABLE Companies
(
             ID_Company int PRIMARY KEY IDENTITY NOT NULL,
             Company nvarchar(30)
)

-- Create the table CompanyPhones 
USE Corporation
Go
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
Go
ALTER TABLE CompanyPhones
ADD CONSTRAINT FK_CompanyPhones_Company
FOREIGN KEY (Company) REFERENCES Companies(ID_Company)
Go

Ubacimo nešto podataka u tabele:

-- Insert some data into Company table
USE Corporation
GO
INSERT INTO Companies
VALUES ('McDonald''s'),
       ('Coca Cola')
Go

-- Insert some data into Company table
USE Corporation
GO
INSERT INTO CompanyPhones
VALUES (1,'012 345 6789'),
       (1,'012 345 6800'),
       (2,'987 654 3210')
Go

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


( Rezultat one-to-many relacije ) 

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 - 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
GO

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
GO
CREATE TABLE Authors
(            ID_Author int PRIMARY KEY IDENTITY NOT NULL,
             FirstName nvarchar(20),
             LastName nvarchar(25)
)

-- Create the table Books
USE Bookstore
GO
CREATE TABLE Books
(            ID_Book int PRIMARY KEY IDENTITY NOT NULL,
             Title nvarchar(50)
            
)

-- Create the table AuthorsBooks
USE Bookstore
GO
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
GO
ALTER TABLE AuthorsBooks
ADD CONSTRAINT FK_AuthorBooks_Authors
FOREIGN KEY(ID_Author) REFERENCES Authors(ID_Author)
 
GO

USE Bookstore
GO
ALTER TABLE AuthorsBooks
ADD CONSTRAINT FK_AuthorBooks_Books
FOREIGN KEY(ID_Book) REFERENCES Books(ID_Book)
GO

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
GO
INSERT INTO Authors
VALUES ('Manuel','Radovanovic'),
      ('Marrisa',' Powell')
GO

-- Insert some data into Books table
USE Bookstore
GO
INSERT INTO Books  
VALUES ('C# for Beginners'),
      ('Microsoft SQL 2016 for Beginners')
GO

-- Insert some data into AuthorsBooks table
USE Bookstore
GO
INSERT INTO AuthorsBooks  
VALUES (1,1),
       (1,2),
       (2,2)
GO

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.  


( Rezultat many-to-many relacije između tabela Authors, Books i AuthorsBooks )

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 - 14. How To Make the Relationship Many-To-Many using SSMS )


( SQL Tutorial - 15. How To Make the Relationship Many-To-Many using T - SQL )