ACADEMIA DE STUDII ECONOMICE BUCURESTI

PROIECT

- BAZE DE DATE APROFUNDATE -

BAZA DE DATE PENTRU GESTIUNEA UNEI BIBLIOTECI

Proiectul isi propune realizarea unei baze de date pentru administrarea unei biblioteci. Este realizat folosind Microsoft SQL Server 2008.

Se considera urmatorul dictionar al datelor:IDCARTE IDAUTORTITLU NUMEAUTORAN PRENUMEAUTORLIMBA IDEDITURAIDCITITOR NUMEEDITURANUMECITITOR TELEFONEDITURAPRENUMECITITOR DATAIMPRUMUT CNP DATARESTITUIRE TELEFON PENALIZARESECTOR VALOARE

Reguli de gestiune:- pot exista mai multi studenti cu acelasi nume- data restituirii trebuie sa coincida sau sa fie ulterioara datei imprumutului- codul numeric personal trebuie sa fie alcatuit din 13 cifre- un autor poate scrie mai multe carti- la o editura pot aparea mai multe carti- sectorul trebuie sa fie 1,2,3,4,5 sau 6

Diagrama bazei de date biblioteca:

Modelul logic al datelor se prezinta dupa cum urmeaza:

CARTE (IDCARTE, TITLU, AN, LIMBA, IDEDITURA)CITITOR (IDCITITOR, NUMECITITOR, PRENUMECITITOR, CNP, TELEFON, SECTOR)AUTOR (IDAUTOR, NUMEAUTOR, PRENUMEAUTOR)EDITURA (IDEDITURA, NUMEEDITURA, TELEFONEDITURA)CARTEAUTOR (IDCARTE, IDAUTOR)FISA (IDCITITOR, IDCARTE , DATAIMPRUMUT, DATARESTITUIRE, PENALIZARE, VALOARE)

TRIGGERS :

Se va crea tabela Arhiva, avand cheia primara ID si elemente explicatie si data operatie. ID-ul se va completa automat.

- Realizati un trigger care sa adauge in tabel toti cititorii ce vor fi adaugati in tabela Cititor.

CREATE TRIGGER [dbo].[ADAUGA_CITITOR] ON [dbo].[CITITOR] AFTER INSERTAS BEGIN

INSERT INTO ARHIVA(EXPLICATIE,DATAOPERATIE)SELECT 'A FOST ADAUGAT CITITORUL' + NUMECITITOR + ''+ PRENUMECITITOR , GETDATE()FROM INSERTEDEND

- Realizati un trigger care sa permita actualizarea numelui studentilor din tabela Cititor.

ALTER TRIGGER [dbo].[MODIFICA_CITITOR] ON [dbo].[CITITOR] AFTER UPDATEAS BEGIN

INSERT INTO ARHIVA(EXPLICATIE,DATAOPERATIE)SELECT 'S-A MODIFICAT CITITORUL' + DELETED.NUMECITITOR + ''+ DELETED.PRENUMECITITOR+'CU'+ INSERTED.NUMECITITOR +''+ INSERTED.PRENUMECITITOR,GETDATE()FROM DELETED,INSERTEDEND

- Realizati un trigger care sa permita stergerea studentilor din sectorul 3.

ALTER TRIGGER [dbo].[STERGE_CITITOR] ON [dbo].[CITITOR] AFTER DELETEAS

BEGINIF EXISTS (SELECT SECTOR FROM DELETED WHERE SECTOR='3')

BEGINROLLBACK TRANSACTION

ENDELSE BEGININSERT INTO ARHIVA(EXPLICATIE,DATAOPERATIE)SELECT 'S-A STERS CITITORUL'+ NUMECITITOR,GETDATE()FROM DELETEDENDEND

VIEWS :

1.Sa se afiseze toate cartile al caror titlu incepe cu litera I.

SELECT TITLUFROM dbo.CARTEWHERE (TITLU LIKE N'I%')

2.Sa se afiseze toti cititorii de sex feminine din tabela Cititor.SELECT CNP, NUMECITITOR, PRENUMECITITORFROM dbo.CITITORWHERE (LEFT(CNP, 1) = '2')

3. Sa se afiseze toti cititorii nascuti in anul 1986.SELECT NUMECITITOR, PRENUMECITITOR, CNPFROM dbo.CITITORWHERE (CNP LIKE N'_86%')

4.Sa se afiseze toate cartile aparute intre anii 1920 si 1990.SELECT AN, TITLUFROM dbo.CARTEWHERE (AN > N'1920') AND (AN < N'1990')

5.Sa se afiseze toate cartile scrise de Liviu Rebreanu.SELECT dbo.AUTOR.NUMEAUTOR, dbo.AUTOR.PRENUMEAUTOR, dbo.CARTE.TITLU, COUNT(dbo.CARTE.IDCARTE) AS Expr1FROM dbo.CARTEAUTOR INNER JOIN dbo.AUTOR ON dbo.CARTEAUTOR.IDAUTOR = dbo.AUTOR.IDAUTOR INNER JOIN dbo.CARTE ON dbo.CARTEAUTOR.IDCARTE = dbo.CARTE.IDCARTEGROUP BY dbo.AUTOR.NUMEAUTOR, dbo.AUTOR.PRENUMEAUTOR, dbo.CARTE.TITLUHAVING (dbo.AUTOR.NUMEAUTOR = N'REBREANU')6.Sa se afiseze toti cititorii nascuti primavara.SELECT NUMECITITOR, PRENUMECITITOR, CNP

FROM dbo.CITITORWHERE (CNP LIKE N'____[345]%')

7.Sa se afiseze cititorii din toate sectoarele in afara de sectorul 6.SELECT IDCITITOR, NUMECITITOR, PRENUMECITITOR, SECTORFROM dbo.CITITORWHERE (SECTOR LIKE N'[^6]')

8.Sa se afiseze cititorii care au restituit cartile aflate in imprumut.SELECT dbo.CITITOR.NUMECITITOR, dbo.CITITOR.PRENUMECITITOR, dbo.FISA.DATARESTITUIREFROM dbo.FISA INNER JOIN dbo.CITITOR ON dbo.FISA.IDCITITOR = dbo.CITITOR.IDCITITORWHERE (dbo.FISA.DATARESTITUIRE < GETDATE())

PROCEDURI STOCATE :

1.Sa se creeze o procedura stocata pentru a afisa datele unui cititor al carui CNP este specificat ca parametru. In cazul in care parametrul nu este specificat se va afisa un mesaj de eroare.

CREATE PROCEDURE [dbo].[AFISEAZA_CITITOR]@CNP NCHAR(13)=NULL

ASIF @CNP=NULLBEGIN

PRINT 'NU ATI FURNIZAT UN CNP'RETURN

ENDELSE SELECT * FROM CITITOR WHERE CNP=@CNP

Execute [dbo].[AFISEAZA_CITITOR] @cnp=’2870104100159’

2. Sa se creeze o procedura stocata pentru a afisa cititorii dintr-o anumita localitate specificata ca parametru. In cazul in care parametrul nu este specificat se va afisa un mesaj de eroare.

CREATE PROCEDURE [dbo].[AFISEAZA_SECTOR]@PSECTOR NVARCHAR(50) = NULL

ASIF @PSECTOR=NULLBEGIN

PRINT 'NU ATI FURNIZAT SECTORUL'RETURN

ENDELSESELECT * FROM CITITOR WHERE @PSECTOR=SECTOR

3.Sa se afiseze cartile scrise de un anumit autor specificat prin parametru.

CREATE PROCEDURE [dbo].[CAUTA_CARTI]@PNUMEAUTOR NVARCHAR(50)ASSELECT TITLU,AN FROM CARTE INNER JOIN CARTEAUTOR ON CARTE.IDCARTE=CARTEAUTOR.IDCARTE INNER JOIN AUTOR ON CARTEAUTOR.IDAUTOR=AUTOR.IDAUTORWHERE @PNUMEAUTOR=AUTOR.NUMEAUTOR

4.Sa se afiseze toate cartile imprumutate avand Id-ul specificat ca parametru.

CREATE PROCEDURE [dbo].[DETALIU_IMPRUMUT] @PIDCARTE NVARCHAR(50)

ASIF @PIDCARTE=NULLBEGINPRINT 'NU ATI FURNIZAT CORECT CODUL'RETURNENDELSESELECT TITLU , DATAIMPRUMUT, DATARESTITUIRE FROM CARTE INNER JOIN FISA ONFISA.IDCARTE=CARTE.IDCARTEWHERE @PIDCARTE=FISA.IDCARTE

5. Sa se afiseze toate cartile avand Id-ul numar par.

CREATE PROCEDURE [dbo].[ISBN_PAR]ASSELECT TITLUFROM CARTEWHERE IDCARTE%2=0GROUP BY TITLU

6.Sa se afiseze numarul cititorilor care au imprumutat carti.

CREATE PROCEDURE [dbo].[NUMAR_CITITORI]@PNUMECARTE AS NVARCHAR(200)

ASSELECT CARTE.IDCARTE,CARTE.TITLU,COUNT(CITITOR.IDCITITOR) AS

NRCITITORIFROM CARTE INNER JOIN FISA ON CARTE.IDCARTE=FISA.IDCARTE INNER

JOIN CITITOR ON FISA.IDCITITOR=CITITOR.IDCITITORWHERE TITLU=@PNUMECARTEGROUP BY CARTE.IDCARTE,TITLU

7. Sa se afiseze toate cartile avand Id-ul numar impar.

CREATE PROCEDURE [dbo].[ORDONEAZA_CARTI]ASSELECT IDCARTE, TITLU

FROM CARTEWHERE IDCARTE%2=1GROUP BY IDCARTE, TITLU

8.Sa se afiseze data maxima a restituirii cartilor.CREATE PROCEDURE [dbo].[RESTITUIRE_IMPRUMUT]

@PRESTITUIRE DATETIME OUTPUT AS SELECT @PRESTITUIRE=MAX(DATARESTITUIRE)FROM FISA

FUNCTII DE TIP TABELAR :

1.Sa se afiseze numarul cartilor scrise de fiecare autor.

CREATE FUNCTION [dbo].[NRCARTI](@PIDAUTOR AS NVARCHAR(50))RETURNS TABLEAS RETURN(SELECT COUNT(IDCARTE) AS NRCARTI, IDAUTORFROM CARTEAUTORWHERE IDAUTOR=@PIDAUTOR

GROUP BY IDAUTOR)

2. Sa se afiseze numarul cartilor aparute la fiecare editura.

CREATE FUNCTION [dbo].[NRCARTIEDITURA](@PIDEDITURA AS NVARCHAR(50))RETURNS TABLEASRETURN(SELECT COUNT(IDCARTE) AS NRCARTI, IDEDITURA FROM CARTEWHERE IDEDITURA=@PIDEDITURAGROUP BY IDEDITURA)

3.Sa se afiseze sub forma unui tabel fiecare carte,cititorul care a imprumutat-o si valoarea penalizarii cumulate.Cititorul este specificat ca parametru.

CREATE FUNCTION [dbo].[TOTALPENALIZARI] (@PIDCITITOR AS NVARCHAR(50))RETURNS @TABELTOTALPENALIZARI TABLE(IDCARTE NVARCHAR(50),IDCITITOR NVARCHAR(50), VALOARE MONEY) AS BEGIN INSERT @TABELTOTALPENALIZARI SELECT dbo.FISA.IDCARTE, IDCITITOR, VALOARE FROM FISA WHERE IDCITITOR=@PIDCITITORGROUP BY FISA.IDCARTE, IDCITITOR, VALOARERETURNEND

FUNCTII DE TIP SCALAR :

4.Sa se aplice penalizari astfel:daca valoarea cartii nu depaseste 12000 lei nu se aplica penalizare,daca valoarea cartii nu depaseste 21000 de lei,se aplica o penalizare de 7% si daca valoarea cartii depaseste suma de 21000 de lei, atunci se aplica o penalizare de 10%.

CREATE FUNCTION [dbo].[PENALIZARE](@PVALOARE AS MONEY)RETURNS MONEY

ASBEGIN

RETURN (CASE WHEN @PVALOARE<12000 THEN 0WHEN @PVALOARE<21000 THEN @PVALOARE*0.07

ELSE @PVALOARE*0.1

END) END

Sa se defineasca un buton de comanda asupra unui formular care sa permita adaugarea de noi date in tabela Carte , cu restrictia la limba sa fie romana sau germana si data sa fie exclusiv anul curent.