Forums for the Business Analyst

 
  Modern Analyst Forums  Business and Sy...  Business Proces...  removed
Previous Previous
 
Next Next
New Post 2/27/2010 8:07 AM
User is offline dolores
2 posts
No Ranking


removed 
Modified By dolores  on 3/1/2010 5:07:13 PM)
removed
 1 Reports
New Post 2/27/2010 3:27 PM
User is offline Kimbo
454 posts
5th Level Poster


Re: Normalisation Help 

 Hi Delores,

They're asking you to identify the main classes / entities from the description they've provided. Then once you have these, produce a normalised database by removing repeating groups and creating association classes etc. to remove many to many relationships. I can't remember what the exact definition of 1st, 2nd and 3rd normal form are but try looking them up in your text books. 

Then once you've done that you have to prove your database design by creating it in MS access and create some screens for input and enquiries (reports).

They've given you all the information you need including some good clues on some suggested entities like Member, time trial, training session, training session attendance, etc.

By doing this you'll learn basic database design and get to prove your design actually works. It looks like a great learning exercise to me. 

I could do it for you but if I do you won't learn how to do it yourself.

Kimbo

 1 Reports
New Post 2/28/2010 3:15 AM
User is offline dolores
2 posts
No Ranking


removed 
Modified By dolores  on 3/1/2010 5:19:10 PM)
removed
 1 Reports
New Post 2/28/2010 9:56 AM
User is offline Guy Beauchamp
257 posts
www.smart-ba.com
5th Level Poster




Re: Normalisation Help 

 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

 
Previous Previous
 
Next Next
  Modern Analyst Forums  Business and Sy...  Business Proces...  removed

Community Blog - Latest Posts

There are big differences between data exploration versus data presentation. And you need to be aware of these differences as you're creating data stories and data presentations. Let’s start by defining our terms: Data exploration means the deep-dive analysis of data in search of new insights. Data presentation means...
Is Agile a reason to avoid documentation? I bet this question shows up again and again while working with product requirements. On one side, we have got long specifications, complicated diagrams, mystical technical design, too many prototypes and pretty obvious for engineers user guides (do we really need so much?). On the other side, can we actual...
The cloud-native application development has helped enterprises all around the globe reduce time-to-market, enhance performance, and develop agility and flexibility. Several enterprises are achieving these results by migrating their systems or traditional monolithic applications to the cloud. But to gain from the real benefits of cloud technology, ...

 






 

Copyright 2006-2022 by Modern Analyst Media LLC