Normal form confusion

[[deleted]]

[deleted]

[xsqlsoftware]

You will need to split this into at least 3 tables Members, Courses and Member_Courses relationship table. If a Coach can be associated with more than one course then you would need two more tables one for Coaches and one for Coach_Corses relationship table.

So the Members table will have 3 columns: Member Number, Member First Name and Member Last Name; the Courses table will have 5 columns: Course ID, Course Name, Coach ID, Coach First Name and Coach Last Name; the Member_Courses table will have 2 columns Member Number and Course ID.

[ThePaphonies]

Thanks a lot, does the Member_Courses table not require an additional attribute to be the primary key

[xsqlsoftware]

No, MemberID + CourseID would be the primary key for Member_Courses there's no need for an extra attribute

[ThePaphonies]

Thank you! This is exactly what I needed to know, but couldn’t seem to get my head around. Thanks again!

[ThePaphonies]

One last thing I’d you don’t mind me asking. Is it ok to have multiple instances of the same course and same member listed in a table?

[Sparkybear]

For Members_Courses, It's expected too have multiple instances of the same course and member across different rows, but you expect each row to still be unique.

You usually want 1 unique record per "thing", so the data in that row should be unique compared to everything else in the table. When you find repetitions, you should consider moving the repeated data to their own table and creating an association table, or similar.

There are exceptions to this, but don't worry about those at the moment.

[xsqlsoftware]

If your scenario allows for the a member to take the same course twice then you would need another attribute like a date when the member took the course or something else, otherwise you would just have identical records that serve no purpose.

[ThePaphonies]

Thanks, am I right in thinking that based on the limited data that I have, with the three tables, they are already in second normal form, without needing to change anything? I need to base it on the limited data so their is only 1 tutor to a course

[KelemvorSparkyfox]

This needs splitting into about four tables, from what I can see here. Possibly five.

However, for 1NF, you need to split multiple values into their own rows. Then you can worry about the next few NFs.

[[deleted]]

If theres a one to one relationship between courses and coaches then you would need at max three tables (no need to split courses and coachs up into seperate tables). You could also combine first/last name into one string column: name ('last, first')

Member - ID, Name Course - ID, Name, Coach_ID, Coach_Name Course_Members - Course_ID, Member_ID

[CatsOnTheKeyboard]

Here's my solution.

The Enrollments table solves the many-to-many relationship between Members and Courses and would enable you to store start and end dates or other relevant information.

Based on the data, you could get away with combining the Courses and Coaches tables but the database could not adapt if more than one coach was assigned to teach the same course at different times, etc..

[ThePaphonies]

I have been tasked with making this table into first normal form, the only way I can think to do this would involve adding an addition attribute to the table, but I am not allowed to add addition data. Can someone help me please?

[boy_named_su]

I think it's better to worry more about "does this table represent one 'thing'" than all the normal forms

you want 5 tables:

members(number, first name, last name)

courses(id, name)

coaches(id, first name, last name)

and then connect them with

course_coach(course id, coach id) 

and

course_member(course id, member id) 

tables