Database Design in Oracle for Pet Net Online Library

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,Dateforservice,S
pecialrequirements) (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 -

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

6DATABASE DESIGN IN ORACLE
Part 3: Implementation.