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.