Practical Skills In SQL, Normalisation, Implementation And Security In Database Environment

Changes

The revised ERD has some changes that make it different from the initial ERD. PetType relation identifies the types of pets that owners can have. Every sitter has pet types that he or she can take care of. Before a booking is complete the owner of the dogs has to make request with all the details of the pets and other requirements about the sitting. These details are used to find a match of a sitter who qualifies or is available according to the requirements specified by the owner of the dogs.

Sitter (sitterID, name, suburb, email, phoneNO, profileDescription, prefferedSittingLocation, hostingPrice, homeSittingPrice, residentialStatus)

sitterPets (typeID, sitterID) foreign key (typeID) references petTypes (petTypeID), foreign key (sitterID) references sitter (sitterID)

petTypes (petTypeID, type, description)

Owner (ownerID, name, suburb, email, phoneNO)

Booking (BookingID, requestID, sitterID, details, agreedPrice, rating) Foreign key (requestID) references bookRequest (requestID)

BookRequest (requestID, ownerID, typeOfSitter, specialRequirements) Foreign key (ownerID) owner (ownerID)

BookRequestDates (requestiD, date) Foreign key (requestID) references bookRequest (requestID)

BookRequestPets (petID, requestID, petTypeID, furtherDescription) Foreign key (requestID) references bookRequest (requestID) , foreign key (typeID) references petTypes (petTypeID) 

Entity	Attribute	Description	Data Type	Domain	constraints
sitter	sitterID	Unique attribute identifying every sitter	Number	NOT NULL UNIQUE	PK
	name	Name of the sitter	VARCHAR2(50)	NOT NULL
	suburb	Suburb sitter is from	VARCHAR2(100)	NOT NULL
	email	Email of sitter	VARCHAR2(100)	NOT NULL
	phoneNO	phoneNO of sitter	VARCHAR2(25)	NOT NULL
	profileDescription	Description about the sitter	TEXT	NOT NULL
	prefferedSittingLocation	Preffered sitting location	VARCHAR2 (50)	NOT NULL
	hostingPrice	Price charge for hosting	NUMBER	NULL
	homeSittingPrice	Price charge for sitting price	NUMBER	NULL
	residentialStatus	Status of the status	VARCHAR2(25)	NOT NULL DEFAULT:GOOD
SitterPets	typeID	ID of the type of pet as per pet types	NUMBER	NOT NULL	PK, FK ON UPDATE CASCADE, ON DELETE RESTRICT
	sitterID	ID of the sitter	NUMBER	NOT NULL	PK, FK ON UPDATE CASCADE, ON DELETE RESTRICT
Owner	ownerID	Unique ID of a pet owner	NUMBER	NOT NULL UNIQUE	PK
	name	Name of the pet owner	VARCHAR2(50)	NOT NULL
	suburb	Suburb of the pet owner	VARCHAR2(50)	NOT NULL
	email	Email of the pet owner	VARCHAR2(50)	NOT NULL
	phoneNO	phoneNO of the owner	VARCHAR2(25)	NOT NULL
Booking	bookingID	Unique ID identifying a booking	NUMBER	NOT NULL UNIQUE	PK
	requestID	Foreign key identifying request that was made for the booking	NUMBER	NOT NULL	FK ON UPDATE CASCADE, ON DELETE RESTRICT
	sitterID	Foreign key of the sitter	NUMBER	NOT NULL	PK ON UPDATE CASCADE, ON DELETE CASCADE
	details	Details agreed upon by sitter and owner	TEXT	NOT NULL
	agreedPrice	Price agreed upon by sitter and owner	NUMBER	NOT NULL
	rating	Rating given to sitter after the sitting is done	NUMBER	NULL DEFAULT NULL
	date	Date of the booking	DATE	NOT NULL
BookRequest	requestID	Unique ID of the request	NUMBER	NOT NULL UNIQUE	PK
	ownerID	Forein key identifying owner who made the request	NUMBER	NOT NULL	FK ON UPDATE CASCADE, ON DELETE RESTRICT
	typeOfSitter	Type of sitter required	VARCHAR2(50)
	specialrequirements	Any special requirments	TEXT
BookRequestDates	requestID	requestID identifying the request	NUMBER	NOT NULL	PK,FK ON UPDATE CASCADE, ON DELETE CASCADE
	date	Specific date for the sitting	DATE	NOT NULL
BookRequestPets	petID	Unique ID identifying the pet	NUMBER	NOT NULL UNIQUE	PK
	requestID	foreign key identifying the request	NUMBER	NOT NULL	FK ON UPDATE CASCADE, ON DELETE RESTRICT
sitteravailability	sitterID	Foreign key identifying the sitter	NUMBER	NOT NULL	PK,FK ON UPDATE CASCADE, ON DELETE CASCADE
	Month	Month sitter is available	NUMBER(2)	NOT NULL
	petTypeID	Foreign key identifying the type of pet	NUMBER	NOT NULL	FK ON UPDATE CASCADE, ON DELETE RESTRICT
	furtherDescription	Additional description about the pets for that request	TEXT	NOT NULL
petTypes	petTypeID	Unique ID identifying the pet type	NUMBER	NOT NULL UNIQUE	PK
	type	Type of the pet	VARCHAR2(50)	NOT NULL
	description	Details about the type	TEXT	NOT NULL

Sitter (sitterID, name, suburb, email, phoneNO, profileDescription, prefferedSittingLocation, hostingPrice, homeSittingPrice, residentialStatus)

sitterPets (typeID, sitterID) foreign key (typeID) references petTypes (petTypeID), foreign key (sitterID) references sitter (sitterID)

petTypes (petTypeID, type, description)

Owner (ownerID, name, suburb, email, phoneNO)

Booking (BookingID, requestID, sitterID, details, agreedPrice, rating) Foreign key (requestID) references bookRequest (requestID)

BookRequest (requestID, ownerID, typeOfSitter, specialRequirements) Foreign key (ownerID) owner (ownerID)

BookRequestDates (requestiD, date) Foreign key (requestID) references bookRequest (requestID)

BookRequestPets (petID, requestID, petTypeID, furtherDescription) Foreign key (requestID) references bookRequest (requestID) , foreign key (typeID) references petTypes (petTypeID) 

CREATE TABLE IF NOT EXISTS `booking` (

  `bookingID` int(11) NOT NULL,

  `requestID` int(11) NOT NULL,

  `sitterID` int(11) NOT NULL,

  `details` text NOT NULL,

  `agreedPrice` int(11) NOT NULL,

  `rating` int(11) NOT NULL,

  `date` date NOT NULL,

  `comments` text NOT NULL

); 

CREATE TABLE IF NOT EXISTS `bookrequest` (

  `requestID` int(11) NOT NULL,

  `ownerID` int(11) NOT NULL,

  `typeOfSitter` varchar(50) NOT NULL,

  `specialRequirements` text NOT NULL

); 

CREATE TABLE IF NOT EXISTS `bookrequestpets` (

  `petID` int(11) NOT NULL,

  `requestID` int(11) NOT NULL,

  `petTypeID` int(11) NOT NULL,

  `furtherDescription` text NOT NULL

);

CREATE TABLE IF NOT EXISTS `book_request_dates` (

  `requestID` int(11) NOT NULL,

  `date` date NOT NULL

);

CREATE TABLE IF NOT EXISTS `owner` (

`ownerID` int(11) NOT NULL,

  `name` varchar(50) NOT NULL,

  `suburb` varchar(50) NOT NULL,

  `email` varchar(50) NOT NULL,

  `phoneNO` varchar(25) NOT NULL

);

CREATE TABLE IF NOT EXISTS `pettype` (

  `petTypeID` int(11) NOT NULL,

  `type` varchar(50) NOT NULL,

  `description` text NOT NULL

3NF relational schema

);

CREATE TABLE IF NOT EXISTS `sitter` (

`sitterID` int(11) NOT NULL,

  `name` varchar(50) NOT NULL,

  `suburb` varchar(50) NOT NULL,

  `email` varchar(100) NOT NULL,

  `phoneNO` varchar(25) NOT NULL,

  `profileDescription` text NOT NULL,

  `prefferedSittingLocation` varchar(50) NOT NULL,

  `hostingPrice` int(11) DEFAULT NULL,

  `homeSittingPrice` int(11) DEFAULT NULL,

  `residentialStatus` varchar(25) NOT NULL DEFAULT ‘GOOD’

);

CREATE TABLE IF NOT EXISTS `sitteravailability` (

  `sitterID` int(11) NOT NULL,

  `month` varchar(25) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `sitterpets` (

  `typeID` int(11) NOT NULL,

  `sitterID` int(11) NOT NULL

);

ALTER TABLE `booking`

 ADD PRIMARY KEY (`bookingID`), ADD KEY `requestID` (`requestID`,`sitterID`), ADD KEY `sitterID` (`sitterID`);

ALTER TABLE `bookrequest`

 ADD PRIMARY KEY (`requestID`), ADD KEY `ownerID` (`ownerID`);

ALTER TABLE `bookrequestpets`

 ADD PRIMARY KEY (`petID`), ADD KEY `requestID` (`requestID`,`petTypeID`), ADD KEY `petTypeID` (`petTypeID`);

ALTER TABLE `book_request_dates`

 ADD PRIMARY KEY (`requestID`);

ALTER TABLE `owner`

 ADD PRIMARY KEY (`ownerID`); 

ALTER TABLE `pettype`

 ADD PRIMARY KEY (`petTypeID`);

 ALTER TABLE `sitter`

 ADD PRIMARY KEY (`sitterID`); 

ALTER TABLE `sitteravailability`

 ADD PRIMARY KEY (`sitterID`);

 ALTER TABLE `sitterpets`

 ADD PRIMARY KEY (`typeID`,`sitterID`), ADD KEY `sitterID` (`sitterID`); 

ALTER TABLE `owner`

MODIFY `ownerID` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `sitter`

MODIFY `sitterID` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `booking`

ADD CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`requestID`) REFERENCES `bookrequest` (`requestID`) ON UPDATE CASCADE,

ADD CONSTRAINT `booking_ibfk_2` FOREIGN KEY (`sitterID`) REFERENCES `sitter` (`sitterID`) ON UPDATE CASCADE; 

ALTER TABLE `bookrequest`

ADD CONSTRAINT `bookrequest_ibfk_1` FOREIGN KEY (`ownerID`) REFERENCES `owner` (`ownerID`) ON UPDATE CASCADE; 

ALTER TABLE `bookrequestpets`

ADD CONSTRAINT `bookrequestpets_ibfk_1` FOREIGN KEY (`petTypeID`) REFERENCES `pettype` (`petTypeID`) ON UPDATE CASCADE; 

ALTER TABLE `book_request_dates`

ADD CONSTRAINT `book_request_dates_ibfk_1` FOREIGN KEY (`requestID`) REFERENCES `bookrequestpets` (`requestID`) ON UPDATE CASCADE; 

ALTER TABLE `sitteravailability`

ADD CONSTRAINT `sitteravailability_ibfk_1` FOREIGN KEY (`sitterID`) REFERENCES `sitter` (`sitterID`) ON DELETE CASCADE ON UPDATE CASCADE; 

ALTER TABLE `sitterpets`

ADD CONSTRAINT `sitterpets_ibfk_1` FOREIGN KEY (`sitterID`) REFERENCES `sitter` (`sitterID`) ON UPDATE CASCADE,

ADD CONSTRAINT `sitterpets_ibfk_2` FOREIGN KEY (`typeID`) REFERENCES `pettype` (`petTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE VIEW_A AS

SELECT SITTERS.* FROM SITTERS

INNER JOIN SITTERPETS ON SITTERS.SITTERID=SITTERPETS.SITTERID

Inner join pettype on pettype.pettypeID=sitterpets.typeID

WHERE PREFERREDSITTINGLOCATION=’own’s home’ AND petType.type=’large dogs’; 

CREATE VIEW_B AS

SELECT SITTERS.* FROM SITTERS

INNER JOIN SITTERPETS ON SITTERS.SITTERID=SITTERPETS.SITTERID

Inner join pettype on pettype.pettypeID=sitterpets.typeID

Inner join sitteravailability on sitteravailability.sitterID=sitter.sitterID

Where sitter.month=’March’ and pettype.type=’cat’;

Create view_C as

Select sitter.name, avg(booking.rating)

Sitter inner join booking on booking.sitterID=sitter.sitterID

WHERE avg(booking.rating)>=4

Group by sitter.sitterID; 

Create view_D as

Select pettype.type, count(sitterpets.sitterID)

From sitterpets

Inner join pettype on pettyype.petTypeID=sitterpets.typeID

Group by pettype.petTypeID;

Create view_E as

Select avg(agreedPrice)

From booking;

Create view_f as

Select owner.name,booking.*,petTypes.type from owner

Inner join bookrequest on bookrequest,ownerID=owner.ownerID

Inner join booking on booking.requestID=bookRequest.requestID

Inner join bookrequestpets on bookrequestpets.requestID=bookRequest.requestID

Inner join pettype on pettype.pettypeUD=bookrequestpets.pettypeID

Where owner.ownerID=1

Order by booking.date desc;

Create view_g as

Select sitter.name,sitter.profiledescription from sitter

INNER JOIN SITTERPETS ON SITTERS.SITTERID=SITTERPETS.SITTERID

Inner join pettype on pettype.pettypeID=sitterpets.typeID

WHERE suburb=’Murdoch’ AND petType.type=’dogs’; 

Create view_g as

Select sitter.name,sitter.profiledescription from sitter

INNER JOIN SITTERPETS ON SITTERS.SITTERID=SITTERPETS.SITTERID

Inner join pettype on pettype.pettypeID=sitterpets.typeID

WHERE suburb=’Murdoch’ AND petType.type=’large dogs’;

Create view_I as

Select booking.comments

From booking

Inner join sitter.sitterID=booking.sitterID

Where sitter.sitterID=’1’;

Create view_j as

Select (sum.agreedPrice) from booking

Where date BETWEEN ’2018-01-01’ AND NOW()

Group by details;