Креирање база на податоци

Членови на тим:

• Aлександар Тодороски 231153
• Антонио Трајковски 231156
• Вељко Аџиќ 231267

Спорт

Тука може да се види како е креирана табелата и како е потпунета. Користено е готово множество од податоци при полнење на податоци. Вкупен број на записи е 40.

Спортска категорија

Во сликата може да се види како е креирана табелата SPORT_CATEGORY. Има поставено ограничување на gender да е од вредност 'M' или 'F', и надворешен клуч sport_id кој референцира id од sport. За полнењето користено е готово податочно множество кое е адаптирано во SQL. Вкупен број на записи но табелата изнесува 337.

Држава

Дефиниција на ентитетот COUNTRY е прикажана подолу. Табелата е пополнета со податоци кои се добиени од готово множество и адаптирано во SQL. Вкупно податоци генерирани се 165.

Спонсор

Табелата SPONSOR е дефинирана на следен начин. За пополнување на податоци е искористено готово множество кое е адаптирано во SQL. Вкупно редови генерирани е 10.001.

Тип на натпревар

Дефиниција на COMPETITION_TYPE е прикажана подолу. Податоците во ова табела се внесени од готово множество кое е адаптирано во SQL. Вкупен број податоци се 17.

Федерација

Ентитетот FEDERATION има 2 други ентитети кои наследуваат од него, INTERNATIONAL_FEDERATION и NATIONAL_FEDERATION. Основната табела ограничување за уникатно име и надворешен клуч кон спорт. Табелата за интернационална федерација има примарен клуч што е исто така надворешен клуч кој референцира кон федерација и каскадно се брише. Националната федерација, како и интернационална, има примарен клуч што е исто така надворешен клуч кон федерација, но има дополнително надворешен клуч кој референцира кон држава и има надворешен клуч кон интернационална федерација во која членува.

Податоците за национална федерација се генерираат на тој начин што за секоја комбинација од спорт и држава се генерира запис во FEDERATION, потоа генерира запис во NATIONAL_FEDERATION користејќи ги генерираните податоци од FEDERATION.

На сличен начин се генерираат и за INTERNATIONAL_FEDERATION, каде за секој спорт се генерира федерација, потоа користејќи ги тие записи од федерација се полни и табелата за интернационална федерација.

Ова ни генерира 6.640 федерации, од кои 6.600 се национални и 40 се интернационални.

Спортски клуб

Табелата SPORT_CLUB е дефинирана со ограничување да има уникатен пар име и држава, и е дефиниран надворешен клуч country_id кој референцира кон табелата за држава. Податоците за ова табела се генерираат со две кверија. Првото квери генерира национални репрезентации за секоја комбинација на држава и спортска категорија.

Второто квери генерира податоци со помош на привремена табела temp_clubs_names (готово множество од имиња на клубови) кади ги спојува случајно со една држава.

Вкупно се генерирани 42.477 редици.

Спортски тим

Ова табела е дефинирана со два надворешни клуча каде еден референцира кон спортска категорија и другиот кон спортски клуб. Податоците се генерираат со две кверија, едно генерира тимови за национални репрезентации, а другото за обични клубови тимоив.

За првото квери се користи секоја комбинација на национална репрезентација клуб и спортска категорија каде се генерира тим.

За второто квери се користи секој клуб што не е национална репрезентација и се зема 5 случајни спортски категории.

Ова ни генерира 478.725 редици.

Клуб-федерација

Табелата CLUB_FEDERATION претставува членство на клуб во некоја федерација. Табелата е дефинирана со ограничување кое проверува валидноста на почетен и краен датум, и надворешни клучеви кои референцираат кон федерација и кон клуб соодветно.

Се генерираат членства во првото квери така што за секоја комбинација на клуб што е национална репрезентација и национална федерација која е во иста држава како клубот се поставува случаен почетен датум пред 40 до 50 години.

Во второто квери за секој тим од секој клуб се земаат сите спортови кои играат тие тимови и од нив се спојува секоја национална федерација. Со овие комбинации се генерира членство кое почнало пред 30 до 35 години (случаен избор).

Ова табела има 951.120 редици.

Регион

Табелата REGION е дефинирана со примарен клуч id, име, и флег дали е дел од држава. Генерирана е од статичко множество на податоци и динамички генерирано од секоја држава поделена на северен, источен, западен и јужен регион. Ова табела има вкупно 1.345 редици.

Држава-регион

Ова табела претставува врска помеѓу држава и регион, имајќи само 2 надворешни клуча кои референцираат кон соодветната табела, и исто така нивната комбинација е примарен клуч.

Редиците се генерираат динамички со спојување на северен, јужен, источен и западен регион на секоја држава со таа држава. Исто се внесуваат податоци од готово множество.

Вкупен број на податоци но ова табела изнесува 1.447.

Локација

Табелата LOCATION е дефинирана со ограничување за капаците да е позитивен, ограничување името да е уникатно за држава, и со надворешен клуч кој референцира кон табела за држава.

Податоците се генерираат со случајна комбинација на неколку множества од имиња и случајни вредност, каде за секоја држава се генерира случајно помеѓу 300 и 1.060 локации. Ова генерира 108.697 редици.

Спонсортство

Ентитетот SPONSORSHIP претставува спонзорство помеѓу спонзор и тим и е дефиниран со ограничување amount да е позитивна, ограничување кое проверува валидност на почетен и краен датум, и два надворешни клуча кои соодветно референцираат кон спортски тим и спонзор.

Вредности за ова табела се генерираат на тој начин што за секој тим се спојува со 7 случајни спонзори и се доделуваат случајни вредности за почетен и краен датум и за исплатата. Ова генерира 3.351.076 вредности.

Национална лига

Ова табела е дефинирана со ограничување за проверка на валидност на date_started и date_disbanded, и надворешни клучеви кои референцираат кон спортска категорија, национална федерација и регион.

Податоците динамички се генерирани на тој начин што за секоја спортска категорија се спојуваат сите национални федерации и се генерира запис каде се избира случаен датум на започнување пред 5 до 6 години.

За оваа табела се генерирани 55.605 податоци.

Сезона

Табелата за SEASON е дефинирана со ограничување кое го проверува валидноста на почетниот и крајниот датум, и со надворешен клуч кој референцира кон национална лига.

За секоја национална лига се генерираат по 4 сезони, секоја една година по друга во последните 4 години со случајна варијација на почеток на сезоната за 0 до 30 дена.

Ова ни генерира 278.026 редици.

Натпревар

Оваа табела е дефинирана со ограничување кое го проверува почетниот и крајниот датум. Дополнително има надворешни клучеви кои референцираат кон тип на натпревар, федерација која го организира натпреварот и кон сезона (доколку е дел од некоја сезона).

Генерирање на податоци за оваа табела се врши така што за секоја сезона се спојува национална лига на таа сезона и се спојува тип на натпревар според име на лигата.

Ова ни генерира 278.026 редици, исто како и сезона.

Сезона-тим

Табелата SEASON_SPORT_TEAM претставува врска помеѓу сезона и спортски тим. Дефинирана е со комплексен примарен клуч кој е создаден од надворешни клучеви што референцираат кон сезона и спортски тим соодветно.

Податоците се генерираат со спојување секоја сезона со 20 случајни тимови од лигата на која припаѓа сезоната. Со оваа квери се генерираат 2.103.219 редици.

Дуел

Оваа табела е дефинирана се ограничување кое проверува дали домаќинот и противникот се различни, и надворешни клучеви кои референцираат кон натпревар (ако е NULL тогаш се подразбира дека е пријателски дуел), клучевите home_team_id и away_team_id кои референцираат кон спортски тим, надворешен клуч кој референцира кон локација, и клуч кој референцира кон спортска категорија. Вредностите на home_team_score и away_team_score се NULL доколку дуелот е закажан за во иднина.

Со кверито се генерираат податоци за дуели помеѓу национални репрезентации од иста спортска категорија. За секој дуел случајно се избира локација, се генерира случаен датум во минатото или иднината за најмногу 5 години. За веќе одиграните натпревари се доделуваат случајни резултати според правилата дефинирани во score_logic за соодветниот спорт. На овој начин се добиваат реалистични податоци со различни датуми, локации и резултати за повеќе спортови.

Дополнително го имаме и ова квери за генерирање на дуели на нерепрезентациски натпревари. Работи на сличен принцип на претходното, но ова квери извршува дополнителни проверки.

Двете кверија генерираат вкупно 16.069.000 дуели.

Човек

Табелата PERSON е дефинирана со ограничување датумот на раѓање да е подоцна од 01.01.1900 и полот да има вредност M или F. Дополнително има дефинирано надворешен клуч кој референцира кон табелата за држава.

Генерирање на редици за оваа табела се врши во две кверија, едното генерира машки, другото женски. Генерирањето користи привремени табели temp_male_names, temp_female_names и temp_surnames кои се генерирани од готово множество од имиња и презимиња. Во двете си избира случаен датум на раѓање, случајно име и презиме и случајна држава на раѓање, потоа се генерира ЕМБГ според генерираниот датум на раѓање.

Од ова се генерирани 13.206.598 редици.

Судија

Табелата REFEREE наследува од PERSON па затоа примарниот клуч е исто така надворешен клуч кој референцира кон човек и е поставено каскадно да се брише. Исто така има и надворешни клучеви кои референцираат кон федерација и спортска категорија соодветно.

Пред да се генерираат судии, се креира привремена табела tmp_people која ги содржи сите луѓе подредени на случаен начин. Со помош на оваа табела за секоја федерација се спојуваат 100 редици од привремената табела. Со ова се генерираат 5.594.200 судии.

Тренер

Табелата COACH, исто како REFEREE, наследува од PERSON и затоа примарниот клуч е надворешниот клуч кој референцира кон човек е каскадно се брише. Исто има и надворешни клучеви кои референцираат кон федерација и спортска категорија.

За генерирање податоци за оваа табела прво се креира нова привремена табела tmp_people_no_referee која ги содржи останатите вредности кои не се искористени во генерирање на судии. На сличен принцип како судии, се генерираат вредностите и за тренер така за секој тим случајно се спојува федерација и се одбираат две случајни луѓе. За оваа табела се генерирани 552.125 редици.

Спортист

SPORTSPERSON е дефинирана со надворешен клуч кој референцира човек и е поставено каскадно да се брише (бидејќи наследува од таа табела), и клуч кој референцира спортска категорија.

Пред генерирање на податоци се креира привремена табела tmp_sportspersons_pool која ги содржи сите останатите луѓе што не биле доделени како судија или тренер. Според тимовите се доделуваат случајно спортски категории на спортистот. Вака се генерираат 2.180.588 спортисти.

Тренирање тим

Табелата COACHING_TEAM претставува врска помеѓу COACH и SPORT_TEAM. Дефинирана е ограничување за проверка на валидност на почетен и краен датум, како и надворешни клучеви кои референцираат кон спортски тим и тренер соодветно. Дополнително, има дефинирано комплексен примарен клуч создаден од team_id и coack_ssn.

Полнење на ова табела се врши така што за секој се доделува до два тренери и се доделува случаен почетен датум пред 6 до 7 години во минатото. Ова генерира 478.725 редици.

Договор

SPORTSPERSON_CONTRACT е дефиниран со ограничувања кои проверуваат валидноста на почетниот и крајниот датум и проверуваат payout да е позитивен број. Дополнително дефинирани се надворешни клучеви кои референцираат спортист и спортски клуб соодветно.

Податоците за оваа табела се генерираат со три кверија. Првото генерира договори во последните 5 до 6 години за секој тим на секој клуб што не е национална репрезентација.

Второто квери на исти начин генерира договори за секој тим на секој клуб што е национална репрезентација.

Последното квери генерира историски податоци, односно генерира договори кои се истечени. Го генерира на сличен начин како првото, за секој тим на секој клуб што не е репрезентација, но почетниот датум е пред 13 до 14 години, а крајниот датум е до пред 11 до 12 години.

Овие кверија генерираат 4.107.739 договори.

Екипа

Табелата TEAM_ROSTER претставува врска помеѓу играч, неговиот тим и учество во дуел, и затоа е дефинирана со комплексен клуч кој ги содржи player_ssn, team_id и duel_id. Дополнително е дефинирано ограничување кое проверува валидноста на почетно и крајно време, дефинирани се и надворешни клучеви кои референцираат спортист, спортски тим и дуел.

Пред да се генерираат податоците, прво се креираат привремени табели tmp_contract_pool, tmp_duel_pool кои служат како множество од кое да се генерираат податоците. Дополнително, има и табела team_roster_progress која служи за следење информации колку се генерирани податоците. Во случај да прекине процедурата може пак да продолжи од последната залувана точка.

Бидејќи многу време му требаше на ова квери да се изврши и често паѓаше беше создадена привремена процедура insert_team_roster_batched_fixed. Таа процедура обработува во серии од по 10.000 дуели, при што за домашниот и гостинскиот тим се избираат играчи со активни договори кои одговараат на спортската категорија и клубот на тимот. По секоја серија се извршува COMMIT и се ажурира контролна точка. Дополнително, за мал дел од играчите се генерира пократко време на настап со цел да се добијат пореалистични податоци. Oвa процедура генерираше над 80 милиони редици.

Судење на дуел

Табелата REFEREEING_DUEL ја претставува врската помеѓу REFEREE и DUEL, па затоа е дефинирана со комплексен примарен клуч кој ги содржи вредностите referee_ssn и duel_id. Дополнително се дефинирани и надворешни клучеви кои референцираат кој судија и кон дуел соодветно.

При генерирање првин се генерираат привремените табели tmp_duel_federation, каде за секој дуел се генерира соодветна федерација (ако дуелот е дел од натпревар се користи организациската федерација, ако не се користи по случаен избор), и tmp_ref_assignment која креира пар за секој судија случаен дуел.

Од tmp_ref_assignment се внесуваат првата фаза на податоци во табелата.

Се креира дополнителна привремена табела tmp_ref_assignment2 која е слична на првата, но додава поместување за да се распоредат судиите во разни дуели, односно да немаме кластерирање. Исто така се внесуваат сите податоци од tmp_ref_assignment2 во REFEREEING_DUEL.

Се вкупно се генерираат 32.543.880 редици.

Поени

Табелата SCORE има цел да води евиденција за кој играч во кој дуел кога добил гол. Таа е дефинирана со надворешни клучеви кои референцираат кон играч и кон дуел соодветно.

Пред да се генерираат податоците за SCORE се креираат привремени табели tmp_score_data, која ги зима сите завршени дуели и времетраењето на секој, и tmp_roster_data која ги зима сите валидни играчи.

Генерирање на податоци се прави во две INSERT кверија, така што во двете кверија за секој гол се избира случаен момент во текот на дуелот од сите активни играчи во тој момент се бира еден случаен играч и се запишува како стрелец на голот. Едното квери ги генерира за домаќин головите, другото за противникот.

Со ова се генерираат 30.000.000 редици.