Part 1: Revised ERD and schema (10 marks)
a) Create and submit the ERD for this database that you are going to use as the basis of your implementation.
b) Include a one or two paragraph explanation as to the changes you have made to the ERD on the basis of your feedback from Assignment 1 and/or as a result of having to support the new functionality and views described in this assignment.
c) Show the relational schema in 3NF that will be the basis of your implemented design. Show your relations using the following convention: RELATION_NAME (PrimaryKey, Attribute, Attribute, ... ForeignKey)
Part 2: Data dictionary (20 marks)
Create a data dictionary for your database. This should include:
a) For each table: a definition of each column (attribute), consisting of the column name, brief description of what it represents, its data type and size, domain (allowable values), any default value, whether it is required, whether unique, and any constraints (primary key, foreign key). You can follow the example in Lab 07. Use the data types available in Oracle.
b) For each of the columns that is a foreign key, give the appropriate referential integrity rules (i.e. the "on delete.... ; on update" etc actions that should apply when the corresponding primary key is altered). The appropriate action should be included whether or not there is a statement in Oracle to implement it.
c) Any business rules (enterprise constraints) that should apply to the database that haven't already been covered.
Note that your data dictionary must be consistent with your ERD and schema.
Part 3: Implementation (30 marks)
Implement the tables for the Pet Net database in Oracle SQLPlus on arion.murdoch.edu.au. Note the following:
a) All tables should be created as per your ERD and data dictionary; the marker will check your ERD against your tables. You do NOT need to include the SQL CREATE TABLE statements that you used to create the tables.
b) All entity and referential integrity constraints should be created and appropriately named.
c) All columns (attributes) should be of an appropriate data type/size and be set as required, unique or not as appropriate.
d) All domain constraints should be implemented.
e) All tables should be populated with sample data that will allow the marker to test that your database fulfils the application requirements as specified and supports the transactions and views listed below. Also provide the same sample data in your Word document. If you use a screen dump, it MUST be a size that is readable without zooming. Note you do not need to include the SQL INSERT statements that you used to add the data.
f) SELECT, UPDATE, INSERT and DELETE permissions should be GRANTED on all database objects (particularly tables and views) to the user MARKERTL. This is most important. If you do not grant this permission, the marker will not be able to mark this part of your assignment and you will not get any marks for it.
g) Please state in your documentation whether you have used your V account or H account.
Part 4: Views (40 marks)
Create VIEWS for the following in Oracle (views should be named as ViewA, ViewB etc). Note that some of these have changed from Assignment 1 in line with WHZ's new requirements.
You should also provide the CREATE VIEW statements you used to create the views in your Word document. Include sufficient attributes in your solutions that they make sense without reference to the question.
VIEW A All the pet sitters who can look after large dogs in the dog's own home.
VIEW B Pet sitters who are available for cats during March 2018.
VIEW C All sitters whose average service has been rated as four stars or over.
VIEW D The number of pet sitters registered in the database for each type of animal.
VIEW E The average price charged by pet sitters.
VIEW F Details of all of the bookings for a particular owner and their pet(s), in descending order of date (oldest first)
VIEW G All of the dog walkers in a particular suburb, and the services they offer.
VIEW H Details of all of the services offered for large dogs in a particular suburb.
VIEW I All comments about a particular sitter.
VIEW J Total amount spent on each dog service (boarding, grooming, training and walking) in 2018 (so far)
Scenario
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.
Case study
Re-read the description of the Pet Net case in Assignment 1 if you need to refresh your memory.
Elizabeth and Jeremy Trellis are pleased with your work so far and asked you to go on to implement your design. They have some additions to the original specifications that you should note:
• They would like to extend the services that participants can offer to include dog walking, dog grooming and dog training. These services would operate in the same way as the boarding, with the services being offered and bookings made between owner and service provider.
• Potential dog walkers would state whether they are available for regular walks or one-off (or both), and whether they are available for long (>5km) walks. Potential dog groomers would state what sort of cuts they can provide.
Dog trainers would describe whether they offer basic, intermediate or advanced training. In each case the dog walker/groomer/trainer states the price(s) for the service(s) they offer. Of course, a person can be any or all of pet sitter, dog walker, dog groomer or dog trainer.