Results 1 to 2 of 2
  1. #1
    Join Date
    May 2015
    Posts
    1

    Help with database design assignment

    Hello

    I know this is not always accepted on Forums but I am so desperate. Please help even a subtle hint would be appreciated

    We received the following pieces of an excel worksheet( there is 7 in total) and need to draw a fully 3rd normal form ERD from it.
    Click image for larger version. 

Name:	sampleWorksheet.JPG 
Views:	8 
Size:	124.4 KB 
ID:	16334
    (this is only the first one, would like to do the others without help)

    I am struggling to identify the Entities and each corresponding entities. Here is what I have so far:
    Click image for larger version. 

Name:	sampleWorksheet2.JPG 
Views:	4 
Size:	53.1 KB 
ID:	16335

    I think that I am heading off in the wrong direction with this.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so what entities do you think are present in this?

    bear in mind that the aim of a normalised db design is to remove duplicated data
    identify logical types of data
    ferinstance:-
    you have data on countries so for me thats an entity
    you have various metrics on life expectancy rates, broken down by year
    ..thats the tricky bit I suppose.
    if you 'know' that there will never, ever (and I do mean NEVER EVER ) be a change to the list of metrics then you could have them as one row per year
    eg:-
    country_code
    reporting_year
    male_le_at_birth
    female_le_at_birth
    male_hale_at_birth
    female_hale_at_birth
    neonatal_mortality_rate
    male_MDG4_rate
    female_MDG4_rate

    or do you instead split on gender
    country_code
    reporting_year
    is_male
    le_at_birth
    hale_at_birth
    neonatal_mortality_rate
    MDG4_rate
    ..in that case you'd have two rows per year per country (you wouldn't store the data for 'both' as you can always derive that from the male & female values

    but then the neonatal rate doesn't fit / seem in the right place if you have separated out baed on gender

    So then another option is to record a specific value by country and year, by data type
    eg:-

    country_code
    reporting_year
    neonatal_mortality

    country_code
    reporting_year
    gender
    le_at_birth

    country_code
    reporting_year
    gender
    hale_at_birth

    country_code
    reporting_year
    gender
    mdg_at_birth

    in practice you could roll up the le, hale & mdg tables into one table with an identifier which identifies what metric this is

    eg:-
    code
    description

    country_code
    reporting_year
    gender
    metric_code
    metric


    the last approach is more flexible. if someone deciodes to add a new metric to the analysis you can easily add the type to the metric codes, and the users would then have that available for use with no schema changes, no data capture UI changes. but probably would require some display / report changes.

    but as with all these sort of questions it really depends on what you are trying to achieve, what you need to demonstrate and how realistic an example.

    for the country I'd suggest you use the iso country codes (whether you use the 2 or 3 digit code is up to you. wherever there is pre existing definitions use 'em. but don't be tempted to use the international telephone codes in place of the country codes as some countries share the same telephone dialling code

    bear in mind for coursework / homework often its the narrative of how you got to your final decision that is just as important as the final design. often its perfectly acceptable to say I got here, based on the following assumptions. if I wanted to extend the model further Id' consider x,y,z.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •