Normalization and entity relation diagrams

[billwizz]

Could anyone please help me to normalize the following data to the 3NF and do an entity relation diagram.

The information is for a small system for a sports center for members and the activities that they book.

Thank you for your help

Membership Number
Title(Mr Mrs etc)
Forename
Surname
Street
Town
State or County
Telephone Number
Activity Number
Time of Activity
Description of Activity
Location of Activity

 

[jrbarnett]

BillWizz

One table - "members" with the following fields:

Membership Number (primary key)
Title(Mr Mrs etc)
Forename
Surname
Street
Town
State or County
Telephone Number

Second table: Activities:
Activity Number (primary key)
Time of Activity
Description of Activity
Location of Activity

Third table (Member_Activities) includes
membership number linked to members table
Activity Number linked to Activity number in Activities table.

Actual content of third table may include time of activity if you wanted to record who did what when (and remove from activities table).
The two fields in the third table (or 3 if you use the other one) become a composite primary key (ie they are both part of the key).

The Entity relationship diagram will have
Members 1:M Member Activities
Activities 1:M Member Activities.

John

 

[billwizz]

Could you please tell me how to present the information.

 

[jrbarnett]

Bill

The actual way of presenting it depends on how you show structure diagrams, and there are many of them. I will tell you mine, which I use, but this is not necessarily correct in your case.

Have a large rectangle for each table (so 3 of them in a row).
Draw a straight line from each table to the next, so there are 3 lines.
With the middle table, put a crows foot either side of the line linking it to the table, so it looks like a 3 pronged fork on each edge, sort of a greater than or less than symbol with a line going through the middle).
This indicates it is on the many side of a relationship.

Within each box (aka table), write the table name and put the key fields (those that make up the primary key) in brackets.
On the line between the table on the single side, write the name of the column that goes into the central table.

I would also put a title on the diagram and add a description of the database that it represents.

John

 

[billwizz]

Thanks very much John you sure are an expert.
Would it be possible to e-mail me with your presentations it would be a great help.

Sorry to sound dumb.


billwizz2001@yahoo.co.uk

 

[jrbarnett]

Bill,

I don't mean to offend, but with the information I have already provided I am sure that you are more than capable of doing it yourself in an appropriate graphics package.

Reading these posts, this project is sounding more and more like the sort of task that a college database course would ask a student to perform as part of its coursework, and on Tek-Tips students postings are not permitted (Take a look at

http://www.tek-tips.com/market.cfm

for more information why).

Why did I think that?
* Request to normalise manually (any seasoned database professional would be able to do this off the top of their head).
* Very simple database design, certainly not commercial level complexity.
* Request to create an entity relationship diagram from scratch, rather than use CASE tools on an existing schema, or reverse engineering a design into a schema.

There is nothing here that persuades me otherwise at the moment, but my apologies if this turns out to be the simplest sports centre setup I have ever seen.

If you don't understand the work yourself given what I have said already, then you should read appropriate textbooks or ask tutors or fellow students, and it is you the student - not me - that is being assessed.

I hope that this hasn't dampened your enthusiasm for Tek-Tips or myself as an individual, and I wish you good luck with the course.

Regards

John Barnett (jrbarnett)

 

[billwizz]

Thank you John but you could not be more wrong.The point is there are so many ways to present normalization that I cant find any solid or consistant ways to do it.

Thank you for your help.

 

[jrbarnett]

Billwizz - just follow the convention used on the course.

John