Pag |1

Baze de date multimedia - aplicaie PostgreSQL (turism)

2012

Baza de date multimedia Turism cazareDescrierea problemei propuse (a scenariului aplicaiei)Aplicaia i propune s modeleze operaiile care au loc pe un site turistic, referitoare la cutarea unui loc de cazare. Aplicaia va fi realizat cu ajutorul softului PostgreSQL v8.4.2-1.

II

Pag |2

Tipuri de obiecte multimedia relevante domeniului ales:

Obiecte tip text. n acest tip de obiecte se vor gsi informaii ca: descrierea locului de cazare (numr camere, tip mas, atracii turistice n mprejurimi, preuri, etc.) ct i comentarii ale celor care deja au fost cazai n locurile respective. Obiecte tip imagine. n acest tip de obiecte se vor gsi imagini ale hotelurilor, camerelor, facilitilor oferite de hotel parcare, sal de conferine, piscin, saun, baie turceasc, sal fitness, loc de joac pentru copii - , imagini ale mncrurilor, atracii turistice din mprejurimi, peisaje, restaurant, recepie etc. Obiecte tip multimedia. n acest tip de obiecte vom gsi filmulee care ne prezint hotelul, activiti reprezentative pentru hotelul respectiv (exemplu: activiti de la clubul pentru copii, discotec, etc.), ct i interviuri luate unor persoane importante din conducere i vizitatorilor. Obiecte tip audio. n acest tip de obiecte ni se prezint locurile de cazare.

Baza de date va conine cel puin trei obiecte multimedia simple (imagine, video i sunet) i un obiect multimedia compus (text cu imagini ncorporate) - descriere mpreun cu metadatele ce le caracterizeaz.

Pag |3

III

Modelarea conceptual a bazei de date

O form a viitoarei scheme corespunztoare bazei de date, va conine mulimile entitate Cazare, Contact, Camere, Descriere, Carte_oaspei, Ofert, Clieni, Audio, Video, Imagine. ntre aceste mulimi entitate exist diverse asocieri. Se vor preciza de asemenea conectivitile acestor legturi:

TUn loc de cazare este Contactat_prin Contact (1, 1) TUn loc de cazare este Prezentate_n Descriere (1,1) -> ob compus TUn loc de cazare Are Ofert (1,m) CClien ii Beneficiaz_de Ofert (m,1) CUn loc de cazare este Comentate_n Carte_oaspe i (1,1) -> ob text TUn loc de cazare este Dotat_cu Camere (1,1) TUn loc de cazare este Prezentat prin Imagine, Audio, Video (1,m) ob imagine, audio, video.

IIII

Implementarea bazei de date multimedia

Pasul urmtor const n transformarea diagramei entitatelegtur prezentate anterior, n schema bazei de date relaionale. Schema relaional a bazei de date ce conine informaii despre turism - cazare, include urmtoarele relaii :

Pag |4

TCazare (id_cazare, nume, tip, tara, nr_stele, faciliti, domeniu de interes) ;

- Domeniul de interes se refer la: business hotel, spa, restaurants, romance, all inclusive, mini club, family, garden, ski - Tipul se refer la: Hotel, Pensiune, Vil sau Caban. - Facilit i se refer la: parcare, magazine, piscin interioar, piscin exterioar, saun, baie turceasc, sal fitness, loc de joac pentru copii, sal conferine, schimb valutar, medical service, taxi, transport aeroport, discotec, loc depozitare bagaje, etc.

CContact (email, ora, adres, cod potal, nr_tel, web, adres gps, cont bancar); ECamere (id_camere, nr_camere, faciliti);

Nr_camere se refer la numrul camerelor dintr-un anumit hotel, iar Facilit i reprezint caracteristicile comune ale camerelor: balcon, aer condiionat, seif, tv, lan, minibar)

MDescriere (id_descriere, descriere oid, nr_paragrafe, nr_cuv, nr_pag, format, nr_imag); CCarte_oaspe i (id_carte, carte_oaspei oid,

nr_paragrafe, nr_cuv, nr_pag, format, nr_impresii);

IOfert (id_ofert, denumire, data_start, data_end, nr_nopi, statut, tip_camer, tip_mas, pre);

Data_start i Data_end reprezint data la care ncepe, respectiv ia sfrit oferta, statut se refer la faptul de a fi nc valabil sau este expirat, Tip_camer poate fi: DBL, SGL, LEVEL, DELUXE, Tip_mas poate fi: DP, AI, SAI.

Pag |5

CClien i (id_client, Cnp, sex, nume, prenume, adres, telefon, email, data);

Data se refer la data la care ncepe cazarea.

RAudio (id_audio, audio oid, data_add, size, durata, format); VVideo (id_video, video oid, data_add, size, durata, format); Imagine (id_imag, data_add, imagine oid, format,

rezolutie, size, referitor_la). Referitor_la se refer la coninutul pozei: camere, restaurant, recepie. n continuare va fi prezentat codul PostgreSQL corespunztor fiecrei scheme n parte, mpreun cu print screen-uri ale acestora dup ce au fost introduse o serie de date de intrare. CCrearea bazei de date Cazare faciliti, mprejurimi, hotel n ansamblu,

CREATE DATABASE "Cazare" WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' CONNECTION LIMIT = -1;

Pag |6

CCrearea mulimii entitate Cazare:

CREATE TYPE t_i_p AS ENUM ('Hotel', 'Pensiune', 'Vila','Cabana'); CREATE TYPE domen AS ENUM ('business', 'spa', 'restaurants','romance','all_inclusive','miniclub','garden','family','sky'); CREATE TABLE "Cazare" ( id_cazare serial NOT NULL, tip t_i_p NOT NULL, -- 'Hotel', 'Pensiune', 'Vila','Cabana' domeniu domen NOT NULL, -- 'business', 'spa', 'restaurants','romance','all_inclusive','miniclub','garden','family','sky' tara character(15) NOT NULL, nume character(25) NOT NULL, nr_stele integer NOT NULL, facilitati character(300) NOT NULL, CONSTRAINT "Cazare_pkey" PRIMARY KEY (id_cazare) ) WITH ( OIDS=FALSE ); ALTER TABLE "Cazare" OWNER TO postgres; COMMENT ON COLUMN "Cazare".tip IS '''Hotel'', ''Pensiune'', ''Vila'',''Cabana''';

Pag |7COMMENT ON COLUMN "Cazare".domeniu IS '''business'', ''spa'', ''restaurants'',''romance'',''all_inclusive'',''miniclub'',''garden'',''family'',''sky''';

(

CCrearea mulimii entitate Contact

CREATE TABLE "Contact" oras character(20) NOT NULL, cod_postal integer NOT NULL, adresa character(30) NOT NULL, nr_tel bigint NOT NULL, email character(50) NOT NULL, web character(40) NOT NULL, gps character(35) NOT NULL, cont character(34) NOT NULL, id_cazare serial NOT NULL, CONSTRAINT "Contact_pkey" PRIMARY KEY (email), CONSTRAINT cazare FOREIGN KEY (id_cazare) REFERENCES "Cazare" (id_cazare) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE

Pag |8); ALTER TABLE "Contact" OWNER TO postgres;

(

CCrearea mulimii entitate Camere

CREATE TABLE "Camere" id_camere serial NOT NULL, facilitati character(100) NOT NULL, nr_camere integer NOT NULL, id_cazare serial NOT NULL, CONSTRAINT id_camere PRIMARY KEY (id_camere), CONSTRAINT cazare FOREIGN KEY (id_cazare) REFERENCES "Cazare" (id_cazare) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); ALTER TABLE "Camere" OWNER TO postgres;

Pag |9

(

CCreare mulime entitate Descriere

CREATE TABLE "Descriere" id_descriere serial NOT NULL, format character(5) NOT NULL, nr_pag integer NOT NULL, nr_cuv integer NOT NULL, nr_paragrafe integer NOT NULL, id_cazare serial NOT NULL, nr_imag integer NOT NULL, descriere oid, CONSTRAINT id_descriere PRIMARY KEY (id_descriere), CONSTRAINT cazare FOREIGN KEY (id_cazare) REFERENCES "Cazare" (id_cazare) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); ALTER TABLE "Descriere" OWNER TO postgres;

P a g | 10

Inserarea

propiu-zis

a

documentului

text

n

cmpul

descriere de tip oid, s-a facut la urm, dup introducerea tuturor metadatelor caracteristice imaginilor, cu o comand de tipul celei de mai jos, pentru fiecare intrare a tabelului:UPDATE "Descriere" SET descriere=lo_import('C:\\temp\\1 (2).docx') WHERE id_descriere=1;

(

CCreare mulime entitate Carte_oaspe i

CREATE TABLE carte_oaspeti id_carte serial NOT NULL, format character(5) NOT NULL, nr_pag integer NOT NULL, nr_cuv integer NOT NULL, nr_paragrafe integer NOT NULL, id_cazare serial NOT NULL, nr_impresii integer NOT NULL, carte_oaspeti oid, CONSTRAINT id_carte PRIMARY KEY (id_carte),

P a g | 11CONSTRAINT id_cazare FOREIGN KEY (id_cazare) REFERENCES "Cazare" (id_cazare) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); ALTER TABLE carte_oaspeti OWNER TO postgres;

Inserarea

propiu-zis

a

documentului

text

n

cmpul

carte_oaspe i de tip oid, s-a facut la urm, dup introducerea tuturor metadatelor caracteristice imaginilor, cu o comand de tipul celei de mai jos, pentru fiecare intrare a tabelului:UPDATE "carte_oaspeti" SET carte_oaspeti=lo_import('C:\\temp\\2.docx') WHERE id_carte=2;

CCrearea mulimii entitate Ofert

CREATE TYPE masa AS ENUM ('DP', 'AI', 'SAI'); CREATE TYPE tip AS ENUM ('DBL', 'SGL', 'LEVEL', 'DELUXE'); CREATE TABLE "Oferta"

P a g | 12( id_oferta serial NOT NULL, id_cazare serial NOT NULL, denumire character(20) NOT NULL, statut_oferta statut NOT NULL, nr_nopti integer NOT NULL, data_start date NOT NULL, data_end date NOT NULL, tip_camera tip NOT NULL, tip_masa masa NOT NULL, pret integer NOT NULL, CONSTRAINT id_oferta PRIMARY KEY (id_oferta), CONSTRAINT cazare FOREIGN KEY (id_cazare) REFERENCES "Cazare" (id_cazare) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); ALTER TABLE "Oferta" OWNER TO postgres;

(

CCreare mulime entitate Clien i

CREATE TABLE "Clienti"

P a g | 13nume character(15) NOT NULL, prenume character(15) NOT NULL, id_client serial NOT NULL, sex character(1) NOT NULL, data_sosirii date NOT NULL, adresa character(30) NOT NULL, tel integer NOT NULL, email character(30) NOT NULL, id_oferta serial NOT NULL, cnp character(13) NOT NULL, CONSTRAINT id_client PRIMARY KEY (id_client), CONSTRAINT oferta FOREIGN KEY (id_oferta) REFERENCES "Oferta" (id_oferta) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); ALTER TABLE "Clienti" OWNER TO postgres;

(

CCreare mulime entitate Audio

CREATE TABLE audio

P a g | 14id_audio serial NOT NULL, id_cazare serial NOT NULL, data_add date NOT NULL, durata integer NOT NULL, format character(5) NOT NULL, audio oid, size real NOT NULL, CONSTRAINT id_audio PRIMARY KEY (id_audio), CONSTRAINT cazare FOREIGN KEY (id_cazare) REFERENCES "Cazare" (id_cazare) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); ALTER TABLE audio OWNER TO postgres;

Inserarea propiu-zis a fiierului audio n cmpul audio de tip oid, s-a facut la urm, dup introducerea tuturor metadatelor caracteristice imaginilor, cu o comand de tipul celei de mai jos, pentru fiecare intrare a tabelului:UPDATE "audio" SET audio=lo_import('C:\\temp\\1.amr') WHERE id_audio=1;

P a g | 15

CCreare mulime entitate Video

CREATE TABLE "Video" ( id_video serial NOT NULL, id_cazare serial NOT NULL, data_add date NOT NULL, durata integer NOT NULL, format character(5) NOT NULL, video oid, size real NOT NULL, CONSTRAINT id_video PRIMARY KEY (id_video), CONSTRAINT cazare FOREIGN KEY (id_cazare) REFERENCES "Cazare" (id_cazare) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE );

P a g | 16ALTER TABLE "Video" OWNER TO postgres;

Inserarea propiu-zis a clipului video n cmpul video de tip oid, s-a facut la urm, dup introducerea tuturor metadatelor caracteristice imaginilor, cu o comand de tipul celei de mai jos, pentru fiecare intrare a tabelului:UPDATE "Video" SET video=lo_import('C:\\temp\\1.flv') WHERE id_video=1;

(

CCreare mulime entitate Imagine

CREATE TABLE "Imagine" id_imag serial NOT NULL, data_add date NOT NULL, id_cazare serial NOT NULL, size real NOT NULL, format character(5) NOT NULL, img oid, referitor_la character(15) NOT NULL, rezolutie integer[] NOT NULL, CONSTRAINT id_imag PRIMARY KEY (id_imag), CONSTRAINT cazare FOREIGN KEY (id_cazare)

P a g | 17REFERENCES "Cazare" (id_cazare) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); ALTER TABLE "Imagine" OWNER TO postgres;

Inserarea propiu-zis a imaginii n cmpul img de tip oid, s-a facut la urm, dup introducerea tuturor metadatelor caracteristice imaginilor, cu o comand de tipul celei de mai jos, pentru fiecare intrare a tabelului:UPDATE "Imagine" SET img=lo_import('C:\\temp\\melia1.jpg') WHERE id_imag=1;

P a g | 18

IVI

Implementarea interogarilor

n ncheiere se vor implementa un set de operaii asupra datelor menite s exemplifice urmtoarele tipuri de interogri: text, imagine, audio i video. Pentru fiecare interogare n parte, se va afia rspunsul primit :1)

S se afieze numele i id_cazare pentru toate locurile

de cazare care au un numr de camere mai mare sau egal dect 50.

P a g | 19

Observaie: Informaiile cu privire la nume se regsesc n tabela Cazare, iar cele cu privire la numr de camere n tabela Camere. Comanda PostgreSQL:SELECT "Cazare".nume, "Camere".id_cazare, "Camere".nr_camere from "Cazare", "Camere" WHERE "Camere".nr_camere>=50 AND "Cazare".id_cazare="Camere".id_cazare ORDER BY "Cazare".id_cazare;

2)

S se exporte n folderul "C:\temp", sub denumirea de

"poza" imaginea cu id-ul 10 i s se afieze numele i id-ul hotelului cruia i corespunde imaginea ct i coninutul acesteia (la ce se refer).Comanda PostgreSQL: SELECT "Cazare".nume, "Cazare".id_cazare, "Imagine".referitor_la, lo_export (img,'C:\\temp\\poza.jpg')

P a g | 20FROM "Cazare", "Imagine" WHERE "Imagine".id_imag=10 and "Imagine".id_cazare="Cazare".id_cazare ;

P a g | 21 3)

Exportai

n

folderul

"C:\temp",

sub

denumirea

"clip_video", clipul video realizat la data de 13 Iunie 2010 - i s se afieze id i numele hotelului despre care s-a realizat clipul video, formatul i data la care s-a adaugat acesta. Comanda PostgreSQL:SELECT "Cazare".nume, "Cazare".id_cazare, "Video".format, "Video".data_add, lo_export (video,'C:\\temp\\clip_video.flv') FROM "Cazare", "Video" WHERE "Video".data_add='2010-06-13' and "Video".id_cazare="Cazare".id_cazare

P a g | 22

4)

S se exporte n folderul "C:\temp", sub denumirea de Fiierul audio cu o durat mai mic de 20 de

"Audio",

secunde, i s se afieze id-ul i numele hotelului pe care l prezint ct i formatul i durata acestuia. Comanda PostgreSQL:SELECT "audio".durata, "audio".format FROM "audio" WHERE "audio".durata