x

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)
more ▼

asked Dec 17, 2011 at 12:14 AM in Default

sun 1 gravatar image

sun 1
21 3 3 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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)
more ▼

answered Dec 17, 2011 at 01:27 AM

Wilfred van Dijk gravatar image

Wilfred van Dijk
1.3k 20 25 31

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)
Dec 17, 2011 at 01:30 AM Wilfred van Dijk

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?
Dec 17, 2011 at 01:57 AM sun 1

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]

Dec 17, 2011 at 02:14 AM Wilfred van Dijk

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
Dec 17, 2011 at 03:27 AM sun 1

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?
Dec 17, 2011 at 03:47 AM Wilfred van Dijk
(comments are locked)
10|1200 characters needed characters left
"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.
more ▼

answered Dec 17, 2011 at 12:48 PM

Alendar gravatar image

Alendar
40 2

thanks for your explanations
Dec 18, 2011 at 01:53 AM sun 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x346
x15

asked: Dec 17, 2011 at 12:14 AM

Seen: 1235 times

Last Updated: Dec 18, 2011 at 11:26 PM