Aplicatie baze de date - Aplicatia prezinta gestionarea informatiilor referitoare la o familie formata din mai multi frati care detine o colectie impresionanta de DVD-uri cu filme

SPECIFICAREA PROBLEMEI

Aplicatia prezinta gestionarea informatiilor referitoare la o familie formata din mai multi frati care detine o colectie impresionanta de DVD-uri cu filme. Acestia s-au hotarat sa le imprumute intre ei sau persoanelor apropiate, cum ar fi:prieteni, cunostinte, rude.

Avem nevoie de o baza de date pentru a tine evidenta DVD-urilor pe care le detin, precum si a persoanelor care le-au imprumutat s-au care le vor imprumuta.

Un DVD se incadreaza intr-un anumit GEN care contine:

id_gen

categoria

DVD-ul se identifica prin:

id_dvd

regizor

titlul

an_aparitie

PERSOANA va fi identificata prin urmatoarele caracteristici:

id_persoana

nume

prenume

adresa

telefon

O persoana poate sa fie proprietarul mai multor DVD-uri si in acelasi timp sa imprumute DVD-uri de la alti proprietari. Fiecare proprietar are DVD-uri diferite fata de ceilalti proprietari si nu exista dubluri. De asemenea persoana poate fi doar client, adica doar sa imprumute DVD-uri nu sa le si detina. O persoana poate imprumuta DVD-uri de genuri diferite si un DVD poate fi imprumutat de mai multe persoane in timp.Un DVD poate apartine unui singur gen.Un DVD are acelasi titlu ca si titlul filmului.Un film intra pe un DVD.

Se cere:

sa se afiseze, regizorul, titlul, categoria din care face parte DVD;

sa se afiseze DVD-urile care apartin fiecarui proprietar, ordonate dupa titlu;

sa se afiseze care sunt persoanele care au imprumutat DVD-ul cu un anumit titlu si la ce data;

sa se afiseze pentru fiecare proprietar titlul filmului ce a fost imprumutat de cele mai putin si multe ori;

sa se afiseze id_dvd, titlul filmului, anul aparitiei, cu conditia ca anul de aparitie sa fie cuprins intre 1980 - 2003, ordonat dupa anul aparitiei;

sa se afiseze cate filme sunt in colectie regizate de: Steven Spielberg;

ANALIZA PROBLEMEI

2.1. MODELUL ENTITATE - ASOCIERE

Pentru a realiza baza de date astfel incat sa satisfaca cerintele familiei, vom avea 3 clase de entitati si 3 clase de asociere.

Cele 3 clase de entitati vor fi urmatoarele:

GEN cu 2 atribute:

id_gen care va fi cheie primara (PK) pentru entitatea GEN;

categoria care va fi numele genului;

PERSOANA cu 5 atribute:

id_persoana care va fi cheie primara (PK) pentru entitatea PERSOANA;

nume - numele persoanei;

prenume - prenumele persoanei;

adresa - adresa domiciliului persoanei;

telefon - reprezinta telefonul persoanei;

DVD cu 4 atribute:

id_dvd care va fi cheie primara (PK) pentru entitatea DVD;

regizor care va fi regizorul filmului de pe DVD;

titlul care va fi titlul filmului;

an_aparitie care va reprezenta anul de aparitie al filmului;

Cele 3 clase de asociere vor fi urmatoarele:

INCADRARE - intre GEN si DVD. Clasa de asociere incadrare nu are atribute proprii;

IMPRUMUTARE - intre DVD si PERSOANA. Clasa de asociere imprumutare are ca atribut: data( =data imprumutarii DVD-ului);

PROPRIETATE - intre DVD si PERSOANA. Clasa de asociere proprietate nu are atribute proprii;

2.2. STABILIREA CARDINALITATILOR

1. In cazul asocierii INCADRARE:

- Un gen poate sa contina mai multe DVD-uri => card.max = n.

- Un gen trebuie sa contina 0 DVD-uri => card.min = 0.

- Un DVD poate sa apartina unui singur gen => card.max = 1.

- Un DVD trebuie sa apartina unui gen => card.min = 1.

2. In cazul asocierii IMPRUMUTARE:

- Un DVD poate fi imprumutat la mai multe persoane => card.max = n.

- Un DVD trebuie imprumutat la 0 persoane => card.min = 0.

- O persoana poate sa imprumute mai multe DVD-uri => card.max = n.

- O persoana trebuie sa imprumute 0 DVD-uri => card.min = 0.

3. In cazul asocierii PROPRIETATE:

- Un DVD poate sa apartina la o singura persoana => card.max = 1.

- Un DVD trebuie sa apartina unei persoane => card.min = 1.

- O persoana poate sa detina mai multe DVD-uri => card.max = n.

- O persoana trebuie sa detina 0 DVD-uri => card.min = 0.

2.3. MODELUL RELATIONAL

Pentru fiecare clasa de entitati se construieste cate o tabela cu acelasi nume cu al clasei de entitati si aceeasi cheie primara. Deci se vor construi pentru inceput tabelele: GEN, DVD, PERSOANA cu atributele mentionate mai sus.

GEN - DVD:

Intre aceste 2 tabele clasa de asociere este INCADRARE si nu are atribute proprii.

De aici rezulta ca ne aflam in situatia A( = clasa de asociere nu are atribute proprii), cazul 2( = cel putin o entitate are cardinalitatea maxima 1).In acest caz nu se creeaza o tabela noua pentru relatia de asociere, dar migreaza in entitatea cu cardinalitate maxima 1 toate cheile primare (PK) din celelalte clase de entitati si acestea devin chei straine (FK). In cazul nostru clasa de entitati DVD are cardinalitatea maxima 1, deci primeste intre atributele sale, ca cheie straina, cheia primara id_gen din clasa de entitati GEN cu care se afla in relatie. Astfel vom avea tabelele:

GEN (id_gen (PK), categoria)

DVD (id_dvd (PK), regizor, titlul, an_aparitie, id_gen (FK))

DVD - PERSOANA:

Intre aceste 2 tabele exista 2 clase de asociere si anume:

a)     IMPRUMUTARE

b)     PROPRIETATE

a)     Pentru clasa de asociere IMPRUMUTARE care are atributul propriu data ne aflam in situatia B( = clasa de asociere are atribute proprii), cazul 1( = toate entitatile intra in relatia de asociere cu cardinalitatea maxima n). In acest caz se creeaza o noua tabela care va avea acelasi nume cu cel al clasei de asociere, iar atributele tabelei vor fi cele initiale + cheile primare ale tuturor entitatilor implicate in relatia de asociere care vor deveni in aceasta noua tabela chei straine. Astfel vom avea tabelele:

DVD (id_dvd (PK), regizor, titlul, an_aparitie,)

PERSOANA (id_persoana (PK), nume, prenume, adresa, telefon)

IMPRUMUTARE (id_dvd (FK), id_persoana (FK), data)

b)     Pentru clasa de asociere PROPRIETATE ne aflam in situatia A( = clasa de asociere nu are atribute proprii), cazul 2( = cel putin o entitate are cardinalitatea maxima 1). In acest caz clasa de entitati DVD are cardinalitatea maxima 1, deci primeste intre atributele sale ca cheie straina, cheia primara id_persoana din clasa de entitati PERSOANA cu care se afla in relatie. Astfel vom avea tabelele:

DVD (id_dvd (PK), regizor, titlul, an_aparitie, id_persoana (FK))

PERSOANA (id_persoana (PK), nume, prenume, adresa, telefon)

In final vom avea urmatoarele tabele ce reprezinta Modelul Relational:

GEN (id_gen (PK), categoria)

DVD (id_dvd (PK), regizor, titlul, an_aparitie, id_gen (FK), id_persoana (FK))

IMPRUMUTARE (id_dvd (FK), id_persoana (FK), data)

PERSOANA (id_persoana (PK), nume, prenume, adresa, telefon)

2.4. SPECIFICATII DE IMPLEMENTARE

Pentru realizarea bazei de date se vor crea urmatoarele 4 tabele:

Tabela GEN cu coloanele:

id_gen valoare numerica de cel mult 2 cifre, cu restrictia ca valoarea sa nu fie null;

categoria sir de caractere de cel mult 20 de cifre, cu restrictia ca valoarea sa nu fie null;

Se mai adauga restrictia de cheie primara pentru id_gen.

G E N

Tabela DVD cu coloanele:

id_dvd valoare numerica de cel mult 4 cifre, cu restrictia ca valoarea sa nu fie null;

regizor sir de caractere de cel mult 30 de caractere, cu restrictia ca valoarea sa nu fie null;

titlul sir de caractere de cel mult 40 de caractere, cu restrictia ca valoarea sa nu fie null;

an_aparitie valoare numerica de cel mult 4 cifre, cu restrictia ca valoarea sa nu fie null;

id_gen valoare numerica de cel mult 2 cifre, cu restrictia ca valoarea sa nu fie null;

id_persoana valoare numerica de cel mult 3 cifre, cu restrictia ca valoarea sa nu fie null;

Se adauga urmatoarele restrictii:

- cheia primara va fi id_dvd;

- cheie straina va fi id_gen care se refera la

id_gen din tabela GEN;

- cheie straina va fi id_persoana care se refera la

id_persoana din tabela PERSOANA;

D V D

Tabela IMPRUMUTARE cu coloanele:

id_dvd valoare numerica de cel mult 4 cifre, cu restrictia ca valoarea sa nu fie null;

id_persoana valoare numerica de cel mult 3 cifre, cu restrictia ca valoarea sa nu fie null;

data de tip Date, cu restrictia ca valoarea sa nu fie null;

Se mai adauga restrictiile de cheie straina pentru id_dvd si id_persoana.

IMPRUMUTARE

Tabela PERSOANA cu coloanele:

id_persoana valoare numerica de cel mult 3 cifre, cu restrictia ca valoarea sa nu fie null;

nume sir de caractere de cel mult 15 caractere, cu restrictia ca valoarea sa nu fie null;

prenume sir de caractere de cel mult 15 caractere, cu restrictia ca valoarea sa nu fie null;

adresa sir de caractere de cel mult 60 de caractere,

telefon valoare numerica de cel mult 15 cifre;

Se adauga restrictia de cheie primara pentru id_persoana;

PERSOANA

IMPLEMENTAREA PROBLEMEI

3.1. CREAREA TABELELOR

CREATE TABLE gen(

id_gen NUMBER(2) NOT NULL,

categoria VARCHAR2(20) NOT NULL,

CONSTRAINT gen_id_gen_pk PRIMARY KEY(id_gen));

CREATE TABLE persoana(

id_persoana NUMBER(3) NOT NULL,

nume VARCHAR2(15) NOT NULL,

prenume VARCHAR2(15) NOT NULL,

adresa VARCHAR2(40),

telefon NUMBER(15),

CONSTRAINT persoana_id_persoana_pk PRIMARY KEY(id_persoana));

CREATE TABLE dvd(

id_dvd NUMBER(4) NOT NULL,

regizor VARCHAR2(30) NOT NULL,

titlul VARCHAR2(40) NOT NULL,

an_aparitie NUMBER(4) NOT NULL,

id_gen NUMBER(2) NOT NULL,

id_persoana NUMBER(3) NOT NULL,

CONSTRAINT dvd_id_dvd_pk PRIMARY KEY(id_dvd),

CONSTRAINT dvd_id_gen_fk FOREIGN KEY(id_gen)

REFERENCES gen(id_gen),

CONSTRAINT dvd_id_persoana_fk FOREIGN KEY(id_persoana)

REFERENCES persoana(id_persoana));

CREATE TABLE imprumutare(

id_dvd NUMBER(4) NOT NULL,

id_persoana NUMBER(3) NOT NULL,

data DATE NOT NULL,

CONSTRAINT imprumutare_id_dvd_fk FOREIGN KEY(id_dvd)

REFERENCES dvd(id_dvd),

CONSTRAINT imprumutare_id_persoana_fk FOREIGN KEY(id_persoana)

REFERENCES persoana(id_persoana));

3.2. INSERAREA DATELOR IN TABELE

Inserarea datelor in tabela GEN:

INSERT INTO gen

VALUES (1,'SF');

INSERT INTO gen

VALUES (2,'ACTIUNE');

INSERT INTO gen

VALUES (3,'COMEDIE');

INSERT INTO gen

VALUES (4,'THRILLER');

INSERT INTO gen

VALUES (5,'DRAMA');

INSERT INTO gen

VALUES (6,'HORROR');

INSERT INTO gen

VALUES (7,'ANIMATIE');

INSERT INTO gen

VALUES (8,'AVENTURA');

INSERT INTO gen

VALUES (9,'FANTASTIC');

INSERT INTO gen

VALUES (10,'ROMANTIC');

Inserarea datelor in tabela PERSOANA:

INSERT INTO persoana

VALUES (1,'Ardelean','Cosmin','str.Predeal nr.13 loc.Tg.Mures',214576);

INSERT INTO persoana

VALUES (2,'Manea','Alexandru','str.Aleea Carpati nr.31 loc.Tg.Mures',217659);

INSERT INTO persoana

VALUES (3,'Bleoca','Silviu','str.Cornisa nr.7 loc.Tg.Mures',215872);

INSERT INTO persoana

VALUES (4,'Bleoca','Tiberiu','str.Cornisa nr.7 loc.Tg.Mures',215872);

INSERT INTO persoana

VALUES (5,'Bleoca','Sorana','str.Cornisa nr.7 loc.Tg.Mures',215872);

INSERT INTO persoana

VALUES (405,'Vancu','Diana','str.Sarguintei nr.25 loc.Tg.Mures',254091);

INSERT INTO persoana

VALUES (11,'Rusu','Serban','str.Stramba nr.12 loc.Brasov',NULL);

INSERT INTO persoana

VALUES (55,'Libeg','Claudiu','str.Stelelor nr.37 loc.Cluj-Napoca',340786);

INSERT INTO persoana

VALUES (120,'Coita','Mihai','str.Bordeni nr.56 loc.Ploiesti',NULL);

Inserarea datelor in tabela DVD:

INSERT INTO dvd

VALUES (1,'Steven Spielberg','Jurassic Park',1993,8,4);

INSERT INTO dvd

VALUES (2,'Peter Jackson','The Lord Of The Rings',2003,9,3);

INSERT INTO dvd

VALUES (3,'Walt Becker','Van Wilder',2002,2,5);

INSERT INTO dvd

VALUES (4,'Clint Eastwood','Absolute Power',1997,4,3);

INSERT INTO dvd

VALUES (5,'Woody Allen','Annie Hall',1977,3,5);

INSERT INTO dvd

VALUES (6,'Steven Spielberg','E.T',1982,1,4);

INSERT INTO dvd

VALUES (7,'Steven Spielberg','A.I.',2002,1,3);

INSERT INTO dvd

VALUES (1024,'Tim Burton','Edward Scissorhands

INSERT INTO dvd

VALUES (356,'Don Bluth','Anastasia',1997,7,4);

INSERT INTO dvd

VALUES (43,'Peter Chelsom','Serendipity',2001,10,3);

Inserarea datelor in tabela IMPRUMUTARE:

INSERT INTO imprumutare

VALUES (1,405,TO_DATE('APR 20,1999','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (2,2,TO_DATE('IUN 10,1993','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (3,4,TO_DATE('SEP 18,2000','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (43,5,TO_DATE('DEC 25,2001','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (356,11,TO_DATE('MAI 27,1994','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (1024,120,TO_DATE('IAN 03,2002','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (7,405,TO_DATE('FEB 12,2001','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (7,4,TO_DATE('FEB 15,2002','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (1,5,TO_DATE('IUL 25,1997','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (2,5,TO_DATE('AUG 12,2003','MON DD,YYYY'));

INSERT INTO imprumutare

VALUES (5,3,TO_DATE('OCT 19,2003','MON DD,YYYY'));

3.3. INTEROGARI

1. Sa se afiseze regizorul, titlul si categoria din care face parte DVD-ul.

SELECT d.regizor, d.titlul, g.categoria

FROM dvd d, gen g

WHERE d.id_gen=g.id_gen;

2. Sa se afiseze DVD-urile care apartin fiecarui proprietar, ordonate dupa titlu.

SELECT d.titlul, p.nume ||' '|| p.prenume persoana

FROM dvd d, persoana p

WHERE d.id_persoana=p.id_persoana

ORDER BY d.titlul;

3. Sa se afiseze care sunt persoanele care au imprumutat DVD-ul cu un anumit titlu, si la ce data.

SELECT p.nume, p.prenume, i.data, i.id_persoana

FROM persoana p, imprumutare i, dvd d

WHERE p.id_persoana=i.id_persoana

AND i.id_dvd=d.id_dvd

AND d.titlul=( SELECT titlul

FROM dvd

WHERE titlul='&titlul');

4. Sa se afiseze pentru fiecare proprietar titlul filmului ce a fost imprumutat de cele mai putine si multe ori.

SELECT p.nume, p.prenume, min(d.titlul) Minim, max(d.titlul) Maxim

FROM persoana p, dvd d

WHERE p.id_persoana=d.id_persoana

GROUP BY p.nume, p.prenume;

5. Sa se afiseze id_dvd, titlul filmului, anul aparitiei, cu conditia ca anul aparitiei sa fie cuprins intre 1980 - 2003, ordonat dupa anul aparitiei.

SELECT id_dvd, titlul, an_aparitie

FROM dvd

WHERE an_aparitie BETWEEN 1980 AND 2003

ORDER BY an_aparitie;

6. Sa se afiseze cate filme sunt in colectie regizate de Steven Spielberg.

SELECT regizor, count(titlul)

FROM dvd

WHERE regizor='Steven Spielberg'

GROUP BY regizor;