ICT285 - Pet Net Database: Design and Implementation in Oracle

This project outlines the design and implementation of the Pet Net database using Oracle. It includes a revised Entity Relationship Diagram (ERD) and relational schema, incorporating a service description field in the booking table. The document provides a detailed data dictionary, specifying column names, descriptions, data types, domains, default values, and constraints for the PETSITTER, PETOWNER, BOOKING, and RATING tables. The implementation section presents the SQL code for creating these tables, along with sample data insertion statements. Permissions are granted to a user (MARKERTL) for data manipulation. Finally, the project showcases the creation of views to extract specific information, such as pet sitters who care for large dogs in their own homes, sitters available for cats in March 2018, and sitters with high average service ratings. This comprehensive documentation serves as a valuable resource for understanding database design principles and practical Oracle implementation.

Running head: DATABASE DESIGN IN ORACLE 1
Database Design In Oracle
Designing Pet Net Oracle Database System.
[Student Name]
[University Name]

2DATABASE DESIGN IN ORACLE
Table of Contents
Revised ERD and schema.............................................................................................................................3
Revised Pet Net Database Entity Relation Diagram.................................................................................3
Revised Pet Net Database Relational Schema.........................................................................................4
Part 2: Data dictionary.................................................................................................................................4
Part 3: Implementation...............................................................................................................................6
Sample data.......................................................................................................................................13
GRANTING PERMISSION TO MARKERTL.............................................................................................17
Part 4: Views..............................................................................................................................................19
References.................................................................................................................................................30

3DATABASE DESIGN IN ORACLE
Revised ERD and schema.
Revised Pet Net Database Entity Relation Diagram.
(Ullman,2016).
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.

4DATABASE DESIGN IN ORACLE
Revised Pet Net Database Relational Schema.
PETSITTER(Sitterid,Fullnames,Surburb,Emailaddress,Phonenumber,Profile,Serviceprovided,
Animals,Prices)
PETOWNER(Ownerid,Fullnames,Surburb,Emailaddress,Phonenumber,Animals,Hostingtype,Da
teforservice,Specialrequirements) (Raghu ,2015).
BOOKING(Bookingid,Dates,Pets,Servicedetails,Price,Ownerid,Sitterid)
RATING(Ratingid,Starrating,Comment,Ownerid,Sitterid,Bookingid) (Bipin,2014).
Part 2: Data dictionary.
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 Varchar2(20) String n/a Yes No -

5DATABASE DESIGN IN ORACLE
details
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).

6DATABASE DESIGN IN ORACLE
Part 3: Implementation.

7DATABASE DESIGN IN ORACLE
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

8DATABASE DESIGN IN ORACLE
, 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
)

9DATABASE DESIGN IN ORACLE
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
(

10DATABASE DESIGN IN ORACLE
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

11DATABASE DESIGN IN ORACLE
)
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;

12DATABASE DESIGN IN ORACLE
ALTER TABLE RATING
ADD CONSTRAINT RATING_FK3 FOREIGN KEY
(
BOOKINGID
)
REFERENCES BOOKING
(
BOOKINGID
)
ENABLE;
Tables created.

13DATABASE DESIGN IN ORACLE
Sample data.
1. PETOWNER TABLE.
INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS,
PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('1',
'john', 'Attwood', '[e-mail]', '[phone]', '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', '[e-mail]', '[phone]', 'birds,cats,dogs,pappy', 'hosting ', 'march-
2018', 'None')
INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS,
PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('3',
'stephen', 'Campbellfield', '[e-mail]', '[phone]', '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', '[e-mail]', '[phone]', 'birds,dogs,pappy', 'hosting ', 'Feb-2018',
'None')
INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS,
PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('5',
'Gideon', 'Craigieburn', '[e-mail]', '[phone]', 'cats,kitten,pappy', 'in-home', 'Jan-2018',
'None')
INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS,
PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('6',

14DATABASE DESIGN IN ORACLE
'samuel', 'Dallas ', '[e-mail]', '[phone]', 'birds,dogs,kitten', 'hosting ', 'march-2018',
'None')
INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS,
PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('7',
'elijah', 'Greenvale', '[e-mail]', '[phone]', 'kitten,dogs,pappy', 'hosting ', 'march-2018',
'None')
INSERT INTO "V33275833"."PETOWNER" (OWNERID, FULLNAMES, SURBURB, EMAILADDRESS,
PHONENUMBER, ANIMALS, HOSTINGTYPE, DATEFORSERVICE, SPECIALREQUIREMENTS) VALUES ('8',
'ezra', 'Jacana ', '[e-mail]', '[phone]', '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',
'[e-mail]', '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', '[e-mail]', '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',
'[e-mail]', '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',
'[e-mail]', '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')

15DATABASE DESIGN IN ORACLE
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')
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')

16DATABASE DESIGN IN ORACLE
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')

17DATABASE DESIGN IN ORACLE
INSERT INTO "V33275833"."RATING" (RATINGID, STARRATING, COMMENTS, OWNERID, SITTERID,
BOOKINGID) VALUES ('12', '4', 'good service', '5', '2', '12')
Commit Successful
GRANTING PERMISSION TO MARKERTL
login as: 33275833
[e-mail]'s password:
Last login: Sun Apr 8 11:04:09 2018 from 105.49.48.65
arion.murdoch.edu.au>sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 8 11:09:43 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: V33275833
Enter password:

18DATABASE DESIGN IN ORACLE
Last Successful login time: Sun Apr 08 2018 03:48:49 +08:00
Connected to:
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.
SQL> GRANT SELECT,INSERT,DELETE,UPDATE ON V33275833.rating to MARKERTL;
Grant succeeded.
SQL>

19DATABASE DESIGN IN ORACLE
Part 4: Views
VIEW A All the pet sitters who can look after large dogs in the dog’s own home.

20DATABASE DESIGN IN ORACLE
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

21DATABASE DESIGN IN ORACLE
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

22DATABASE DESIGN IN ORACLE
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

23DATABASE DESIGN IN ORACLE
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

24DATABASE DESIGN IN ORACLE
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

25DATABASE DESIGN IN ORACLE
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

26DATABASE DESIGN IN ORACLE
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

27DATABASE DESIGN IN ORACLE
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

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

29DATABASE DESIGN IN ORACLE
Sql codes
SELECT SUM(PRICE) AS "Total amount spent on each dog service" FROM BOOKING WHERE PETS LIKE
'dogs%'
Out put

30DATABASE DESIGN IN ORACLE
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;