a Gyorsindítás bemutató relációs adatbázis tervezés

Bevezetés

relációs adatbázis javasolta Edgar Codd (IBM Research) körül 1969. Azóta a kereskedelmi alkalmazások domináns adatbázismodelljévé vált (összehasonlítva más adatbázis-modellekkel, például hierarchikus, hálózati és objektummodellekkel). Ma már számos kereskedelmi relációs adatbázis-kezelő rendszer (RDBMS) létezik, mint például az Oracle, az IBM DB2 és a Microsoft SQL Server., Számos ingyenes és nyílt forráskódú RDBM is létezik, mint például a MySQL, az mSQL (mini-SQL) és a beágyazott JavaDB (Apache Derby).

a relációs adatbázis Táblázatokban (vagy kapcsolatokban) szervez adatokat. A táblázat sorokból és oszlopokból áll. A sort rekordnak (vagy tuple-nek) is nevezik. Az oszlopot mezőnek (vagy attribútumnak) is nevezik. Az adatbázis tábla hasonló a táblázathoz. A táblázatok között létrehozható kapcsolatok azonban lehetővé teszik egy relációs adatbázis számára, hogy hatékonyan tároljon hatalmas mennyiségű adatot, valamint hatékonyan lekérje a kiválasztott adatokat.,

egy SQL (Structured Query Language) nevű nyelvet fejlesztettek ki relációs adatbázisok kezelésére.

adatbázis tervezési célkitűzés

egy jól megtervezett adatbázis:

• megszünteti az adat redundanciát: ugyanazt az adatot nem lehet egynél több helyen tárolni. Ennek oka az, hogy az ismétlődő adatok nemcsak a hulladéktárolókat, hanem könnyen következetlenségeket is eredményeznek.
• biztosítsa az adatok integritását és pontosságát:
• több

relációs adatbázis-tervezési folyamat

Az adatbázis-tervezés inkább művészet, mint tudomány, mivel sok döntést kell hoznia., Az adatbázisokat általában egy adott alkalmazáshoz igazítják. Nincs két testreszabott alkalmazás egyforma, ezért nincs két adatbázis egyforma. Iránymutatások (általában annak szempontjából, hogy mit ne tegyünk, ahelyett, hogy mit tegyünk) a tervezési döntés meghozatalában, de a döntések végső soron az Ön – a tervező-döntésére vonatkoznak.

1. lépés: Határozza meg az adatbázis célját (Követelményelemzés)

Gyűjtse össze a követelményeket, és határozza meg az adatbázis célját, pl…

a mintabeviteli űrlapok, lekérdezések és jelentések elkészítése gyakran segít.,

2. lépés: adatgyűjtés, táblázatok rendezése és az elsődleges kulcsok megadása

miután eldöntötte az adatbázis célját, Gyűjtse össze az adatbázisban tárolandó adatokat. Osszuk az adatokat tárgy alapú táblákra.

válasszon egy oszlopot (vagy néhány oszlopot) az úgynevezett elsődleges kulcsként, amely egyedileg azonosítja az egyes sorokat.

elsődleges kulcs

a relációs modellben a táblázat nem tartalmazhat ismétlődő sorokat, mert ez kétértelműséget okozna a visszakeresésben., Az egyediség biztosítása érdekében minden táblának tartalmaznia kell egy oszlopot (vagy oszlopkészletet), amelyet elsődleges kulcsnak neveznek, amely egyedileg azonosítja a táblázat minden rekordját. Például egy customerID egyedi szám használható a Customers táblázat elsődleges kulcsaként; productCode Products táblázat; isbn mert Books táblázat. Az elsődleges kulcsot egyszerű kulcsnak nevezik, ha egyetlen oszlopról van szó; összetett kulcsnak nevezik, ha több oszlopból áll.,

a legtöbb RDBMSs indexet épít az elsődleges kulcsra a gyors keresés és visszakeresés megkönnyítése érdekében.

az elsődleges kulcsot más táblázatok hivatkozására is használják (később kidolgozandó).

el kell döntenie, hogy melyik oszlopot(oszlopokat) kell használni az elsődleges kulcshoz. A döntés nem lehet egyenesen előre, de az elsődleges kulcsnak a következő tulajdonságokkal kell rendelkeznie:

• az elsődleges kulcs értékeinek egyedinek kell lenniük (azaz nincs ismétlődő érték)., Például a customerNamenem feltétlenül használható a Customers táblázat elsődleges kulcsaként, mivel két azonos nevű ügyfél lehet.
• az elsődleges kulcsnak mindig van értéke. Más szóval, nem tartalmazhat NULL-t.

vegye figyelembe a következőket: Válassza ki az elsődleges kulcsot:

• az elsődleges kulcsnak egyszerűnek és ismerősnek kell lennie, pl.employeeID mertemployees táblázat ésisbnbooks táblázat.,
• az elsődleges kulcs értéke nem változhat. Az elsődleges kulcs más táblázatok hivatkozására szolgál. Ha megváltoztatja annak értékét, meg kell változtatnia az összes hivatkozást; ellenkező esetben a hivatkozások elvesznek. Előfordulhat például, hogy aphoneNumber nem megfelelő aCustomers táblázat elsődleges kulcsaként használni, mert ez megváltozhat.
• az elsődleges kulcs gyakran egész (vagy szám) típust használ. De más típusok is lehetnek, például szövegek. Azonban a legjobb, ha a numerikus oszlopot elsődleges kulcsként használja a hatékonyság érdekében.
• az elsődleges kulcs tetszőleges számot vehet fel., A legtöbb RDBMSs támogatja az úgynevezett automatikus növekményt (vagy AutoNumber típus) az integer elsődleges kulcshoz, ahol (jelenlegi maximális érték + 1) az új rekordhoz van rendelve. Ez az önkényes szám tény nélküli, mivel nem tartalmaz tényszerű információkat. Ellentétben tényszerű információk, mint például a telefonszám, fact-kevesebb szám ideális elsődleges kulcs, mivel nem változik.
• az elsődleges kulcs általában egyetlen oszlop (pl. customerIDvagy productCode). De több oszlopból is állhat. A lehető legkevesebb oszlopot kell használnia.,

Let’s illustrate with an example: a table customers contains columns lastName, firstName, phoneNumber, address, city, state, zipCode. The candidates for primary key are name=(lastName, firstName), phoneNumber, Address1=(address, city, state), Address1=(address, zipCode). Name may not be unique. Phone number and address may change., Ezért jobb, ha egy tény nélküli automatikus növekményszámot hozunk létre, mondjuk customerID, mint elsődleges kulcs.

3. lépés: kapcsolatok létrehozása A

táblázatok között egy független és nem kapcsolódó táblákból álló adatbázis kevés célt szolgál (inkább táblázatkezelőt használhat). A relációs adatbázis ereje a táblázatok között meghatározható kapcsolatban rejlik. A relációs adatbázis tervezésének legfontosabb szempontja a táblák közötti kapcsolatok azonosítása., A kapcsolat típusai a következők:

1. egy-sok
2. sok-sok
3. egy-egy

egy-sok

egy-egy “class roster” adatbázisban a tanár nulla vagy több osztályt taníthat, míg az osztályt egy (és csak egy) tanár tanítja. A” vállalat ” adatbázisban a menedzser nulla vagy több alkalmazottat kezel, míg a munkavállalót egy (csak egy) menedzser kezeli. Egy” termékértékesítési ” adatbázisban az ügyfél számos megrendelést tehet fel; míg egy megrendelést egy adott ügyfél helyez el. Ez a fajta kapcsolat ismert, mint egy-a-sok.,

egy-sok kapcsolat nem ábrázolható egyetlen táblázatban. Például egy “osztálylista” adatbázisban a Teachers nevű táblázattal kezdhetjük, amely a tanárokra vonatkozó információkat tárolja (például name, office, phone és email). Az egyes tanárok által tanított osztályok tárolásához oszlopokat hozhatunk létre class1, class2, class3, de azonnal problémát jelent, hogy hány oszlopot kell létrehozni., Másrészt, ha egy Classes nevű táblázattal kezdjük, amely információkat tárol egy osztályról (courseCode, dayOfWeek, és timeEnd); további oszlopokat hozhatunk létre az (egy) tanárra vonatkozó információk tárolására (például name, office, phone és email). Mivel azonban egy tanár sok osztályt taníthat, adatait sok sorban lemásolják a táblázatban.,

támogatása egy-sok kapcsolat, meg kell terveznünk két táblázatok: táblázat Classes, hogy adatokat tároljon az osztályok classID, mint az elsődleges kulcs; valamint egy táblázat Teachers, hogy adatokat tároljon a tanárok teacherID, mint az elsődleges kulcs. Ezután létrehozhatjuk az egy-sok kapcsolatot a táblázat elsődleges kulcsának tárolásával Teacher (azaz,, teacherID) (a”one” -end vagy a szülő táblázat) a táblázatban classes (A”many” -end vagy a gyermek táblázat), az alábbiak szerint.

az oszlop teacherIDa gyermek táblázatban Classes idegen kulcsként ismert. A gyermekasztal idegen kulcsa a szülői táblázat elsődleges kulcsa, amelyet a szülői táblázat hivatkozására használnak.

vegye figyelembe, hogy a szülőtáblázat minden értéke esetén nulla, egy vagy több sor lehet a gyermektáblázatban., A gyermekasztal minden értéke esetében csak egy sor van a szülői táblázatban.

Many-to-Many

egy “termékértékesítési” adatbázisban az ügyfél megrendelése tartalmazhat egy vagy több terméket; egy termék számos megrendelésben megjelenhet. Egy” könyvesbolt ” adatbázisban egy könyvet egy vagy több szerző ír; míg a szerző nulla vagy több könyvet írhat. Ez a fajta kapcsolat ismert, mint sok-sok.

illusztráljuk egy “termékértékesítési” adatbázissal. Két táblával kezdjük: Productsés Orders., A products táblázat információkat tartalmaz a termékekről (például: name, description és quantityInStock) productID mint elsődleges kulcs. A orders táblázat tartalmazza az ügyfél megrendeléseit (customerID, dateOrdered, dateRequired és status). Ismét nem tárolhatjuk a Orders táblázatban megrendelt elemeket, mivel nem tudjuk, hány oszlopot kell foglalni az elemekhez., A rendelési adatokat nem tárolhatjuk a Products táblázatban.

a sok-sok kapcsolat támogatásához létre kell hoznunk egy harmadik táblázatot (úgynevezett csomóponttáblázat), mondjuk OrderDetails (vagy OrderLines), ahol minden sor egy adott sorrend egy elemét képviseli. AOrderDetails táblázat esetében az elsődleges kulcs két oszlopból áll:orderID ésproductID, amelyek egyedileg azonosítják az egyes sorokat., A orderID és productID in OrderDetails a táblázat a Orders és Products táblákra hivatkozik, így ezek is az idegen kulcsok a OrderDetails táblázatban.

a sok-sok kapcsolat valójában két egy-sok kapcsolatként valósul meg, a csatlakozási táblázat bevezetésével.

1. egy rendelésnek sok eleme van a OrderDetails – ban. Egy OrderDetails az elem egy adott sorrendhez tartozik.,
2. egy termék sok OrderDetails – ban jelenhet meg. Minden OrderDetails tétel megadott egy termék.

egy-egy

egy “termékértékesítési” adatbázisban a termék opcionális kiegészítő információkkal rendelkezhet, mint például a image, moreDescription és comment. A Products táblán belül tartása sok üres helyet eredményez (azokban a rekordokban, amelyek nem tartalmazzák ezeket az opcionális adatokat). Ezen túlmenően ezek a nagy adatok ronthatják az adatbázis teljesítményét.,

ehelyett létrehozhatunk egy másik táblázatot (mondjuk ProductDetails, ProductLines vagy ProductExtras) az opcionális adatok tárolásához. A rekord csak akkor jön létre az említett termékek opcionális adatokat. A két tábla, Productsés ProductDetails egy-egy összefüggést mutat. Vagyis a szülői táblázat minden sorában legfeljebb egy sor (esetleg nulla) van a gyermekasztalban. Mindkét táblázat elsődleges kulcsaként ugyanazt a productID oszlopot kell használni.,

egyes adatbázisok korlátozzák a táblán belül létrehozható oszlopok számát. Egy-egy kapcsolat segítségével az adatokat két táblára oszthatja. Az egy-egy kapcsolat hasznos bizonyos érzékeny adatok biztonságos táblázatban történő tárolására is, míg a főtáblában lévő nem érzékeny adatok tárolására.

oszlop adattípusok

minden oszlophoz ki kell választania a megfelelő adattípust. Az adattípusok általában a következők: egész számok, lebegőpontos számok, string (vagy szöveg), dátum / idő, bináris, gyűjtemény (például Felsorolás és készlet).,

4. Lépés: Finomítani a & Normalizálni a Design

például,

• összeadás több oszlopok,
• hozzon létre egy új táblát az opcionális adatok segítségével egy-egy kapcsolat,
• split egy nagy asztal, két kisebb táblázatok
• a többiek.

normalizáció

alkalmazza az úgynevezett normalizációs szabályokat annak ellenőrzésére, hogy az adatbázis szerkezetileg helyes és optimális-e.

első normál forma (1NF): a táblázat 1NF, ha minden cella egyetlen értéket tartalmaz, nem pedig az értékek listáját. Ezt a tulajdonságot atomnak nevezik., 1NF is tiltja ismétlődő oszlopcsoport, mint például a item1, item2,.., itemN. Helyette, létre kell hoznia egy másik táblázatot egy-sok kapcsolat.

második normál forma (2NF): a táblázat 2NF, ha 1NF, és minden nem kulcs oszlop teljes mértékben függ az elsődleges kulcstól. Továbbá, ha az elsődleges kulcs több oszlopból áll, minden nem kulcs oszlop a teljes készlettől függ, nem pedig annak egy részétől.,

például a OrderDetails orderID és productIDelsődleges kulcsa. Ha unitPrice csak a productID – tól függ, akkor azt nem lehet a OrderDetails táblázatban tartani (de a Products táblázatban). Másrészt, ha aunitPrice a terméktől és az adott megrendeléstől függ, akkor azt aOrderDetails táblázatban kell tartani.,

harmadik normál forma (3NF): a táblázat 3NF, ha 2NF, és a nem kulcsos oszlopok egymástól függetlenek. Más szavakkal, a nem kulcs oszlopok az elsődleges kulcstól függenek, csak az elsődleges kulcstól, semmi mástól. Tegyük fel például, hogy van egyProducts táblázat oszlopokkalproductID (elsődleges kulcs),name ésunitPrice., A discountRate oszlop nem tartozik a Products táblához, ha az is függ a unitPrice táblától, amely nem része az elsődleges kulcsnak.

magasabb normál forma: a 3NF-nek vannak hiányosságai, ami magasabb normál formához vezet, mint például a Boyce/Codd Normal form, a negyedik Normal Form (4NF) és az ötödik Normal Form (5NF), amely túlmutat ezen oktatóanyagon.

időnként dönthet úgy, hogy megszegi a normalizálási szabályokat, teljesítmény ok miatt (pl., hozzon létre egy totalPrice nevű oszlopot a Orderstáblában, amely aorderDetails rekordokból származhat); vagy azért, mert a végfelhasználó kérte. Győződjön meg róla, hogy teljes mértékben tisztában van vele, programozási logikát fejleszt annak kezelésére, és megfelelően dokumentálja a döntést.

integritási szabályok

az integritási szabályokat is alkalmazni kell a tervezés integritásának ellenőrzéséhez:

Entity Integrity szabály: az elsődleges kulcs nem tartalmazhat NULL-t. Ellenkező esetben nem tudja egyedileg azonosítani a sort., Több oszlopból álló összetett kulcs esetén egyik oszlop sem tartalmazhat NULL értéket. A legtöbb RDBMS ellenőrzi és érvényesíteni ezt a szabályt.

referenciális integritási szabály: minden idegen kulcsértéket a hivatkozott táblázatban (vagy szülő táblázatban) szereplő elsődleges kulcsértékhez kell igazítani.

• idegen kulccsal ellátott Sort csak akkor helyezhet be a gyermektáblázatba, ha az érték a szülőtáblában létezik.
• ha a kulcs értéke megváltozik a szülőtáblázatban(pl. a sor frissítve vagy törölve), akkor a gyermektáblázat (ok) ban található idegen kulccsal rendelkező összes sort ennek megfelelően kell kezelni., Vagy (a) letilthatja a módosításokat; (b) a gyermektáblázatokban a változást (vagy törölheti a rekordokat) ennek megfelelően; (c) állítsa a gyermektáblázatok kulcsértékét NULL értékre.

a legtöbb RDBMS beállítható az ellenőrzés végrehajtásához, valamint a referenciális integritás biztosításához a megadott módon.

üzleti logika integritása: a fenti két általános integritási szabály mellett létezhet az üzleti logikára vonatkozó integritás (validáció), pl., az irányítószám egy bizonyos tartományon belül 5 számjegyű, a szállítási dátum és az idő a munkaidőben esik; a megrendelt mennyiségnek meg kell egyeznie vagy kevesebbnek kell lennie, mint a raktáron lévő mennyiség stb. Ezeket érvényesítési szabályban (az adott oszlop esetében) vagy programozási logikában lehet végrehajtani.

oszlop indexelés

az adatkeresés és-visszakeresés megkönnyítése érdekében indexet hozhat létre a kiválasztott oszlop(ok) ban. Az index egy strukturált fájl, amely felgyorsítja a SELECT adathozzáférést, de lelassíthatja a INSERT, UPDATE és DELETE., Indexszerkezet nélkül a SELECT lekérdezés megfelelő kritériummal történő feldolgozásához (pl. SELECT * FROM Customers WHERE name='Tan Ah Teck') az adatbázismotornak össze kell hasonlítania a táblázat minden rekordját. Egy speciális index (például a BTREE struktúrában) minden rekord összehasonlítása nélkül elérheti a rekordot. Az indexet azonban újra kell építeni, amikor egy rekordot megváltoztatnak, ami az indexek használatával kapcsolatos költségeket eredményez.

Index lehet meghatározni egy oszlop, egy sor oszlop (az úgynevezett összefűzött index), vagy része egy oszlop (pl.,, az első 10 karakter a VARCHAR(100)) (úgynevezett részleges index) . Lehet építeni több mint egy index egy táblázatban. Például, ha gyakran keres egy ügyfelet a customerName vagy phoneNumber segítségével, felgyorsíthatja a keresést a customerName oszlop indexének felépítésével, valamint a phoneNumber. A legtöbb RDBMS épít index az elsődleges kulcs automatikusan.

referenciák & források

legújabb verzió tesztelve: MySQL 5.5.15
utolsó módosítás: 2010. szeptember