Dit is deel drie van de serie, Five Normal Forms. De titels van de eerste twee delen (tutorials) zijn First Normal Form, gevolgd door Second Normal Form. In dit deel van de serie wordt de derde normaalvorm uitgelegd.
De uitleg volgt de verhaallijn: een vader is overleden en heeft wat geld nagelaten voor zijn zoon. De zoon besloot het geld te investeren in een buurtwinkel. Een gemakswinkel, ook wel gemakswinkel genoemd, is een kleine detailhandel die alledaagse artikelen van leveranciers ontvangt en deze verkoopt aan individuele klanten in de buurt.
Op dit moment is de winkel al bevoorraad en zijn er al enkele verkopen gedaan. De zoon, die de eigenaar van het bedrijf is, heeft een aantal werknemers, die in deze tutorial griffiers worden genoemd. De eigenaar en elke werknemer kunnen voorraden ontvangen en verkopen na het registreren van de producten.
Voordat de winkel begon, wisten noch de eigenaar, noch de werknemers iets van normale formulieren. Dus registreerden ze alles als transacties in één tabel en één schrift. Ze hadden geen computer.
U, de lezer, heeft de vijf delen van deze tutorialreeks voltooid; je bent nu een database-ontwikkelaar. De eigenaar van de gemakswinkel is je vriend. Je hebt de winkel twee dagen geleden bezocht en de eigenaar en de bedienden getraind in het produceren van een tafel in zijn eerste normale vorm. Je hebt gisteren ook de winkel bezocht en ze getraind in het maken van een tafel in de tweede normale vorm van de eerste normale vorm.
Vandaag ben je net aangekomen in de winkel voor een bezoek om ze te trainen in het produceren van een tafel in de derde normaalvorm uit de tweede normaalvorm. Alle tafels die ze momenteel hebben, zijn in de tweede normale vorm. De tabellen (op naam en kolomkoppen) zijn:
Producten(productID, categorieID, product)
Categorieën (categorieID, categorie)
Verkoop(saleID, klant, medewerker, datum)
SaleDetails(saleID, productID, numberSold, sellingPrice)
Orders(orderID, leverancier, medewerker, datum)
OrderDetails(orderID, productID, numberBought, costPrice)
De enkelvoudige of samengestelde toetsen zijn onderstreept.
Na een samenvatting van wat er de afgelopen twee dagen is geleerd en voordat je iets kon doen, vraagt de eigenaar:
“Hoe zit het met telefoonnummers, adressen etc. van klanten en medewerkers?
Hoe zit het met de hoeveelheid op voorraad, het bestelniveau, etc. voor producten?
Hebben ze hun eigen aparte tafels nodig, of moeten ze in de huidige tafels passen?”
U, de database-ontwikkelaar, antwoordt:
“Gefeliciteerd, eigenaar! U hebt indirect de kwestie van de derde normaalvorm geïntroduceerd.
Ga jij maar verder.
Andere noodzakelijke kolommen
Andere noodzakelijke kolommen worden eerst toegevoegd aan de vorige tabellen, die in 1NF en 2NF staan. Sommige van de vorige kolomnamen zijn gewijzigd.
De tabel Categorieën moet minimaal de volgende kolommen hebben:
Categorieën (categorieID, categorienaam, beschrijving)
De beschrijving is een korte paragraaf die de categorie beschrijft. Deze categorieëntabel is al in 1NF, 2NF en 3NF. De 3NF wordt hieronder uitgelegd:
De tabel Producten moet minimaal de volgende kolommen hebben:
Producten(product-ID, categorie-ID, leverancier-ID, productnaam, eenheidsprijs, hoeveelheidInvoorraad, bestelniveau)
Aangezien elk product wordt verkocht, zal een laag niveau (aantal) van de producten worden bereikt wanneer het product opnieuw moet worden besteld, dus klanten mogen niet naar de winkel komen en het product niet hebben. Een dergelijke afwezigheid is niet goed voor de zaken. hoeveelheidInVoorraad is het aantal van een bepaald product dat op voorraad is. Dit omvat wat er in de winkel is en wat er in het schap ligt.
categoryID en supplierID zijn vreemde sleutels. Daarom hebben ze streepjes onderstrepingen in plaats van enkele onderstrepingen. Vreemde sleutel wordt hieronder uitgelegd. In het vorige deel van de serie (Second Normal Form) maakte categoryID deel uit van de primaire sleutel met een enkele onderstreping vanwege de manier waarop deze tot stand was gekomen. Uit de onderstaande uitleg zou het echter duidelijk zijn dat de categoryID een externe sleutel moet zijn (met een streepje onderlijnd).
Deze productentabel is al in 1NF, 2NF en 3NF. Bekijk hieronder waarom het in 3NF staat:
De tabel SaleDetails moet minimaal de volgende kolommen hebben:
SaleDetails(saleID, productID, unitSellingPrice, kwantiteit, korting)
De discontowaarde zal naar verwachting meestal nul zijn. Een korting is de korting die de winkel een klant geeft.
De tabel OrderDetails moet minimaal de volgende kolommen hebben:
OrderDetails(orderID, productID, unitCostPrice, kwantiteit, korting)
De discontowaarde zal naar verwachting meestal nul zijn. De korting hier is de korting die de leverancier de winkel geeft.
Zoals hieronder te zien is, kan de productentabel worden beschouwd in 2NF of 3NF. De Verkoop- en Ordertafels hebben de uitgifte van 3NF. Alleen de verkooptabel wordt gebruikt om het probleem en de oplossing uit te leggen. De 3NF voor Orders Table en Products Table volgen een vergelijkbare redenering en zouden gewoon worden geciteerd.
Bij het toevoegen van kolommen zou de tabel Verkoop er als volgt uitzien:
Sales(saleID, dateSold customerName, phone, address, city, region, postcode, Country, werknemer)
Zeven kolommen hebben de klantenkolom in de oorspronkelijke tabel vervangen. Aangezien de klanten mensen in de buurt zijn, kunnen de cellen voor de kolommen stad, regio(staat), postcode en land leeg worden gelaten, hoewel ze in dit artikel niet leeg worden gelaten.
Deze verkooptabel is nog steeds in 2NF omdat zowel de 1NF- als de 2NF-regels niet zijn overtreden. U dient zich echter te realiseren dat in een verkooptabelrij de klant(naam) is vervangen door zeven klantrijcellen.
Opmerking: een adrescel bevat het huisnummer, de straat- of straatnaam en de plaatsnaam, allemaal gescheiden door komma's. Een stad kan worden beschouwd als bestaande uit meerdere steden. Hoewel komma's deze specifieke tekenreekscomponenten scheiden, vormen ze één celwaarde en niet drie celwaarden.
Ook de werknemerskolom moet vervangen worden door zeven van dergelijke kolommen. In deze tutorial wordt dat echter niet gedaan om lestijd en -ruimte te besparen. Een verkooptabel met gegevens kan dus zijn:
Verkooptabel – 2NF – Zonder klantID
Het gegevenstype SaleID-kolom is een geheel getal of, beter, auto-increment. Het gegevenstype van de kolom dateSold is een datum en geen getal omdat het het teken '/' heeft, wat geen cijfer is. Het gegevenstype voor de rest van de kolommen, inclusief de telefoonkolom, is string (of tekst). De telefoonwaarde heeft het teken '-', wat geen cijfer is.
Merk op dat voor elke rij klant(naam), net als in het vorige deel van de reeks, is vervangen door zeven cellen, waarvan één nog steeds klantnaam is. Dit betekent dat klantgegevens een entiteit zijn. Momenteel identificeert de klantnaam de andere zes gegevens op rij. Als deze tabel is geprogrammeerd, is het handig om de klantentiteit in elke rij te identificeren met een geheel getal (niet automatisch verhogen). In dat geval moet vóór de klantnaam een kolom klantID staan. De vorige tabel wordt:
Verkooptabel – 2NF – Met klantID
Er zijn drie klant-ID's: 1, 2 en 3, waarbij 1 vijf keer voorkomt voor John Smith, 2 twee keer voor James Taylor en 3 één keer voor Susan Wright.
Merk op dat sommige klant-ID's en hun afhankelijkheden zich herhalen.
Regels voor derde normaalvorm
Een tabel bevindt zich in de derde normaalvorm als deze aan de volgende regels voldoet:
- Het zou al in de tweede normale vorm moeten zijn.
- En het mag geen transitieve afhankelijkheid hebben.
Dan vraagt een van de griffiers (werknemers): “Wat is een transitieve afhankelijkheid?”. En jij, de database-ontwikkelaar, antwoordt: 'Dat is een goede vraag!'
Transitieve afhankelijkheid
Het is waar dat SaleID in een rij alle waarden in de rij identificeert; customerID identificeert echter zijn zeven gegevenswaarden, maar identificeert niet de rest van de waarden die door SaleID in die rij worden geïdentificeerd. Anders gezegd, de SaleID is afhankelijk van tien celwaarden in elke rij. De customerID is echter afhankelijk van zeven celwaarden in dezelfde rij, maar de customerID is niet afhankelijk van de SaleID en de andere waarden waarvan SaleID afhankelijk is.
Een dergelijke afhankelijkheid voor de klant-ID is transitieve afhankelijkheid. En klant-ID wordt een externe sleutel genoemd en is onderstreept in deze tutorialserie, The Five Normal Forms.
Stel dat een niet-primair attribuut (niet-primaire celwaarde) afhangt van andere niet-primaire attributen, en het niet-primaire attribuut in kwestie (bijv. CustomerID en zijn afhankelijkheden) niet afhankelijk is van de primaire sleutel en de rest van de cel waarden in de rij. Dan is dat transitieve afhankelijkheid.
De vorige Sales-tabel met de externe sleutel en zijn afhankelijkheden zou boekhoudkundige problemen (afwijkingen) veroorzaken.
Verkooptabel Van 2NF tot 3NF
Om het probleem van de externe sleutel en zijn afhankelijkheden op te lossen, verwijdert u de externe sleutel en zijn afhankelijkheden om een nieuwe tabel te vormen zonder herhalingen. Maar zelfs als de externe sleutel niet afhankelijk is van de primaire sleutel, is de primaire sleutel afhankelijk van de externe sleutel. Er moet dus een kopie van de externe sleutel in de bovenliggende tabel blijven. De nieuwe verkooptabel voldoet op dit moment aan 1NF, 2NF en 3NF; het is een bovenliggende tabel. De nieuwe onderliggende tabel van de vorige verkooptabel voldoet ook aan 1NF, 2NF en 3NF. De naam van de onderliggende tabel met externe sleutel en zijn ondergeschikten is Klanten. Als er geen geschikte naam kan worden gevonden, is er iets misgegaan met de analyse. De nieuwe verkooptabel in 3NF is:
Definitieve verkooptabel in 3NF
Deze tabel in 3NF heeft hetzelfde aantal rijen als die in 2NF maar met minder kolommen.
De tabelnotatie voor deze definitieve verkooptabel in 3NF is:
Verkoop(saleID, dateSold, klantID, werknemerID)
De saleID is de primaire sleutel met een enkele onderstreping. customerID is een vreemde sleutel, met een streepje onderstreept. employeeID is ook een externe sleutel met een onderstrepingsteken. Merk op dat de werknemerssituatie in de tabel Verkoop in 2NF gelijk is aan de klantsituatie. De werknemerID en zijn eigen afhankelijkheden moeten worden verwijderd om een andere tabel te vormen; er blijft een kopie van het werknemersID achter.
Let op: saleID, customerID en employeeID vormen geen samengestelde sleutel. saleID is afhankelijk van klantID en werknemerID.
De relatie tussen saleID en customerID is veel-op-een.
De klantentabel in 3NF
Deze tabel heeft drie rijen in plaats van 9 rijen in de tabel 2NF Sales. In deze tabel is customerID een primaire sleutel. Het is hetzelfde als de externe sleutel in de tabel Verkoop, maar zonder herhalingen. De externe sleutel in de tabel Verkoop en de primaire sleutel in de tabel Klant koppelen beide tabellen.
De herhaalde rijen in de tabel Klanten zijn verwijderd om 1NF niet te schenden.
Zoals de lezer kan zien, zou het plaatsen van een tabel in 3NF ook het probleem van herhaalde rijen (redundantie) oplossen.
De tabelnotatie voor Klantentabel is:
Klanten(customerID, customerName, telefoon, adres, stad, regio, postCode, land)
De productentabel opnieuw bezocht
De bovenstaande productentabel in notatievorm is:
Producten(product-ID, categorie-ID, leverancier-ID, productnaam, eenheidsprijs, hoeveelheidInvoorraad, bestelniveau)
De primaire sleutel hier is productID. categoryID en supplierID zijn vreemde sleutels. Net als bij de tabel Klant is er een tabel Categorieën, waarbij categoryID de primaire sleutel is, en er is een tabel Leverancier, waarbij supplierID de primaire sleutel is.
Als de waarden voor de cellen voor eenheidPrijs, kwantiteitInStock en reorderLevel vast blijven, dan is de tabel Producten, zoals die is, echt in 3NF. Als deze waarden zullen veranderen, dan is de tabel Producten, zoals die is, in 2NF. In dit deel van de zelfstudiereeks wordt ervan uitgegaan dat die waarden in de loop van de tijd vast blijven.
Alle tafels
Alle tabellen zijn nu in 3NF. Ze worden weergegeven als:
Werknemers(employeeID, naam, telefoon, adres, stad, regio, postcode, land, geboortedatum, aanwervingsdatum, dateReleased)
Leveranciers (supplierID, naam, telefoon, adres, stad, regio, postcode, land)
Producten(product-ID, categorie-ID, leverancier-ID, productnaam, eenheidsprijs, hoeveelheidInvoorraad, bestelniveau)
Categorieën (categorieID, categorienaam, beschrijving)
Verkoop(saleID, dateSold, klantID, werknemerID)
SaleDetails(saleID, productID, numberSold, sellingPrice)
Klanten(customerID, customerName, telefoon, adres, stad, regio, postCode, land)
Orders(orderID, dateSold, supplierID, employeeID)
OrderDetails(orderID, productID, numberBought, costPrice)
Er zijn tot negen professionele tabellen gemaakt uit slechts één tabel die door beginners is gemaakt om redundantie en boekhoudkundige problemen (afwijkingen bij het invoegen, verwijderen en bijwerken) te voorkomen. Alleen al de beginnerstafel zou tot financiële verliezen leiden.
Het testen van het personeel
Op dit moment zouden alle werknemers, inclusief de eigenaar, 1NF, 2NF en 3NF moeten hebben begrepen. Ze moeten echter getest worden. Ze zullen allemaal, inclusief de eigenaar, op verschillende plaatsen zitten en de test voltooien. De test, bestaande uit één vraag, duurt een uur en ziet er als volgt uit:
Vraag: Bewijs met behulp van regels voor 1NF, 2NF en 3NF dat alle bovenstaande negen tabellen al in de eerste normaalvorm, tweede normaalvorm en derde normaalvorm staan. De klanten en leveranciers hoeven geen echte entiteiten te zijn. Gegevens voor tabellen moeten een back-up maken van de tabelnotaties.
Terwijl zij de test maken, ga jij als database-ontwikkelaar op pad voor een hapje en een biertje, om na een uur weer terug te komen.
De nabije en verre toekomst
Terwijl jij, de database-ontwikkelaar, er niet bent, denk je ook na over welk advies je ze moet geven als ze allemaal slagen voor de test.
En terwijl u ze aan het trainen was, en nu ze de test afleggen, zijn er klanten komen en gaan zonder te worden bediend. Dat is niet goed voor het bedrijfsleven, en dat weet u, de database-ontwikkelaar. Sommige klanten gaan naar de winkels van de concurrent en komen nooit meer terug.
U, de database-ontwikkelaar, bent 30 jaar oud. De eigenaar, als uw vriend, is ook 30 jaar oud. De bedienden (werknemers) zijn tussen de 18 en 24 jaar oud. Alle eigenschappen die ze nodig hadden om voor de eigenaar te werken waren: gezond zijn, kunnen lezen en schrijven, kunnen optellen, aftrekken, vermenigvuldigen en delen , en om de computer en het internet te kunnen gebruiken.
Wanneer een tabel in 3NF staat, zijn de meeste kwetsbaarheden uit de database verwijderd. Veel commerciële databases gaan niet verder dan 3NF, en de bedrijven of bedrijven voelen zich op hun gemak.
Dus als ze allemaal slagen voor de test, vraag je de griffiers om te gaan en door te werken. Ook adviseer je ze om een deel van hun salaris te sparen zodat ze eigenaar kunnen worden van hun gemakswinkels. Je gaat morgen verder om alleen de eigenaar te trainen in 4NF en 5NF. Met medeweten van 4NF en 5NF worden alle bekende kwetsbaarheden verwijderd.
evaluatie
Na een uur komt u, de database-ontwikkelaar, terug. Je markeert hun scripts. Een stuk uitstekend nieuws! Ze hebben allemaal, inclusief de eigenaar, elk 100%. Hoera! Dat is uitstekend!
Dus proficiat aan jullie allemaal: de leraar en de leerlingen.
U hoeft in deze tutorial niets anders te doen dan af te ronden.
Conclusie
Een tabel is in eerste normaalvorm als deze geen van de volgende regels overtreedt:
- Alle kolommen in een tabel moeten unieke kopnamen hebben.
- Elke cel mag slechts één waarde hebben.
- Waarden die in een kolom zijn opgeslagen, moeten van hetzelfde type zijn.
- De rijen moeten verschillend zijn.
- De volgorde van de kolommen of rijen maakt niet uit.
Een tabel bevindt zich in de tweede normaalvorm als deze geen van de volgende regels overtreedt:
- De tabel moet al in First Normal Form staan.
- Er mag geen gedeeltelijke afhankelijkheid zijn.
Een tabel bevindt zich in de derde normaalvorm als deze geen van de volgende regels overtreedt:
- Het moet al in de tweede normale vorm zijn.
- En het mag geen transitieve afhankelijkheid hebben.
Jij, de database-ontwikkelaar, vertelt de klerken dat ze genoeg hebben geleerd. Je geeft advies en vraagt hen standaard weer aan het werk te gaan en op hun werkplek te blijven.
U maakt alleen een afspraak met de eigenaar, die morgen in zijn kantoor zal plaatsvinden voor training op 4NF en 5NF.
