MicroNugget: How to Normalize Databases
ฝัง
- เผยแพร่เมื่อ 27 ก.ค. 2024
- Start learning cybersecurity with CBT Nuggets. courses.cbt.gg/security
In this video, Ben Finkel covers how to normalize a database. This is an essential process for structuring a database while avoiding redundancy and data integrity failures. There are three normal forms you could use, and Ben will walk through the pros and cons of each.
Database normalization is structuring a relational database in accordance with a series of “normal forms” to reduce data redundancy and improve data integrity. It’s the subject of quite a bit of discussion in the IT world. Entire college courses are taught on this subject, which can get incredibly complicated and very in-depth.
In this Nugget, Ben covers a high-level overview, digging into the concept of database normalization and discussing the three normal forms. Once you understand these concepts, you’ll be equipped to continue researching these forms on your own to discover which is the best fit for your specific situation.
📁 Download the Ultimate Sysadmin Cert Guide: blog.cbt.gg/to2o
⬇️ 4-Week Study Plan: MD-100 & MD-101: blog.cbt.gg/d2n6
Start learning with CBT Nuggets:
• Oracle Database 12cR2 Administration (1Z0-072) | courses.cbt.gg/tx9
• Oracle Database 12c Administrator Certified Associate (1Z0-071 & 1Z0-072) | courses.cbt.gg/tx9
• Intro to Databases | courses.cbt.gg/vlz
• Microsoft MTA: Database Fundamentals (98-364) | courses.cbt.gg/9ix
• Oracle Database SQL Certified Associate (1Z0-071) | courses.cbt.gg/7iq
This is the kind of video I was looking for. Well explained, Great example, and easy to follow. Thank you Ben :)
Nice and to the point. Thanks.
that was a very good quick rundown.
Quite interesting and well explained. I'd love a similar video on further levels of normalisation, it makes it really easy to learn.
Thanks man. Bookmarking this video for future reference. I got asked this in an interview today and didn't know the answer lol. I'm ready for the next time!
Sad to hear that; best of luck for the next time!
thank you very much for the support
on here for this. great video/explanation
thanks for sharing man
thank you very much. really easy to understand.
explained really well, I can't lie
thank you :)
Very nice ty
I think you would generally want to keep price inside the order table, since an item price might change but order table should keep the older price. Perhaps a better example would be item weight, assuming this doesn't change.
then this is denormalization by a redundant column
@@joaovitorgarcia4120 No, it really isn't. I don't think you understand what he's saying... Prices become historical and vary over time, and the order should capture the price that was paid for an item *at the time of the order* . You can't do that unless the price is intrinsically captured by the orders table.
Databases are a cringeworthy topic, especially in academia. People try to treat them like they're some kind of math problem with only a single, objective answer, and they're not.
5:40 If prices change frequently, the item table could end up having many entries for the same item, all at the different prices.
A solution is perhaps to include a date field (e.g. Effective Date) in the Items (or Products) table; so, when looking up for an historical order record, we can use the transaction date to retrieve the correct price from the Items table where the Price is the latest and is less than or equal to the Effective Date.
I could be wrong.
Great video - thank you.
At the end of the day, am I naively correct in saying that: if there are duplicate values stored somewhere in the database, then the data modelling is not normalised! Note: there may be times when the duplication of data is called for (e.g. historical reasons or to improve performance (derived/calculated data)).
What is your opinion?
Good content
The atomic part seem to be my problem. I still did not get it. 🤦♂️
The price is changing. When i update it. Also the old orders.items.price is also updated. Kinda a problem this atomic structure.
What is an entity, what are attributes?
I think you still need OrderPrice in the Orders table, ItemPrice would stay in items table (catalogue) as it's equivalant to a 'ListPrice', which we all know in real life may change at order stage?
Hi, Vincent! Thank you for the question. When serializing the data to make the objects coincide with table views or lookups, we want to have (as you mentioned) a relationship with the price since it can change. You would then want to use a join for example to pull the information in as needed. Let us know if you have any other questions!
Should add a column for CUSTID. Not just rely on Excel row number.
For 2NF, you say "Every non-prime attribute is dependent exclusively on the primary key." But in your example, the order price doesn't depend on the order ID, and that is in your "better" example that is supposed to be in 2NF. For 3NF, you say, "Attributes are determined only by the primary key." That's the exact same thing as what you said for 2NF. Your definitions of partial and transitive dependencies are being blurred here and 2NF ends up not being clear. What is the difference between 2NF and 3NF? If your definitions were correct, there would be no difference between 2NF and 3NF.