Hi Everyone

I have been given the task of designing a database for a volutery organsiation and i have been give the following information as basis to design the database.The following information below is example of a paper record


•Volunteer details
Volunteer ID
Name
Telephone
Address

Services provided by volunteer
Code Service
01 Translation
05 Wheel Chair pushing
09 Baby sitting


Booking records
client no client name date required hours required service code
004 Lee Wong 19/01/08 2 01
004 Lee Wong 02/02/08 1 05
006 Sara Plat 04/02/08 3 09
007 Jay Palin 04/02/08 05


Assume also that additional details are kept for clients, including address, telephone numbers and next of kin. Clients who have pets have information stored on these as well (pet number, pet name, and description). Clients who have children and require baby sitting have the following information: child number, name, date of birth, sex.

Can anyone help me produce an Entity-Relationship diagram for this organsiation
secondly convert that ER daigram into relational model by specifying the primary and foreign keys, mapping any one-to-one relationships into relations, and decomposing any many-to-many relationships

A relation diagram and ERD diagram for the above organization

To be honest with ya'all i dont know where to start can someone guide me to enlightment by making a relation diagram and ERD diagram for the above organisation



I really appreciate you help and will be grateful for any help.

Thanx,

Lee

very clear question and thanks for showing your effort and what you are having trouble with

i'm not too big on vocabulary, but you want to try to get the database to use third normal form

something like this for your bookingrecords table

bookingrecords
CLIENT_ID
DATE
REQUIRED_HOURS
REQUIRED_SERVICE_CODE

clients
CLIENT_ID
CLIENT_NAME
ADDRESS
CITY
STATE
ZIP (sometimes demographics are in a separate table but we'll keep it simple)
PHONE

clientfamily
CLIENT_FAMILY_ID
CLIENT_ID
CONTACT_NAME
DATE_OF_BIRTH
SEX
RELATIONSHIP_ID

relationship
RELATIONSHIP_ID
RELATIONSHIP_NAME

clientpets
CLIENT_PET_ID
PET_NAME
PET_DESCRIPTION

some of the requirements are a little fuzzy, like who owns the pets, client or a family member

that should be enough to get you started, let me know if you need any clarification

Clients who have pets have information stored on these as well (pet number, pet name, and description). Clients who have children and require baby sitting have the following information: child number, name, date of birth, sex.

then this table structure should work for you

Here is the requirments matrix see what you can do with it

The poster you forgot the Voluteer Entity which is vital in this case

ahhh, now with the document i see how volunteers are related


volunteers
VOLUNTEER_ID
NAME
TELEPHONE
ADDRESS

volunteerservicesprovided
VOLUNTEER_SERVICE_ID (optional)
VOLUNTEER_ID
SERVICE_CODE

change booking records to this

bookingrecords
CLIENT_ID
DATE
REQUIRED_HOURS
REQUIRED_SERVICE_CODE
VOLUNTEER_ID

this way, if a volunteer ever changes his service offered, you still have record of which volunteer did the service

Hi thank your for expert insight it is so much appreciated. Since we on the last leg of the project can you please give me your email so i can send you what i have done so far so that you can review it and maybe mistakes if you will



Thank you again

you can just use this post here, so everyone can benefit from it and be able to offer you advice

Here is the case study and my workings please added whatever you think will help
All your help will be much appreciated once you finish with the corrections please reattach


Thanks

Lee