Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the GardenShare database.
Everyone loves a garden, but not everyone loves gardening (or all aspects of it anyway). For the last couple of years, Astrid Winterblossom has been swapping gardening chores with her neighbours. She maintains her neighbour Bill's reticulation in return for him doing tree-lopping for her garden, and mows her other neighbour Jens lawn in return for home-grown tomatoes. Bill and Jen also have their own arrangements: Bill lends Jen his trailer in return for Jen pruning his roses. In this way everyone gets their gardening jobs done, and expertise is shared among the neighbours. Astrid is now thinking of expanding this network of swapping so that more garden enthusiasts in her local area can participate. She needs a database (which will be available from the web) to enable people to search for others offering particular services, and to indicate the services they offer in return. The only rules are that no money is to be involved, and all the skills/equipment/tasks involved must be to do with gardening. (This rule allows her to include Jens famous carrot cake.) Astrid has drafted the following requirements for the database: Gardeners who want to participate must all register as members on the GardenShare website, listing their name, street address, suburb, email and contact number. They also write a brief biography about their gardening interests. They must post at least one service they are prepared to offer before they can request a service. Services may be anything gardening related:
the main categories are maintenance jobs such as mowing, pruning, weeding, mulching, watering, planting and sweeping;
pickup and delivery services such as collecting plants from the nursery, taking clippings to the council composting centre or taking rubbish to the tip; equipment loan (such as chainsaws, lawnmowers and mulchers);
and landscaping services such as garden design or paving. Members also indicate when the service they offer is available: this could be quite specific such as "only in September-October" or more general such as any Sunday or by arrangement. Members who are looking for someone to undertake a service for them post a request to the GardenShare database indicating what they need. They include the broad category, the task name (e.g. 'pruning'), a more detailed description of the task (e.g. I have 6 overgrown citrus trees that need pruning), and an approximate date (by 15 August or as soon as possible). The database then returns a shortlist of suitable people. The requester selects someone from the list who needs a skill they themselves are offering, contacts them via email or phone, and, if both parties agree, logs the swap on the database. The members and tasks involved in the swap and the dates they are to be carried out are recorded. (It is likely that the two swapped tasks would be done on different days.) When each task is completed, the member for whom the task was done logs it as closed, and when both tasks in the swap are closed the swap is completed. Sometimes there is nobody in the database who has listed suitable skills for a posted request, but there is someone who could do the requested task. Members often scan through the database looking for open requests and approach the requester directly. A swap is arranged and the tasks logged in the usual way by the parties involved. A swap is always between only two parties, but Astrid has found that some members of the GardenShare community have skills or other services that could be offered to groups of members, such as demonstrating how to set up a worm farm, or holding open garden days. She would like to advertise these through GardenShare as well. Members can post an event to GardenShare, giving a title, description, date, location, and number of places available. Other members can then sign up for the event, up to the limit of places. No swapping is required for participating in the events.
Below are several queries and reports that the GardenShare database must be able to support. There may well be many others as Astrid and her neighbours analyse their venture and plan for the future; therefore, you should design for flexibility as well as ensuring your ERD could answer these questions.
1. All the members represented in the database, and the skills they offer.
2. The number of members offering lawn mowing.
3. The number of different services offered in each category.
4. All members wanting to hire a chainsaw in September 2018.
5. Members who have completed tasks that aren't in their recorded categories of skills offered.
6. All currently open requests.
7. All members registered with GardenShare who havent posted any requests yet.
8. All the gardening jobs logged as completed in September 2018.
9. A list of members who have signed up for Bills workshop on composting.
10. Members who have gone to every event held in 2018.
What to do:
Astrid Winterblossom is pleased with your work so far and has asked you to go on to implement your design. However, like so many clients, she wants to make some changes to the requirements. As it turns out, people were not very keen on formalising the swapping part of their activities, although they would still like to view the various skills that other members have and are prepared to help others with. The swaps themselves will probably continue informally, but will NOT be recorded as part of the database at this stage. However, the event advertising part of Astrid's garden community has proved to be very popular, and she would like you to develop this part as a priority, with the resultant system renamed GardenFriends. She has a revised set of requirements based on feedback from the community. Astrids revised requirements are as follows:
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 having to support the new functionality and views described
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)
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). 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 by cardinality or participation constraints. Note that your data dictionary must be consistent with your ERD and schema.
Implement the tables for the GardenFriends database in Oracle
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.
Create VIEWS for the following in Oracle (views should be named as ViewA, ViewB etc.
A. All the members represented in the database, and details of the skills they offer, listed in alphabetical order of last name.
B. A list of all the equipment available for loan, the contact details of the member who is loaning the equipment, and any specific information about the conditions of loan.
C. A list of attendees (name, phone number, and whether they are registered members or guests) who have signed up for Fran Forklift's workshop on 'Chainsaw use and maintenance.
D. All the events (title, date) that were more popular (i.e. higher attendance) with guests than with registered members.
E. All the upcoming events (title, date, location) in a given postcode (e.g. 6150) for the next month (i.e. the whole of the next calendar month).
F. Details (title, description, date, location, leader name, instructions) of all the events that mention roses.
G. Names of any members who have organised, led or helped in more events than they have attended as participants. The result table should also show the number of events.
H. All the events (title, date, leader) that meet accessibility guidelines.
I. A list of guests who attended (or are signed up to attend) events that are not in their home suburb. The result table should also show their home suburb and the event suburb.
J. The number of unfilled places in each category of event that has been held so far.
NOTES: