Database Design 7 - Data Integrity

hey what's up everybody I'm on the opposite side today which just feels weird but anyways we're going to be talking about data integrity alright so what is data integrity we talked about it a little bit in the last video but this video is going to be going over in more depth data integrity is just having correct data in your database so when you when we're databasing we don't want repeating values we don't want incorrect values and we don't want broken relationships between tables for example in the last video I gave the example you have a table here and this is the user table this is weird writing like this and then we have a table over here and that is the sale table which is where you sell products for example this is just an example you can you can implement other examples but this is just to illustrate my point a sale is when a user buys product that means the sale needs a user as the buyer otherwise there's no sale we can't have a sale if no one bought it does that make sense that means the sale is dependent on a user and we have a relationship the user buys stuff stuff is bought by a user this is a known as a Casillas this is known as a relation now do keep in mind that the term relational database does not come from the word relationship it comes from relations which is the mathematical connexion of sets which we talked about that in an earlier video but just don't get that confused but relational database does have relationships between tables so this if for some reason this was broken and now we have a sale that was bought by a user or no user so for example let's say sale was bought by user with the ID of seven okay and then we have a user of ID of seven so there's a connection the sale was bought by the user of ID of seven which points back to the user makes sense that's data integrity but if this relationship is broken and it says the sale was bought by the user with the ID of seven and then this person is removed from the database well now this sale points to a table or a user that doesn't exist and that might be that might be practical in some sense to say that sale was bought by that user when you did exist but for things that need to update consistently we don't want that to happen so that's the example of integrity issues so you want to keep that relationship to say okay well we got rid of the user we need to update the sales to say well either the person is deleted or also do something with the sales so yeah sorry sounds a little confusing okay so the three main types of data integrity first one is entity integrity we're talking about entities in like one of the first few videos an entity is anything we store data about so we had the user to begin with this is an entity what it means by entity integrity is basically unique entities when we have a user we often give them IDs this is known as a key so an ID is used to enforce uniqueness among the entities like for example if we had a table for users and we store the the name of the person and then we store their address no this is go their phone number yes right this is when we talk about addresses it's best to break it up into Lake Street State and I don't want to go through all that so we could potentially have a person with a repeating name so let's just say his name's Caleb and his phone number is 1-800-321-8633 onto referential integrity this is the one we talked about earlier is when we reference the ID of a table and another table if we had a comments table on the website right and we say who the comment is by we have a user ID which points back to this idea over here that's because the comment has to be posted by a user otherwise we have a comment that didn't have someone who posted it which doesn't make sense in the practical world so we always have to have referential integrity which basically says every time there's a comment there needs to be a person who posted that comment and that connection needs to remain it needs to stay if for some reason this is disconnected and then this user is removed or this table is changed well then this could change I mean this stays the same and then there's a disconnection to where this has changed and this is old outdated data where either the user no longer exists and the comment still says he does or something else like that that's referential integrity and that'll make more sense once we start designing and stuff but yet a domain integrity that that brings up a new word domain domain is basically just the acceptable values for a column for say it's what we are storing the range of what we are storing within a database so when we're talking about tables we have columns so these columns up here let's say we have a phone number well that means we should have a certain number of digits we have the area code and then the three digits and then the four digits and that's at least the u.s. in might change other places but that is ten digits so we know that the phone number should be ten digits and it should be numbers so if for some reason I put the word take in the phone number column well for one this is going to cause an error if we have domain integrity because it's not a ten digit number it's just a word that's not the proper domain it's not a digit is not numbers and it's not tenable so we know that's going to cause an error so that's the basics of data integrity with when we don't have data integrity we have errors when we have data integrity we do something to correct those errors such as saying all these all need to be numbers that's how we implement data integrity by setting database rules and how you do that with the database well that kind of varies upon the relational database management system that you use some relational database management systems allow for general rules saying okay this has to be a range of this numbers some of them are more vague where they where they only allow you to implement data types data types would be like integer ten digits so when we talk about practically implementing data integrity different relational database management systems have different things you can do almost every single relational database system that Idaho I'm pretty sure all of them but who knows they allow for data types so a data type is pretty pretty self-explanatory it's the type of data so in database we classify things as generally integer or text or date so we either have numbers text or dates that's like three general categories which we'll be talking about that more in a future video but the data type can put limits on what we're allowed to store for example can say okay we only want a character column with the maximum of 20 characters that means we can put a string in the column that says I like people yeah 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 that'll fit but if I wanted to change this pizza to lasagna well then I just added a couple extra characters that might cut it off I'm not sure 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 so that's the maximum we could put in that data type that's an example of implementing that data integrity for the characters so this this will be for the domain integrity we can also have referential integrity with what's known as foreign key constraints which allows us to connect tables because when we draw tables we could say that they're connected in some way but how do we enforce that in a database we use foreign key constraints for example we can say if this is the the parent let's say this is the users and this is the comments the user places a comment so this is the parent this is the child because the child can't exist without the parent you can't have a comment without a commenter well using foreign key constraints we can say alright if this user gets removed also remove all of the comments by this user that's an example of foreign key constraint as for the non-repeating data that is done with just designing your database in the best way so yeah I know that video was kind of a jump back and forth from concept to concept and maybe a little confusing but I'm sure you'll get it as time goes on so yeah that's all I've to say in this video thank you for watching if you liked it please be sure to subscribe and click like and share it to your friends on Facebook or Twitter or Google Plus work yeah that's all I know LinkedIn yeah

Channel: Caleb Curry

Views: 71,629

Rating: 4.9695239 out of 5

Keywords: Database Design, design 7, data integrity, Database (Software Genre), Data (Website Category), domain integrity, entity integrity, referential integrity

Id: 1D_h-yFtQVo

Length: 13min 25sec (805 seconds)

Published: Wed Jul 09 2014