baze de date - lant hotelier

LANT HOTELIER

Fie urmatoarea specificatie pentru  o baza de date aplicabila unui lant de hotelier:

Hotelurile sunt identificate prin : Hotel_ID, Nume, Oras , Categorie, Website,

Nr_Telefon

Managerii sunt identificati prin CNP, Nume, Nr_Telefon. Fiecare Hotel este condus de catre un singur manager, mai multe hoteluri putand fi conduse de acelasi manager.

Oferta este caracterizata de : Oferta_ID, Hotel_ID, An, Luna_in, Luna_sf. Un Hotel poate sa aiba una sau mai multe oferte intr-un an.

Agentia de turism este identificata prin : NumeAg, Oras, Tara si Nr_Telefon . Orice agentie poate sa promoveze 0 sai mai multe hoteluri din lant.

Angajatii sunt caracterizati de : CNPA, NumeA, Sarcina, Salariu, Hotel_ID . Managerii pot sa angajeze si sa concedieze angajati, si se retine data corespunzatoare angajarii sau concedierii.

Clientul e identificat prin : CNP, Hotel_ID, Camera, Data_cazarii,Data_elib. Un apartament e identificat prin Camera(numarul), Hotel_ID, Etaj, Tarif, nr_Paturi, Tip_Ap. Fiecare client rezerva unul sau mai multe apartamente la hotelul la care s-a cazat.

Un hotel poate sa aiba unul sau mai multe restaurante cu specific , identificate prin Hotel_ID,Specific si Nr_Mese

/* Lant hotelier */

drop table RezervaA

drop table RezervaM

drop table are

drop table Lanseaza

drop table angajeaza

drop table concediaza

drop table promoveaza

drop table Apeleaza

drop table oferte

drop table ag_turism

drop table restaurant

drop table apartament

drop table angajat

drop table client

drop table manager

drop table Hotel

/* Crearea tabelelor, cu specificatiile corespunzatoare */

/* Entitati */

Create Table Hotel (

Hotel_ID INT NOT NULL,

Nume VARCHAR(32),

Website VARCHAR(32),

Oras VARCHAR(64),

Categorie INT NOT NULL,

Nr_Telefon char(13)

CONSTRAINT PK_Hotel PRIMARY KEY /*cheia primara*/

(

Hotel_ID

)

GO

Create Table Oferte (

Hotel_ID INT NOT NULL,

An INT NOT NULL,

Oferta_ID INT NOT NULL,

Luna_in INT,

Luna_sf INT,

CONSTRAINT PK_Oferte PRIMARY KEY /*cheia primara*/

(

Oferta_ID

)

GO

Create Table Ag_Turism(

NumeAg Varchar(64),

Tara Varchar(32),

Oras Varchar(64),

Nr_telefon Char(10),

CONSTRAINT PK_Ag_Turism PRIMARY KEY /*cheia primara*/

(

Oras,

NumeAg

)

GO

Create Table Manager(

NumeM Varchar(64),

CNPM Varchar(32),

Nr_telefon Char(10),

CONSTRAINT PK_Manager PRIMARY KEY /*cheia primara*/

(

CNPM

)

GO

Create Table Angajat(

Hotel_ID INT Not null,

NumeA Varchar(64),

CNPA Varchar(32),

Sarcina Char(20),

Salariu Int,

CONSTRAINT PK_Angajat PRIMARY KEY /*cheia primara*/

(

CNPA

)

GO

Create Table Client(

CNP Varchar(32),

Hotel_ID Int not null,

Camera INT not null,

Data_cazarii DATE,

Data_elib DATE,

CONSTRAINT PK_Client PRIMARY KEY /*cheia primara*/

(

CNP

)

GO

Create Table Restaurant(

Hotel_ID Int not null,

Specific Varchar(32),

Nr_Mese INT not null,

CONSTRAINT PK_Restaurant PRIMARY KEY /*cheia primara*/

(

Hotel_ID,

Specific

)

GO

Create Table Apartament(

Hotel_ID INT not null,

Camera INT Not null, /* Numarul camerei */

Etaj INT not null,

Tip Varchar(32),

Nr_Paturi INT,

Tarif INT not null,              

CONSTRAINT PK_Apartament PRIMARY KEY /*cheia primara*/

(

Hotel_ID,

Camera

)

GO

/*Relatii*/

/* Hotelurile lanseaza oferte*/

CREATE TABLE Lanseaza (

Hotel_ID INT not null,

Categorie INT not null,

Oferta_ID INT NOT NULL,

An INT not null,

PRIMARY KEY (Hotel_ID,Oferta_ID), /*cheia primara*/

FOREIGN KEY (Hotel_ID) REFERENCES Hotel, /*cheia straina */

FOREIGN KEY (Oferta_ID) REFERENCES Oferte) /*cheia straina */

GO

/*Hotelurile au cate un manager */

CREATE TABLE Are (

Hotel_ID INT not null,

CNPM Varchar(32),

NumeM varchar(64),

PRIMARY KEY (Hotel_ID,CNPM), /*cheia primara*/

FOREIGN KEY (Hotel_ID) REFERENCES Hotel, /*cheia straina */

FOREIGN KEY (CNPM) REFERENCES Manager) /*cheia straina */

GO

/*Agentiile de turism promoveaza hoteluri */

CREATE TABLE Promoveaza (

NumeAg Varchar(64),

Oras Varchar(64),

Hotel_ID INT NOT NULL,

Nume VARCHAR(32),

PRIMARY KEY (Hotel_ID,NumeAg,Oras), /*cheia primara*/

FOREIGN KEY (Hotel_ID) REFERENCES Hotel) /*cheia straina */

GO

/*Managerii Angajeaza personal*/

CREATE TABLE Angajeaza (

CNPM varchar(32),

CNPA varchar(32),

Sarcina Char(20),

Salariu Int,

Data DATE,

PRIMARY KEY (CNPM,CNPA), /*cheia primara*/

FOREIGN KEY (CNPM) REFERENCES Manager, /*cheia straina */

FOREIGN KEY (CNPA) REFERENCES Angajat) /*cheia straina */

GO

/* Managerii concediaza angajati */

CREATE TABLE Concediaza (

CNPM varchar(32),

CNPA varchar(32),

Data DATE,

PRIMARY KEY (CNPM,CNPA), /*cheia primara*/

FOREIGN KEY (CNPM) REFERENCES Manager, /*cheia straina */

FOREIGN KEY (CNPA) REFERENCES Angajat) /*cheia straina */

GO

/* Clientul apeleaza la angajat */

CREATE TABLE Apeleaza (

CNP varchar(32),

ANALIZA: Filtre numerice iir - lucrarea de laborator ANALIZA: Recursivitate - proiect de tehnologie didactica

CNPA varchar(32),

Tip_Apel varchar(32),

Data Datetime,

PRIMARY KEY (CNPA,Data)) /*cheia primara*/

GO

/* Clientul rezerva masa la restaurantul cu un anumit specific */

CREATE TABLE RezervaM (

CNP varchar(32),

Hotel_ID int not null,

Specific varchar(32),

Data Date,

PRIMARY KEY (CNP,Hotel_ID,Specific), /*cheia primara*/

FOREIGN KEY (CNP) REFERENCES Client, /*cheia straina */

FOREIGN KEY (Hotel_ID,Specific) REFERENCES Restaurant) /*cheia straina */

GO

/* Clientul rezerva o camera la hotel */

CREATE TABLE RezervaA(

CNP varchar(32),

Hotel_ID INT Not Null,

Data_cazarii DATE,

Camera INT Not null,

PRIMARY KEY (CNP,Hotel_ID,camera,Data_cazarii), /*cheia primara*/

FOREIGN KEY (CNP) REFERENCES Client, /*cheia straina */

FOREIGN KEY (Hotel_ID,camera) REFERENCES Apartament) /*cheia straina */

GO

/* Popularea entitatilor */

delete from oferte

delete from ag_turism

delete from restaurant

delete from apartament

delete from angajat

delete from client

delete from manager

delete from Hotel

insert into Hotel values(1,'Majestic','www.majestic.com','Cluj',5,'0729823612')

insert into Hotel values(2,'Intercontinental','www.intercont.com','Constanta',4,'0726378477')

insert into Hotel values(3,'RexPark','www.rexPark.com','Dej',3,'0728432345')

insert into Hotel values(4,'Athos','www.athos.com','Bucuresti',5,'0789321342')

insert into Hotel values(5,'Confort','www.confort.com','Timisoara',4,'0245210600')

insert into Hotel values(6,'Opera','www.opera.com','Bucuresti',4,'0744790073')

insert into Hotel values(7,'Athene Palace','www.athenePalace.com','Iasi',5,'0261712333')

insert into Hotel values(8,'Carol Park','www.CarolPark.com','Suceava',4,'0214347899')

insert into Hotel values(9,'Tulip Inn','www.tulipInn.com','Craiova',2,'0214212437')

Select *

From Hotel

insert into ag_turism values('Alfatour','Romania','Brasov','0732435433')

insert into ag_turism values('Betatour','Bulgaria','Sofia','0765435432')

insert into ag_turism values('Gamatour','Ungaria','Budapesta','0743234433')

insert into ag_turism values('Infinity Travel','Romania','Craiova','0745434421')

insert into ag_turism values('Giulia Tour','Romania','Cluj','0653231921')

insert into ag_turism values('Ellada Travel','Grecia','Atena','0731435331')

Select *

From ag_turism

insert into restaurant values(1,'Italienesc',34)

insert into restaurant values(3,'Chinezesc',15)

insert into restaurant values(3,'Grecesc',26)

insert into restaurant values(4,'Frantuzesc',41)

insert into restaurant values(5,'Thailandez',33)

insert into restaurant values(5,'Portughez',19)

insert into restaurant values(5,'Maghiar',22)

insert into restaurant values(6,'Ardelenesc',31)

insert into restaurant values(6,'Grecesc',37)

insert into restaurant values(7,'Italienesc',28)

insert into restaurant values(8,'Frantuzesc',14)

Select *

From restaurant

insert into Oferte values(1,2008,1,11,12)

insert into Oferte values(2,2009,2,6,8)

insert into Oferte values(3,2008,3,4,5)

insert into Oferte values(4,2009,4,2,5)

insert into Oferte values(5,2009,5,2,6)

insert into Oferte values(5,2008,6,11,12)

insert into Oferte values(6,2008,7,9,11)

insert into Oferte values(6,2009,8,7,9)

insert into Oferte values(7,2009,9,1,5)

insert into Oferte values(7,2009,10,6,7)

Select *

From Oferte

insert into Manager values('Grigore Ureche','1772307123143','0264229213')

insert into Manager values('Ion Popescu','1822311143134','0623123421')

insert into Manager values('Marin Preda','1841122123143','0783213123')

insert into Manager values('Anca Banu','2791221133344','0623245613')

insert into Manager values('Cristian Cremene','1690212131183','0765323114')

insert into Manager values('Dan Zicoane','1670617222133','0765458761')

insert into Manager values('Flaviu Pop','1590122143445','0793476513')

insert into Manager values('Georgeta Moldovan','1881122123143','0757638541')

Select *

From Manager

insert into Angajat values(1,'Daniel Morar','1740102253771','receptioner',1500)

insert into Angajat values(2,'Marius Manasturean','1591224333771','room service',1600)

insert into Angajat values(2,'Crina Manzat','2791112253351','portar',1100)

insert into Angajat values(3,'Adrian Stoian','1640405453472','sofer',1800)

insert into Angajat values(4,'Nicoleta Pop','2780303354768','camerista',1200)

insert into Angajat values(4,'Andrei Grecu','1680707423372','sofer',1900)

insert into Angajat values(5,'Bogdan Lazar','1710104253173','portar',2100)

insert into Angajat values(6,'Mihai Balan','1741101412412','receptioner',1600)

insert into Angajat values(6,'Cristina Bob','2841201423374','room service',1900)

insert into Angajat values(7,'Ioan Criste','1740915234452','portar',1700)

insert into Angajat values(8,'Alexandru Rus','1611115213879','room service',2200)

Select *

From Angajat

insert into Client values('1881021321875',1,130,'10/11/2008','10/14/2008')

insert into Client values('2851211321835',2,240,'09/17/2008','10/10/2008')

insert into Client values('2801111425865',2,239,'10/19/2008','11/10/2008')

insert into Client values('1780911321344',4,450,'06/21/2008','06/30/2008')

insert into Client values('2790211431575',4,450,'09/30/2008','10/10/2008')

insert into Client values('1611115213879',4,450,'10/28/2008','11/07/2008')

insert into Client values('2841201423374',5,120,'03/30/2008','04/10/2008')

insert into Client values('2780303354768',5,240,'01/12/2008','02/02/2008')

insert into Client values('1740915234452',6,199,'10/20/2008','11/10/2008')

insert into Client values('1740102253771',7,161,'11/25/2008','12/08/2008')

insert into Client values('1591224333771',8,234,'12/20/2008','01/03/2009')

insert into Client values('1561626335741',9,456,'11/10/2008','02/05/2009')

Select *

From Client

insert into Apartament values(1,130,1,'prezidential',1,300)

insert into Apartament values(1,131,1,'prezidential',2,400)

insert into Apartament values(2,240,2,'familly',3,400)

insert into Apartament values(2,239,2,'small',1,150)

insert into Apartament values(3,320,3,'star',2,500)

insert into Apartament values(4,450,4,'prezidential',2,700)

insert into Apartament values(5,120,1,'star',2,450)

insert into Apartament values(5,240,2,'familly',4,350)

insert into Apartament values(7,161,1,'prezidential',2,750)

insert into Apartament values(8,234,2,'familly',4,400)

insert into Apartament values(9,456,4,'small',1,100)

Select *

From Apartament

/* Popularea Relatiilor */

delete from RezervaA

delete from RezervaM

delete from are

delete from Lanseaza

delete from angajeaza

delete from concediaza

delete from promoveaza

delete from Apeleaza

insert into Lanseaza values(1,5,1,2008 )

insert into Lanseaza values(2,4,2,2009 )

insert into Lanseaza values(3,3,3,2008 )

insert into Lanseaza values(4,5,4,2009 )

insert into Lanseaza values(5,5,5,2009 )

insert into Lanseaza values(5,5,6,2008 )

insert into Lanseaza values(6,5,7,2008 )

insert into Lanseaza values(6,5,8,2009 )

insert into Lanseaza values(7,5,9,2009 )

insert into Lanseaza values(7,5,10,2009 )

Select *

From Lanseaza

insert into are values(1,'1772307123143','Grigore Ureche')

insert into are values(2,'1822311143134','Ion Popescu')

insert into are values(3,'1841122123143','Marin Preda')

insert into are values(4,'2791221133344','Anca Banu')

insert into are values(5,'1690212131183','Cristian Cremene')

insert into are values(6,'1670617222133','Dan Zicoane')

insert into are values(7,'1690212131183','Cristian Cremene')

insert into are values(8,'1881122123143','Georgeta Moldovan')

insert into are values(9,'1590122143445','Flaviu Pop')

Select *

From are

insert into Promoveaza values('Alfatour','Brasov',2,'Intercontinental')

insert into Promoveaza values('Betatour','Sofia',1,'Majestic')

insert into Promoveaza values('Gamatour','Budapesta',3,'RexPark')

insert into Promoveaza values('Ellada Travel','Atena',4,'Athos')

insert into Promoveaza values('Infinity Travel','Craiova',5,'Confort')

insert into Promoveaza values('Infinity Travel','Craiova',6,'Opera')

insert into Promoveaza values('Giulia Tour','Cluj',2,'Intercontinental')

insert into Promoveaza values('Giulia Tour','Cluj',4,'Hilton')

insert into Promoveaza values('Ellada Travel','Atena',7,'Athene Palace')

insert into Promoveaza values('Gamatour','Budapesta',8,'Carol Park')

Select *

From Promoveaza

insert into Angajeaza values('1772307123143','1740102253771','receptioner',1500,'10/10/2006')

insert into Angajeaza values('1822311143134','1591224333771','room service',1600,'11/10/2006')

insert into Angajeaza values('1822311143134','2791112253351','portar',1100,'09/10/2005')

insert into Angajeaza values('1841122123143','1640405453472','sofer',1800,'04/12/2006')

insert into Angajeaza values('2791221133344','2780303354768','camerista',1200,'11/23/2003')

insert into Angajeaza values('2791221133344','1680707423372','sofer',1900,'10/10/2003')

insert into Angajeaza values('1690212131183','1710104253173','portar',2100,'06/07/2005')

insert into Angajeaza values('1670617222133','1741101412412','receptioner',1600,'10/19/2005')

insert into Angajeaza values('1670617222133','2841201423374','room service',1900,'11/21/2006')

insert into Angajeaza values('1690212131183','1740915234452','portar',1700,'08/14/2005')

insert into Angajeaza values('1881122123143','1611115213879','room service',2200,'06/14/2004')

Select *

From Angajeaza

insert into Concediaza values('1772307123143','1591224333771','11/10/2007')

insert into Concediaza values('1881122123143','1640405453472','08/16/2007')

insert into Concediaza values('2791221133344','2780303354768','06/12/2006')

insert into Concediaza values('1670617222133','2841201423374','12/11/2007')

Select *

From Concediaza

insert into Apeleaza values('1881021321875','1740102253771','pachet','11/10/2006')

insert into Apeleaza values('2851211321835','1591224333771','sampanie','11/11/2007')

insert into Apeleaza values('2801111425865','1591224333771','bere','10/21/2006')

insert into Apeleaza values('1780911321344','2780303354768','curatenie','02/24/2007')

insert into Apeleaza values('2790211431575','1680707423372','transport','04/14/2007')

insert into Apeleaza values('1611115213879','2780303354768','curatenie','09/05/2006')

insert into Apeleaza values('2780303354768','2841201423374','vin alb','08/08/2007')

insert into Apeleaza values('1740102253771','1611115213879','sampanie','11/12/2007')

Select *

From Apeleaza

insert into RezervaM values('1881021321875',1,'Italienesc','04/12/2006')

insert into RezervaM values('1780911321344',4,'Frantuzesc','09/10/2005')

insert into RezervaM values('2790211431575',4,'Frantuzesc','12/09/2006')

insert into RezervaM values('1611115213879',4,'Frantuzesc','09/10/2005')

insert into RezervaM values('2841201423374',5,'Thailandez','04/12/2007')

insert into RezervaM values('2780303354768',6,'Ardelenesc','12/11/2006')

insert into RezervaM values('1740915234452',6,'Grecesc','09/23/2007')

insert into RezervaM values('1740102253771',8,'Frantuzesc','06/30/2006')

insert into RezervaM values('1591224333771',8,'Frantuzesc','04/25/2006')

Select *

From RezervaM

insert into RezervaA values('1881021321875',1,'10/11/2008',130)

insert into RezervaA values('2851211321835',2,'09/17/2008',240)

insert into RezervaA values('2801111425865',2,'10/19/2008',239)

insert into RezervaA values('1780911321344',4,'06/21/2008',450)

insert into RezervaA values('2790211431575',4,'09/30/2008',450)

insert into RezervaA values('1611115213879',4,'10/28/2008',450)

insert into RezervaA values('2841201423374',5,'03/30/2008',120)

insert into RezervaA values('2780303354768',5,'01/12/2008',240)

insert into RezervaA values('1740102253771',7,'11/25/2008',161)

insert into RezervaA values('1591224333771',8,'12/20/2008',234)

insert into RezervaA values('1561626335741',9,'11/10/2008',456)

Select *

From RezervaA

INTEROGARI

/* 1. Ofertele date de hotelurile de 4 stele in anul 2008 */

Select Oferta_ID,'Nume Hotel'=Nume,Categorie,An

From Oferte O, Hotel H

Where

O.Hotel_ID=H.Hotel_ID

AND H.Categorie=4

AND O.An=2008

/*In algebra relationala :

Πoferta_ID(σcategorie=4(Hotel)∩ σAn=2008 (Oferte)) (Hotel X Oferte)

/* 2. Numarul de apeluri la sampanie in luna noiembrie a anului 2007 */

Select 'Numar Apeluri'=COUNT(*)

From Apeleaza A

Where MONTH(A.data)=11

AND Tip_Apel='sampanie'

AND YEAR(A.data)=2007

/* 3. Cate Hoteluri au oferta in anul 2009*/

Select 'Numar Hoteluri'=COUNT(*)

From Hotel

Where Hotel_ID IN

( SELECT Hotel_ID

FROM Oferte O

Where O.An=2009)

/* 4. Numele oraselor din Romania in care exista agentii de turism , impreuna cu numarul acestora*/

Select Oras,'Numar Agentii'=COUNT(NumeAg)

From Ag_Turism A

Where A.Tara='Romania'

Group by Oras

In algebra Relationala :

σ Oras _gcount(NumeAg)(Ag_Turism )

/*5 Outer Join */

Select *

From Hotel full outer join Oferte ON Hotel.Hotel_ID=Oferte.Hotel_ID

In algebra relationala :

/* 6. Lista tuturor hotelurilor, impreuna cu detaliile lor ( join pe aceeasi tabela ) */

Select *

From Hotel AS H1 INNER JOIN Hotel ON (H1.Hotel_ID=Hotel.Hotel_ID)

/* 7. Angajatii concediati de managerul Georgeta Moldovan*/

SELECT NumeM,CNPA

FROM Concediaza C,Manager M

WHERE (C.CNPM=M.CNPM AND NumeM='Georgeta Moldovan')

/* 8. Vedere care contine toate angajarile impreuna cu detaliile lor */

IF EXISTS (SELECT Table_Name FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_NAME='Angajari')

DROP VIEW Angajari

GO

CREATE VIEW Angajari AS

Select     Manager.CNPM,

Angajat.CNPA,

Angajat.NumeA,

Angajeaza.Data

From Manager, Angajat, Angajeaza

Where

Manager.CNPM=Angajeaza.CNPM AND

Angajat.CNPA=Angajeaza.CNPA

GO

/* 9. Numele Agentiilor de Turism care promoveaza cel putin doua hoteluri */

SELECT NumeAg,Oras

FROM Ag_turism

WHERE NumeAg IN

(

SELECT NumeAg

FROM Promoveaza P

WHERE NumeAg IN

(SELECT NumeAg

FROM Promoveaza

WHERE Hotel_ID<>P.Hotel_ID AND NumeAg=P.NumeAg)

/*10. Clientii care nu au rezervat masa la restaurant */

SELECT CNP,Hotel_ID

FROM Client C

WHERE CNP NOT IN

(SELECT CNP

FROM RezervaM M

WHERE M.CNP=C.CNP) /* Lant hotelier */

/* Trebuie sterse indexurile clusterate implicite din tabelele pe care se creeaza indexuri */

/* Index pe tabela RezervaM pentru interogarea 10*/

/*SELECT CNP,Hotel_ID

FROM Client C

WHERE CNP NOT IN

(SELECT CNP

FROM RezervaM M

WHERE M.CNP=C.CNP)*/

IF EXISTS (SELECT name FROM sysindexes   

WHERE name='Ind.Ind_RezM')

DROP INDEX Ind.Ind_RezM

GO

CREATE UNIQUE CLUSTERED INDEX Ind_RezM ON RezervaM(CNP)

/* Index pe tabela Ag_Turism, pentru interogarea 4*/

/*Select Oras,'Numar Agentii'=COUNT(NumeAg)

From Ag_Turism A

Where A.Tara='Romania'

Group by Oras     */

IF EXISTS (SELECT name FROM sysindexes   

WHERE name='Ind.Ind_Ag')

DROP INDEX Ind.Ind_Ag

GO

CREATE UNIQUE CLUSTERED INDEX Ind_Ag ON Ag_Turism(Oras)

/* Index pe tabela Hotel, pentru interogarea 6*/

/*Select *

From Hotel AS H1 INNER JOIN Hotel ON (H1.Hotel_ID=Hotel.Hotel_ID)*/

IF EXISTS (SELECT name FROM sysindexes   

WHERE name='Ind.Ind_Hotel')

DROP INDEX Ind.Ind_Hotel

GO

CREATE UNIQUE CLUSTERED INDEX Ind_Hotel ON Hotel(Hotel_ID)

// Clasa creata in NetBeans, care realizeaza diferite tranzactii

import java.sql.*;

public class Main

// Closes a connection

private void closeConnection(Connection con)catch(Exception e)

}

// Describes different types of statements

private void Statement(Connection con) throws SQLException

}

private static void main(String args[]) throws SQLException

}