Universidad Técnica Particular de Loja Base de Datos I

Alumna: Marilyn Jaramillo

Ing. Juan Carlos Morocho

Desarrollo de Ejercicios del Capitulo 6

6.10 Cree la tabla Hotel utilizando las características de mejora de la integridad de SQL

CREATE TABLE Hotel (

hotelNo varchar2(4) PRIMARY KEY, hotelName varchar2(30) NOT NULL, city varchar2(15) NOT NULL

);

6.11 Ahora cree las tablas Room, Booking y Guest utilizando las características de mejora de la integridad de SQL con las siguientes restricciones

Type puede tener los siguientes valores: Single,Double o Family. Price debe estar comprendido entre 10 euros y 100 euros. roomNo debe estar comprendido entre 1 y 100. dateFrom y dateTo deben ser mayores que la fecha actual. no se puede reservar dos veces en una misma habitación. Un mismo huésped no puede tener reservas que se solapen.

CREATE TABLE Room

( roomNo number(3) CHECK(roomNo BETWEEN 1 AND 100), hotelNo varchar2 (4) NOT NULL, type varchar2(15) NOT NULL CHECK(type IN('Single','Double','Family')), price number(8,2) NOT NULL CHECK(price BETWEEN 10 AND 100), CONSTRAINT roomNo_PK PRIMARY KEY (roomNo), CONSTRAINT hotelNo_FK FOREIGN KEY (hotelNo)reference Hotel(hotelNo),

);

CREATE TABLE Booking (

hotelNo varchar2(4) NOT NULL, guestNo number(3) NOT NULL, dateFrom DATE NOT NULL CHECK(dateFrom < dateTo), dateTo DATE NOT NULL CHECK(dateTo > dateFrom), roomNo number(3) NOT NULL UNIQUE, CONSTRAINT Booking_PK PRIMARY KEY (hotelNo, guestNo, roomNo), CONSTRAINT hotel_FK FOREIGN KEY (hotelNo) reference Hotel(hotelNo), CONSTRAINT guest _FK FOREIGN KEY (guestNo) reference Guest(guestNo), CONSTRAINT room _FK FOREIGN KEY (roomNo) reference Room(roomNo),

);

CREATE TABLE Guest (

guestNo number(3) PRIMARY KEY, guestName varchar2(30) NOT NULL, guestAddress varchar2(30) NOT NULL

);

6.12 Cree una tabla separada con la misma estructura que la tabla Booking para almacenar los registros antiguos. Utilizando la instrucción INSERT, copie los registros de la tabla Booking a la tabla de archivo, traspasando únicamente las reservas anteriores al 1 de enero de 2003. Borre todas las reservas anteriores al 1 de enero de 2003 de la tabla Booking.

CREATE TABLE Reg_Antiguos (

hotelNo varchar2(4) NOT NULL, guestNo number(3) NOT NULL, dateFrom DATE NOT NULL CHECK(dateFrom < dateTo), dateTo DATE NOT NULL CHECK(dateTo > dateFrom), roomNo number(3) NOT NULL UNIQUE,

CONSTRAINT Booking_PK PRIMARY KEY (hotelNo, guestNo, roomNo), CONSTRAINT hotel_FK FOREIGN KEY (hotelNo) reference Hotel(hotelNo), CONSTRAINT guest _FK FOREIGN KEY (guestNo) reference Guest(guestNo), CONSTRAINT room _FK FOREIGN KEY (roomNo) reference Room(roomNo),

); INSERT INTO Reg_Antiguos SELECT * FROM Booking b WHERE b.dateTo < '1 de enero de 2003'; DELETE FROM Booking WHERE dateTo < ''1 de enero de 2003'; 6.13 Cree una vista que contenga el nombre del hotel y los nombres de los huéspedes albergados en el hotel. CREATE VIEW Vista(hotelName, guestName) AS SELECT h.hotelName, g.guestName FROM Booking b, Guest g, Hotel h WHERE b.hotelNo = h.hotelNo AND b.guestNo = g.guestNo; 6.14 Cree una vista que contenga la cuenta correspondiente a cada huésped del Grovenor Hotel. CREATE VIEW Cuenta(Cuenta, Cliente) AS SELECT ((b.dateTo – b.dateFrom)*r.price), g.guestName FROM Booking b, Guest g, Room r WHERE b.roomNo = r.roomNo AND b.guestNo = g.guestNo; 6.15 Proporcione a los usuarios Manager y Director acceso completo a estas vistas, con el privilegio de pasar el acceso a otros usuarios. GRANT SELECT, UPDATE, DELETE, INSERT ON Vista TO Manager, Director WHIT GRANT OPTION; GRANT SELECT, UPDATE, DELETE, INSERT

ON Cuenta TO Manager, Director WHIT GRANT OPTION; 6.16 Proporcione al usuario Accounts acceso de tipo SELECT a estas vistas. Ahora revoque el acceso de ese usuario. GRANT SELECT ON Vista TO Accounts; REVOKE SELECT ON Vista FROM Accounts; GRANT SELECT ON Cuenta TO Accounts; REVOKE SELECT ON Cuenta FROM Accounts; 6.17 Considere la siguiente vista definida sobre el esquema Hotel: CREATE VIEW HotelBookingCount ( hotelNo,bookingCount) AS SELECT h.hotelNo, COUNT (*) FROM Hotel h, Room r, Booking b WHERE h.hotelNo = r.hotelNo AND r.roomNo = b.roomNo GROUP BY h.hotelNo; Para cada una de las siguientes consultas indique si la consulta es valida y, en caso de que lo que sea, muestre como se establecería la correspondencia entre la consulta y la respectiva consulta sobre las tablas base subyacentes.

SELECT * FROM HotelBookingCount;

Verdadero

SELECT h.hotelNo, COUNT (*) FROM Hotel h, Room r, Booking b WHERE h.hotelNo = r.hotelNo AND r.roomNo = b.roomNo GROUP BY h.hotelNo;

SELECT hotelNo WHERE hotelNo = ‘H001’;

Verdadero SELECT hotelNo FROM Hotel WHERE hotelNo = 'H001';

SELECT MIN (bookingCount) FROM HotelBookingCount;

Falso

SELECT COUNT (*) FROM HotelBookingCount;

Falso

SELECT hotelNo FROM HotelBookingCount

WHERE bookingCount > 1000; Verdadero SELECT h.hotelNo, COUNT (*) FROM Hotel h, Room r, Booking b WHERE h.hotelNo = r.hotelNo AND r.roomNo = b.roomNo GROUP BY h.hotelNo; HAVING COUNT (*) > 1000

SELECT hotelNo FROM HotelBookingCount ORDER BY bookingCount;

Falso