Question

Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the All You Need Are Toys Library database. Your ERD should be able to be implemented in a relational DBMS.

Toy libraries operate in a manner similar to book libraries, with members able to borrow a toy for a number of weeks then return it. As with book libraries, toy libraries enable families to have access to a wider range of toys than they might otherwise have, and to avoid spending money on toys that might be outgrown quickly or never played with.

Chester Merrythought has always remained a child at heart, so when she inherited a house in
Fremantle she decided to use it to house a toy library. She began with her own old toys and those donated by some like-minded friends, and others sourced from op shops and garage sales. She has advertised her venture as the All You Need Are Toys Library, and it has grown rapidly.

The house is small but has two rooms on the ground floor and three more on the first floor, up a flight of stairs. (There is also an attic room, which is not used at present.) Chester stores the toys in the four largest rooms, and uses the front room on the ground floor as her office where people can do the actual borrowing and returning of toys.

Each of the rooms houses a different category (or categories) of toy: games and puzzles; softies and dolls; arts and crafts; construction; and sports and activities. “Sports and activities” tends to include large heavy items such as basketball hoops and swings, and so this category is kept in one of the ground floor rooms, while more portable items such as jigsaws can be stored upstairs. Chester has fitted the rooms out with shelving, which is numbered so that an individual toy can easily be located according to shelf within a room (e.g. room 1, shelf 12).

Each toy in the library is described thoroughly. As well as the broad category (already mentioned), each toy is given a unique identifier, a descriptive name, a longer description, the age range it is suitable for; whether it needs batteries (and how many of which size); the material it is made of, and its general condition. Chester also keeps a record of who the toy was donated by.

It is free to join the library, and there is no age limit, but all members need to register before they can borrow any toys. Their name, address, contact phone number and email are recorded and they are given a membership number. Each ordinary member is able to borrow a total of 3 toys at a time, for a maximum period of 2 weeks. A member who has also donated a toy or toys to the library can borrow up to 4 toys at a time.

Chester also has plans to use the library to hold themed events (in nearby locations such as parks). Her idea is that members would bring appropriate toys or wear costumes for themes such as Harry Potter, Hallowe’en, or a teddy bears’ picnic. Members would sign up for the event (there would be a maximum number of attendees for an event) and could also borrow a toy just for the event.

Chester began by using a combination of paper records and an Excel spreadsheet to keep track of things, but has very quickly realised that there was a great demand for the toy library, and that she needs to put her recordkeeping on a more formal basis. The specific areas she needs first are registering members and donors, creating an inventory of the toys, and keeping a record of what ison loan. She also wants to keep a record of the themed events: as well as when, where, and the theme, who attended and which toys were borrowed for the event.

Below are several queries and reports that the All You Need Are Toys Library database must be able to support. There may well be many others as Chester analyses her venture and plans for the future; therefore, you should design for flexibility as well as ensuring your ERD could
answer these questions.
1. All the toys in the library: their name, description, age group, category and location.
2. All the toys (ID and name) located in room 4, shelf 17.
3. The number of different toys in each broad category.
4. All the toys that don’t need batteries.
5. The category of toy that is most frequently returned late.
6. All toys currently on loan.
7. All members registered with All You Need Are Toys who haven’t borrowed any toys yet.
8. All the toys borrowed by member Lily Pond so far.
9. A list of members who have signed up for the Teddy Bears Picnic on 15 September 2019.
10. A list of all the toys that were borrowed for the Star Wars event held in July 2019.

What you have to do:

-- Use the case study description and querying requirements to create an entity-relationship
diagram (ERD) for the All You Need Are Toys Library 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

Answer 1