Hi Delores,
Here is how to normalise your data.
1. read the case study and identify all the candidate entities - these are names of real world things such member or stock and interactions between these real world things e.g. sale is interaction between member and stock. Your case study seems to be limiting you to considering the 4 forms as the starting point for your model so confine yourself to them. Look at each column on the forms and think if they could be entities. There are also entities that classify other entities. Example: there are columns on the personal best form called breaststroke, butterfly, freestyle and backstroke - could these be a classification of swimming styles? If so you have got a "swimming style type" entity with values of ed breaststroke, butterfly, freestyle and backstroke. This entity will be used to classify the "personal best" entity...
2. Pick what you think will be a central entity - it doesn't matter which you pick as it will all work out in the end anyway. Draw a rectangle on a sheet of paper and name the entity as the one you picked. Tick this off the list of candidate entities. Now start at the top of the list and work your way down. For each entity in turn put a box on the paper and name it as the candidate entity and work out all the ways that it relates to other entities already on the diagram. Example. Suppose your first entity was Member and the next you put down was Personal Best. Ask yourself, for each individual Member how many Personal Bests could they have? The legitimate answers are "0", "1" or "Many". The answer here is that a Member could could have many Personal Bests. Now ask yourself how many Members can an individual Personal Best belong to? The answer is that each Personal Best is for 1 Member only. So there is 1:m relationship between Member and Personal Best. You can fully express this cardinality by saying that 1 member can have zero, 1 or more Personal Bests but a Personal Best is for 1 and only 1 Member. There are various ways of showing this cardinality on data model diagrams. Do that for all the candidate entities.
3. Define the primary key for each entity. A primary key really tells you what the entity is regardless of what it is called. E.g. the primary key of Member appears to be name. Well ok for this exercise but in the real world I would say that a name identifies a Person not a Member. There is another problem with using name: Primary keys once assigned can never be updated. Well what happens if a person marries - they often change their name! So name is not a good key but maybe ok for this exercise...?
4. For all each entity define what you need to know about it. Example, for a Member you might want to know the Member's date of birth. Make sure that each attribute depends on the Member's primary key. By that I mean can you construct a sentence like "I want to know the Member's date of birth". The phrase "Member's date of birth" literally tells you that the attribute "date of birth" is dependant on the Member (that is the primary key of the Member which in this case seems to Member's name). Notice how the attribute "date of birth" almost prompts you to ask "date of birth of what?" - in this case Member. But whenever you are tempted to ask (for example) "price? Price of what?" then realise that you have an attribute and that the "of what" part of the question is the entity. I think your exercise may be limited to assigning all the columns that are not primary keys of entities as attributes of entities. E.g. Price of stock.
5. Now prove that your model is in 3rd normal for,
- Is each entity a real world object or interaction between 2 real world objects?
- are there no repeating groups on any entity?
If yes your model is in 1st Normal Form
- are all of the columns mandatory (i.e. nulls are not allowed)?
If yes then your model is 2nd normal form
- are all the columns dependant on the whole primary key of the entity (remember Member's date of birth?)
If yes then your model is in 3rd Normal Form and you can claim direct knowledge of the data modeller's 'joke': 3rd Normal form was define by Ted Codd and can be summarised as "a model is in 3rd normal form when every attribute depends on the key, the whole key and nothing but the key - so help me Codd!". Yes, now you can wow them at data modelling parties! :-)
I hope this helps...Kimbo is of course right that you have to do it yourself but if you want to email me your models at [email protected] I will review them to see if they are 3rd normal form.
Have fun: data modelling is a fantastically underrated way of defining with provable rigour business rules as they relate to data and once you get in to that style of thinking is very easy and VERY powerful. Often people say it is too technical for users to understand but I have never had a problem (I do the actual modelling and apply the rules, the users just have to validate I have got the requirements right). The only people who seem to have a problem with it tends to be technical IT people who can't seem to understand it defines business data requirements and NOT a database design. I use it a lot on real world projects...
Guy