This discussion is now closed.

Check out other Related discussions

database normalisation. differenct between 2NF and 3NF

i totally dont get it.

to be in the second normal form, each non key attribute must have a functional dependency on the primary key.

to be in the 3rd normal form, you must eliminate transient dependencies.

to me, that sounds like one and the same thing. if in the second normal form, everything depends on the p. key, then surely that means there already arent any transient dependencies.

gah!

heres the 1NF anyway, what would that look like in 2NF and 3NF

1NF

*Catalogue No. <-- this is a unique ID of a cd type. there may be more than 1 cd belonging to each catalogue number tho
*Record Company
*Principle Artist(s)
*Title/Short Description
*No. of CDs in set
*No. of Copies Held

*Catalogue No.
*Accession No. <-- this is a second id unique to each individual CD
*Supplier
*Date Acquired
*Cost

in fact, im not even sure if the catalogue no. needs to be part of the key in the second table. i dont quite know if the accession No. is globally unique, or only unique in relation to the catalogue no.

this might belong in dev.... sorry if it is sposed to be there

Reply 1

slugonamission

Hmmm...my class notes are a bit, well, lacking on the subject. I'll have a delve around Wikipedia and try and get back to you.

I remember thinking WTF and not really getting my head around it though too...

Reply 2

Choad

OP

1

slugonamission

Hmmm...my class notes are a bit, well, lacking on the subject. I'll have a delve around Wikipedia and try and get back to you.

I remember thinking WTF and not really getting my head around it though too...

every time i think "aha, i've got it!" i take step back, and realise that it blatantly doesnt work that way.

grrrrrrr

Reply 3

slugonamission

OK, I think what it's trying to say is that in 3NF, all fields in a table must be dependant on the primary key, and if not, must be moved into another table. In 2NF, "If a table has a composite key, all attributes must be related to the whole key".

It still seems a bit confusing to me :s-smilie:

Anyway, I got it all from here, so it may be a little different to what you are reading/being taught...

http://en.wikipedia.org/wiki/Database_normalization#First_normal_form

Reply 4

Talon

18

You are right there I think. IIRC for 3NF you have to remove any attributes that depend on an attribute that isnt the primary key.

Reply 5

rahmed

Choad

every time i think "aha, i've got it!" i take step back, and realise that it blatantly doesnt work that way.

grrrrrrr

Haha, I remember learning about this in my last year of computing. It was long to understand, but I got there eventually using logic.

But Ive forgotten it all now.

Reply 6

Choad

OP

1

rahmed

Haha, I remember learning about this in my last year of computing. It was long to understand, but I got there eventually using logic.

But Ive forgotten it all now.

well, thats what i've got now. does that look right to you?

the cost isnt dependent on the accession number, its dependent on the supplier. and i didnt see anything that needed changing for 3nf. but catalogue number is in all 3 tables... which somehow doesnt seem right to me... or maybe it is... i dunno

Reply 7

rahmed

I would help you if I could! But the stepwise procedures are beyond me now - it was something I learnt *just* for the exams, lol.

Reply 8

Swinely

2

After staring at it for a few minutes i *think* thats right...
That second Catalogue number need to be underlined in 1NF though

Reply 9

Choad

OP

1

Swinely

After staring at it for a few minutes i *think* thats right...
That second Catalogue number need to be underlined in 1NF though

well, i sent an email to "the head librarian" aka my teacher, asking him whether accession number was globally unique, so ill decide that when i get a reply.

i think it's right too. and im glad. today, i said to myself i would get all the tables normalised, but i have so far done a grand total of 1.

yay for me

Reply 10

Swinely

2

Choad

well, i sent an email to "the head librarian" aka my teacher, asking him whether accession number was globally unique, so ill decide that when i get a reply.

i think it's right too. and im glad. today, i said to myself i would get all the tables normalised, but i have so far done a grand total of 1.

yay for me

Don't worry about it, normalisation always takes ages anyway! Hopefully it will be right though, maybe the point was to show you that you don't *have* to make changes between 2NF and 3NF? And i'm pretty sure you can have the same atrribute in all 3 tables without any problems.
My coursework project has 8 tables in.. now that was great fun to normalise :rolleyes:

.

Reply 11

o-e-s-j

2

the key, the whole key, and nothing but the key, so help me Cod*

*i've completely forgotten how to do normalising, but i seem to remember i didnt get the 3NF thing at first...

(any one know what the Cod part of this saying means? (my memory is crap!!)

Reply 12

Choad

OP

1

pig

Principle Artist(s) isn't dependent on catalogue number.

-------
- Catalogue No.
- Record Company
- Title/Short Description
- No. of CDs in set
- No. of Copies Held
-------
- Record Company
- Principle Artist(s)
-------

*cries*

edit: lol, i see you wrote out the table underneath. because of the --------- lines i thought it was your sig and didnt read it lol

you're probably right, but is it ok that an artist could potentialy be published by multiple record companies? actually... i suppose thats the whole point of putting it in the separate table! *sigh* ok is that 3nf or 2nf? thanks btw

edit: hold on, no that wont work, because in the very likely event that a record company publishes more than 1 artist, you wont be able to find out who the artist that made the cd is.... at least i cant see how

Reply 13

Choad

OP

1

pig

Sorry, I mistook what 'Principal Artist(s)' meant - I assumed artists that are associated with the record company, rather than the artists who made a recording. In that case I think for 3NF you need something like this:

-------
- Catalogue No.
- Item ID
- No. of Copies Held
-------
- Item ID
- Record Company
- Principle Artist(s)
- Title/Short Description
- No. of CDs in set
-------

Item ID implies the data I've moved to the other table, not Catalogue ID. That's what that transient dependency stuff is all about: Record Company, Principle Artist(s) and Title/Short Description are transiently dependent on Catalogue ID.

I know it all seems backwards and it really is, in practice 3NF is often a waste of time and you end up better off with more redundancy.

oh god this is doing my nut....

i got an email from my lecturer... apparently cost can change from purchase to purchase even from the same supplier. so i cant see how the table can get any more refined than 1NF without you loosing some information.

also, is my 1NF actually 1NF, because catalogue no. will be repeated. AHHHHH im going to kill myself

Reply 14

Choad

OP

1


1NF

Catalogue No.
Record Company

Catalogue No.
Principle Artist(s)
Title/Short Description
No. of CDs in set
No. of Copies Held


Accession No.
Catalogue No.

Accession No.
Supplier
Date Acquired
Cost

well i think that is a better 1NF

but again i cant seem to normalise it any further. the accession No. is basically the item ID, so you dont need to add that one in.

.... but this is madness, because then principle artist(s) will be repeated, so that should be moved out to a little table of its own, and then supplier will be repeated, and so on. eventually you just end up with a dozen tables with only 2 rows.

Reply 15

Choad

OP

1

ok, im done with that one i think. i got it good

this next one tho... i cant really see how to normalise it any further. but it doesnt seem right that 3nf is the same as 1nf

i could say that "label" is needed in a table of its own, but it really doesnt help anything. it would just be a table with label and catalogue No. in it, with catalogue No. as the primary key. label would *STILL* be repeated all the time. its wierd... you really need object oriented paradigms in this to get it without redundancy.

Reply 16

username81904

15

Why do you only have one field for address? Also label should be in a table of its own for it to be completely normalised - I think the reason you would want to do that is because it makes it limits the chance of data entry error and makes sure that the label is valid, also an oppertunity for further expansion and a method for better data analysis if you have fixed Labels. that is not normalised. should be first line second line etc to be totally normalised. wow A2 ICT helped me answer a question. Once in a thousand year occurance that.

Reply 17

rahmed

Try posting your query on here: http://forums.devarticles.com/database-development-6/

Reply 18

Choad

OP

1

cookiejest

Why do you only have one field for address? Also label should be in a table of its own for it to be completely normalised - I think the reason you would want to do that is because it makes it limits the chance of data entry error and makes sure that the label is valid, also an oppertunity for further expansion and a method for better data analysis if you have fixed Labels. that is not normalised. should be first line second line etc to be totally normalised. wow A2 ICT helped me answer a question. Once in a thousand year occurance that.

ok, so supposing i do that (and yeah, data entry/ data integrity is a good reason for doing that) then that would still come under 1NF right? which means that i still have nothing to do for 2 and 3NF which leaves me with a feeling that i am doing something wrong

oh and thanks for that link... ill be sure it check it out

edit: wait wait wait. if label is in its own table, it cant be the p key, because there will be 1 label with many catalogue no. associated with it. so it would be catalogue no. as the p key. which means its the exact same table. any table with the same p key is the same table. so there IS NO POINT! AHHHH IM GOING TO KILL SOMETHING SOOON

Reply 19

username81904

15

1NF is when a table contains no repeating attributes or group attributes. - You have done this.

2NF - There will be no partial key dependencies. - You have already done this since its been split into three and your tables only have one field that from another table that is unique. EG you havnt got surname and forename included in the accession table because you already have Borrower ID in it.

3 NF - In order for your set to be NF3 you must make sure that there are no fields that are dependent on another field in any table. At the moment the "label" should be in its own table that has only two fields in it something like labelname and label ID then you have a one to many relationship between the new label table and catalogue. Then you have 3NF i think. Label is a non key dependancy of catelogue.

database normalisation. differenct between 2NF and 3NF

Related discussions

Latest

Trending

Trending

Articles for you