Журнал LinuxFormat - перейти на главную

LXF157:Для про­дви­ну­тых. SQL

Материал из Linuxformat
Перейти к: навигация, поиск


Содержание

SQL: Ди­зайн ба­зы дан­ных

Hardcore Linux Про­верь­те се­бя на кру­том про­ек­те для про­дви­ну­тых поль­зо­ва­те­лей

Для боль­шин­ст­ва лю­дей ба­за дан­ных под­ра­зу­ме­ва­ет ре­ля­ци­он­ную ба­зу вро­де MySQL или SQLite. А Дейв Кросс соз­да­ет ба­зу дан­ных про те­ле­шоу.

Наш эксперт

Дэйв Кросс уже не пом­нит, ко­гда на­чал поль­зо­вать­ся SQL и соз­да­вать ба­зы дан­ных, а про­чи­тан­ным стан­дар­там ANSI SQL он и счет по­те­рял.

(thumbnail)
На глав­ной стра­ни­це при­ло­же­ния по­ка­зан спи­сок те­ле­шоу, со­дер­жа­щих­ся в ба­зе дан­ных.

B ре­ля­ци­он­ных ба­зах дан­ных с по­мо­щью SQL про­из­во­дит­ся и объ­яв­ление таб­лиц, и ра­бо­та с дан­ны­ми в этих таб­ли­цах. По­это­му внут­ри SQL есть два раз­лич­ных язы­ка – DDL (Data Definition Language – язык оп­ре­де­ления дан­ных) и DML (Data Manipulation Language – язык управ­ления дан­ны­ми). Сна­ча­ла по­зна­ко­мим­ся с DDL.

При соз­дании ба­зы дан­ных по­лез­но на­чать с пе­ре­чис­ления объ­ек­тов ре­аль­но­го ми­ра, сведения о ко­то­рых нуж­но хранить в ба­зе. Ка­ж­дый из та­ких объ­ек­тов ско­рее все­го бу­дет пред­став­лен таб­ли­цей в на­шей ба­зе дан­ных. Как обещано, мы бу­дем хранить дан­ные о те­ле­шоу, по­это­му пер­вой таб­ли­цей бу­дет tv_show. И под те­ле­шоу я имею в ви­ду нечто вро­де Док­то­ра Кто или Шер­ло­ка, а не от­дель­ную се­рию. По­это­му на­ша сле­дую­щая таб­ли­ца – episode [се­рия]. Но в боль­шин­ст­ве те­ле­шоу се­рии еще и объ­е­ди­ня­ют­ся в се­зо­ны, вот вам и сле­дую­щая таб­ли­ца – series [се­зо­ны].

Об­ри­су­ем сце­ну

На этом эта­пе по­лез­но на­ри­со­вать схе­му на­шей ба­зы дан­ных. Мы на­ри­су­ем так на­зы­вае­мую «схе­му сущ­ность–связь». На та­ких схе­мах таб­ли­цы (или сущ­но­сти) изо­бра­жа­ют­ся в ви­де пря­мо­угольников, а свя­зи ме­ж­ду ними – в ви­де линий:

LXF157.tut mysql.scre opt0.jpeg.png

Об­ра­ти­те внимание, что ка­ж­дая линия на дальнем кон­це раз­ветв­ля­ет­ся. Это оз­на­ча­ет, что с этой сто­ро­ны от­но­шения мо­жет быть несколь­ко эк­зем­п­ля­ров сущ­но­сти. Так, на­ша схе­ма го­во­рит, что у те­ле­шоу есть несколь­ко се­зо­нов, а в ка­ж­дом се­зоне – несколь­ко се­рий. Эти от­но­шения «один ко мно­гим» – клю­че­вая идея в про­ек­ти­ро­вании баз дан­ных. Вско­ре мы рас­смот­рим, как про­ек­ти­ро­вать таб­ли­цы с та­ки­ми от­но­шения­ми. Начнем с ко­ман­ды DDL для соз­дания на­шей ба­зы дан­ных. В од­ной уста­нов­ке MySQL мо­жет быть несколь­ко от­дель­ных баз дан­ных, и для ка­ж­до­го но­во­го про­ек­та сто­ит соз­да­вать но­вую ба­зу дан­ных и но­во­го поль­зо­ва­те­ля, ко­то­рый бу­дет вла­дель­цем таб­лиц в этой ба­зе дан­ных. Для на­ча­ла нуж­но под­клю­чить­ся к сер­ве­ру MySQL от имени су­пер­поль­зо­ва­те­ля-root, но по­сле соз­дания ба­зы дан­ных и но­во­го поль­зо­ва­те­ля ад­минист­ра­тив­ный доступ боль­ше не по­на­до­бит­ся.

Вот на­чаль­ная ко­ман­да для под­клю­чения к ба­зе дан­ных:

mysql -uroot -p

В боль­шин­ст­­ве уста­но­вок MySQL на­чаль­ный па­роль поль­зо­ва­те­ля root по умол­чанию пуст. Ес­ли вам он по­на­до­бит­ся, мо­же­те его из­менить.

Ко­ман­да для соз­дания но­вой ба­зы дан­ных до­воль­но про­ста:

create database television;

Точ­ка с за­пя­той в кон­це го­во­рит MySQL, что ко­ман­да за­кон­чи­лась. Ес­ли на­жать Enter в кон­це стро­ки, не за­вер­шаю­щей­ся точ­кой с за­пя­той, MySQL бу­дет ждать вво­да дальней­ших команд и ниче­го не вы­полнит. Таким способом лег­ко на­би­рать слож­ные ко­ман­ды, со­стоя­щие из несколь­ких строк. Ко­ман­да для соз­дания но­вого поль­зо­ва­те­ля менее оче­вид­на:

grant all on television.*

to ‘television’@’localhost’

identified by ‘PASSWORD’;

На са­мом де­ле, соз­дание но­во­го поль­зо­ва­те­ля – лишь по­боч­ный эф­фект этой ко­ман­ды. Ее основ­ная цель – дать поль­зо­ва­те­лю пра­ва досту­па к объ­ек­там ба­зы дан­ных. Здесь мы го­во­рим, что хо­тим дать все пра­ва досту­па ко всем объ­ек­там в ба­зе дан­ных television (television.*) поль­зо­ва­те­лю television, и он бу­дет под­клю­чать­ся с локаль­но­го хоста с па­ро­лем “PASSWORD”. При соз­дании ва­ше­го поль­зо­ва­те­ля со­ве­тую взять дру­гой па­роль! Те­перь есть на­де­ж­да под­клю­чить­ся к на­шей ба­зе дан­ных MySQL ко­ман­дой

mysql -utelevision -Dtelevision -p

Па­ра­метр -u — имя поль­зо­ва­те­ля, -D – имя ба­зы дан­ных, а -p ве­лит MySQL за­про­сить па­роль. Па­роль мож­но ука­зать пря­мо в команд­ной стро­ке, но ес­ли вы не уве­ре­ны, что никто не сто­ит у вас за спи­ной, луч­ше вве­сти его в от­вет на за­прос.

Соз­да­ем таб­ли­цы

По­ра соз­да­вать таб­ли­цы. Но пе­ред этим нуж­но ре­шить, ка­кие дан­ные для раз­лич­ных сущ­но­стей бу­дут хранить­ся. Эле­мент дан­ных в ба­зе – это стол­бец. В ка­ж­дой из на­ших таб­лиц их бу­дет со­всем немно­го: на­при­мер, в таб­ли­це tv_show их бу­дет все­го два. Для ка­ж­до­го столб­ца нуж­но ука­зать имя и тип дан­ных, оп­ре­де­ляю­щий, ка­кие дан­ные бу­дут там хранить­ся.

create table tv_show (

id integer not null auto_increment,

title varchar(100) not null,

primary key(id)

) engine=InnoDB;

(thumbnail)
Щелк­нув по се­зо­ну, вы по­лу­чи­те пол­ную ин­фор­ма­цию о се­зо­не и спи­сок се­рий.

Хо­тя это про­стая таб­ли­ца, в ее оп­ре­де­лении нуж­но кое-что объ­яснить. В таб­ли­це всегда сто­ит за­во­дить пер­вич­ный ключ [primary key]. Это це­лое чис­ло, уве­ли­чи­ваю­щее­ся на единицу с ка­ж­дой за­пи­сью, ко­то­рое од­но­знач­но оп­ре­де­ля­ет стро­ку в таб­ли­це. Я всегда на­зы­ваю этот стол­бец id. Мы оп­ре­де­ли­ли его как not null, что оз­на­ча­ет, что в нем всегда долж­но быть зна­чение; но так­же ука­зав auto_increment, мы по­зво­ля­ем MySQL са­мо­му за­пол­нять эти зна­чения. Ка­ж­дый раз при до­бав­лении но­вой стро­ки в таб­ли­цу MySQL бу­дет оп­ре­де­лять сле­дую­щее неис­поль­зо­ван­ное зна­чение иден­ти­фи­ка­то­ра и за­но­сить его в стол­бец id. За­тем мы оп­ре­де­ля­ем стол­бец title – это един­ст­вен­ные дан­ные, ко­то­рые мы бу­дем хранить в дан­ной таб­ли­це. Мы оп­ре­де­ля­ем их тип как varchar(100), то есть это стро­ка дли­ной до 100 сим­во­лов.

В сле­дую­щей стро­ке оп­ре­де­ления таб­ли­цы не за­да­ет­ся но­вого столб­ца: она про­сто со­об­ща­ет MySQL, ка­кой из на­ших столб­цов яв­ля­ет­ся пер­вич­ным клю­чом. На­конец, по­сле спи­ска столб­цов мы го­во­рим MySQL, ка­кой дви­жок ба­зы дан­ных ис­поль­зо­вать. Это неваж­но, по­ка мы не го­во­рим о ссы­лоч­ной це­ло­ст­но­сти дан­ных. А го­во­рить об этом мы не мо­жем, по­ка у нас нет хо­тя бы двух таб­лиц. Вот на­ша таб­ли­ца series.

create table series (

id integer not null auto_increment,

number integer not null,

name varchar(100),

tv_show integer not null,

primary key (id),

foreign key (tv_show) references tv_show(id)

) engine=InnoDB;

Она на­чи­на­ет­ся по­хо­же на таб­ли­цу tv_show. У нас есть пер­вич­ный ключ, под на­званием id. За­тем идут два столб­ца дан­ных, number и name. Мы оп­ре­де­ли­ли number как not null, так как у лю­бого се­зо­на есть но­мер, но name мо­жет быть пустым (да и бу­дет по умол­чанию пустым, ес­ли мы ниче­го не ука­жем), так как у от­дель­ных се­зо­нов обыч­но нет соб­ст­вен­ных на­званий. Сле­дую­щий стол­бец – tv_show, и это це­лое чис­ло, ко­то­рое не мо­жет быть пустым. Этот стол­бец свя­зы­ва­ет се­зон с со­от­вет­ст­вую­щим те­ле­шоу.

Крат­кий курс ис­то­рии РБД

Тер­мин «ре­ля­ци­он­ная ба­за дан­ных» вы­шел из-под пе­ра Тэ­да Код­да [Ted Codd] в од­ном из до­ку­мен­тов, ко­то­рые он на­пи­сал, ра­бо­тая в IBM в 1970 го­ду. Этот до­ку­мент «A Relational Model of Data for Large Shared Data Banks [Ре­ля­ци­он­ная мо­дель дан­ных для боль­ших раз­де­ляе­мых бан­ков дан­ных]» все еще досту­пен на http://mgnm.at/codd. Тем, кто не зна­ком с тео­ри­ей мно­жеств и ре­ля­ци­он­ной ал­геб­рой, чи­тать его бу­дет нелег­ко, но ин­те­рес­но, ес­ли вы вникае­те в ис­то­рию баз дан­ных и го­то­вы при­ло­жить уси­лия.

За сле­дую­щее де­ся­ти­ле­тие Кодд и его ко­ман­да усо­вер­шен­ст­во­ва­ли свои идеи. В на­ча­ле вось­ми­де­ся­тых они опуб­ли­ко­ва­ли спи­сок из 12 пра­вил, ко­то­рым долж­на сле­до­вать ис­тин­но ре­ля­ци­он­ная ба­за дан­ных (его мож­но про­честь на http://mgnm.at/12rules). Ин­те­рес­но от­ме­тить, сколь немно­гим из этих пра­вил сле­ду­ют со­вре­мен­ные ре­ля­ци­он­ные БД. До Код­да ба­зы дан­ных уже су­ще­ст­во­ва­ли, но по боль­шей час­ти бы­ли осно­ва­ны на про­прие­тар­ных тех­но­ло­ги­ях, и с ними бы­ло труд­но ра­бо­тать.

И эти из­менения про­изош­ли не внезап­но. В се­ре­дине вось­ми­де­ся­тых, на мо­ем ака­де­ми­че­­ском учеб­ном кур­се, SQL изу­чал­ся как но­вая тех­но­ло­гия. Я да­же пом­ню, что он на­зы­вал­ся язы­ком за­про­сов, под­хо­дя­щим для конеч­ных поль­зо­ва­те­лей. Эта меч­та про­дли­лась недол­го...

Ес­ли вам ин­те­рес­на бо­лее тео­ре­ти­че­­ская сто­ро­на ре­ля­ци­он­ных баз дан­ных, со­ве­тую про­честь книги Кри­са Дей­та [Chris Date], в ча­ст­но­сти, «SQL and Database Theory [SQL и тео­рия баз дан­ных]».

Скорая помощь

SQL – со­кра­ще­ние от «Structure Query Language» (язык струк­ту­ри­ро­ван­ных за­про­сов). Из­на­чаль­но он был сре­ди мно­гих экс­пе­ри­мен­таль­ных язы­ков для ра­бо­ты с ба­за­ми дан­ных, но вы­жил в ито­ге один.

Из на­шей схе­мы «сущ­ность–связь» мы зна­ем, что те­ле­шоу со­сто­ит из несколь­ких се­зо­нов. В на­шей мо­де­ли за это от­ве­ча­ет стол­бец tv_show таб­ли­цы series, со­дер­жащий id со­от­вет­ст­вую­ще­го те­ле­шоу. Так, ес­ли в таб­ли­це tv_show у нас есть стро­ка для се­риа­ла «Док­тор Кто» и ее иден­ти­фи­ка­тор ра­вен 10, то в ка­ж­дой стро­ке в таб­ли­це се­зо­нов для «Док­то­ра Кто» в столб­це tv_show бу­дет сто­ять 10. Стол­бец, ссы­лаю­щий­ся на ро­ди­тель­скую таб­ли­цу, на­зы­ва­ет­ся внешним клю­чом [foreign key], так как он со­дер­жит зна­чения из столб­ца пер­вич­но­го клю­ча, но они не яв­ля­ют­ся пер­вич­ны­ми клю­ча­ми этой таб­ли­цы. Для за­дания внешних клю­чей мы до­бав­ля­ем clauses к оп­ре­де­лению таб­ли­цы. В этой таб­ли­це мы го­во­рим, что стол­бец tv_show – внешний ключ; он со­дер­жит зна­чения столб­ца id таб­ли­цы tv_show. В таб­ли­це до­пуска­ет­ся все­го один пер­вич­ный ключ, но внешних вполне мо­жет быть несколь­ко. Поз­же мы рас­смот­рим та­кой при­мер.

Скорая помощь

MySQL – са­мая по­пу­ляр­ная от­кры­тая ре­ля­ци­он­ная СУБД. Но боль­шая часть дан­но­го учеб­ни­ка впол­не по­дой­дет и к SQLite и PostgreSQL.

У внешних клю­чей есть два важ­ных свой­ст­ва. Во-пер­вых, по­скольку се­зон при­над­ле­жит те­ле­шоу, в ка­ж­дой стро­ке таб­ли­цы series обя­за­тель­но долж­но быть зна­чение в столб­це tv_show. Для это­го мы при­ну­ди­тель­но объ­яв­ля­ем стол­бец как непустой. А во-вто­рых, ка­ж­дое зна­чение в столб­це tv_show долж­но быть дей­ст­ви­тель­ным, су­ще­ст­вую­щим в таб­ли­це tv_show. Именно это я имел в ви­ду ранее, когда упоминал о ссы­лоч­ной це­ло­ст­но­сти дан­ных. Ес­ли по­про­бо­вать вста­вить в таб­ли­цу series стро­ку со зна­чением по­ля tv_show, ко­то­ро­го нет в таб­ли­це tv_show, встав­ка долж­на за­вер­шить­ся неудач­но. Точ­но так же, при по­пыт­ке уда­лить стро­ку в таб­ли­це tv_show уда­ление долж­но за­вер­шиться неудач­но, иначе в таб­ли­це series оста­нут­ся стро­ки-си­ро­ты.

В ранних вер­си­ях MySQL не бы­ло под­держ­ки внешних клю­чей – их нель­зя бы­ло да­же за­дать. Поз­же такая воз­мож­ность поя­ви­лась, но MySQL внешние клю­чи не кон­тро­ли­ро­вал, то есть мож­но бы­ло иметь внешние клю­чи, ука­зы­ваю­щие на несу­ще­ст­вую­щие за­пи­си. С вер­сии 4 в MySQL ис­поль­зу­ют­ся же­ст­кие внешние клю­чи, но толь­ко в движ­ке InnoDB. В движ­ке по умол­чанию MyISAM же­ст­кие внешние клю­чи все-та­ки не ис­польз­уют­ся, и по­это­му мы ука­зы­ва­ем дви­жок InnoDB в оп­ре­де­лениях таб­лиц.

(thumbnail)
Щелк­нув на на­зва­нии се­рии, вы по­лу­чи­те под­роб­ную ин­фор­ма­цию о ней и спи­сок по­яв­ляю­щих­ся в ней пер­со­на­жей.

Вот оп­ре­де­ление на­шей треть­ей таб­ли­цы, episode.

create table episode (

id integer not null auto_increment,

number integer not null,

title varchar(100) not null,

first_broadcast datetime,

series integer not null,

primary key (id),

foreign key (series) references series(id)

) engine=InnoDB;

Здесь нам незна­ком толь­ко но­вый тип дан­ных. Мы оп­ре­де­лили стол­бец first_broadcast как datetime. В MySQL предусмотрено мно­же­ст­во пре­до­пре­де­лен­ных ти­пов дан­ных, и всегда имеет смысл воспользоваться тем, ко­то­рый боль­ше все­го под­хо­дит для хранимых дан­ных.

Это пер­вые три таб­ли­цы; те­перь до­ба­вим в них немно­го дан­ных.

insert into tv_show (title) values (‘Док­тор Кто’);

insert into tv_show (title) values (‘Шер­лок’);

insert into series (number, tv_show) values (1, 1);

insert into series (number, tv_show) values (1, 2);

insert into series (number, tv_show) values (2, 1);

insert into series (number, tv_show) values (2, 2);

insert into episode (number, title, first_broadcast, series)

values (1, ‘Ро­за’, ‘2005-03-26’, 1);

insert into episode (number, title, first_broadcast, series)

values (2, ‘Ко­нец све­та’, ‘2005-04-02’, 1);

insert into episode (number, title, first_broadcast, series)

values (1, ‘Этюд в ро­зо­вых то­нах’, ‘2010-07-25’, 2);

insert into episode (number, title, first_broadcast, series)

values (2, ‘Сле­пой бан­кир’, ‘2010-08-01’, 2);

insert into episode (number, title, first_broadcast, series)

values (1, ‘Но­вая зем­ля’, ‘2006-04-15’, 3);

insert into episode (number, title, first_broadcast, series)

values (2, ‘Зуб и ко­готь’, ‘2006-04-22’, 3);

insert into episode (number, title, first_broadcast, series)

values (1, ‘Скан­дал в Бель­гра­вии’, ‘2012-01-01’, 4);

insert into episode (number, title, first_broadcast, series)

values (2, ‘Со­ба­ка Бас­кер­ви­лей’, ‘2012-01-08’, 4);

Те­перь ин­фор­ма­цию из на­шей ба­зы дан­ных мож­но до­быть про­стым SQL-за­про­сом

select title from tv_show;

Еще ин­те­рес­нее ста­нет, ес­ли объ­е­ди­нить таб­ли­цы вме­сте.

select tv_show.title, series.number as series,

episode.number as episode, episode.title,

date_format(episode.first_broadcast, ‘%a %e %b %Y’)

from tv_show

join series on series.tv_show = tv_show.id

join episode on episode.series = series.id;

С по­мо­щью клю­че­во­го сло­ва join в SQL мож­но вы­брать дан­ные из несколь­ких таб­лиц одним за­про­сом. Для это­го в за­про­се на­до ука­зать объ­е­ди­няе­мые таб­ли­цы и от­но­шения пер­вич­но­го и вто­рич­но­го клю­чей. В пер­вом при­ме­ре в за­про­се вы­ше мы свя­зы­ва­ем таб­ли­цу tv_show с таб­ли­цей series, и внешний ключ в таб­ли­це series (series.tv_show) со­от­вет­ст­ву­ет пер­вич­но­му клю­чу в таб­ли­це tv_series (tv_series.id).

Этот за­прос так­же де­мон­ст­ри­ру­ет воз­мож­но­сти ти­па дан­ных datetime. В MySQL есть несколь­ко функ­ций для об­ра­бот­ки да­ты и вре­мени. В этом при­ме­ре вы восполь­зо­ва­лись про­стей­шей из них. Функ­ция date_format бе­рет зна­чение да­ты и вре­мени и пе­ре­фор­ма­ти­ру­ет его. Пер­вый ар­гу­мент – зна­чение да­ты и вре­мени, а вто­рой – стро­ка фор­ма­та, в ко­то­ром мы хо­тим их ото­бра­зить. Со­че­тания %X в стро­ке фор­ма­та пред­став­ля­ют раз­лич­ные час­ти да­ты и вре­мени, ко­то­рые нуж­но ото­бра­зить. Спи­сок этих со­че­таний при­ве­ден в до­ку­мен­та­ции по MySQL (о том, где ее най­ти, см. ин­фор­ма­цию во врез­ке).

Сей­час мы так­же мо­жем про­де­мон­ст­ри­ро­вать мощь ссы­лоч­ной це­ло­ст­но­сти. По­пы­тав­шись уда­лить стро­ку из таб­ли­цы tv_show, вы по­лу­чи­те со­об­щение об ошиб­ке, и стро­ка не бу­дет уда­ле­на. При­чи­на в том, что для ка­ж­дой стро­ки в таб­ли­це tv_show есть стро­ки в таб­ли­це series, ко­то­рые на нее ссы­ла­ют­ся. Вы не мо­же­те уда­лить стро­ку в tv_show, по­ка не уда­ли­те все со­от­вет­ст­вую­щие ей се­зо­ны. Ана­ло­гич­но, нель­зя уда­лить стро­ку се­зо­на, по­ка есть со­от­вет­ст­вую­щие ей се­рии.

До­бав­ля­ем пер­со­на­жей

Все это ми­ло, но от­но­шения ме­ж­ду на­ши­ми таб­ли­ца­ми до сих пор бы­ли очень про­сты­ми: это бы­ли от­но­шения «один ко мно­гим». Увы, в ми­ре не всегда все так глад­ко. Да­вай­те до­ба­вим в на­шу ба­зу дан­ных ин­фор­ма­цию о пер­со­на­жах и их по­яв­лении. Ка­кие таб­ли­цы нуж­но соз­дать? И ка­кие внешние клю­чи вы­брать?

Оче­вид­но, нуж­на таб­ли­ца с пер­со­на­жа­ми [character]. Но character [тж. сим­вол] – так­же за­ре­зер­ви­ро­ван­ное клю­че­вое сло­во в SQL, по­это­му да­вай­те на­зо­вем на­шу таб­ли­цу person [лич­ность]. Эта таб­ли­ца вы­гля­дит так:

create table person (

id integer not null auto_increment,

name varchar(100) not null,

primary key (id)

) engine=InnoDB;

(thumbnail)
Оче­ред­ная схе­ма «сущ­ность–связь», по­ка­зы­ваю­щая, как до­пол­ни­тель­ная таб­ли­ца (По­яв­ле­ние) пре­вра­ща­ет от­но­ше­ние «мно­гие ко мно­гим» в два от­но­ше­ния «один ко мно­гим».
Скорая помощь

Две хо­ро­шие кни­ги об SQL – Learning SQL [Изу­ча­ем SQL] Ала­на Бо­лье [Alan Beaulieu] и SQL For Smarties [SQL для ум­ни­ков] Джо Сел­ко [Joe Celko].

В ней за­да­ет­ся толь­ко имя пер­со­на­жа. Мы не со­единили таб­ли­цу ни с од­ной из дру­гих, по­то­му что не мо­жем. Долж­на быть связь ме­ж­ду за­пи­сью о пер­со­на­же и за­пи­сью о се­рии, оз­на­чаю­щая, что пер­со­наж по­яв­ля­ет­ся в се­рии. Но внешний ключ се­рии в таб­ли­цу пер­со­на­жей по­мес­тить нель­зя, так как пер­со­наж мо­жет по­яв­лять­ся в несколь­ких се­ри­ях.

И нель­зя по­мес­тить внешний ключ пер­со­на­жа в таб­ли­цу с се­рия­ми, по­то­му что в од­ной се­рии по­яв­ля­ет­ся несколь­ко пер­со­на­жей. Это от­но­шение «мно­гие ко мно­гим». Смо­де­ли­ро­вать его сложнее, чем от­но­шения «один ко мно­гим», ко­то­рые встре­ча­лись нам до на­стоя­ще­го мо­мен­та.

Но сде­лать это мож­но. И ключ к ре­шению за­да­чи при­ве­ден несколь­ко аб­за­цев на­зад. Я ска­зал, что нам нуж­на ин­фор­ма­ция о пер­со­на­жах и об их по­яв­лении. Ре­шение – соз­дать от­дель­ную таб­ли­цу appearance [по­яв­ления пер­со­на­жей]. Она вы­гля­дит так:

create table appearance (

id integer not null auto_increment,

person integer not null,

episode integer not null,

primary key (id),

foreign key (person) references person(id),

foreign key (episode) references episode(id)

) engine=InnoDB;

Клю­че­вая фи­гу­ра

Вся эта таб­ли­ца со­сто­ит из клю­чей. Это пер­вич­ный ключ и два внешних клю­ча – к таб­ли­це с пер­со­на­жа­ми и к таб­ли­це с се­рия­ми. Ка­ж­дый раз, когда мы зна­ем, что некий пер­со­наж поя­вил­ся в ка­кой-то се­рии, мы мо­жем до­ба­вить стро­ку в эту таб­ли­цу.

И ес­ли у нас есть episode id [иден­ти­фи­ка­тор се­рии], мы смо­жем по­лу­чить спи­сок всех пер­со­на­жей, по­яв­ляю­щей­ся в этой се­рии; и на­обо­рот – по person id [иден­ти­фи­ка­то­ру пер­со­на­жа] мож­но по­лу­чить спи­сок всех се­рий, где он по­яв­ля­ет­ся. Вот немно­го дан­ных для на­ших но­вых таб­лиц:

insert into person (name) values (‘Де­вя­тый Док­тор’);

insert into person (name) values (‘Де­ся­тый Док­тор’);

insert into person (name) values (‘Роз Тай­лер’);

insert into appearance (person, episode) values (1, 1);

insert into appearance (person, episode) values (3, 1);

insert into appearance (person, episode) values (1, 2);

insert into appearance (person, episode) values (3, 2);

insert into appearance (person, episode) values (2, 5);

insert into appearance (person, episode) values (3, 5);

insert into appearance (person, episode) values (2, 6);

insert into appearance (person, episode) values (3, 6);

И вот вам не­сколь­ко за­про­сов, де­мон­ст­ри­рую­щих, как таб­ли­ца appearance ра­бо­та­ет в обо­их на­прав­ле­ни­ях.

select episode.title

from episode

join appearance on episode.id = appearance.episode

join person on person.id = appearance.person

where person.name = ‘Де­вя­тый Док­тор’;

select person.name

from episode

join appearance on episode.id = appearance.episode

join person on person.id = appearance.person

where episode.title = ‘Но­вая зем­ля’;

Об­нов­ленн­ная схе­ма ба­зы дан­ных при­ве­де­на сле­ва. На ней яс­но вид­но, что таб­ли­ца appearance яв­ля­ет­ся свя­зую­щим зве­ном ме­ж­ду таб­ли­ца­ми person и episode.

Это все, о чем мы мо­жем уз­нать в нашем про­стом ру­ко­вод­стве по про­ек­ти­ро­ванию баз дан­ных. Я на­пи­сал про­стое PHP-при­ло­жение, де­мон­ст­ри­рую­щее ра­бо­ту с этой ба­зой дан­ных. Его код мож­но най­ти на DVD, а несколь­ко сним­ков эк­ра­на по­ка­за­ны по соседству. |

Персональные инструменты
купить
подписаться
Яндекс.Метрика