[Andreika_86]

Hello everyone!
It might seem to be a very easy question, but I still very confused about identifying and non-identifying relationship.
I included the file with the diagram. For example one university can have many colleges, each college has an id so it can be uniquely identified, shold this be non-identifying? The problem I have here is the normalisation as well, since I used identifying most the time - table programme has too many repeating rows... Would it be correct to use non-identifying between: University and college, college and school and school and programme? If not not, how will I get rid of repeating tables for normalisation? I am not lazy and did research but I am comletely confused... Any help gratly appriciated!!!

Attached File(s)

•  ERD.bmp (1.87MB)
  Number of downloads: 31

[Andreika_86]

I meant to say how will I get rid of repeating rows (not tables), for example in one school there is 10 programmes - and they all will have the same value for college and university. So if school has unique id but is still belong to a particular college can the relationship between college and school be non-identifying?

[baavgai]

Stop dragging your FKs around in you PKs.

A quick sample.

College
	college_id(PK)
	university_id(PK,FK)
	college_name
	
School
	school_id(PK)
	college_id(PK,FK)
	university_id(PK,FK)
	school_name

So, School relates to college, which is fine. But why does School also need a univeristy_id? And why are these things PKs?!?

Instead:

College
	college_id(PK)
	university_id(FK)
	college_name
	
School
	school_id(PK)
	college_id(FK)
	school_name

Same data, less repetition.

It gets worse in Programme, where you just seem to keep propgating fields down.

Your Modules_on_Programmes should have only two fields:

Modules_on_Programmes
	programme_id(PK,FK)
	module_id(PK,FK)

Hope this helps.

[Andreika_86]

I perfectly understand what you saying, but if in Erwin you specify the relationship as identifying the keys are dragged automatically, and you cannot remove them. That's why I was asking if its possible to use non-identifying relationship - which I am not sure about...

baavgai, on 11 December 2011 - 09:05 AM, said:

Stop dragging your FKs around in you PKs.

A quick sample.

College
	college_id(PK)
	university_id(PK,FK)
	college_name
	
School
	school_id(PK)
	college_id(PK,FK)
	university_id(PK,FK)
	school_name

So, School relates to college, which is fine. But why does School also need a univeristy_id? And why are these things PKs?!?

Instead:

College
	college_id(PK)
	university_id(FK)
	college_name
	
School
	school_id(PK)
	college_id(FK)
	school_name

Same data, less repetition.

It gets worse in Programme, where you just seem to keep propgating fields down.

Your Modules_on_Programmes should have only two fields:

Modules_on_Programmes
	programme_id(PK,FK)
	module_id(PK,FK)

Hope this helps.

[baavgai]

Erwin??? This isn't a design issue so much as a application usage issue, then. I didn't know the product still had a following. I mean, after CA eats you...

If there is an export and import to SQL code, you could try an export, manually mess with it as you like, then import.

I'm afraid I can't offer much help on GUI ERD software. I simply don't use it. Good luck.

[Andreika_86]

baavgai, on 11 December 2011 - 06:48 PM, said:

Erwin??? This isn't a design issue so much as a application usage issue, then. I didn't know the product still had a following. I mean, after CA eats you...

If there is an export and import to SQL code, you could try an export, manually mess with it as you like, then import.

I'm afraid I can't offer much help on GUI ERD software. I simply don't use it. Good luck.

Ok, thanks! Its just the thing is I have to use it(requirement) and generate the code... Yes maybe I should mess with it, but triggers generated as well - too much work