Hi I have a test coming up soon and I predict there will be a database question on the following scenario from the case study. It would be great to hear from anyone who could give me any ideas on ERD and Normalisation (which are my weakest topics). Any help would be greatly appreciated. Thanks Mack
Here is an extract from the case study about an animal shelter:
The charity runs a sponsorship scheme where, for £1 a month (£2 for a horse), members of the public can sponsor an animal. In return for their sponsorship they will receive pictures and letters, supposedly from their animals, telling them how they are getting along, as well as a card, complete with paw-print or hoof print signature, on the sponsor’s birthday.
When a new animal is brought to the sanctuary, the first month is taken up grooming and generally making the animal presentable and ensuring it is medically fit. Details such as name, breed and colour are collected and placed in a card index system by the admissions clerk. A picture is taken and entered, along with the animal’s details, into a catalogue. This catalogue is printed bi-annually and distributed to a large number of vets, dentists and doctors, in fact anywhere where there is a waiting room. Prospective sponsors contact the sanctuary and are provided with an application form, which includes a direct debit mandate. On completion and return, their application is processed by the sanctuary. The sponsor’s details are put onto a card index system and the direct debit mandate passed to the sponsor’s
Each animal in the sanctuary is assigned a special carer, a casual worker, one of whose duties is to write the letters on behalf of their animals and send out the birthday cards when the time arises. They get the required information from the card index system. Although this is an onerous task it makes a pleasant change from mucking out the stables!
One of these casual workers is a student who has suggested that the catalogue should be put on a website and people should be able to sponsor animals using this method. She feels that this will reach more people than simply having the catalogues in various waiting rooms. She has further
suggested that the card index system could be computerised and that this would make the process a lot easier to administer.
As its coursework a good satrting point is for you to analyse your requirements and come up with some form of ERD yourself. Whatever means you sue is up to you.
As a general rule if its academic coursework then its to your benefit to demonstrate the process by which you came to your physical (table) design, by showing how you derived your entities and how you eliminated columns, how you came to your final design.
So I'd suggest as a first step try to identify your entities, try to identify columns within your entities. A usefull techinique if yoiu are normalising the data is to use the mantra of the key and only the key to identify the critical data that makes each element unique.
Lets take an example, lets look at donors
You have many donors, but do you want to limit to just donors - I'd guess no - its probably Persons,
you need to store details about that person (eg names, DoB)
you need to store contact details for that person (Address(es), phone, email, moby etc...)
you may need to store interests of that person (eg interested in Cats, Dogs, Horses or whatever)
Persons may or may not be donors
if they are donors then they MUST provide bank details, deatils of when the payment should be made, time limits (the donation start datae, end date & intercal)
you may need to store something to identify what type of donation it is
arguably you may need something to cater for types of bank (depends how you desing the bank interface). do you allow a single donor to make multiple donotaions / adoptions or is your donation stricly one ot one. if you allow multiple donations from one donor then how do you handle that? is it a single payment covering multiple donations or individual payments covering separate adoptions.
think of the financial records
so what identifies a persons bank details?
for a UK bank its going to be Sort Code & Account No, you will probably also need account name, bank name and possibly branch address. If you now the IBAN code even better.
I think it wil help if you work through the process yourself. If you hit problems then come back but unless you go through the process yourself, you won't understand it and you won't pass your assessment. You will get a much better response for if you show some effort and show what you have tried.
How do you know what type of animal it is, and therefore how much to charge?
how do you know when an adoption expires.
do you care that a donor is no longer an adopter - IE do you want to remove that person from your list, or keep them on the list but inactive, so you can approach them again.
do you care that you have sent a direct debt form but not heard from the prospective donor
to handle carers you probably need to treat in the same manner as person (ie have a separate table contaning details of animal carers and a table similar to adoption that cross references a carer with an animal
your current design doesn't yet map to the brief, you still have other issues to do with Animal for instance the picture (hsa it been taken, how do you store it), the date the animal entered the shelter, the duration of the grooming period prior to release for adoption. is the grooming period fixed (ie alwasy one month between arrival and available for adoption)
Do you have a fixed interval for commnication form the carer to the adoptor. - is it the same for all animals. do you need a management report showing what letters have been sent to what adptors covering which animals
do you need some form of state flag to identify where an animal is (either Avalable for adoption or Not Available)
do you want the breed to be freeform or do you expect it to be controlled - freform requires no further input but runs the risk of typos meaning that you miss groupings. you donors may have in mind they want Siamese cats, rather than Sighamese
how do you propose to handle the disparate classification of different breeds eg horses size quoted in hands, dogs probably in weight.
thinking of the website, how would you want to presented with animals available for adoption. what rules would you want to use to zoom in on the animal you would want to adopt, what rules would the charity want. for example if you were interested in horses but not dogs, cats, hampsters etc....., you might however be interested in a particular breed or colour of animal eg a palomino shetland pony
so you need to consider if controlling the types of animals is important, if the breed or sub type is important. and can these categroies be mixed. ie its doubtfull that someone would want a horse but specified a Rottweiler as the breed, or a Palomino Dog, but a Palomino Horse or a Rottweiler Dog are valid
if you go down the website route do you need some form of password & userid for the donor so they can get all gooy eyed at the precious animal online. The userid shields their name from public view and may be a legal requirment tha tyou do not show names on line)