Boyce-Codd Normal Form (BCNF) | Database Normalization | DBMS
HTML-код
- Опубликовано: 6 сен 2024
- For complete DBMS tutorial: www.studytonig...
In this video, you will learn about the Boyce-Codd Normal Form, which is popularly known as BCNF, with help of the simplest database example ever.
You will also learn how to design a table which follows the Boyce-Codd normal form? What are the rules and requirements for a table to be in the BCNF?
Introduction to Normalization: • Basic Concept of Datab...
First Normal Form: • First Normal Form (1NF...
Second Normal Form: • Second Normal Form (2N...
Third Normal Form: • Third Normal Form (3NF...
Fourth Normal Form: • 4th Normal Form (4NF) ...
Fifth Normal Form: • 5th Normal Form (5NF) ...
If you LIKE this video, give it a thumbs up and do SUBSCRIBE to our channel.
Our website: www.studytonig...
Facebook: / studytonight
Twitter: / studytonight
Instagram: / study.tonight
3 videos of less than 15 minutes explained my 1-hour lecture of 45 slides. Your work is amazing AND I LOVE IT!
Amen! xD
120 Likes to the comment. Wow!
@@Studytonight 531
536
45 slides, mine was 85 :| this videos saved me
I spent 2 hours watching my professor's lectures, 1 hour reading the textbook, and 15 minutes watching your videos. Guess which one of those 3 was the most beneficial?
I cannot believe how easily you explained it. You are truly a master of the world of databases.
Nếu bạn chưa coi tài liệu tôi cá là khi bạn cọi video này bạn sẽ hỏi: primary key là gì? BCNF là gì? vân vân :)) . Chỉ đùa xíu thôi. Video rất dễ hiểu. Cám ơn tác giả rất nhiều
same
tomoroow my exam I seen these video my dobt will be clear because sir you explain one example
and related on different topic amazing 😊
Everything you learn about databases is common sense. Then, they use 500 key terms and nomenclature and you forget what the hell they were ever talking about. Boiled down explanations like this are all you need. A 4 minute video that educates in a way I can't forget. Appreciate the video, a whole lot!
2024 attendance here--
May 1
may 7
may 7
May 14, I have the exam in less than 2 hours...
may 22
I recommend this playlist for those who seek concise explanations on normalization relations. This lecturer has perfectly explained all the normalization relations around 5-6 minutes, whereas a typical SE lecturer would take hours creating a huge confusion.
6 years later its still helpful to me for my college, thanks a lot !!
You're very welcome!
When the video ended, I did not expect it to be *4 MINUTES SHORT*. You have me gaGGING at how incredibly simple and educational your videos are.
Simply O MY GOD! spent 2 days understanding BCNF and this explained it in less than 5 minutes!!! can't believe. After all those non-sense bullshit with drowning into never-ending thousand's of key terms, composite, prime, super, candidate, alternate, foreign and loads of trivial non trivial dependencies, attribute closures etc. This lecturer is a GEM! U deserve at least a million SUBS!
Hahaha! Thanks. Spread the word :)
I tried to understand this with myself for weeks.... but you made me fully understood in 5 minutes. WOW
Perfect!
Thank you for this, my professors keep using functional dependencies in (A -> B) form only to explain 2NF, 3NF, BCNF, and it gets so confusing, these vids were really helpful :)
Almost four years later and this is still great! Thank you so much, this set of videos certainly helped me understand this somewhat dense material.
You're very welcome! New video on Database Transaction dropping today :) Subscribe to the channel.
I think there is a partial dependency in the example : professor column only depends on subject not on student_id so it doesn't satisfies 2nd NF
I too have the same query, what did you conclude on?
@@friday7211 Sab farzi hai 😂😂
These videos saved me 2 hours of lecture time and 81 slides of powerpoint!
All my 4 years of engineering, I have followed studytonight. I just love it. Thank you sir for this. You are amazing. YOu are awesome! Its amazing how we can learn so much from a free resource. Thank you A big thanks!!.. thank you * 100!
These videos are so simple to understand. Thank you very much for putting this into the universe!
Thank you Avishek. Share them around!
Holy shit it took months to find a good explanation of bcnf! no one else explained it in a clear way! Thank you!
thank you very much 2 lectures 3 days study trying to understand but only ur 3 small videos works with me thanks a lot
Best video till now to explain normalization in the whole RUclips.
You, my man, are a certified legend. You got the gift bro. Thanks for the great videos!
Glad you like them!
Instructions were unclear my tables are now chairs.
Hi voxeledphoton,
What confused you and turned your tables upside down?
You explained these concepts better than anyone else out there
Amazing nf series, I had surfed entire yt but no other video could beat this.
Here explanation is the fabulous
The way you explained it, my professors never could! Brilliant work, Sir! Thanks a ton. :)
Great video, the claps at the end startled me lol.
Dude you are my savior, I have exam in a few hours and this is just amazing
Absolutely love these! They are short and not boring at all. Thanks mateys!
Your videos are good as they explain these concepts with solid examples. One thing you can improve upon is to also explain why it was necessary to convert the table into BCNF. What was the problem with table before converting it into BCNF?
3:18 How does this table satisfy second normal form? Professor is dependent on subject, but not on student_id. I think it is a partial dependency.
(Student_id,subject) together determines professor attribute.
This is known as fully functionally dependent
And not partial dependency
@@_sowndarya.senthil but what's the use of having student ID inorder to determine professor ? Logically There's no link between professor and students ID. Please explain
@@-HarishkumarG Apparently, it's because a subject may have more than one professor. Subject alone is not enough to determine professor. However, I'd say it should make more sense if both subject and professor were part of a 'class' table, which would be then linked to student_id.
@@aadjxx thank you so much
ss_editz is right, because the functional dependency is professor -> subject, not subject -> professor. Given subject math you cannot tell which professor is teaching this student, there are many math profressors! Example like these are hard to come up, therefore just go alone with the fake business logic and just consider the functional dependency
Welcome to RUclips university, where complicated things from school makes it less complicated.
How does this table satisfy the second normal form? Professor is dependent on the subject, but not on student_id. So, It doesn't No fulfill partial dependency. Hence not in 2nd normal form.
no because there can be different professors for the same subject
thank you, it s the first time i managed to understand the difference between 3nf and bcnf
I wish RUclips allowed us to Like a video multiple times... Every time I watch this for revision I feel a strong urge to click on the Like button again 😁😁😁 Great work, keep it up 👏👏👏
Please do a video about tables relationships in a DB
You explain so good 👍
It is worth mentioning for the example that we assume that each professor only teaches one corse, otherwise the deconstruction would not work.
Noted! and thanks for mentioning this.
Yup, I was questioning the same thing. Thx for clarifying.
I double checked and it is actually stated in the video. Briefly, but it is there.
thanks... while everyone is just teaching B must not depend on A and blah blah... you explained like a pro...
An hour before exam, thanks for simple explanation
How is there no partial dependency when professor is determined on the subject, but not the student_id and subject is part of a comp key?
The example is wrong. There is partial dependency like you said but also subject is not a prime attribute. It should be split into more tables due to 2NF
was confused by that too
Maybe 1 subject can have multiple professors?
No, the subject alone cannot uniquely determine the professor.
There are different professors for the same subject, like Pjava,Pjava2 for java subject.
Only student along with subject can uniquely identify professor.
So it does not have partial dependency ,so 2NF
Finally I understood Normalization By ur Valuable videos...
Sir I have no word explained about your video amazing video
bro tmr is my exam it is very usefull for my sem bro thank u so much bruhhh🤗🤗😍😍
You are welcome and glad this video helped you. I hope you scored well in he exam.
watched many videos this one was on point. thanks man
Glad you liked it!
How is the table given at 3:24 not partially dependent? like the professor name only depends upon the subject id and not on the student id so that will definitely cause partial dependency to exist.
the professor name depends on both subject_id and student_id in this example, because we assumed that multiple professors can teach the same subject. Therefore given only a subject_id, we can't be sure which professor teaches it.
@@danawang780 even if multiple professor can teach same subject how come professor get dependent on student? @StudyTonight
6 hours of lectures compressed in 4 videos... and this one is easier to understand too
Pretty simple and straightforward. Thank you so much!!!!
Very Quality content. I wonder why did you guys stop making more videos. Very clean, simple and nice explanation.
Bhagwaan ho aap! Seriously god!!❤️💯🔥 Thank you so much for these videos sir! They are so so easily explained! I just can't wait to see this question in my tomorrow's dbms exam! And fill the paper as fast as u taught me! I hope to learn more from you!⭐ Keep going!!!!
We all are learning and teaching others is the best way to understand things in a better way yourself.
Do subscribe to our channel for more videos.
this tonight channel is making my tomorrow's exam simple.
What a nice explanations. Thanks for saving my life oo
Thanks for your comment :)
I have no words . Much respect👏
⚠I feel the new professor table can be not in 3NF and BCNF because P.Java2 can determine what the P.id(violates BCNF) can be and also the subject it teaches(violates 3NF).
The example instead of using "P.Java2" as a name should use for example "ABC" as a professor ABC can teach Java2 or maybe English_1 or whatever this makes the example more life like 👍, as ABC can not determine the both.
it seems a little weird a professor having two id's but not at the cost of violating the two forms. (composite key makes sense here)
another design could be using extra column for a professor (if they want to teach extra subjects)
but seems like it would create variables which wont be occupied ( i dont know how space is utilized in NULL places in RDBMS)
@Studytonight watch this please, and thanks for this amazing tutorial one of the most consistent lectures i have seen.
EDIT: OH WAIT i just realised my design suggestion would lead to violation of 2NF (only professor enough can determine other two subjects in seperate column (maybe)), of 3NF( if professor isnt primary key or part of composite) and of BCNF(as professor name can determine their ID 😭.
The above composite primary key example is much better 👍
4:09 Isn't there Transitive Dependancy now on the right table? Since Subject is functionally dependant on Professor (Professor implies Subject, proof @ 3:07), and both are non-prime attributes. That would mean that it isn't in 3NF, which means that it also is not in BCNF.
Either that or I've gotten the definition of transitive dependancy wrong. May someone explain why there is no transitive dependency in the last right table? Thanks.
For a Database to be in Boyce codd Normal form two condition must be satisfied:
1. It should be in 3rd Normal form
2. There should not be a dependency A->B such that, A is a non-prime attribute and B is a prime attribute.
How is there no partial dependency ? Primary Key is {student_id, subject} and Professor relies on only part of the primary key which is subject. Professor should not be included in the table due to 2NF
Study tonight is my favorite website
Just think after 100 years, our son and daughter would have to study and remember 1000 normal forms. I really feel sad for my upcoming kids in future.
I understand as a teacher how much effect u put for this lectures in simple way
How will it be in 2NF? Professor is only dependent on SubjectId and not StudentId.
I have the same doubt, were you able to figure it out?
oh never mind, I got it. A single subject can have more than one Professor. So, we need both student_ID and subject to figure out professor. So, no partial dependencies!
consider stdid 101 you will get your answer ;)
@@Huhwhatt thanx
Parth Patel yes that's right one subject may have more then one proffesor but it is not specified that students are divided into groups and one teacher is allocated to a group and second to other. If it would be the case then proffesor depends upon both student-id and subject-id and there would not be any partial dependency...but it is not the case..so proffessor dependes upon subject_id only...
I think..
Why are people making it so difficult to understand? This video makes it simple af
Brother! Could you explain how there is no partial dependency where the professor is only dependent on the subject right?
After BCNF, in table 1, Pid is not dependent on Student Id, so table 1 voids 2NF rule.
Hi, thanks for explaining it very clearly. I want to point out that the CollegeEnrollmentTable is NOT in 2NF since the column Professor is only partially dependent on the Subject and not the studentID. So in my view it's not a good example to explain BCNF. But if you group all the 3 columns and consider it as a primary key , then it's in 3NF since the table has no non-prime attribute.
Professor is not partially dependent on Subject. Subject does not derive Professor, since a subject can be taught by many professors (Java - PJava1, PJava2). Looking at a subject, you cannot tell exactly which professor it is taught by, since there are many.
@@dunlopgator1539 thanks it's really helpful❤
How easily you explained it. Brilliant! Thank you so much
thanks, this is special video on normalization
bro, u understand good u should also give example with data like u give in 1nf and 2nf
Ok.
Amazingly explained :)
Post other videos soon. My exams are from next month.
Waiting!
Hi there. In the final example to BCNF we can have (Student1, Teacher1) and (Student1, Teacher2) and (Teacher1, Java), (Teacer2, Java). This will imply that Student1 foloows Java twice (once with Teacher1 once with Teahcer2). I think this is not correct as the original table had a unique key on the combination of Student and Subject so the combination of Student1 and Java must be unique. Or am I missing something here?
right
thanks for the explanation, that was quick and time saving thank you
You're welcome!
The series was awesome and helpful. Thank you.
Is there another video that explains BCNF? The 1NF, 2NF, 3NF vids are superb! Thank you
Let me see what can be improved in BCNF video 😊
sir make videos on "Transaction and Concurrency control" and other DBMS important topics too. your way of teaching is exceptional.
You are born for teaching . Really good videos :D
After converting to BCNF isn't there a transitive dependency in Professor table
(p_id -> professor - > subject).
I think no, as the names of two teachers can be the same, and therefore you cannot be sure which teacher you are referring to. But the professor ID will be unique for each professor, and you can find the name and subject from it.
But... this also means that the table was in BCNF at the start, as you cannot uniquely determine subject from professor!
i was thinking the same thing
Once you break the table, Table A (Student_id, Professor_id) -> Student_id cannot be considered as primary key because one student can be taught by multiple professor. In your example, student 101 is taught by PJava and PCpp. Although you break the table you need to take care of many to many relationship.
might be a bit late for this, but if you were to understand the relation the other way (i.e., subject -> professor) that would break 2NF instead of BCNF. My question is, since normalization is a bit ambiguous sometimes, is it possible to understand it like I explained? this would therefore be true for the majority of relations breaking BCNF, just switching the attribute that derives on the otherone, right?
Thank you soo much for this great job..you explained it in such a manner that anyone can easily understand..we expect more and more videos related to cs
The video was good indeed.But should have shown the example by solving it in the table format.
Hmmm...noted!
You saved so many lives!
QUESTION: Lets assume that single professor can teach multiple subject. Would it mean that the original table / model was already in BCNF ? As this split to 2 table would not be possible, right ? More importantly the (student, subject) will not guarantee the path to professor, right?
I like your voice...... finnaly i got a good lecturer🙂
Question
Are the decomposed table in bcnf?
I dont think so bcos even now
Professor->subject
Non prime->non prime
Transitive dependency?
Please explain
Well, when we break the table P_ID is more like a representation of the relationship between Professor and Subject. Each P_ID will have a unique combination of Professor and Subject against it where may be a Professor teaches more than one subject and may be a subject is also taught by more than one professor. Hence P_ID will be the deciding factor or we can say the prime attribute in the broken down relationship.
Hope it makes sense.
Studytonight But, according to your explanation you said, in the last versiyon professors have a right to teach more than one subject.
However, if so in the first table there is no functional dependency between professor and subject since professor teach more than one subject, which means first version is also bcnf?
@@gurkandemir1643 Yes, true. When we say that each P_ID will have a unique combination of Professor and Subject that means, "Prof1"-"Java", "Prof1"-"C++", "Prof1"-"Python" all are unique combinations as the subject names are different. Hence, one professor can teach more than one subject.
@@Studytonight But you said in the video that each professor teaches unique subject. If multiple professor can teach multiple subjects then the problem of bcnf wouldn't have been arised!
East or west but SIR you r the BEST
In your BCNF example, what happens if an admin decides to update a professor's subject? Say from Java to C++. Can we still find the right score with the student, professor, and Java?
Having confusion in BCNF ....the concept of supper key ...u have used in this video
in last, when we split the table in to student & professor,
the student_id in student table is not unique ?🤔
because a student is taught by multiple professor? Then how student_id can be a primary key ?
God of dbms @studytonight
I have a doubt..
I think removing subject from student table is meaningless..
Because each student will be having many subjects..and therefore there will be many professors associated with that record..not just one..
Then how to break the table?
Should we have to use all the 3 attributes to uniquely identify the row??
I have doubts, too, but for different reasons.
To accommodate for multiple professors, you would make both student_id and p_id primary keys so you can have multiple records for the same student.
But this set up assumes each professor only teaches one subject, which we know is often not the case. Associating just the professor with the student won't tell you which subject the student is taking if the professor does teach multiple courses.
It sounds like you will end up with a 3 way many-to-many relationships. (A student can have multiple subjects and professors, a subject can be taught by multiple professors be taken by multiple students, a professor can teach multiple subjects and students)
I think it makes more sense if the table is left unfixed.
you mean this?
PK: primary key, FK: foreign key
1st situation:
PKstudent_id, FKsubject_id
PKsubject_id, subject_name, FKp_id
PKp_id, professor_name
you end up only having 1 professor per subject
or
2nd situation:
PKstudent_id, FKp_id
PKsubject_id, subject_name
PKp_id, professor_name, FKsubject_id
subject name is unique so there's no real need for subject_id, so you end up like in the video:
PKstudent_id, FKp_id
PKp_id, professor_name, subject_name
You should be my computer science teacher.
sir i have one doubt that does super key contains only prime attribute? if yes then it means if our prime attribute is dependent on prime attribute then is it in BCNF??
amazing teaching... I finally understood the normalization(riddle)
That's great Christeena. Mission accomplished :)
Thanks a lot sir!
The given example is not in 2NF as professor column is partially dependent on the composite key. Kindly check it yourself to have them corrected for the learners.
Hi Siva,
One Professor teaches only one subject, but there can be more than 1 professor for one subject. Both are different statements. Had the case been, one subject is taught by only one professor then there would have been a partial dependency. So Subject depends on Professor and can be identified using professor, while Professor can not be identified using Subject, which is also conveyed in the video.
Adding an example, if Mr. A teaches DBMS and Mr. B teaches DBMS, then DBMS cannot lead us to one entry of the professor. One professor is teaching one subject here.
Hope that makes sense.
thnku.. atlast i understood the logic of normalisation
bhai baap explanation in less time😁
Thanks bro. Glad you liked it.
I just realised the channel name is study tonight and I'm literally studying in middle of the night (its 3.30am) 😭😂
You guys are the best !!!!❤❤❤❤
In the professor table, we can also use subject to find p_id, then it will also be a non-prime to prime. Do we allow this in BCNF?
There can be same subjects with different pid
What am I missing ? Once you split the table the Student table would not even satisfy 1NF because the student id could appear more then once as some students where in two subjects . Please explain if I'm just missing something obvious . Thanks
Would satisfy BCNF a table with 2 attributes {ID_Student,ID_Subject} where both attributes conform the primary key? And... ID_Subject contains chains like(Math1,Math2,Economy,Physic...)? What is about 4FN? cause the theory with your video I understood it, but with my example... things looks complicated. Thank you
please pick my question.Why there is no partial dependency because partial dependency will occur if any of the non prime attribute depends on a part of the primary key.Here,the professor column depends on only subject and it has nothing to do with the student id.Please explain
Really Helpful.
Glad it helped.
Can anyone explain me why the last tables are in BCNF when the subject is still dependent on the professor?
Don´t we have a transitive dependency here?
Subject can be taught by multiple professors, but professor can only teach one subject. Therefore subject is not dependent on professor