Revised Pet Net Database Essay Schema With Data Dictionary And Implementation.

The assignment addresses the following learning outcomes for the unit:
1. Demonstrate practical skills in using SQL
2. Demonstrate practical skills in normalisation and convert a conceptual database design to a logical design in 3NF
3. Create a database from a given design using a DBMS and implement specified constraints using appropriate tools and approaches
4. Explain and implement security as it applies in the database environment.
 

In the above pet net ERD there are few changes made where the services description field is added in the booking table ,this to make it to identify the specific service that are offered by participant who will also be taking care of the dogs and other types of pets. 

PETSITTER(Sitterid,Fullnames,Surburb,Emailaddress,Phonenumber,Profile,Serviceprovided,

Animals,Prices)

PETOWNER(Ownerid,Fullnames,Surburb,Emailaddress,Phonenumber,Animals,Hostingtype,Dateforservice,Specialrequirements) (Raghu ,2015). 

BOOKING(Bookingid,Dates,Pets,Servicedetails,Price,Ownerid,Sitterid)

RATING(Ratingid,Starrating,Comment,Ownerid,Sitterid,Bookingid) (Bipin,2014).  

Table  name	Column name	Description	Data type/size	Domain	Default value	Required	Unique	Constraints	Referential  integrity rules
PETSITTER	Sitterid	Sitter details.	integer(20)	numbers	n/a	Yes	yes	Primary key	on delete, on update
	Fullnames	Full names  details	Varchar2(20)	String	n/a	Yes	No	-
	Surburb	Surburb   details	Varchar2(20)	String	n/a	Yes	No	-
	Emailaddress	Email address details	Varchar2(20)	String	n/a	Yes	No	-
	Phonenumber	Phone number  details	Varchar2(20)	String	n/a	Yes	No	-
	Profile	Profile  details	Varchar2(20)	String	n/a	Yes	No	-
	Serviceprovided	Service provided  details	Varchar2(20)	String	n/a	Yes	No	-
	Animals	Animals  details	Varchar2(20)	String	n/a	Yes	No	-
	Prices	Prices details	Varchar2(20)	String	n/a	Yes	No	-
PETOWNER	Ownerid	Owner id  details	Integer(20)	number	n/a	Yes	yes	Primary key	on delete, on update
	Fullnames	Full names  details	Varchar2(20)	String	n/a	Yes	No	-
	Surburb	Surburb  details	Varchar2(20)	String	n/a	Yes	No	-
	Emailaddress	Email address details	Varchar2(20)	String	n/a	Yes	No	-
	Phonenumber	Phone number details	Varchar2(20)	String	n/a	Yes	No	-
	Animals	Animals  details	Varchar2(20)	String	n/a	Yes	No	-
	Hostingtype	Hosting type details	Varchar2(20)	String	n/a	Yes	No	-
	Dateforservice	Date for service  details	Varchar2(20)	String	n/a	Yes	No	-
	Specialrequirements	Special requirements  details	Varchar2(20)	String	n/a	Yes	No	-
BOOKING	Bookingid	Booking id  details	Integer(20)	number	n/a	Yes	yes	Primary key	on delete, on update
	Dates	Dates  details	Varchar2(20)	String	n/a	Yes	No	-
	Pets	Pets  details	Varchar2(20)	String	n/a	Yes	No	-
	Servicedetails	Service details  details	Varchar2(20)	String	n/a	Yes	No	-
	Price	Price  details	Varchar2(20)	String	n/a	Yes	No	-
	Ownerid	Owner id  details	Integer(20)	String	n/a	Yes	No	Foreign key	on delete, on update
	Sitterid	Sitter id  details	Integer(20)	String	n/a	Yes	No	Foreign key	on delete, on update
RATING	Ratingid	Rating id  details	Integer(20)	number	n/a	Yes	yes	Primary key	on delete, on update
	Starrating	Star rating details	Varchar2(20)	String	n/a	Yes	No	-
	Comment	Comment  details	Varchar2(20)	String	n/a	Yes	No	-
	Ownerid	Owner id details	Integer(20)	String	n/a	Yes	No	Foreign key	on delete, on update
	Sitterid	Sitter id details	Integer(20)	String	n/a	Yes	No	Foreign key	on delete, on update
	Bookingid	Booking id details	Integer(20)	String	n/a	Yes	No	Foreign key	on delete, on update

(Abraham ,2013).  

PETSITTER  Sql code

CREATE TABLE PETSITTER

(

  SITTERID INTEGER NOT NULL

, FULLNAMES VARCHAR2(20) NOT NULL

, SURBURB VARCHAR2(20) NOT NULL

, EMAILADDRESS VARCHAR2(20) NOT NULL

, PHONENUMBER VARCHAR2(20) NOT NULL

, PROFILE VARCHAR2(20) NOT NULL

, SERVICEPROVIDED VARCHAR2(20) NOT NULL

, ANIMALS VARCHAR2(20) NOT NULL

, PRICES VARCHAR2(20) NOT NULL

, CONSTRAINT PETSITTER_PK PRIMARY KEY

  (

    SITTERID

  )

  ENABLE

);

PETOWNER Sql code

CREATE TABLE PETOWNER

(

  OWNERID INTEGER NOT NULL

, FULLNAMES VARCHAR2(20) NOT NULL

, SURBURB VARCHAR2(20) NOT NULL

, EMAILADDRESS VARCHAR2(20) NOT NULL

, PHONENUMBER VARCHAR2(20) NOT NULL

, ANIMALS VARCHAR2(100) NOT NULL

, HOSTINGTYPE VARCHAR2(100) NOT NULL

, DATEFORSERVICE VARCHAR2(20) NOT NULL

, SPECIALREQUIREMENTS VARCHAR2(100) NOT NULL

, CONSTRAINT PETOWNER_PK PRIMARY KEY

  (

    OWNERID

  )

  ENABLE

);

BOOKING Sql code

CREATE TABLE BOOKING

(

  BOOKINGID NUMBER NOT NULL

, DATES VARCHAR2(20) NOT NULL

, PETS VARCHAR2(200) NOT NULL

, SERVICEDETAILS VARCHAR2(200) NOT NULL

, PRICE VARCHAR2(20) NOT NULL

, OWNERID NUMBER NOT NULL

, SITTERID NUMBER NOT NULL

, CONSTRAINT BOOKING_PK PRIMARY KEY

  (

    BOOKINGID

  )

  ENABLE

);

ALTER TABLE BOOKING

ADD CONSTRAINT BOOKING_FK1 FOREIGN KEY

(

  OWNERID

)

REFERENCES PETOWNER

(

  OWNERID

)

ENABLE;

ALTER TABLE BOOKING

ADD CONSTRAINT BOOKING_FK2 FOREIGN KEY

(

  SITTERID

)

REFERENCES PETSITTER

(

  SITTERID

)

ENABLE;

RATING Sql code

CREATE TABLE RATING

(

  RATINGID VARCHAR2(20) NOT NULL

, STARRATING VARCHAR2(20) NOT NULL

, COMMENTS VARCHAR2(20) NOT NULL

, OWNERID NUMBER NOT NULL

, SITTERID NUMBER NOT NULL

, BOOKINGID NUMBER NOT NULL

, CONSTRAINT RATING_PK PRIMARY KEY

  (

    RATINGID

  )

  ENABLE

);

ALTER TABLE RATING

ADD CONSTRAINT RATING_FK1 FOREIGN KEY

(

  OWNERID

)

REFERENCES PETOWNER

(

  OWNERID

)

ENABLE; 

ALTER TABLE RATING

ADD CONSTRAINT RATING_FK2 FOREIGN KEY

(

  SITTERID

)

REFERENCES PETSITTER

(

  SITTERID

)

ENABLE;

ALTER TABLE RATING

ADD CONSTRAINT RATING_FK3 FOREIGN KEY

(

  BOOKINGID

)

REFERENCES BOOKING

(

  BOOKINGID

)

ENABLE;

Tables created. 

1. PETOWNER TABLE.

INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('1', 'john', 'Attwood', '[email protected]', '+617899999', 'cats,dogs,pappy', 'in-home ', 'Jan-2018', 'None')

INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('2', 'peter ', 'Broadmeadows', '[email protected]', '+6178777777', 'birds,cats,dogs,pappy', 'hosting ', 'march-2018', 'None')

Answer:

INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('3', 'stephen', 'Campbellfield', '[email protected]', '+61781111111', 'doves,cats,dogs,pappy', 'hosting ', 'Feb-2018', 'None')

INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('4', 'simon', 'Coolaroo', '[email protected]', '+617822222222', 'birds,dogs,pappy', 'hosting ', 'Feb-2018', 'None')

INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('5', 'Gideon', 'Craigieburn', '[email protected]', '+617888999999', 'cats,kitten,pappy', 'in-home', 'Jan-2018', 'None')

INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('6', 'samuel', 'Dallas ', '[email protected]', '+617855555555', 'birds,dogs,kitten', 'hosting ', 'march-2018', 'None')

INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('7', 'elijah', 'Greenvale', '[email protected]', '+617833333333', 'kitten,dogs,pappy', 'hosting ', 'march-2018', 'None')

INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('8', 'ezra', 'Jacana ', '[email protected]', '+6178789999', 'cats,dogs', 'in-home ', 'march-2018', 'None')

2. PETSITTER TABLE

INSERT INTO "V33275833"."PETSITTER" (SITTERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, PROFILE, SERVICEPROVIDED, ANIMALS, PRICES) VALUES ('1', 'PATRICK', 'Attwood', '[email protected]', '6178999777', 'we are animals caretakers we take care of dogs,cats,rabbits,rats,mice,fish,birds', '"in-home,hosting ,dog walking, dog grooming and dog training.', 'dogs,cats,rabbits,rats,mice,fish,birds', '210')

INSERT INTO "V33275833"."PETSITTER" (SITTERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, PROFILE, SERVICEPROVIDED, ANIMALS, PRICES) VALUES ('2', 'JONATHAN', 'Broadmeadows', '[email protected]', '6178777788', 'we are animals caretakers we take care of dogs,cats,rats,mice,birds  ', '"in-home,hosting ,dog walking, dog

grooming and dog training."

', 'dogs,cats,rats,mice,birds', '170')

INSERT INTO "V33275833"."PETSITTER" (SITTERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, PROFILE, SERVICEPROVIDED, ANIMALS, PRICES) VALUES ('3', 'JACOB', 'Campbellfield', '[email protected]', '61781111333', 'we are animals caretakers we take care of dogs,rabbits,mice,fish', '"in-home,hosting ,dog walking, dog

grooming and dog training."

', 'dogs,rabbits,mice,fish', '200')

INSERT INTO "V33275833"."PETSITTER" (SITTERID, FULLNAMES, SURBURB, EMAILADDRESS, PHONENUMBER, PROFILE, SERVICEPROVIDED, ANIMALS, PRICES) VALUES ('4', 'ISAIH', 'Coolaroo', '[email protected]', '6.17822E+11', 'we are animals caretakers we take care of dogs,cats,rats,mice,fish,birds', '"in-home,hosting ,dog walking, dog

grooming and dog training."

', 'dogs,cats,rats,mice,fish,birds', '180')

Commit Successful 

3. BOOKING TABLE

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('2', 'Jan-18', 'dogs,cats,rabbits,rats,mice,fish,birds', ‘we take care of dogs,cats,rabbits,rats,mice,fish,birds’, '200', '2', '2') 

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('3', 'Mar-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '190', '3', '3')

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('4', 'Mar-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '200', '4', '4')

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('5', 'Mar-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '180', '5', '2')

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('6', 'Feb-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '180', '6', '3')

Revised Pet Net Database Relational Schema.

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('7', 'Feb-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '180', '7', '4')

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('8', 'Mar-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '120', '8', '1')

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('9', 'Jan-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '130', '1', '2')

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('10', 'Mar-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '120', '3', '3')

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('11', 'Feb-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '180', '2', '4')

INSERT INTO "V33275833"."BOOKING" (BOOKINGID, DATES, PETS, SERVICEDETAILS, PRICE, OWNERID, SITTERID) VALUES ('12', 'Mar-18', 'dogs,cats,rabbits,rats,mice,fish,birds', 'we take care of dogs,cats,rabbits,rats,mice,fish,birds', '180', '4', '1')

Commit Successful 

4. RATING TABLE 

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('1', '4', 'very good service', '2', '2', '1')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('2', '5', 'best service', '6', '3', '2')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('3', '4', 'very good service', '7', '4', '3')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('4', '3', 'good service', '8', '1', '4')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('5', '5', 'best service', '1', '2', '5')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('6', '5', 'best service', '3', '3', '6')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('7', '5', 'best service', '2', '4', '7')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('8', '5', 'best service', '4', '1', '8')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('9', '4', 'very good service', '1', '1', '9')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('10', '3', 'good service', '3', '3', '10')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('11', '5', 'best service', '4', '4', '11')

INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID, BOOKINGID) VALUES ('12', '4', 'good service', '5', '2', '12')

Commit Successful 

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> GRANT SELECT,DELETE,INSERT,UPDATE ON V33275833.booking to MARKERTL; 

Grant succeeded. 

SQL> GRANT SELECT ,INSERT,DELETE,UPDATE ON V33275833.petowner to MARKERTL; 

Grant succeeded. 

SQL> GRANT SELECT ,INSERT,DELETE,UPDATE ON V33257833.PETSITTER TO MARKERTL; 

SQL> GRANT SELECT,INSERT,DELETE,UPDATE ON V33275833.petsitter to MARKERTL;

Grant succeeded. 

Part 2: Data dictionary.

SQL> GRANT SELECT,INSERT,DELETE,UPDATE ON V33275833.rating to MARKERTL; 

Grant succeeded. 

SQL>

VIEW A All the pet sitters who can look after large dogs in the dog’s own home. 

Sql codes.

SELECT SITTERID,FULLNAMES,SURBURB,EMAILADDRESS,PHONENUMBER FROM V33275833.PETSITTER WHERE ANIMALS LIKE 'large dogs%' AND SERVICEPROVIDED LIKE 'in-home%'

Results. 

VIEW B Pet sitters who are available for cats during March 2018. 

Sql codes

SELECT DISTINCT P.SITTERID,P.FULLNAMES,P.SURBURB,P.EMAILADDRESS,P.PHONENUMBER

FROM PETSITTER P JOIN BOOKING b ON P.SITTERID=b.SITTERID WHERE b.DATES LIKE 'Mar-18%'

Out put 

VIEW C All sitters whose average service has been rated as four stars or over. 

Out put

VIEW D The number of pet sitters registered in the database for each type of animal.

Sql codes

select

DISTINCT (select count(ANIMALS) from PETSITTER where ANIMALS LIKE 'large dogs%') as large_dogs,

(select count(ANIMALS) from PETSITTER where ANIMALS LIKE 'small dogs%') as small_dogs,

(select count(ANIMALS) from PETSITTER where ANIMALS LIKE 'medium dogs%') as medium_dogs,

(select count(ANIMALS) from PETSITTER where ANIMALS LIKE 'cats%') as cats,

(select count(ANIMALS) from PETSITTER where ANIMALS LIKE 'rabbits%') as rabbits,

(select count(ANIMALS) from PETSITTER where ANIMALS LIKE 'rats%') as rats,

(select count(ANIMALS) from PETSITTER where ANIMALS LIKE 'mice%') as mice,

(select count(ANIMALS) from PETSITTER where ANIMALS LIKE 'fish%') as fish,

(select count(ANIMALS) from PETSITTER where ANIMALS LIKE 'birds%') as birds

from PETSITTER

out put 

VIEW E The average price charged by pet sitters. 

Sql codes

SELECT AVG(PRICES) AS "AVARAGE PRICES BY PET SITTERS" FROM PETSITTER

Out put 

VIEW F Details of all of the bookings for a particular owner and their pet(s), in descending order of date (oldest first) 

Sql codes

SELECT b.BOOKINGID, b.DATES, b.PETS, b.SERVICEDETAILS, b.PRICE, o .FULLNAMES FROM BOOKING b JOIN PETOWNER o ON b.OWNERID=o.OWNERID order by b.DATES DESC

Out put

VIEW G All of the dog walkers in a particular suburb, and the services they offer. 

Sql codes

SELECT FULLNAMES, SURBURB,SERVICEPROVIDED FROM PETSITTER WHERE SERVICEPROVIDED LIKE 'dog walking%'

Out put

VIEW H Details of all of the services offered for large dogs in a particular suburb. 

Sql codes

SELECT SURBURB,SERVICEPROVIDED FROM PETSITTER WHERE ANIMALS LIKE 'large dogs%'

Out put 

VIEW I All comments about a particular sitter.

Sql codes

SELECT r.COMMENTS,o.FULLNAMES FROM RATING r JOIN PETOWNER o ON r.OWNERID=o.OWNERID

Out put 

 VIEW J Total amount spent on each dog service (boarding, grooming, training and walking) in 2018 (so far) 

Sql codes

SELECT SUM(PRICE) AS "Total amount spent on each dog service" FROM BOOKING WHERE PETS LIKE 'dogs%'

Out put  

References.

Abraham ,S.(2013). Database System Concepts .Michigan: McGraw-Hill.

Bipin,D.(2014). An Introduction to Database Systems.Boston:

Addison-Wessley Publication.

Raghu ,R.(2015). Database Management Systems. New York:

McGraw-Hill Higher Education

Ullman,D.(2016). Principles of Database Systems. Berlin:Springer publishers;