question

sun 1 avatar image
sun 1 asked

Normalization the table

Can anyone help me in normalize this table..and tell me in which normal form the table sign Sign (Member-ID, Member-Name, Activity-ID, Activity-Name, Session-ID, Day, Time, Coach-ID, Coach-Name)
sql-servernormalization
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
Without knowing your total diagram at least you can create the following tables: 1. a table with Member-id and Member-Name 2. a table with activity-id and activity-name 3. a table with session-id, day and time 4. a table with coach-id and coach-name the sign table will be sign (member-id, activity-id, session-id, coach-id)
12 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

some hints: - If a coach is dependent on a session, this table is not in BCNF - Same if session is dependent on an activity (you need to clarify you application a little more, or we'll start guessing)
0 Likes 0 ·
Given this scenario Table Sign is one-table database recording the enrollment information of sports club members in sports activities. The information recorded are the member id, the member name, the activity id, the activity name, a session id that is unique within the same activity, the day, times of the activity session enrolled, and the id and name of the coach supervising the activity session. Each session must be supervised by only one coach and the duration of all activity sessions is one hour. so that I think the primary key is may be memeber id >> what did you think?
0 Likes 0 ·
So it seems a session_id covers: activity_id, schedule and coach Try this : table member([member_id], member_name) table activity([activity_id], activity_name) table coach ([coach_id], coach_name) table session([session_id, activity_id, coach_id, day, time]) table sign([member_id, session_id]) PK is written as [column]
0 Likes 0 ·
i think the dependency are MemberID --> ActivityID ActivityID --> SessionID so that I get different table and why you put the day, time as primary key
0 Likes 0 ·
You're right: in table session: session_id is enough as a PK, but you should also create a unique index on activity_id, coach_id, day and time to prevent duplicates If you assign a member_id to an activity_id, you can only create this combination one time, or is this the case? Could you give some examples of an activity?
0 Likes 0 ·
Show more comments
Alendar avatar image
Alendar answered
"first table Member ([MemID], MemName, ActivityID) Second table Activity ([ActivityID], ActName, SesID) Third table Session ([SesID], Day, From, To, CoachID) Fourth table Coach ([CoachID], Name)" is incorrect. It states that one member can only ever engage in a single activity, which is incorrect. "Session" is your clue. What do sessions have? Times. They happen at times and involve people, places, and things. They are transactional. This is your fact table. All others are referential. The reference tables Member, Activity, and Coach are not related to each other. Think of Session as being a grab bag that pulls all the others together for a party.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks for your explanations
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.