Che materia stai cercando?

Anteprima

ESTRATTO DOCUMENTO

Traduzione di relazioni

Relazioni uno a uno non sono presenti.

Di relazioni molti a molti sono presenti prenota e occupazione, esse vanno

entrambe tradotte in R-relazioni:

Occupazione (Albergo,Camera, Data_arrivo, Data_partenza,

 Box_associato, Cliente);

Prenotazione (Id, Cliente, Albergo, Camera, Box, Data_arrivo,

 Data_partenza);

Le relazioni uno a molti sono: associazione, composizione e possedere.

Gli ultimi due sono rilevabili delle chiavi esterne rispettivamente di

camera e box, la prima invece deve essere rappresentata dalla seguente

tabella:

Associazione(box, albergo, data_inizio, camera,data_fine)

Implementazione di alcune operazioni.

In questo paragrafo è proposta l’implementazione di alcune delle

operazioni che definiscono la catena di alberghi.

Script di istallazione

************************************************************

* script di istallazione

************************************************************

create database CatenaAlberghi

***

*Albergo

***

Si prevede che la catena comprende 100 alberghi, tale numero andrà a

modificarsi solo con l’acquisto di un altro albergo che accade di rado. Ciò

fa sì che il tablespace delle tabelle albergo, camere e box è quasi invariato

nel tempo.

CREATE TABLE "ALBERGO" ("ID" NUMBER(10) NOT NULL,

"NOME" VARCHAR2(20) NOT NULL,

"LOCALITA" VARCHAR2(20) NOT NULL,

"INDIRIZZO" VARCHAR2(30) NOT NULL,

"NOME_DIRETTORE" VARCHAR2(10) NOT NULL,

PRIMARY KEY("ID"))

TABLESPACE "USERS" PCTFREE 5 PCTUSED 60 INITRANS 2

STORAGE ( INITIAL 16384 NEXT 8192 PCTINCREASE 0)

***

*camera

***

La seguente create table presuppone che le camere sono memorizzate su

bd diversi in base all’albergo.

CREATE TABLE "CAMERA" ("NUMERO" NUMBER(10) NOT NULL,

"NUM_LETTI" NUMBER(10) NOT NULL, "TIPO" NVARCHAR2(15) NOT

NULL, "ALBERGO" NUMBER(10) NOT NULL,

CONSTRAINT "SYS_C003615" PRIMARY KEY("NUMERO", "ALBERGO")

USING INDEX

TABLESPACE "USERS"

STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255,

CONSTRAINT "SYS_C003616" FOREIGN KEY("ALBERGO")

REFERENCES "ALBERGO"("ID"))

TABLESPACE "USERS" PCTFREE 20 INITRANS 2 MAXTRANS 255

STORAGE ( INITIAL 80K NEXT 8K PCTINCREASE 0)

NOLOGGING

PARTITION BY RANGE ("ALBERGO") (PARTITION "CAMERA_P1"

VALUES LESS THAN (15)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING,

PARTITION "CAMERA_P2"

VALUES LESS THAN (30)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING,

PARTITION "CAMERA_P3"

VALUES LESS THAN (45)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING,

PARTITION "CAMERA_P4"

VALUES LESS THAN (60)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING,

PARTITION "CAMERA_P5"

VALUES LESS THAN (75)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING,

PARTITION "CAMERA_P6"

VALUES LESS THAN (90)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING,

PARTITION "CAMERA_P7"

VALUES LESS THAN (105)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING,

PARTITION "CAMERA_P8"

VALUES LESS THAN (120)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING,

PARTITION "CAMERA_P9"

VALUES LESS THAN (135)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING,

PARTITION "CAMERA_P10"

VALUES LESS THAN (150)

TABLESPACE "USERS" PCTFREE 20 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 80K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0) LOGGING)

***

*box

***

CREATE TABLE "BOX" ("ALBERGO" NUMBER(10) NOT NULL,

"NUMERO" NUMBER(10) NOT NULL, "UBICAZIONE" VARCHAR2(20 byte)

NOT NULL, "TIPO" NUMBER(1) NOT NULL, "DIMENSIONE" NUMBER(10)

NOT NULL,

CONSTRAINT "BOOL_BOX" CHECK(tipo=0 or tipo=1),

CONSTRAINT "FK_BOX_ALBERGO" FOREIGN KEY("ALBERGO")

REFERENCES "ALBERGO"("ID"),

CONSTRAINT "PK_BOX" PRIMARY KEY("ALBERGO", "NUMERO"))

TABLESPACE "USERS" PCTFREE 5 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 48K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING

***

*Cliente

***

Le tabelle create di seguito hanno una crescita considerevole nel tempo,

quindi i tablespace cresceranno notevolmente. Quello della tabella cliente

ha una crescita minore rispetto agli altri perché sono previsti clienti fedeli.

CREATE TABLE "CLIENTE" ("CI" VARCHAR2(10 byte) NOT

NULL, "NOME" VARCHAR2(20 byte) NOT NULL, "COGNOME"

VARCHAR2(20 byte) NOT NULL, "DATA_NASCITA" DATE NOT NULL,

"RESIDENZA" VARCHAR2(40 byte) NOT NULL,

CONSTRAINT "SYS_C003635" PRIMARY KEY("CI")

USING INDEX

TABLESPACE "USERS"

STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS

255) TABLESPACE "USERS" PCTFREE 10 PCTUSED 0 INITRANS 1

MAXTRANS 255

STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING

***

* Prenotazioni

***

Si è creato una sequenza per incrementare automaticamente il valore id

della tabella prenotazione.

CREATE SEQUENCE "CATENAALBEGHI"."CONT_PREN" INCREMENT BY 1 START

WITH 21 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE

CACHE 20 NOORDER

CREATE TABLE "PRENOTAZIONE" ("ID" NUMBER(10) NOT NULL,

"CLIENTE" VARCHAR2(10 byte) NOT NULL, "ALBERGO" NUMBER(10)

NOT NULL, "CAMERA" NUMBER(10) NOT NULL, "NUM_BOX" NUMBER(10)

NOT NULL, "DATA_ARRIVO" DATE NOT NULL, "DATA_PARTENZA" DATE

NOT NULL,

CONSTRAINT "CK_DATE_PRENOTAZIONI"

CHECK(data_arrivo<data_partenza),

CONSTRAINT "FK_PRENOTAZIONE_CAMERA" FOREIGN KEY("CAMERA",

"ALBERGO")

REFERENCES "CAMERA"("NUMERO", "ALBERGO"),

CONSTRAINT "FK_PRENOTAZIONE_CLIENTE" FOREIGN KEY("CLIENTE")

REFERENCES "CLIENTE"("CI"),

CONSTRAINT "PK_PRENOTAZIONE" PRIMARY KEY("ID")

USING INDEX

TABLESPACE "USERS"

STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)

TABLESPACE "USERS" PCTFREE 5 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 808K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING

***

*Occupazione

***

CREATE TABLE "OCCUPAZIONE" ("ALBERGO" NUMBER(10) NOT

NULL, "CAMERA" NUMBER(10) NOT NULL, "DATA_ARRIVO" DATE NOT

NULL, "DATA_PARTENZA" DATE NOT NULL, "BOX_ASSOCIATO"

NUMBER(10) NOT NULL, "CLIENTE" VARCHAR2(10 byte) NOT NULL,

CONSTRAINT "FK_CLIENTE" FOREIGN KEY("CLIENTE")

REFERENCES "CLIENTE"("CI"),

CONSTRAINT "FK_OCC_BOX" FOREIGN KEY("ALBERGO",

"BOX_ASSOCIATO")

REFERENCES "BOX"("ALBERGO", "NUMERO"),

CONSTRAINT "FK_OCC_CAMERA" FOREIGN KEY("CAMERA", "ALBERGO")

REFERENCES "CAMERA"("NUMERO", "ALBERGO"),

CONSTRAINT "PK_OCCUPAZIONE" PRIMARY KEY("ALBERGO", "CAMERA",

"DATA_ARRIVO")

USING INDEX

TABLESPACE "USERS"

STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)

TABLESPACE "USERS" PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 1400K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING

***

*Associazione

***

CREATE TABLE "ASSOCIAZIONE" ("BOX" NUMBER(10) NOT NULL,

"ALBERGO" NUMBER(10) NOT NULL, "CAMERA" NUMBER(10) NOT NULL,

"DATA_INIZIO" DATE NOT NULL, "DATA_FINE" DATE NOT NULL,

CONSTRAINT "FK_ASS_BOX" FOREIGN KEY("ALBERGO", "BOX")

REFERENCES "BOX"("ALBERGO", "NUMERO"),

CONSTRAINT "FK_ASS_CAMERA" FOREIGN KEY("CAMERA", "ALBERGO")

REFERENCES "CAMERA"("NUMERO", "ALBERGO"),

CONSTRAINT "PK_ASSOCIAZIONE" PRIMARY KEY("BOX", "ALBERGO",

"CAMERA", "DATA_INIZIO")

USING INDEX

TABLESPACE "USERS"

STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)

TABLESPACE "USERS" PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS

255

STORAGE ( INITIAL 48K NEXT 0K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0)

LOGGING

Operazioni O1

La seguente procedura controlla se l’ospite è un vecchio cliente, in caso

contrario memorizza i suoi dati nel db.

create or replace procedure ins_cliente

(n in cliente.nome %type, -- nome cliente

c in cliente.cognome %type, -- cognome cliente

carta in cliente.ci %type, -- numero carta di identità

nascita in cliente.data_nascita %type, -- data di nascita

indirizzo in cliente.residenza %type -- residenza del cliente

) is

begin

declare

presente number(5);

begin

-- controlla se il cliente è già memorizzato

select count(*) into presente from cliente where ci=carta;

if presente=0 then

-- inserisce i dati del cliente nel db

insert into cliente values(carta,n,c,nascita,indirizzo);

commit;

end if;

end;

end ins_cliente;

Operazioni O2

La cancellazione nella tabella cliente, avviene solo nel caso in cui un

nuovo cliente ha prenotato e poi disdetto una camera, quindi solo dopo la

cancellazione della prenotazione.

Tale operazione è fatta dal seguente trigger, che seleziona i clienti che non

hanno mai occupato una stanza e non hanno una prenotazione, li copia in

un cursore tramite il quale identifica i clienti da cancellare :

create or replace trigger del_cliente

after delete on prenotazione

for each row

declare

cli cliente.ci %type;

-- creazione del cursore

cursor cliente_as is

select c.ci from cliente c, occupazione o prenotazione p where

c.ci!=o.cliente and c.ci!=p.cliente;

begin

-- apertura del cursore

open cliente_as;

fetch cliente_as into cli;

while cliente_as %notfound

loop

-- cancellazione del cliente

delete from cliente where ci=cli;

fetch cliente_as into cli;

end loop;

--chiusura del cursore

close cliente_as;

commit;

end;

Operazioni O3

Di seguito sono rappresentate sia le procedure di controllo che la

procedura di inserimento.

Quando un cliente chiede di prenotare una camera, un responsabile

dell’albergo controlla se ci sono camere e box liberi per il periodo e del

tipo richiesto.

La camera è controllata dalla funzione disp() che utilizza una query

innestata che interroga le tabelle camera, albergo e prenotazione, il box è

controllato dalla funzione posti_liberi() che restituisce il numero di posti

liberi in un dato periodo, infine la procedura prenota() effettua la

prenotazione inserendo una tupla nella relativa tabella.

create or replace function disp

(nome_alb in albergo.nome %type, -- nome albergo

loc in albergo.localita %type, -- località albergo

tip in camera.tipo %type, -- tipo di camera richiesta

arrivo in prenotazione.data_arrivo %type, -- data arrivo

partenza in prenotazione.data_partenza %type) –data partenza

return varchar2

is

begin

declare

libero varchar2(100);

cam camera.numero %type;

alb camera.albergo %type;

cursor cur is

select c.numero, c.albergo

from camera c inner join albergo a on c.albergo=a.id

where a.nome=nome_alb and a.localita=loc and c.tipo=tip and

c.numero<> ALL(select camera

from prenotazione p

where p.data_arrivo<=partenza and

p.data_partenza>=arrivo);

begin

open cur;

loop

fetch cur into cam,alb; exit when cur %notfound;

libero:=libero||to_char(cam)||' '||to_char(alb);

end loop;

close cur;

--restituisce l’elenco delle camere libere

return libero;

end;

end disp;

create or replace function posti_liberi

(num in albergo.id %type, -- id albergo

arrivo in prenotazione.data_arrivo %type, -- data di arrivo

partenza in prenotazione.data_partenza %type --data partenza

)

return number

is

begin

declare

libero number(5);

tutti number(5);

pieni number(5);

begin

-- conta i posti presenti in un albergo

select count(*) into tutti from box b where b.albergo=num;

-- selezionna il numero dei posti occupati

select sum(p.num_box) into pieni from prenotazione p where

p.albergo=num and p.data_arrivo<=partenza and

p.data_partenza>=arrivo;

/* restituisce il numero dei posti liberi sottraendo i totali da

quelli occupato */

libero:=(tutti-pieni);

return libero;

end;

end posti_liberi;

create or replace procedure prenota

(

n in cliente.nome %type, --nome cliente


ACQUISTATO

1 volte

PAGINE

21

PESO

100.28 KB

AUTORE

Sara F

PUBBLICATO

+1 anno fa


DETTAGLI
Esame: Basi di Dati
Corso di laurea: Corso di laurea in ingegneria informatica
SSD:
A.A.: 2013-2014

I contenuti di questa pagina costituiscono rielaborazioni personali del Publisher Sara F di informazioni apprese con la frequenza delle lezioni di Basi di Dati e studio autonomo di eventuali libri di riferimento in preparazione dell'esame finale o della tesi. Non devono intendersi come materiale ufficiale dell'università Napoli Federico II - Unina o del prof Chianese Angelo.

Acquista con carta o conto PayPal

Scarica il file tutte le volte che vuoi

Paga con un conto PayPal per usufruire della garanzia Soddisfatto o rimborsato

Recensioni
Ti è piaciuto questo appunto? Valutalo!

Altri appunti di Basi di dati

Esercitazione Basi di dati
Esercitazione
Basi di Dati
Dispensa
Basi di Dati – Campionato di calcio
Appunto
Basi di Dati -SQL EMBEDDED
Dispensa