Question : Conceptual Design Use the case study description and list of requirements below to create an entity-relationship diagram showing the data...

Question : Conceptual Design

Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the Pet Net database. Your ERD should be able to be implemented in a relational DBMS. 

Pet Net is a new business venture by animal lovers Elizabeth and Jeremy Trellis, providing in-home pet boarding for pet owners while they are on holiday. Its motto is "Your Friend Our Friend". Pet Net's business model is about bringing together pet owners looking for a suitable sitter, with sitters who offer a particular service depending on their circumstances. The Pet Net database provides the opportunity to match the needs of the particular pet with an appropriate pet sitter. For example, a large dog that needs lots of exercise would be paired with a sitter who can take it for long walks, while a pet rat in a cage could be cared for in a small apartment. 

Pet Net aims to provide care in either the pet's own home, or hosted in the pet sitter's home, depending on what the pet owner is looking for. 

Potential pet sitters register on the Pet Net database by providing their name, suburb, email address and phone number. They write a short profile describing themselves and the services they provide that can be used for display on the website. They also answer a number of standard questions including whether they provide pet sitting at the pet's home or hosting in their own home (or both), and which animals they are prepared to look after: dogs (small, medium, large), cats, small animals (such as rabbits, rats, mice, etc), fish, or birds. Pet sitters are in competition with each other for opportunities and can set their own prices (for example, $25 per night for pet hosting; $30 per night for in-home sitting). Sitters must provide various identification documents to Pet Net and once these are verified they are assigned a Sitter ID and can commence operating.

Pet owners register on the Pet Net database by providing their name, suburb, email address and phone number, and are assigned an Owner ID. They provide details of the animal (or animals) they are seeking accommodation for, and whether they are looking for an in-home sitter, or hosting at the sitter's home. They also provide the dates they need the service for, and any special requirements to be discussed with potential sitters.

Once a pet owner has located a suitable sitter through the Pet Net database, they contact the sitter and arrange to meet. If this is successful, a booking is then arranged between sitter and owner. The booking information also includes the dates of the stay, the pet (or list of pets), details of the service, and agreed price. Once the booking is paid for (NOT handled by the Pet Net database) the booking is confirmed in the database.

After the pet stay is completed, the owner may choose to provide a star rating and comments on their experience. This feedback is used in subsequent Pet Net searches: for example, an owner may search for all the five star rated sitters in their suburb. Pet Net may also remove a sitter from the database if they have verified poor reviews. 

Obviously, a pet owner is likely to need Pet Net's boarding services on more than one occasion, and may need it for different pets each time. Similarly, a sitter's availability and services may change and they need to keep this updated in the database. 

Below are several queries and reports that the Pet Net database must be able to support. There may well be many others as Elizabeth and Jeremy analyse their business and plans for the future; therefore, you should design for flexibility as well as ensuring your ERD can answer these questions. 

1.      All the pet sitters who can look after large dogs in the dog's own home. 

2.      All the pet sitters who provide hosting services in a particular suburb.

3.      Pet sitters who are available for cats during March 2018.

4.      All sitters rated as four stars or over.

5.      All comments on bookings for a particular sitter.

6.      Sitters who can look after both dogs and cats.

7.      Any sitter registered at an address of a previously disqualified sitter.

8.      The number of pet sitters available for each type of animal.

9.      The average price charged by pet sitters. 

10.  All of the bookings for a particular owner.

What you have to do:

•      Use the case study description and querying requirements to create an entity-relationship diagram (ERD) for the Pet Net database. Your ERD should be able to be implemented in a relational DBMS. 

•      List and explain any assumptions you have made in creating the data model. 

•      You should use the crow's feet ERD notation we have been using in the lectures, and should include a legend to explain the notation. You should include attributes in the ERD, and indicate primary and foreign keys. The use of a drawing tool such as Visio will make this task easier. 

•      Whichever tool you use, you must copy and paste the ERD into a word-processed document. This is because your tutor might not have access to the tools you have used. Please make sure the labels and symbols in your ERD are readable. Enlarge them from the default if necessary.

•      Please note that hand-drawn ERDs are not acceptable.

Some important things to note:

•      You don't have to create the database or any of the reports/queries at this point. 

•      You should make any assumptions that are required, but must state them clearly. Obviously, your assumptions should not contradict any of the information already provided.