Baze jedan na jedan

[milos_r]

Pozdrav,
imao bih jedno pitanje povodom povezivanja tabela jedan na jedan. Naime interesuje me da li u slucaju na slici trebam za povezivanje jedan na jedan te tri tabele da imam jos kljuceva ili je dovoljno samo primarni kljuc. Takodje ako bi mogao neko da napise kod za relacije (ms sql) bio bih mu zahvalan da vidim da li sam ja to dobro uradio.
Evo i slike

Malo objasnjenje bi bilo da je recimo entitet obavezan da bi postojale druge dve tabele takodje ako se obrise entitet trebalo bi da mi se obrisu i redovi iz druge dve tabele. Obrnuto nije obavezno, ako se obrise dodatna specijalizacija mogu ostati entitet i specijalizacija, ali ako se obrise specijalizacija mora nestati i dodatna specijalizacija.

Ja sto sam nesto generisao sql kod izgleda ovako

/*==============================================================*/
/* Table: ENTITET                                               */
/*==============================================================*/
create table ENTITET (
   ENTITETID            int                  not null,
   IME                  varchar(30)          null,
   constraint PK_ENTITET primary key nonclustered (ENTITETID)
)
go

/*==============================================================*/
/* Table: SPECIJALIZACIJA                                       */
/*==============================================================*/
create table SPECIJALIZACIJA (
   ENTITETID            int                  not null,
   SPECIJALIZACIJAID    int                  not null,
   PREZIME              varchar(30)          null,
   constraint PK_SPECIJALIZACIJA primary key nonclustered (ENTITETID, SPECIJALIZACIJAID)
)
go

/*==============================================================*/
/* Index: RELATIONSHIP_20_FK                                    */
/*==============================================================*/
create index RELATIONSHIP_20_FK on SPECIJALIZACIJA (
ENTITETID ASC
)
go

alter table SPECIJALIZACIJA
   add constraint FK_SPECIJAL_RELATIONS_ENTITET foreign key (ENTITETID)
      references ENTITET (ENTITETID)
go

/*==============================================================*/
/* Table: DODATNA_SPECIJALIZACIJA                               */
/*==============================================================*/
create table DODATNA_SPECIJALIZACIJA (
   ENTITETID            int                  not null,
   SPECIJALIZACIJAID    int                  not null,
   DODATNA_SPECIJALIZACIJA_ID int                  not null,
   SREDNJEIME           varchar(30)          null,
   constraint PK_DODATNA_SPECIJALIZACIJA primary key nonclustered (ENTITETID, SPECIJALIZACIJAID, DODATNA_SPECIJALIZACIJA_ID)
)
go

/*==============================================================*/
/* Index: RELATIONSHIP_21_FK                                    */
/*==============================================================*/
create index RELATIONSHIP_21_FK on DODATNA_SPECIJALIZACIJA (
ENTITETID ASC,
SPECIJALIZACIJAID ASC
)
go

alter table DODATNA_SPECIJALIZACIJA
   add constraint FK_DODATNA__RELATIONS_SPECIJAL foreign key (ENTITETID, SPECIJALIZACIJAID)
      references SPECIJALIZACIJA (ENTITETID, SPECIJALIZACIJAID)
go

Al sam ga zakomplikovao. Ne pitajte zasto 🙂
Hvala unapred na odgvoru i bilo kakvom savetu
Milos

 

[Nothingman]

Prvo da se ogradim da moj odgovor ne uzimas zdravo za gotovo, jer sam uvek bezao od baza kao djavo od krsta :d
Medjutim nije mi najjasnija recenica:

"Naime interesuje me da li u slucaju na slici trebam za povezivanje jedan na jedan te tri tabele da imam jos kljuceva ili je dovoljno samo primarni kljuc."

Koliko ja vidim u generisanom kodu gore, ti tu imas i kompozitne primarne kljuceve i foreign kljuceve. Recimo u kodu:

create table SPECIJALIZACIJA (
ENTITETID int not null,
SPECIJALIZACIJAID int not null,
PREZIME varchar(30) null,
constraint PK_SPECIJALIZACIJA primary key nonclustered (ENTITETID, SPECIJALIZACIJAID)
)

alter table SPECIJALIZACIJA
add constraint FK_SPECIJAL_RELATIONS_ENTITET foreign key (ENTITETID)
references ENTITET (ENTITETID)

imas napisano da svaka specijalizacija sadrzi ENTITETID kao foreign key, pa mi zato ta citirana recenica nije jasna 🙂
Ista stvar je i sa dodatnom specijalizacijom. Sto se tice brisanja specijalizacije kada se obrise entitet, kao i brisanja dodatne kada se obrise specijalizacija, proguglaj cascade delete rule. Mislim da je to ono sto ti treba.

Inace, da li bi mogao malo detaljnije da objasnis sta zelis da postignes? Koliko ja vidim iz ovog koda, ti mozes da imas jednu specijalizaciju vezanu za vise entiteta (jer je primarni kljuc kompozitni). Npr. neka ti tabela entitet ima sledece redove:

(ENTITETID, IME)
(1, Pera)
(2, Zika)
(3, Mika)

Prema ovome gore, kod tebe je sada moguce sledeca situacija u tabeli specijalizacija:
(SPECIJALIZACIJAID, ENTITETID, PREZIME)
(1,1,Zivkovic)
(1,2,Zivkovic)
(1,3, Pavlovic)

Dakle omogucio si ponavljanje SPECIJALIZACIJAID vise puta, jer ti je ona deo kompozitnog kljuca. Da li si to i zeleo? Slicno imas i kod dodatne specijalizacije.
Moras da kazes vise o tome sta zelis da postignes kako bi bilo moguce videti da li je sve ok ili ne. 🙂
Ovo sto trenutno imas je klasican identifying 1-1 relationship, medjutim to moze, ali i ne mora da odgovara tome sto zelis da postignes.

 

[milos_r]

Pa da pretpostavio sam da tako ne valja. Zato sam i pitao. Zbunio me ovaj program u kojem sam projektovao bazu on mi je generisao taj kod. Evo reci cu ponovo sta zelim. Recimo da imam tabelu entitet sa nekoliko atributa, zatim na nju treba biti vezana tabela sa dodatnim atributima tog entiteta koji se nece koristiti cesto to bi bila specijalizacija i na tu dodatnu jos jedna tabela koja ce ima neka specijalna polja koja zahtevaju da budu u dodatnoj tabeli. Dakle striktna veza jedan na jedan izmedju sve tri tabele za jedan entitet moze biti samo jedna specijalizacija i samo jedna dodatna specijalizacija. Kao sto sam rekao dodatne dve tabele nisu obavezne i mogu postojati samo ako postoji ona ispred njih. Sto znaci ako se obrise entitet to povlaci brisanje specijalizacije sto bi povuklo brisanje tabele dodatne specijlizacije. Obrnuto naravno ne vazi.
Dakle za jedan red u entitetu moze vaziti iskljucivo samo jedan red u druge dve tabele.
Ako sam malkice pojasnio super 😀
Pozz i hvala na prethodnom odgovoru i objasnjenju
Milos

 

[Nothingman]

Ok, tako sam nesto i pretpostavljao. Jedan od nacina da postignes to sto zelis je da u postojeci kod koji si gore napisao dodas UNIQUE constraint za sve kljuceve.

Tvoja struktura trenutno izgleda ovako:

Entitet:
ENTITETID (PK)

Specijalizacija:
SPECIJALZIACIJAID (PK) ---> setovati na unique
ENTITETID (PK) (FK) ---> setovati na unique

Dodatna:
DODATNAID (PK) ---> setovati na unique
SPECIJALZIACIJAID (PK) (FK) ---> setovati na unique
ENTITETID (PK) (FK) ---> setovati na unique

I trenutni problem je taj, sto zbog ovih kompozitnih kljuceva mozes imati visestruke kombinacije sa istim. Da bi to sprecio mozes dodati UNIQUE constraint pored svakog kljuca u tabelama specijalizacija i dodatna specijalizacija. Kao sto znas i sam, svaki primarni kljuc je unique, ali tebi ovde treba da i parcijalni kljucevi od kojih se sastoji taj primarni kljuc budu takodje unique i da se ne mogu ponavljati u tabeli. Za primer iz mog proslog posta bi to znacilo da ne mozes vise imati sledece:

(SPECIJALIZACIJAID, ENTITETID, PREZIME)
(1,1,Zivkovic)
(1,2,Zivkovic)
(1,3, Pavlovic)

jer bi se DBMS pobunio pri pokusaju ubacivanja (rekao bi da je SPECIJALIZACIJAID unique i da ne moze da se ponavlja).

takodje ti ne bi dozvolio ni sledece:
(SPECIJALIZACIJAID, ENTITETID, PREZIME)
(1,1,Zivkovic)
(2,2,Zivkovic)
(3,1, Pavlovic)

jer u ovom slucaju ti se ponavlja ENTITETID, a posto je unique DBMS ne bi to dozvolio. A to je upravo ono sto zelis, zar ne? Da ti ne dozvoli da jedan entitet ima 2 specijalizacije, nego samo jednu.

Evo kako je ovo kod mene izgenerisao Mysql workbench:

-- -----------------------------------------------------

-- Table `mydb`.`entitet`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`entitet` (

`entitet_id` INT NOT NULL ,

PRIMARY KEY (`entitet_id`) )

ENGINE = InnoDB;





-- -----------------------------------------------------

-- Table `mydb`.`specijalizacija`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`specijalizacija` (

`specijalizacija_id` INT NOT NULL ,

`entitet_entitet_id` INT NOT NULL ,

PRIMARY KEY (`specijalizacija_id`, `entitet_entitet_id`) ,

INDEX `fk_specijalizacija_entitet` (`entitet_entitet_id` ASC) ,

UNIQUE INDEX `specijalizacija_id_UNIQUE` (`specijalizacija_id` ASC) ,

UNIQUE INDEX `entitet_entitet_id_UNIQUE` (`entitet_entitet_id` ASC) ,

CONSTRAINT `fk_specijalizacija_entitet`

FOREIGN KEY (`entitet_entitet_id` )

REFERENCES `mydb`.`entitet` (`entitet_id` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;





-- -----------------------------------------------------

-- Table `mydb`.`dodatna_spec`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`dodatna_spec` (

`dodatna_spec_id` INT NOT NULL ,

`specijalizacija_specijalizacija_id` INT NOT NULL ,

`specijalizacija_entitet_entitet_id` INT NOT NULL ,

PRIMARY KEY (`dodatna_spec_id`, `specijalizacija_specijalizacija_id`, `specijalizacija_entitet_entitet_id`) ,

INDEX `fk_dodatna_spec_specijalizacija1` (`specijalizacija_specijalizacija_id` ASC, `specijalizacija_entitet_entitet_id` ASC) ,

UNIQUE INDEX `dodatna_spec_id_UNIQUE` (`dodatna_spec_id` ASC) ,

UNIQUE INDEX `specijalizacija_specijalizacija_id_UNIQUE` (`specijalizacija_specijalizacija_id` ASC) ,

UNIQUE INDEX `specijalizacija_entitet_entitet_id_UNIQUE` (`specijalizacija_entitet_entitet_id` ASC) ,

CONSTRAINT `fk_dodatna_spec_specijalizacija1`

FOREIGN KEY (`specijalizacija_specijalizacija_id` , `specijalizacija_entitet_entitet_id` )

REFERENCES `mydb`.`specijalizacija` (`specijalizacija_id` , `entitet_entitet_id` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

Sto se tice brisanja specijalizacija kada se izbrise entitet, potrebno je da dodas ON DELETE CASCADE kao pravilo (ON DELETE NO ACTION zamenis u ON DELETE NO CASCADE u mom gornjem primeru. Mozda se kod MS SQL servera neznatno razlikuje sintaksa , ali snaci ces se vec 🙂). Na taj nacin se brisu sve specijalizacije i dodatne spec. u kojima se kao foreign key nalazi izbrisani entitet.

Moj savet ti je da izgenerises bazu po ovim uputstvima, i da nakon toga pokusas da ubacujes podatke u nju kako bi istestirao sve slucajeve koji te zanimaju (kao i brisanje za cascade), za slucaj da sam nesto prevideo 🙂

 

[milos_r]

Mozda mi je najednostavnije ovako to da uradim. Mislim da sam postigao zeljeni efekat.

CREATE TABLE [dbo].[Entitet](
	[EntitetID] [int] IDENTITY(1,1) NOT NULL,
	[Ime] [nvarchar](50) NULL,
 CONSTRAINT [PK_Entitet] PRIMARY KEY CLUSTERED 
(
	[EntitetID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[Specijalizacija](
	[SpecijalizacijaID] [int] NOT NULL,
	[Prezime] [nvarchar](50) NULL,
 CONSTRAINT [PK_Specijalizacija] PRIMARY KEY CLUSTERED 
(
	[SpecijalizacijaID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[DodatnaSpecijalizacija](
	[DodatnaSpecijalizacija] [int] NOT NULL,
	[SrednjeIme] [nvarchar](50) NULL,
 CONSTRAINT [PK_DodatnaSpecijalizacija] PRIMARY KEY CLUSTERED 
(
	[DodatnaSpecijalizacija] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Specijalizacija]  WITH CHECK ADD  CONSTRAINT [FK_Specijalizacija_Entitet] FOREIGN KEY([SpecijalizacijaID])
REFERENCES [dbo].[Entitet] ([EntitetID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Specijalizacija] CHECK CONSTRAINT [FK_Specijalizacija_Entitet]
GO

ALTER TABLE [dbo].[DodatnaSpecijalizacija]  WITH CHECK ADD  CONSTRAINT [FK_DodatnaSpecijalizacija_Specijalizacija] FOREIGN KEY([DodatnaSpecijalizacija])
REFERENCES [dbo].[Specijalizacija] ([SpecijalizacijaID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DodatnaSpecijalizacija] CHECK CONSTRAINT [FK_DodatnaSpecijalizacija_Specijalizacija]
GO

 

[Nothingman]

Mozes i tako, s tim sto gubis fleksibilnost. Ako jednog dana odlucis da ti ipak treba 1-n veza izmedju recimo entiteta i specijalizacije, nece biti trivijalno da se to izvede. Tebi sada primarni kljuc specijalizacije predstavlja isto sto i primarni kljuc entiteta. To automatski ogranicava mogucnost da nekada u buducnosti omogucis da vise specijalizacija pripadaju jednom entitetu. Da bi to izveo moraces da dodajes nove kljuceve, da menjas upite u programu i slicno. Dok u primeru koji sam naveo to je trivijalno izvesti obicnim izbacivanjem unique constrainta sa parcijalnog kljuca 🙂

 

[solomonbg]

Ne znam da li je bitno da vodi racuna o fleksibilnosti koja mu mozda nece trebati...Pitanje je i kako ce se te tabele puniti, i nije bas lako dati precizan odgovor

Mozda je sledece moguce resenje, jednostavno i bez generickih kljuceva koji mozda nece trebati uopste:

Entitet:
ENTITETID (PK)

Specijalizacija:

ENTITETID (PK) (FK)

Dodatna:
ENTITETID (PK) (FK) --s tim sto ovde moze i FK na Specijalizacija ili direktno na Entitet ako je potrebna veza 1 i 3 tabele....


A od ovoga se relativno laki pravi 1-n ako je potrebno u buducnosti:

Specijalizacija:

ENTITETID postaje samo (FK) na ENTITET a dodaje se novi PK koji ima vrednosti od 1 do ukupnog broja redova



ps. jel postoji "jak" razloga zasto si ovo inicijalno i razbio na tri tabele?

 

[Nothingman]

Ne znam da li je meni nesto promaklo ili si ti dao identicno resenje koje je on vec odabrao? 🙂

Cim moras da dodajes nove kljuceve to znaci da buduca izmena baze vise nije trivijalna. Ona se mozda cini trivijalnom na ovako prostom primeru, ali u praksi kada se nad nekom bazom izvrsava gomila softvera, izmene tog tipa mogu da proizvedu nezgodne bagove. Slazem se da njemu fleksibilnost mozda i ne treba, ali uvek je bolje navesti prednosti i mane svake opcije 🙂

 

[milos_r]

ps. jel postoji "jak" razloga zasto si ovo inicijalno i razbio na tri tabele?

Pa mozda i ne ali ni to nisam umeo drugacije da resim. Naime entitet mi ima mislim recimo 50 polja. Klasicna reprezentacija svih mogucih atributa zaposlenog. Da ne bih tako veliku tabelu stalno zvao, podelio sam je na tri u prvoj samo ime prezime user i pass u drugoj su svi ostali sifrarnici i u trecoj su neki vrlo osetljivi podaci.

 

[solomonbg]

Ne znam da li je meni nesto promaklo ili si ti dao identicno resenje koje je on vec odabrao? 🙂

Cim moras da dodajes nove kljuceve to znaci da buduca izmena baze vise nije trivijalna. Ona se mozda cini trivijalnom na ovako prostom primeru, ali u praksi kada se nad nekom bazom izvrsava gomila softvera, izmene tog tipa mogu da proizvedu nezgodne bagove. Slazem se da njemu fleksibilnost mozda i ne treba, ali uvek je bolje navesti prednosti i mane svake opcije 🙂

mozda, ja sam se vise fokusirao na ono sto si ti predlozio kao resenje🙂 Istina da znaci dosta do toga kakav softver radi nad tom bazom, premda za vecinu stvari to nije problem (ako su entiteti/klase napravljeni nad bazom, a "visi softver" koristi te entitete, onda je potrebna samo izmena entiteta i to je relativno trivijalno-- npr za Oracle ADF, verujem da je veoma slicno i za .NET)...


edit:
jeste isto resenje,sad pogledah, jedino ja ne bih menjao nazive PK nego ostavio isti naziv - kada vec i jeste isti naziv (3 tabele koje predstavljaju "jednu logicku tabelu")