Studiu de caz - Proiectarea si interogarea BD în SQL

Studiu de caz - Proiectarea si interogarea BD în SQL

Contabilitatea firmei

Pentru realizarea bazei de date se vor analiza documentele utilizate in cadrul compartimentului de contabilitate. Se constata ca firma dispune de un plan de conturi în care figureaza simbol cont, nume cont, tip cont (activ, pasiv). Operatiile contabile sunt înregistrate în registrul jurnal în baza unor documente justificative ce sunt identificate prin cod document, tip document (factura, proces verbal, etc.) si data document. Fiecare operatie cont 23323m122x abila din registru jurnal este numerotata si datata si i se poate adauga o explicatie.

O operatie contabila poate sa contina mai multe conturi specificându-se pentru fiecare suma debitoare sau suma creditoare, dupa caz. Din balanta de verificare se constata ca pentru fiecare cont se cunoaste soldul initial debitor sau soldul initial creditor si se calculeaza rulajele debitoare (totalul sumelor de pe debit din operatiile contabile) si cele creditoare (totalul sumelor creditoare din operatiile contabile).

Reguli de gestiune:

Simbolurile conturilor sunt unice

Unui cont îi corespunde un singur sold initial debitor sau un sold initial creditor

Numerele operatiilor contabile sunt unice

Pentru simplificarea problemei presupunem ca unui document justificativ îi este atribuit în contabilitate un cod unic

Unei operatii contabile îi corespunde un singur document justificativ

Un document poate genera mai multe operatii contabile

O operatie contabila poate contine mai multe conturi debitoare/creditoare

Se cere

Conceperea modelului relational prin normalizare

Sa se exprime in limbajul SQL urmatoarele comenzi:

Sa se adauge in tabelul Conturi contul "Fond Comercial" cu simbol 207 (INSERT

Sa se creeze tabelul ConturiD2 cu simbolurile, numele si soldurile initiale debitoare ale conturilor din clasa a 2-a (Imobilizari) la care soldul initial debitor depaseste 6.000 (SELECT. INTO.

Pentru contul cu simbolul 1012 sa se completeze tipul contului cu litera "P" si sa se mareasca soldul initial creditor cu 300 RON (UPDATE)

Presupunând ca numele de conturi nu au fost scrise corect sa se transforme toate realizarile din câmpul nume în majuscule (UPDATE)

Sa se stearga din tabela Operatii toate operatiile contabile mai vechi de 1 ianuarie 2002 de zile si operatia cu numarul 5 (DELETE

Afisati toate informatiile despre documentele de tipul "Proces Verbal"

Afisati lista ordonata alfabetic (dupa nume cont) cu simbolurile si numele conturilor din clasele 1 si 2.

Afisati lista cu numele, simbolurile si soldurile initiale pentru primele 3 conturi cu cele mai mari solduri initiale creditoare.

Calculati pentru fiecare cont soldul initial debitor in euro fara zecimale (un euro =3,5 RON) si ordonati lista descrescator dupa Soldul initial debitor iar pentru conturile cu acelasi sold, ordonati alfabetic dupa nume cont.

Afisati lista operatiilor contabile (numar, data si explicatie) dintr-o anumita luna a anului 2006 precizata ca parametru. In cazul operatiilor fara explicatie se va preciza intr-o coloana numita Observatii textul "De completat", pentru celelalte se va afisa textul "Corect"

Afisati lista cu documentelor (cod si tip document) care au justificat operatii contabile in ultimele 30 de zile. In lista nu vor figura documentele de tip "factura" si "chitanta"

Caz particular : Jonctiuni externe (LEFT/RIGHT JOIN) : Care sunt conturile care nu au rulaje ?

Afisati lista fara duplicate a conturilor care au înregistrat rulaje debitoare între 1 ianuarie si 30 aprilie 2006.

Calculati rulajele totale debitoare si creditoare pentru fiecare dintre "5121" si "5122".

Calculati câte documente justificative exista în firma pe fiecare tip de document dupa 1 ianuarie 2006 si ordonati lista descrescator dupa numarul de documente existente (doar pentru tipurile de documente din care exista cel putin 3)

Calculati suma maxima cu care a fost creditat fiecare cont de activ.

Calculati Soldul final (Sold initial+Rulaje Debitoare-Rulaje creditoare) pentru fiecare Imobilizari (clasa 2).

Pentru aceasta interogare am adaugat în baza de date un tabel intitulat Conturi Vechi cu urmatoarele câmpuri: SimbolCont si NumeCont

Se doreste afisarea unei liste cu 3 coloane ce va contine numele si simbolurile conturilor din tabelele Conturi si ConturiVechi iar în cea de-a treia coloana (numita Observatii) se va afisa textul "Cont utilizat" pentru conturile provenite din tabelul Conturi si "Neutilizat" pentru cele provenite din tabelul ConturiVechi.

Observatii pentru interogari UNION

- toate sursele de date trebuie sa contina acelasi numar de câmpuri

- câmpurile din sursele de date sa fie de acelasi tip

- ordinea câmpurilor trebuie sa fie aceeasi

Sintaxa:

TRANSFORM expresie

Instructiune de selectie

PIVOT câmpul ce urmeaza a fi afisat pe coloanele tabelului

Sa se realizeze o interogare de tip analiza încrucisata pentru a determina rulajele debitoare totale ale fiecarui cont in fiecare an. Se vor ordona anii pe coloane si simbolurile conturilor pe linii.

Sa se determine la cate operatii contabile a participat fiecare cont în fiecare luna a anului 2006. Se vor ordona lunile anului pe coloane si simbolurile conturilor pe linii.

Un exemplu particular îl reprezinta interogarile de tip INSERT INTO ce permit adaugarea de date dintr-o tabela în alta tabela:

Sa se adauge in tabela ConturiVechi toate conturile de cheltuiei (din clasa 6).

Sa se afiseze lista conturilor care au fost debitate cu cea mai mare suma în cadrul unei operatii contabile (se utilizeaza ALL

Sa se afiseze numerele pentru toate operatiile care au avut loc in zile in care a fost creditat contul 5121 (se utilizeaza ANY

Care sunt conturile din tabelul Conturi care nu se regasesc si în tabela ConturiVechi.

Sa se diminueze cu 10% rulajele debitoare din operatiile înregistrate in ultimele 90 de zile (se utilizeaza IN

Sa se stearga din tabela conturi toate conturile de Activ care au fost utilizate de mai putin de 2 ori in operatii contabile (se utilizeaza IN