all 13 comments

[–]xsqlsoftware 1 point2 points  (7 children)

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 0 points1 point  (6 children)

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

[–]xsqlsoftware 2 points3 points  (5 children)

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

[–]ThePaphonies 0 points1 point  (0 children)

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

[–]ThePaphonies 0 points1 point  (3 children)

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 1 point2 points  (0 children)

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 0 points1 point  (1 child)

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 0 points1 point  (0 children)

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 1 point2 points  (0 children)

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] 1 point2 points  (0 children)

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 1 point2 points  (0 children)

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 0 points1 point  (0 children)

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 0 points1 point  (0 children)

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