Database Management System Assignment | Homework Help Websites

Case study: 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 Jen’s 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 Jen’s 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.

Don't use plagiarized sources. Get Your Assignment on
Database Management System Assignment | Homework Help Websites
Just from $13/Page
Order Now

Below are several queries and reports that the Garden Share 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.

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 haven’t 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 Bill’s workshop on composting.

10. Members who have gone to every event held in 2018. 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 Garden Friends. She has a revised set of requirements based on feedback from the community. Astrid’s revised requirements are as follows:

 As before, all people who host or attend an event must be registered on the GardenFriends database, with the same requirements for registration information as stated previously. This includes a list of the services/skills they are prepared to offer.

 There are three categories of events: Open Gardens, where attendees are taken round a member’s garden where features of particular interest are pointed out; Skills and Techniques, where attendees learn skills such as composting or chainsaw maintenance; and Know Your Plants, which focuses on the cultivation of a particular species or group, such as roses or Banksias.

 Every event has an organiser, as well as an event leader who presents the event on the day (these may or may not be the same person). Some events also include additional helpers who assist in providing 1:1 assistance to attendees. These helpers come from the pool of registered members and each helper may volunteer to assist in many different events.

 The event information required is similar to before (i.e. title, description, date, location, and number of places available), but additional information has also proved useful and is to be included in the final system. In particular, Astrid would like to ensure the postcode of the location is collected, and any instructions to participants included (“bring your own trowel and secateurs” or “don’t forget your hat and sunscreen”. She would also like to record

whether the event meets accessibility guidelines for people in wheelchairs or with limited mobility.

 Astrid would like to open the events to the general public as well as registered GardenFriends members, in the hope that many will be inspired to join GardenFriends. Guests sign up on the website with their name, suburb, mobile phone number and email.

Part 1: Revised ERD and schema

a) Recreate and submit the ERD for this database that you are going to use as the basis of your implementation. Refer to the image above. 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 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.

Part 3: Implementation Implement the tables for the GardenFriends and 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.

Part 4: View Create VIEWS for the following in Oracle (views should be named as ViewA, ViewB etc). You should also provide the CREATE VIEW statements you used to create the views in your Word document. These should be as text, not screen dumps. 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.

Calculate the price
Make an order in advance and get the best price
Pages (550 words)
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
How it works
Receive a 100% original paper that will pass Turnitin from a top essay writing service
step 1
Upload your instructions
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
Pro service tips
How to get the most out of your experience with Homework Writing Services
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
See why 20k+ students have chosen us as their sole writing assistance provider
Check out the latest reviews and opinions submitted by real customers worldwide and make an informed decision.
Thank you for your work. Some sentences required minimal edits for clarification/explanation. In the future, please review your sentences for clarity and readability. Consider also reading out loud before submitting. We look forward to future contributions.
Customer 463469, October 21st, 2022
Business and administrative studies
Use Grammarly on all your papers.
Customer 459947, March 28th, 2022
I am pleased with my paper. The support team helping me to receive this finally was phenomenal. Thank you
Customer 453993, February 10th, 2020
Awesome! Thanks
Customer 454007, June 19th, 2020
Customer 456821, October 4th, 2022
Social Work and Human Services
All of the papers I have so far received have me extremely satisfied. The content that is asked for each task is written by the writers in great detail, and they perform a fantastic job of it. I'll keep using the service and tell other people about it. Again, many thanks.
Customer 463411, January 29th, 2023
Nice job.
Customer 452441, April 6th, 2022
Good job after revision. Make Grammarly your tool.
Customer 462769, March 29th, 2022
Business & Management
Excellent work!
Customer 453377, May 20th, 2022
Entertainment & Gaming
Good work.
Customer 452441, April 4th, 2022
Business and administrative studies
Good job.
Customer 459305, April 6th, 2022
Computer science
Excellent report.
Customer 462881, April 21st, 2022
Customer reviews in total
Current satisfaction rate
3 pages
Average paper length
Customers referred by a friend
15% OFF your first order
Use a coupon FIRST15 and enjoy expert help with any task at the most affordable price.
Claim my 15% OFF Order in Chat