anyone can check my normalization?

[Hermione-wong]

i am doing a project and need to create a data scheme for it, the topic is sports day system. I am quite sure for my 3NF, however, i don't know how to convert back to 2NF, 1NF and UNF...

my another question is, can i create a new field during normalization? and can UNF contains more than 1 table?

PLEASE HELP ME!!!

UNF

• STUDENT (stud_id, stud_name, stud_class, stud_class_no, sex, dob, stud_score, house, house_score, user_pw, event_ID, event_name, event_type, start_time, event_record_holder, event_hist_record, record_holder_house, heat_lane, result, rank_ID, rank_name, score_achieved, prize_ID, prize_name, input_user)
• TEACHER (teacher_ID, teacher_name, user_pw)
• ADMIN (admin_ID, admin_name, user_pw, ann_ID, announcement, ann_date, ann_s_date, ann_e_date)

1NF

• STUDENT (stud_id, stud_name, stud_class, stud_class_no, sex, dob, stud_score, house, house_score, user_pw)
• TEACHER (teacher_ID, teacher_name, user_pw)
• ADMIN (admin_ID, admin_name, user_pw)
• ANN_DETAIL (ann_ID, announcement, ann_date, ann_s_date, ann_e_date, admin_ID)
• Event (event_ID, event_name, event_type, start_time, event_record_holder, event_hist_record, record_holder_house)
• PARTICIPATE (stud_ID, event_ID, heat_lane, result, rank_ID, rank_name, score_achieved, input_user)
• PRIZE (prize_ID, prize_name, winner_ID)

2NF
same as 1NF

3NF

• STUDENT (stud_id, stud_name, stud_class, stud_class_no, sex, dob, stud_score, house, user_pw)
• HOUSE (house, house_score)
• TEACHER (teacher_ID, teacher_name, user_pw)
• ADMIN (admin_ID, admin_name, user_pw)
• ANN_DETAIL (ann_ID, announcement, ann_date, ann_s_date, ann_e_date, admin_ID)
• SCORING (rank_ID, rank_name, score_achieved)
• Event (event_ID, event_name, event_type, start_time, event_record_holder, event_hist_record, record_holder_house)
• PARTICIPATE (stud_ID, event_ID, heat_lane, result, rank_ID, input_user)
• PRIZE (prize_ID, prize_name, winner_ID)

[Dr John]

Of course you can make a new field after normalising! Fields are attributes of your entities, and if it clearly belongs to a given entity (represented by a table), just add it.

Just watch - imagine you just realise house has a date of foundation. Possibly even a real attribute in your example. Now, do I need to denormalise anything to work out it belongs in the house table?

You can have as many unnormalised tables initially as you like. A single table in UNF is a bit artificial, many real systems start with a few obvious tables, each not normalised to different extents.

PS WHY convert back to UNF?

[disgracian]

It's probably a uni homework question. Lecturers have students do all sorts of strange and not terribly useful things.

Cheers,
D.

[Dr John]

None on the courses I took would ask you to make something back into some unnormalised form. I think the OP assumes that if you find a new field you have to unnormalise everything then run through the whole step by step normalisation process again. Notice it says "my 3NF"

Me? I think of the entities first. And what attributes they have. I wonder if the OP has also done an ERD first, then assumes you must run through the whole normalisation process backwards to check your entities are correct. But I doubt it.