Az adatbázis célja egy menekültek ellátásával foglalkozó segélyszervezt mindennapos munkájának a támogatása. Ennek a sokrétű munkának a megszervezésében kulcsfontosságú a koordináció. Nevezetesen az, hogy a megfelelő személyek, a megfelelő helyen, kellő tárgyi felszereltséggel tudják a munkájukat végezni.
Elsősorban ennek a karítatív munkának a megszervezéséhez szükséges adatok kerültek letárolásra az adatbázisban:
- a segélyszervezet dolgozóinak legfontosabb személyes adatai, elérhetősége, szakterületük,
- a segélyszervezet munkáját segítő önkéntesek adatai, elérhetőségei, szakterületük,
- az adományozók legfontosabb adatai, elérhetőségei,
- a felajánlott adományok típusa, leírása, fellelhetősége,
- az ellátási helyek, azok típusai, pontos helyük, elérhetőségük.
A masterwork nevében szereplő 'határtalan' szó arra utal, hogy a segélyszervezet munkatársai, önkéntesei, ellátási pontjai és adományozói között is szép számban akadnak határontúliak.
- Microsoft SQL Server 2019 futtatására alkalmas operációs rendszer. Javasolt: Windows 10 Pro 10.0 (Build 19044:)
- Microsoft SQL Server 2019 Javasolt verzió: Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64))
- SQL Server Management Studio 15.0.18410.0
Két módon lehetséges.
- A csatolt Borderless_DbBackup.bak fájlból. (Ez az adatbázis már tartalmazza a mintaadatokat.)
- A csatolt sql fájlok sorrendben történő futtatásával. Ebben az esetben a felhasználó dönti el, hogy telepíti-e a mintaadatokat tartalmazó Datainsert.sql fájlt.
- Collation: SQL_Latin1_General_CP1_CI_AS
- Recovery model: Full
- Kompatibilitás szint: SQL 2019 (150)
Az adatbázis a dolgozók, önkéntesek, adományozók, adományok és ellátási helyek tulajdonságait leíró entitásokat tartalmazza.
Az adatbázist alkotó táblákat és a köztük lévő kapcsolatokat a dokumentum végén lévő diagram mutatja be. A táblák harmadik normálformában (3NF) kerültek megtervezésre.
A 01_Create_DB_Schema_tables.sql szkript futása során jön létre a Borderless adatbázis, az Aid séma és a 15 tábla. Mindegyik tábla legalább három indexet tartalmaz.
A segélyszervezet dolgozóinak adatait tartalmazza.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| EmployeeID | INT | Elsődleges kulcs, Clustered index | A dolgozó egyedi azonosítója | |
| EmpFirstName | VARCHAR(50) | A dolgozó I. keresztneve | ||
| EmpMidName | VARCHAR(25) | A dolgozó II. keresztneve | ||
| EmpLastName | VARCHAR(25) | Non-Unique, Non-Clustered index | A dolgozó családneve | |
| EmpSSN | VARCHAR(9) | Egyedi kulcs, Non-Clustered index | A dolgozó társadalomazonosító száma | |
| EmpTaxNumber | Varchar(9) | Egyedi kulcs, Non-Clustered index | A dolgozó adószáma | |
| EmpBirthDate | DATE | A dolgozó születési dátum | ||
| EmpDateOfEntry | DATE | A munkakezdés dátuma | ||
| EmpUserOfExit | DATE | A munka befejezésének dátuma | ||
| EmpWorkStatus | BIT | Non-Unique, Non-Clustered index | A dolgozó státusza (aktív vagy passzív) | |
| EmpOfficePhone | VARCHAR(20) | Check constraint* | A dolgozó hivatali telefonszáma | |
| EmpDepartmentID | INT | Idegen kulcs | 1:N | A dolgozó osztályájának azonosítója |
| EmpJobTitleID | INT | Idegen kulcs | 1:N | A munkakör azonosítója |
| EmpAddressID | INT | Idegen kulcs | 1:N | A dolgozó elérhetőségének azonosítója |
*Constraint: EmpOfficePhone VARCHAR (20) NOT NULL DEFAULT '3615554555' (DF__Employees__EmpOf__3E52440B)
A segélyszervezet belső részlegeit, osztályainak a megnevezését tartalmazza.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| DepartmentID | INT | Elsődleges kulcs, Clustered index | Az osztály egyedi azonosítója | |
| DepartmentName | NVARCHAR(50) | Egyedi kulcs, Non-Unique Non-Clustered index | Az osztály megnevezése | |
| Description | NVARCHAR(500) | Az osztály feladatkörének leírása |
A segélyszervezetnél dolgozók munkakörei.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| JobTitleID | INT | Elsődleges kulcs, Clustered index | A munkakör egyedi azonosítója | |
| JobTitleName | NVARCHAR(50) | Egyedi kulcs, Non-Unique Non-Clustered index | A munkakör megnevezése | |
| Description | NVARCHAR(500) | A munkakör leírása |
A segélyszervezetnél tevénykedő önkéntesek adatai.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| VolunterID | INT | Elsődleges kulcs, Clustered | Az önkéntes egyedi azonosítója | |
| VolFirstName | VARCHAR(50) | Az önkéntes I. keresztneve | ||
| VolMidName | VARCHAR(25) | Az önkéntes II. keresztneve | ||
| VolLastName | VARCHAR(25) | Non-Unique, Non-Clustered index | Az önkéntes családneve | |
| VolBirthDate | DATE | Az önkéntes születési dátuma | ||
| VolDateOfEntry | DATE | Az önkéntes belépési dátuma | ||
| VolDateOfExit | DATE | Az önkéntes kilépésének dátuma | ||
| VolStatus | BIT | Filtered, Non-Unique, Non-Clustered index | Az önkéntesség státusza: aktív vagy passzív | |
| VolAddressID | INT | Idegen kulcs | 1:N | Az önkéntes elérhetőségeinek azonosítója |
Szakterületek, speciális ismeretek, minden olyan szaktudás, amely a segélyszervezet munkáját segíti.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| SpecializationID | INT | Elsődleges kulcs, Clustered | Egyedi szakterület azonosító | |
| SpecializationName | VARCHAR(100) | Egyedi kulcs, Non-Unique, Non-Clustered index | A szakterület egyedi azonosítója | |
| Description | VARCHAR(500) | A szakterület leírása |
Ellátási helyek, ahol menekülteket fogadank, és ott hosszabb-rövidebb időt tölthetnek el.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| SupplyPlaceID | INT | Elsődleges kulcs, Clustered | Egyedi dolgozóazonosító | |
| PlaceTypeID | INT | Idegen kulcs, Clustered | 1:N | Az ellátási hely azonosítója |
| SupplyPlaceName | NVARCHAR(100) | Egyedi kulcs, Non-Unique, Non-Clustered index | Az ellátási hely neve | |
| Status | BIT | Az ellátási hely státsza (működik/nem működik) | ||
| SupplyPlaceAddressID | INT | Idegen kulcs, index | 1:N | Az ellátási hely címének azonosítója |
| Latitude | VARCHAR(20) | A hely szélességi koordinátája | ||
| Longitude | VARCHAR(20) | A hely hosszúsági koordinátája |
Megjegyzés: ézen a táblán a szélesség (Latitude) és a hosszúság (Longitude) adatok string-ként történő letárolása a későbbi felhasználásuk érdekében történt. Ugyanis az ellátási helyekre beosztott önkéntéseknek így akár e-mail-ben is átküldhető a szolgálatteljesítés helye, annak címe és koordinátái, amit akár a Google Maps szolgáltatásba, akár egy GPS-be bemásolva térképen is megjeleníthető válik a konkrét hely.
Az ellátási helyek típusai.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| TypeID | INT | Elsődleges kulcs, Clustered | az ellátási típushely egyedi azonosítója | |
| PlaceTypeName | NVARCHAR(50) | Egyedi kulcs, Non-Unique, Non-Clustered index | az ellátási típushely neve | |
| Description | NVARCHAR(500) | az ellátási hely leírása |
Az adományozó magánszemélyek és szervezetek neve.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| DonatorID | INT | Elsődleges kulcs, ClusteredClustered | Az adományozó egyedi azonosítója | |
| DonatorName | NVARCHAR(75) | Check Constraint*, Non-Unique Non-Clustered | Az adományozó neve | |
| DonatorCompanyName | VARCHAR(100) | Check Constraint*, Non-Unique Non-Clustered | Az adományozó szervezet neve | |
| DonatorAddressID | INT | Idegen kulcs, index | 1:N | Az adományozó címének azonosítója |
*Constraint:CONSTRAINT ChkDonatorName CHECK((DonatorName is not null) OR (DonatorCompanyName is not null))
A felajánlott adományok legfontosabb tulajdonságai.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| DonationID | INT | Elsődleges kulcs, Clustered | Az adomány egyedi azonosítója | |
| DonationGUID | uniqueidentifier | Check constraint* | Az adományok GUID azonosítója | |
| DonatorID | INT | Idegen kulcs, index | 1:N | Az adományozó egyedi azonosítója |
| DonationTypeID | INT | Idegen kulcs, index | 1:1 | Az adomány típusának azonosítója |
| DonationDate | DATE | Check constraint** | A felajánlás ideje | |
| DonationStatus | BIT | Non-Unique, Non-Clustered | Az adományok státusza: folyamatban(1) vagy teljesült(0) | |
| DonatorValue | MONEY | Check constraint***, Non-Unique, Non-Clustered | Az adományozó egyedi azonosítója | |
| Description | NVARCHAR(500) | Az adomány leírása | ||
| DonationAddressID | INT | Idegen kulcs, index | 1:N | Az adomány helyének egyedi azonosítója |
*Constraint: DonationGUID DEFAULT NEWID() : DF__Donations__Donat__5165187F **Constraint: CHK_DonationDate CHECK(DonationDate <= GETDAY ***Constraint: Chk_DonationValue CHECK(DonationValue >= 0)
Az adományok típusai.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| DonationTypeID | INT | Elsődleges kulcs, Clustered, Non-Unique, Non-Clustered | Az adománytípus egyedi azonosítója | |
| DonationTypeName | NVARCHAR(50) | Non-Unique, Non-Clustered | Az adománytípus neve | |
| Description | NVARCHAR(500) | Az adománytípus leírása |
A telefon, mobil, e-mail, cím és földrajzi koordináták összefogaló, központi táblája.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| AddressID | INT | Elsődleges kulcs, Clustered | A cím egyedi azonosítója | |
| MobilePhone1 | VARCHAR(12) | Egyedi kulcs, Unique, Non-Clustered | A címhez tartozó mobilszám(1) | |
| MobilePhone2 | VARCHAR(12) | A címhez tartozó mobilszám(2) | ||
| PrivatPhone | VARCHAR(12) | A címhez tartozó vezetékes telefonszám | ||
| VARCHAR(50) | Egyedi kulcs, Unique, Non-Clustered | A címhez tartozó e-mail-cím | ||
| CityID | INT | Idegen kulcs, Non-Unique, Non-Clustered | 1:N | A város egyedi azonosítója |
| Street | NVARCHAR(50) | A címhez tartozó közterület neve | ||
| HouseNr | SMALLINT | A címhez tartozó házszám | ||
| Geolocation | GEOGRAPHY | A címhez tartozó koordináták |
Az európai országok nevei, EU státuszuk.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| CountyID | INT | Elsődleges kulcs, Clustered | A város egyedi azonosítója | |
| CountryID | INT | |||
| CountryName | NVARCHAR(50) | Egyedi kulcs, Non-Unique, Non-Clustered | Az ország neve | |
| EuStatus | BIT | Non-Unique, Non-Clustered | EU tagállam-e az adott ország |
A megyék/kerületek/járások nevét tartalmazza.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| CountyID | INT | Elsődleges kulcs, Clustered | A megye egyedi azonosítója | |
| CountryID | INT | Idegen kulcs | 1:N | |
| CountyName | NVARCHAR(50) | Egyedi kulcs, Non-Unique, Non-Clustered | A megye, kerület, járás neve |
A városok neveinek táblája.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| CityID | INT | Elsődleges kulcs, Clustered | A város egyedi azonosítója | |
| CountyID | INT | Idegen kulcs | 1:N | |
| CityName | NVARCHAR(50) | Egyedi kulcs, Non-Unique, Non-Clustered | A város neve |
Kapcsolótábla az Employees és a Specialization, valamint a Volunter és a Specialization táblát kapcsolja össze úgy, hogy egy személyhez több szakterületet is lehessen rendelni.
| Attribútum neve | Attribútum típusa | Attribútum szerepe | Kapcsolat típusa | Attribútum leírása |
|---|---|---|---|---|
| ID | INT | Elsődleges kulcs, Clustered, | A kapcsolótábla egyedi azonosítója | |
| SpecID | INT | Idegen kulcs, Non-Unique, Non-Clustered | 1:N | A szakterület tábla azonosítója |
| EmpID | INT | Idegen kulcs, Non-Unique, Non-Clustered | 1:N | A dolgozó azonosítója |
| VolID | INT | Idegen kulcs | 1:N | Az önkéntes azonosítója |
Készül egy "HELPERS" nevű login, melynek alapértelmezett adatbázisa a "Borderless". A HELPERS loginhoz készül egy felhasználó: JoakarJoe. További felhasználók login nélkül: DolgosPaul és BeszedesTeca
PROJET MANAGER Joga van az Aid sémában SELECT-hez, UPDATE-hez, DELETE-hez és INSERT-hez. Ebbe a szerepkörbe tartozó felhasználó JoakaroJoe.
PROJECT TEAM LEADER Joga van az Aid sémában SELECT-hez. Ebbe a szerepkörbe tartozó felhasználó: DolgosPaul
PROJECT ASSISTANT SELECT joga van a Aid.APL táblához. Ebbe a szerepkörbe tartozó felhasználó: BeszedesTeca
Egy segélyszervezet munkáját nagyon sok önkéntes segíti. A folyamatos és zavartalan működéshez elengedhetetlen, hogy az adatbázisban naprakész legyen a tagi nyilvántartás.
Ez a tárolt eljárás bekéri az új önkéntes adatait és 'ellenőrzi'. Például azt, hogy a belépés a rögzítés időpontjánál régebbi vagy azzal azonos-e, vagy azt, hogy az önkéntes betöltötte-e a 18- életévét? Amennyiben jövőbeli időpontot adtak meg, akkor hibaüzenettel tér vissza: "A belépés időpontja nem lehet a mai dátumnál későbbi!". Ha 18 alatti a jelentkező, akkor a hibaüzenet a következő: 'Jelentkezzen később, ha betöltötte a 18. életévét."
A második tárolt eljárás egy már az adatbázisban szereplő önkéntes elérhetőségét módosítja. A példa tárolt eljárásban a mobiltelefonszám ellenőrzésérekerül sor.
A harmadik tárolt eljárás törli azokat az önkénteseket a nyilvántartásből, akiknek a kilépési dátuma (VolDateOfExit) és a mai dátum között legalább egy év eltelt.
A fájl négy, view-ként lementett összetett lekérdezést, illetve egy pivoting táblát tartalmaz. Mindegyik a szervezet napi munkájának megszervezését segíti.
Az első nézetben készült lekérdezés az összes aktív státuszú, azaz jelenleg üzemelő hazai és külföldi ellátási hely listáját adja vissza, azok típusával, elérhetőségével és koordinátáival.
A másodikban azok az adományok kerültek lekérdezésre, amelyek szintén 'aktív' státuszúak, azaz a célbajuttatásuk még nem történt meg.
A harmadik nézetben az aktív önkéntesek és dolgozók elérhetőségei kerültek a lekérdezésbe.
A negyedik nézetben egy egyszerű pivoting tábla található az aktív önkéntesek szakterületenkénti számával.
A felajánlott adományok - típusuknak megfelelően - kerülnek felhasználásra. A pénzösszegek lekönyvelésre kerülnek, a tárgyi adományok egyrésze azonnal felhasználásra kerül (például készételek, zöldség-gyümölcs, ruházat, stb.), másik része pedig raktárba. A felhasznált, elszállított, lekönyvelt adományok státuszát nullára módosítják, majd törlik a táblából az érintett rekordokat. Ez a trigger akkor aktiválódik, amikor kiadnak egy törlés (DELETE) parancsot egy 0 státuszú adományra vonatkozóan az Aid.Donations táblán, függetlenül annak eredményességétől.
A földrajzi koordináták általában több forrásból, különböző adattípusban (float, varchar) állnak rendelkezésre. Ez a felhasználó által definiált scalar függvény egy metódus segítségével a bemeneti koordinátákat Geography, azaz földrajzi (pont) adattípussá/objektummá alakítja. Ezenkívül több tér-adattípus (line, poligon) létezik, ebben az adatbázisban csak pontok kerültek letárolásra az APL tábla, Geolocation mezőjében.
Paraméterek:
- @Longitudine
- Típusa: FLOAT
- Használata: bemeneti
- @Latitudine
- Típusa: FLOAT
- Használata: bemeneti
A visszatérési érték típusa: GEOGRAPHY
A segélyszervezet azonfelül, hogy adományokat fogad és állami támogatás kap, számos pályázatot is ír. Minden pályázat fontos része a statisztika. Az alábbi, felhasználó által definiált tábla-érték függvény a segélyszervezet osztályainak dolgozóiról szolgáltat statisztikai adatokat táblázatos formában.
Paraméterek:
- @departm
- Típusa: VARCHAR
- Használata: bemeneti
A visszatérési érték típusa: TABLE
- DepartmentName AS 'Osztály neve'
- 'Dolgozók száma' (számított oszlop)
- 'Átlagéletkor' (számított oszlop)
Ez a függvény olyan, felhasználó által definiált függvény, amely egy táblázatban foglalva adja vissza az aktív önkéntesek lakóhelyének távolságát a bemeneti paraméterként megadott legfontosabb ellátási pontoktól.
Paraméterek:
- @Origin_BOK GEOGRAPHY, @Origin_ZAHONY GEOGRAPHY, @Origin_BEREGSZASZ GEOGRAPHY
- Típusa: GEOGRAPHY
- Használata: bemeneti
A visszatérési érték típusa: TABLE
- Aktív önkéntesek neve
- Elérhetősége
- Lakóhelye
- Távolság km-ben
