7 Replies - 1012 Views - Last Post: 17 August 2013 - 06:25 PM Rate Topic: -----

#1 batsta13  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-June 13

What is normalisation? (specifically 3NF)

Posted 14 August 2013 - 02:40 AM

I've spent countless hours researching about normalization. I have some understanding on 1& 2NF but I still do not understand. I've tried removing all 3NF violations from my ERD and based on my current understanding of 3NF I think it is correct but I would like someone to have a look to see if I am correct. Can anyone see any normalization violation in the attached ERD? or any other type of issues?.

Attached Image

Is This A Good Question/Topic? 0
  • +

Replies To: What is normalisation? (specifically 3NF)

#2 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3715
  • View blog
  • Posts: 5,973
  • Joined: 08-June 10

Re: What is normalisation? (specifically 3NF)

Posted 14 August 2013 - 05:21 AM

The only things I noted while glancing over that is the Park location (address, suburb, city) and the various contact info. If you read the sections on the 1NF and the 3NF in my Normalization tutorial, you should see what I mean.
Was This Post Helpful? 1
  • +
  • -

#3 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5795
  • View blog
  • Posts: 12,629
  • Joined: 16-October 07

Re: What is normalisation? (specifically 3NF)

Posted 14 August 2013 - 08:54 AM

Just brainstorming here. ( I design and use a lot of ERDs. )

Coordinates has an employeeId but doesn't reference Employee. Shouldn't lon and lat just belong to the location? Perhaps a table with empId and locationId instead?

You already have an animal employee connection, what is the point of inspections?

If department only consists of name, then you haven't really gained anything by making it a separate table. Particularly if it's only referenced in one place and has no constraints.

Shift and Availability seem to be identical. Day of week might be pointless.

Park?

Location could scoop up some of Park's data and help unify things.
Was This Post Helpful? 2
  • +
  • -

#4 batsta13  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-June 13

Re: What is normalisation? (specifically 3NF)

Posted 14 August 2013 - 02:05 PM

View Postbaavgai, on 14 August 2013 - 08:54 AM, said:

Just brainstorming here. ( I design and use a lot of ERDs. )

Coordinates has an employeeId but doesn't reference Employee. Shouldn't lon and lat just belong to the location? Perhaps a table with empId and locationId instead?

You already have an animal employee connection, what is the point of inspections?

If department only consists of name, then you haven't really gained anything by making it a separate table. Particularly if it's only referenced in one place and has no constraints.

Shift and Availability seem to be identical. Day of week might be pointless.

Park?

Location could scoop up some of Park's data and help unify things.


While shift and availability are identical they are modelling two different things. Availability models when the employee is available for work which will be useful to their employer if they want to check when the employee is available for work (even though the employee has no shifts recorded in shift this does not necessarily mean they are available). Shift just models when the employee is scheduled to work.

View Postbatsta13, on 14 August 2013 - 02:40 AM, said:

I've spent countless hours researching about normalization. I have some understanding on 1& 2NF but I still do not understand. I've tried removing all 3NF violations from my ERD and based on my current understanding of 3NF I think it is correct but I would like someone to have a look to see if I am correct. Can anyone see any normalization violation in the attached ERD? or any other type of issues?.

Attachment Capture.PNG

if I was to add a attended boolean (did they show up for their shift) would I still need the day table? Because even if an employee had two days with the same start/end times, the start/end times would have to been duplicated twice so that their attendance for each shift can be recorded. Currently, start/time info isn't being duplicated as the day of the week is in a different table.


While shift and availability are identical they are modelling two different things. Availability models when the employee is available for work which will be useful to their employer if they want to check when the employee is available for work (even though the employee has no shifts recorded in shift this does not necessarily mean they are available). Shift just models when the employee is scheduled to work.

if I was to add a attended Boolean (did they show up for their shift) would I still need the day table? Because even if an employee had two days with the same start/end times, the start/end times would have to been duplicated twice so that their attendance for each shift can be recorded. Currently, start/time info isn't being duplicated as the day of the week is in a different table.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5795
  • View blog
  • Posts: 12,629
  • Joined: 16-October 07

Re: What is normalisation? (specifically 3NF)

Posted 14 August 2013 - 03:51 PM

View Postbatsta13, on 14 August 2013 - 05:05 PM, said:

Availability models when the employee is available for work ... Shift just models when the employee is scheduled to work.


You should be able to infer Availability from the the Shift the employee is scheduled for. I you are attempting to record both, where if one exists the other doesn't, you pretty much are denormalized. Instead, you could have one table with another field that flags availability.

Strangely, while many employees can work a job, via JobDetails, only one employee can work a given park at a given time?
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3322
  • View blog
  • Posts: 11,234
  • Joined: 12-December 12

Re: What is normalisation? (specifically 3NF)

Posted 14 August 2013 - 04:34 PM

Out of interest where is that ERD from? Is it Visio?
Was This Post Helpful? 0
  • +
  • -

#7 batsta13  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-June 13

Re: What is normalisation? (specifically 3NF)

Posted 17 August 2013 - 01:14 AM

yes I used Microsoft Visio
Was This Post Helpful? 0
  • +
  • -

#8 batsta13  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-June 13

Re: What is normalisation? (specifically 3NF)

Posted 17 August 2013 - 06:25 PM

batsta13, on 17 August 2013 - 01:22 AM, said:

"You should be able to infer Availability from the the Shift the employee is scheduled for. I you are attempting to record both, where if one exists the other doesn't, you pretty much are denormalized. Instead, you could have one table with another field that flags availability"

Could you provide furthur explanation. If I have only one table with an availability flag how would I store the times I was available between as while an employee may not have a shift this does not necessarily able to work i.e other commitments.

"Strangely, while many employees can work a job, via JobDetails, only one employee can work a given park at a given time?"


What do you mean?

This post has been edited by andrewsw: 17 August 2013 - 06:37 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1