Technical Database Questions
Student MK
QAQB.docxQuestion A: Conceptual Design
Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the GardenShare database. Your ERD should be able to be implemented in a relational DBMS.
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.
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 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.
What you have to do:
· Use the case study description and querying requirements to create an entity-relationship diagram (ERD) for the GardenShare 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 setting 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. However, Assignment 2 will involve creating the database from your design, so you should be satisfied that it will work with at least the queries shown.
· 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.
Question B: Relational algebra
A fitness centre maintains a database of information about the various classes that are available for members of the centre to take. Each class (Zumba, Pilates, Aqua Fit, etc) may offer several sessions per week. Each session is led by a qualified instructor at a particular day and time, and is held in one of the centre’s venues. Each session has a limited number of places available and members must sign up for a session. Basic Information about members, instructors, classes and venues are also held in the database.
The schema for this database is as follows: (note that primary keys are shown underlined, foreign keys in bold).
MEMBER (MemberID, MemberName, MemberEmail) CLASS (ClassName, Description, SuitableFor)
INSTRUCTOR (InstructorName, InstructorEmail, Specialty)
VENUE (VenueName, Capacity)
SESSION (SessionID, DayAndTime, NumberOfPlaces, ClassName, VenueName, InstructorName)
PARTICIPANT (SessionID, MemberID)
Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.
a. List the name and specialty of all instructors.
b. List the class name and description of all classes suitable for ‘Over 60s’.
c. List the names of instructors who run sessions in the classes ‘Aqua Deep’, ‘Aqua Fit’, or both.
d. List the names of all members who participated in a class held in Studio 5 on 19/9/2018, and the name of the class they participated in.
e. List the names of members who participated in any class with a venue capacity greater than 30.
f. List the details of all sessions running on 19/9/2018, and the names of the members who have signed up for each of them (if any).
g. List the names of any instructors who run sessions in both Studio 1 and Studio 2.
h. List the names of members who have either participated in classes for the Over 60s, classes held in the Warm Water Pool, or both.
i. List the names of the members who have NOT participated in a ‘Zumba’ class.
j. List the names of members who have participated in all of the different classes offered.
