ICT581 Assignment 2: Information Systems Principles and Practice Question Sem 1 2025

ICT581 Question 1: Normalisation

The following question is based upon the BIRD relation below, which lists information about the birds of the USA1 . You can assume the data is representative. Note that scientific names are generally used worldwide for plants and animals to eliminate ambiguity, as common names can vary between countries.

You have been asked to design a relational database based on this design. You know that there are problems with the current design and that it will need to be modified in order to work effectively.

Answer the following questions. Each question is worth 5 marks. Make sure you answer all parts of each question.

1. What is the candidate key (or keys) of the relation, as it currently exists? What normal form is the relation in? Explain your reasoning.
2. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.
3. Convert the relation to a set of relations in at least Third Normal Form (3NF). You only need to show the schema, not the data. Do not create any new attributes: work with the ones in the table. Give each of your new relations an appropriate name. Show all primary keys and foreign keys.
4. Explain how your new design addresses the problems you identified in (b). Also demonstrate that your set of relations has the dependency-preserving and lossless join properties.

Are You Looking for Answer of ICT581 Assignment 2

Order Non Plagiarized Assignment

ICT581 Question 2: Conceptual Design

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 with2

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 wouldsign 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 is on 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 2023.
10. .A list of all the toys that were borrowed for the Star Wars event held in July 2024.

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 assumptionsyou have made in creating the data model. 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.
• You should use the crow's feet ERD notationwe have been using in the lectures and should include a legendto explain the notation. You shouldinclude 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 document. Please make sure the labels and symbols in your ERD are readable. Enlarge them from the default if necessary 
• Please note that hand-drawn ERDs are NOT acceptable; however, you may use Visio or another drawing tool as long as it provides the functionality required