Database Normalization in SQL - 1NF, 2NF, 3NF, 4NF - SQL Training Online
Vložit
- čas přidán 2. 08. 2024
- Database Normal Forms with Examples.
In this video I show you how to normalize your relational database.
What is covered:
--1st Normal Form and Atomic Values
--2nd Normal Form and Partial Dependence
--3rd Normal Form and Transitive Dependence
--4th Normal Form and Multi-Value Facts
I start with smaller examples for 1NF, 2NF, 3NF, and 4NF. Then I move into a more comprehensive example that uses all of the normal forms together.
TABLE OF CONTENTS
00:00 - Intro
01:07 - 1st Normal Form
4:18 - 2nd Normal Form
9:44 - 3rd Normal Form
13:50 - 4th Normal Form
17:33 - Normalizing Customer Orders
20:35 - Customer Table (2NF)
21:55 - Product Table (2NF)
24:20 - Order Header vs Order Lines
29:05 - Product Table (3NF)
31:25 - Customer Table (1NF, 4NF)
DOWNLOAD THE EXCEL FILE HERE
www.sqltrainingonline.com/SQLN...
READ THE ORIGINAL ARTICLE WITH EXCEL FILE HERE
www.sqltrainingonline.com/sql-...
CZcams NEWS UPDATES
/ sqltrainingonline
VISIT SQLTRAININGONLINE.COM FOR VIDEOS & TIPS
www.sqltrainingonline.com
SUBSCRIBE FOR OTHER SQL TIPS AND NEWS!
czcams.com/users/subscription_c...
SUBSCRIBE TO OUR EMAIL LIST!
www.sqltrainingonline.com
LET'S CONNECT!
Facebook: / sqltrainingonline
Twitter: / sql_by_joey
Linkedin: / joeyblue
SQLTrainingOnline: www.sqltrainingonline.com - Věda a technologie
TABLE OF CONTENTS
00:00 - Intro
01:07 - 1st Normal Form
4:18 - 2nd Normal Form
9:44 - 3rd Normal Form
13:50 - 4th Normal Form
17:33 - Normalizing Customer Orders
20:35 - Customer Table (2NF)
21:55 - Product Table (2NF)
24:20 - Order Header vs Order Lines
29:05 - Product Table (3NF)
31:25 - Customer Table (1NF, 4NF)
nee.
@@Kipviss .
That's brilliant, confused by a textbook but fully normalised now!
I have been struggling with this concept all week in class. I finally get it. Thank you so much.
Great explanation! It's obvious that you actually understand the topic well, and you're not just spewing out definitions. I've had some difficulty understanding these concepts until now
Thank you for explaining this so clearly. I like the focus and the delivery.
This video will top the search results in youtube on "DB Normalization" going forward.
Excellent video and clear explanation. Can't get better than this!
Thanks a lot!
Your explanation is absolutely easy to understand, examples are perfect.
I really like this video. It was so much easier for me to understand normal forms by watching this video instead of reading my book. Thank you so much!
Really useful information. Helped me with my concepts. Thank you so much Joey.
You did a great job teaching a complex topic in an understandable way! Just subscribed to your channel and expect you to get many more in the coming months because you’re producing quality content-it really stands out from the other videos on normalization. Keep up the great work!
Best explaination i have seen so far in any book or video!. Hats off. Great example and walk thourgh
Thank you so much, you just make it easy for me to understand, that what a perfect teacher looks like.
I'm a total noob on this topic and watched many videos about database normalization, this is the simplest explanation on the topic and the one that helped me the most improving my database structure. Thanks a lot!
Thanks. Very easy to digest explanation on normalization.
Great video Joey.
Simply great!
Thank you! This was an excellent format for a visual learner like me!
Very well explained. This video tied up a loose ends for me. I already knew how to break apart one table into other tables, but this explanation really helped me to 100% confdent. Wouldn't it be a good idea to put 'Product Manufacturer Address' into it's own table in case the product is manufactured in multiple locations? If so, then what would be the key to the 'Product Manufacturer Address' table?
Great, concise video! Thank you
excellent work Joey
awesome video. Great explanation.
Thank you so much. your video is really useful for me
You're a great teacher - thanks for making this available to everyone.
love this one very much!!!!
Thank you so much for this video.
The absolute best !!!! Saving me from the land of total confusion!!!
Thank you so much , this helped me a lot , keep it up plz
Great, thank you!
Joey, Thank you so much for helping us the little guys.
Brilliant ,,,,,lecturer
This is good. I'm new to this therefore, I'll need a week to design my first database...lol
Very well explained
Thanks for this video
I cannot thank you enough.
Great explanation of the topic. Thank you for teaching us :)
Excellent job
Good explanation. After seeing the video, I felt like using pivot tables in Excel could speed up the process.
Thanks so much for the informative video. This is much more clear and concise than the slides and book I've received from my database class.
Thank you a lot, you are different!
Great! Thanx!
Thanks for this
That was Excellent ! I guess I’ve been lucky with not having so complicated data. Thanks, Sgt. Dave
Your tutorials are of no doubt ....
Kindly make video(s) on "Entity Diagrams"
Great stuff.
How would you deal with price changes over time? Do you need a separate Price table with dates or something?
Would be great to get your input.
Thanks, appreciate your content.
Excellent
Pls tutor java,c#,assembly. Only one ofcourse. I only saying this
b/c u helped me a lot thank you very much.
Thank you! the best explanation I have found on the net!!
thanks answered my question🙂
Thanks Joey, you're a great teacher.
nice tutorial
Extremely helpful! Thank you Joey!!!
Hi Joey,
Do you offer training beside the online videos? you are good at this.
Thank you Joey, very helpful!
Awesome
waw impressive
It was very well explained with example, thank you
Nicely done Joey! Thank you...
THANK YOU this helped so much!
The best one that i have seen!!
Excellent Video.
This video is quite good. It would be great if you could keep doing more of these Database theory videos.
Thanks for the video, Joey! Pretty good explanation. I got with one doubt in the end. What is the PK for the Customer mail catalog and the customer email subscription tables?
@Joey Blue Thank you for your videos. They are very helpful. Now about this specific video. Is it necessary to have a separate Customer order lines table? If we had an orders table with the product id as a 2nd FK and the quantity, wouldn't we also be in 4NF?
It all depends on what else you are storing in the orders table. Many times there are things that you want to store at the Order level. Maybe a flat Handling Fee that doesn't depend on the Product. You would need to figure out where to store that information without repeating it on every product line. There are many ways to do it, you just want to keep in mind the grain of the table and if you are unnecessarily repeating data.
Joey, please help with the download link for SQL server. I am starting SQL learning from your video.
Just awesome man!!!
In the section explaining 2NF, when deduping the Product Table, you got rid of two rows. The first I understand (Bat, Louisville Slugger) because it was a duplicate. However, you also got rid of (Basketball, Titleist) even though it wasn't a duplicate. Why? Great video!
Thanks for the video!
You’re amazing!
Great video, Thanks!
Great stuff! Very helpful. Not to be picky but in trying to understand the "solution" I noticed (in the original large table) that Bob Smith made two purchases on 8/9 and the Order Total didn't add up to the total of the two lines. The total in the chart is $70...shouldn't it be $60 or am I missing something? Also should the "Customer Email Subscriptions" and "Customer Mail Catalogs" tables be JOIN tables? It seems that there is a Many-to-Many relationship between these tables and Customers (e.g.,, Customers can subscribe to Many Email subscriptions and an Email subscription can belong to many Customers). Seems like the classic situation for a Join table. Also currently the way you have it defined there is no primary key for these Email and Mail tables (as there are duplicates). Not sure if that is an issue. Thoughts?
1. Thanks for your video!
2. One of the "Basketball-SP" records under the "Products" table needs to be deleted.
3. Price for "Basketball-SP" needs to be updated in the original table ( @16:49). It should be either $23 or $25.
4. Order Totals for "Customer Order ID" #1 and #4 don't look correct ("Customer Orders" table).
Thank you, that was helpful
In this video 20:16 how did you sort product key. if we double click on that will it save all the rows
Our teacher showed us this video.
I am the luckiest to be here.Thanks Sir
But how are Dozen, Pack, Inch and $ atomic when they are combined with numbers?
hows does the qualntity depend on the order date? Can we just notpull the quantities using customer id and product?
Do we need to split the Customer address in example 1 to make it in 1st normal form as address seems to be composite attribute?
Isn't the Order Total field a Calculated field? Can it have a place in a table? If yes, how was it calculated here to show the results it exhibits here? Newbie here, thank you :)
Could the customer order table be further normalised to the 4nf by pulling out the customer order and order date?
Hi Joey, Thanks for the very instructional video. I do have a question. What do we do with the two ProductID Basketball-SP with different prices?
I think that was an oversight. They should either be the same price and then one of the rows would be deleted or the basketballs should be named to differentiate them (in the product column) like Basketball-yellow and Basketball-Orange.
You just add a different Product ID for the differently priced one.
one of 2 records needs to be deleted
That's how you explain normalisation.
Glad I found this.
Big thanks from Dublin, Ireland! :)
which application are you using for this lesson?
Thank you very much.
Hi sir for the 8:30 part the basketball are spaulding and Titleist, but where is the Titleist after u delete those stuff, and the customer order twice, then the price should be 2x isn't it? but when you separated it, it is still the same price for one, I am confused. By the way thanks for the video😊😊😊.
Normalization might as well have been mandarin before now, thank you!
sir my db is in mysql using MYISAM.... No foreign keys implemented with some columns repeating in tables as well... i have approximatelly 40 tables and all containns approx 500 rows.... but 5 or 6 tables contains above one million rows each....db is not normalized.....but it is still working fine...on LAN and on cloud as well ... what will be fuiture of my db...
How does product manufacturer depend on product if there is a manufacturer that has 2 diff products
Just wondering have I wrote the SQL correctly for this? Many thanks, Robert
CREATE DATABASE OrderLine
/* Create this table 1st */
Create Table ProductManufacturers(ProductManufacturer varchar(50) Primary Key ,
ProductManufacturerAddress varchar(50))
Insert into ProductManufacturers(ProductManufacturer,ProductManufacturerAddress) values('Spaulding', '1 Spalding way')
Insert into ProductManufacturers(ProductManufacturer,ProductManufacturerAddress) values('Louisville Slugger', '345 Slugger Avenue')
Insert into ProductManufacturers(ProductManufacturer,ProductManufacturerAddress) values('Rawlings', '23 Rawlings Court')
Insert into ProductManufacturers(ProductManufacturer,ProductManufacturerAddress) values('Titleist', '1234 Titlest Road')
/* Create this table 2nd */
Create Table CustomerEmailSubscription(CustomerID varchar(100) NOT NULL,
CustomerEmailSubscriptions varchar (100),
Primary Key(CustomerID,CustomerEmailSubscriptions))
Insert into CustomerEmailSubscription(CustomerID,CustomerEmailSubscriptions) values('bsmith1', 'Baseball')
Insert into CustomerEmailSubscription(CustomerID,CustomerEmailSubscriptions) values('bsmith1', 'Basketball')
Insert into CustomerEmailSubscription(CustomerID,CustomerEmailSubscriptions) values('jthomas1', 'Baseball')
Insert into CustomerEmailSubscription(CustomerID,CustomerEmailSubscriptions) values('bsmith2', 'Golf')
/* create this table 3rd */
Create Table CustomerMailCatalogs(CustomerID varchar(100),
CustomerMailCatalogs varchar(100),
Primary Key(CustomerID,CustomerMailCatalogs))
Insert into CustomerMailCatalogs(CustomerID,CustomerMailCatalogs) values('bsmith1', 'Baseball')
Insert into CustomerMailCatalogs(CustomerID,CustomerMailCatalogs) values('bsmith1', 'Basketball')
Insert into CustomerMailCatalogs(CustomerID,CustomerMailCatalogs) values('jthomas1', 'Baseball')
Insert into CustomerMailCatalogs(CustomerID,CustomerMailCatalogs) values('bsmith2', 'Baseball')
/*Create this table 4th*/
Create Table Customers(CustomerID varchar(100) Primary Key,
FirstName Varchar(100),
Surname Varchar(100),
CustomerAddress varchar(100),
CustomerEmail varchar(100))
Insert into Customers(CustomerID, FirstName,Surname,CustomerAddress,CustomerEmail) values ('bsmith1','Bob', 'Smith', '123 Broadway', 'bsmith@gmail.com')
Insert into Customers(CustomerID, FirstName,Surname,CustomerAddress,CustomerEmail) values ('jthomas1','Jill', 'Thomas', '12 Cockland grove', 'jthomas@gmail.com')
Insert into Customers(CustomerID, FirstName,Surname,CustomerAddress,CustomerEmail)values ('bsmith2','Jill', 'Thomas', '5 Maple Street', 'smith5@hotmail.com')
/*Create this table 5th*/
Create Table CustomerOrders(CustomerOrderID int Primary Key identity(1,1),
CustomerID varchar(100) references Customers(CustomerID),
OrderDate Date,
OrderTotal Float)
Insert into CustomerOrders(CustomerID,OrderDate,OrderTotal) values('bsmith1','8/9/2020', 70)
Insert into CustomerOrders(CustomerID,OrderDate,OrderTotal) values('jthomas1','8/10/2020', 57)
Insert into CustomerOrders(CustomerID,OrderDate,OrderTotal) values('bsmith1','8/10/2020', 23)
Insert into CustomerOrders(CustomerID,OrderDate,OrderTotal) values('bsmith2','8/10/2020', 88)
/*Create this table 6th*/
Create Table Products(ProductID VarChar(50) Primary Key,
Product Varchar(50),
ProductManufacturer Varchar(50) references ProductManufacturers(ProductManufacturer),
ProductDetails Float,
ProductCost Float)
Insert into Products(ProductID,Product,ProductManufacturer,ProductDetails,ProductCost) values('BasketBall-SP', 'Basketball','Spaulding',29.5, 25)
Insert into Products(ProductID,Product,ProductManufacturer,ProductDetails,ProductCost) values('Bat-L', 'Bat','Louisville Slugger',33, 35)
Insert into Products(ProductID,Product,ProductManufacturer,ProductDetails,ProductCost) values('Softball-R', 'Softball','Rawlings',4, 6)
Insert into Products(ProductID,Product,ProductManufacturer,ProductDetails,ProductCost) values('Bat-R', 'Bat','Rawlings',32, 45)
Insert into Products(ProductID,Product,ProductManufacturer,ProductDetails,ProductCost) values('Golfballs-T', 'Golf Balls','Titleist',12, 44)
Insert into Products(ProductID,Product,ProductManufacturer,ProductDetails,ProductCost) values('Basketball-T', 'Basketball','Titleist',12, 44)
/* Create this table 7th */
drop Table CustomerOrderLines
create Table CustomerOrderLines(ProductID varchar(50),
CustomerOrderID int,
quantity int,
Primary Key(ProductID,CustomerOrderID),
Foreign Key(ProductID) references Products(ProductID),
Foreign Key(CustomerOrderID) references CustomerOrders(CustomerOrderID))
Insert into CustomerOrderLines(ProductID,CustomerOrderID,Quantity) values('Basketball-SP',1,1)
Insert into CustomerOrderLines(ProductID,CustomerOrderID,Quantity) values('Bat-L',1,1)
Insert into CustomerOrderLines(ProductID,CustomerOrderID,Quantity) values('Softball-R',2,2)
Insert into CustomerOrderLines(ProductID,CustomerOrderID,Quantity) values('Bat-R',2,1)
Insert into CustomerOrderLines(ProductID,CustomerOrderID,Quantity) values('GolfBalls-T', 4, 2)
Insert into CustomerOrderLines(ProductID,CustomerOrderID,Quantity) values('Basketball-T', 4, 2)
select* FROM CustomerEmailSubscription
select * from ProductManufacturers
select * from Products
select * from Customers
At 8:25, when normalizing to 2NF, Mark Smith has a Titleist basketball and Bob Smith has a Spaulding basketball. A few seconds later after deleting rows from the Product table, Mark Smith has a Spaulding (note: no longer Titleist) basketball. Manufacturer:Product was m:1 and then changed to 1:1 behind the scenes. This is confusing for those learning about normalization. It seems the error was noticed while recording and fixed "off camera" - it would be helpful to correct the error in the video for those of us following along. I was confused about how having just the product name as a foreign key could uniquely identify both the product and manufacturer when, at that point, two manufacturers had basketballs.
niemand gaat dit lezen broer
@@Kipviss behalve jou!
2nd Normal form
Why do we need order date to make a unique composite key, when customer and product are sufficient to make a primary key? Please answer the question, thanks
Assume you have a table User(id, email, username). All fields are unique. Does it satisfy 3NF?
Bob Smith on bottom two rows changes to Bill Smith later
I noticed that Product ID - Basketball - SP is duplicate in product tabke so it is not qualified as Primary Key...?
so how you create in in sql .?
ذاكر الكويز يابطل…
قاعدين نذاكر
4:19 2nd normal form
5:05 *partial dependence*
6:55 resolve partial dependency
9:45 3rd normal form
11:46 transitive dependency
13:51 fourth normal form
17:12 demo example
17:34 1.