Question Creating ERD's in the correct way

[davida1992]

I am undergoing a project for university where i am going to be creating a complex Access Database for an Animal Sanctuary.

I have previous worked on bottom up designs, however i have a few questions i hope someone can help with!

I haven't done databases in some time so i hope someone can help correct me if i am wrong!

When doing bottom up design, we did first second and third normal form during normalization, as far as i can remember normalization is only done on a bottom up design?

This database is brand new so i will be using a top down design, i have never really done it so are there any official methods to go about doing it?

All i have done is written what i beleive would be needed for part of my database, so i was wondering if someone could review my ERD and let me know if it would work or not, and also let me know if there is a "method" i should have used to come to the conclusion i have come to.

To give you a perspective of what im doing, part of the database will be for hedgehogs coming into the animal sanctuary that are sick or injured, and then being released by volunteers etc, so here is what i have:

Entity name: Hedgehog in Care
Hedgehog ID
Date in
Medication
Weight
Comments
Where the Hedgehog came from

Entity Name: Released Hedgehogs
Hedgehog ID
Release ID
Date In
Date Out
Last Recorded Weight
Comments
Where the Hedgehog came from

Entity Name: Releaser Details
Releaser ID
First Name
Last Name
Phone
Mobile
Email
Address
Post Code

Entity Name: Deceased Hedgehogs
Hedgehog ID
Date In
Death Date
Weight
Medication
Comments
Where the Hedgehog came from


Any ideas on if i am on the right line and on how i should have came to these results...

Thanks!!!

 

[jdraw]

Here's a tutorial that should help you.
It leads you through the steps to identify entities, attributes and relationships.

http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip


Here are 2 outline from another source (Barry Williams).
http://www.databaseanswers.org/approach2db_design.htm
http://www.databaseanswers.org/tutorial4_data_modelling/index.htm

I'm sure there are many flavours of "correct".

Good luck with your project.

 

[davida1992]

Thanks for that, i gave that a go but still cannot make it work with my scenario! I know im being silly!

I have tried so hard and i have been at it for like 4 hours, im getting frustrated and tired now since its 1am, not to mention i feel so stupid for not being able to do the most simple thing!

Here is basically my scinario:

The Sanctuary would like to view records of hedgehogs coming in and out of the Sanctuary for being injured/sick.
This includes hedgehogs that are admitted into the sanctuary including the date they came in, their weight, their medication, where it came from and any comments about the hedgehog.
They would like to be able to view any hedgehogs that have died after being admitted with their date of death and the probable cause of death logged.
They would like a database of willing individuals who will take hedgehogs and release them back into the wild from their home once they have a made a full recovery after being admitted. It should include the individuals name, address, email, postcode and contact number.
Any released hedgehog should be viewable along with the releaser.

Anyone know what im doing wrong after reading that scenario?

thanks again!!

 

[MarkK]

You only need one hedgehog table. If DeathDate is filled in, it died on that date. If DeathDate is not filled in, the critter is not dead. There is no reason to separate out dead animals into their own table. Same for Released. If there is a ReleasedDate that hedgehog is gone, and if IsNull(ReleaseDate) then the release has not happened yet, because the data doesn't exist yet. Again, no need to move that record to a special table, just update the data when you get it.
hth

 

[davida1992]

OK, well the only other thing that i need is i want to create different forms for viewing records, so one for for current hedgehogs, one for released hedgehogs and one for dead hedgehogs.

Can i still do this with all that data in one table?

I assume i would still have a seperate table for the releasers?

Thanks!

PS - As well as forms i want to sometimes be able to look back at tables, one with all current hedgehogs, one with released and one with dead hedgehogs. Would this be were quiries come in? And would i really need a relationship database for this section of my database?

 

[stopher]

OK, well the only other thing that i need is i want to create different forms for viewing records, so one for for current hedgehogs, one for released hedgehogs and one for dead hedgehogs.

Well you could do all that on a single form and just set up different filters e.g. behind buttons. So to filter dead hedgehogs just filter records where there is a date for "Death Date" i.e. where it is not null (see lagbolts post)

But you are jumping the gun a bit. Your question suggests you are trying to create the ERD. So that's the bit you need to focus on. The ERD is the model of the real world. So it should show the model of the real world such that the model contains all the necessary entities and data with the appropriate relationships.

Can i still do this with all that data in one table?

I assume i would still have a seperate table for the releasers?

Thanks!

Lagbolt is suggesting that Hedgehog is one entity and any information about a hedgehog is held in that entity e.g. arrival date, leave date, death date etc. These are all things about a specific hedgehog so yes they are a single entity. HOWEVER, if I remember correctly, I don't think blank entries are allowed in entities (I might be wrong). So for modelling the ERD you may well have multiple tables abeit with a 1:1 relationship. 1:1 relationships pop up a lot in ERDs. In which case you are not far off with your entities.

So Released Hedgehogs could/should be an entity (I'd call it Releases or Release Dates). But I think it only contains the fields "release date" and "comments". Note that entities in ERDs DO NOT contain foreign keys. The foreign key relationship is depicted by the lines drawn between entities.

You mention "Last Recorded Weigh". This should not be in the Releases Hedgehogs entity imho. Surely this is to with Hedgehogs not the process of releasing? Also, are we recording multiple weights for a given hedgehog i.e. the weight over time? How will you do this in the current model?

Yes I think Releaser is a separate entity. These are human beings - not hedgehogs. There is of course a relationship between releasers and released hedgehogs.

You have "Release ID" in Released Hedgehogs. Does this ID relate to something in the real world? If not then it should not be in an ERD.

Don't confuse your ERD with your "relational" model. The relational model will be based on your ERD but you will have engineered it towards a relational model fit for your database - the single table that lagbolt suggests would likely be part of this redesign. Are you intending to do an ERD here or are you really trying to create your relational model?

hth
Chris

 

[davida1992]

All i really have at the moment is the outline for what they want:

The Sanctuary would like to view records of hedgehogs coming in and out of the Sanctuary for being injured/sick.
This includes hedgehogs that are admitted into the sanctuary including the date they came in, their weight, their medication, where it came from and any comments about the hedgehog.
They would like to be able to view any hedgehogs that have died after being admitted with their date of death and the probable cause of death logged.
They would like a database of willing individuals who will take hedgehogs and release them back into the wild from their home once they have a made a full recovery after being admitted. It should include the individuals name, address, email, postcode and contact number.
Any released hedgehog should be viewable along with the releaser.

So i need to basically do the entire things from scratch, but i havent done databases in a while and have forgotten how to start it off, so i am probably jumping ahead.

 

[jdraw]

davida1992,

I suggest you look at the procedure outlined and demonstrated in the first link I gave in previous post.
Focus on the ERD (the database design) not the database per se at the start.
It's a liitle like the artist's concept of a new mall versus building the physical mall.

http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck.