Proiect BDA - Baze de Date Avansate

O societate comerciala de publicitate isi propune realizarea unui baze de date privind gestionarea activitatii sale. Societatea detine in proprietate un site.

Fiecare client care doreste sa i se faca publicitate se va inscrie cu urmatoarele date: codul unic, denumire, localitate, adresa, telefon, tipul din care face parte (persoana fizica sau juridica), domeniul de activitate.

In momentul in care clientul se decide a-si face publicitate prin intermediul firmei, acesta plaseaza o comanda care se identifica prin numar comanda, data comanda si valoare comanda. Pe comanda se va stipula cantitatea comandata si pretul comandat. In aceasta comanda clientul solicita ce tip de publicitate doreste (prin intermediul site-ului firmei de publicitate(1), prin intermediul bannere-lor(2), prin intermediul site-urilor specificate de client(3)), urmand ca fiecare din cele 3 tipuri de servicii oferite sa aiba tarife diferite.

Pentru serviciile oferite, societatea elibereaza o factura de vanzare care trebuie sa contina numarul facturii, data facturii, data scadenta si valoarea facturii. Pe aceasta factura se stipuleaza cantitatea vanduta si pretul de vanzare. Societatea stabileste ca data scadentei sa fie obligatoriu mai mare decat data facturii.

Incasarea contravalorii facturilor eliberate se poate face doar prin plata bancara din partea clientilor. Asadar, pe documentul de incasare trebuie stipulate numarul documentului, data documentului, tipul documentului (ordin de plata sau fila cec), suma incasata si banca platitoare.

Lunar, societatea beneficiaza din partea furnizorilor sai de servicii precum: intretinere, chirie, lumina, telefonie si altele. Fiecare furnizor se identifica prin cod furnizor, denumire, localitate, adresa, telefon si tip furnizor (persoana fizica sau juridica). Cu furnizorii cu care societatea are activitati regulate, aceasta este obligata a intocmi un contract caracterizat prin numar contract, data incheierii, data sfarsirii, eventualele clauze si valoare contract.

Facturile de intrare sunt identificate cu numar factura, data factura, data scadenta si valoare factura. Pe facturi este trecuta cantitatea facturata si pretul facturat.

Documentul prin care societatea realizeaza plata catre furnizorii sai trebuie sa contina numarul documentului, data acestuia, tipul documentul de plata (care poate fi ordin de plata, fila cec sau chitanta) si suma platita.

Platile se fac dupa emiterea facturii.

Cu un furnizor se incheie un singur contract.

Un furnizor poate oferi mai multe servicii cu tarif diferit.

Societate nu incheie contracte cu clientii deoarece acestia sunt ocazionali, asadar, legea nu impune a incheie contracte cu cei cu care societatea nu are activitati cel putin lunare.

Data comenzii este data implicita a sistemului.

FURNIZOR (CodFurnizor, DenumireFurnizor, LocalitateFurnizor, AdresaFurnizor, TelefonFurnizor, TipFurnizor)

SERVICIU (TipServiciu, TarifServiciu)

COMANDA (NumarComanda, DataComanda, CodClient)

CLIENT (CodCient, DenumireClient, LocalitateClient, AdresaClient, TelefonClient, TipClient, DomeniuActivitate)

DOCUMENTINCASARE (NumarDocumentIncasare, DataDocumentIncasare, TipDocumentIncasare, SumaIncasata, Banca)

FACTURAVANZARE (NumarFacturaVanzare, DataFacturaVanzare, DataScadentaVanzare, ValoareFacturaVanzare)

FACTURA (NumarFactura, DataFactura, DataScadenta, ValoareFactura, CodFurnizor, NumarContract)

DOCUMENTPLATA (NumarDocumentPlata, DataDocumentPlata, TipDocumentPlata, SumaPlatita)

CONTRACT (NumarContract, DataContract, DataSfarsit, Clauze, ValoareContract, CodFurnizor)

OFERA (CodFurnizor, TipServiciu, Cantitate, Pret)

CONTINE (TipServiciu, NumarComanda, CantitateComandata, PretComandat)

GENEREAZA (NumarComanda, NumarFacturaVanzare)

ACHITA (NumarFacturaVanzare, NumarDocumentIncasare)

LINIEVANZARE (TipServiciu, NumarFacturaVanzare, CantitateVanduta, PretVanzare)

LINIEFACTURA (TipServiciu, NumarFactura, CantitateFacturata, PretFacturat)

PLATESTE (NumarDocumentPlata, NumarFactura)

CERINTE PROIECT - ETAPA I

a. Proiectati un model pentru baza de date conform temei alese. Fiecare student isi va formula propriul enunt (maxim 2 pagini tehnoredactate in Word), astfel incat sa rezulte in modelul relational minim 5-6 tabele.

b. Implementati in SQL Server baza de date proiectata la cerinta anterioara (tabelele si diagrama de relatii) si stabiliti optiunile pentru stergere si actualizare in cascada, acolo unde este cazul.

CERINTE PROIECT - ETAPA II

a. Importati in baza de date, intr-un nou tabel, datele din urmatorul fisier Excel: Colaboratori.xls

b. Stabiliti restrictii asupra datelor la nivel de tabel pe fiecare dintre tabelele bazei de date(CHECK CONSTRAINTS) Minimim 6 exemple.

1. TipCient=’PF’ or TipClient=’PJ’

2. DataScadentaVanzare>DataFacturaVanzare

3. TipDocumentIncasare=’OP’ or TipDocumentIncasare=’CEC’

4. TipFurnizor=’PF’ or TipFurnizor=’PJ’

5. TipDocumentPlata=’OP’ or TipDocumentPlata=’CEC’ or TipDocumentPlata=’chitanta’

6. DataSfarsit>=DataContract

8. CodFurnizor<>0, CodClient>0, NumarComanda>0, NumarDocumentIncasare>0, NumarDocumentPlata>0, SumaIncasata>0, SumaPlatita>0, NumarFacturaVanzare>0, NumarFactura>0, NumarContract>0, Cantitate>0, CantitateComandata>0, CantitateVanduta>0

c. Realizati 3 proceduri tip TRIGGER in baza de date (cate una pentru fiecare dintre operatiile DELETE, INSERT, UPDATE)

C1. Realizati un trigger pe tabelul Comanda care sa nu permita stergerea acelor comenzi care au data mai veche de 1 ianuarie 2008.

Create trigger StergereComenziVechi on Comanda for delete on

If exists (Select DataComanda from deleted

Where DataComanda <=’1/1/2008’)

Begin

Raiserror (‘Nu stergeti comenzile vechi’, 18, 1)

Rollback transaction

End

C2. Realizati un trigger pe tabelul FacturaVanzare care sa nu imi permita adaugarea unei facturi cu o valoare mai mica de 50 lei.

Create trigger VerificaValoareFacturaVanzare on FacturaVanzare for insert as

If exists (Select ValoareFacturaVanzare from inserted

Where ValoareFacturaVanzare < '50')

Rollback transaction

C3. Realizari un trigger pe tabelul Comanda care sa permita adaugarea comenzilor mai vechi de 1 ianuarie 2008 in tabelul IstoricComenzi.

Create trigger IstoricComenzi on Comanda for update as

If exists (Select DataComanda from deleted

Where DataComanda <=’1/1/2008’)

Insert into IstoricComenzi (NumarComanda, DataComanda, CodClient)

Select NumarComanda, DataComanda, getdate()

From deleted

CERINTE PROIECT - ETAPA III

Realizati 10 obiecte de tip View. Incercati, pe cât posibil, sa exemplificati:

1. Interogari cu grupari de date si utilizarea functiilor agregat (SUM, COUNT, AVG, MIN, MAX)

2. Filtre cu operatorii LIKE, IN, BETWEEN

3. Expresii calculate cu functiile prezentate la curs (DateADD, DateDiff, DatePart, Left, Substring, etc.)

Atentie: functiile Rank, Row_number si Ntile nu pot fi utilizate in cadrul View-urilor

4. Interogari de tip UNION

a) Realizati un obiect de tip view care sa afiseze numarul total al contractelor, suma totala a valorii acestora, valoare minima si valoarea maxima a contractelor incheiate. (denumire: InformatiiContracte).

SELECT COUNT(NumarContract) AS [numar contracte], SUM(ValoareContract) AS [valoare totala contracte], MIN(ValoareContract) AS [valoare minima contract], MAX(ValoareContract) AS [valoare maxima contract]

FROM dbo.CONTRACT

b) Realizati un obiect de tip view care sa afiseze numarul total al facturilor de vanzare emise intre iunie 2008 si decembrie 2008, valoarea totala si media valorii acestor facturi. (denumire: InformatiiFacturiVanzare).

SELECT COUNT(NumarFacturaVanzare) AS [numar total facturi vanzre], AVG(ValoareFacturaVanzare) AS [medie facturi vanzare], SUM(ValoareFacturaVanzare) AS [valoare totala facturi vanzare]

FROM dbo.FACTURAVANZARE