Dosta tečajeva koji obrađuju problematiku baza uzima kao glavni zadatak poslovanje videoteke. Iz tog razloga će se ovdje probati riješiti takav “problem”.

Što sve osnovno treba imati takva baza?

• evidencija članova
• evidencija objekata posudbe – nosača zapisa
• evidencija posudbi i povrata objekata

Koje podatke člana treba zapisati?

• ime i prezime
• adresa
• grad (videoteka je lokalnog karaktera i teško da će netko iz drugog grada doći posuditi kazetu)
• datum rođenja
• broj telefona fiksni i mobilni
• e-mail adresa
• porezni broj
• datum učlanjenja
• datum prestanka članstva

Koje podatke kazeta odnosno objekta posudbe treba zapisati?

• naziv filma – na lokalnom jeziku, u našem slučaju na hrvatskom
  
• naslov originala
• godina izdanja
• žanr
• podžanr – npr. akcijske komedije, horror komedije i sl.
• medij – DVD, BlueRay, VHS,…
• datum kada je neki medij došao u videoteku
• redatelj(i)
• glumci – glavni muški, ženski i nekoliko značajnijih sporednih

Ovdje ću ostaviti mogućnost dopune jer program se uvijek može proširiti u smjeru koji niti nakon pomnog planiranja nije planiran.

Prva skica tablica

MP_CLAN – član videoteke

• CLAN INT
• IME  VARCHAR(60)
• PREZIME VARCHAR(60)
• ADRESA VARCHAR(120)
• GRAD CHAR(10)
• DATUM_RODJENJA DATETIME
• TELEFON1 VARCHAR(60)
• TELEFON2 VARCHAR(60)
• POREZNI_BROJ VARCHAR(60)
• DATUM_UCLANJENJA DATETIME
• DATUM_PRESTANKA_CLANSTVA DATETIME

MP_GRAD

• GRAD CHAR(10)
• NAZIV VARCHAR(120)
• DRZAVA CHAR(3)

MP_DRZAVA

• DRZAVA CHAR(3)
• NAZIV VARCHAR(120)
• NAZIV_EN VARCHAR(120)
• VALUTA CHAR(3)

MP_VALUTA

• VALUTA CHAR(3)
• NAZIV VARCHAR(60)
• OZNAKA CHAR(3)

Kazete odnosno objekte posudbe treba rasšlaniti na podatke koji se odnose na sami film kao takav i na podatke koji se odnose na medij.

Recimo da ćemo nepromjenjive podatke zapisivati u tablicu

VD_NASLOV

• NASLOV INT
• NAZIV VARCHAR(120)
• NAZIV_EN VARCHAR(120)
• ZANR INT
• GODINA_IZDANJA INT

Žanrove moramo spremiti u posebnu tablicu

MP_ZANR

• ZANR INT
• NAZIV VARCHAR(60)

Ostaju nam osobe vezane uz film – redatelji, glumci, scenaristi,…

VD_OSOBA

• OSOBA INT
• NAZIV VARCHAR(120)
• NAZIV_EN VARCHAR(120)

Kako se jedna osoba u istom filmu može pojaviti na više funkcija (npr. Clint Eastwood kao redatelj i glumac), potrebno je imati i tablicu za to (1 redatelj, 2 scenarist, 3 glavna uloga, 4 sporedna uloga, 5 producent,…)

VD_OSOBA_TIP

• OSOBA_TIP INT
• NAZIV VARCHAR(60)

Sve to je potrebno kako bi se definirala tablica u koju ćemo moći povezati sve osobe bitne za taj film.

VD_NASLOV_OSOBA

• NASLOV INT
• OSOBA INT
• OSOBA_TIP INT

… nastavak slijedi …

Podaci tablice MP_MJERA će sadržavati podatke koji imaju šifru u slijedu, npr: 1 komad, 2 kilogram, 3 metar,…

CREATE PROCEDURE dbo.mp_mjera_insert
 @mjera            INT,
 @naziv            VARCHAR(60),
 @oznaka            VARCHAR(5),
 @out_mjera        INT        OUT
AS
SET NOCOUNT ON

DECLARE    @err    INT,
 @pomerr    INT,
 @ima    INT

SET @err = -1

SET @pomerr = 1
EXEC @pomerr = dbo.mp_mjera_exists
 @mjera        = @mjera,
 @out_ima    = @ima        OUT
SET @err = @err + CASE @pomerr WHEN -1 THEN 0 ELSE 1 END

IF @ima = 0
 BEGIN
 IF @mjera = 0
 BEGIN
 SELECT    @mjera    = MAX(MJERA)
 FROM    MP_MJERA
 SET @err = @err + @@ERROR

 SET @mjera = ISNULL(@mjera, 0) + 1
 END

 INSERT    INTO    MP_MJERA (
 MJERA,
 NAZIV,
 OZNAKA)
 VALUES     (
 @mjera,
 @naziv,
 @oznaka)
 SET @err = @err + @@ERROR
 END
ELSE
 BEGIN
 UPDATE    MP_MJERA
 SET    NAZIV        = @naziv,
 OZNAKA        = @oznaka
 WHERE    MJERA        = @mjera
 SET @err = @err + @@ERROR
 END

SET @out_mjera    = @mjera

RETURN @err
GO

U tablici MP_MJERA se nalaze mjerne jedinice kao npr. komad, kilogram, pakiranje, metar, sat i sl.

Šifre mjernih jedinica nisu zadane te će se ovdje pokazati kako zapisivati takve podatke.

CREATE TABLE [dbo].[MP_MJERA] (
 [MJERA] [int] NOT NULL ,
 [NAZIV] [varchar] (60) COLLATE Croatian_CI_AS NOT NULL ,
 [OZNAKA] [varchar] (5) COLLATE Croatian_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MP_MJERA] ADD
 CONSTRAINT [PK_MP_MJERA] PRIMARY KEY  CLUSTERED
 (
 [MJERA]
 )  ON [PRIMARY]
GO

..

U proceduri mp_valuta_exists je bio ovaj dio koda:

SELECT    @out_ima    = COUNT(VALUTA)
 FROM    MP_VALUTA
 WHERE    VALUTA    = @valuta
SET @err = @err + @@ERROR

Dva načina za koji kažu da su bolji od ovo gore navedenog su:

1. Koristi se EXISTS za provjeru

IF EXISTS (SELECT VALUTA FROM MP_VALUTA WHERE VALUTA = @valuta)
 BEGIN
 SET @err = @err + @@ERROR
 SET @out_ima = 1
 END

2. Koristi se varijabla koja vraća broj zahvaćenih slogova, @@ROWCOUNT. Pomoćna varijabla @pom se koristi za prihvat podatka šifre varijable.

SELECT    @pom = VALUTA
 FROM    MP_VALUTA
 WHERE    VALUTA    = @valuta
SET @err = @err + @@ERROR
SET @out_ima = @@ROWCOUNT

Kako se radi o provjeri primarnog ključa, uvijek će broj vraćenih slogova biti 0 ili 1.

U ovoj proceduri nema pozivanja neke deuge procedure niti nema provjera. Vrlo je jednostavna, vraća podatke za traženu šifru valute.

CREATE PROCEDURE dbo.mp_valuta_select
 @valuta        CHAR(3),
 @naziv        VARCHAR(60)    = ''    OUT,
 @oznaka        CHAR(3)        = ''    OUT
AS
SET NOCOUNT ON

DECLARE    @err    INT
SET @err = -1

SELECT    @naziv        = NAZIV,
 @oznaka        = OZNAKA
 FROM    MP_VALUTA
 WHERE    VALUTA    = @valuta
SET @err = @err + @@ERROR

RETURN @err
GO

Jedan sažetak nakon rasprave na news grupi:

COUNT(*)

De facto standard za brojanje svih redaka u tablici.

COUNT(field) npr. COUNT(VALUTA)

Vraća broj redaka u tablici za koje polje field nema vrijednost NULL. Za polje koje je NOT NULL vraća broj redaka u tablici.

COUNT(const) npr. COUNT(1)

Uvijek vraća broj redaka u tablici.

Ova procedura ima dvojaku ulogu, zapisuje slog ukoliko se radi o novom zapisu i radi izmjenu sloga ako se radi o postojećem zapisu.

Zadatak procedure za zapis i iumjenu u našoj tablici MP_VALUTA je

• prihvat svih podataka potrebnih za zapis u tablicu
• provjera postojanja primarnog ključu pomoću procedure mp_valuta_exists
• ako je novi slog radi se INSERT, za postojeći slog se radi UPDATE
• u pomoćnu varijablu zapisati upješnost izvršenja radnje

Evo kako bi ta procedura izgledala:

CREATE PROCEDURE dbo.mp_valuta_insert
 @valuta        CHAR(3),
 @naziv        VARCHAR(60)    = '',
 @oznaka        CHAR(3)        = '',
 @insert        TINYINT,
 @postoji    TINYINT        OUT
AS
SET NOCOUNT ON

DECLARE @ima        INT,
 @err        INT,
 @pomerr        INT

SET @err = -1

SET @pomerr = 1
EXEC @pomerr = dbo.mp_valuta_exists
 @valuta        = @valuta,
 @out_ima    = @ima        OUT
SET @err = @err + CASE @pomerr WHEN -1 THEN 0 ELSE 1 END
SET @postoji = 0

IF @ima = 0
 BEGIN
 INSERT    INTO    MP_VALUTA (
 VALUTA,
 NAZIV,
 OZNAKA)
 VALUES    (
 @valuta,
 @naziv,
 @oznaka)
 SET @err = @err + @@ERROR
 END
ELSE
 IF @insert = 0
 BEGIN
 UPDATE    MP_VALUTA
 SET    NAZIV        = @naziv,
 OZNAKA        = @oznaka
 WHERE    VALUTA        = @valuta
 SET @err = @err + @@ERROR
 END
 ELSE
 SET @postoji = 1

RETURN @err
GO

Prihvaćaju se podaci u varijable

 @valuta        CHAR(3),
 @naziv        VARCHAR(60)    = '',
 @oznaka        CHAR(3)        = '',
 @insert        TINYINT,

rade se deklaracije pomoćnih varijabli, poziva se procedura mp_valuta_exists koja vraća @ima = 1 ako postoji slog sa istom šifrom valute odnosno @ima = 0 ako šifre nema.

Ako šifre nema, radi se INSERT podataka, a ako šifra postoji radi se UPDATE ukoliko smo to željeli (@insert = 0). Ako smo željeli zapisati novi slog (@insert = 1), a šifra valute je već zapisana, output varijabla @postoji će biti postavljena na 1 i neće se izvršiti niti INSERT niti UPDATE.

Prva procedura koju ću opisati je procedura za brisanje. Ona je najjednostavnija i na njoj se mogu pokazati neke stvari koje će se kasnije stalno ponavljati.

Zadatak procedure za brisanje sloga u našoj tablici MP_VALUTA je

• prihvat podataka šifre valute koju želimo obrisati
• obrisati slog koji ima traženu šifru valute
• u pomoćnu varijablu zapisati upješnost izvršenja radnje

Evo kako bi ta procedura izgledala:

CREATE PROCEDURE dbo.mp_valuta_delete
@valuta        CHAR(3)
AS
SET NOCOUNT ON

DECLARE    @err    INT
SET @err = -1

DELETE
FROM    MP_VALUTA
WHERE    VALUTA    = @valuta
SET @err = @err + @@ERROR

RETURN @err
GO

Varijabla @valuta prihvaća vrijednost šifre valute koju želimo obrisati.

Svaka SQL naredba vraća poruku o broju zahvaćenih slogova. U procedurama je taj podatak suvišan, a često i smeta te se koristi SET NOCOUNT ON kako se te poruke ne bi javljale.

Varijabla @err je tipa INT i dodijeljena je inicijalna vrijednost -1.

Izvršava se naredba DELETE nad tablicom MP_VALUTA za slog gdje je VALUTA = @valuta.

Odmah nakon izvršavanja naredbe DELETE postoji redak: SET @err = @err + @@ERROR.

@@ERROR je sistemska varijabla samog MS SQL Servera u koju se zapisuje rezultat izvršavanja posljednje SQL naredebe. Ako nije došlo do greške, @@ERROR je 0, a ako je bila greška, zapisuje se broj te greške koji je uvijek neki broj veći od nule. Bitno je vrijednost preuzeti nakon samog izvođenja naredbe jer se vrijednost prepisuje s novom vrijednošću nakon izvršavanja ideuće naredbe.

Analogno tome, ako nije došlo do greške na kraju vrjednost lokalne varijable mora biti -1. U slučaju bilo kakve greške vrijednost je veća od -1.

Na kraju se vraća vrijednost varijable @err. To nije output varijabla nego tzv. return value.

Jako je korisna kada se procedura poziva iz neke druge procedure.

Za svaku jednostavnu tablicu koristim 4 procedure. Jedna je za brisanje sloga (DELETE), druga za provjeru da li postoji slog za traženi PK, treća za zapis i ispravak podataka (INSERT / UPDATE) i četvrta je za vraćanje cijelog slog (SELECT).

Kako se daju imena?

Vrlo jednostavno. Za tablicu MP_VALUTA procedure se zovu

• mp_valuta_delete
• mp_valuta_exists
• mp_valuta_insert
• mp_valuta_select

Kao što se vidi, procedure se sastoje od naziva tablice nad kojom se odvija radnja i naziv radnje. Ovakva podjela je za veliku većinu procedura. Izvan toga pravila izlaze procedure koje rade složene poslove ali o njima nešto kasnije.

Naziv procedure je napisan malim slovima.

Store koristim za sve (dobro, skoro za sve) radnje nad podacima. Uz dobro odabrane nazive nije se teško snaći niti u tisućama procedura.