1 00:00:00,860 --> 00:00:01,900 In this video we're going to 2 00:00:02,000 --> 00:00:03,670 learn about translating designs in 3 00:00:03,900 --> 00:00:06,300 the UML modelling inter-relational schemas. 4 00:00:07,900 --> 00:00:09,200 As a reminder if the 5 00:00:09,630 --> 00:00:11,050 application developer is using a 6 00:00:11,210 --> 00:00:12,470 high level design model then 7 00:00:12,660 --> 00:00:13,990 they will create their design usually 8 00:00:14,350 --> 00:00:16,450 using a graphical language that's 9 00:00:16,920 --> 00:00:18,490 sent to a translator and the 10 00:00:18,670 --> 00:00:19,820 translator will then produce 11 00:00:21,200 --> 00:00:23,240 a schema using the data 12 00:00:23,500 --> 00:00:24,910 model of the underline database 13 00:00:25,450 --> 00:00:27,550 management system, which frequently is relational. 14 00:00:28,870 --> 00:00:30,090 So, in our case we're looking at 15 00:00:30,230 --> 00:00:31,370 high level design in the 16 00:00:31,500 --> 00:00:32,600 UML data modeling subset which 17 00:00:32,700 --> 00:00:35,000 is then translated to 18 00:00:35,180 --> 00:00:37,450 relations and installed in a relational database system. 19 00:00:38,140 --> 00:00:41,050 It's this translation process that's the topic of this video. 20 00:00:42,270 --> 00:00:43,280 In the data model of subset 21 00:00:43,660 --> 00:00:44,690 of UML, we have five 22 00:00:45,240 --> 00:00:46,420 concepts that we learned in 23 00:00:46,510 --> 00:00:48,370 the previous video and we'll 24 00:00:48,530 --> 00:00:50,090 see now that designs using these 25 00:00:50,260 --> 00:00:51,830 concepts can be translated to 26 00:00:51,930 --> 00:00:53,880 relations automatically with one 27 00:00:54,020 --> 00:00:54,930 provision which is that every 28 00:00:55,270 --> 00:00:56,740 regular class has a key. 29 00:00:57,010 --> 00:00:58,900 And we'll see what we mean by a regular class in a moment. 30 00:00:59,580 --> 00:01:00,440 So, what we're going to 31 00:01:00,530 --> 00:01:01,380 do is go through each one of 32 00:01:01,510 --> 00:01:02,500 these concepts one at a 33 00:01:02,620 --> 00:01:03,360 time and show show how 34 00:01:03,490 --> 00:01:04,980 the concepts are translated to relations. 35 00:01:06,520 --> 00:01:07,780 For our examples we'll use 36 00:01:07,980 --> 00:01:09,540 primarily the same examples that 37 00:01:09,670 --> 00:01:11,970 we used in the previous video where we introduced UML. 38 00:01:13,000 --> 00:01:14,320 For classes will have a 39 00:01:14,640 --> 00:01:16,560 student class and a college class. 40 00:01:18,170 --> 00:01:19,320 Our students have a student 41 00:01:19,950 --> 00:01:22,150 ID which is the primary key, 42 00:01:22,290 --> 00:01:23,270 a student name and a GPA 43 00:01:23,350 --> 00:01:24,410 and our colleges will have a 44 00:01:24,600 --> 00:01:25,580 college name which we'll assume 45 00:01:25,920 --> 00:01:26,750 is unique, so that's the primary 46 00:01:27,190 --> 00:01:28,670 key, the state and the enrollment. 47 00:01:29,560 --> 00:01:31,340 The translation from classes to 48 00:01:31,490 --> 00:01:33,140 relations is absolutely direct 49 00:01:33,850 --> 00:01:35,350 we take the student class and 50 00:01:35,490 --> 00:01:36,680 we make a relation called student, 51 00:01:37,500 --> 00:01:38,740 we put the three attributes 52 00:01:39,230 --> 00:01:41,230 down for that relation and 53 00:01:41,440 --> 00:01:43,240 then the primary key from 54 00:01:43,580 --> 00:01:46,440 the student class becomes the key attribute in that relation. 55 00:01:47,390 --> 00:01:48,830 And similarly our college relation 56 00:01:49,860 --> 00:01:51,930 has the college name as 57 00:01:52,080 --> 00:01:54,100 a key, the state, and the enrollment. 58 00:01:54,730 --> 00:01:55,770 So all we're doing to turn 59 00:01:56,130 --> 00:01:58,870 classes into relation is basically turning them side ways. 60 00:01:59,600 --> 00:02:01,900 So now we're done with classes and let's move on to associations. 61 00:02:03,600 --> 00:02:05,010 We'll see that each association is 62 00:02:05,070 --> 00:02:06,270 going to be translated to its 63 00:02:06,460 --> 00:02:07,820 own relation that grabs a 64 00:02:08,080 --> 00:02:09,090 key from each side of the 65 00:02:09,460 --> 00:02:10,700 association so let's go 66 00:02:10,950 --> 00:02:12,110 to our example, the same one we 67 00:02:12,210 --> 00:02:14,130 used earlier, that has students applying to colleges. 68 00:02:15,900 --> 00:02:17,530 So, here's our diagram, with 69 00:02:17,690 --> 00:02:19,690 students applying to colleges and 70 00:02:19,940 --> 00:02:21,390 we'll already have generated the 71 00:02:21,460 --> 00:02:22,520 two relations for our classes, 72 00:02:23,200 --> 00:02:24,300 the student and the college relation 73 00:02:25,200 --> 00:02:26,840 and for the association applied 74 00:02:27,300 --> 00:02:28,710 between students and colleges we 75 00:02:28,870 --> 00:02:30,340 create a relation, we call 76 00:02:30,650 --> 00:02:32,000 it applied and that relation 77 00:02:32,540 --> 00:02:34,120 has attributes the keys 78 00:02:34,430 --> 00:02:35,440 from both sides so that 79 00:02:35,540 --> 00:02:36,880 would be the student ID from 80 00:02:37,070 --> 00:02:38,710 the student class and the 81 00:02:38,970 --> 00:02:40,260 college name from the college class. 82 00:02:41,310 --> 00:02:42,940 And if you think about it it makes a lot of sense. 83 00:02:43,370 --> 00:02:45,330 Applied is capturing relationships between 84 00:02:45,660 --> 00:02:47,080 students and colleges so we'll 85 00:02:47,230 --> 00:02:48,400 capture each of those relationships 86 00:02:49,100 --> 00:02:50,540 with one tuple in the 87 00:02:50,700 --> 00:02:52,410 applied relation identifying the student 88 00:02:52,770 --> 00:02:54,370 with the key and the college with the key. 89 00:02:55,530 --> 00:02:56,570 Now one question you might have 90 00:02:56,910 --> 00:02:57,790 is what is the key 91 00:02:58,260 --> 00:02:59,300 for a relation that we 92 00:02:59,400 --> 00:03:01,450 generate from an association, it 93 00:03:01,750 --> 00:03:02,880 turns out that depends on the 94 00:03:02,960 --> 00:03:04,600 concept of multiplicity that we 95 00:03:04,730 --> 00:03:05,810 learned about in the previous video 96 00:03:06,300 --> 00:03:07,300 so let me setup a generic 97 00:03:07,910 --> 00:03:08,950 example of two classes 98 00:03:09,410 --> 00:03:10,850 with an association and then and 99 00:03:10,940 --> 00:03:13,920 we'll talk about multiplicity and keys. 100 00:03:14,140 --> 00:03:15,450 Ok, so here we have both classes. 101 00:03:15,770 --> 00:03:16,770 We'll call them C1 and C2. 102 00:03:17,190 --> 00:03:18,570 And each one has 103 00:03:18,710 --> 00:03:19,980 a key attribute, we called it 104 00:03:20,130 --> 00:03:21,710 K1 and K2 over here 105 00:03:22,290 --> 00:03:23,690 and then one other attribute 01 106 00:03:23,770 --> 00:03:25,670 and 02 and then we have an association 107 00:03:26,530 --> 00:03:27,510 A between the two classes. 108 00:03:28,350 --> 00:03:29,160 So in terms of their relations 109 00:03:29,580 --> 00:03:30,920 that will be generated, we'll have for 110 00:03:31,070 --> 00:03:33,400 C1, K1-01 with K1 111 00:03:33,840 --> 00:03:35,340 as a key for C2 we'll 112 00:03:35,720 --> 00:03:37,560 have K2-02 with K2 113 00:03:37,960 --> 00:03:39,280 as key and then 114 00:03:39,630 --> 00:03:41,720 for our association A, we'll 115 00:03:41,900 --> 00:03:42,950 grab the key from each 116 00:03:43,130 --> 00:03:44,700 side, K1 and K2 and 117 00:03:45,320 --> 00:03:46,680 the question we have now is, 118 00:03:46,990 --> 00:03:49,180 what is the key for relation A? 119 00:03:49,840 --> 00:03:50,870 Well, as a default the key 120 00:03:51,110 --> 00:03:53,400 can be both attributes together, but 121 00:03:53,520 --> 00:03:54,540 in certain cases we can 122 00:03:54,740 --> 00:03:56,010 be more specific, and again 123 00:03:56,420 --> 00:03:57,450 that's going to depend on the 124 00:03:57,510 --> 00:03:59,150 multiplicity on our association. 125 00:04:00,800 --> 00:04:02,920 Let's suppose that our multiplicity is 0..1 126 00:04:04,880 --> 00:04:06,180 on the left hand side and a 127 00:04:06,480 --> 00:04:07,620 star on the right hand side 128 00:04:08,300 --> 00:04:09,200 and let's look at what 129 00:04:09,370 --> 00:04:12,310 that represents in terms of the type of relationship we're capturing. 130 00:04:13,200 --> 00:04:14,170 And, I'll make sure get it right 131 00:04:14,530 --> 00:04:16,000 this time, so what this 132 00:04:16,100 --> 00:04:17,470 is saying here is that if 133 00:04:17,660 --> 00:04:18,940 we have objects of C1 134 00:04:19,300 --> 00:04:20,370 on the left hand side, 135 00:04:21,420 --> 00:04:22,820 each one can be related to 136 00:04:23,020 --> 00:04:24,430 many objects of C2 137 00:04:24,830 --> 00:04:26,300 on the right hand side, but 138 00:04:26,590 --> 00:04:27,780 each object of C2 can 139 00:04:28,070 --> 00:04:29,480 be only be related to at 140 00:04:29,660 --> 00:04:31,400 most one object of 141 00:04:31,490 --> 00:04:33,640 C1, and some of them might not be related to any. 142 00:04:33,960 --> 00:04:35,270 So remember now, it's 143 00:04:35,500 --> 00:04:36,510 these edges that are being 144 00:04:36,710 --> 00:04:38,550 captured by the tuples in 145 00:04:38,880 --> 00:04:39,860 our relation for the association, 146 00:04:40,860 --> 00:04:41,920 and we can see that 147 00:04:42,200 --> 00:04:43,050 each element on the right 148 00:04:43,290 --> 00:04:44,290 hand side can only be 149 00:04:44,360 --> 00:04:45,810 involved in at most one 150 00:04:46,190 --> 00:04:47,870 tuple of A, so 151 00:04:47,990 --> 00:04:49,300 that tells us that K2 152 00:04:50,050 --> 00:04:51,270 is actually a key for 153 00:04:51,450 --> 00:04:52,830 A. So when we 154 00:04:53,100 --> 00:04:54,420 have zero dot dot 155 00:04:54,630 --> 00:04:56,660 one or one dot dot one, for that matter. 156 00:04:57,350 --> 00:04:58,910 On the left side, or 157 00:04:59,000 --> 00:05:00,010 on one side of an association, 158 00:05:01,130 --> 00:05:02,560 then the key attribute from 159 00:05:02,710 --> 00:05:04,730 the other side is a key for the association. 160 00:05:06,490 --> 00:05:07,710 So lets test out that rule 161 00:05:08,110 --> 00:05:09,850 on our student and college 162 00:05:10,600 --> 00:05:12,630 association and see if it makes sense. 163 00:05:13,550 --> 00:05:15,220 So I've drawn the association here 164 00:05:15,790 --> 00:05:16,990 and we're interested in the 165 00:05:17,110 --> 00:05:18,330 relation that's going to be 166 00:05:18,810 --> 00:05:20,900 generated for the association 167 00:05:21,530 --> 00:05:22,650 itself which will be the applied 168 00:05:22,890 --> 00:05:24,040 relation with the student ID 169 00:05:24,250 --> 00:05:25,420 on one side from one side 170 00:05:25,830 --> 00:05:27,120 and the college name from the 171 00:05:27,270 --> 00:05:28,410 other, so that's the relation 172 00:05:28,960 --> 00:05:30,150 we're going to generate for the 173 00:05:30,190 --> 00:05:31,160 association and the question 174 00:05:31,410 --> 00:05:32,740 is what's key for that relation. 175 00:05:33,590 --> 00:05:34,640 Well, let's add the constraint 176 00:05:35,500 --> 00:05:38,140 that every student applies to exactly one college. 177 00:05:38,610 --> 00:05:39,620 So, that would be a one dot 178 00:05:39,880 --> 00:05:41,380 dot one on the right and 179 00:05:41,540 --> 00:05:42,640 then a star on the 180 00:05:42,700 --> 00:05:45,220 left if a college can have any number of applicants. 181 00:05:46,150 --> 00:05:47,580 So, our rule of thumb, 182 00:05:47,780 --> 00:05:49,030 it's actually a rule, said 183 00:05:49,280 --> 00:05:50,410 that if we have one dot dot 184 00:05:50,580 --> 00:05:51,700 one or zero dot one dot 185 00:05:51,890 --> 00:05:53,250 dot one on one side then 186 00:05:53,490 --> 00:05:54,900 the other side would be the key. 187 00:05:55,640 --> 00:05:56,720 So that would tell us 188 00:05:56,750 --> 00:05:57,870 that if we have this one 189 00:05:58,300 --> 00:05:59,020 dot dot one on the right 190 00:05:59,580 --> 00:06:00,700 that student ID would be 191 00:06:00,850 --> 00:06:02,160 a key for applied and indeed 192 00:06:02,450 --> 00:06:04,060 that makes complete sense if each 193 00:06:04,260 --> 00:06:05,250 student can only apply to 194 00:06:05,350 --> 00:06:06,520 one college, then, they will 195 00:06:06,810 --> 00:06:08,220 indeed be a in the applied relation. 196 00:06:09,570 --> 00:06:11,520 Now there's actually a related concept here 197 00:06:12,080 --> 00:06:13,260 where we might need a 198 00:06:13,300 --> 00:06:14,880 relation for associations at all 199 00:06:15,270 --> 00:06:16,120 and again, that depends on 200 00:06:16,180 --> 00:06:18,690 multiplicity and again let's start with a generic example. 201 00:06:20,030 --> 00:06:21,540 so here's our generic example and 202 00:06:21,690 --> 00:06:23,090 from this our standard translation 203 00:06:23,710 --> 00:06:25,660 would give us three relations, one 204 00:06:26,010 --> 00:06:27,740 for C1, one for C2 205 00:06:28,530 --> 00:06:30,010 and then one capturing the association 206 00:06:30,880 --> 00:06:32,500 A which would have a key from each side. 207 00:06:33,780 --> 00:06:35,070 Now, what we're going to discover, is 208 00:06:35,190 --> 00:06:36,370 that in come cases it's actually 209 00:06:36,780 --> 00:06:38,280 possible to fold relations 210 00:06:38,970 --> 00:06:40,450 A into either C1 or 211 00:06:40,510 --> 00:06:42,050 C2 will end up with 212 00:06:42,250 --> 00:06:43,450 just two relation instead of 213 00:06:43,530 --> 00:06:45,220 three and will be capturing the same information. 214 00:06:46,930 --> 00:06:48,840 So let's suppose we have a 1..1 215 00:06:49,220 --> 00:06:50,390 and on our left hand side 216 00:06:50,760 --> 00:06:51,610 so again we're going to have 217 00:06:51,920 --> 00:06:53,900 the situation where from the 218 00:06:53,970 --> 00:06:54,760 left side to the right 219 00:06:55,030 --> 00:06:56,070 we have a fan out so 220 00:06:56,280 --> 00:06:57,180 each one on the right is 221 00:06:57,300 --> 00:06:59,430 related to exactly one on the left. 222 00:06:59,650 --> 00:07:01,110 If that's the case, 223 00:07:01,630 --> 00:07:02,590 then instead of having a 224 00:07:02,930 --> 00:07:04,410 separate relation for the 225 00:07:04,600 --> 00:07:06,050 associations basically for capturing 226 00:07:06,550 --> 00:07:08,070 these edges here, we can 227 00:07:08,310 --> 00:07:09,960 simply take the related element 228 00:07:10,750 --> 00:07:11,860 up from the left, the key 229 00:07:12,220 --> 00:07:14,820 from the left and add it to the element for the right. 230 00:07:15,180 --> 00:07:16,080 Let me show how that's done. 231 00:07:16,980 --> 00:07:17,790 So what we'll do is we'll 232 00:07:18,030 --> 00:07:19,770 change this C2 to have 233 00:07:20,080 --> 00:07:20,960 K2 and O go to 234 00:07:21,080 --> 00:07:22,900 as before, but also to 235 00:07:23,050 --> 00:07:24,340 have the key from the 236 00:07:24,490 --> 00:07:25,590 left hand side of class, 237 00:07:26,320 --> 00:07:27,360 or relation so the key 238 00:07:27,620 --> 00:07:30,310 from C-1, and then we don't need A any longer. 239 00:07:31,240 --> 00:07:32,460 And we can see why that works, 240 00:07:32,650 --> 00:07:35,040 because every element in C2 241 00:07:35,220 --> 00:07:36,590 is related to exactly one element 242 00:07:36,990 --> 00:07:37,970 in C-1, so we just 243 00:07:38,210 --> 00:07:39,300 add the key for that single 244 00:07:39,710 --> 00:07:41,120 element that is related to that relation. 245 00:07:42,400 --> 00:07:43,390 Now, what if the left hand 246 00:07:43,600 --> 00:07:46,190 side were zero dot dot one instead of one dot dot one? 247 00:07:46,790 --> 00:07:47,760 In that case, it would be 248 00:07:47,890 --> 00:07:48,970 possible for there to be 249 00:07:49,290 --> 00:07:50,760 some elements in C2 that 250 00:07:51,290 --> 00:07:52,510 aren't related to any elements 251 00:07:52,940 --> 00:07:54,210 in C1, and that would 252 00:07:54,350 --> 00:07:55,690 still be okay with this 253 00:07:55,940 --> 00:07:57,030 design as long as null 254 00:07:57,370 --> 00:07:58,710 values would be allowed in 255 00:07:58,880 --> 00:08:00,550 the third attribute of the relation for C-2. 256 00:08:00,950 --> 00:08:03,530 And finally, what's the key to this relation? 257 00:08:04,060 --> 00:08:05,250 Well, we knew before that the 258 00:08:05,480 --> 00:08:06,850 key for C2 was K2, 259 00:08:07,160 --> 00:08:08,980 just by definition of it 260 00:08:09,130 --> 00:08:10,300 being the primary key for the 261 00:08:10,440 --> 00:08:11,750 class, and that's still the 262 00:08:11,850 --> 00:08:13,840 case in the expanded C2. 263 00:08:14,170 --> 00:08:15,680 K2 will still be a 264 00:08:15,750 --> 00:08:16,970 key because we'll only have 265 00:08:17,170 --> 00:08:18,330 one instance of each 266 00:08:18,720 --> 00:08:19,700 object and the one K 267 00:08:19,880 --> 00:08:22,250 one that it would be related to through the association. 268 00:08:23,960 --> 00:08:25,390 So what we saw, to summarize, 269 00:08:25,900 --> 00:08:26,690 is that when we have zero 270 00:08:27,130 --> 00:08:28,080 dot dot one or one dot 271 00:08:28,250 --> 00:08:30,070 dot one on one aside and 272 00:08:30,160 --> 00:08:31,690 then we have an association and instead 273 00:08:31,900 --> 00:08:33,020 of making a relation for the 274 00:08:33,140 --> 00:08:34,700 association we can add 275 00:08:35,060 --> 00:08:36,460 the key from the side 276 00:08:36,890 --> 00:08:37,830 with the zero dot dot one 277 00:08:38,000 --> 00:08:39,480 or one dot dot one, to the 278 00:08:39,620 --> 00:08:42,170 relation that we generated for the class on the right hand side. 279 00:08:42,970 --> 00:08:44,090 So, let's take a look again 280 00:08:44,470 --> 00:08:47,150 with students and colleges just to confirm this intuition. 281 00:08:48,720 --> 00:08:50,090 So let's again suppose that a 282 00:08:50,290 --> 00:08:51,970 student applies to exactly one 283 00:08:52,260 --> 00:08:53,430 college and our rule 284 00:08:53,780 --> 00:08:54,570 of thumb says that we have 285 00:08:54,690 --> 00:08:55,790 a one dot dot one on 286 00:08:55,930 --> 00:08:57,400 one side, then we can 287 00:08:57,620 --> 00:08:59,740 take the key from that 288 00:09:00,060 --> 00:09:00,970 side and add it to 289 00:09:01,090 --> 00:09:02,020 the relation for the other side 290 00:09:02,390 --> 00:09:03,550 so we would be adding to 291 00:09:04,420 --> 00:09:05,760 the student relation, the college 292 00:09:06,300 --> 00:09:08,610 name attribute and we'd 293 00:09:08,880 --> 00:09:10,450 be getting rid of applied entirely. 294 00:09:11,440 --> 00:09:12,390 Let's just think about if that 295 00:09:12,640 --> 00:09:13,620 makes sense if every student 296 00:09:13,930 --> 00:09:15,270 is applying to exactly one college, 297 00:09:15,680 --> 00:09:17,070 it makes perfectly good sense to 298 00:09:17,220 --> 00:09:19,140 just add that college name to 299 00:09:19,260 --> 00:09:21,060 the student relation and further 300 00:09:21,340 --> 00:09:22,420 more if we had students 301 00:09:22,870 --> 00:09:23,780 just applying to either zero 302 00:09:24,150 --> 00:09:25,470 or one college that would 303 00:09:25,610 --> 00:09:27,510 still be an acceptable design provided 304 00:09:28,020 --> 00:09:29,330 that no values are allowed for 305 00:09:29,530 --> 00:09:31,860 the college name attribute Ok, we've 306 00:09:32,090 --> 00:09:33,150 finished with classes and with 307 00:09:33,310 --> 00:09:35,680 associations, now let's talk about associations classes. 308 00:09:37,030 --> 00:09:38,770 Association classes are pretty straight forward. 309 00:09:39,380 --> 00:09:40,020 We're not going to generate new 310 00:09:40,200 --> 00:09:41,200 relations more then we're just 311 00:09:41,240 --> 00:09:42,680 going to add attributes to 312 00:09:42,800 --> 00:09:44,200 relations that we're generating anyway, 313 00:09:44,880 --> 00:09:46,030 so let me use our usual example 314 00:09:46,500 --> 00:09:47,670 with the students and colleges and 315 00:09:47,850 --> 00:09:49,320 we'll add some information to their application. 316 00:09:51,010 --> 00:09:52,090 Ok, so, here's our UML diagram. 317 00:09:52,610 --> 00:09:54,080 We still have students applying to 318 00:09:54,420 --> 00:09:55,460 colleges and then we've added 319 00:09:56,220 --> 00:09:58,330 an association class, which 320 00:09:58,580 --> 00:10:00,320 as you remember is attached to 321 00:10:00,470 --> 00:10:02,240 an association and it 322 00:10:02,350 --> 00:10:03,950 gives extra attributes for that association. 323 00:10:04,700 --> 00:10:05,670 So, here we're saying when a 324 00:10:05,700 --> 00:10:06,980 student applies to a college, 325 00:10:07,570 --> 00:10:08,560 we'll have a date of that 326 00:10:08,860 --> 00:10:10,390 application and a decision of that application. 327 00:10:12,120 --> 00:10:13,410 The first scheme that I've 328 00:10:13,550 --> 00:10:14,530 shown down here is what 329 00:10:14,730 --> 00:10:15,810 we would get if we use 330 00:10:16,030 --> 00:10:17,330 the techniques we've already learned without 331 00:10:18,100 --> 00:10:19,300 the association class so the student 332 00:10:20,930 --> 00:10:22,350 class generates the student relation, 333 00:10:23,230 --> 00:10:25,220 college generates college and then 334 00:10:25,350 --> 00:10:27,470 the applied association generates a 335 00:10:27,640 --> 00:10:29,420 relation that has a key from each side. 336 00:10:30,080 --> 00:10:30,990 And by the way, right now 337 00:10:31,140 --> 00:10:32,210 let's say that there's no 338 00:10:32,790 --> 00:10:34,490 multiplicities or other 339 00:10:34,720 --> 00:10:35,650 information that's going to give 340 00:10:35,730 --> 00:10:36,880 us special keys so in 341 00:10:37,020 --> 00:10:38,450 that case the applied relation doesn't 342 00:10:38,740 --> 00:10:40,220 have any keys other than 343 00:10:40,580 --> 00:10:41,810 the two attributes together. 344 00:10:42,520 --> 00:10:43,850 And further more, because we 345 00:10:43,950 --> 00:10:45,570 have no special multiplicities, we 346 00:10:45,690 --> 00:10:46,810 won't be folding that relation 347 00:10:47,350 --> 00:10:49,360 into others as we showed in the previous example. 348 00:10:50,620 --> 00:10:52,480 So, now what do we do with our association class? 349 00:10:52,860 --> 00:10:55,170 Well, as you can probably guess, it's quite simple. 350 00:10:55,910 --> 00:10:57,490 All we do is extend our 351 00:10:57,670 --> 00:10:59,170 applied relation to include 352 00:10:59,710 --> 00:11:01,750 the attributes in the association class. 353 00:11:02,430 --> 00:11:03,830 So, we just add date and 354 00:11:04,080 --> 00:11:06,610 decision right here and then we're all set. 355 00:11:07,690 --> 00:11:10,640 Now there's actually a few things I want to mention at this point. 356 00:11:11,480 --> 00:11:12,990 First of all, I discussed in 357 00:11:13,250 --> 00:11:14,760 the video on UMO modeling. 358 00:11:14,980 --> 00:11:16,610 The fact that UMO assumes 359 00:11:16,950 --> 00:11:18,540 that when you have a relationship or 360 00:11:18,690 --> 00:11:20,550 an association between two 361 00:11:20,720 --> 00:11:22,060 classes, there's at most 362 00:11:22,530 --> 00:11:24,210 one instance of that 363 00:11:24,420 --> 00:11:27,200 association between any two elements of the class. 364 00:11:27,940 --> 00:11:29,220 And that is, can be 365 00:11:29,340 --> 00:11:30,280 seen quite well in this 366 00:11:30,420 --> 00:11:31,470 relational scheme up because 367 00:11:31,770 --> 00:11:33,120 we're assuming we have a 368 00:11:33,140 --> 00:11:35,050 most one relationship between any 369 00:11:35,290 --> 00:11:37,110 student and any college and 370 00:11:37,230 --> 00:11:38,630 then we have associated with that 371 00:11:38,850 --> 00:11:40,690 one relationship, the date and the decision. 372 00:11:41,340 --> 00:11:42,280 A second thing I wanted 373 00:11:42,800 --> 00:11:43,670 to talk about at this point 374 00:11:43,940 --> 00:11:45,410 in time is the fact that 375 00:11:45,610 --> 00:11:48,300 we for an automatic translation require 376 00:11:48,980 --> 00:11:51,550 that we have a key for every regular class. 377 00:11:52,940 --> 00:11:53,770 When I mentioned that at the 378 00:11:53,830 --> 00:11:55,010 beginning of the video, I wasn't 379 00:11:55,240 --> 00:11:56,130 able to motivate it very 380 00:11:56,340 --> 00:11:58,460 well but this example explains it pretty well. 381 00:11:59,210 --> 00:12:01,450 When we want automatic translation, the 382 00:12:02,030 --> 00:12:03,150 translation for an association 383 00:12:04,280 --> 00:12:05,620 requires a key from each 384 00:12:05,790 --> 00:12:06,900 side, and each side 385 00:12:07,220 --> 00:12:09,070 is going to be a regular class in that case. 386 00:12:09,620 --> 00:12:10,520 So, we needed to have a 387 00:12:10,560 --> 00:12:11,690 student ID or some 388 00:12:11,920 --> 00:12:12,920 key for the student class and 389 00:12:13,420 --> 00:12:14,250 we needed a key for the 390 00:12:14,350 --> 00:12:16,130 college class in order to capture the association. 391 00:12:17,440 --> 00:12:19,020 On the other hand, here the 392 00:12:19,210 --> 00:12:20,760 application info is not 393 00:12:21,300 --> 00:12:22,580 a regular class, this is 394 00:12:22,630 --> 00:12:23,830 an association class and we're 395 00:12:23,920 --> 00:12:24,930 just defining that as not 396 00:12:25,130 --> 00:12:26,540 regular and we don't need 397 00:12:26,830 --> 00:12:27,810 to have a key for this one 398 00:12:28,240 --> 00:12:29,250 because we're just adding their 399 00:12:29,430 --> 00:12:31,290 attributes to the relation 400 00:12:31,900 --> 00:12:34,120 for the association itself and the 401 00:12:34,820 --> 00:12:35,400 last thing that I wanted to mention 402 00:12:35,920 --> 00:12:36,930 is that the rules we 403 00:12:37,090 --> 00:12:39,110 saw for determining keys for 404 00:12:40,210 --> 00:12:42,500 the relation associated with associations 405 00:12:44,110 --> 00:12:46,120 and for folding in the 406 00:12:46,260 --> 00:12:47,630 class and relations for associations 407 00:12:48,820 --> 00:12:49,590 also work when we have 408 00:12:49,770 --> 00:12:51,840 an association class and it just goes naturally. 409 00:12:52,540 --> 00:12:54,450 You just bring the attributes with 410 00:12:54,700 --> 00:12:56,630 the association class along with 411 00:12:57,150 --> 00:12:59,340 the attributes that you have for the association. 412 00:13:00,730 --> 00:13:01,800 You might want to give that 413 00:13:01,990 --> 00:13:03,310 a try on an example just to 414 00:13:03,410 --> 00:13:05,180 see how it works but again it's pretty straight forward. 415 00:13:06,510 --> 00:13:07,320 Now let's take a look at 416 00:13:07,400 --> 00:13:10,600 how self associations are translated to relations. 417 00:13:11,600 --> 00:13:12,630 It actually follows exactly the same 418 00:13:12,850 --> 00:13:15,200 rules but it's worth while looking at to see exactly what happens. 419 00:13:16,150 --> 00:13:17,170 So here's our first example 420 00:13:17,830 --> 00:13:18,770 of association which was sibling 421 00:13:19,240 --> 00:13:20,420 relationships between students. 422 00:13:21,310 --> 00:13:22,420 So in this case, we generate 423 00:13:22,890 --> 00:13:23,850 the relation for the class 424 00:13:24,490 --> 00:13:25,810 as usual, student ID, name and 425 00:13:26,240 --> 00:13:27,750 GPA and then we 426 00:13:27,980 --> 00:13:29,250 generate a relation for our 427 00:13:29,390 --> 00:13:30,860 association just following the 428 00:13:30,950 --> 00:13:32,630 same that we already had 429 00:13:32,810 --> 00:13:34,030 when we had an association between 430 00:13:34,670 --> 00:13:36,200 two different classes so we 431 00:13:36,320 --> 00:13:37,810 need to grab one key from 432 00:13:38,080 --> 00:13:39,600 each side, well it's the 433 00:13:39,700 --> 00:13:41,240 same side that we're 434 00:13:41,370 --> 00:13:42,240 grabbing from so the one 435 00:13:42,570 --> 00:13:43,870 thing we need to do is just 436 00:13:44,230 --> 00:13:45,440 change the attribute names 437 00:13:45,830 --> 00:13:46,840 so that we have two instances 438 00:13:48,180 --> 00:13:50,620 of the key attribute from the class, but they have different names. 439 00:13:51,500 --> 00:13:52,840 So you can see what we're doing here. 440 00:13:52,990 --> 00:13:54,610 We're just saying that we have sibling relationship. 441 00:13:55,390 --> 00:13:56,690 We take the student ID from 442 00:13:56,850 --> 00:13:58,440 the two students who are 443 00:13:58,560 --> 00:13:59,710 siblings, and that pair 444 00:13:59,950 --> 00:14:00,750 becomes a tuple in our 445 00:14:00,860 --> 00:14:02,530 sibling relation and because 446 00:14:02,910 --> 00:14:04,170 so we have stars on both sides 447 00:14:04,630 --> 00:14:06,530 so we're not making any assumptions about multiplicities. 448 00:14:07,050 --> 00:14:09,350 We don't have any zero dot dot ones or one dot dot ones. 449 00:14:10,000 --> 00:14:11,120 Then we don't have any key 450 00:14:11,550 --> 00:14:13,940 for the sibling relation except the two attributes together. 451 00:14:15,370 --> 00:14:16,210 Now, the other example we had 452 00:14:16,400 --> 00:14:18,510 in self-association, was colleges 453 00:14:19,090 --> 00:14:20,270 being branches of other colleges. 454 00:14:21,290 --> 00:14:22,790 So here's our college relation 455 00:14:23,290 --> 00:14:24,930 that we generate with the 456 00:14:25,000 --> 00:14:26,680 usual attributes and then 457 00:14:27,100 --> 00:14:29,130 we have our, that's the key there. 458 00:14:29,290 --> 00:14:31,210 We have our association which we're 459 00:14:31,470 --> 00:14:33,250 calling branch which takes 460 00:14:33,670 --> 00:14:34,990 a home campus and a 461 00:14:35,290 --> 00:14:36,900 satellite campus and since we've 462 00:14:37,170 --> 00:14:38,240 labeled those two sides we can 463 00:14:38,380 --> 00:14:39,830 actually use those labels as 464 00:14:40,030 --> 00:14:41,770 the attributes for the relation we generate. 465 00:14:42,600 --> 00:14:43,990 So we'll call it branch and 466 00:14:44,150 --> 00:14:45,680 we'll have home and satellite. 467 00:14:46,250 --> 00:14:47,630 So that's saying that the 468 00:14:47,870 --> 00:14:49,230 satellite, the campus on 469 00:14:49,480 --> 00:14:50,880 the, the college on the 470 00:14:50,960 --> 00:14:52,600 right hand side is a 471 00:14:52,910 --> 00:14:54,630 satellite of the one on the left hand side. 472 00:14:55,090 --> 00:14:56,700 Now these values here will 473 00:14:57,230 --> 00:14:59,290 be college names that 474 00:14:59,450 --> 00:15:00,600 will be the domain that we're 475 00:15:00,730 --> 00:15:01,630 drawing from but we'll calling 476 00:15:01,940 --> 00:15:04,150 home and satellite to distinguish their roles in the association. 477 00:15:05,750 --> 00:15:06,380 Now what about keys? 478 00:15:06,870 --> 00:15:08,630 We said before that every 479 00:15:09,900 --> 00:15:11,630 campus, every satellite campus 480 00:15:12,010 --> 00:15:14,690 has exactly one home campus so we write it that way. 481 00:15:15,310 --> 00:15:16,650 And that a home campus could have, 482 00:15:16,870 --> 00:15:18,000 I think we said something like 483 00:15:18,260 --> 00:15:20,500 zero to ten satellite campuses but 484 00:15:20,620 --> 00:15:21,900 in terms of generating our relations 485 00:15:22,510 --> 00:15:23,730 the only really important thing 486 00:15:23,880 --> 00:15:25,370 is this one to one and 487 00:15:25,550 --> 00:15:26,580 if you remember what our rules 488 00:15:27,000 --> 00:15:28,260 said, it said that 489 00:15:28,480 --> 00:15:29,490 if we have one one to 490 00:15:29,650 --> 00:15:31,430 one on one side then 491 00:15:31,660 --> 00:15:33,000 the other side is a 492 00:15:33,260 --> 00:15:34,520 key in the association relation. 493 00:15:35,330 --> 00:15:36,240 So what that would tell us 494 00:15:36,340 --> 00:15:37,630 is that satellite is a 495 00:15:37,700 --> 00:15:39,040 key here and that does 496 00:15:39,170 --> 00:15:40,250 make sense, so we'll only 497 00:15:40,620 --> 00:15:42,200 have each satellites home campus 498 00:15:42,690 --> 00:15:44,260 listed once and, but 499 00:15:44,290 --> 00:15:45,380 a home might have many 500 00:15:45,800 --> 00:15:47,810 satellites so the left hand would not be a key. 501 00:15:48,860 --> 00:15:49,860 Ok, we're getting there. 502 00:15:50,100 --> 00:15:52,640 We finished the first three and now it's time for sub classes. 503 00:15:53,050 --> 00:15:53,760 This is a pretty big one. 504 00:15:54,950 --> 00:15:55,990 First of all, it turns out that 505 00:15:56,170 --> 00:15:57,610 there's well atleast three and 506 00:15:57,790 --> 00:16:00,210 three commonly use translations from 507 00:16:00,450 --> 00:16:02,290 a sub class relationship into 508 00:16:02,570 --> 00:16:03,840 relations so let me 509 00:16:04,020 --> 00:16:06,080 setup a generic sub classing setup 510 00:16:06,740 --> 00:16:07,630 and then we'll talk about the three 511 00:16:07,890 --> 00:16:10,310 different translation and when we might use which one. 512 00:16:11,250 --> 00:16:14,180 So, here's our generic set up, I had to draw it a little funny to fit it in the space. 513 00:16:14,830 --> 00:16:16,240 S is our super class 514 00:16:17,200 --> 00:16:18,400 and then we have two sub 515 00:16:18,650 --> 00:16:20,360 classes S1 and S2 516 00:16:20,760 --> 00:16:21,730 and just as a reminder 517 00:16:22,170 --> 00:16:23,040 what this says is that we 518 00:16:23,200 --> 00:16:24,570 have object of type S 519 00:16:24,910 --> 00:16:25,960 and they have a key attribute 520 00:16:26,370 --> 00:16:27,400 call K and another attribute 521 00:16:27,850 --> 00:16:29,330 call A and then 522 00:16:30,160 --> 00:16:31,300 we have sub classes of those, 523 00:16:31,560 --> 00:16:32,610 one called S1 which will 524 00:16:32,920 --> 00:16:34,200 have attributes K and A 525 00:16:34,490 --> 00:16:35,680 and will also have an attribute 526 00:16:36,020 --> 00:16:37,260 B and the difference to 527 00:16:37,350 --> 00:16:38,720 the sub class as two that 528 00:16:38,830 --> 00:16:39,980 will have attributes K and A 529 00:16:40,150 --> 00:16:41,410 inherited from its parent and 530 00:16:41,540 --> 00:16:44,170 then an additional attribute C. Now 531 00:16:44,330 --> 00:16:45,330 there are three different ways, 532 00:16:46,130 --> 00:16:47,330 as I said, atleast three that we 533 00:16:47,460 --> 00:16:50,590 could translate this UML diagram to relations. 534 00:16:51,610 --> 00:16:53,580 In the first way, we 535 00:16:53,840 --> 00:16:55,440 have a relation for each 536 00:16:55,800 --> 00:16:57,010 of our classes including the sub-classes 537 00:16:58,160 --> 00:16:59,940 and the sub-class relations will contain 538 00:17:00,680 --> 00:17:03,410 their own attributes, plus the key of the super class. 539 00:17:04,540 --> 00:17:05,970 In the second case, we 540 00:17:06,190 --> 00:17:08,020 still have one relation for 541 00:17:08,350 --> 00:17:10,190 each of our three classes, but 542 00:17:10,340 --> 00:17:11,960 in this case the sub-class relation contains 543 00:17:12,390 --> 00:17:13,570 all of the attributes from the 544 00:17:13,730 --> 00:17:15,080 super class and then the 545 00:17:15,570 --> 00:17:16,500 third case we just use 546 00:17:16,670 --> 00:17:18,030 one mega relation for the 547 00:17:18,180 --> 00:17:19,580 whole thing that contains all 548 00:17:19,930 --> 00:17:21,800 of the super class and sub-class attributes. 549 00:17:22,680 --> 00:17:23,570 So let's take a look at 550 00:17:23,670 --> 00:17:24,950 what the three different translations 551 00:17:25,580 --> 00:17:26,990 would produce for this particular setup. 552 00:17:27,890 --> 00:17:29,230 For the first one we'll have 553 00:17:29,470 --> 00:17:30,760 our relation for S that's 554 00:17:30,960 --> 00:17:32,180 going to have K and A, 555 00:17:33,010 --> 00:17:34,410 then we'll have one relation for 556 00:17:34,530 --> 00:17:36,130 each of the two sub-classes, S1, 557 00:17:36,410 --> 00:17:37,930 that will contain the key 558 00:17:38,300 --> 00:17:39,340 for the super class, so it 559 00:17:39,390 --> 00:17:40,920 will be K and that 560 00:17:41,060 --> 00:17:41,900 will still be a key here for 561 00:17:42,040 --> 00:17:44,010 the sub-class and B. So, 562 00:17:44,140 --> 00:17:46,110 to find all components of 563 00:17:46,880 --> 00:17:48,430 an object of S1 will 564 00:17:48,710 --> 00:17:50,340 get it's A competent from 565 00:17:50,580 --> 00:17:53,530 the super class, following that key, and then, we'll get the B component. 566 00:17:53,960 --> 00:17:55,500 from this relation and then 567 00:17:55,680 --> 00:17:57,270 finally say S2 will be 568 00:17:57,380 --> 00:17:58,270 similar, it will have the 569 00:17:58,330 --> 00:17:59,290 key for the super class 570 00:17:59,970 --> 00:18:01,870 and attribute C, again what 571 00:18:02,080 --> 00:18:03,470 this will require is some assembly 572 00:18:04,310 --> 00:18:06,760 to get all attributes of the sub classes. 573 00:18:07,800 --> 00:18:08,770 Now, let's look at the second translation. 574 00:18:09,450 --> 00:18:10,530 We still have the super class, 575 00:18:11,090 --> 00:18:12,710 and our sub classes now 576 00:18:12,980 --> 00:18:14,350 are going to contain all of 577 00:18:14,440 --> 00:18:15,290 the attributes that are relevant 578 00:18:15,800 --> 00:18:17,930 so they'll have the key attribute from the super class. 579 00:18:18,550 --> 00:18:19,940 They'll also have attribute A 580 00:18:20,290 --> 00:18:21,930 and attribute B and then 581 00:18:22,300 --> 00:18:24,120 S2 will similarly have 582 00:18:24,350 --> 00:18:25,440 K and then A 583 00:18:25,830 --> 00:18:27,210 and then C. So in 584 00:18:27,670 --> 00:18:28,680 this case what we can see is 585 00:18:28,770 --> 00:18:29,870 that when we have an object 586 00:18:29,960 --> 00:18:31,050 that's in a sub class all 587 00:18:31,430 --> 00:18:32,560 of it's information will be in 588 00:18:32,650 --> 00:18:34,490 it's sub class relation and the 589 00:18:34,590 --> 00:18:35,840 super class will only be 590 00:18:35,980 --> 00:18:37,030 used for objects that are 591 00:18:37,180 --> 00:18:39,420 in the super class but not in any of the sub classes. 592 00:18:41,390 --> 00:18:42,740 And the third translation says, let's 593 00:18:43,100 --> 00:18:45,030 just make a mega relation that contains everything. 594 00:18:45,550 --> 00:18:46,320 So, it will have the key 595 00:18:46,580 --> 00:18:48,520 attribute K and A 596 00:18:49,240 --> 00:18:51,290 and B and C. And 597 00:18:51,500 --> 00:18:52,510 in that case, we can 598 00:18:52,700 --> 00:18:53,670 see that we may have some 599 00:18:53,980 --> 00:18:55,320 null values, so if we 600 00:18:55,490 --> 00:18:56,610 have an object that's only 601 00:18:56,950 --> 00:18:58,280 in sub class as one for 602 00:18:58,530 --> 00:18:59,660 example, it would then 603 00:18:59,840 --> 00:19:01,890 have a null attribute C. If 604 00:19:02,020 --> 00:19:03,020 we add an object that was 605 00:19:03,270 --> 00:19:04,270 only in the super class and 606 00:19:04,450 --> 00:19:05,060 not in any of the sub 607 00:19:05,380 --> 00:19:06,190 classes it would have a 608 00:19:06,230 --> 00:19:07,310 null for both B and C. 609 00:19:08,570 --> 00:19:09,510 Now as you may have noticed, 610 00:19:09,920 --> 00:19:11,110 what has already been on 611 00:19:11,250 --> 00:19:12,730 the slide the entire time is 612 00:19:12,830 --> 00:19:15,160 that the best translation may depend 613 00:19:15,770 --> 00:19:16,860 on the properties of the sub 614 00:19:17,130 --> 00:19:18,600 class relationship and if 615 00:19:18,780 --> 00:19:20,000 you remember, we had the properties 616 00:19:20,750 --> 00:19:24,230 overlapping versus disjoint, and complete versus incomplete. 617 00:19:25,150 --> 00:19:26,530 So overlapping meant that we 618 00:19:26,680 --> 00:19:28,150 might have objects that were 619 00:19:28,360 --> 00:19:29,480 in more than one sub class, 620 00:19:29,860 --> 00:19:31,060 disjoint meant that's not possible, 621 00:19:31,980 --> 00:19:33,170 complete said that every 622 00:19:33,650 --> 00:19:34,620 object that's in the super 623 00:19:34,820 --> 00:19:35,740 class and the super class 624 00:19:36,000 --> 00:19:37,180 is also in at least 625 00:19:37,420 --> 00:19:38,980 one of the sub classes, where incomplete 626 00:19:40,020 --> 00:19:40,890 said that there would be some 627 00:19:41,450 --> 00:19:42,720 objects, or they could be they're 628 00:19:42,810 --> 00:19:44,670 in the super class and not in any sub classes. 629 00:19:45,120 --> 00:19:46,600 Sort of, alluded to how 630 00:19:46,720 --> 00:19:48,660 some of those properties affect the different translations. 631 00:19:49,420 --> 00:19:50,460 But, let's look at it very specifically. 632 00:19:51,960 --> 00:19:53,250 Let's suppose, for example, that 633 00:19:53,450 --> 00:19:55,210 we had a heavily overlapping 634 00:19:56,460 --> 00:19:57,470 sub class relationship. 635 00:19:58,150 --> 00:19:59,440 In other words, we have many 636 00:20:00,140 --> 00:20:00,890 objects that are in multiple 637 00:20:01,630 --> 00:20:03,390 subclasses; if that's the 638 00:20:03,620 --> 00:20:04,590 case then we might prefer 639 00:20:05,050 --> 00:20:06,340 design three because design 640 00:20:06,860 --> 00:20:08,670 three captures all of 641 00:20:08,750 --> 00:20:09,910 the different attributes in one 642 00:20:10,270 --> 00:20:11,700 place so if many objects have 643 00:20:12,050 --> 00:20:13,120 all those attributes we might 644 00:20:13,330 --> 00:20:14,160 like to just have them together 645 00:20:14,940 --> 00:20:17,260 rather than needing to assemble them from the different pieces. 646 00:20:18,610 --> 00:20:19,540 On the other hand, let's say 647 00:20:19,790 --> 00:20:21,480 that we have have a disjoint 648 00:20:22,380 --> 00:20:23,920 and furthermore, let's say it's complete. 649 00:20:24,500 --> 00:20:26,090 So, we have every 650 00:20:26,410 --> 00:20:28,130 element being in exactly one 651 00:20:28,430 --> 00:20:29,210 subclass and there are no 652 00:20:29,530 --> 00:20:31,650 elements elements that are in just the super class. 653 00:20:32,090 --> 00:20:33,100 So in that case, we might 654 00:20:33,360 --> 00:20:35,510 like to use design two because 655 00:20:36,000 --> 00:20:37,900 design two puts each object 656 00:20:38,310 --> 00:20:41,400 in individual subclass relation and 657 00:20:41,730 --> 00:20:43,290 furthermore, since it's complete, we 658 00:20:43,450 --> 00:20:44,510 could actually, in this particular 659 00:20:45,130 --> 00:20:46,740 case, get rid of the first relation. 660 00:20:47,140 --> 00:20:48,290 We wouldn't need that, and so 661 00:20:48,450 --> 00:20:49,640 the best design would be number 662 00:20:49,940 --> 00:20:52,040 2, modified to only have the subclass relations. 663 00:20:53,540 --> 00:20:54,960 So, now, let's revisit our 664 00:20:55,260 --> 00:20:56,930 gigantic example from the 665 00:20:57,060 --> 00:20:59,900 UML modeling video and see how that would be translated to relations. 666 00:21:01,190 --> 00:21:02,220 So, just to remind you 667 00:21:02,300 --> 00:21:03,210 what's going on in this big 668 00:21:03,330 --> 00:21:05,720 diagram we have students, and 669 00:21:05,860 --> 00:21:06,970 students have a student 670 00:21:07,140 --> 00:21:08,340 ID and a name and then 671 00:21:08,540 --> 00:21:11,020 some of our students might be foreign students and they have a country. 672 00:21:12,080 --> 00:21:13,040 Some of our students may be domestic 673 00:21:13,560 --> 00:21:14,620 students, and that they would 674 00:21:14,760 --> 00:21:16,320 have a state and a social security number. 675 00:21:16,620 --> 00:21:17,510 We'll assume that every student 676 00:21:17,830 --> 00:21:18,980 is either foreign or domestic, and 677 00:21:19,050 --> 00:21:20,510 then some of our 678 00:21:20,680 --> 00:21:22,110 students are AP students and interestingly, 679 00:21:22,560 --> 00:21:23,880 they have no attributes but, those 680 00:21:24,120 --> 00:21:25,000 are the ones who have an association 681 00:21:25,890 --> 00:21:27,050 called took with AP 682 00:21:27,510 --> 00:21:29,020 courses AP courses have 683 00:21:29,130 --> 00:21:30,460 a course number and a 684 00:21:30,570 --> 00:21:31,680 title and then, when a 685 00:21:31,730 --> 00:21:32,880 student takes an AP course, 686 00:21:33,270 --> 00:21:35,280 there's an association class that 687 00:21:35,480 --> 00:21:37,390 says the year they took it and the grade they got. 688 00:21:38,100 --> 00:21:40,610 So, let's translate this to relations; the whole diagram. 689 00:21:41,500 --> 00:21:42,670 And we're going to use the first 690 00:21:43,270 --> 00:21:45,730 translation from our three schemes. 691 00:21:46,150 --> 00:21:46,960 So, that's the one where the 692 00:21:47,090 --> 00:21:48,930 sub-class relations contain the 693 00:21:49,000 --> 00:21:51,270 super class keys and then all the specialized attributes. 694 00:21:52,450 --> 00:21:53,810 So, let's start by generating 695 00:21:54,350 --> 00:21:56,080 the relation for the student class 696 00:21:56,610 --> 00:21:58,020 and that's straightforward, that's just the 697 00:21:58,090 --> 00:21:59,150 student ID and the student 698 00:21:59,600 --> 00:22:01,640 name, with the student ID being the key. 699 00:22:02,400 --> 00:22:03,500 And, then we're going to generate one 700 00:22:03,880 --> 00:22:05,490 relation for each of our three sub-classes. 701 00:22:06,330 --> 00:22:07,210 So that would be the foreign 702 00:22:07,540 --> 00:22:08,980 students which will take 703 00:22:09,240 --> 00:22:10,220 the key then from the 704 00:22:10,490 --> 00:22:11,420 student relations, so their student 705 00:22:11,860 --> 00:22:13,370 ID and the country that they're from. 706 00:22:14,140 --> 00:22:16,300 They'll be the domestic students and 707 00:22:16,410 --> 00:22:17,380 there we'll have again, the student 708 00:22:17,840 --> 00:22:18,810 ID, inherited from the super 709 00:22:19,140 --> 00:22:20,350 class and the state 710 00:22:20,830 --> 00:22:22,940 they're from and their social security number. 711 00:22:24,780 --> 00:22:26,120 And, then we'll also have the AP 712 00:22:26,810 --> 00:22:28,630 students and those have 713 00:22:28,830 --> 00:22:30,250 none of their own attributes but, 714 00:22:30,410 --> 00:22:31,500 we can get the key, 715 00:22:32,270 --> 00:22:33,650 and we do, from the super class. 716 00:22:33,990 --> 00:22:35,090 So, this is just going to be 717 00:22:35,280 --> 00:22:37,790 a list of the student ID's who are AP students. 718 00:22:39,190 --> 00:22:39,800 Now, let's keep going. 719 00:22:40,160 --> 00:22:42,470 So, let's take a look at AP students taking courses. 720 00:22:42,970 --> 00:22:44,530 So, we'll have, from the 721 00:22:44,630 --> 00:22:47,570 AP class, a 722 00:22:47,830 --> 00:22:49,620 straight forward translation to the 723 00:22:49,740 --> 00:22:51,650 course number and the 724 00:22:51,820 --> 00:22:53,900 title of the course and 725 00:22:54,080 --> 00:22:55,240 then, finally, we're going to 726 00:22:55,500 --> 00:22:56,970 have a relation for the 727 00:22:57,150 --> 00:22:58,540 fact that a AP student took classes. 728 00:22:59,610 --> 00:23:01,540 We had some multiplicities on that 729 00:23:01,640 --> 00:23:02,650 the first time but, neither of 730 00:23:02,730 --> 00:23:04,080 them were a zero one or 731 00:23:04,260 --> 00:23:06,720 one one so they're not going to be relevant in changing our design. 732 00:23:07,610 --> 00:23:10,320 So, took is going to have the key from both sides. 733 00:23:10,790 --> 00:23:13,060 Well, we don't see any key in here do we? 734 00:23:13,340 --> 00:23:15,060 But, subclasses always inherit 735 00:23:15,530 --> 00:23:16,630 the key from their parents, so 736 00:23:16,740 --> 00:23:18,220 the key here is implicitly 737 00:23:18,670 --> 00:23:21,180 the student ID and, that's what we'll be using here. 738 00:23:21,570 --> 00:23:22,810 Student ID, the key 739 00:23:23,100 --> 00:23:24,230 from the right hand side, the 740 00:23:24,320 --> 00:23:25,930 course number, that they've taken 741 00:23:26,890 --> 00:23:27,960 and then we'll have the two 742 00:23:28,220 --> 00:23:29,600 attributes from the association class, 743 00:23:30,340 --> 00:23:31,250 the year and the grade. 744 00:23:32,470 --> 00:23:34,050 So this actually looks like a pretty good design. 745 00:23:34,470 --> 00:23:35,290 Let me just make a few comments. 746 00:23:36,460 --> 00:23:38,580 The first one has to do with the AP student relation. 747 00:23:39,500 --> 00:23:41,070 So if every AP student 748 00:23:41,530 --> 00:23:44,170 does have to, does have to take at least one course. 749 00:23:44,990 --> 00:23:46,220 So, if we have, for example, 750 00:23:46,790 --> 00:23:47,880 a one dot dot something 751 00:23:48,360 --> 00:23:49,730 over there, then, we could 752 00:23:50,190 --> 00:23:51,890 actually eliminate it's relation because 753 00:23:52,450 --> 00:23:53,780 every student ID that 754 00:23:53,890 --> 00:23:55,410 appears in AP student, will 755 00:23:55,620 --> 00:23:56,960 also appear in the 756 00:23:57,040 --> 00:23:58,470 Took relation, so, this one 757 00:23:58,750 --> 00:24:00,630 will be redundant in that case. 758 00:24:00,840 --> 00:24:01,840 So, again, that could be 759 00:24:01,950 --> 00:24:03,330 eliminated based on the multiplicity, 760 00:24:03,730 --> 00:24:05,270 though, I wouldn't really expect an 761 00:24:05,440 --> 00:24:07,780 automatic translator to necessarily figure that one out. 762 00:24:09,030 --> 00:24:10,370 Another possibility I should mention, 763 00:24:10,850 --> 00:24:12,250 is that we could have a primary 764 00:24:12,580 --> 00:24:14,060 key specified here for 765 00:24:14,160 --> 00:24:15,410 domestic students for social security 766 00:24:15,780 --> 00:24:16,900 numbers since we would expect that 767 00:24:17,060 --> 00:24:18,120 to be unique, and that 768 00:24:18,380 --> 00:24:19,370 would translate to a key 769 00:24:19,690 --> 00:24:22,280 here, but let me be clear, this would be a separate key. 770 00:24:22,500 --> 00:24:23,860 It wouldn't be social security number and 771 00:24:23,960 --> 00:24:25,080 student ID together, but these 772 00:24:25,280 --> 00:24:26,890 would be two different keys and 773 00:24:27,030 --> 00:24:28,760 in SQL you can actually distinguish 774 00:24:29,220 --> 00:24:30,110 between those two, but we 775 00:24:30,200 --> 00:24:31,830 can't do it the way we've written the relations here. 776 00:24:33,030 --> 00:24:34,290 And, finally let me mention again 777 00:24:34,680 --> 00:24:36,110 that requirement that we 778 00:24:36,270 --> 00:24:38,040 have keys for regular classes 779 00:24:39,130 --> 00:24:40,180 if we want to do translation 780 00:24:41,020 --> 00:24:42,740 automatically and you might 781 00:24:42,950 --> 00:24:44,050 have noticed that for example, we 782 00:24:44,370 --> 00:24:45,350 certainly didn't have a key here 783 00:24:45,700 --> 00:24:46,830 for a foreign student with country 784 00:24:47,220 --> 00:24:48,760 being the only attribute, but subclasses 785 00:24:49,480 --> 00:24:51,350 are also not considered regular classes. 786 00:24:52,240 --> 00:24:54,240 So subclasses and association 787 00:24:55,080 --> 00:24:56,070 classes don't need to have keys 788 00:24:56,450 --> 00:24:58,170 and we can still have an automatic translation. 789 00:24:59,140 --> 00:25:00,790 Logically subclasses are inheriting 790 00:25:01,490 --> 00:25:03,250 their keys from their super-class 791 00:25:04,130 --> 00:25:05,310 and that one does need to 792 00:25:05,540 --> 00:25:07,490 have a key in order to have automatic translation. 793 00:25:09,170 --> 00:25:10,070 So that was a big one. 794 00:25:10,620 --> 00:25:11,410 But you'll be glad to know 795 00:25:11,570 --> 00:25:13,770 that composition and aggregation is going to be quite quick. 796 00:25:15,040 --> 00:25:16,320 So here is our example where 797 00:25:16,460 --> 00:25:17,870 we have our college class as 798 00:25:18,030 --> 00:25:19,880 usual, but colleges contain 799 00:25:20,500 --> 00:25:22,050 departments, and this solid diamond 800 00:25:22,510 --> 00:25:24,050 here is the composition operator 801 00:25:24,740 --> 00:25:25,620 that says that we have objects 802 00:25:26,010 --> 00:25:27,650 from the right hand side class that 803 00:25:27,780 --> 00:25:30,130 are components of the left hand side objects. 804 00:25:31,290 --> 00:25:32,630 So let's look at the translation to relations. 805 00:25:33,410 --> 00:25:34,740 We'll translate the college side 806 00:25:35,230 --> 00:25:36,700 as usual with a key 807 00:25:37,280 --> 00:25:39,180 attribute - college name and the other attributes. 808 00:25:40,410 --> 00:25:41,250 And then although this is an 809 00:25:41,410 --> 00:25:42,510 association, we're not going 810 00:25:42,540 --> 00:25:43,910 to have a separate class for 811 00:25:44,210 --> 00:25:45,230 it, we're going to have the 812 00:25:45,340 --> 00:25:46,530 right hand side class 813 00:25:47,220 --> 00:25:48,720 have both the values of 814 00:25:48,750 --> 00:25:49,700 the object in that class 815 00:25:50,280 --> 00:25:51,530 and the association captured together. 816 00:25:52,790 --> 00:25:54,340 So we'll have a relation called 817 00:25:54,580 --> 00:25:56,310 department, and it 818 00:25:56,410 --> 00:25:58,020 will have the department name 819 00:25:58,490 --> 00:25:59,990 and the building and then 820 00:26:00,250 --> 00:26:02,240 it will also include, the 821 00:26:02,400 --> 00:26:04,520 key of the object it belongs to. 822 00:26:04,730 --> 00:26:06,460 So in this case, that would be a college name. 823 00:26:07,460 --> 00:26:08,630 Now if you think carefully about it, 824 00:26:08,750 --> 00:26:10,280 this translation actually makes a 825 00:26:10,390 --> 00:26:11,530 lot of sense and is consistent with 826 00:26:11,700 --> 00:26:13,140 what we've already done. 827 00:26:13,410 --> 00:26:14,600 We said when we introduced 828 00:26:15,040 --> 00:26:16,210 the notion of composition that we 829 00:26:16,300 --> 00:26:17,930 have Effectively have by definition 830 00:26:18,470 --> 00:26:20,640 a one dot dot, one on the left hand side of the composition. 831 00:26:21,780 --> 00:26:23,360 So if we treated this 832 00:26:23,680 --> 00:26:26,080 as a regular association, used our 833 00:26:26,420 --> 00:26:28,060 regular translation and then 834 00:26:28,360 --> 00:26:29,590 used our rule that allowed 835 00:26:29,950 --> 00:26:30,740 us to get rid of association 836 00:26:31,390 --> 00:26:33,980 relations, we'd actually end up at exactly the same design. 837 00:26:35,290 --> 00:26:36,010 By the way this is a last 838 00:26:36,420 --> 00:26:37,630 example where we have a 839 00:26:37,830 --> 00:26:38,850 class that's not one of those 840 00:26:39,050 --> 00:26:40,930 regular classes that require a key. 841 00:26:41,210 --> 00:26:42,540 We don't have to have a 842 00:26:42,740 --> 00:26:43,870 key for the right-hand side 843 00:26:44,140 --> 00:26:46,960 of a composition in order to have an automatic translation. 844 00:26:48,690 --> 00:26:49,640 Aggregation, by the way, 845 00:26:49,890 --> 00:26:50,810 was the case where we have 846 00:26:51,250 --> 00:26:53,090 the MC diamond and 847 00:26:53,310 --> 00:26:54,700 that's implicitly a zero dot 848 00:26:55,010 --> 00:26:56,460 dot one instead of one dot dot one. 849 00:26:56,630 --> 00:26:57,600 We had an example with apartment 850 00:26:58,110 --> 00:26:59,190 buildings, I'm not going to 851 00:26:59,380 --> 00:27:00,150 bother to give that one again, 852 00:27:00,820 --> 00:27:03,410 but in that case where we have an aggregation should have in an association. 853 00:27:04,580 --> 00:27:05,400 All we need is the same 854 00:27:05,840 --> 00:27:07,350 design but the ability for 855 00:27:07,470 --> 00:27:09,110 that key we're grabbing from the 856 00:27:09,180 --> 00:27:10,200 left hand side to be 857 00:27:10,370 --> 00:27:11,860 null, and, then, everything works out fine. 858 00:27:13,170 --> 00:27:14,480 So, to conclude, the data modeling 859 00:27:14,810 --> 00:27:16,110 portion of UML, is a 860 00:27:16,380 --> 00:27:18,780 popular high level language for data base designs. 861 00:27:19,660 --> 00:27:21,180 It's graphical and it can 862 00:27:21,340 --> 00:27:22,970 be translated automatically to lead 863 00:27:23,220 --> 00:27:24,600 to relations, as long as 864 00:27:24,820 --> 00:27:26,390 every regular class has a 865 00:27:26,450 --> 00:27:28,120 key, and that typically is the case.