АЛМАТЫ ЭНЕРГЕТИКА ЖӘНЕ БАЙЛАНЫС ИНСТИТУТЫ
ИНЖЕНЕРЛІК КИБЕРНЕТИКА КАФЕДРАСЫ
МӘЛІМЕТТЕР ҚОРЛАРЫН ЖОБАЛАУ
пәні бойынша
050702 - “Автоматтандыру және басқару” мамандығының барлық оқу түрінің студенттері үшін зертханалық жұмыстарды орындауға арналған әдістемелік нұсқаулар
Алматы 2009
ҚҰРАСТЫРУШЫЛАР: Ешпанова М.Д., Ибраева Л.К., Сябина Н.В. «Мәліметтер қорларын жобалау» пәні бойынша 5В0702 -«Автоматтандыру және басқару» мамандығының барлық оқу түрінің студенттері үшін зертханалық жұмыстарды орындауға арналған әдістемелік нұсқаулар.– Алматы: АЭжБИ, 2009. – 51 б.
Әдестемелік нұсқауда мәліметтер қорын жобалаудың сұрақтары және оны MS SQL Server мәліметтер қорларын басқару жүйесінде іске асыру сұрақтары: мәліметтер қорының объекттерін жасау, мәліметтермен әрекеттесу, ақпаратты іздеудің күрделі сұраныстарын құрастыру және де кәсіпқор пайдаланушының құралдарын – процедуралар, триггерлер, пайдаланушының қолданбалыларын өңдеу қарастырылады; сонымен бірге мәліметтер қорларын басқару жүйелер ортасында мәліметтерді шифрлеу сұрақтары.
Кіріспе
Ақпараттық жүйелерді жасаумен байланысты ең күрделі және жауапкершілік есептердің бірі мәліметтер қорларын жобалау болып табылады. Осы есепті шешу нәтижесінде мәліметтер қорларының мазмұны, оның болашақ пайдаланушыларына тиімді мәліметтерді үйымдастырудың әдісі және мәліметтерді басқарудың инструменталды құрылғылары анықталуы керек. Мәліметтер қорын жобалау процесі тақырып аймақтың ақпараттық құрылымының сөзбен бейнеленуінен тақырып аймақтың объекттерін кейбір модель терминдеріндегі формалданған бейнелеуге көшу болып табылады. Мәліметтер қорының құрылымын нақты жобалауда семантикалық модельдеу әдісі қолданылады. Бұл әдісте мәліметтердің құрылымы олардың мағнасы сүйемелденіп модельденеді. Семантикалық модельдеудің құрылғысы ретінде «нақтылық-байланыс» (Entity-Relationship - ER) диаграммаларының әртүрлі нұсқалары қолданылады. «Нақтылық-байланыс» диаграммаларының барлық нұсқалары тақырып аймақтың нақтылықтарының (объекттерінің), олардың қасиеттерінің және объекттер арасындағы өзара байланстарының графикалық бейнелерін қолданады. Мәліметтер қорларын жобалау нәтижесінде модельденетін тақырып аймақтың концептуалды сұлбасы (ER-диаграммасы) пайда болады.
Мәліметтер қорының жобасы өңделгеннен кейін оны белгілі мәліметтер қорларын басқару жүйесінде (МҚБЖ) іске асыруын бастауға болады. Қазіргі кездегі мәліметтерді басқару ақпараттық технологиялар реляциялық мәліметтер қорларын басқару жүйелерін қолдануда негізделген.
Реляциялық мәліметтер қорлары пайда боларының алдында өңделген мәліметтермен әрекеттесудің барлық тілдері файлдардың логикалық жазбалары түрде көрсетілетін мәліметтермен операцияларды жасауға бағытталған болатын. Бұл жағдай мәліметтерді сақтауының ұйымдасуын пайдаланушыға толығымен білуді талап етеді және де қандай мәліметтер керек екенін көрсетумен бірге олар қай жерде орналасқанын, оларды қалай алуға болатынын көрсетуге жеткілікті ынта керек. Әдістемелік нұсқауларда қарастырылып отырған процедуралық емес SQL (Structured Query Language – құрамдасқан сұраныстар тілі) тілі кестелердің логикалық өзара байланысқан жиындары ретінде көрсетілген мәліметтермен әрекеттесуге бағытталған. Бұл тілдің ерекшелігі олардың мәліметтерді өңдеудің поцедураларына бағытталғаны емес, мәліметтерді өңдеудің соңғы нәтижесіне бағытталғаны. Мәліметтер қай жерде орналасқанын, оларды алу үшін қандай индекстері мен операциялардың тиімділеу болатын тізбектерін орындау керек екенін SQL өзі анықтайды, мәліметтер қорына сұраныстың осындай детальдарын көрсетпеуге болады.
Мәліметтермен жұмыс жасау үшін мәліметтер қорын жобалап, жасаған жеткілікті емес. Мәліметтерге қол жеткізу, оларды өңдеу мүмкіншіліктері болуы қажет. Сондықтан пайдаланушыға ыңғайлы түрде мәліметтермен жұмыс жасауға негізделген арнайы бағдарламаны – “клиенттік қолданбалыны” жасау керек.
Жалпы кезде бір мәліметтер қорымен әртүрлі қолданбалының көптеген саны жұмыс жасауы мүмкін. Бір мәліметтер қорымен жұмыс жасайтын қолданбалыларды қарастырған кезде олар параллельді және бір-бірінен тәуелсіз жұмыс жасай алады деп есептелінеді. Дәл МҚБЖ тұтас жалғыз мәліметтер қорымен бірнеше қолданбалылардың жұмысын қамтамасыздандыруы керек: әрқайсысы дұрыс орындалуы, бірақ басқа қолданбалылардың мәліметтер қорына енгізілген өзгерістерді есепке алып отыруы керек.
Зертханалық жұмыстарға әдістемелік нұсқауларда мәліметтер қорын жобалаудың сұрақтары және оны таңдалынған мәліметтер қорларын басқару жүйесінде іске асыру сұрақтары: мәліметтер қорының объекттерін жасау, мәліметтермен әрекеттесу, ақпаратты іздеудің күрделі сұраныстарын құрастыру және де кәсіпқор пайдаланушының құралдарын – процедуралар, триггерлер, пайдаланушының қолданбалыларын өңдеу қарастырылады. Жобаны іске асыру үшін кәсіпорын деңгейінде мәліметтер қорларын жасап, сүйемелдеуге негізделген Microsoft SQL Server 2005 мәліметтер қорларын басқару жүйесі ұсынылады. SQL сұраныстар тілі әртүрлі мәліметтер қорларының мәліметтеріне қол жеткізу үшін стандартты құралдарын ұсынады. Мәліметтер қорының көбісінде осы тілдің өзінің версиясы бар. Бірақта SQL тілінің негізін түсіну әртүрлі платформалардағы көптеген мәліметтер қорларымен жұмыс істеуге көмек болады.
Ақпараттық қызметтер нарығында өнімдер әр 4-6 ай сайын жаңарады. Сондықтан зертханалық сабақтарда қазіргі кезде қолданатын Microsoft SQL Server программасының версиясы өзгеруі мүмкін. Зертханалық жұмыстарға тапсырмалар МҚБЖ-нің версиясынан бірсыпыра тәуелсіз болуы үшін, сонымен бірге әр оқу жылы әдістемелік нұсқауларды қайтадан баспадан шығармау үшін «Инженерлік кибернетика» кафедрасында қолданылатын SQL тілінің негізгі мүмкіншіліктерін үйрену үшін студенттерге көмек ретінде оқытуға программа өңделген. Бұл программаның көрнекі интерфейсі бар, ол видеоанимация құралдарымен қамтылған, көптеген мысалдармен сүйемелденген. Зертханалық жұмысқа дайындалған кезде студент осы программаның сәйкес бөлігін (әдістемелік нұсқалар бойынша) оқып алынған білімдерін зертханалық жұмысты орындауға пайдаланады.
Кестелермен жұмыс жасауға негізделген SQL тілінің күрделі қолданбалы программаларды жасаудың жеткілікті құралдары жоқ. Сондықтан көптеген МҚБЖ-де ол жоғарғы деңгейдегі программалау тілдермен бірге қолданылады. 7 зертханалық жұмыста пайдаланушының қолданбалысын өңдеудің сұрақтары қарастырылады. Өңдеудің нәтижесінің соңғы түрін көрсету үшін осы жұмыстың әдістемелік нұсқасында өңдеудің мысалы ретінде exe.файл ұсынылады.
Соңғы зертханалық жұмыста MS SQL Server ортасында шифрлеу сұрақтары қарастырылады. Бұл жұмыс «Ақпаратты қорғау» пәнінде қарастырылған алгоритмдерді қолдану нәтижелерін көрсетеді.
1 №1 зертханалық жұмыс. Мәліметтер қорын жасау және оның құрылымын анықтау
Жұмыстың мақсаты - MS SQL Server 2005 ортасында мәліметтер қорларын жасау, жою, резервті көшірмелеу және қалпына келтіру процедураларын меңгеру, мәліметтерді анықтау (мәліметтер қорының құрылымын жасау) тілін игеру.
1.1 MS SQL Server 2005 жүйесімен танысу
1.1.1 Мәліметтер қорының логикалық компоненттері
MS SQL Server реляциялық мәліметтер қорларын басқару жүйесі. Реляциялық мәліметтер қоры дегеніміз кестелер деп аталатын логикалық тұтас сегменттерге бөлінген мәліметтер қоры, осы кестелер мәліметтер қорының ішінде бір-бірімен кілттік өрістері арқылы байланысады. Мәліметтер қорында мәліметтерді сақтайтын негізгі түрі кестелер болып табылады. Реляциялық мәліметтер қоры мәліметтерді одан да майда және басқаруға ыңғайлы логикалық сегменттерге бөлуге мүмкіншілік береді. Сондықтан мәліметтерді оптималды көрсетуге және мәліметтерге қол жеткізудің бірнеше денгейлерін ұйымдастыруға болады. Кестелерде кілттер болғандықтан, бірнеше кестелердегі мәліметтерді жалғыз нәтижелік жиынтыққа ұйымдастырудың мүмкіндігі бар. Осы жағдай реляциялық мәліметтер қорларының негізгі артықшылығы болып табылады. Мәліметтер қорларына қолданылатын және тұтас болып есептелінетін әрекеттер жиыны транзакция деп аталады. Транзакция мәліметтер қорына кейбір өзгертулерді енгізу болып табылады. Әрбір мәліметтер қорының оған сәйкес транзакциялар журналы болады, мәліметтер қорына жазу өзгертулерді алдында SQL Server журналға барлық орындалған транзакцияларды жазып қояды.
SQL Server-де мәліметтер бірнеше әртүрлі объекттерге ұйымдастырылған, оларды пайдаланушы мәліметтер қорын жасағанда көреді. Олар келесідей:
- мәліметтер қорының пайдаланушылары (Database Users);
- мәліметтер қорының рольдері (Database Roles);
- кестелер (Tables);
- елестетулер (SQL Server Views);
- сақталанған процедуралар (Stored Procedures);
- ережелер (Rules);
- келісім бойынша мәндер (Defaults);
- пайдаланушымен анықталған мәліметтер типтері (User Defined Datatypes);
- мәліметтер қорының диаграммалары (Database Diagrams).
Осы көрсетілетін объекттерден басқа әр мәліметтер қорында тағы да келесідей объекттер бар:
- мәндерге қойылатын шарттар (Constraints);
- индекстер (Indexes);
- кілттер (Keys);
- триггерлер (Triggers).
1.1.2 SQL бұйрықтарының типтері
SQL-де әртүрлі функцияларды орындайтын бұйрықтардың негізгі категориялар:
• DDL (Data Definition Language — мәліметтерді анықтау тілі;
• DML (Data Manipulation Language — мәліметтерді манипуляциялау тілі);
• DQL (Data Query Language — мәліметтерге сұраныстарды орнату тілі);
• DCL (Data Control Language — мәліметтерді басқару тілі);
• мәліметтерді әкімдеу бұйрықтары;
• транзакцияларды басқару бұйрықтары.
Осындай функциялары арасында – мәліметтер қорының объектілерін құрастыру, объектілерді басқару, мәліметтер қорының кестелерін жаңа мәліметтермен толтыру, кестелерде бар мәліметтерді жаңарту, сұраныстарды орындау, мәліметтер қорына пайдаланушылардың қол жеткізуін басқару және мәліметтер қорымен жалпы әкімдеуді орындау.
1.1.3 Мәліметтердің негізгі типтері
Мәліметтердің типтері мәліметтер қорларында әртүрлі мәліметтерді сақтауға мүмкіндік береді: кезкелген символдардан ондық сандар, даталар мен уақыт мәндеріне дейін. Барлық тілдерде үшінші ұрпақтағы С тілдерінде айнымалылармен жұмыс істегенде, SQL көмегімен реляциялық мәліметтер қорларымен жұмыс жасағанда мәліметтерді типтерге бөлу амалдары бірдей болады. SQL-дің әртүрлі іске асыруында стандартты мәліметтер типтері үшін әртүрлі атаулар қолданылғанымен, олардың барлығы практикада бірдей жұмыс істейді. Қысқа мерзімге жоспарлағанда перспективалар көзқарасы жағынан да, мәліметтер типтерін, олардың ұзындығын, масштабын, дәлдігін ұқыпты таңдау керек. Сонымен бірге, сәйкес бизнестің де ережелерін және пайдаланушыға мәліметтер қандай түрде көрсетілетінін есепке алып отыру қажет. Ол үшін, әрине, мәліметтердің міндеті мен олардың мәліметтер қорының ішінде қалай байланысқанын түсіну керек. Мәліметтер типтері мәліметтердің өзінің сипаттамалары болып табылады, олардың атрибуттары тікелей кестелердің сәйкес өрістерінде орнатылады. Мысалы, кейбір өріс тек қана сандық мәндерден тұрады деп орнатылған болса, онда оған әріп-сандық мәндерді енгізуге мүмкін болмайды.
Басқа тілдерінің көпшілігінде, SQL тілінде де ең жалпы типтері келесідей болады: символдық жолдар, сандық жолдар, дата мен уақыт мәндері.
Мәліметтердің символдық (character) типтері әріптік, сандық және арнайы (мысалы, ? немесе >) символдарды сақтауға мүмкіндік береді. Сақтау өрісіне (кесте тік жолы сияқты) енгізген кезде символдық мәліметтер тырнақша немесе апостроф ортасында орнатылады.
Типі char(n) болатын мәліметтерде әр символы үшін бір байт қолданылады. n саны анықталған тік жолдағы символдардың максималды санын анықтайды. Егер де n-нен кіші шама енгізілсе, SQL Server жалпы ұзындың n-ге тең болатындай соңғы символдан кейін бос орындарды (пробел) қосады.
Дискілік кеңістікті тиімді қолдану үшін тік жолда сақталынатын мәндер ұзындықтары әртүрлі болса, varchar(n) типін қолдануға болады. Бұл типтің мәліметтері үшін сақтау аймағының өлшемі символдардың нақты санына сәйкес өзгеріп отырады, соңынан бос жолдар қосылмайды.
Мәтіндік мәліметтердің үлкен көлемдері үшін text типі қолданылады. Осы типтерге сәйкес тік жолға мәліметтерді енгізу үшін оларды апострофтарға орнату керек.
Мәліметтердің сандық (numeric) типтері. SQL үшін стандарттылар келесілер болып табылады: integer, smallint – бүтін сандарды сақтау үшін; real – жеті санға дейін дәлдікпен оң таңбалы және теріс таңбалы бөлшектерді сақтау үшін; float(n) – он бес санға дейін дәлдікпен оң таңбалы және теріс таңбалы бөлшектерді сақтау үшін.
Келесі datetime және smalldatetime мәліметтер типтері даталар мен уақытты сақтауға қолданылады. Символдар жолдары түрінде емес, осы типтерді қолданған ыңғайлы, себебі сонда датасы мен уақыт экранға үйреншікті форматта көрсетіледі. Datetime типі датасы мен уақытты 1/1/1753 бастап 12/31/9999-ге дейін анықтауға, ал, smalldatetime - 1/1/1900-дан 6/6/2079-ға дейін даталар мен уақытты анықтауға мүмкіндік береді.
Аталған мәліметтер типтері ақпараттың 90%-на дейін сақтауға мүмкіндік береді. Сонымен бірге, Transact SQL-де мәліметтердің арнайы типтерінің жиыны бар. Соңынан сақталынатын құрылымдарға қолданатын пайдаланушының типінде анықтауға болады.
Кестенің тік жолына мәліметтер типтерін орнатқанда NULL/NOT NULL шектеулерін қолдануға болады. Бұл шектеу қандай тік жолдың мәндері кестенің жатық жолдарының барлығына орнатылуы керек екендігін көрсетеді.
1.2 Мәліметтер қорымен жұмыс істеу
1.2.1 Мәліметтер қорын жасау
SQL Server-дің кез-келген объектісін жасау үшін анықталған бұйрықты орындауда негізделген бірнеше тәсілі бар.
Жұмысты мәліметтер қорын жасаудан бастаймыз. Мәліметтер қорын жасаудың Create Database бұйрығының синтаксисі:
CREATE DATABASE мәліметтер_қорының_аты
ON [PRIMARY]
(NAME = мәліметтер_қорының_аты _data, FILENAME=' …\
мәліметтер_қорының_аты _data.mdf ', size = өлшем, maxsize = максималды өлшемі, filegrowth = өсімше)
LOG ON
(NAME = мәліметтер_қорының_аты _log, FILENAME=’… \ мәліметтер_қорының_аты _log.ldf ', size = өлшем, maxsize = максималды өлшемі, filegrowth = өсімше)
Ескерту:
а) Осы жерде және соңынан да опцияларды квадратты жақшада орнату осы параметрдің міндетті түрде керек еместігін белгілейді. Мысалы, жоғарыдағы бұйрықта PRIMARY параметрі мәліметтер қорының және жүйелік кестелердің логикалық басы болатын файлды анықтайды. Мәліметтер қорында бастапқы (PRIMARY ) файл жалғыз болады. Бұл параметр көрсетілмеген болса, тізімдегі бастапқы файл ретінде бірінші файл қолданылады. Келісім бойынша primary типті файлдарға .mdf кеңейтілуі меншіктеледі.
б) Егер де опциялар тік сызығымен бөлінген болса, онда екі альтернативті нұсқаларардан таңдаудың мүмкіншілігі бар екенін түсінеміз.
1.2.2 Мәліметтер қорын жою
Мәліметтер қорын жою нәтижесінде оның орнатылған файлдардың барлығы және оларда орнатылған объектілердің барлығы жойылады:
а) графикалық режимде мәліметтер қоры келесі әрекеттер көмегімен жойылады:
- мышканың оң жақ батырмасын жойылатын мәліметтер қорының атында басыңыз;
- контекстті менюден Delete бұйрығын таңдаңыз; пайда болған хабарлар терезесінде мәліметтер қорын жоюды дәлелеңіз.
б) тransact-SQL көмегімен мәліметтер қорын жою үшін келесі бұйрық орындалады:
DROP DATABASE имя_базы_данных
1.2.3 Мәліметтер қорының резервті көшірмесін жасау
Мәліметтер қорының көшірмесін жасау, оны тасымалдаушыларға көшіру үшін біріншіден оның MS SQL Server 2005-тің арнайы мүмкіншіліктері көмегімен резервті көшірмесін жасау керек:
- Databases тізімінен қажетті мәліметтер қорын және контекстті менюден Tasks-Back Up жолын таңдаңыз;
- пайда болған терезеде біріншіден көшірмелеуге дайын мәліметтер қорларының тізімін тазартыңыз;
- «Browse» батырмасы көмегімен дискіде орын таңдайтын терезесін ашып, мәліметтер қорының резервті көшірмесінің атын енгізіп, көшірме бұйрығын дәлелдеңіз.
Жүйе көшірменің ойдағыдай болғаны туралы хабар беруі керек.
Резервті копия деп бұл жерде мәліметтер қорының мәліметтерін сақтауды айтамыз. Егер де мәліметтер қорын толығымен көшіру керек болса, онда C:\Program Files\...\Ms SQL\Data\имя_файла адрес бойынша сақталынған .mdf және .ldf файлдарды көшіру керек.
1.2.4 Мәліметтер қорын қалпына келтіру
Егер де сақталынған резервті көшірмені қайтадан компьютерге көшіру қажет болса, олда MS SQL Server 2005 жүйесінің арнайы мүмкіншіліктері көмегімен жасалады:
- қалпына келтірілетін мәліметтер қорын таңдаңыз;
- контексттік менюден Tasks-Restore жолын таңдаңыз;
- қалпына келтірудің жалпы параметрлері бар бет пайда болады; резервті көшірменің көзін және оның орнын орнатыңыз;
- «Параметры» бетін таңдап, «Перезаписать существующую базу данных» жалауын және «Состояние восстановления» бөліктегі бірінші қайта қосу белгісін белгілеңіз;
- таңдалынған операцияларды дәлелдеңіз.
Жүйе қалпына келтіру әрекеті ойдағыдай орындалғаны туралы хабар беруі керек. Егер де жүйе мәліметтер қорын қалпына келтіру орындалмады деп скрпитті өзгерту керек деп ұсынса, бөлек терезеде оны орындау керек.
1.3 Мәліметтер қорының құрылымын анықтау
1.3.1 Зертханалық жұмыста қолданылатын мәліметтер қорының бейнелеуі
SQL Server ортасында жұмыс жасауды игеру үшін келешекте сатулармен айналысатын сауда компанияcын қарастырамыз. Мәліметтер қорының құрылымы жобалау қадамында аныөталады. Мәліметтер қорларын жобалаудың сұрақтары №1 есептеу-графикалық жұмыста қарастырылады. Мәліметтер қорын TradeCompany деп атаймыз.
TradeCompany мәліметтер қоры кейбір сауда фирмасының қызметі туралы мәліметтерден тұрады. Фирма тауарлардың әртүрлі түрлерін сатады. Фирмада клиенттер (заңды құқығы бар мекеме) туралы ақпарат бар. Әр сатулар бойыша келісімдерге шот жасалынады, онда келесідей мәліметтер орнатылады; шот нөмірі, клиент туралы мәліметтер, сатылған тауарлардың тізімі мен сандары, сату датасы. Сатылған тауардың құны автоматты түрде есептелінуі керек.
Осы ақпараттың барлығы Тауарлар, Клиенттер және Шоттар кестелерінде сақталатын болсын. Кез келген кестенің құрылымы мен кестеде сақталатын мәліметтері бар. Кесте құрылымы оның тік жолдарымен анықталады: тік жол сандарымен, әр тік жолдың атымен, тік жолда сақталатын мәліметтің типімен, тік жол енімен.
TradeCompany мәліметтер қорының кестелерінің құрылымдары төменде келтірілген:
Тауарлар кестесі:
|
Тауар коды |
Тауар бейнелеуі |
Тауар бағасы |
Клиенттер кестесі:
|
Клиент коды |
Клиент атауы |
Клиент адресі |
Клиент телефоны |
Шоттар кестесі:
|
Шот нөмірі |
Клиент коды |
Тауар коды |
Саны |
Шот датасы |
Қордың мәліметтері кестенің жатық жолдар мазмұнымен анықталады. Кесте тік жолдары өрістер, ал жатық жолдары – жазбалар деп аталады.
Кілттік өріс (primary key) – реляциялық мәліметтер қорының кестесінде мәліметтердің әр жатық жолын мәліметтері бір мағыналы анықтайтын тік жол. Кілттік өрістің міндеті - әр жазбаның уникалдығын қамтамасыздандыру болып табылады. Әдетте кілт кестенің жалғыз бағанымен беріледі, бірақ бірнеше бағандар комбинациясымен анықталатын күрделі кілтті де орнатуға болады. Кілт кесте жасалған кезде меншіктеледі.
Сыртқы кілт (foreign key) – бағындыратын кестенің кілтіне сілтеме жасайтын бағынышты кестедегі тік жол. Сыртқы кілт ретінде таңдалынған тік жол басқа кестедегі кілт ретінде анықталған тік жолға сілтеме жасау үшін қолданылады.
Қолданылатын бағдарламалық қамтамасыздандырудың талаптарына сәйкес қарастырылып отырған мәліметтер қорының объектілері үшін атауларын ағылшын тілде орнатамыз (мәліметтердің өздерін орыс тілде енгізу болады). Ал, кестелерге аттарды таңдағанда келесіні ескертуге болады. Мәліметтер қорында кестелерден басқа да көп объектілер бар. Сондықтан кестелер аттарында _TBL суффиксті орнату стандарт болып табылады (ал, мысалы, _IDX суффиксі кестелердің индекстеріне қолданылады). Аттарды беру ережелерімен бірге сәйкес қызмет саласындағы ережелерге орындау дұрыс болады. Сонда аттардың бейнелеу қасиеттері болып, олар сақталынатын мәліметтерге сәйкес болады. Сонымен бірге, бірнеше сөзден тұратын атаулардың әр сөзі арасында төменнен сызып қою белгісі қолданылады. Мәліметтер қорының объектілерінің аттарында суффикстерді қолдану міндетті түрде емес. Жасалынатын мәліметтер қорының кестелері үшін келесідей CUSTOMER_TBL, PRODUCTS_TBL, ORDERS_TBL аттарды орнатамыз.
Жоғарыда айтылғандарды есепке алып, қарастырылып отырған мәліметтер қорының кестелері арасындағы байланыстар сұлбасы келесі түрде көрсетуге болады (1.1 суреті). Кестелерді байланыстыратын сызулар жалпы кілттік өріс арқылы кестелердің байланысуын көрсетеді. Ұсынылған нұсқада ORDERS_TBL кестесінде бастапқы кілт анықталмаған, себебі бір нөмірі бар шотта бірнеше тауарлар тізімі болуы мүмкін (шот нөмірі бухгалтерлік құжаттарда қолданылады).
 |
1.1 Сурет – TradeCompany мәліметтер қорының кестелері арасындағы байланыстар
1.3.2 Мәліметтер қорының құрылымын анықтау (DDL)
Мәліметтерді анықтау тілі (DDL) мәліметтер қорының әртүрлі объектілерін жасауға, олардың құрылымын жаңадан анықтауға, мысалы, кестелерді жасап, жоюға пайдаланушыға мүмкіндік беретін SQL-дің бөлігі.
DDL-дің келесі бұйрықтарын: CREATE TABLE, ALTER TABLE, DROP TABLE қарастырайық.
Кестелерді жасау үшін CREATE TABLE операторы қоллданылады. Бұл оператордың синтаксисі:
CREATE TABLE кесте_аты
(ӨРІС1 МӘЛІМЕТТЕР ТИПІ [NOT NULL],
ӨРІС2 МӘЛІМЕТТЕР ТИПІ [NOT NULL],
ӨРІС3 МӘЛІМЕТТЕР ТИПІ [NOT NULL],
ӨРІС4 МӘЛІМЕТТЕР ТИПІ [NOT NULL],
ӨРІС5 МӘЛІМЕТТЕР ТИПІ [NOT NULL] )
Кестегі кілт оны жасаған кезде PRIMARY KEY опциясы көмегімен жалғыз немесе бірнеше өрістерге тағайындалады және де шектеу шарты болып табылады:
CREATE TABLE кесте_аты PRIMARY KEY
(ӨРІС1 МӘЛІМЕТТЕР ТИПІ [NOT NULL], …)
Кілтті кестенің тік жолдарының барлығы анықталғаннан кейін де шектеу шарты ретінде жасауға болады және кілт құрамды болса, оның барлық компоненттері үтір арқылы аталып кетеді:
CREATE TABLE кесте_аты
(ӨРІС1 МӘЛІМЕТТЕР ТИПІ [NOT NULL],
ӨРІС2 МӘЛІМЕТТЕР ТИПІ [NOT NULL], …
PRIMARY KEY (ӨРІС1, ӨРІС2)
)
Сыртқы кілт FOREIGN KEY опциясы көмегімен жасалады. Сыртқы кілт кестенің барлық тік жолдары анықталғаннан кейін келесідей жасалады
CREATE TABLE кесте_аты _1
(ӨРІС1_1 МӘЛІМЕТТЕР ТИПІ [NOT NULL],
ӨРІС1_2 МӘЛІМЕТТЕР ТИПІ [NOT NULL],
…,
ӨРІС2_1 МӘЛІМЕТТЕР ТИПІ [NOT NULL],
CONSTRAINT ӨРІС2_1_FK FOREIGN KEY (ӨРІС2_1)
REFERENCES кесте_аты _2 (ӨРІС2_1)
)
ӨРІС2_1 тік жолы бұларад а кесте_аты_1 кестенің сыртқы кілті ретінде тағайындалған. Бұл сыртқы кілт кесте_аты_2 кестенің ӨРІС2_1 тік жолына сілтеме жасайды.
1.4 Кестені модификациялау
Кесте жасалғаннан кейін оны ALTER TABLE бұйрығы көмегімен модификациялауға болады. Бұл бұйрық көмегімен тік жолдарды косуға немесе жоюға, тік жолдар анықтамаларын өзгертуге, шектеулерді косуға немесе жоюға болады.
ALTER TABLE бұйрығының стандартты синтаксисі келесідей:
ALTER TABLE кесте_аты [MODIFY] [COLUMN тік_жол_аты]
[МӘЛІМЕТТЕР ТИПІ |NULL NOT NULL] [RESTRICT|CASCADE]
[DROP] [CONSTRAINT шектеу_аты]
[ADD] [COLUMN] тік_ жол_ анықтамасы
а) Кесте элементтерін модификациялау. Тік жол атрибуттары мәліметтерді тік жолдарда көрсетудің ережелерін орнатады. ALTER TABLE бұйрығы көмегімен тік жол атрибуттарын өзгертуге болады. Атрибут деп бұл жерде келесілерді түсінеміз: тік жол мәліметтерінің типтерін, тік жолдағы мәліметтердің ұзындығын, дәлдігін және масштабын; тік жолда NULL мәнін орнатуға рұқсат немесе рұқсат еместігін.
Мәліметтері бар кестеге тік жолды қосқан кезде жаңа тік жолға NOT NULL атрибутын беруге болмайды. NOT NULL атрибут тік жолда міндеттті түрде мәндер бар дегенді білдіреді, сондықтан, жаңа тік жолға осы атрибутты меншіктесеңіз, қайшылық пайда болады (жаңа тік жолдың әзірше мәндері жоқ болғандықтан). Бірақ осындай атрибуты бар тік жолды орнатудың амалы бар:
- тік жолды қосқанда оған NULL атрибутын орнатыңыз (басқа сөзбен айтқанда, тік жолда мәліметтердің болмауы болады деген);
- кестенің жаңа тік жолының әрбір жатық жолына мәндерді енгізіңіз;
- жаңа тік жолдың барлық жатық жолдарының мәндері бар екеніне көз жеткеннен кейін тік жол атрибутын NOT NULL деп өзгертіңіз.
б) Тік жолдарды өзгерту. Кестелердің тік жолдарын өзгерту үшін келесі ережелерді орындау керек:
- тік жол ені сәйкес мәліметтер типінің максималды ұзындығына дейін үлкейтуге болады;
- тік жол енін онда орнатылған мәндердің максималды ұзындығына дейін азайтуға болады;
- сандық мәндері бар тік жолдар үшін оның енін кезкелген уақытта үлкейтуге болады;
- сандық мәндері бар тік жолдар үшін оның енін тек қана тік жол мәндері сиятынға дейін азайтуға болады;
- сандық мәндер үшін ондық белгілер санын көбейтуге немесе азайтуға болады;
- тік жолдағы мәліметтер типін әдетте өзгертуге болады.
SQL-дің кейбір іске асыруларында ALTER TABLE операторының кейбір опцияларын қолдануға рұқсат етілмейді. Мысалы, кестелерден тік жолдарды жоюға рұқсат етілмеуі мүмкін. Оның орнына кестені жойып, қажетті тік жолдары бар кестені жасау керек болады немесе басқа кестенің тік жолдарынан тәуелді ағынды кестенің тік жолдарын жоюға, сонымен бірге, басқа кесте сілтеме жасайтын тік жолды жоюға рұқсат берілмеуі мүмкін. Мұндай жағдайда жұмыс жасалынатын SQL-дің іске асырылудың құжаттарын ұқыпты оқу қажет.
в) Шектеулерді қосу. Осындай әрекеттерді қолдану келесідей жағдайларда пайда болуы мүмкін. Мысалы, кесте жасалғанда кілттік өрістері анықталмаған болды. Оны келесідей түзетуге болады:
ALTER TABLE кесте_аты
ADD CONSTRAINT кесте_аты _PK PRIMARY KEY (өріс_аты1, өріс_аты2)
Сыртқы кілттерді кестеде келесідей орнатуға болады:
ALTER TABLE кесте_аты
ADD CONSTRAINT ID_FK FOREIGN REY (өріс_аты)
REFERENCES кесте_аты (өріс_аты)
г) Кестелерді жою ең қарапайым бұйрық болып табылады. Оператор синтаксисі келесі:
DROP TABLE кесте_аты [RESTRICT|CASCADE ]
RESTRICT опциясы қолданылса немесе кестеге елестету мен шектеулер сілтеме жасаса, қолданылып отырған DROP операторы қате туралы хабар береді. CASCADE опциясы қолданылған кезде, кестенің тек өзі емес, оған сілтеме жасайтын елестетулер мен шектеулер де жойылады.
1.5 Зертханалық жұмысқа тапсырма
1.5.1. TradeCompany мәліметтер қорын жасау
а) Мәліметтер қорын графикалық режимде жасау
Бұл әдісті қолданғанда келесідей әрекеттер жасалады:
- MS SQL Server 2005 жүйесін негізгі меню көмегімен жұмысқа қосыңыз: Пуск-Все программы- MS SQL Server 2005-SQL Server Management Studio;
- пайда болған терезеде сервер атын енгізуді сұрайды, оның аты келісім бойынша орнатылған, Connect батырмасын басыңыз;
- келесі Object Explorer терезеде Databases жолдың контексттік менюінен New Database бұйрығын таңдаңыз;
- пайда болған терезеде жасалынып отырған мәліметтер қорының атын енгізіп, ОК басыңыз; осы терезенің төменгі жағында мәліметтер қорының файлдары туралы ақпарат орнатылған (кестенің барлық тік жолддарын көру үшін терезені үлкейтіңіз); егер де мәліметтер қоры жалғыз емес бірнеше файлда орнатылса мұндағы Add батырмасы қолданылады;
- терезені жауып, сіздің мәліметтер қорыңыздың аты Database тізімінде бар екенін көріңіз;
- мәліметтер қорын жойыңыз (контекстті менюден Delete бұйрығы).
б) Мәліметтер қорын Transact-SQL бұйрық көмегімен жасау.
Object Explorer терезесінде New Query батырмасын басыңыз. Экранның оң жағында сұраныстар редакторының терезесі пайда болады. Осы терезеге мәліметтер қорын жасау бұйрығын енгізіңіз. Егер де бұйрық дұрыс орындалса, терезенің төменгі жағында келесідей: Command (s) completed successfully хабар пайда болады.
Графикалық режимнен өзгеше жасалынған мәліметтер қорын тізімнен көру үшін Database жолда контекстті менюден Refresh бұйрығын таңдау керек.
1.5.2 Мәліметтер қорының резервті көшірмесін жасаңыз. Резервті көшірме сақталған жерді еске сақтаңыз.
1.5.3 Мәліметтер қорын қалпына келтіру бұйрығын орындаңыз.
1.5.4 Қарастырылып отырған TradeCompany мәліметтер қорының кестелерінің өрістеріне мәліметтер типтерін дәлелдеп таңдаңыз. Кестелердің бастапқы және сыртқы кілттерді анықтаңыз.
1.5.5 MS SQL Server 2005 жүйесін жұмысқа қосыңыз; ағынды мәліметтер қоры ретінде TradeCompany қорын таңдаңыз.
1.5.6 TradeCompany мәліметтер қорының кестелерін жасау және модификациялау
Кестелерді бірнеше жолдармен жасауға болады:
а) CUSTOMER_TBL кестесін графикалық режим көмегімен жасаймыз. Ол үшін келесілерді орындау керек:
- өзіңіздің мәліметтер қорыңыздың папкасын ашыңыз; Tables жолда контекстті менюден New Table бұйрығын таңдаңыз;
- пайда болған терезеде кестенің тік жолдарының атауларын енгізіңіз, мәліметтер типтерін таңдаңыз, Allows Null жалауын белгілеңіз (немесе белгісін алып тастаңыз); терезенің төменгі жағында тік жолдар қасиеттерін орнатыңыз.
б) Object Explorer терезесінде New Query батырмасын басып, бұйрық редакторының терезесін ашыңыз; осы терезеге тізбектеп кестелерді (PRODUCTS_TBL және ORDERS_TBL) жасау бұйрықтарын енгізіп, Execute батырмасын басып, қалған кестелерді жасаңыз.
в) кесте құрылымын өзгерту үшін контексттік менюден Modify Table бұйрығын таңдаймыз.
г) SQL бұйрықтарын тез үйрену үшін мәліметтер қорының кестесін белгілеп, контексттік менюден Script Table As/…/New Query Editor Window (көп нүкте орнына қажетті бұйрықты таңдаңыз) жолын таңдаңыз, бұйрықтар редакторының терезесі пайда болады, одан оқылып отырған кесте жасаудың, жоюдың, жаңартудың, т.б. скрипттерінің дайындамаларын қарап шығуға болады.
1.5.7 Жасалынған мәліметтер қорының байланыстар диаграммасын қарап шығыңыз (Diagrams бөлігі).
1.6 Есеп беруге қойылатын талаптар
Жұмыс бойынша есеп беру қағазда орындалады және оған келесілер кіреді:
- мәліметтер қорын жасаудың нұсқаларын бейнелеу;
- кестелер өрістерінің мәліметтер типтерін таңдауын дәлелдеу;
- кестелердің бастапқы және сыртқы кілттерін анықтау;
- кесте құрылымдарын жасау бұйрықтары.
1.7 Бақылау сұрақтары
1.7.1 SQL аббревиатурасы қалай шешіледі?
1.7.2 SQL бұйрықтарының типтерін атаңыз.
1.7.3 Мәліметтер қорларын жасаудың қандай әдістері бар, олардың айырмашылықтары?
1.7.4 Мәліметтер қорлары үшін неше файл қолданылады?
1.7.5 Транзакциялар журналының міндеті?
1.7.6 Мәліметтер қорының резервті көшірмесін жасау және қалпына келтіруді түсіндіріңіз.
1.7.7 Мәліметтер қорын жою әдістерін түсіндіріңіз.
1.7.8 Реляциялық мәліметтер қорының түсініктемесін беріңіз.
1.7.9 Мәліметтер қорының бастапқы ақпараты қандай объектілерде сақталады?
1.7.10 Кестенің өрісі деген не? Кесте жазбасы деген не? Айырмашылықтарын түсіндіріңіз.
1.7.11 Кесте құрылымын анықтайтын бұйрық?
1.7.12 Кестенің бастапқы кілті анықтамасын беріңіз.
1.7.13 Мәліметтердің негізгі типтері атап кетіңіз.
1.7.14 Кесте құрылымын қандай бұйрықпен өзгертуге болады?
1.7.15 Кестені қалай жоюға болады?
2 №2 зертханалық жұмыс. Мәліметтермен әрекеттесу тілі
Жұмыстың мақсаты: мәліметтермен әрекеттесу тілін оқу.
Мәліметтермен әрекеттесу тілі (DML) пайдаланушыға реляциялық мәліметтер қорының мәліметтеріне нақты өзгерістерді енгізуге мүмкіндік беретін SQL-дің бөлігі болып табылады. DML көмегімен пайдаланушы кестелерге жаңа мәліметтерді енгізуіне немесе бар болатын мәліметтерді жаңартуға және жоюға болады. SQL-де DML-дың үш негізгі бұйрықтары бар: INSERT, UPDATE, DELETE.
2.1 Кестелерді мәліметтермен толтыру
Кестелерді мәліметтермен толтыру – бөлек бұйрықтар көмегімен немесе автоматты түрде процедуралар көмегімен кестеге жаңа мәліметтерді енгізу процесі болып табылады. Қандай мәліметтерді және қандай көлемде кестеге енгізуге болатыны көптеген факторлардан тәуелді, олардың ішінде маңыздысы кестені анықтағандағы орнатылған шектеулер, кестенің физикалық өлшемдері, кесте тік жолдарының мәліметтер типтері, тік жолдар өлшемдері, кілттер және сыртқы кілттер түрінде анықталыт бүтіндік талаптары болып табылады.
Келесідей жағдайлар болуы мүмкін:
а) кестеге жаңа мәліметтерді енгізу.
Кестеге жаңа мәліметтерді енгізу үшін INSERT операторы қолданылады. Оның түрі келесідей:
INSERT INTO кесте_аты
VALUES ('мән1', 'мән2', [ NULL ] )
Көрсетілген INSERT операторының синтаксисіне сәйкес, VALUES тізіміне кестенің тік жолдарының барлығына мәндерді орнату керек. Тізімде мәндер үтірлермен бөлінеді. Символдық мәндер мен даталар мәндері апострофта орнатылады. Сандық мәндер үшін және NULL сөзімен берілетін бос мәндер үшін тырнақшалар керек емес. Кестенің барлық тік жолдары үшін мәндер көрсетілуі керек;
Б) кестенің белгілі тік жолдарына мәліметтерді енгізу.
Егер де мәліметтерді кестенің барлық емес, тек белгілі тік жолдарына енгізу керек болса, INSERT операторында VALUES тізімімен бірге сәйкес тік жолдар тізімінде көрсету керек:
INSERT INTO кесте_аты ('ТІК_ЖОЛ1', 'ТІК_ЖОЛ2',) VALUES ('МӘН1', 'МӘН2');
Мәндер тізіміндегі реті тік жолдар тізімімен берілетін кестеге енгізілетін мәндер ретіне сәйкес болуы керек. INSERT операторындағы тік жолдар реті сәйкес кестенің анықтамасындағы тік жолдар ретіндей болмауы мүмкін, ал енгізілетін мәндер міндетті түрде таңдалынған тік жолдар ретіне сәйкес болуы керек;
в) NULL мәндерін енгізу.
NULL мәнін кестеге енгізу өте қарапайым. Осындай жағдай, мысалы, сәйкес тік жолдың мәні белгісіз болғанда туады. NULL енгізуге оператор синтаксисі келесідей:
INSERT INTO сұлба_аты.кесте_аты
VALUES ('МӘН1', NULL, 'МӘН3'}
2.2 Мәліметтерді жаңарту
Кестегі мәліметтерді UPDATE бұйрығымен жаңартуға болады. UPDATE бұйрығы кестеге жаңа жазбаларды қоспайды және оларды жоймайды, тек қана оларды өзгертуге мүмкіндік береді. Осындай бұйрық көмегімен тек қана жалғыз кестенің, бірақ кестенің бірнеше тік жолының мәліметтерін өзгертуге болады. Осындай оператордың біреуімен мәліметтердің жалғыз жатық жолын немесе бірнеше жолдарын өзгертуге болады:
а) жалғыз тік жолдың мәндерін өзгерту. Өзінің қарапайым түрінде UPDATE операторы кестенің бір тік жолын өзгертеді. Бір тік жолды өзгерткенде тек қана жалғыз жазбаны немесе бірнешеуін өзгертуге болады. Бір тік жолдағы мәліметтерді өзгертудің операторының синтаксисі:
UPDATE кесте_аты
SET ТІК_ЖОЛ_Аты = 'МӘН'
[WHERE ШАРТ]
б) UPDATE оператоы көмегімен бірдей бірнеше тік жолдардың мәндерін өзгерту:
UPDATE кесте_аты
SET ТІК_ЖОЛ1 = 'МӘН'
[,ТІК_ЖОЛ2 = 'МӘН']
[,ТІК_ЖОЛ3 = 'МӘН']
[WHERE ШАРТ]
SET қызмет сөзін қолдануды есепке алыңыз: сөз жалғыз, ал тік жолдардың бейнелеуі – бірнеше. Бейнелеулер үтірлермен бөлінеді.
2.3 Кестелерден мәліметтерді жою
Кестелерден мәліметтерді жою үшін DELETE бұйрығы қолданылады. DELETE бұйрығының міндеті бөлек тік жолдардағы мәндерді жою үшін емес, жазбаларды толығымен жою. DELETE қолданғанда ұқыпты болу керек. Кестеден жалғыз немесе бірнеше жазбаларды жою үшін DELETE операторының келесі синтаксисін қолданыңыз:
DELETE [FROM] кесте_аты
[WHERE ШАРТ];
Кестеден жатық жолдарды жойғанда DELETE операторының өте маңызды бөлігі болып WHERE қызмет сөзі табылады. Егер де DELETE операторында WHERE сөзі орнатылмаса, кестеден барлық жазбалар жойылады. Сондықтан DELETE операторында міндетті түрде WHERE қызмет сөзін қолдануды ереже түрінде қолданыңыз.
2.4 Зертханалық жұмысқа тапсырма
2.4.1 ORDERS_TBL кестенің құрылымын өзгертіңіз, оған ORDERS_ID кілттік өрісті қосыңыз (бұл өріс кестедегі әр жолдың идентификаторы болып табылады).. Бұл өріс келесі мақсатпен қосылады - әр сатылымды бір мағыналы анықтау қажеттілігімен.
2.4.2 Мәліметтер қорыңыздың барлық кестелерін мәліметтермен толтырыңыз; мәліметтерді енгізудің барлық мүмкіншіліктерін қолданыңыз:
- графикалық режим: кестені белгілеп, контексттік менюден Open Table жолын тандаңыз;
- кестені белгілеп, контексттік менюден Insert To-New Query Editor Window таңдаңыз;
- New Query менюды тандаңыз.
CUSTOMER_TBL және PRODUCT_TBL кестелерінде бес жазбадан кем болмауы керек, ORDERS_TBL кестесінде – ең кемінде 15 жазба (барлық клиенттермен әртүрлі тауарларды әртүрлі күндерде сатып алу).
2.4.3 ORDERS_TBL кестесіне TOTAL тік жолын қосыңыз (бұл тік жолда сатылған тауарлардың толық бағалары сақталады). Бұл тік жолды NULL мәндерімен толтырыңыз.
2.4.4 UPDATE бұйрығын қолданып, TOTAL өрістің мәндерін есептеңіз: тауар бағасының (PRODUCT_TBL.COST) тауардың сатылған санына (ORDERS_TBL.QTY) көбейтіндісі.
2.4.5 PRODUCT_TBL кестесінде кейбір тауарлар бағасын өзгертіңіз. Сатылған тауар санын өзгертіңіз. Сонда ORDERS_TBL.TOTAL тік жолында мәліметтердің өзгеретінін көріңіз.
2.4.6 Тауар өнімшілері туралы мәліметтерден тұратын MANUFACTURER_TBL кестесін жасаңыз (кесте өрістерін өз қалауыңыз бойынша таңдаңыз). Сонда PRODUCT_TBL кестенің де құрылымы өзгереді: MANUFACTURER_TBL кестенің бастапқы кілті PRODUCT_TBL кестесінің сыртқы кілті болады. Осы өзгерістерді орнатыңыз.
2.4.7 Сатушылар туралы мәліметтерден тұратын SELLER_TBL кестесін құрастырыңыз. Осыған байланысты ORDERS_TBL кестесі де өзгереді, оған SELLER_TBL кестесінің бастапқы кілтін орнату керек (кім сатылымды жасағанын тіркеп отырамыз).
2.4.8 Мәліметтер қорының өзгертілген реляциялық сұлбасын қарап шығыңыз.
2.4.9 Жасалынған кестелер мен өрістерді мәндермен толтырыңыз.
2.4.10 Ағынды жылдың наурыз айындағы сатылымдар туралы жазбаларды жойыңыз.
2.5 Есеп беруге қойылатын талаптар
Жұмыс бойынша есеп беру қағазда орындалады және оған келесілер кіреді:
- мәліметтер қорының кестелеріне мәліметтерді қосу бұйрықтары;
- мәліметтердің реляциялық сұлбасын өзгертудің барлық бұйрықтары;
- мәліметтер қорының кестелеріндегі мәліметтерді жаңарту бұйрықтары;
- мәліметтер қорының байланыстар диаграммасы;
- мәліметтері бар кестелердің листингтері.
2.6 Бақылау сұрақтары
2.6.1 DML деген не?
2.6.2 Мәліметтермен әрекеттесудің қандай бұйрықтарын білесіз?
2.6.3 Өрістің NOT NULL анықтамасы нені білдіреді?
2.6.4 INSERT бұйрығының VALUES тізімінде не орнатылады?
2.6.5 Мәліметтерді кестенің анықталған тік жолдарына енгізуге бола ма?
2.6.6 UPDATE бұйрығымен бірнеше кестелердің мәліметтерін өзгертуге бола ма?
2.6.7 UPDATE бұйрығымен кестенің бірнеше тік жолдарының мәліметтерін өзгертуге бола ма?
2.6.8 Кестені жою бұйрығында шартты әрқашанда қолдану керек пе?
2.6.9 DDL мен DML айырмашылықтары?
2.6.10 Кесте құрылымы мен кестенің мәліметтерінің айырмашылығы не де?
3 №3 зертханалық жұмыс. Мәліметтерді сұрыптау (DQL)
Жұмыстың мақсаты – мәліметтер қорына сұраныстарды жасау тілін игеру мен қарапайым және күрделі сұраныстарды жасауды үйрену.
3.1 SELECT операторы
Бұл бөлікте тек қана жалғыз бұйрық қарастырылады, бірақ реляциялық мәліметтер қорының пайдаланушысы үшін мәліметтерге сұраныстарды жасау (DQL) тілі SQL-дің ең басты бөлігі болып табылады. Бұл бұйрық – SELECT бұйрығы. Бұл бұйрықтың көп опциялары мен міндетті түрде қажетті емес параметрлері көп, олар реляциялық мәліметтер қорына сұраныстарды құрастыру үшін қолданылады. Оның көмегімен әртүрлі күрделілігі бар сұраныстарды құрастыруға болады, ең қарапайым және жалпы сұраныстардан арнайы және өте күрделі сұраныстарға дейін.
Сұраныс — мәліметтер қорынан ақпаратты алуға талап. Пайдаланушыға ыңғайлы түрде мәліметтер қорынан ақпаратты алу үшін сұраныстар қолданылады.
Мәліметтер қорына сұраныстарды құрастыру үшін SELECT операторы қолданылады. SELECT операторы өздігінен қолданылмайды, қызмет сөздер көмегімен сұраныстың кейбір параметрлерін орнатуды талап етеді. Міндетті түрдегі қызмет сөздерден басқа бұл операторда бірсыпыра міндетті түрде қажет емес қызмет сөздері бар, олар оператордың мүмкіндіктерін кеңейтеді.
Мәліметтер қорынан мәліметтерді оқуға ыңғайлы форматта алу үшін SELECT операторында SELECT қызмет сөзі FROM қызмет сөзімен бірге қолданылады. Сұраныстың SELECT қызмет сөзімен белгіленетін бөлігі сұрыптауға негізделген мәліметтер көзін белгілейді. Қарапайым SELECT операторының синтаксисі келесідей:
SELECT * | ALL | DISTINCT [ТІК_ЖОЛ1, ТІК_ЖОЛ2]
FROM КЕСТЕ1 [, КЕСТЕ2 ]
Сұраныс нәтижесінде алынатын мәндердің тік жолдар тізімі SELECT қызмет сөзінен кейін орнатылады. FROM қызмет сөзінен кейін мәліметтері алынатын кестелер тізімі көрсетіледі. Егер де сұраныс нәтижесінде кестенің барлық тік жолдарының мәндері көрсетілуі керек болса, жұлдызша (*) орнатылады. Егер де тік жолдың барлық мәндерін, сонымен бірге, қайталанатын мәндерінде көрсету керек болса ALL опциясы қолданылады. Егер де қайталануларды жою керек болса, DISTINCT опциясы қолданылады. Бұл опциялар ішінде келісім бойынша орнатылатын ALL опциясы болады, сондықтан оны көрсетпеуге де болады. SELECT қызмет сөзден кейін орнатылған тік жолдар аттары бір бірінен үтірлермен бөлінеді, FROM қызмет сөзінен орнатылған кестелер аттары да бір-бірінен үтірлермен бөлінеді.
3.2 Мәліметтерді сұрыптауға орнатылатын шарттардың операциялары
Мәліметтерді сұрыптауға шарттар қолданылады. Шарт дегеніміз сұраныс бөлігі, бұл бөлікте мәліметтерді сұрыптауға негізгі болатын ақпарат орнатылады. Шарт сұрыптауға қолданылатын TRUE немесе FALSE мәндерін қабылдайды.
WHERE өрнегін қолданатын SELECT операторының синтаксисі келесідей:
SELECT [ ALL | * | DISTINCT СТОЛБЕЦ1, СТОЛБЕЦ2 ]
FROM КЕСТЕ1 [ , КЕСТЕ2 ]
WHERE [ШАРТ1|ӨРНЕК1]
[ AND ШАРТ2 | ӨРНЕК2 ]
WHERE өрнегінде бірнеше шарттар орнатылуына болады.
Шарттар операциялар көмегімен беріледі. Операция дегеніміз – SQL операторындағы элементтерді байланыстыруға қолданылатын символ немесе SQL-дің қызмет сөзі.
Мәліметтерді сұрыптаудың шарттарында операциялардың келесідей типтері қолданылады: салыстыру операциялар, логикалық операциялар, арифметикалық операциялар, арнайы операциялар.
Салыстыру операциялар келесі=, <>, <, >, <=, >= белгілермен көрсетіледі. Олар мәндер арасында "тең", "тең емес", "үлкен", "кіші", "үлкен немесе тең", "кіші немесе тең" қатынастардың орындалуын тексеруге негізделген.
Логикалық операциялар SQL-де символдармен, емес қызмет сөздермен беріледі. Келесідей логикалық операциялар қолданылады:
- AND (логикалық "ЖӘНЕ") – екі логикалық мәндерді салыстырады, егер де екеуі де ақиқат болса, TRUE мәнін қайтарады, басқа жағдайларда - FALSE мәні қайтарылады;
- OR (логикалық "НЕМЕСЕ") - екі логикалық мәндерді салыстырады; егер де аргументтерінің біреуі TRUE болса, TRUE мәнін қайтарады;
- NOT (логикалық келіспеу ) - егер де оператор аргументі FALSE болса, TRUE мәнін қайтарады, кері жағдайда – керісінше.
Арифметикалық операциялар SQL-де басқа тілдердегідей қолданылады. Осындай операциялар төртеу: + (қосу), * (көбейту), - (алу), / (бөлу).
Аоифметикалық операцияларды комбинациялауғға болады. Пайдаланушы жақшаларды қойып, олардың өрнекте орындалу ретін өзгертуіне болады. Жақшада орнатылған өрнек тұтас блок ретінде қарастырылады, операциялар орындалу реті (операциялар приоритеті) SQL-дің математикалық өрнектерінде немесе өзінің ішкі функцияларында өрнектердің өңделу ретін көрсетеді.
3.3 Бірнеше кестелерден мәліметтерді сұрыптау
SQL-дің ең пайдалы мүмкіншіліктерінің бірі – бірнеше кестелерден мәліметтерді сұрыптау. Кестелерді байланыстыру үшін кілттік өрістер қолданылады.
Кестелерді байланыстыру үшін SQL операторының қажетті элементі WHERE қызмет сөзі болады. Байланыстыруға қолданылатын кестелер аттары FROM қызмет сөзінің тізімінде көрсетіледі. Байланыс WHERE қызмет сөзінің өрнегінде анықталады. Байланыстардың бірнеше типтері бар:
а) Теңдік бойынша байланыстыру. Бұл ең пайдалы байланыстыру типі. Оны тағы да ішкі байланыстыру (INNER JOIN) деп атайды. Теңдік бойынша байланыстырғанда кестелер жалпы тік жол бойынша байланыстырады, әдетте ол әр кестеде кілттік тік жол болып табылады. Теңдік бойынша салыстырудың синтаксисі:
SELECT кесте1.тік_жол1, кесте2.тік_жол2…
FROM кесте1, кесте2 2 [, кесте3]
WHERE кесте1.тік_жол1_аты = кесте2.тік_жол2_аты
[ AND кесте1.тік_жол1_аты = кесте3.тік_жол_аты]
SELECT операторының тізімінде әр тік жол атымен бірге сәйкес кестелердің аттары орнатылғанына зер салыңыз. Осындай көрсетуді сұраныста тік жолды түгелімен анықтау деп атайды. Түгел анықтаулар сұраныста көрсетілген кестелердің бірнешеуінде орнатылған тік жолдар үшін керек болады. Бірақ операторда әдетте түгел анықтаулар барлық тік жолдар үшін орнатылады.
б) Кәдімгі байланыстыру. Кәдімгі (немесе жаратылыс) байланыстыру теңдік бойынша байланыстыруға ұқсас, бірақ кәдімгі кестелерді байланыстырғанда эквивалентті тік жолдардың қайталанулары жойылады. Байланыстыру шарттары алдындағыдай, бірақ тік жолдар басқаша таңдалынады. Оператор синтаксисі:
SELECT кесте1.*, кесте2.тік_жол_аты
[ , кесте3. тік_жол_аты1]
FROM кесте1, кесте2 2 [, кесте3 ]
WHERE кесте1.тік_жол_аты = кесте2.тік_жол_аты
[ AND кесте1.тік_жол_аты = таблица3.тік_жол_аты]
г) Теңсіздік бойынша байланыстыру. Теңсіздік бойынша байланыстыруда екі немесе бірнеше кестелер бір кестенің тік жолының мәні басқа кестенің тік жолының мәніне тең болмау шарты бойынша байланысады. Оператордың сәйкес бөлігінің синтаксисі:
FROM кесте1, кесте2 [, кесте3 ]
WHERE кесте1.тік_жол_аты = таблица2.тік_жол_аты
[ AND таблица1.тік_жол_аты = таблица3.тік_жол_аты]
Теңсіздік бойынша байланыстыру кезде шығу өрнегінде қажет болмайтын жолдар болуы мүмкін, сондықтан теңсіздік бойынша байланыстыру қолданылатын сұраныстың нәтижесін қосымша тексеру керек.
д) Рекурсивті байланыстыру (псевдонимдерді қолдану). Мәтінді теру жұмысын азайту үшін кестелерге псевдонимдерді - уақытша басқа аттарды беруге болады. Рекурсивті байланыстыру кестелерде рекурсивті тәртібі бойынша байланыстыруды орнатқанда жиі қолданылады.
Рекурсивті байланыстыру (SELF JOIN) – SQL операторында кестені уақытша басқа атпен атап, өзін өзімен байланыстыру болып табылады. Оператор синтаксисі:
SELECT А.тік_жол_аты, В.тік_жол_аты [ , С.тік_жол_аты]
FROM кесте1 А, кесте2 В [, кесте3 С ]
WHERE А.тік_жол_аты = В.тік_жол_аты
[AND А.тік_жол_аты = С.тік_жол_аты]
Мұндағы А, В, С – кестелердің псевдонимдері.
е) Бірнеше кілттер бойынша байланыстыру. Мәліметтер қорының құрылымына қарай кестелердің қарапайым емес, құрамдасқан яғни бірнеше тік жолдардан тұратын кілттері болуы мүмкін. Бірнеше тік жолдардан сыртқы кілттерде болуы мүмкін. Осындай жағдайда WHERE сөйлемінде кілттің әрбір құрамдастырушысын салыстыру керек.
3.4 Зертханалық жұмысқа тапсырма
3.4.1 TradeCompany фирмасының мәліметтер қорының бөлек кестелеріндегі ақпаратты табуын орындаңыз:
- фирманың барлық клиенттерінің тізімін;
- фирмада бар болатын тауарлардың барлығының тізімін;
- бағалары көрсетілген мәннен артық болмайтын тауарлардың тізімін;
- ағынды айда берілген шоттар тізімін;
- белгілі аты бар тауарлар тізімін.
3.4.2 Мәліметтер қорының бірнеше кестелерінде орнатылған мәліметтерді табуын орындаңыз:
- белгілі түрдегі тауарларды сатып алған фирма клиенттерінің тізімін; \
- барлық клиенттер мен олардың шоттарының тізімін;
- белгілі клиентке тіркелген шоттар тізімін;
- белгілі датасында алынған тауарлар мен оларды алған клиенттер тізімін;
- белгілі клиентпен алынған тауарлар тізімі мен олардың саны;
- белгілі тауарды алғаны үшін белгілі клиентке тіркелген шоттың датасын;
- белгілі тауарды алмаған клиенттердің тізімі.
3.5 Есеп беруге қойылатын талаптар
Зертханалық жұмыс бойынша есеп беру бұйрықтар листингтері мен сұраныстарды орындағанның нәтижелерінен тұрады.
3.6 Бақылау сұрақтары
3.6.1 SELECT операторы SQL-дің қай бөлігіне қатысты?
3.6.2 SELECT операторының міндетті түрдегі құрамдастырушыларын атаңыз.
3.6.3 WHERE қызмет сөзінің өрнегіндегі мәліметтердің барлығына тырнақшаны қолдану керек пе?
3.6.4 WHERE қызмет сөздің өрнегінде бірнеше шарттарды орнатуға бола ма?
3.6.5 Сандық өрістер үшін тырнақшаларды қолдануға бола ма?
3.6.6 SELECT операторын FROM қызмет сөзсіз қолдануға бола ма?
3.6.7 Кестенің псевдонимы деген не?
3.6.8 Кестелерді байланыстырғанда олардың реті FROM қызмет сөзіндегі өрнегінде көрсетілгендей болуы керек пе?
3.6.9 Сұраныс операторында байланыстыратын кестені қолданғанда оның тік жолдарын міндетті түрде сұраныс сөйлемінде таңдау керек пе?
3.6.10 Сұраныста кестенің жалғыз емес, бірнеше тік жолдарын байланыстыруға бола ма?
3.6.11 SQL оператораның қай бөлігі кестелер байланыстыруының шартын орнатады:
3.6.12 Егер де сұраныста екі кестеден сұрыптауды орнатып, оларды байланыстырмаса, не болады?
3.6.13 Рекурсивті байланыстыру деген не?
4 №4 зертханалық жұмыс. Агрегатты функциялар мен арнайы операторларды сұрыптау шарттарында қолдану
Жұмыстың мақсаты: арнайы түрдегі операторларды және сұраныс нәтижелері бойынша қорытындыларды алуды үйрену.
4.1 Арнайы түрдегі операторлар
Арнайы түрдегі оператор деп сұрыптау операторларда әртүрлі қызмет сөздерді қолдануды түсінеміз:
- IN – өріс мәні кіретін мәндер тізімін анықтайды. IN операторы үшін мәндер жиыны жақшаға орнатылып, мәндері үтірлермен бөлінеді. Мысалы, WHERE ADDRESS IN (‘Samal’, ‘Tastak’) сөйлемі Самал мен Тастак шағын аудандарда тұратын қызметкерлер тізімін береді.
- BETWEEN – рұқсат етілген мәндер тізімінен ерекше BETWEEN мәндер диапазонын белгілейді. Сұраныста BETWEEN қызмет сөзін көрсетіп, бастапқы мәнін, AND қызмет сөзін және соңғы мәнін көрсетеді.
- LIKE – жолдың ішкі құрамдастырушысын табу үшін тек қана символдық өрістерге қолданылады. Шарт ретінде арнайы символдар қолданылады: төменнен сызып қою ( _) символы кезкелген жалғыз символдың орнын басады, пайыз белгісі % - символдардың кезкелген санының тізбегінің орнын басады. LIKE мәндерді іздегенде ыңғайлы мәннің есіңізде бар бөлігін қолдануға болады.
- IS NULL – қарастырылып отырған мәннің NULL мәніне тең екендігін тексеруге қолданылады. Мысалы, WHERE PHONE IS NULL – телефондары жоқ қызметкерлерді табу үшін.
Кері шарттарды қарастыру үшін барлық осы операциялар үшін олардың кері мәндерін қарастыруға болады «қызмет сөзі NOT). NOT қызмет сөзі операциялармен келесідей қолданылады: NOT BETWEEN, IS NOT NULL, NOT IN, NOT LIKE.
4.2 Шығудағы мәліметтерді реттеу
Әдетте шығарылатын мәліметтерді реттеуд қажет болады. Шығарылатын мәліметтерді реттеуді ORDER BY қызмет сөзін қолданатын өрнектермен орындайды. ORDER BY қызмет сөзімен орнатылатын реттеу келісім бойынша өсу тәртібі бойынша реттеу болады және аттарды реттеу үшін A-Z (А-Я) деп белгіленеді. Кему бойынша алфавитті реттеу Z-А (Я-А) ретіне сәйкес. Сандық мәндер үшін 1-ден 9-ға дейнгі мәндер үшін өсу бойынша реттеу 1-9 деп, ал кему бойынша - 9-1 деп белгіленеді. ORDER BY өрнегін қолданатын SELECT операторының синтаксисі келесідей:
SELECT [ ALL | * | DISTINCT ТІК_ЖОЛ1, ТІК_ЖОЛ2]
FROM КЕСТЕ1 [ , КЕСТЕ2 ]
WHERE [ ШАРТ1 | ӨРНЕК1 ]
[ AND ШАРТ2 | ӨРНЕК2 ]
ORDER BY ТІК_ЖОЛ1|БҮТІН_МӘН [ ASC|DESC ];
Өсу бойынша реттеу келісім бойынша орнатылатын болғандықтан, ASC қызмет сөзін көрсетпеуге болады.
SQL-де кейбір қысқартулар ұсынылады. ORDER BY қызмет сөзінің тізімінде көрсетілген тік жолдың атын санмен алмастыруға болады. БҮТІН_МӘН тік жолдың атын алмастырады және SELECT қызмет сөзінен кейін орнатылған тік жолдардың ретіне сәйкес болады.
4.3 Агрегатты функцияларды қолдану
Агрегатты функциялар мәліметтерді қорытуға қолданылады. SQL Server бірнеше агрегатты функцияларды ұсынады:
- COUNT – сұраныстың шартын қанағаттандыратын жатық жолдар санын есептейді. Оператор синтаксисі:
COUNT [ (*) │(DISTINCT│ALL) ] (тік_жол_аты)
- SUM – тік жолдың барлық мәндерінің арифметикалық қосындысын есептейді:
SUM ( [ DISTINCT ] тік_жол_аты)
- AVG – барлық мәндердің орта арифметикалық мәнін есептейді:
AVG ( [ DISTINCT тік_жол_аты)
- MAX – таңдалынған мәндер арасындағы максималдысын табады:
MAX( [ DISTINCT ] тік_жол_аты)
- MIN - таңдалынған мәндер арасындағы минималдысын табады:
MIN ( [ DISTINCT ] тік_жол_аты)
SUM және AVG функциялары тек қана сандық өрістерге қолданылады. COUNT, MAX, MIN функцияларымен сандық және символдық өрістерін қолдануға болады. Символдық өрістеріне MAX, MIN функцияларын қолданғанда, олар мәндерді алфавит ретімен салыстырады. Агрегаттық функциялар өз жұмысында NULL мәнін есепке алмайды. COUNT функциясы басқа функциялардан ерекше. Ол тік жолдағы мәндер санын немесе кестедегі жатық жолдар санын есептейді.
WHERE шартындағы GROUP BY қызмет сөзі агрегатты функция қолданылатын мәндер жиынын беруге және сұраныс нәтижесін реттеуге қолданылады.
4.4 Зертханалық жұмысқа тапсырма
4.4.1. TradeCompany мәліметтер қорын таңдаңыз.
4.4.2 Аттары белгілі әріптен басталатын клиенттер тізімін табыңыз.
4.4.3 Фирма клиенттерінің санын есептеңіз.
4.4.4 Тауарлар аттарының санын есептеңіз.
4.4.5 Соңғы үш айда тіркелген шоттар тізімін алыңыз.
4.4.6 Шоттарының жалпы суммасы аталған суммадан артық болатын клиенттердің тізімін алыңыз.
4.4.7 Соңғы екі ай ішінде алынған тауарлар аттары мен клиенттер тізімін алыңыз.
4.4.8 Белгілі клиенттермен алынған тауарлардың жалпы суммасын есептеңіз.
4.4.9 Белгілі клиентке тіркелген счеттардың барлығының санын есептеңіз.
4.4.10 Сатушылардың кейбіреуінде телефон жоқ деп есептейік (мәліметтерді осыған сәйкес келтіріңіз). Телефондары бар сатушылар санын есептеңіз.
4.4.11 Барлық сатушылардың қаржыларының суммасын есептеңіз (әр сатушының қаржысы оның жасалған келісімдерінің белгілі пайызы деп есептейік).
4.4.12 Сатушының жалақысының орта мәнін есептеңіз.
4.4.15 Максималды жалақы мәнін табыңыз.
4.4.16 Минималды жалақы мәнін табыңыз.
4.5 Есеп беруге қойылатын талаптап
Жұмыс бойынша есеп беру агрегатты функцияларды қолдану бұйрықтарының листингтерінен және сұраныстардың нәтижелерінен тұрады.
4.6 Бақылау сұрақтары
4.6.1 Арнайы түрдегі қандай операторларды білесіз? Әр операцияның міндеті?
4.6.2 Мәліметтерді сұрыптау шарттарында қандай қатынас операциялары қолданылады?
4.6.3 Мәліметтерді сұрыптау шарттарында қандай логикалық операциялары қолданылады?
4.6.4 Агрегатты функция деген не?
4.6.5 COUNT функциясын қолданғанда мәліметтер типін есепке алу керек пе?
4.6.6 Мәліметтерді тік жол бойынша топтастыру үшін осы тік жолды SELECT қызмет сөздің тізімінде көрсету керек пе?
4.6.7 Кесте псевдонимдері не үшін қолданылады?
5 №5 зертханалық жұмыс. Елестетулер мен сақталынатын процедураларды жасау
Жұмыстың мақсаты: мәліметтерді қорғау үшін елестетулерді қолдану; сақталынатын процедураларды жасауды үйрену.
5.1 Мәліметтерді қорғау үшін елестетулерді қолдану
Елестету дегеніміз алдын ала анықталған сұраныс түрінде жасалған кестелердің комбинациясы болатын виртуалды кесте. Кесте мен елестетудің негізгі айырмашылығы келесіде болады: кесте мәліметтері өздерін сақтау үшін физикалық жадыны талап етеді, ал елестету тек қана нақты кестелердің мәліметтеріне сілтеме жасайды, сондықтан оның мәліметтері үшін орын керек емес.
Елестету анықтамасында WHERE қызмет сөздің өрнегінің шарттарына байланысты кестелердің белгілі тік және жатық жолдарына пайдаланушылардың қол жеткізуін шектеу үшін елестетуді қолдануға болады.
Елестету CREATE VIEW бұйрығымен жасалады.
а) Жалғыз кестенің мәліметтері үшін елестетуді жасау:
CREATE VIEW елестету_аты
SELECT * | тік_жол1 [ , тік_жол2]
FROM кесте_аты
[WHERE өрнек1 [ , өрнек2 ] ]
б) Бірнеше кестенің мәліметтері үшін елестетуді жасау:
CREATE VIEW елестету_аты
SELECT * | тік_жол1 [ , тік_жол2]
FROM кесте_аты1,кесте_аты2 [ ,кесте_аты3]
[WHERE өрнек1 [ , өрнек2 ] ] проверить!!!
в) Басқа елестету негізінде елестетуді жасау:
CREATE VIEW елестету2
SELECT *
FROM елестету1
5.2 Сақталынған процедураларды қолдану
Бағдарламаларда қолдану ыңғайлы болу үшін жасалынған SQL-дің операторлар жиынтығын сақталынған процедура деп атайды.
Сақталынған прроцедураларды қолданудың кейбір артықшылықтары:
- процедуралар операторлары мәліметтер қорында сақталынған;
- процедура операторлары тексерілген және қолдануға дайын түрде орнатылған;
- процедураларды қолданғанда нәтиже тезірек алынады;
- процедураларды сақтау мүмкіншілігі модульдік бағдарламалауды қолдануға мүмкіндік береді;
- сақталынған процедуралар басқа процедураларды шақыра алады;
- сақталынған процедураларды басқа бағдарламалардан шақыруға болады.
SQL Server –де процедуралар келесі операторлармен жасалады:
CREATE PROCEDURE процедура_аты
[ [ ( ] @параметр_аты
МӘЛІМЕТТЕР_ТИПІ [(ҰЗЫНДЫҒЫ) | (ДӘЛДІГІ) [, МАСШТАБ ])
[=DEFAULT][OUTPUT] ]
[, @параметр_аты
МӘЛІМЕТТЕР_ТИПІ [(ҰЗЫНДЫҒЫ) | (ДӘЛДІГІ) [, МАСШТАБ ])
[=DEFAULT][OUTPUT] ]
[WITH RECOMPILE]
AS SQL оператолары
Сақталынған процедураны келесі түрде қолданылады: EXECUTE [ @ = ] процедура_аты
[ [ @ параметр_аты =] мәні |
[ @ параметр_аты = ] @ айнымалы [ OUTPUT ] ]
[WITH RECOMPILE]
5.3 Зертханалық жұмысқа тапсырма
5.3.1 TradeCompany фирмасының мәліметтер қорына әртүрлі елестетулерді жасаңыз:
- тауардың тек қана бейнелеуімен бағасынан тұратын елестету;
- клиенттер аттарынан және олардың адрестерінен тұратын елестету;
- клиенттер аттарынан, шот нөмірлерінен, тауар бейнелеуінен және шот датасынан тұратын елестету.
5.3.2 Келесі әрекеттерді жасайтын сақталынған процедураларды жасаңыз:
- PRODUCTS_TBL кестесіне жаңа жазбаларды енгізу;
- бағалары белгілі мәннен артық болатын тауарлар тізімін табу;
- тауарлар бағаларының барлығын 12% көтеру;
- жалпы суммасы максималды болатын шоттарды табу;
- клиенттер және олардың телефондарының тізімін алу;
- тауарлар тізімі мен олардың бағаларын алу;
- барлық процедуралар жұмысын тексеріңіз.
5.4 Есеп беруге қойылатын талаптар
Жұмыс бойынша есеп беру қағазда орындалады және елестетулер мен процедураларды жасаудың және олардың жұмысының листингтерінен тұрады.
5.5 Бақылау сұрақтары
5.5.1 Елестету деген не?
5.5.2 Елестетуді жасағанда пайдаланған кесте жойылса не болады?
5.5.3 Елестетуді мәліметтерді қорғауға қалай қолдануға болады?
5.5.4 Елестетуді қалай жоюға болады?
5.5.5 Сақталынған процедура деген не?
5.5.6 Процедураларды қолданудың артықшылығы неде?
5.5.7 Сақталынған процедура басқа сақталынған процедураны шақыра ала ма?
5.5.8 Сақталынған процедура есептеулерді орындай ала ма?
5.5.9 Сақталынған процедура пайдаланушы терген мәндерді алдын ала орнатылған шарттармен салыстыра ала ма?
5.5.10 Сақталынған процедура қай жерде сақталады?
6 №6 зертханалық жұмыс. Триггерлерді қолдану
Жұмыстың мақсаты: мәліметтер қорының мәліметтерінің бүтіндігін қамтамасыздандыратын триггерлер механизмін игеру.
6.1 Триггер анықтамасы
Кестелерге мәліметтерді енгізгенде, өзгерткенде немесе жойғанда автоматты түрде жұмысқа қосылатын сақталынған процедуралар арнайы класы триггер (Trigger) деп аталады. Триггер мәліметтерді өзгерткенде жұмысқа қосылып, белгілі әрекеттерді орындайтын сақталынған процедураны жұмысқа қосады. Триггердің қосылуына себеп болатын пайдаланушының жасайтын әрекеттеріне сәйкес олар үш категорияға бөлінеді: өзгертудің триггерлері (UPDATE TRIGGER), енгізудің триггерлері (INSERT TRIGGER) және жоюдың триггерлері (DELETE TRIGGER). Бір тирггерде осы үш түрдің барлығы болуы мүмкін. Триггерлерді көп жағдайда қолдануға болады, бірақ көбінесе олар іскерлік регламентті іске асыруға қолданылады.
Триггерлерді тек қана орнатылған мәліметтер қорында емес, оларды сонымен бірге басқа мәліметтер қорларының және басқа серверлерінің де кестелерінің мәліметтерін модификациялауға пайдалануға болады.
Триггерді жасау операторының синтаксисі:
/*Триггердің ат жолы мен аты*/
CREATE TRIGGER Trigger_name
/*триггер міндеттелген кестенің аты*/
ON table_name
/*жұмысына қандай оқиғалар себеп болады*/
FOR INSERT, UPDATE, DELETE
/* қызмет сөз */
AS
/*Триггердің мәліметтерін анықтау*/
/* триггер денесінің басы*/
BEGIN
DECLARE @var_name type
/*var_name – айнымалы аты, @ - міндетті түрдегі символ,
type- айнымалы мәліметтерінің типі*/
/*айнымалыға кесте тік жолының мәнін меншіктеу*/
SELECT @var_name=Table_name.Column_name
/*қай кестеден тік жол таңдалынады; T,Q – псевдонимдер;
inserted – енгізілетін мәліметтердің белгілеуі*
/ FROM table_name T, inserted Q
/* бастапқы кестемен енгізілетін мәліметтердің кілттік өрістері салыстырылады */
WHERE T.key=Q.key
/*триггерді болдырмаудың шарты*/
IF условие
BEGIN
/* транзакцияны орындамау*/
ROLLBACK TRAN
/*қате туралы хабар*/
RAISERROR (‘Сообщение’)
END
END
/* триггердің соңы */
Триггерді жасаудың мысалы (6.2.1 – 6.2.3 орындағаннан кейін триггерді жасау және оның жұмысын тексеру дұрыс болады).
Мысалы, біздер сата алатын тауарлардың санын бақылап отыруымыз керек болсын. Бұл сан складта бар тауарлар санынан артық болмауы керек. Егер де менеджер байқамай осы шартты бұзса, жүйе оған хабар беруі керек. Триггер мәтіні келесідей болады:
CREATE TRIGGER CHECK_OSTATOK
ON ORDERS_TBL
FOR
INSERT
AS
DECLARE @ost int, @ost1 int
SELECT /*OSTATOK =
= PRODUCTS_TBL. QTY_VSEGO - ORDERS_TBL.QTY*/
@ost= ORDERS_TBL.QTY,
@ost1= PRODUCTS_TBL. OSTATOK
FROM ORDERS_TBL, PRODUCTS_TBL
WHERE PRODUCTS_TBL.PROD_ID= ORDERS_TBL. PROD_ID
IF @ost>@ost1
BEGIN
ROLLBACK TRAN
RAISERROR ('недостаточно товара на складе',16,3)
END
6.2 Зертханалық жұмысқа тапсырма
Барлық тапсырмалар Transact-SQL бұйрықтарымен орындалады (графикалық интерфейсті қолданбаңыз).
6.2.1 TradeCompany мәліметтер қорының құрылымына келесідей өзгертулер енгізіңіз:
- PRODUCTS_TBL кестесіне QTY_VSEGO өрісін қосыңыз;
- PRODUCTS_TBL кестесіне OSTATOK өрісін қосыңыз;
- ORDERS_TBL кестесіне поле SUMMA_К_OPLATE өрісін қосыңыз;
- CUSTOMER_TBL кестесіне CITY өрісін қосыңыз;
- ORDERS_TBL кестесіне OPLATA өрісін қосыңыз (логикалық өріс, мұнда клиентпен сатылым үшін төлеу өткізілгені немесе өткізілмегені белгіленеді);
6.2.2 UPDATE бұйрығын қолданып, PRODUCTS_TBL. QTY_ VSEGO, CUSTOMER_TBL. CITY, ORDERS_TBL.OPLATA өрістерін мәліметтермен толтырыңыз (соңғы өрісте кейбір клиенттер үшін NO мәнін орнатыңыз).
PRODUCTS_TBL кестенің QTY_ VSEGO өрісінде складтағы тауар сандары туралы мәліметтер сақталады, ORDERS_TBL кестенің QTY өрісінде – сатылған тауарлардың сандары, OSTATOK өрісінде осы екі өрістердің мәндерінің айырымы.
6.2.3 Сақталынған процедуралар механизмін қолданып, мәліметтер қорында келесідей өзгерістерді орнатыңыз:
- PRODUCTS_TBL.OSTATOK өрісінде – складтағы тауарлардың қалған саны (PRODUCTS_TBL. QTY_VSEGO - ORDERS_TBL.QTY);
- ORDERS_TBL.SUMMA_К_OPLATE өрісінде – сатып алынған тауарға жасалған төленімдер; ол тауар бағасының (PRODUCTS_TBL кестесі) тауар санына (ORDERS_TBL кестесі) көбейтіндісі болады;
6.2.4 Алдындағы 6.1 п. келтірілген триггерді жасаңыз; ORDERS_TBL кестесіне сатылымдар туралы жазбаларды енгізіңіз, триггердің жұмысын тексеру үшін бұл жазбаларда тауар саны алдын ала осы тауардың складтағы қалдықтарынан артық болсын; оның жұмысын тексеріңіз.
6.2.5 Келесі ситуацияларды бақылайтын триггерлерді жасаңыз:
- белгілі қаладағы клиенттерге қызмет жасамау;
- тек қана белгілі қаладағы клиенттерге қызмет жасау:
- тауарға төленім өткізілген болмаса, сатылым туралы жазбаны жоймау;
- бағалары белгілі мәннен артық тауарларды сатып алмау.
6.3 Есеп беруге қойылатын талаптар
Жұмыс бойынша есеп беру қағазда орындалады және келесілерден тұрады:
- триггерлер мәтіндерінің листингтері;
- жасалған триггерлердің жұмысының нәтижелері.
6.4 Бақылау сұрақтары
6.4.1 Триггерлерді қолданудың артықшылығы?
6.4.2 Триггерлер қай кезде орындалады - INSERT, UPDATE және DELETE бұйрықтарының алдында ма, соңында ма?
6.4.3 Триггерді өзгертуге бола ма?
6.4.4 MS SQL 2005 жүйесіне триггер мәтіні қалай орнатылады?
6.4.5 Триггер жұмысы қалай тексеріледі?
7 №7 зертханалық жұмыс. Пайдаланушының интерфейсін өңдеу
Жұмыстың мақсаты: Borland Builder C++ құралдары көмегімен клиенттік қолданбалыны жасаудың процедурасын үйрену.
7.1 ADO технологиясы көмегімен мәліметтерге қол жеткізу
Клиенттік қолданбалының мақсаты – пайдаланушыға мәліметтермен ыңғайлы түрде жұмыс істеуді қамтамасыздандыру. Мәліметтерге қол жеткізудің әртүрлі механизмдері бар. Соның ішіндегі – Microsoft корпорациясы өңдеген Active Data Objects (ADO) технологиясы. Бұл технология клиенттің мәліметтер қорының сервермен өзара байланысуын қамтитын клиент-серверлік қолданбалыларды жасауға негізделген.
ADO технологиясы Windows операциялық жүйесінің құрамындағы OLE DB жоғары деңгейлі интерфейстерінің жиынтығынан тұрады. OLE DB (Object Linking and Embedding Database – мәліметтер қорларының объекттерін байланыстыру және ендіру) интерфейсі – кезкелген типті мәліметтер көзіне қол жеткізудің универсалды технологиясы; қол жеткізу мәліметтерді өңдеу мен объект арасында мәліметтерді тасымалдау функциялары бар арнайы COM (Component Object Model – компоненттік объекттердің моделі) объекттерді қолданумен өткізіледі.
Зертханалық жұмыста қарастырылып отырған TradeCompany мәліметтер қорына интерфейсту өңдеу сұрақтары қарастырылады.
Есепті жеңілдеті үшін ORDERS_TBL кестесінің қарапайымдалынған құрылымын жасаңыз: бұл кестеде ORD_NUM өрісі бастапқы кілт болып табылады. (ORDERS_TBL өрісі кестеден жойылады). Кестені мәліметтермен толтырыңыз (TOTAL_COST өрісін толтырыңыз).
Осы интерфейс көмегімен төмендегі есептерді шешуге мүмкіндік беретін интерфейс өңделеді:
- мәліметтер қорындағы кестелердегі мәліметтерді қарап шығу;
- мәліметтер қорына әртүрлі сұраныстарды орындау;
- есеп берулерді дайындау.
Ескерту: жұмыс бейнелеуінде ешқандай бейнелер жоқ, себебі зертханалық жұмыстарға қосымша өңделген қолданбалының exe-файлы дайындалған, ол дискіде орнатылған. Жұмысты орындау кезде өңдеудің нәтижелерін қарап шығуға болады. Бұл файл зертханалық жұмыстарды орындайтын компьютерлік кластарының барлығында орнатылған.
7.2 ADO байланысын икемдеу
Қолданбалының мәліметтер қорымен байланыс сеансын қамтамасыздандыру үшін олар арасындағы байланысты орнатып, оның параметрлерін икемдеу керек. Осы мақсатқа ADOConnection компоненті негізделеген:
- Borland Builder C++ қолданбасында формаға ADO компоненттер жиынтығынан ADO Connection компонентін орнатыңыз. Байланыс параметрлері ConnectionString қасиетінде орнатылады;
- сол жақтағы Инспектор объектов терезесінде ADO Connection қасиеттерінен Connection String қасиетін таңдаңыз. Пайда болған терезеде Build батырмасын басыңыз; «Свойства связи с данными» терезесінің «Поставщик данных» бетінде SQL Native Client жолын таңдаңыз;
- «Подключение» бетінде: «Для входа в сервер» жолынан Учетные сведения Windows NT таңдалынады; келесі жолда ашылатын тізімнен бастапқы каталогты таңдаңыз. Байланысты тексеріңіз;
- Инспектор объектов» терезесінде Login Promt қасиеттің мәнін false деп орнатыңыз; сонда мәліметтер қорымен жұмыс жасаған кезде парольді енгізетін терезе пайда болмайды.
7.3 Мәліметтер қорының мәліметтеріне қол жеткізу
Мәліметтер қорларын қолданатын қолданбалыларда келесі түрдегі компоненттердің ең кемінде біреуі болады:
- мәліметтер қорымен тікелей байланысатын мәліметтер жиындары (DataSet); зертханалық жұмыста олар Table, Query, StoredProc компоненттер болып табылады;
- Dataset компоненттерімен мәліметтерді визуализациялау және оларды басқару компоненттері арасындағы ақпаратпен алмасуды орындайтын мәліметтер көзі (DataSource);
- мәліметтерді визуализациялау және оларды басқару DBGrid, DBEdit, DBText компоненттері (7.1 сурет).
7.1 Сурет – Компоненттердің өзара және мәліметтер қорымен байланысуы
DataSource компонентінің DataSet қасиеті TDataSet компоненттің атын анықтайды. Жобалау кезеңінде DataSet қасиетке мәнін Инспектора объектов көмегімен, ал орындау кезеңде – программа көмегімен меншіктеуге болады.
Мәліметтер қорының мәліметтерін көрсету үшін екі амалды қолдануға болады: АDO Table және ADOQuery.
Осы компоненттерді қолдануын қарастырайық:
а) жергілікті мәліметтер қорлары үшін әдетте TTable класының Table (ADOTable) компоненті қолданылады. Бұл компонент кестеге ең тез және қарапайым қол жеткізуді қамтамасыздандырады:
- 7.2 тарауда аталған формаға ADOTable1, DataSource1 компоненттерін орнатыңыз;
- компоненттердің қасиеттерін орнатыңыз (қасиеттер Инспектор объектов бөлімінде орнатылады):
|
Компонент |
Қасиеті |
Мәні |
|
DataSource1 |
DataSet |
ADOTable1 |
|
ADOTable1 |
Connection |
ADOConnection1 |
|
ADOTable1 |
TableName |
Имя таблицы |
- формаға DBGrid1 және DBNavigator1 компоненттерін орнатыңыз; олардың қасиеттері:
|
Компонент |
Қасиеті |
Мәні |
|
DBGrid1 |
DataSource |
DataSource1 |
|
DBNavigator1 |
DataSource |
DataSource1 |
|
DBNavigator1 |
ShowHint |
True |
- ADOTable1 компоненті үшін Active қасиетін true мәніне орнатыңыз;
- қолданбалыдан шығуды орнатуын ұмытпаңыз;
- қолданбалыны жұмысқа қосып, нәтижесін қарап шығыңыз;
- Columns Editor қасиеттер редакторы көмегімен кестенің бейнесін өзгертіңіз (мысалы, тік жол атауларын). Бұл редактор DBGrid компонентінің контексттік менюінен немесе Инспектор объектов бөліктің Columns қасиетінен таңдалынады. Мышканың оң жақ батырмасы көмегімен Add All Fields жолын таңдап, Title қасиеттерінен әр өрістің Caption қасиетінде өрістердің жаңа атауларын жазуға болады.
Навигатор көмегімен мәліметтер жиынының жазбалары бойынша көшуге болады. Егер де мышка көрсеткішін навигатордың бір батырмасына орнатып күте тұрсаңыз, түсініктемелер пайда болады (ShowHint қасиеттің True мәні);
б) көбінесе TTable мәліметтер жиындары орнына TQuery (ADOQuery) объекттері қолданылады, себебі TQuery негізіндегі SQL тілі өте тиімді болып табылады. TQuery компонентін қолданудың артықшылықтары келесідей факторларымен байланысты;
- мәліметтер қорын жобалау кезеңінде ескерілмеген байланыстармен кестелерді біріктіру мүмкіншілігі;
- мәліметтердің бір жиынында әртүрлі кестелердегі ақпараттарды топтастыру;
- күрделіліктері әртүрлі болатын фильтрлерді, жазбаларды реттеудің реттерін, соңғы сұрыптауға кіретін өрістер жиындарын, т.б. жеңіл орнату мүмкіншілігі;
- параметрлерді қолдану мүмкіншіліктері.
ADOQuery компонентін қолдану: ADOTable1 компонентін формадан жойып, оның орнына ADOQuery1 орнатыңыз; бұл компонент үшін қосымша қасиеттері:
|
Компонент |
Қасиеті |
Мәні |
|
ADOQuery1 |
Connection |
ADOConnection1 |
|
ADOQuery1 |
SQL |
select * from PRODUCTS_TBL |
|
ADOQuery1 |
Active |
true |
Query компонентінің негізгі қасиеті — SQL, оның типі TStrings болады. Бұл SQL сұраныстарынан тұратын жолдар тізімі. Жоғарыда SQL мәні мысал ретінде келтірілген.
Компоненттің соңғы қасиетін программа көмегімен орнатуға болады:
{ADOquery1->Open();
//мәліметтер қорымен байланысу
ADOquery1->Enabled=False
//активті мәліметтер қорымен байланысу
// болмайды
}
Қолданбалы орындалғанда SQL қасиеттері программа бойынша TStrings класының әдеттегі әдістерімен орнатылуы мүмкін: Clear() - тазарту, Add ()- жолды қосу, Open() – сұранысты қосу, т.с.
Бұл жерде әртүрлі сұраныстарға қолданылатын жалғыз Query компоненті пайдалынады: сұраныс мәтіні код мәтініне жазылады (алдын ала көне сұраныс жойылады), содан кейін ағынды сұраныс қосылады. Сұраныс орындалу нәтижесінде DBGrid компоненті PRODUCTS_TBL кестенің жазбаларын кесте түрде көрсетеді.
Формаға Button1 батырмасын орнатыңыз: баттырмаға басқан кезде SQL қасиетіндегі сұраныс орындалады; оқиғаны өңдейтін коды келесідей жазылады:
void __fastcall TForm1::Button1Click(TObject *Sender)
{
ADOQuery1->SQL->Clear();
//көне сұранысты жояды
ADOQuery1->SQL->Add("select * from PRODUCTS_TBL "); //сұраныс
ADOQuery1->Open(); //компонент қосылады
}
7.4 Параметрлер бойынша ізденісті орындау
7.4.1 Фильтрді қолдану
Ізденіс деп кейбір шарттарды қанағаттандыратын мәліметтерді көрсетуді түсінеді. Ізденісті TADOTable, TADOQuery компоненттер көмегімен іске асыруға болады.
Ізденісті орындау үшін TADOTable компонентте Filter қасиеті негізделген. Онда мәліметтерді сұрыптаудың шартын орнатуға болады. Фильтрді жұмысқа қосу үшін TADOTable қасиеттерінің мәндерін орнату керек:
|
Компонент |
Қасиеті |
Мәні |
|
ADOTable |
Filter |
Келесі түрдегі шарт мәтіні: Өріс [салыстыру операторы] ’мәні’ |
|
ADOTable |
Filtered |
true |
Мысалы, шарт мәтіні келесідей болуы мүмкін: PROD_DECS =’ватман’;
Фильтрді өзгерту үшін TЕdit енгізу жолын қолдануға болады: формаға қосымша TЕdit1 компонентін (оған тауар атауы енгізіледі) және «Фильтрация по названию товара» батырмаларын орнатыңыз. Ізденісті программалық орнатамыз:
void __fastcall TForm2::Button1Click(TObject *Sender)
{
ADOTable1->Filtered=False;
ADOTable1->Filter="PROD_DECS='"+Edit1->Text+"'";
ADOTable1->Active=True;
ADOTable1->Filtered=True;
}
Мәнін жалғыз тырнақшада (апострофтарда) көрсету керек, ал енгізу жолы қос тырнақшада орнатылады, сондықтан шарттың түрі '"+Edit1->Text+"' болады. Осындай құрылымды әрі қарай да қолданамыз.
7.4.2 Ізденіске SQL тілін қолдану
Фильтрді қолданудың кемшілігі – фильтрді өңдеудің жылдамдылығы төмен. Фильтр орындалуы үшін мәліметтердің барлығын серверден алып, содан кейін клиент жағында сұрыптау керек. Желі бойынша мәліметтердің үлкен көлемі тасымалданады, клиентке артық жүктеме пайда болады. Үлкен қорлармен SQL-сұраныстарды қолданған жөн.
SQL-сұранысты қолданған кезде клиент серверге шарттары бар мәтіндік сұранысты жібереді, сервер сұранысты тексеріп, клиентке шартты қанағаттандыратын мәліметтерді қайтарады.
Егер де сұранысты ADOQuery компоненттің SQL қасиетіне жазып қойсақ, сұраныс статикалық болады. Егер де программа жұмысы кезінде сұраныс мәтіні өзгеріп отырса, сұраныс динамикалық болады. Екінші жағдайда сұранысқа өзгертіліп отыратын айнымалыны енгізіп, сұраныстың өзін программалау кезінде орнату ыңғайлы болады.
Тауар атауы бойынша тауар туралы барлық ақпаратты алу керек болсын.
Жаңа форманы ашыңыз. Оған келесідей компоненттерді орнатыңыз: Label1 –мәтін орнатылады, Edit1 – тауар атауы енгізіледі; Label2, Label3 – жазбалар санын шығару үшін; компоненттер қасиеттері:
|
Компонент |
Қасиеті |
Мәні |
|
Label1 |
Caption |
Введите название товара |
|
Label2 |
Caption |
Видов данного товара |
Аталған тауарды іздеуге негізделген код:
void __fastcall TForm2::Button1Click(TObject *Sender)
{
ADOQuery1->SQL->Clear();
ADOQuery1->SQL->Add("select PROD_DECS, COST from PRODUCTS_TBL where PROD_DECS='"+Edit1->Text+"'");
ADOQuery1->Open();
Label3->Caption=IntToStr(ADOQuery1->RecordCount);
}
//---------------------------------------------------------------------------
Ескерту: жоғарыдағы кодта сұраныс мәтіні бір жолда жазылады деп есептеледі; егер де сұраныс мәтіні бірнеше жолдарда орнатылса, оның түрі келесідей болады:
ADOQuery1->SQL->
Add("select PROD_DECS, COST from PRODUCTS_TBL");
ADOQuery1->SQL->Add("where PROD_DECS='"+Edit1->Text+"'");
TADOTable және TADOQuery компоненттерінің RecordCount (кестедегі жазбалар саны) қасиеті бар.
Есепті күрделілейік – аталған датасында сатылған белгілі тауар туралы барлық мәліметтерді шығару керек: тауар атауын, тауар бағасын, шотта көрсетілген санын, датасын.
Формаға келесі компоненттерді орнатыңыз: Edit1– тауар атауын, Edit2 – сатылған датасын енгізу үшін. SQL тілін қолданғанда өңдеу коды келесідей болады:
void __fastcall TForm1::Button1Click(TObject *Sender)
{
ADOQuery1->SQL->Clear();
ADOQuery1->SQL->Add("Select PRODUCTS_TBL.PROD_DECS,");
ADOQuery1->SQL->
Add("PRODUCTS_TBL.COST, ORDERS_TBL.QTY,");
ADOQuery1->SQL->Add("ORDERS_TBL.ORD_DATE");
ADOQuery1->SQL->Add(from PRODUCTS_TBL,ORDERS_TBL");
ADOQuery1->SQL->Add("where PRODUCTS_TBL.PROD_DECS=");
ADOQuery1->SQL->Add("'"+Edit1->Text+"' and");
ADOQuery1->SQL->
Add("ORDERS_TBL.ORD_DATE='"+Edit2->Text+"' and");
ADOQuery1->SQL->
Add("ORDERS_TBL.PROD_ID=PRODUCTS_TBL.PROD_ID");
ADOQuery1->Open();
}
Егер де сұраныста select операторы қолданылса, Open() әдісі пайдалынады..
Егер де сұраныста жазбалар жойылса немесе тік жол құрамдары өзгерсе (сұраныста INSERT, UPDATE, DELETE және/немесе CREATE TABLE операторлары болса) ADOQuery компонентінің ExecSQL әдісін қолдану керек. Басқа сөзбен айтқанда, егер де сұраныс мәліметтерді қайтарса, тек қана Open() компонентін қосуға болады, ал егер де өзгертсе – сұранысты ExecSQL көмегімен орындау керек. Мысалы, мәліметтерді өзгерту, кестедегі жазбаны жою, жаңа жазбаны қосу үшін келесідей кодтарды орнатуға болады:
void __fastcall TForm1::Button1Click(TObject *Sender)
{
ADOQuery1->SQL->Clear();
ADOQuery1->SQL->
Add("Update PRODUCTS_TBL set PROD_DECS='ручка1'
where PROD_DECS='ручка'");
ADOQuery1->ExecSQL();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button2Click(TObject *Sender)
{
ADOQuery1->SQL->Clear();
ADOQuery1->SQL->Text="Delete PRODUCTS_TBL
where PROD_ID='"+Edit1->Text+"'";
ADOQuery1->ExecSQL();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button3Click(TObject *Sender)
{
ADOQuery1->SQL->Clear();
ADOQuery1->SQL->Text="Insert into PRODUCTS_TBL
VALUES( 24,'коврик для мыши',3000)";
ADOQuery1->ExecSQL();
}
7.5.Кестелерді толтыру
Кесте жазбаларын енгізуге және жөндеуге негізделген форманы жасауын қарастырайық. Әуелі PRODUCTS_TBL кестесін толтыруды қарастырамыз. Жаңа форманы ашып, оған келесідей компоненттерді орнатыңыз – TDBEdit1, TDBEdit2, TDBEdit, ADO Table1, DataSource1, DBNavigator1. Компоненттер қасиеттері төменде көрсетілген:
|
Компонент |
Қасиеті |
Мәні |
|
TDBEdit |
DataSource |
DataSource1 |
|
TDBEdit |
DataField |
Сәйкес өріс таңдалынады |
|
DataSource |
DataSet |
ADO Table1 |
|
ADO Table1 |
Connection |
ADO Connection1 |
|
ADO Table1 |
TableName |
Тізімнен таңдалынады |
|
ADO Table1 |
Active |
True |
|
DBNavigator1 |
DataSource |
DataSource1 |
Бұл компоненттер мәліметтер қорындағы аталған өрістерді автоматты түрде жөндейді.
Қолданбалыны жұмысқа қосыңыз. Навигаторда Insert record батырмасын басыңыз. DBEdit компонентіне мәліметтерді енгізіп, Post edit батырмасын басыңыз. Мәліметтер PRODUCTS_TBL кестесіне енгізіледі.
Күрделілеу - ORDERS_TBL кестесіне мәліметтерді енгізу есебін қарастырайық. Келесі ORD_NUM, CUST_ID, PROD_ID, ORD_DATE, QTY (сәйкесінше шот нөмірі, клиент коды, тауар коды, шот датасы және тауар саны) өрістерді және есептелінетін TOTAL_COST толтыруға негізделген жаңа форманы жасаймыз. Толтыру үшін DBEdit компоненттері қолданылады DataControl беті).
Формамен жұмыс істегенде формада орналасатын «Описание товара», «Цена товара», «Стоимость товара» батырмаларды басқан кезде формада сәйкесінші өрістердің мәндері пайда болуы керек. Ол мәндер ORDERS_TBL кестеге енгізілген тауар кодына байланысты анықталады. Сондықтан, тауардың бағасы мен бейнелуін анықтау үшін сұранысты құрастыру қажет (енгізілген тауар коды бойынша).
Сонымен, формаға қажетті компоненттерді орнатамыз, олардың тізімі және қасиеттері төмендегі кестеде келтірілген:
|
Компонент |
Қасиеті |
Мәні |
|
TDBEdit |
DataSource |
DataSource1 |
|
TDBEdit |
DataField |
Сәйкес өрісті таңдау |
|
DataSource1 |
DataSet |
ADO Table1 |
|
DataSource2 |
DataSet |
ADO Query1 |
|
DataSource3 |
DataSet |
ADO Query2 |
|
ADO Table1 |
Connection |
ADO Connection1 |
|
ADO Table1 |
TableName |
Тізімнен таңдау |
|
ADO Table1 |
Active |
True |
|
DBNavigator1 |
DataSource |
DataSource1 |
|
ADO Query1 |
Connection |
ADO Connection1 |
|
ADO Query2 |
Connection |
ADO Connection1 |
|
DBText1 |
DataSource |
DataSource2 |
|
DBText2 |
DataSource |
DataSource3 |
DBText1, DBText2 компоненттері PROD_DECS және COST өрістерімен программа бойынша байланысады, мысалы:
DBText1->DataField="PROD_DECS";.
Төменде әртүрлі оқиғалар үшін негізделген программа фрагменттері келтірілген:
- тауар атын анықтау
void __fastcall TForm1::Button1Click(TObject *Sender)
{
AnsiString t;
ADOQuery1->SQL->Clear();
t=DBEdit3->Text;
ADOQuery1->SQL->Add("select PROD_DECS
FROM PRODUCTS_TBL WHERE PROD_ID='"+t+"'");
DBText1->DataField="PROD_DECS";
ADOQuery1->Open();
}
- тауар бағасын анықтау
//---------------------------------------------------------------------------
void __fastcall TForm1::Button2Click(TObject *Sender)
{AnsiString t;
ADOQuery2->SQL->Clear();
t=DBEdit3->Text;
ADOQuery2->SQL->Add("select COST from PRODUCTS_
WHERE PROD_ID='"+t+"'");
DBText2->DataField="COST";
ADOQuery2->Open();
}
- тауар бағасын есептеу және TOTAL_COST өрісін толтыру
//---------------------------------------------------------------------------
void __fastcall TForm1::Button3Click(TObject *Sender)
{
DBText3->Caption=
IntToStr(StrToInt(DBText2->Caption)*StrToInt(DBEdit5->Text));
DBEdit6->Text= DBText3->Caption;
//DBEdit6->Text=
IntToStr(StrToInt(DBText2->Caption)*StrToInt(DBEdit5->Text));
}
Кейбір жағдайларда қолданбалыда ADOQuery компонентімен байланысқан кесте өрістерінің аттар тізімін алу керек. Оны GetFieldNames әдісі көмегімен орындауға болады. Бұл әдіс өрістер аттарының тізімін оған аргумент ретінде жіберілетін кезкелген TStrings типті айнымалыға меншіктейді.
Мысалы,
ADOQuery1->GetFieldNames(ComboBox1->Items);
операторы ComboBox1 тізіміне ADOQuery1 компонентімен байланысқан кестенің өрістер аттарын орнатады.
7.6 Мәліметтер модулін қолдану
Кестелер саны көп болатын немесе сұраныстардың көп санын пайдаланатын қолданбалыларды өңдегенде, формалар интерфейсінде мәліметтер жиындары мен мәліметтер көздеріне сәйкес өте көп визуалды емес компоненттер орнатылуы мүмкін. Borland Builder C++ (және Delphi) мәліметтер қорына қол жеткізудің компоненттерін сақтауға ыңғайлы болатын арнайы Data Module (мәліметтер модулі) терезін жасауға мүмкіндік береді.
Мәліметтер модулі – программа орындалғанда көрінбейтін форманың арнайы типі. Мәліметтер модулі мәліметтер қорымен жұмыс жасауға бағытталған компоненттерінің өзара байланысуын орталықтандырып басқаруға мүмкіндік береді.
Мәліметтер модулін жобаға қосу үшін File|New|Data Module бұйрықтарды орындау керек. Содан кейін формада мәліметтер қорымен жұмыс жасауға негізделген визуалды емес ADOConnection, DataSource, ADOTable, ADOQuery компоненттерін орнату керек.
Форманы мәліметтер модулімен байланыстыру үшін форманың сәйкес атау жолының файлына келесі директиваны қосу керек:
#include<”Unit#.h”>.
Мәліметтер модуліндегі объектке қатысу үшін құрамды <название модуля данных>-><название объекта атау қолданылады. Мысалы:
DataModule1->ADOTable1->Open();
DataModule1->ADOQuery->SQL-> Text="Delete tovar
where PROD_ID='"+Edit1->Text+"'";
DataModule1->ADOQuery1->ExecSQL();
7.7 Есеп берулерді жасау
Мәліметтер қорларымен жұмыс істегенде әдетте олар негізінде қағазға басып шығару үшін құжаттар дайындалады. Зертханалық жұмыста Borland Builder C++ құрамындағы есеп берулерді жасайтын Quick Reports генераторы қарастырылады.
Borland Builder C++ орнатқанда есеп беру QuickReport генераторы бірдей орнатылмайды, бірақ оны өздігімен орнатуға болады. Ол үшін Project | Options менюды таңдап, Packages бетінде Add батырмасын басу керек. Содан кейін Builder орнатылған Bin папкасынан dcltqr60.bpl файлды тауып ашыңыз.
Quick Reports-тің барлық компоненттері компоненттер тізімінің Qreport бетінде орнатылған. Бас компоненті – TQuickRep. Ол барлық есеп берулердің негізі болып табылады. Осы компоненттің Bands қасиеті бірнеше бөлімдерден тұрады. Оларда болашақ құжаттың мазмұнын орнатуға болады:
- HasTitle – бұл бөлікте есеп берудің атауы орнатылады;
- HasColumnHeader – тік жолдар аттары. Егер де есеп беруде кестелер болса, тік жолдар аттары орнатылатын жатық жол құжаттың осы бөлігінде анықталады. Егер де кесте керек болса, осы қасиет мәні true болады;
- HasDetail – егер де есеп беруде кестелер болса, жатық жолдар түрі осы бөлікте анықталады;
- HasPageFooter – бұл бөлікте төменгі колонтитул жасалады;
- DataSet қасиетінде есеп беру мәліметтерді алатын кесте көрсетіледі.
Компоненттер тізімінің QReport бетінде осы бөліктерде орнатылатын компоненттер бар: QRLabel - жазу (бұл компонент тек қана қажетті мәліметтерді көрсетеді); QRDBText – мәліметтер (тек қана мәліметтер қорының өрісінің мәнін көрсетуге негізделген); QRSysData – жүйелік ақпарат (TLabel сияқты, бірақ жүйелік ақпаратты көрсетеді: датаны, уақытты, бет нөмерін, т.с.); QRMemo – жолдар жиыны (TMemo сияқты, мәліметтер қорындағы Memo-мәліметтерді көрсетеді).
Жасалған есеп беруді алдын ала қарап шығу үшін QuickRep1->Preview() әдісі қолданылады.
Есеп беруді жасауын қарастырайық:
а) қағазға тауарлар тізімін шығарайық. Есеп беруде датасы, басып шығару уақыты, құжат аты – «Список товаров» болуы керек.
Формаға DataSource1, ADOTable1, QuickRep1 компоненттерін орнатамыз.
DataSource, ADOTable компоненттерін икемдейміз.
QuickRep1 компонентінің DataSet қасиеті үшін ADOTable1 мәнін орнатамыз. Bands қасиеттерін ашып, HasTitle қасиетінде true мәнін орнатамыз.
Пайда болған жолда QReport бетінен QRLabel компонентін орнатып, Caption қасиетіне «Список товаров» мәтінін енгіземіз. Сонымен бірге ағынды дата мен уақытты көрсету үшін QRSysData1 компонентін орнату керек. HasColumnHeader қасиетте true мәнін орнатыңыз. Сонда есеп беруде кесте өрістерінің аттары көрсетіледі. Пайда болған жолға QRLabel үш компонентін орнатыңыз. Ат жолында Код товара, Описание товара, Цена товара мәтіндерді жазыңыз.
HasDetail қасиетінің мәнін true деп орнатыңыз. Пайда болған жолға төмендегідей қасиеттері бар үш QRDBText компонентін енгіңіз:
|
Компонент |
Қасиеті |
Мәні |
|
QRDBText |
DataSet |
ADOTable1 |
|
QRDBText |
DataField |
указать соответствующие поля |
|
QRSysData1 |
Data |
qrsDateTime |
Формаға батырманы орнатыңыз. Осы батырманы басқанда алдын ала көріп шығу терезесі көрсетілуі керек. Барлық икемделулері Инспектор объектов бөлігінде жасалады. Келесі программа фрагментінің коды PRODUCTS_TBL кестедегі тауарлар тізімін шығарады:
/---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
QuickRep1->Preview();
}
б) алдында қарастырған сұраныс бойынша есеп беруді жасайық: берілген датасында сатылған аталған тауар бойынша мәліметтерді шығару. Есеп беруде келесідей мәліметтер шығарылуы керек: тауар аты, тауар бағасы, тауар саны, шот датасы. Есеп берудің бас жолында төмендегі мәтін орнатылады:
Данные по наименованию [название товара], купленному [дата счета].
Берілген датасында сатылған аталған тауарды іздеудің формасын қолданамыз. QuickRep компонентін орнатамыз, DateSet қасиеті - ADOQuery1.
Bands қасиеттерін икемдейміз:
- есеп берудің ат жолына QRLabel компоненттерін орнатыңыз; тауар аты мен датасы өзгеретін параметрлер екенін есепке алыңыз, олар Edit1 және Edit2 компоненттерінде анықталады;
- HasColumnHeader жолында өрістер аттарын көрсетіңіз;
- HasDetail жолында QRDBText компоненттерін орнатыңыз; DateSet қасиетінің мәні - ADOQuery1. .
Бұл мысалда DataField қасиетінің мәні программамен анықталады:
QRDBText1->DataField="+имя поля+";
Аталған сұраныс бойынша есеп беру келесі программа бойынша өңделеді:
void __fastcall TForm1::Button1Click(TObject *Sender)
{
QRLabel2->Caption=Edit1->Text;
QRLabel4->Caption=Edit2->Text;
ADOQuery1->SQL->Clear();
ADOQuery1->SQL->Text="Select PRODUCTS_TBL.PROD_DECS, PRODUCTS_TBL.COST, ORDERS_TBL.QTY, ORDERS_TBL.ORD_DATE, ORDERS_TBL.TOTAL_COST
from PRODUCTS_TBL,ORDERS_TBL
where PRODUCTS_TBL.PROD_DECS='"+Edit1->Text+"' and ORDERS_TBL.ORD_DATE='"+Edit2->Text+"' and ORDERS_TBL.PROD_ID=PRODUCTS_TBL.PROD_ID";
ADOQuery1->Open();
QRDBText1->DataField="COST";
QRDBText2->DataField="QTY";
QRDBText3->DataField="TOTAL_COST";
QuickRep1->Preview();
}
7.8 Зертханалық жұмысқа тапсырма
Қол жеткізудің механизмдерін пайдаланып өзіңіздің мәліметтер қорыңызға клиенттік қолданбалы программаны өңдеңіз.
7.9 Есеп беруге қойылатын талаптар
Зертханалық жұмыс бойынша есеп беру келесілерден тұрады:
- өңделген қолданбалы;
- модульдердің түсініктемелері бар листингтері;
Студент өңделген қодһлданбалының жұмысын көрсетеді.
7.10 Бақылау сұрақтары
7.10.1 ADO технологиясы неге бағытталған?
7.10.2 Мәліметтер қоры мен қолданбалы арасында байланысты орнататын компонентті атаңыз.
7.10.3 Мәліметтерді көрсетуге негізделген компонентті атаңыз.
7.10.4 Datasource, ADOTable, ADOQuery компоненттердің әдістері мен
қасиеттерінің міндеттерін түсіндіріңіз.
7.10.5 Мәліметтерді фильтрлеуде қолданылатын ADOTable компонентінің қасиеттерін атаңыз.
7.10.6 RecordCount қасиеті нені көрсетеді және қандай кластың компоненттерінің қасиеті болып табылады?
7.10.7 ExecSQL(), Open() әдістері қандай жағдайда қолданылады?
7.10.8 ADOQuery-мен байланысқан кесте өрістерінің аттар тізімін қандай әдіс көмегімен алуға болады?
7.10.9 Мәліметтер модулін қолдануды түсіндіріңіз.
7.10.10 Мәліметтер модулінде орналасқан обектті қалай қолдануға болады?
7.10.11 Bands компонентінің міндетін түсіндіріңіз.
7.10.12 QRSysData компоненті нені көрсетеді?
7.10.13 Жасалған есеп беруді алдын ала қарап шығуға қолданатын компонентті атаңыз.
8 №8 зертханалық жұмыс. MS SQL Server 2005 ортасында мәліметтерді шифрлеу
8.1 Жалпы мағлұматтар
Мәліметтер қорларын өңдегенде сақталынатын мәліметтердің қауіпсіздігін қамтамасыздандыру міндетті түрдегі қадам болып табылады. Кешенді қорғау құралдары қауіпсіздік скрипттері (триггерлер мен елестетулер) мен пайдаланушыларды бөлу құралдарын ұйымдастыру болып табылады. Пайдаланушылардың құқықтарын бөлуді иілген икемделу болғандықтан, мәліметтерге әртүрлі қол жеткізу деңгейлері бар әртекті пайдаланушылар жасалады. Өкіліктерді бөлу процедурасын ролдерді жасау жеңілдетеді. Рольдер кейбір шартты қорды және оларға рұқсаттарды жасауға мүмкіндік береді. Мәліметтер қорының әр пайдаланушысы – мәліметтер қорының серверін қорғау жүйесінің белгілі деңгейі. Пайдаланушылар объектілерін және олар үшін ролдерді икемдеудің кең мүмкіншілектеріне байланысты қауіпсіздік жүйесін жасауға кететін уақыт шығындары көп емес.
Алдыңғы версияларына қарағанда MS SQL Server 2005 тек қана өзінің серверлерін емес сонымен бірге одан да тыс мәліметтердің қорғалуын қамтамасыздандырады. Мұны қорғалмаған канал бойынша тасымалданатын мәліметтерді қорғайтын шифрлеу жүйесі орындайды. Шифрлеу алгоритмдері қарапайым да, әр қадамда шифрлеу кілттері бөлек генерациялайтындай (DES, TRIPPLE DES, RSA алгоритмдері) көп қадамды және кешендік те болады. Қаскүнемнің физикалық қол жеткізе алған кездерде MS SQL Server серверінде барлық шифрленген ақпарат оқылмай қалады.
SQL Server 2005 жүйесінде мәліметтерді шифрлеуге төрт әдіс қолданылады:
- сертификаттар көмегімен шифрлеу. Сертификат мәліметтер қорының объекті ретінде жасалған болу керек (в SQL Server Management Studio-да бар болатын сертификаттарды Databases/ Имя_базы_данных/Security/Certificates контейнерінде қарауға болады);
- асимметриялық кілттер көмегімен шифрлеу. Мұндағы қолданылатын алгоритм сертификаттарды қолданғандағы алгоритмдей. Асимметриялық кілттің сертификаттан ерекшелігі: асимметриялық кілтте кімге берілген, қандай мақсатымен, қай уақытқа дейін нақты болатыны туралы ақпарат орнатылатын қосымша өрістері жоқ. Бар болатын асимметриялық кілттерді Asymmetric Keys контейнерінен қарап шығуға болады, ол Certificates контейнері орнатылған жерде орнатылады;
- симметриялық кілттер көмегімен шифрлеу. Асимметриялық кілттерге қарағанда алгоритмдер жылдамдықтары тездеу болады. Симметриялық кілттердің өздері мәліметтер қорларының объекттері ретінде жасалып, сертификатпен, басқа симметриялық кілтпен, асимметриялық кілтпен немесе парольмен қорғалуы мүмкін. Оларды қарап шығу үшін Symmetric Keys контейнері қолданылады;
- парольдер көмегімен қарапайым шифрлеу.
Шифрлеу мүмкіншіліктерін Education мәліметтер қорының (зертханалық жұмыстардың оқыту программасында қарастырылған) Num_cards кестесінің мәліметтерін қорғау мысалында қарастырамыз. Бұл кестеде студенттің идентификаторы ID және төлеу карточкасының нөмірі сақталады (стипендяны алу үшін). Шифрлеуді карточка нөмеріне қолданамыз.
8.2 MS SQL Server 2005 ортасында мәліметтерді шифрлеу процедуралары
8.2.1 Кілттерді қорғау үшін сертификаттармен шифрлеу
Сертификатты жасау CREATE CERTIFICATE бұйрығы көмегімен орындалады. Бұл бұйрықтың ең қарапайым түрі келесідей
CREATE CERTIFICATE Cert1
ENCRYPTION BY PASSWORD = '11'
WITH SUBJECT = 'Проверка шифрования',
START_DATE = '02/06/2009'
Назар аударыңыз: сертификатты жасау үшін ешқандай сертификациялау орталығы керек емес, барлық қажетті құралдар SQL Server жүйесіне ендірілген.
ENCRYPTION BY PASSWORD параметрі сертификатпен қорғалған мәліметтердің шифрын ашуға қажетті парольді анықтайды (мәліметтерді шифрлеуге ол керек емес). Егер де бұл параметрді орнатпасаңыз, жасалынып отырған сертификат автоматты түрде мәліметтер қорының бас кілтімен (Database Master Key) қорғалады. Автоматты түрде бұл кілт жасалмайды. Онымен жұмыс істеу үшін біріншіден оны жасап алу керек
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'P@ssw0rd';
Парольден басқа мәліметтер қорының бас кілті автоматты түрде қызметтің бас кілтімен (Service Master Key) қорғалады. Бұл кілт SQL Server 2005 орнатылған кезде автоматты түрде генерацияланады.
Мәліметтер қорының бас кілтін қолданғанда ұқыпты болыңыз: егер де серверді қайтадан орнатсақ (сондықтан, әрине, қызметтің бас кілті өзгереді), шифрленген мәліметтер жоғалып қалуы мүмкін. Бұдан қорғалу үшін master мәліметтер қорын жиі көшірмелеп отыру керек немесе қызметтің бас кілтін BACKUP SERVICE MASTER KEY бұйрығы көмегімен файлға көшіріп отыру керек.
CREATE CERTIFICATE бұйрығынығ міндетті түрдегі SUBJECT параметрі сертификатты берудің мақсатын анықтайды.
Сертификат жасалғаннан кейін оны мәліметтерді шифрлеуге қолдануға болады. Ол үшін арнайы EncryptByCert функциясы қолданылады:
INSERT INTO Num_cards
VALUES (1, EncryptByCert(Cert_ID('Cert1'), N'111001') )
INSERT INTO Num_cards
VALUES (2, EncryptByCert(Cert_ID('Cert1'), N'111002') )
INSERT INTO Num_cards
VALUES (3, EncryptByCert(Cert_ID('Cert1'), N'111003') )
Шифрды ашу үшін DecryptByCert бұйрық қолданылады. Мәліметтердің шифрын ашу үшін қарапайым SELECT сұранысы қолданылады:
SELECT CONVERT(nvarchar(50),
DECRYPTBYCERT(Cert_ID('Cert1'),Cred_ID, N'11') )
FROM Num_cards
8.2.2 Ассиметриялық кілтпен шифрлеу
Біріншіден ассиметриялық кілтті жасаймыз
CREATE ASYMMETRIC KEY ASymKey1
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = '11'
Назар аударыңыз: бұл жерде парольден басқа жасалатын кілттің ұзындығын көрсету керек. Біздерде үш нұсқа бар: 512, 1024 және 2048 бит.
Содан кейін жасалған кілт көмегімен мәліметтерді шифрлеуге болады
INSERT INTO Num_cards
VALUES
(1, EncryptByAsymKey(AsymKey_ID('ASymKey1'), N'111001'))
INSERT INTO Num_cards
VALUES
(2, EncryptByAsymKey(AsymKey_ID('ASymKey1'), N'111002'))
INSERT INTO Num_cards
VALUES
(3, EncryptByAsymKey(AsymKey_ID('ASymKey1'), N'111003'))
Кестеге бірдей асимметриялы ASymKey1 кілтпен үш жазба енгізілді. Нәтижесінде кестегі мәліметтер оқылмайтын символдар жиыны болып көрсетіледі.
Шифрын ашу үшін DecryptByAsymKey функциясын қолданамыз
SELECT Convert(nvarchar(50), DecryptByAsymKey(AsymKey_ID('ASymKey1'),Cred_ID, N'11'))
FROM Num_cards
8.2.3 Симметриялық кілтпен шифрлеу
Асимметриялық кілттерге қарағанда симметриялық кілттерді қолданғанда шифрлеу тезірек орындалады, сондықтан мәліметтеродің үлкен көлемдері үшін соларды қолданған жөн. Қолдануы ұқсас өткізіледі. Бірақ кішкене айырмашылықтары бар. Біріншіден, симметриялық кілтті жасағанда оны тек қана парольмен емес, басқа симметриялық кілтпен, асимметрмялық кілтпен және сертификатпен қорғауға болады. Екіншіден симмметриялыө кілтті жасағанда SQL Server 2005 сүйемелдейтін сегіз шифрлеу алгоритмдерінің (DES, TRIPLE_DES, RC2, RC4, DESX, AES_128, AES_192, AES_256) біреуін орнатуға болады. Симметриялық кілтті жасау келесідей жолмен орындалады
CREATE SYMMETRIC KEY SymKey1
WITH ALGORITHM = DES
ENCRYPTION BY PASSWORD = '11'
Кілтті қолданар алдында (мәліметтерді шифрлеуге немесе шифрын ашуға) оны міндетті түрде ашу керек. Бұл әректті пайдаланушы сеансы кезінде бір рет орындауға болады
OPEN SYMMETRIC KEY SymKey1 DECRYPTION
BY PASSWORD = '11'
Жасалған кілтті мәліметтерді шифрлеуге қолданамыз
INSERT INTO Num_cards
VALUES(1, EncryptByKey(Key_GUID('SymKey1'), CONVERT(nvarchar(50),'111001') ))
INSERT INTO Num_cards
VALUES(2, EncryptByKey(Key_GUID('SymKey1'), CONVERT(nvarchar(50),'111002') ))
INSERT into Num_cards
VALUES(3, EncryptByKey(Key_GUID('SymKey1'),
CONVERT(nvarchar(50),'111003') ))
Назар аударыңыз: шифрды ашқан кезде DecryptByKey функциясына симметриялық кілттің атын және парольді беру қажеттілігі жоқ. Автоматты түрде ашық кілттің мәліметтері open бұйрығы көмегімен орнына қойылады. Шифрды ашу үшін келесі бұйрықты жұмысқа қосамыз
SELECT CONVERT(nvarchar(50), DecryptByKey(Cred_ID))
FROM Num_cards
Шифрленген мәліметтерді типтері int, char болатын тік жолдарда сақтауға болмайды, сондықтан convert бұйрығы қолданылады.
8.2.4 Парольмен шифрлеу
SQL Server 2005 жүйесінде мәліметтерді қарапайым жолмен парольмен шифрлеуге болады. Ол үшін EncryptByPassPhrase функциясы қолданылады.
Қарапайым жағдайда бұл функция тек қана парольмен шифрленетін мәліметтерді қабылдайды
INSERT INTO Num_cards
VALUES(4, EncryptByPassPhrase('Password', N'111004'))
Шифрды ашу DecryptByPassphrase функция көмегімен орындалады
SELECT
CONVERT(nvarchar(50), DecryptByPassPhrase('Password', Cred_Id))
FROM Num_cards
8.3 Зертханалық жұмысты орындауға тапсырма
8.3.1 Өзіндік есептеу жұмыста өңдеген мәліметтер қорын қолданыңыз. Осы қорда әртүрлі типті өрістері (символдық, сандық, дата/уақыт, т.б.) бар кестені таңдаңыз.
8.3.2 Өрістердің барлық типтеріне барлық төрт әдіс бойынша шифрлеу процедураларын қолданыңыз.
8.3.3 Шифрлеудің әр түрі үшін оның артықшылықтары мен кемшіліктерін анықтаңыз.
8.4 Есеп беруге қойылатын талаптар
Зертханалық жұмыс бойынша есеп беру келесілерден тұрады:
- қолданылатын мәліметтер қорының кестесінің құрылымы;
- әр түрлі әдіс бойынша шифрлеу бұйрықтарының мәтіндері, шифрленген ақпаратқа қол жеткізу нәтижелерінің листингтері;
- әр түрлі типті өрістерді шифрлеудің әдістерінің салыстырмалы талқылауы.
8.5 Тапсырмалар нұсқалары
Студент өзінің мәліметтер қорын және осы мәліметтер қорында таңдалған құрылымы бар кестені қолданады; осы кестенің мәліметтерін қолданып тапсырманы орындайды.
8.6 Бақылау сұрақтары
8.6.1 Мәліметтер қорларында шифрлеу процедурасы не ге керек?
8.6.2 Мәліметтер қорының мәліметтерін шифрлеудің қандай әдістерін білесіз?
8.6.3 Сертификат деген не?
8.6.4 Шифрлеу қолданылған кестенің мәліметтері қалай көрсетіледі?
8.6.5 Кесте өрісінің типіне қарай шифрлеуді қолдану айырыла ма?
Мазмұны
|
|
б |
|
Кіріспе |
|
|
1 №1 зертханалық жұмыс. Мәліметтер қорын жасау және оның құрылымын анықтау |
|
|
2 №2 зертханалық жұмыс Мәліметтермен әрекеттесу тілі |
|
|
3 №3 зертханалық жұмыс Мәліметтерді сұрыптау (DQL) |
|
|
4 №4 зертханалық жұмыс Агрегатты функциялар мен арнайы операторларды сұрыптау шарттарында қолдану |
|
|
5 №5 зертханалық жұмыс. Елестетулер мен сақталынатын процедураларды жасау |
|
|
6 №6 зертханалық жұмыс. Триггерлерді қолдану |
|
|
7 №7 зертханалық жұмыс. Пайдаланушының интерфейсін өңдеу |
|
8 №8 зертханалық жұмыс. MS SQL Server 2005 ортасында мәліметтерді шифрлеу |
|
Әдебиеттер тізімі |
|