Database Design 38 - 3NF (Third Normal Form of Database Normalization)

Start your software dev career - calcur.tech/de... 💯 FREE Courses (100+ hours) - calcur.tech/al...
🐍 Python Course - calcur.tech/py...
✅ Data Structures & Algorithms - calcur.tech/ds...
~~~~~~~~~~~~~~~ CONNECT ~~~~~~~~~~~~~~~
✉️ Newsletter - calcur.tech/ne...
📸 Instagram - / calebcurry
🐦 Twitter - / calebcurry
🔗 LinkedIn - / calebcurry
▶️ Subscribe - calcur.tech/sub...
👨🏻‍🎓 Courses - www.codebreakt...
~~~~~~~~~~~~~~ SUPPORT ME ~~~~~~~~~~~~~~
↪ My Amazon Store - www.amazon.com...
🅿 Patreon - calcur.tech/pat...
🅖 GitHub Sponsors - github.com/spo...
Ⓟ Paypal - paypal.me/calcur
🅑 Bitcoin - 3HnF1SWTzo1dCU7RwFLhgk7SYiVfV37Pbq
🅔 Eth - 0x350139af84b60d075a3a0379716040b63f6D3853
📈 Buy Bitcoin - calcur.tech/cr...
Reserve the Ruby Steel crypto rewards card and get a $25 bonus (use affiliate code "Caleb") - calcur.tech/cr...

[@raressimtion1]

Dude you a are lifesaver all the courses at my uni don't come near to 20% of how clear you explain the 1NF,2NF,3NF Good job man!

[@milad5554]

University learning method Sucks!

[@No_you_dont_grandma]

I have a presentation in like 20 minutes and these 3 videos came in clutch😂😂🔥🔥‼‼‼

[@vamsikolla5669]

Thanks for the explanation can u do video on !!BCNF!!

[@justinmaddox795]

@@walibou287 Tell that to my database professor.

[@EminoMeneko]

@@walibou287 Practical or not. Rare or not. It is always a good thing to know about it.

[@war1us11]

Nice video! You still have a transitive dependency in the Star table. The star meaning depends on star, which in turn depends on star id. You'd have to lose the star meaning column and compute it somewhere else based on star in order to have your database 3NF.

[@codebreakthrough]

I appreciate your comment! I actually disagree, though.

[@war1us11]

Thanks for replying! How come though?

[@codebreakthrough]

Well, if you considered the channel without the ID column, it could still be a valid table with a natural key. And all of a sudden we aren’t breaking any rules. The star id is a surrogate key (star id) which has no real world meaning, so I try not to think about it too much. Kinda hard to explain here in a comment, but essentially the dependency is on the entity, which is a star. All of the columns describe an individual star. The id, the value, and description. In theory you could extract to another table, but it doesn’t serve the purpose of database design which is to remove redundancy and prevent anomalies. Also I didn’t watch the video before commenting on this so I hope I got all the details correct. It’s hard to remember everything from all of my vids 🙂

[@ReneeSLiu-zx5tj]

@@codebreakthrough Should it be only separating out star meaning?

[@dr.octogon666]

@@ReneeSLiu-zx5tj i didnt get the point ,as though why the dependency is bad ,its good that if a review id changes the review changes and so the stars .....also star is dependent on review and then on review id plus review id is surrogate ? so many questions man

[@sparshsharma618]

I took classes and read books to understand this concept, but I remained totally blind to the fact that the whole explanation to the 3NF lies in the words 'transitive dependency'. You made me aware of it through such simple explanation. Many thanks!

[@abidshaikh4473]

haha you are Nerd. I love the way you teach.Its fun

[@leighhurley9410]

I went through like all our teaching material to try understand 2nf vs 3nf and I got it straight away from this. Thanks man

[@emilyreed4586]

"This is known as a transitive dependency which is BAD!" LOL.

[@EminoMeneko]

Well... I'm not sure it was clearly explained why transitive dependency is bad but at least the concept is so far the one that explained it in the clearest manner. Thanks for that. You see it seems so cryptic and difficult until someone succeed to explain simple things in a simple fashion. Really you have no idea how grateful I am every time stops on relying on complex wording which actually hide simple concepts and actually explain things. Big 5 ⭐.

[@jeffmiller7108]

OMG...My professor and school suck. You did an amazing job in just 8 minutes compared to what they can't do in their lectures, videos and an entire book.

[@Iliumin]

Just wanted to say thank you for this series. I was very lost until I watched this and you broke everything down into very simple explanations that were easy to follow. Also, go lefties!

[@codebreakthrough]

Lefties unite!!!

[@seanarchard]

There's no need to have a star_ID and star column - they're the same

[@milad5554]

Maybe he used the start_id for better understanding , but thanks for your mention

[@warunadevaka406]

no

[@ashtreylil1]

currently failing my database online class because its only powerpoints and little explanation or interaction. you have helped me though this chaper. hopefully i can pass and not ruin my 4.0 completly.

[@pooh2693]

Awesome video in basic English. Me as a german just with basic English knowledge understood it completely. Very nice

[@sylmacg1]

I finally understood with simple explanation! "This is known as a transitive dependency, and it is bad", lol, funny too.

[@partspieces8165]

So kinda like what u said in the lookup table video? Dude has got great explanations & is officially 1 of my professors in KZbin University

[@bensadeghi8624]

Given R(a, b, c, d, e, p, w) two keys: (b, c, d) and (p). F ={ {b,c,d} -> {a,e,p,w}, p -> {a, b, c, d, e, w}, w -> c }. Is R in the 3rd normal form?

[@salem1cat]

Thank you for clearing up the confusion caused by the horrible textbook I'm forced to use! You are amazing.

[@LiomieKurera]

Great One! I searched a lot for a better video for normalization and I was fed up with Indian videos. It's better to explain this way taking real examples than explaining with definitions and all.

[@ranikheir5272]

Awesome videos! Thanks a lot for them :) Simple question, do we really need to have star_id? Can't we just have tables with those instead: Review: review_id | review | stars | user_id Star: stars | star_meaning (stars being the PK in Star and FK in Review)

[@joegranados703]

Question: Since the Star table has star_id, don’t we now have a transitive dependence in the star table?

[@dr.octogon666]

i dont get it whats normal even left after removing all dependencies ,bcuz as soon as u add a key every column of the row is dependent on something else......

[@ramesh-dk9nr]

5:57 Hasn't the transitive dependency just been carried from Reviews table to Star table? Doesn't the new Star table now has the transitive dependency within it now? Any ways to remove it?

[@TheFinalCutBG]

What is the difference between the lookup table in your 22nd video of the series and this one here? Or back then you were just explaining the concept, without telling us what is called? Also why is Star dependent upon Review id ? Probably you can use the same star rating entity while rating something other in different table (for example book orders, stored in the same DB). I'm aware that these are quite abstract conceptions, but again I'm just beginning to study DB with this playlist and I want to learn to differentiate the terms.

[@stela1343]

I just added your Normalization videos to my Moodle (college coursewear) page, under "additional resouces," for the 300-level database class I am teaching at a private university. These are great explanations--good work."****!"

[@DruMUSIC06]

Forgot all about this. Good thing you have some awesome videos about it. Thanks man!

[@skylerwaite]

All you did was take the transitive dependency (Star_meaning-->Star) and move it to a new table. I am a beginner so excuse my ignorance, but how is this 3NF? Also, star meaning is not dependent on the star rating. The meaning would not change based upon a user's rating.

[@toopided]

Nice! Clear and good explanation given. Will you be doing 4NF and above?

[@codebreakthrough]

Maaaaaybeeee.... Thanks! I'm glad that it was clear because I find that a lot of videos covering this topic are confusing.

[@NoodleGodGaming]

Thanks dude. Skipped class and these videos got me all caught up haha

[@hm2OO5]

can't you just use "star" field as the primary key of the star table and omit star_id field?

[@Wickedroonz]

seem like there is still transitive dependency in the example he gave because star meaning still depends on star

[@FewCode]

​@@Wickedroonz This is actually an issue in understanding his initial explanation. in the first table he makes, star_meaning is dependent on star. For example, if the user edits their review and changes the star, then the meaning of the star would change since the rating went up or down. So he moves that to another table. The new table is more of a "reference" table than anything. In the star table, if you were to edit the star, the meaning would not necessarily change. Since the star table is a reference table, the table unlikely to change unless it is a fix or simple edit. so the meaning would still be correct. For example, maybe you had the stars as numbers from 1 to 5, but then you changed it to emojis (for some reason) of stars *,**,***,****,*****. The meaning would not change here.

[@RubiixCat]

@@Wickedroonz That's fine. We're not looking to get rid of dependencies altogether (they're pretty much unavoidable). For 3NF we're looking to get rid of *transitive* dependencies. For Example: A -> C (A directly depends on C. This is ok) A -> B -> C (A depends on B, which depends on C. This is a *transitive* dependency and therefore is *not* ok) In this case, A = star_meaning, B = star, C = review_id

[@ReneeSLiu-zx5tj]

Thank you for this tutorial! Maybe I am confused. How is it NOT transitive anymore if moving star and star meaning to the other table? The new star table still contains star meaning which relies on star which relies on star id? Can someone check my understanding here, please?

[@lidiaphilo1808]

thx!!!! it really helps me for my exam tommorow!!

[@YisraelDovL]

Ok, so why not use `star` as the PK in the stars table? Seems like star_id is redundant here and less verbose.

[@Mintmintg]

Hello, I have a question for the 3NF. Can we use one primary key to get two different answers in 3NF? For example, we need to know the relationship between professor and class code. but what if one class is taught by two professors. Class 111# is taught by professor A and professor B. Can the class code be only the primary key to get the professor name. so when you search class 111, you can get answers A and B. or we need to use a composited key, by using "class 111 + another key" to get the answer "professor A" , and using "class 111 + another key" to get the answer " professor B) . Thank you very much.

[@Hirenji]

YOURE A LIFESAVER

[@windstoner]

I have a question. Lets say we have a table "request" and there is an "approval" fields(ex. Remarks, status), should we separate approval in separate table? Lets say there are 5 levels of approvals with different inputs fields then request data will be on the 5 tables?

[@kendra1908]

So glad I found your channel. Your videos are helping me better understand the material in my Database Processing course. Thank you. Thank you. Thank you!

[@realisticgaming4947]

I have a question, in the newly created star table, starrmeaning is depends on star and star depends on star id, isn't that also a transitive dependency ??

[@gabrieldediego8579]

+Jani Pro101 star_id is simply a reference in indicating a unique row. However, you may be correct depending on how star_meaning is used. If star_meaning has set values that it must depend on, then yes, we would have to create a new star_meaning table with a foreign key on the star table to reference these values. If star_meaning is meant to contain literally anything than it is fine the way it is. I think I am correct with this analogy, but if I am not please correct me.

[@jefflong1799]

Shouldn't you have a "BookID" column in your Review table (so we know what the heck we're reviewing, lol).. Also, REALLY glad you summarized at the end. :)

[@cholowao]

i just loved the fade video editing

[@016mbo]

Could you make a lecture on the Boyce-Codd normal form as well? Thanks for really great videos and explanation!

[@misou1995]

a quetion please : for the 2NF normally we'll be avoiding every attribute that doesn't really depend from the primary key of the table don't we ? so i mean the meaning star shouldn't be there by the 3NF ,i hope i can get an answer :)

[@jeramiahsherman971]

Thanks for taking the time to do this bro!

[@mideff96]

it's a rectangle, caleb. a rectangle ! 0:37

[@fredzrich]

you just made 3NF so easy. MANTIS!

[@milandukic6959]

Your videos are awesome and you are an excellent teacher. Thank you for sharing your knowledge. Can you please explain the Boyce-Codd normal form and the fourth normal form?

[@medinamariam5405]

Do you really need a star id? Why cant just use star as the primary key for star table since its already in numbers..

[@fran9519]

But now don't you have a transitive dependency under the Star table, with the attributes Star and Star_meaning?

[@Steven-wo9bm]

Dude keep this videos coming , really love them. Keep up the good work

[@kurisumakise5641]

Why not make a bcnf video thatll also help:) PS you were great explaining things

[@butterflyfly7022]

Ur great man ! Wish I had teachers like you

[@meenafazal2854]

Thank you Caleb!!Your videos are very helpful!!

[@KenJASMR]

Thanks! So nice. Can you make a video for BCNF?

[@merajis]

Awesome! Thank you Caleb

[@BeardTech]

This was rad. I like it. Keep doing them!!!!

[@ajanthanthiruchelvam2542]

Thanks for the video! Really good explanation.

[@aMwWSpHhHdn2]

Hello. Thanks for the informative videos. I had a question. Could initials also be described as being transitive? Your initials are dependent upon your Name which is dependent upon personID. If someone changes their name then their initials would change also. Or is my example flawed? Trying to come up with my own example here. Thanks!

[@TobyMellor]

Would you store initials along with the first and last name anyway? There's no need, the program should just take the first letter of both the first name and last name to return the initials

[@eboxwilson99]

Throw back to the chalk board! Haven't seen one of these bad boys since middle school. But anyways great videos man keep up the good work!!

[@adamcallinan3125]

Really good explanation. Can't thank you enough!

[@ivankontra3446]

Didn't you create a whole new transitive dependency with the star table? Star meaning > Star > Star ID

[@codebreakthrough]

Star ID is essentially redundant, just following the surrogate ID convention. Just see it as Star meaning > Star

[@coolestcatintown1501]

Thanks Caleb, very informative video

[@Jackismycopilot]

Thanks dude. Good stuff.

[@jamalbutt7864]

Hey Caley Curry, Can you please recommend me a book on DB. Anything which has similar sort of examples like you are giving? Thank you

[@johnbritolima]

What would be the relation? 1:1, 1:n?

[@alainbusokoza2874]

Thanks a lot, your videos are very helpful! (It would be nice if you could make a video on BCNF as well.)

[@hafizmfadli]

Why trasitive dependency is bad ?

[@bhavanakumar5434]

WHY is transitive dependency BAD? Could u explain with example hope to hear from u asap - i have my exams coming up next week i have watched this full series great videos - u made difficult concepts simpler mostly by using examples thanks a lot(subscribed)

[@JurgHurg]

+Bhavana Kumar But I think the other issue which the uploader describes is that it faces update anomalies, like in the video: IF 'StarID' is changed in the review table, it DOES NOT mean 'starMeaning' will be updated also, BUT if it references 'starID' from a DIFFERENT table (Created in 3NF) then the 'starMeaning' update anomaly won't occur since the 'starMeaning' attribute will correspond to the 'starID' in that table. so awkward to explain lol

[@davidclough8671]

Assuming the star internally is an integer 1 thru 5, why not just make that the primary key in the second table?

[@ivankontra3446]

Pretty sure that's what you actually do. I think he created a new transitive dependency with the second table, surprised nobody is mentioning it. But you've probably passed the class.

[@RafayKhanfy]

The star meaning is still dependent on star in the star table.

[@patrickwalsh4588]

no, it depends on the primary key star_id. Both star and star_meaning now depend on the primary key star_id.

[@DakotaGraftt]

3NF only excludes certain forms of transitive dependencies, but you are right, starID should be gone and star should then be the primary and foreign key.

[@xbc2000_]

Best video on the topic

[@TheFrozenHeart5794]

short and simple.Thank you :)

[@LaurentMeyer]

I think it was really super ok (I really laughed at this one!). Go on, mate!

[@borisjacimovic7390]

This is awesome!! Thanks

[@SupunWiratunga]

Thanks mate...

[@nazanhacyeva2439]

thank you very much!!

[@fredriklindholm9046]

Hi, I know i am 4 years late.... But, isn't it unnecessary to have Star_ID and Star in the Star table? Can't you just have Star(PK) and then star_meaning for each star(PK) and then have Star as a FKey in the review table?

[@codebreakthrough]

This could work. Often people add ID by convention.

[@codebreakthrough]

And wow, took you long enough 😂

[@fredriklindholm9046]

@@codebreakthrough Sure :D Just making thins clear for myself, examination tomorrow :3

[@yousteveaaaa]

thanks !! great videos !!

[@jyothish99]

Can you do BCNF ?

[@globalmacro2817]

thanks for the video!

[@eljazzmusic1939]

Thanks man great video

[@had2blunts960]

better thn my teaher ..good bro

[@vigneshmohan3960]

Normalization concepts are made simpler by you

[@darthbleen984]

Is starid foreign key in review table?

[@edwardanthony7134]

Wikipedia sucks! Caleb rocks!

[@coldzeroan]

wouldn't the transitive dependency appear on the star table also ?? cuz now star meaning attribute also depends on star attribute which depends on star id

[@terminallumbago5582]

+Anas Jubara Not really, since the star_id has no real bearing on the star/star_meaning. It's just a unique reference. Doesn't make sense to update primary keys anyway unless you want to break your database lol. I'm not even sure if primary key constraint allows it.

[@기바랜드]

I have the same doubt. As I watched the lecture, I thought it'd be more appropriate to create table which only referring the star as foreign key while reserving the star in the previous table. Any special advantages for making new column (star_id) as primary in a new table?

[@94aishah]

thank you! your videos are very helpful for me. ^^

[@azizas9366]

thanks

[@Acyett]

Chalk??? Lit!

[@neer2890]

You didn’t tell why transitive dependency is bad?

[@maomao3413]

thank you caleb

[@wesnkrissytoehay792]

Bruh! You totally just saved me! Thanks for this. Keep it up. Liked. Sub'd. and Mayonnaise.

[@hghlndr2]

5 star video ;)

[@MrPetzle]

Thank you so much for the help. I love the jokes.

[@dhrIsaac-jh1ix]

Thanks nerd!!

[@btrain9912]

great

[@ibRebecca]

OMG Thank you so much, I love you so much

[@spacepod100]

can anyone explain what he means by start relies on review id

[@codebreakthrough]

+spacepod100 Regarding foreign keys? They reference a primary key, meaning the value of the foreign key for a row must be a value that matches a row in the primary key column.

[@JurgHurg]

+spacepod100 StarID has no reason to exist or be set unless associated with a reviewID ReviewID determines StarID which determines StarMeaning R -> sID, sID -> sM sM is transitively dependent on R; AND sID is functionally dependent on R

[@JurgHurg]

Good video :)

[@flydipdrop]

Also...a review title!

[@bam22bb]

You're a boss

[@nurulaisyah7877]

thank you so much! it really helps ToT

[@YumanoidPontifex]

transitive dependency explained by renly baratheon. it doesn't get any better :).