[Solution] ICT285 Databases Case Studies, database design & implementation

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.

Part 1: Revised ERD and schema

1. Create and submit the ERD for this database that you are going to use as the basis of your implementation.
2. 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.
3. 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

Create a data dictionary for your database. This should include:

1. 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.
2. 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.
3. 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

Implement the tables for the Pet Net database in Oracle SQLPlus on arion.murdoch.edu.au. Note the
following:

1. 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.
2. All entity and referential integrity constraints should be created and appropriately named.
3. All columns (attributes) should be of an appropriate data type/size and be set as required, unique or not as appropriate.
4. All domain constraints should be implemented.
5. 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.
6. 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.
7. Please state in your documentation whether you have used your V account or H account.

Part 4: Views

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)

Get Project Solution by contacting us

- via WhatsApp: +92-324-7042178

- via email: codelogixstudio@gmail.com