Re: Please check my 1,2&3 NF for assignment

In 3NF Option 2, there is no way to determine which job a particular interview was for. This could be solved by adding job-reference# as an FK in the interview table, but you would quickly see that this could then be factored down to your classmate's design. In option 1, your concern about matching the date and time to a specific interview (good catch, BTW) can be solved simply by adding another record to the job_application table for each interview. Your classmate's version is the more correct. Check out many-to-many relationships for a better understanding of the issues involved.

Regarding your address question, you've identified the issues, and the correct solution is the one that is right for your particular business and technical requirements. The answer, as you'll find with most I.S. questions is, "It depends."

Good luck!

"Colin Kinsella" <colin.kinsella_at_ntlworld.com> wrote in message news:4ab55a17.0111241347.438e9810_at_posting.google.com...
> Hi
>
> The follwoing is the result of 2 students work on a Normalization
> assignment, with a difference of opinion / uncertainty with 3NF.
>
> We would appreciate your comments.
>
> If you would prefer to see this in Word - please email me.
>
> Thanks in anticipation - Colin Kinsella
>
> Assumptions and comments at end (*= Key Field ie Star at front of
> field and underlined), both * and underlined are used for readability
> in both Word and basic email.
>
> Applicant Record (Applicant#, applicant-name, applicant-address,
> applicant-phone, date (Job-reference#, job-title, job-department,
> interview-date, interview-time))
>
> 1st normal form
>
> Remove Multiple Attributes (Fields) to new tables. Set Composite Key
> where non key attributes (Fields) could not be uniquely identified by
> a single Key (virtually always required).
>
> APPLICANT: ( *Applicant#, applicant-name, applicant-address,
> applicant-phone, Date)
> JOB_APPLICATION: ( *Applicant#, *job-reference#, Job-title,
> Job-Department, inteview-date, interview_time)
>
> 2nd normal form
>
> All Attributes (Fields) which are not part of the Key, must be fully
> functionally dependent on all of the Key.
> Remove Attributes (Fields) which are dependent on one part of the Key
> only
>
> APPLICANT: ( *Applicant#, applicant-name, applicant-address,
> applicant-phone, Date)
> JOB_APPLICATION: ( *Applicant#, *Job-reference#, interview-date,
> interview-time)
> JOB ( *Job-reference#, job-title, job-department)
>
> 3rd normal form
>
> All attributes (fields) must be fully functional on the whole of the
> Key. Remove Attributes (Fields) which are dependent on another
> attribute which is not the Key
>
> 3NF OPTION1 (no change from 2NF)
> APPLICANT: ( *Applicant#, applicant-name, applicant-address,
> applicant-phone, date)
> JOB_APPLICATION: ( *Applicant#, *Job-reference#, interview-date,
> interview-time)
> JOB ( *Job-reference#, job-title, job-department)
>
> One of my fellow students has produced option 1 for 3NF but has not
> given any clear reasons.
>
> 3NF OPTION2
> APPLICANT: ( *Applicant#, applicant-name, applicant-address,
> applicant-phone, date)
> JOB_APPLICATION: ( *Applicant#, Job-reference#,)
> INTERVIEW ( *Applicant#, interview-date, interview-time)
> JOB ( *Job-reference#, job-title, job-department)
>
> My Option 2 for 3NF is based on the fact that Interview Time is
> dependant on Interview Date for any given Applicant therefore
> applicant number is sufficient to identify the date/time of interview.
> BUT is this not really a result of the 2NF rule. In database terms
> this will also mean that Applicant# in table INTERVIEW will have
> duplicates (as each Applicant may have more than 1 interview.
>
> I am inclined to go with my fellow students suggestion ie the table is
> already in 3NF at the 2NF stage. But I would be surprised if our
> assignment would have such a result.
>
> Whatever option we choose we need to give clear reasons why we have
> chosen it.
>
> I would appreciate your comments on our Normalizations attempt and our
> assumptions.
>
> KNOWN FACTS
> This is a large company who will have many interviews on the same day
> and same time.
> Each applicant may apply for several jobs
>
> ASSUMPTIONS
> &#8220;date&#8221; field is not defined &#8211; as it is inside the
> first set of brackets we will keep it with applicant and presume it is
> date of first application or date of birth.
> Several applicants could apply from the same address but they could
> have different phone numbers eg mobile
> Several applicants with the same name could apply but they will not
> live in the same house
> Logically each applicant can only have 1 interview at one time but
> could have more than one on the same day
> Several Departments could use the same job title eg typist, admin
> assistant
>
> QUERY
>
> It would be possible to form a new table for applicant-address (as
> several applicants COULD live at the same address), but this does not
> seem logical; if a new table was produced, would an additional field
> be needed to identify the address and link it to the Applicant table?
> and is adding an additional field something that is done when working
> with Normalization.
>
> Thanks again
>
>
> Colin Kinsella
Received on Mon Nov 26 2001 - 19:59:23 CET