Suppose that some relation satisifies a non-trivial functional dependency of the form A->B, where B is a nonprime attribute. 2NF is violated if A is not a superkey but is a proper subset of a candidate key 3NF is violated if A is not a superkey You have spotted that the 3NF requirement is just … Read more
Although the question is old, the other questions/answers don’t seem to provide a very clear step-by-step general answer on determining and decomposing relations to BCNF. 1. Determine BCNF: For relation R to be in BCNF, all the functional dependencies (FDs) that hold in R need to satisfy property that the determinants X are all superkeys … Read more
Well, if I had to explain it to my wife it would have been something like that: The main idea is to avoid duplication of large data. Let’s take a look at a list of people and the country they came from. Instead of holding the name of the country which can be as long … Read more
A philosophical answer: Sub-optimal (relational) databases are rife with insert, update, and delete anomalies. These all lead to inconsistent data, resulting in poor data quality. If you can’t trust the accuracy of your data, what good is it? Ask yourself this: Do you want the right answers slower or do you want the wrong answers … Read more
The difference between 3NF and BCNF is subtle. 3NF Definition A relation is in 3NF if it is in 2NF and no non-prime attribute transitively depends on the primary key. In other words, a relation R is in 3NF if for each functional dependency X ⟶ A in R, at least one of the following … Read more
I try to attempt to explain normalization in layman terms here. First off, it is something that applies to relational database (Oracle, Access, MySQL) so it is not only for MySQL. Normalisation is about making sure each table has the only minimal fields and to get rid of dependencies. Imagine you have an employee record, … Read more
Your senior colleague is a developer, not a data modeller. You are better off starting from scratch, without them. Normalisation is complicated only to those who will not read books. It is fair enough that he makes you think, but some of the issues are absurd. Your numbers: You need to appreciate the differences between … Read more
The two most common reasons to denormalize are: Performance Ignorance The former should be verified with profiling, while the latter should be corrected with a rolled-up newspaper 😉 I would say a better mantra would be “normalize for correctness, denormalize for speed – and only when necessary”
It’s true that it’s not a good idea to denormalize into JSON, but sometimes you need to deal with JSON data, and there’s a way to extract a JSON array into rows in a query. The trick is to perform a join on a temporary or inline table of indexes, which gives you a row … Read more
Another common Name for this model is the Supertype Model, where one has a base set of attributes that can be expanded via joining to another entity. In Oracle books, it is taught both as a logical model and physical implementation. The model without the relations would allow data to grow into invalid state and … Read more