normalizing a database to 1nf, 2nf, 3nf, bcnf?

Question

I know the basic rules for each step but i find them very confusing when i have to apply them. I'm working on this example right now

This table is used for a car hiring company. Customers can hire cars from different outlets, a particular car is available at a particular outlet only and can only be hired to one customer on a given date. The 1nf table has fields:

CarReg, HireDate, Make, Model, CustNo, CustName, OutletNo, OutletLoc

Primary keys are (carReg, HireDate)

Can someone please talk me through converting this to bcnf step by step? and the results produced at every step? thanks

My answers:

2nf

CarReg, HireDate, Custno, CustName (pk: CarReg, Hiredate)

CarReg, Make, Model, OutNo, outLoc (pk: CarReg)

3nf

CarReg, HireDate, CustNo (pk:carReg, HireDate)

CustNo, Custname (pk:custNo)

CarReg, Model, OutNo (pk:carReg)

Model, Make (pk: model)

OutNo, outLoc (pk: OutNo)

Are my answers right? and how do i convert this to bcnf?

Answer 1

You have to know the definitions of all the NFs you listed well in order to do that. So let's recap:

• 1NF: Simply put -- we want no multivalued or composite attributes on our table together with others. So we separate them to a new table, attaching a key to connect the original table with the new one.

• 2NF: This one is a bit tricky but here's how you can do it: If there is some dependency on a composite key, it must be to the entirety of the key and not just some of it. This is only important if you have a composite key in your table.

• 3NF: This one says we can't have transitivity in our attributes of the table. What this means is this: Can I know from one attribute, all other attributes regardless of the key? Example: Suppose you have a restaurant, and a table for the Meal. Meal has what in it? A price, name and let's say an ID. From the name of the meal, assuming the meals have unique names, you can find the price and the ID. ID is assumed to be the key, so this isn't allowed in 3NF. But suppose now that you have a table for your Customers. Suppose it has a unique number, name, address and a contact number. Now, if we assume the unique number to be the key, can you find from their name, the other attributes? What if they have the same name? Or even the same address? Clearly these aren't transitive in this case.

• BCNF: In short, this says that for any kind of dependency, it must be only on the key and the key only. Example: Suppose you have a table for the bills in a restaurant that has the following: bill_number, customer_number, waiter_ID, meal_number and a description. Suppose everything but the description are keys and unique. Look what happens now: waiter_ID depends on the bill_number, so we can find it through that. We don't even need the meal_number or the customer_number and vice versa. The thing is that there is some unnecessary relationships here, so they must be separated further as the dependency is not on the entire key. (Notice that the description however depends on all of them. Maybe the same waiter took the same customer's bill of the same dish. See what happens in this case? Only bill_number can be used to identify)

I'm a bit rusty on these topics but these shouldn't be too wrong. Overall, if you understand the definitions properly you should be able to do just fine though. There are many good examples on youtube from what I've found.