database implementation final document
TRANSCRIPT
Database Implementation plan for Refugio
Web System.
Prepared and created by KMDC2
Table of Contents
Entity Relationship Diagram for Refugio Web System Project.................................................1
Logical Database Model for Refugio Web System Project........................................................2
Data Dictionary for Refugio Web System Project.....................................................................3
Functional Dependency Graph for Refugio Web System Project.............................................5
SQL Script with Sample Data for Refugio Web System Project................................................6
Declaration.............................................................................................................................14
i | P a g e
Entity Relationship Diagram for Refugio Web System Project
Prepared by: KMDC2 Date: 16/01/2015
1 | P a g e
Logical Database Model for Refugio Web System Project
Prepared by: KMDC2 Date: 19/01/2015
2 | P a g e
Data Dictionary for Refugio Web System Project
Prepared by: KMDC2 Date: 16/01/2015
Owner 'Owner_ID' int (10) NOT NULL AUTO_INCREMENT,'Owner_username' char(25) DEFAULT NOT NULL,'Owner_full_name' var char(50) DEFAULT NOT NULL,'Owner_Password' varchar(50) DEFAULT NOT NULL,'Owner_Email' varchar(50) DEFAULT NOT NULL,'Contact_Details' int(30) DEFAULT NOT NULL,'Responses_ID' int(10) NOT NULL AUTO_INCREMENT,
Registrants'Reg_id' int(10) NOT NULL AUTO_INCREMENT, 'Reg_UserName' varchar(50) DEFAULT NOT NULL, 'Reg_FullName' varchar(50) DEFAULT NOT NULL, 'Contact_Details' int(30) DEFAULT NOT NULL, 'Reg_Password' varchar (20) DEFAULT NOT NULL, 'Reg_Address' varchar(100) DEFAULT NOT NULL, Accommodation 'Accom_id' int(10) NOT NULL AUTO_INCREMENT,'Accom_Description' varchar(500) DEFAULT NOT NULL,'Accom_Type' varchar(50) DEFAULT NOT NULL,'Accom_Check_in_Time' time(6)DEFAULT NOT NULL,'Accom_Check_out_Time' time(6)DEFAULT NOT NULL,'Accom_Price_Details' varchar(6) DEFAULT NOT NULL,'Accom_No_Bedrooms' int(2) DEFAULT NOT NULL,'Accom_No_Minimum_Nights' int(3) DEFAULT NOT NULL,'Accom_Address' varchar(100) DEFAULT NOT NULL,'Accom_Location' varchar(50) DEFAULT NOT NULL,'Accom_Capacity' int(12) DEFAULT NOT NULL,'Accom_Extra' varchar(45) DEFAULT NOT NULL,'City_ID' int(10) DEFAULT NOT NULL
Country'Country_id' int(10) NOT NULL AUTO_INCREMENT, 'Location' varchar(50) DEFAULT NOT NULL, 'Description' varchar(500) DEFAULT NOT NULL,
City'City_id' int(10) NOT NULL AUTO_INCREMENT,'Country_id' int(10) DEFAULT NULL,
3 | P a g e
'City_Name' varchar(50) DEFAULT NOT NULL,
Review'Review_ID' int(10) NOT NULL AUTO_INCREMENT,'Review_Description' varchar(500) NULL DEFAULT NULL,'Review_ExperienceMark' int(5) NULL DEFAULT NULL,'Review_Date' date NULL DEFAULT NULL,'Review_Time' time(6) NULL DEFAULT NULL,'Accommodation_ID' int(10) NULL DEFAULT NULL,'Registrant_ID' int(10) NULL DEFAULT NULL,'Booking_ID' int(10) NULL DEFAULT NULL,'Responses_ID' int(10) NULL DEFAULT NULL
Response'Response_id' int(10) NOT NULL AUTO_INCREMENT, 'Date_of_Response' date DEFAULT NOT NULL, 'Time_of_Response' time(6) DEFAULT NOT NULL, 'Owner_id' int (10) NOT NULL AUTO_INCREMENT,
Booking 'Booking_id' int(10) NOT NULL AUTO_INCREMENT, 'Booking_Arrival_Date' date DEFAULT NOT NULL, 'Booking_Departure_Date' date DEFAULT NOT NULL, 'Booking_No_Nights' int(10) DEFAULT NOT NULL, 'Booking_No_In_Party' int(10) DEFAULT NOT NULL, 'Accom_id' int(10) DEFAULT NULL,'Registrant_id' int(10) DEFAULT NULL,
Admin'Admin_Name' varchar(50) DEFAULT NOT NULL,'Admin_Password' varchar(50) DEFAULT NOT NULL,
4 | P a g e
Functional Dependency Graph for Refugio Web System Project
Prepared by: KMDC2 Date: 16/01/2015
Registrants = 'Reg_id' , 'Reg_UserName' , 'Reg_FullName' , 'Contact_Details' 'Reg_Password', 'Reg_Address'
Owner = 'Owner_ID' ,'Owner_username' ,`Owner_full_name`'Owner_Password' 'Owner_Email', ‘Owner_Contact_Details’, ‘Responses_ID’
Accommodation = 'Accom_id' ,'Accom_Description' ,'Accom_Type' ,'Accom_Check_in_Time',
'Accom_Check_out_Time','Accom_Price_Details','Accom_No_Bedrooms','Accom_No_Minimum_Nig
hts' , 'Accom_Address' ,'Accom_Location' , 'Accom_Capacity' ,'Accom_Extra' , 'City_ID' ,
Country = 'Country_id' ,'Description' ,'Location'
City = 'City_id','Country_id' ,'City_Name'
Review = 'Review_ID' ,'Review_Description' ,'Review_ExperienceMark' ,'Review_Date' 'Review_Time' ,'Accommodation_ID' 'Registrant_ID' ,'Booking_ID','Responses_ID'
Response = 'Response_id' , 'Date_of_Response','Time_of_Response', 'Owner_id'
Booking ='Booking_id' ,'Booking_Arrival_Date' , 'Booking_Departure_Date' , 'Booking_No_Nights', 'Booking_No_In_Party' , 'Accom_id' , 'Registrant_id'
Admin= 'Admin_Name' ,'Admin_Password'
5 | P a g e
SQL Script with Sample Data for Refugio Web System Project
Prepared by: KMDC2 Date: 05/03/2015
CREATE DATABASE IF NOT EXISTS `refugio` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `refugio`;-- MySQL dump 10.13 Distrib 5.6.17, for Win32 (x86)---- Host: localhost Database: refugio-- -------------------------------------------------------- Server version 5.6.23-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
---- Table structure for table `accommodation`--
DROP TABLE IF EXISTS `accommodation`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;
6 | P a g e
CREATE TABLE `accommodation` ( `Accom_id` int(6) NOT NULL AUTO_INCREMENT, `Accom_description` varchar(500) NOT NULL, `Accom_check_in_time` time(6) NOT NULL, `Accom_check_out_time` time(6) NOT NULL, `Accom_price_details` varchar(50) NOT NULL, `Accom_no_bedrooms` int(12) NOT NULL, `Accom_minimun_no_nights` int(10) NOT NULL, `Accom_address` varchar(100) NOT NULL, `Accom_location` varchar(45) NOT NULL, `Accom_capacity` int(12) NOT NULL, `Accom_extras` varchar(45) NOT NULL, `city_id` int(6) NOT NULL, `Accom_Type` varchar(50) NOT NULL, PRIMARY KEY (`Accom_id`), KEY `city_id_idx` (`city_id`), CONSTRAINT `city_id` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `accommodation`--
LOCK TABLES `accommodation` WRITE;/*!40000 ALTER TABLE `accommodation` DISABLE KEYS */;INSERT INTO `accommodation` VALUES (1,'Stay in the amazing Excelsior Palace Hotel in the renouned Italian Riviera for an experience of a lifetime. Situated overlooking the coastline, Excelsior Palace Hotel is just 10 minutes to all local amenities such as bars, clubs, shops and so on. Experience the breathtaking views across the Tyrrhenian Sea without interruption from the comfort of the bedroom. Amenities include: Late bar, restaurant, luxury spa, pool, jacuzzi, Twin / Double / King / Queen rooms as well as a honeymoon suite ','14:00:00.000000','12:00:00.000000','E255pps',200,1,'Viale Pietro Toselli, 8, 98039 Taormina ME, Italy','Italy',650,'AirCon,Internet,SkyTV',1,'Hotel'),(2,'Here at The Mercure Nice Promenade des Anglais, we know what our guests want. Allow us to take you on a truly memorable experiance with world class service at your finger tips. Our experienced and friendly staff know exactly how your trip is meant to go, making it the trip of a lifetime.','14:00:00.000000','15:00:00.000000','E185pps',200,1,'2 rue Halevy 06000 NICE - FRANCE','France',650,'AirCon,Internet,SkyTV',2,'Hotel');/*!40000 ALTER TABLE `accommodation` ENABLE KEYS */;UNLOCK TABLES;
---- Table structure for table `admin`--
DROP TABLE IF EXISTS `admin`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;
7 | P a g e
CREATE TABLE `admin` ( `admin_name` varchar(50) NOT NULL, `admin_password` varchar(50) NOT NULL, PRIMARY KEY (`admin_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `admin`--
LOCK TABLES `admin` WRITE;/*!40000 ALTER TABLE `admin` DISABLE KEYS */;INSERT INTO `admin` VALUES ('Claire','adminTest896'),('Pat','adminTest265');/*!40000 ALTER TABLE `admin` ENABLE KEYS */;UNLOCK TABLES;
---- Table structure for table `booking`--
DROP TABLE IF EXISTS `booking`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `booking` ( `booking_id` int(6) NOT NULL AUTO_INCREMENT, `booking_arrival_date` datetime NOT NULL, `booking_departure_date` datetime NOT NULL, `booking_no_of_nights` int(12) NOT NULL, `booking_no_in_party` int(10) NOT NULL, `registrant_id` int(6) NOT NULL, `accom_id` int(6) NOT NULL, PRIMARY KEY (`booking_id`), KEY `registrant_id_idx` (`registrant_id`), CONSTRAINT `registrant_id` FOREIGN KEY (`registrant_id`) REFERENCES `registrant` (`registrant_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `booking`--
LOCK TABLES `booking` WRITE;/*!40000 ALTER TABLE `booking` DISABLE KEYS */;INSERT INTO `booking` VALUES (1,'2012-04-15 00:00:00','2016-04-15 00:00:00',4,3,1,1),(2,'2024-03-15 00:00:00','2030-03-15 00:00:00',7,2,2,2);/*!40000 ALTER TABLE `booking` ENABLE KEYS */;UNLOCK TABLES;
8 | P a g e
---- Table structure for table `city`--
DROP TABLE IF EXISTS `city`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `city` ( `city_id` int(10) NOT NULL AUTO_INCREMENT, `country_id` int(6) NOT NULL, `city_name` varchar(50) NOT NULL, PRIMARY KEY (`city_id`), KEY `country_id_idx` (`country_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `city`--
LOCK TABLES `city` WRITE;/*!40000 ALTER TABLE `city` DISABLE KEYS */;INSERT INTO `city` VALUES (1,1,'Sicily'),(2,2,'Nice');/*!40000 ALTER TABLE `city` ENABLE KEYS */;UNLOCK TABLES;
---- Table structure for table `country`--
DROP TABLE IF EXISTS `country`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `country` ( `country_id` int(6) NOT NULL AUTO_INCREMENT, `Location` varchar(50) NOT NULL, `Description` varchar(500) NOT NULL, PRIMARY KEY (`country_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `country`--
LOCK TABLES `country` WRITE;/*!40000 ALTER TABLE `country` DISABLE KEYS */;INSERT INTO `country` VALUES (1,'Italy','Stay in the amazing Excelsior Palace Hotel in the renouned Italian Riviera for an experience of a lifetime. Situated overlooking the coastline, Excelsior Palace Hotel is just 10 minutes to all local amenities such as bars, clubs,
9 | P a g e
shops and so on. Experience the breathtaking views across the Tyrrhenian Sea without interruption from the comfort of the bedroom. Amenities include: Late bar, restaurant, luxury spa, pool, jacuzzi, Twin / Double / King / Queen rooms as well as a honeymoon suite '),(2,'France',' Here at The Mercure Nice Promenade des Anglais, we know what our guests want. Allow us to take you on a truly memorable experiance with world class service at your finger tips. Our experienced and friendly staff know exactly how your trip is meant to go, making it the trip of a lifetime.');/*!40000 ALTER TABLE `country` ENABLE KEYS */;UNLOCK TABLES;
---- Table structure for table `owner`--
DROP TABLE IF EXISTS `owner`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `owner` ( `Owner_ID` int(10) NOT NULL AUTO_INCREMENT, `Owner_username` varchar(50) NOT NULL, `Owner_full_Name` varchar(50) NOT NULL, `Owner_Password` varchar(50) NOT NULL, `Owner_Email` varchar(50) NOT NULL, `Owner_Contact_Details` int(30) NOT NULL, `review_id` varchar(15) NOT NULL, `response_id` int(10) NOT NULL, PRIMARY KEY (`Owner_ID`), KEY `response_id_idx` (`response_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `owner`--
LOCK TABLES `owner` WRITE;/*!40000 ALTER TABLE `owner` DISABLE KEYS */;INSERT INTO `owner` VALUES (1,'OW22356874','Michael McCarthy','lightBug2016','[email protected]',895634527,'RE4653',1),(2,'OW755457982','Mary Fitzpatrick','EmpireGreenland92','[email protected]',865439865,'RE8692',2);/*!40000 ALTER TABLE `owner` ENABLE KEYS */;UNLOCK TABLES;
---- Table structure for table `registrant`--
DROP TABLE IF EXISTS `registrant`;/*!40101 SET @saved_cs_client = @@character_set_client */;
10 | P a g e
/*!40101 SET character_set_client = utf8 */;CREATE TABLE `registrant` ( `registrant_id` int(6) NOT NULL AUTO_INCREMENT, `reg_username` varchar(50) NOT NULL, `reg_full_name` tinytext NOT NULL, `reg_contact_details` int(30) NOT NULL, `reg_password` varchar(20) NOT NULL, `reg_address` varchar(100) NOT NULL, `reg_email` varchar(50) NOT NULL, PRIMARY KEY (`registrant_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `registrant`--
LOCK TABLES `registrant` WRITE;/*!40000 ALTER TABLE `registrant` DISABLE KEYS */;INSERT INTO `registrant` VALUES (1,'R23568420','Eoin Murphy',856497253,'Yellowbutterfly56','23 oakfield drive Dublin','[email protected]'),(2,'R36875429','Maria Collins',872576981,'Giantbluecooper2013','7 courtyard newcastle Sligo','[email protected]');/*!40000 ALTER TABLE `registrant` ENABLE KEYS */;UNLOCK TABLES;
---- Table structure for table `response`--
DROP TABLE IF EXISTS `response`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `response` ( `response_id` int(10) NOT NULL AUTO_INCREMENT, `owner_id` int(10) NOT NULL, `date_of_response` varchar(45) NOT NULL, `time_of_response` varchar(45) NOT NULL, `response_description` varchar(500) NOT NULL, PRIMARY KEY (`response_id`), KEY `owner_id_idx` (`owner_id`), CONSTRAINT `owner_id` FOREIGN KEY (`owner_id`) REFERENCES `owner` (`Owner_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `response`--
11 | P a g e
LOCK TABLES `response` WRITE;/*!40000 ALTER TABLE `response` DISABLE KEYS */;INSERT INTO `response` VALUES (1,1,'2012-03-15','13:13:00','Dear Customer, \r Thank you very much for your review. It is wonderful that you relish your junior suite and that our services came to your satisfaction. Kindly be advised although our first class Hotel Excelsior is not officially star rated we provide a four star service.We hope to welcome you back in the near future.\r Best regards.Michael McCarthy'),(2,2,'2016-03-15','18:34:00','We would like to thank you for your kind comment about our hotel and service. We appreciate your recommendation which helps us to build a better e-reputation among hotels in Nice. We are very grateful for having this chance to receive you.\r We welcome you to come back visiting Nice and it would be an honour for us to give you warm reception.\r Faithfully yours, Mary Fitzpatrick');/*!40000 ALTER TABLE `response` ENABLE KEYS */;UNLOCK TABLES;
---- Table structure for table `review`--
DROP TABLE IF EXISTS `review`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `review` ( `review_id` int(10) NOT NULL AUTO_INCREMENT, `review_description` varchar(500) NOT NULL, `review_Experience_mark` varchar(50) NOT NULL, `review_Date` date NOT NULL, `review_Time` time(6) NOT NULL, `accom_id` int(10) NOT NULL, `registrant_id` int(10) NOT NULL, `booking_id` int(10) DEFAULT NULL, `Response-id` int(10) DEFAULT NULL, PRIMARY KEY (`review_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `review`--
LOCK TABLES `review` WRITE;/*!40000 ALTER TABLE `review` DISABLE KEYS */;INSERT INTO `review` VALUES (1,'I spent many hours researching Italian hotels, finally opting for the 3 star Excelsior, even though 4 stars are my general preference. Junior suites were good value and the overall hotel ambiance most attractive making it the final choice. This is a great hotel. ','3','2012-03-15','13:13:00.000000',1,2,2,1),(2,'This Hotel exceeded my expectations. Lovely room furnished to a high standard. Welcoming staff and a fabulous location. I would definitely stay here again. Cheap and very cheerful . I am a very very happy customer. I most certainly will be back. ','4','2016-03-15','18:34:00.000000',2,1,1,2);/*!40000 ALTER TABLE `review` ENABLE KEYS */;
12 | P a g e
UNLOCK TABLES;
---- Dumping events for database 'refugio'--
---- Dumping routines for database 'refugio'--/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-03-05 12:17:2
13 | P a g e
Declaration
Prepared by: KMDC2 Date: 11/03/2015
We as a group, KMDC2, declare that all work enclosed in this document is 100% our own.
We declare that each member has contributed equally to the workload and has participated
in accordance with the requirements of the project specification.
Kim Hornibrook
Conor Murphy
Daniel Murphy
Conor O’Callaghan
Meghan Walsh
14 | P a g e