Question 1: Establish Relationships and Enforce Referential Integrity
ALTER TABLE `reservation` ADD FOREIGN KEY (`TripID`) REFERENCES `trip`(`TripID`) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE `reservation` ADD FOREIGN KEY (`CustomerNum`) REFERENCES `customer`(`CustomerNum`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `tripguides` ADD FOREIGN KEY (`GuideNum`) REFERENCES `guide`(`GuideNum`) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE `tripguides` ADD FOREIGN KEY (`TripID`) REFERENCES `trip`(`TripID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `tripguides` ADD `TripGuideID` INT NULL AFTER `GuideNum`, ADD INDEX `NewIDX` (`TripGuideID`);
UPDATE `tripguides` SET `TripGuideID` = ‘1’ WHERE CONCAT(`tripguides`.`TripID`) = ‘1’ AND `tripguides`.`GuideNum` = ‘GZ01’; UPDATE `tripguides` SET `TripGuideID` = ‘2’ WHERE CONCAT(`tripguides`.`TripID`) = ‘1’ AND `tripguides`.`GuideNum` = ‘RH01’; UPDATE `tripguides` SET `TripGuideID` = ‘3’ WHERE CONCAT(`tripguides`.`TripID`) = ‘2’ AND `tripguides`.`GuideNum` = ‘SL01’; UPDATE `tripguides` SET `TripGuideID` = ‘4’ WHERE CONCAT(`tripguides`.`TripID`) = ‘3’ AND `tripguides`.`GuideNum` = ‘SL01’; UPDATE `tripguides` SET `TripGuideID` = ‘5’ WHERE CONCAT(`tripguides`.`TripID`) = ‘4’ AND `tripguides`.`GuideNum` = ‘BR01’; UPDATE `tripguides` SET `TripGuideID` = ‘6’ WHERE CONCAT(`tripguides`.`TripID`) = ‘4’ AND `tripguides`.`GuideNum` = ‘GZ01’; UPDATE `tripguides` SET `TripGuideID` = ‘7’ WHERE CONCAT(`tripguides`.`TripID`) = ‘5’ AND `tripguides`.`GuideNum` = ‘KS01’; UPDATE `tripguides` SET `TripGuideID` = ‘8’ WHERE CONCAT(`tripguides`.`TripID`) = ‘5’ AND `tripguides`.`GuideNum` = ‘UG01’; UPDATE `tripguides` SET `TripGuideID` = ‘9’ WHERE CONCAT(`tripguides`.`TripID`) = ‘6’ AND `tripguides`.`GuideNum` = ‘RH01′; UPDATE `tripguides` SET `TripGuideID` = ’10’ WHERE CONCAT(`tripguides`.`TripID`) = ‘7’ AND `tripguides`.`GuideNum` = ‘SL01′; UPDATE `tripguides` SET `TripGuideID` = ’11’ WHERE CONCAT(`tripguides`.`TripID`) = ‘8’ AND `tripguides`.`GuideNum` = ‘BR01′; UPDATE `tripguides` SET `TripGuideID` = ’12’ WHERE CONCAT(`tripguides`.`TripID`) = ‘9’ AND `tripguides`.`GuideNum` = ‘BR01′; UPDATE `tripguides` SET `TripGuideID` = ’13’ WHERE CONCAT(`tripguides`.`TripID`) = ’10’ AND `tripguides`.`GuideNum` = ‘GZ01′; UPDATE `tripguides` SET `TripGuideID` = ’14’ WHERE CONCAT(`tripguides`.`TripID`) = ’11’ AND `tripguides`.`GuideNum` = ‘DH01′; UPDATE `tripguides` SET `TripGuideID` = ’15’ WHERE CONCAT(`tripguides`.`TripID`) = ’11’ AND `tripguides`.`GuideNum` = ‘KS01′; UPDATE `tripguides` SET `TripGuideID` = ’16’ WHERE CONCAT(`tripguides`.`TripID`) = ’11’ AND `tripguides`.`GuideNum` = ‘UG01′; UPDATE `tripguides` SET `TripGuideID` = ’17’ WHERE CONCAT(`tripguides`.`TripID`) = ’12’ AND `tripguides`.`GuideNum` = ‘BR01′; UPDATE `tripguides` SET `TripGuideID` = ’18’ WHERE CONCAT(`tripguides`.`TripID`) = ’13’ AND `tripguides`.`GuideNum` = ‘RH01′; UPDATE `tripguides` SET `TripGuideID` = ’19’ WHERE CONCAT(`tripguides`.`TripID`) = ’14’ AND `tripguides`.`GuideNum` = ‘KS02′; UPDATE `tripguides` SET `TripGuideID` = ’20’ WHERE CONCAT(`tripguides`.`TripID`) = ’15’ AND `tripguides`.`GuideNum` = ‘GZ01′; UPDATE `tripguides` SET `TripGuideID` = ’21’ WHERE CONCAT(`tripguides`.`TripID`) = ’16’ AND `tripguides`.`GuideNum` = ‘KS02′; UPDATE `tripguides` SET `TripGuideID` = ’22’ WHERE CONCAT(`tripguides`.`TripID`) = ’17’ AND `tripguides`.`GuideNum` = ‘RH01′; UPDATE `tripguides` SET `TripGuideID` = ’23’ WHERE CONCAT(`tripguides`.`TripID`) = ’18’ AND `tripguides`.`GuideNum` = ‘KS02′; UPDATE `tripguides` SET `TripGuideID` = ’24’ WHERE CONCAT(`tripguides`.`TripID`) = ’19’ AND `tripguides`.`GuideNum` = ‘DH01′; UPDATE `tripguides` SET `TripGuideID` = ’25’ WHERE CONCAT(`tripguides`.`TripID`) = ‘2’ AND `tripguides`.`GuideNum` = ‘AM01′; UPDATE `tripguides` SET `TripGuideID` = ’26’ WHERE CONCAT(`tripguides`.`TripID`) = ’20’ AND `tripguides`.`GuideNum` = ‘SL01′; UPDATE `tripguides` SET `TripGuideID` = ’27’ WHERE CONCAT(`tripguides`.`TripID`) = ’21’ AND `tripguides`.`GuideNum` = ‘AM01′; UPDATE `tripguides` SET `TripGuideID` = ’28’ WHERE CONCAT(`tripguides`.`TripID`) = ’22’ AND `tripguides`.`GuideNum` = ‘UG01′; UPDATE `tripguides` SET `TripGuideID` = ’29’ WHERE CONCAT(`tripguides`.`TripID`) = ’23’ AND `tripguides`.`GuideNum` = ‘DH01′; UPDATE `tripguides` SET `TripGuideID` = ’30’ WHERE CONCAT(`tripguides`.`TripID`) = ’23’ AND `tripguides`.`GuideNum` = ‘SL01′; UPDATE `tripguides` SET `TripGuideID` = ’31’ WHERE CONCAT(`tripguides`.`TripID`) = ’24’ AND `tripguides`.`GuideNum` = ‘BR01′; UPDATE `tripguides` SET `TripGuideID` = ’32’ WHERE CONCAT(`tripguides`.`TripID`) = ’25’ AND `tripguides`.`GuideNum` = ‘BR01′; UPDATE `tripguides` SET `TripGuideID` = ’33’ WHERE CONCAT(`tripguides`.`TripID`) = ’26’ AND `tripguides`.`GuideNum` = ‘GZ01′; UPDATE `tripguides` SET `TripGuideID` = ’34’ WHERE CONCAT(`tripguides`.`TripID`) = ’27’ AND `tripguides`.`GuideNum` = ‘GZ01′; UPDATE `tripguides` SET `TripGuideID` = ’35’ WHERE CONCAT(`tripguides`.`TripID`) = ’28’ AND `tripguides`.`GuideNum` = ‘BR01′; UPDATE `tripguides` SET `TripGuideID` = ’36’ WHERE CONCAT(`tripguides`.`TripID`) = ’29’ AND `tripguides`.`GuideNum` = ‘DH01′; UPDATE `tripguides` SET `TripGuideID` = ’37’ WHERE CONCAT(`tripguides`.`TripID`) = ’30’ AND `tripguides`.`GuideNum` = ‘AM01′; UPDATE `tripguides` SET `TripGuideID` = ’38’ WHERE CONCAT(`tripguides`.`TripID`) = ’31’ AND `tripguides`.`GuideNum` = ‘SL01′; UPDATE `tripguides` SET `TripGuideID` = ’39’ WHERE CONCAT(`tripguides`.`TripID`) = ’32’ AND `tripguides`.`GuideNum` = ‘KS01′; UPDATE `tripguides` SET `TripGuideID` = ’40’ WHERE CONCAT(`tripguides`.`TripID`) = ’33’ AND `tripguides`.`GuideNum` = ‘UG01′; UPDATE `tripguides` SET `TripGuideID` = ’41’ WHERE CONCAT(`tripguides`.`TripID`) = ’34’ AND `tripguides`.`GuideNum` = ‘KS01′; UPDATE `tripguides` SET `TripGuideID` = ’42’ WHERE CONCAT(`tripguides`.`TripID`) = ’35’ AND `tripguides`.`GuideNum` = ‘GZ01′; UPDATE `tripguides` SET `TripGuideID` = ’43’ WHERE CONCAT(`tripguides`.`TripID`) = ’36’ AND `tripguides`.`GuideNum` = ‘KS02′; UPDATE `tripguides` SET `TripGuideID` = ’44’ WHERE CONCAT(`tripguides`.`TripID`) = ’37’ AND `tripguides`.`GuideNum` = ‘RH01′; UPDATE `tripguides` SET `TripGuideID` = ’45’ WHERE CONCAT(`tripguides`.`TripID`) = ’38’ AND `tripguides`.`GuideNum` = ‘KS02′; UPDATE `tripguides` SET `TripGuideID` = ’46’ WHERE CONCAT(`tripguides`.`TripID`) = ’39’ AND `tripguides`.`GuideNum` = ‘BR01′; UPDATE `tripguides` SET `TripGuideID` = ’47’ WHERE CONCAT(`tripguides`.`TripID`) = ’40’ AND `tripguides`.`GuideNum` = ‘DH01′; UPDATE `tripguides` SET `TripGuideID` = ’48’ WHERE CONCAT(`tripguides`.`TripID`) = ’41’ AND `tripguides`.`GuideNum` = ‘BR01’;
Question 2: Create Unique Indexes and Ensure Entity Integrity
Select * from Trip where TripType = ‘Biking’ and Distance > 15 and StartLocation NOT IN
(Select StartLocation From Trip where StartLocation = ‘New Hampshire’);
Select * from Trip where StartLocation = ‘Maine’ OR StartLocation = ‘Massachuset and TripType = ‘Biking’ OR TripType = ‘Hiking’;
Select * from Trip where StartLocation = ‘Maine’ OR StartLocation = ‘Massachuset and TripType = ‘Biking’ OR TripType = ‘Hiking’;
Select Trip.TripName, Trip.TripType, trip.MaxGrpSize from Trip
Inner Join TripGuides On Trip.TripID = TripGuides.TripID
Inner Join Guide On TripGuides.GuideNum = Guide.GuideNum
where guide.FirstName = ‘Susan’ and guide.LastName = ‘Kiley’;
Select TripName, State From trip Where Season = ‘Summer’
Order By State;
Select Trip.TripName, Trip.Season from Trip
Inner Join TripGuides On Trip.TripID = TripGuides.TripID
Inner Join Guide On TripGuides.GuideNum = Guide.GuideNum
where guide.FirstName = ‘Rita’ and guide.LastName = ‘Boyers’ and Trip.TripType = ‘Hiking’;
Select Trip.TripName, Guide.firstname, Guide.lastname from Trip
Inner Join TripGuides On Trip.TripID = TripGuides.TripID
Inner Join Guide On TripGuides.GuideNum = Guide.GuideNum
where Trip.StartLocation = ‘New Hampshire’ and Trip.TripName LIKE ‘mount’ OR Trip.TripName LIKE ‘mountains’ OR Trip.TripName LIKE ‘mt’ OR Trip.TripName LIKE ‘mtn’;
select Reservation.reservationID, Customer.CustomerNum, Customer.LastName, Customer.FirstName
From Reservation Inner Join Customer On Reservation.CustomerNum = Customer.CustomerNum
Inner Join Trip On Reservation.TripID = Trip.TripID
Where Reservation.TripDate > ‘2016-09-01’ and Reservation.TripDate > ‘2016-09-30’;
Select Reservation.reservationID, Trip.TripName, Customer.LastName, Customer.FirstName, ((Reservation.TripPrice + Reservation.OtherFees)*Reservation.NumPersons) As ‘Total Cost’
From Reservation Inner Join Customer On Reservation.CustomerNum = Customer.CustomerNum
Inner Join Trip On Reservation.TripID = Trip.TripID;
Create View Guide_Padddling As
Select Guide.LastName, Guide.FirstName
From Guide Inner Join TripGuides On Guide.GuideNum = TripGuides.GuideNum
Inner Join Trip On TripGuides.TripID = Trip.TripID
Where Trip.State= ‘NH’ and Trip.TripType = ‘Paddling’;
Select * From Guide_Padddling;
Create View ‘Trips_in_states’ As
Select State From Trip
Having COUNT(TripID) > 3;
Select * from ‘Trips_in_states’;
CREATE PROCEDURE `DisplayGuideName`(IN `ID` CHAR(4)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER SELECT CONCAT(“The Guide’s fullname is “, LastName,” “,FirstName) AS Fullname FROM Guide WHERE GuideNum = ID;
CALL DisplayGuideName(‘BR01’);
CREATE PROCEDURE `ShowReservationInfo`(IN `IDA` CHAR(7)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER Select * FROM reservation where ReservationID = IDA;
CALL ShowReservationInfo(‘1600008’);
CREATE PROCEDURE `AvgDist_TripType_Guide`( IN `IDB` CHAR(4), IN `Type` CHAR(20)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER SELECT AVG(trip.Distance) FROM trip INNER JOIN tripguides ON trip.TripID = tripguides.TripID WHERE trip.TripType = Type AND tripguides.GuideNum = IDB
CALL AvgDist_TripType_Guide(‘SL01′,’Paddling’);
CALL AvgDist_TripType_Guide(‘AM01′,’Biking’);
CALL AvgDist_TripType_Guide(‘SL01′,’Hiking’);
CREATE PROCEDURE `UpdateMaxGrpSize`(IN `IDC` DECIMAL(3,0), IN `mx` DECIMAL(4,0)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER UPDATE trip SET MaxGrpSize = mx WHERE TripID = IDC
Select * trip;
CALL UpdateMaxGrpSize(12,18);
SELECT * FROM trip;
Duggan, J., Elmore, A. J., Stonebraker, M., Balazinska, M., Howe, B., Kepner, J., … & Zdonik, S. (2015). The bigdawg polystore system. ACM Sigmod Record, 44(2), 11-16.
Petkovi?, D. (2017). JSON integration in relational database systems. Int J Comput Appl, 168(5), 14-19.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
