Microsoft Access 2013 Tutorials
Database Design 101 / Understanding Integrity
Subtitles of the Movie (Microsoft Access 2013 / Database Design 101 / Understanding Integrity)
All this talk about normalization and relationships between tables brings up another concern that we're going to have to have with our databases and that is one of integrity. Because as we start to split data away from the information it's directly related to in a single table and we start to put it into separate tables, we've now got a new concern. How do we make sure that we don't lose the relationships between that data? And how do we enforce those relationships in such a way that we don't, southern technical term here, foul up or mess up these relationships. Because once we do that then we're going to make a bunch of data totally useless and meaningless and it's also going to get us an opportunity to probably meet someone much higher than our pay grade and it's probably not going to be pleasant meeting. Okay. So notice in this little example here we clearly have taken the order ID information from the students table. Right. Here we have our little student IDs, Mary, Tom and Alice once again. Over here we've created an order ID table to track those orders just like in previous examples I've done. And now we've got an issue. We have related this data, we have got to make sure that we always have this relationship and that if there's a person in this table, they match to a person here. Right. And if we have a person in this table with data here then nothing happens to this person in a way that violates this. Let's look at a couple of examples here. Okay. Our data relationships have to be maintained. What if I go into this table. Okay. And I add a number four and an order ID. Well, err won't work. Okay. Because I don't have a number four over here. This data has no meaning, it doesn't make sense. So I can't just add data to this table unless it matches here. Now Access will do this for you. If I try to do an insert to this table and the foreign key doesn't match any of the primary key entries over here, it'll throw and error message and say err not valid can't do it. Don't know exactly what it will say, it'll be a Microsoft speak and it won't make a lot of sense but you will be able to see that you've violated primary foreign key relationship. Okay. So we can't add data to this table that doesn't have a matching primary key entry in this table. Then so we got that straight. We can't delete data from this table if data exists in this table, because if we take Alice out of here then this data has now become what we call orphaned. This data's out here but it has no meaningful relationship back to anything here. So two months from now we come in there and say oh wait a minute there was an order number 3572 but it doesn't match anyone, we don't know who placed that order. So that's problematic we don't want that to happen. And guess what? Access will not allow you to delete from this table if there's related data in this table. You need to delete this first and then this. Now in some database products you can set what's called cascading deletes. And if I delete from here it will automatically delete from this table. You should have just got a little gnawing in your stomach about the problems with that. But in some cases that's exactly what you want to do, your call. But that's a real quick look at integrity, that's something that we want to watch for. We do need to be concerned about. Thankfully Access is going to take care a lot of that for us and when we create these primary foreign key or what we're calling data relationships at this point in the course, these things are being watched for us. And it will drive you nuts when you start trying to input or manipulate data in a highly normalized table or a highly normalized database because every table's connected to something else and it seems like everything you try to do violates relationship. But trust me you want it that way because you don't want to inadvertently scramble all your data and have to go to that meeting. Okay. So anyway there's a quick look at understanding integrity.
Tutorial Information
| Course: | Microsoft Access 2013 |
| Author: | Mark Long |
| SKU: | 34405 |
| ISBN: | 978-1-61866-090-9 |
| Release Date: | 2013-02-01 |
| Duration: | 7 hrs / 89 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
