1
00:00:01,310 --> 00:00:03,879
alone working taxes 2013 she two rooms

2
00:00:03,879 --> 00:00:07,048
this video will be talking you through indexes

3
00:00:07,049 --> 00:00:11,620
what they are and how you can use them so gonna play along follow the link to

4
00:00:11,619 --> 00:00:16,350
the video to download section website and on taxes 2013 TV 05

5
00:00:16,350 --> 00:00:19,470
a and then its opener

6
00:00:19,469 --> 00:00:26,339
way up okay so if you're so last couple videos

7
00:00:26,339 --> 00:00:29,868
I we normalized a table

8
00:00:29,868 --> 00:00:35,140
which is we broke it down into discrete data structures

9
00:00:35,140 --> 00:00:38,910
I think the technical definition organization is

10
00:00:38,909 --> 00:00:42,269
and and we created

11
00:00:42,270 --> 00:00:45,760
originally this wasn't a

12
00:00:45,759 --> 00:00:49,929
and we had three tables songs albums and artists there's a link between artists

13
00:00:49,929 --> 00:00:50,600
and songs

14
00:00:50,600 --> 00:00:54,620
and then we realized that wasn't quite sufficient for the data model because

15
00:00:54,619 --> 00:00:55,899
some songs

16
00:00:55,899 --> 00:00:59,840
can be sung by more than one artist so we have to drop

17
00:00:59,840 --> 00:01:03,149
a table in the middle and recreate to

18
00:01:03,149 --> 00:01:06,210
want many relationships and the end result is

19
00:01:06,209 --> 00:01:10,719
a many to many relationship so that's all well and good

20
00:01:10,719 --> 00:01:14,379
but I something that can happen

21
00:01:14,379 --> 00:01:19,429
a.m. is you can sometimes end up in a situation where

22
00:01:19,430 --> 00:01:23,939
even to primary keys on enough

23
00:01:23,938 --> 00:01:29,319
for you wanna do and a good example here are close this if we go to

24
00:01:29,319 --> 00:01:35,809
or type songs military muscle me too

25
00:01:35,810 --> 00:01:39,299
junction if we take the song

26
00:01:39,299 --> 00:01:43,868
the sergeant pepper's Lonely Hearts Club Band which is written by McCartney

27
00:01:43,868 --> 00:01:47,090
early the this song

28
00:01:47,090 --> 00:01:52,579
is from the album sergeant Pepper's but it also appeared on the Blue Album

29
00:01:52,578 --> 00:01:56,468
which is released some use of this is a compilation album

30
00:01:56,468 --> 00:01:59,819
so what that means is that

31
00:01:59,819 --> 00:02:05,228
we would tax need to have more than one record that was contained

32
00:02:05,228 --> 00:02:09,610
information about the song so you're such a pepperoni October McCartney

33
00:02:09,610 --> 00:02:13,349
I'm and the album sergeant pepper's and then you can have another record which

34
00:02:13,348 --> 00:02:13,829
was set

35
00:02:13,830 --> 00:02:17,300
sergeant pepper's Lonely Hearts Club Band McCartney and the Blue Album

36
00:02:17,300 --> 00:02:20,800
so you actually know using three fields

37
00:02:20,800 --> 00:02:24,170
to back to see a.m.

38
00:02:24,169 --> 00:02:27,488
identify for the record key point being

39
00:02:27,489 --> 00:02:31,580
that you can have this name the song the artist and the album

40
00:02:31,580 --> 00:02:34,690
but that they too should rip 80 so

41
00:02:34,689 --> 00:02:37,840
how do we do that we

42
00:02:37,840 --> 00:02:41,590
do a similar thing like here where we create we at

43
00:02:41,590 --> 00:02:45,539
are problems into here then burn extra primera Cena

44
00:02:45,539 --> 00:02:50,900
well no that wouldn't work some wish I had to do it so first things first

45
00:02:50,900 --> 00:02:56,430
let bus can leave that how is bill S

46
00:02:56,430 --> 00:03:00,269
go to to production and it's good to design view

47
00:03:00,269 --> 00:03:03,700
and stop they should be a primary key

48
00:03:03,699 --> 00:03:08,568
are you insert a row

49
00:03:08,568 --> 00:03:11,679
we're gonna colder junction

50
00:03:11,680 --> 00:03:15,019
Friday so we're gonna give this table now a standard

51
00:03:15,019 --> 00:03:19,060
kinda primary key setup where you've got

52
00:03:19,060 --> 00:03:23,500
a to the standard ID which is an alternate number which in increments

53
00:03:23,500 --> 00:03:27,239
we save that I'm a call this on the primary key can go today she

54
00:03:27,239 --> 00:03:31,158
you we get there now please notice

55
00:03:31,158 --> 00:03:34,878
the the relationships that intact

56
00:03:34,878 --> 00:03:38,139
which is good organization in the crate

57
00:03:38,139 --> 00:03:42,979
an extra road for an extra field for the

58
00:03:42,979 --> 00:03:49,268
albums scope it back up in design view record aswan

59
00:03:49,269 --> 00:03:53,060
album ID SK

60
00:03:53,060 --> 00:03:58,699
and it's going to be number and we're gonna credit lookup field as well so we

61
00:03:58,699 --> 00:04:00,048
know what we're dealing with

62
00:04:00,049 --> 00:04:05,650
so como books a.m. row source

63
00:04:05,650 --> 00:04:10,879
be warm table opens column count

64
00:04:10,878 --> 00:04:14,560
to column widths 0 and for

65
00:04:14,560 --> 00:04:17,930
should do it and forget the day she view

66
00:04:17,930 --> 00:04:21,728
we can't use of purpose

67
00:04:21,728 --> 00:04:26,279
was sergeant purpose

68
00:04:26,279 --> 00:04:30,709
to things let it be cases safer

69
00:04:30,709 --> 00:04:34,109
close to go back to the most whose relationships

70
00:04:34,110 --> 00:04:40,150
and now lets track our mighty to this okay

71
00:04:40,149 --> 00:04:43,839
so we for

72
00:04:43,839 --> 00:04:47,258
done what we wanted to to

73
00:04:47,259 --> 00:04:50,560
and let's go to table albums and miss at

74
00:04:50,560 --> 00:04:55,490
the blue album

75
00:04:55,490 --> 00:04:58,990
and if you go back table junction

76
00:04:58,990 --> 00:05:02,180
we can now choose solder purposes mccartney

77
00:05:02,180 --> 00:05:05,930
and the Blue Album okay

78
00:05:05,930 --> 00:05:09,590
so this is how you would get it so that you can base

79
00:05:09,589 --> 00:05:12,739
on three keys bar from we've got

80
00:05:12,740 --> 00:05:16,840
is what happens if we to this

81
00:05:16,839 --> 00:05:20,819
and we choose to same values again

82
00:05:20,819 --> 00:05:24,269
okay doesn't restrict us and that's a major problem

83
00:05:24,269 --> 00:05:27,469
because this goes back to the original vision problems

84
00:05:27,470 --> 00:05:31,900
what do we do about repeating data we don't want repeating data so you set the

85
00:05:31,899 --> 00:05:33,599
table a pair we wanna

86
00:05:33,600 --> 00:05:39,199
were saying that we won I we are mine songs and artists are now been repeating

87
00:05:39,199 --> 00:05:43,750
we don't mind combinations of songs and artists were paying

88
00:05:43,750 --> 00:05:47,439
for artists and albums four songs and I haven't what we called have

89
00:05:47,439 --> 00:05:51,939
is combination of a song and artist and album repeating

90
00:05:51,939 --> 00:05:56,019
I'm and this is actually is that this can be an issue and is an issue if they

91
00:05:56,019 --> 00:05:57,668
order sometimes in the

92
00:05:57,668 --> 00:06:02,959
you have a table you need US three sometimes four fields to

93
00:06:02,959 --> 00:06:06,089
represent key so what we do

94
00:06:06,089 --> 00:06:09,689
as you drop into design view coming up here me

95
00:06:09,689 --> 00:06:12,819
to cut in taxes I'm

96
00:06:12,819 --> 00:06:17,340
and in books that happens indexes going to function like a

97
00:06:17,339 --> 00:06:21,310
per locker most for primary case over click here

98
00:06:21,310 --> 00:06:24,788
and type in Maine index

99
00:06:24,788 --> 00:06:28,149
K and down here

100
00:06:28,149 --> 00:06:31,629
choose unique yes

101
00:06:31,629 --> 00:06:34,709
and weak choose so my day

102
00:06:34,709 --> 00:06:38,109
call to start the now a mighty

103
00:06:38,110 --> 00:06:41,689
and what we're saying is that you can't the what we're saying is that you making

104
00:06:41,689 --> 00:06:42,550
a

105
00:06:42,550 --> 00:06:46,800
combination of the song artist and album which can be repeated

106
00:06:46,800 --> 00:06:51,639
keep when if you click in here is a song you know is this box that may come up

107
00:06:51,639 --> 00:06:55,660
where you can choose the type of index and then you click an artist and album

108
00:06:55,660 --> 00:06:56,870
that goes away

109
00:06:56,870 --> 00:07:00,348
and that's gonna go away until we put something else in this

110
00:07:00,348 --> 00:07:03,930
the index name pair so the

111
00:07:03,930 --> 00:07:08,240
a.m. indexes thought about his intelligence know if you have main index

112
00:07:08,240 --> 00:07:09,449
a

113
00:07:09,449 --> 00:07:13,629
and to you put something else in Maine in know in this column

114
00:07:13,629 --> 00:07:17,560
its gonna see in these to be the same index so what that mean

115
00:07:17,560 --> 00:07:20,668
well that no means if we saved a got it a she

116
00:07:20,668 --> 00:07:27,668
and we tryin reenter combination already exists

117
00:07:27,750 --> 00:07:31,149
you get this change requests able not successful because I would create

118
00:07:31,149 --> 00:07:32,848
duplicate values

119
00:07:32,848 --> 00:07:36,019
okay so the press escape order

120
00:07:36,019 --> 00:07:39,379
so that's an index I'm

121
00:07:39,379 --> 00:07:42,569
key point is you use an index to create

122
00:07:42,569 --> 00:07:45,810
a.m. a car primary key where it's

123
00:07:45,810 --> 00:07:49,439
based on more than one filled specifically more than two fields in a

124
00:07:49,439 --> 00:07:51,918
you can't use a many to many relationship

125
00:07:51,918 --> 00:07:55,389
is very useful and it does

126
00:07:55,389 --> 00:07:58,740
does happen from time to time

127
00:07:58,740 --> 00:08:00,259
so thanks for watchin see nexidia