DataBase Design Help

[xman12]

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

[dickersonka]

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

[xman12]

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.

[dickersonka]

then this table structure should work for you

[xman12]

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

[xman12]

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

[dickersonka]

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

[xman12]

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

[dickersonka]

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

[xman12]

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

[dickersonka]

I will help, not do assignments, what specific pieces are you having trouble with?

Also what is background and skills in the volunteers table?
One more, where did the relationships table go? ClientChild table means only children and not other relationships? Client table has a next of kin column, what is this?

[xman12]

I deleted the background and skills attributes
The next of kin is the person to be informed just in there is any problems. Now i completed the ERD MODEL i need help with idenfying potential fan traps. i identify the client entity as the entity that is most likely to have a fan traps

[dickersonka]

i agree, would also add clientpets to bookings / volunteers

[xman12]

Next Stage is covert the relational model into object oriented shema

I need help converting a relational model into an object-oriented database design in terms of classes, listing all attributes for each class together with their reference and collection data types.

Entities

Entity
Booking
Attributes
bookID (Pk)
volServID (Fk)
clientID (Fk)
Date-Req,
Hours_Req

Entity
Client
Attributes
ClientNo (Pk)
ClientName
ClientType
ClientAddress
ClientTel
NextOfKin

Entity
Volunteers
Attributes
volNo (Pk)
volName
volTel
volAddress
AreaofSpeciality

Entity
Volunteer_Services
Attributes
Vol_ServNo
serviceNo (Fk)
volNo (Fk)
track

Entity
Service
Attributes
serviceNo (Pk)
serviceName

Entity
Client_Pet
Attributes
PetNo (Pk)
PetName
Description
Client_No(FK)

Entity
Client_Child
Attributes
ChildNo (Pk)
ChuldName
DateOfBirth
Sex
Client_No(FK)