วีดีโอ

When we say that a candidate key is a "minimal superkey", what we mean is that it is a superkey that contains only attributes that (in combination) ensure uniqueness. It does not contain any attributes additional to those. On this basis, there might be several candidate keys for a given table. For example, {Release_Year, Popularity_Ranking} is a candidate key, but if you add "extra" attribute Movie_Name to it, you get {Release_Year, Popularity_Ranking, Movie_Name} - which is a superkey that is not a candidate key. Another way of thinking about this superkey {Release_Year, Popularity_Ranking, Movie_Name} is that it's what you get when you add "extra" attributes Release_Year and Popularity_Ranking to the candidate key {Movie_Name}. Whichever way you look at it, this superkey consists of some candidate key plus some extra attribute or attributes.

@@decomplexify aah got it thanks for the quick explanation

Database theorists like Chris Date and Hugh Darwen nowadays consider "atomicity" to be a fairly meaningless concept. They say the notion of being "non-atomic" would be either so sweeping that it includes everything, or so subjective that we can't usefully apply it. For example, suppose there's a table that has a column called Number_Of_Members. This column has values like, for example, 27. But 27, if you think about it, really means 2 tens and 7 ones. So 27 isn't an atomic value; it is decomposable into parts. On this basis, any table with an integer column would violate First Normal Form. The same would likewise be true of any string column, because a string can be decomposed into individual characters. The same would likewise be true of any date column (which decomposes into year, month, day of month). And so on. And yet this would be absurd. This is why people like Chris Date no longer regard atomicity as a 1NF criterion, and neither do I.

To make Locker ID the primary key is to declare that each Locker can only ever have one reservation. It means once this single reservation is made, no one can reserve that particular Locker ever again (because doing so would be a primary key violation). Focus not on the rows that happen to be in the table right now, but rather on the rules we want to enforce for what rows are and aren't allowed in the table.

Kalam

I think you've misunderstood what a "primary key" is. A primary key doesn't have to consist of just a single column. A primary key can consist of multiple columns: for example, ALTER TABLE Customer_Address ADD PRIMARY KEY (Customer_ID, Address_Sequence_Number). In SQL, the only way you can prevent duplicate rows in a table is by adding a primary key constraint or unique key constraint to the table.

@@decomplexify @decomplexify Primary key is not mandatory in 1 NF. Candidate key should work based on the requirement. However, primary key is desirable... i should've been specific...

would this not work? This is what I came up with before the video showed its answer. I guess I don't understand why the 3rd table 'Element' needs to be joined. Trying to get better at thinking through this so any help is appreciated

It's probably because the question asks for "Hydrogen", should work on reading questions fully lol

Good question! If a table structure permits inconsistency, then the update that we're required to do is more subtle and involved than it would otherwise be, therefore we're more likely to make a mistake. We may in fact not even realize that such updates are necessary. I've seen the following sort of example many times in real life. We have a table that holds transactions relating to an insurance policy, like premium transactions and claim transactions. Each such transaction is for a particular policy, occurs on a particular date, is of a particular type and for a particular amount, etc. Let's say a policy has certain properties, and one of these properties is "Policy Country". The designer has decided to include Policy Country on the transaction table, and therefore the transaction table is not in 3NF. It may simply never occur to the designer that Policy Country might change, e.g. Policy Country might be entered on the policy as Spain initially, and then some weeks later it might be corrected to Italy. Since the process for writing out a transaction involves copying the Policy Country from a policy table and stamping it on the transaction being generated, what will naturally happen in this case is that the first few transactions will show a Policy Country of Spain, and subsequent transactions will show a Policy Country of Italy. This will probably come to light only when somebody tries to report transaction amounts broken down by country and gets odd results.

I think you're talking about a transaction table whose attributes are along the lines of: TRANSACTION_ID, TRANSACTION_DATE, CUSTOMER_ID, PRODUCT_ID, NUMBER_OF_UNITS_BOUGHT, UNIT_PRICE_THAT_THE_CUSTOMER_PAID. It's important to understand, however, that this table actually IS in 5NF - it's not denormalized. This is because UNIT_PRICE_THAT_THE_CUSTOMER_PAID doesn't have a functional dependency on PRODUCT_ID. If on the other hand there was an attribute called CURRENT_UNIT_PRICE, this attribute would have a functional dependency on PRODUCT_ID. The table would in this case not be normalized.