Typy relacφ
Zde je uveden p°φklad pro lepÜφ porozum∞nφ problematice relacφ.
IdentifikaΦnφ relace
Primßrnφ klφΦ z parent entity migruje do child entity, a tam je souΦßstφ primßrnφho
klφΦe. Pou₧φvß se tehdy, pokud primßrnφ klφΦ cizφ entity nenφ schopen zajistit
jednoznaΦnou identifikaci. Entita, kterß je child entitou propojenou p°es identifikaΦnφ
relaci, je tzv. zßvislou (dependent) entitou , a je v modelu zobrazena se zaoblen²mi
rohy.
P°φklad:
// parent entity
Create table Invoice (
InvoiceNo Integer NOT NULL,
ExportDate Date,
BLN_Supplier Char(10) NOT NULL) ;
// child entity
Create table InvItem (
InvoiceNo Integer NOT NULL,
InvLineNo Integer NOT NULL,
Amount Integer) ;
Alter table Invoice add primary key (InvoiceNo);
Alter table InvItem add primary key (InvoiceNo,InvLineNo);
Alter table InvItem add foreign key (InvoiceNo)
references Invoice (InvoiceNo);
╚φslo °ßdku je jako jedineΦn² identifikßtor zßznamu nedostateΦn², child entita
si musφ vypomoci ╚φslem faktury (InvoiceNo). Proto je pou₧ita identifikaΦnφ
relace. Polo₧ka faktury nem∙₧e existovat bez samotnΘ faktury, proto je polo₧ka
faktury zßvislou entitou.
NeidentifikaΦnφ relace
Primßrnφ klφΦ z parent entity migruje do child entity, a tam neni souΦßsti
primßrnφho klφΦe.
P°φklad:
Create table Invoice (
InvoiceNo Integer NOT NULL,
ExportDate Date, BLN_Supplier Char(10) NOT NULL);
Create table AddressBook (
BLN_Supplier Char(10) NOT NULL,
CompanyName Char(50),
BLN_Customer Char(10) NOT NULL);
Alter table Invoice add primary key (InvoiceNo);
Alter table AddressBook add primary key (BLN_Supplier);
Alter table Invoice add foreign key (BLN_Supplier)
references AddressBook (BLN_Supplier);
Alter table AddressBook add foreign key (BLN_Customer)
references AddressBook (BLN_Supplier) on delete cascade;
╚φslo faktury je jako jedineΦnß identifikace faktury zcela postaΦujφcφ. Proto
je pou₧ita neidentifikaΦnφ relace, iΦo je pouze cizφm klφΦem. Faktura m∙₧e existovat
bez nßvaznosti na firmu, proto je faktura nezßvislou entitou.
Relace M:N
P°φklad:
Create table Sup_Price (
BLN_Supplier Char(10) NOT NULL,
ArticleId Integer NOT NULL) ;
Alter table Sup_Price add primary key (BLN_Supplier,ArticleId);
Alter table Sup_Price add foreign key (BLN_Supplier)
references AddressBook (BLN_Supplier);
Alter table Sup_Price add foreign key (ArticleId)
references PriceList (ArticleId) ;
Informativnφ Relace
Primßrnφ klφΦ nikam nemigruje, negeneruje se ₧ßdnß referenΦnφ integrita ani
triggery, jednß se pouze o informaci, ₧e n∞jak² vztah existuje.
Self Relace
K vytvo°enφ Self relace je zapot°ebφ kliknout prav²m tlaΦφtkem myÜi na entitu
a vybrat "P°idat Self Relaci"
P°φklad:
Create table AddressBook (
BLN_Supplier Char(10) NOT NULL,
CompanyName Char(50),
BLN_Customer Char(10) NOT NULL) ;
Alter table AddressBook add primary key (BLN_Supplier);
Alter table AddressBook add foreign key (BLN_Customer)
references AddressBook (BLN_Supplier) on delete cascade;
Dopl≥ujφcφ informace
Generated SQL Script
/* Database Oracle 8 */
Create table Invoice (
InvoiceNo Integer NOT NULL,
ExportDate Date,
BLN_Supplier Char(10) NOT NULL) ;
Create table InvItem (
InvoiceNo Integer NOT NULL,
InvLineNo Integer NOT NULL,
Amount Integer) ;
Create table AddressBook (
BLN_Supplier Char(10) NOT NULL,
CompanyName Char(50),
BLN_Customer Char(10) NOT NULL) ;
Create table PriceList (
ArticleId Integer NOT NULL,
Artice Char(20),
Price Number(10,2)) ;
Create table Sup_Price (
BLN_Supplier Char(10) NOT NULL,
ArticleId Integer NOT NULL) ;
Alter table Invoice add primary key (InvoiceNo);
Alter table InvItem add primary key (InvoiceNo,InvLineNo);
Alter table AddressBook add primary key (BLN_Supplier);
Alter table PriceList add primary key (ArticleId);
Alter table Sup_Price add primary key (BLN_Supplier,ArticleId);
Alter table InvItem add foreign key (InvoiceNo)
references Invoice (InvoiceNo) ;
Alter table Sup_Price add foreign key (BLN_Supplier)
references AddressBook (BLN_Supplier);
Alter table AddressBook add foreign key (BLN_Customer)
references AddressBook (BLN_Supplier) on delete cascade;
Alter table Invoice add foreign key (BLN_Supplier)
references AddressBook (BLN_Supplier);
Alter table Sup_Price add foreign key (ArticleId)
references PriceList (ArticleId);
/* Update trigger for AddressBook */
Create Trigger tu_AddressBook after update
of BLN_Supplier,BLN_Customer
on AddressBook
referencing new as new_upd old as old_upd for each
row
declare numrows integer;
begin
/* cascade child AddressBook update when parent
AddressBook changed */
if (:old_upd.BLN_Supplier != :new_upd.BLN_Supplier)
then
begin update AddressBook
set BLN_Customer = :new_upd.BLN_Supplier
where AddressBook.BLN_Customer = :old_upd.BLN_Supplier
;
end;
end if;
end;
/
/* Roles permissions */
/* Users permissions */
Nastavenφ referenΦnφ integrity
Identifying Relationship 1
Parent Update - Restrict
Parent Delete - Restrict
Non Identifying Relationship 1
Parent Update - Restrict
Parent Delete - Restrict
Relationship M:N 1
Parent Update - Restrict
Parent Delete - Restrict
Relationship M:N 2
Parent Update - Restrict
Parent Delete - Restrict
Self Relationship 1
Parent Update - Cascade
Parent Delete - Cascade
Jak nadefinovat pravidla pro referenΦnφ integritu?
Prav²m tlaΦφtkem myÜi klikn∞te na Φßru zobrazujφcφ relaci a nastavte p°φsluÜnΘ
hodnoty v zßlo₧ce "ReferenΦnφ integrita"
NepodporovanΘ typy referenΦnφ integrity?
CASE Studio 2 Vßm umo₧≥uje generovat takΘ nepodporovanΘ typy referenΦnφ integrity
za pomocφ trigger∙.