Proiectarea și elaborarea bazei de date în MS SQL Server 2003

I.Scopul lucrarii: De a se familiariza cu proiectare bazelor de date utilizind forme normale

II.Sarcina: Crearea unei baze de date individuale conform tuturor cerintelor din indrumarul de laborator(crearea tabelelor, indecsilor, constrîngerilor, viziunilor, procedurilor stocate, trigger-ilor, precum si de efectuare a unui back-up a bazei de date, exportarea ei în Microsoft Excel si publicarea în Internet)

III.Continutul lucrarii de laborator

Domeniul de interes

În calitate de domeniu de interes noi am ales un sistem de comandari si livrari care contine patru tabele principale:

1) produsele

2) producatori

3) comandari

4) clienti

Pentru a efectua forma normala 3 noi utilizam pentru atribute urmatoarele notatii:

Comanda_Id=A

Produs_Id=B

Pret=C

Cantitatea=D

Reducerea=E

Client_Id=F

Produs_Id=B

Marca=G

Producator_Id=H

Categoria=J

Unit_de_mas=I

Pret=C

Producator_Id=H

Denumirea_firma=K

Numele_Prod=L

Post=M

Telefon=N

Adresa=O

Client_Id=F

Client_Nume=P

Status=R

Adr_Client=S

Dependentele functionale

Cercetind domeniul de interes noi ajungem la urmatoarele dependente functionale:

F={AB->CDEF,B->GHJIC,H->KLMNO,F->PRS}

Algoritmul de sinteza

În calitate de algoritm de sinteza noi am apelat la construirea schemei bazei de date utilizind forma normala 3.

Avem urmatoarele dependente functionale:

F={ AB->CDEF,B->GHJIC,H->KLMNO,F->PRS}

Adaugam o dependenta functionala artificiala:

F={ AB->CDEF,B->GHJIC,H->KLMNO,F->PRS, }

Facem dezagregarea:

F={AB->C,AB->D,AB->E,AB->F,B->G,B->H,B->J,B->I,B->C,H->K,H->L,H->M,H->N,

H->O,F->P,F->R,F->S , }

(F)+F/{AB->C}=<AB,ABDEFGHJIC>-aruncam

(F)+F/{AB->D}=<AB,ABEFGHJICKLMNOPRS>

(F)+F/{AB->E}=<AB,ABDFGHJICKLMNOPRS>

(F)+F/{AB->F}=<AB,ABDEGHIJKLMNOPRS>

(F)+F/{B->G}=<B,BHJICKLMNO>

(F)+F/{B->H}=<B,BGHJICKLMNO>

(F)+F/{B->J}=<B,BHGJICKLMNO>

(F)+F/{B->I}=<B,BHJGICKLMNO>

(F)+F/{B->C}=<B,BHJIGKLMNO>

(F)+F/{H->K}=<H,HLMNO>

(F)+F/{H->L}=<H,HKMNO>

(F)+F/{H->M}=<H,HLKNO>

(F)+F/{H->N}=<H,HLMKO>

(F)+F/{H->O}=<H,HLMNK>

(F)+F/{F->P}=<F,FRS>

(F)+F/{F->R}=<F,FPS>

(F)+F/{F->S}=<F,FRP>

](F)+F/{ABCDEFGHIJKLMNOPRS->&}=<ABCDEFGHIJKLMNOPRS>

S-a aruncat dependenta functionala AB->C

Astfel am obtinut norma redundanta:

F={AB->D,AB->E,AB->F,B->G,B->H,B->J,B->I,B->C,H->K,H->L,H->M,H->N,

H->O,F->P,F->R,F->S , }

Facem reducerea la stinga:

(B)+F=<B,BGHJICKLMNO>

(A)+F=<A>

(BCDEFGHIJKLMNOPRS)+F=< BCDEFGHIJKLMNOPRS>

(ACDEFGHIJKLMNOPRS)+F=< ACDEFGHIJKLMNOPRS >

(ABDEFGHIJKLMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABEFGHIJKLMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABFGHIJKLMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABGHIJKLMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABHIJKLMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABIJKLMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABJKLMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABKLMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABLMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABMNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABNOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABOPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABPRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABRS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

(ABS)+F=< ABCDEFGHIJKLMNOPRS& >-aruncam

Impartim in clase de echivalenta

F={AB->D,AB->E,AB->F,B->G,B->H,B->J,B->I,B->C,H->K,H->L,H->M,H->N,

H->O,F->P,F->R,F->S , }