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!!! :sick:

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)

For all the established normal forms, it's always the case that if a relation is in a higher normal form it must also be in a lower normal form.

So a relation in 3NF must be in 2NF and one in 2NF must be in 1NF.

You can't tell if a relational variable is in 1NF unless you know that: a. it has a primary key and b. the rows aren't ordered in any way and c. there are no values that contain multiple values packed in, the so-called "repeating groups".

You can't tell if a relational variable is normalized by looking at the attribute names. If you're interested in normalizing up to BCNF, you need to work out the functional dependencies.

Until you tell me what is functionally dependent on what, I can't tell you if it's in 2NF or 3NF.

but you can make a pretty decent guess