1 00:00:00,083 --> 00:00:06,180 [MUSIC]. 2 00:00:06,180 --> 00:00:09,932 So, last time we talked about data models and used them to motivate databases and 3 00:00:09,932 --> 00:00:13,765 define the term database in, in a, in a broad sense. 4 00:00:13,765 --> 00:00:17,249 And now I want to build on that to talk specifically about relational databases, 5 00:00:17,249 --> 00:00:19,673 okay? So, last time we talked about these 6 00:00:19,673 --> 00:00:23,489 questions you could use to reason about different ways of organizing data. 7 00:00:23,489 --> 00:00:25,970 And evaluate them with respect to your requirements. 8 00:00:25,970 --> 00:00:29,285 And I want to talk about these questions and apply them to examples of different 9 00:00:29,285 --> 00:00:34,016 kinds of databases you saw in the past. that end up motivating the relational 10 00:00:34,016 --> 00:00:36,196 model. And in particular, the reason I want to 11 00:00:36,196 --> 00:00:39,163 go through this, this sort of historical view of things. 12 00:00:39,163 --> 00:00:42,030 Is that you see some of these same designs being proposed in terms of no 13 00:00:42,030 --> 00:00:45,467 sequel systems. And some of the same issues come up, both 14 00:00:45,467 --> 00:00:48,310 the benefits, both pros and cons are still there. 15 00:00:48,310 --> 00:00:51,005 So, it's good to have a historical perspective on them when you're 16 00:00:51,005 --> 00:00:54,512 evaluating these modern systems that are becoming popular. 17 00:00:54,512 --> 00:00:57,728 Okay, so the questions we talked about were, how is data physically organized on 18 00:00:57,728 --> 00:01:00,790 disks? You can ask this about a system. 19 00:01:00,790 --> 00:01:02,490 what kind of queries are efficiently supported? 20 00:01:02,490 --> 00:01:05,905 How do you update things? And so on. 21 00:01:05,905 --> 00:01:08,711 Alright, so one example is, well, I'll call it network database although 22 00:01:08,711 --> 00:01:13,180 arguably this is just sort of pre-databases where you just have files. 23 00:01:13,180 --> 00:01:15,556 And you, if you go back to our questions, you might ask, well how is it physically 24 00:01:15,556 --> 00:01:19,717 organized on disk? Well, if you, you're using sort of a 25 00:01:19,717 --> 00:01:24,253 parts and order model here, you, you would have a, order record. 26 00:01:24,253 --> 00:01:28,096 And it would have an address associated with this order record that would 27 00:01:28,096 --> 00:01:32,867 physically point to the first part associated with that order. 28 00:01:32,867 --> 00:01:36,840 And that part would point to the next one and so on. 29 00:01:36,840 --> 00:01:41,926 Another field in the record would point to the customer that made that order. 30 00:01:41,926 --> 00:01:45,049 Okay? So going back, you know what kind of 31 00:01:45,049 --> 00:01:50,635 crews are efficiently supported. Well, if I want to find all of the parts 32 00:01:50,635 --> 00:01:54,380 associated with an order I can do that pretty efficiently. 33 00:01:54,380 --> 00:01:58,256 I have an access to over, I just give an order, and I'll just walk down this chain 34 00:01:58,256 --> 00:02:03,107 to gather all the parts. What kind of queries are not supported 35 00:02:03,107 --> 00:02:07,651 officially is you know i want to find all the orders that involve a particular 36 00:02:07,651 --> 00:02:11,943 part. All the orders that involve this washer 37 00:02:11,943 --> 00:02:16,227 well now i have to scan every order to look for them. 38 00:02:16,227 --> 00:02:18,487 Okay? There are some ways around that by 39 00:02:18,487 --> 00:02:23,758 putting back pointers and so on. Another problem with this file oriented 40 00:02:23,758 --> 00:02:27,507 you know, sort of proto-database model is that. 41 00:02:27,507 --> 00:02:31,451 Whenever I want to make a change to the data, all right, if I want to have an 42 00:02:31,451 --> 00:02:35,803 extra field added to support the billing customer as opposed to the shipping 43 00:02:35,803 --> 00:02:41,586 customer. Well, I've just added a new field, I've 44 00:02:41,586 --> 00:02:46,074 extended the length of this record, that means that everything else below that 45 00:02:46,074 --> 00:02:51,780 record needs to be moved. More importantly, all the programs that, 46 00:02:51,780 --> 00:02:56,506 that, navigate the structure now need to be aware of this other field. 47 00:02:56,506 --> 00:03:00,044 They all need to be rewritten to accommodate this extra piece of data, 48 00:03:00,044 --> 00:03:04,038 okay. Moreover, if you, if you want to support 49 00:03:04,038 --> 00:03:09,645 different access methods as we talked about, if you want to look, lets say by 50 00:03:09,645 --> 00:03:17,280 part and find all the orders. I end up having to make a complete second 51 00:03:17,280 --> 00:03:21,704 copy, of the database. And now when I update, when I make a 52 00:03:21,704 --> 00:03:25,437 change to one copy, I need to make a change to all copies. 53 00:03:25,437 --> 00:03:28,965 And you can imagine how the space of possible copies might grow might get 54 00:03:28,965 --> 00:03:30,812 pretty big. Okay? 55 00:03:30,812 --> 00:03:35,096 So, a partial solution to this problem was this notion of hierarchical databases 56 00:03:35,096 --> 00:03:38,867 characterized by perhaps IBM's IMS system. 57 00:03:38,867 --> 00:03:42,221 Which actually still exists and still has customers. 58 00:03:42,221 --> 00:03:47,093 And so here, they used to order organize data in terms of segments. 59 00:03:47,093 --> 00:03:51,989 but still the logical model was, has the higher flavor that we saw in the network 60 00:03:51,989 --> 00:03:56,032 model as well. So, here I switched, I made the top-level 61 00:03:56,032 --> 00:04:00,420 access, the customer's set of order. And so logically what you have is and, 62 00:04:00,420 --> 00:04:03,520 on, and order is only located underneath the customer, and the part is only 63 00:04:03,520 --> 00:04:09,052 located underneath an order. However, given that there are in separate 64 00:04:09,052 --> 00:04:11,656 segments I can make a change to one segment. 65 00:04:11,656 --> 00:04:15,180 Without having to break all the code that accesses other segments. 66 00:04:16,380 --> 00:04:20,176 The downside that still exist here though is that, the programmer, the application 67 00:04:20,176 --> 00:04:25,115 developer, still needs to understand this hierarchy in order to find anything. 68 00:04:25,115 --> 00:04:27,824 Okay? They have to actually know exactly how 69 00:04:27,824 --> 00:04:31,680 things are, are organized for example, that orders appear under customers. 70 00:04:32,790 --> 00:04:35,646 so you still have to anticipate what kind of access methods your customers are 71 00:04:35,646 --> 00:04:39,198 going to want and design for those. All right ? 72 00:04:39,198 --> 00:04:43,896 Updates here are a little bit easier given that I can add an order to one 73 00:04:43,896 --> 00:04:51,827 segment stored elsewhere without reflecting all the other structures. 74 00:04:51,827 --> 00:04:57,092 And I didn't even had to field, and I can only, only make changes to the orders as 75 00:04:57,092 --> 00:05:03,576 I suppose changing everything,. more over the softer layer on top of this 76 00:05:03,576 --> 00:05:07,520 variable just sort of insulate from those kind of changes with us, with, with some 77 00:05:07,520 --> 00:05:12,059 reliability. Okay, so this new field would only be, 78 00:05:12,059 --> 00:05:17,240 passed back to the client when they actually needed that new field. 79 00:05:17,240 --> 00:05:18,762 Okay? So, there's some measure of, what I'll 80 00:05:18,762 --> 00:05:21,304 call data independence, and we'll talk about that a little bit more in a few 81 00:05:21,304 --> 00:05:24,350 minutes. Okay, so moving towards relational 82 00:05:24,350 --> 00:05:28,750 databases the one view of what a relational database really is. 83 00:05:28,750 --> 00:05:33,592 Is, here I'm quoting Curt Monash, who's an analyst for the database industry. 84 00:05:33,592 --> 00:05:36,777 And he says, you know, Relational Database Management Systems were invented 85 00:05:36,777 --> 00:05:39,728 to let you use one set of data in multiple ways. 86 00:05:39,728 --> 00:05:42,992 Including ways that were unforeseen at the time the database was built and at 87 00:05:42,992 --> 00:05:46,329 the time that the first applications were written. 88 00:05:46,329 --> 00:05:49,497 And so I want to emphasize here that this is the key idea of relational databases, 89 00:05:49,497 --> 00:05:52,702 not, you know, SQL. And not some of the other things you may 90 00:05:52,702 --> 00:05:55,620 associate it with, with particularly implementations. 91 00:05:55,620 --> 00:05:58,800 It's really just about organizing the data in such a way, just to support 92 00:05:58,800 --> 00:06:02,245 unforeseen access methods, querying in ways that you didn't anticipate when 93 00:06:02,245 --> 00:06:06,425 organized it. Insulating applications from changes. 94 00:06:06,425 --> 00:06:11,915 Okay, so what is a relational database? Well, at the simplest level everything is 95 00:06:11,915 --> 00:06:15,710 a relation which is synonymous with a table, right? 96 00:06:15,710 --> 00:06:18,416 Everything's rows and columns, and this, this probably doesn't need to be made 97 00:06:18,416 --> 00:06:21,780 explicitly, but let me do so. Every row in the table has exactly the 98 00:06:21,780 --> 00:06:24,622 same columns. Has the same number of columns, but they 99 00:06:24,622 --> 00:06:26,740 also have the same types. Okay? 100 00:06:26,740 --> 00:06:29,620 So, if a column has an integer and one row, then it needs to be an integer in 101 00:06:29,620 --> 00:06:31,332 all the rows. All right. 102 00:06:31,332 --> 00:06:36,057 And then, a consequence of this model of everything being a table is that you 103 00:06:36,057 --> 00:06:42,180 don't have pointers anymore. Right, you don't have physical addresses. 104 00:06:42,180 --> 00:06:46,050 All you have is tables. And so, relationships between different 105 00:06:46,050 --> 00:06:50,840 data items are implicit. So, instead of having the, so here we 106 00:06:50,840 --> 00:06:56,107 switch to the, the domain one of course and students. 107 00:06:56,107 --> 00:07:01,525 So, this table is a student takes, lets say takes, a student takes course and 108 00:07:01,525 --> 00:07:08,621 this is a student record. Well, instead of having a physical 109 00:07:08,621 --> 00:07:19,630 pointer form the, course record back to the student, we just have a shared ID. 110 00:07:21,930 --> 00:07:25,180 The only, the only relationship between these two data items is they both have 111 00:07:25,180 --> 00:07:29,799 the same value in a particular column. Okay, and so this is, intuitively this 112 00:07:29,799 --> 00:07:33,900 sounds really bad for performance right off the bat, right? 113 00:07:33,900 --> 00:07:37,248 If I want to go look up all the students associated, all the student's names 114 00:07:37,248 --> 00:07:41,650 associated with a particular course. Once I have my course, I need to go look 115 00:07:41,650 --> 00:07:44,473 up in this table, all the values that match. 116 00:07:44,473 --> 00:07:47,482 As opposed to just navigating directly to them which you can do with the 117 00:07:47,482 --> 00:07:50,942 hierarchical method. But, if I want to go the other direction. 118 00:07:50,942 --> 00:07:54,230 It's the exact same process. I look up the names I want to find, you 119 00:07:54,230 --> 00:07:57,700 know, all the courses that a student has taken, right. 120 00:07:57,700 --> 00:08:00,256 I can do so the same why. I do have to do the look ups, which maybe 121 00:08:00,256 --> 00:08:03,191 is a cost in performance. But the mechanism by which I look things 122 00:08:03,191 --> 00:08:08,296 up is the same in both cases. Moreover, everything is stored only once 123 00:08:08,296 --> 00:08:11,304 which is, which is a feature that the hierarchical databases were able to 124 00:08:11,304 --> 00:08:14,960 achieve in most cases. Okay. 125 00:08:14,960 --> 00:08:17,961 But the network databases were not. We don't have that multiple copies of 126 00:08:17,961 --> 00:08:20,106 things lying around. All right. 127 00:08:20,106 --> 00:08:23,811 So, the philosophy here is, you know, being cute about this, the quote from the 128 00:08:23,811 --> 00:08:27,402 19th century is that, you know, God made the integers, all else is the work of 129 00:08:27,402 --> 00:08:31,114 man. Well, you know, Codd made the relations, 130 00:08:31,114 --> 00:08:33,966 which is a reference to Edgar Codd, he wrote the first relational database 131 00:08:33,966 --> 00:08:36,608 paper. And when on to win the Turing Award for 132 00:08:36,608 --> 00:08:39,443 his work, which is sort of the Nobel Prize in computer science, Codd made 133 00:08:39,443 --> 00:08:42,500 relations and all else is the work of man. 134 00:08:42,500 --> 00:08:45,391 So, everything is a table, is the number one thing to remember about the 135 00:08:45,391 --> 00:08:49,000 relational data model. Everything is a relation. 136 00:08:49,000 --> 00:08:53,960 All right. So, let's actually break here and I'll 137 00:08:53,960 --> 00:08:57,860 pick up with this slide next time.