Алматы энергетика және байланыс институты

Инженерлік кибернетика кафедрасы

МӘЛІМЕТТЕР ҚОРЛАРЫН ЖОБАЛАУ

Лабораториялық жұмыстарды орындауға әдістемелік нұсқаулар

(барлыќ мамандық студенттері үшін)

Алматы 2004

ҚҰРАСТЫРУШЫЛАР: Л.К. Ибраева, Е.Г. Сатимова. Мєліметтер қорларын жобалау. Лабораториялық жұмыстарды орындауға єдістемелік нұсқаулар (барлық мамандық студенттері үшін). – Алматы: АЭжБИ, 2004 ж. – 43 б.

Қазіргі замандағы мәліметтерді өңдеу технологиялар клиент-серверлік архитектурасын қолданады. Бұл технология бойынша “клиенттік” машинадан жіберілетін сұраныстар мәліметтер қорының арнайы серверлерінде өңделіп, ”клиентке” тек қана сұранысты өңдеудің нәтижелері қайталанады. Бұл кезде сервермен қатысуға арналған бір тіл қажет, осындай тіл ретінде SQL таңдалынған.

Єдістемелік нұсқауларда MS SQL Server ортасында мәліметтер қорларын өңдеудің сұрақтары қарастырылған: мәліметтер қорының объектілерін жасау, мәліметтермен әрекет жасау және мәліметтерді табу үшін әр түрлі күрделі сұраныстарды құрастыру.

SQL кестелермен жұмыс жасауға негізделген сондықтан оның күрделі қолданбалы бағдарламаларды жасауға құралдары жоқ. Сондықтан онымен бірге жоғары деңгейлі бағдарламалық тілдер қолданылады. Бір лабораториялық жұмыс Delphi ортасында “клиенттік” қолданбалы бағдарламасын жасауды қарастырады.

Оқу жоспары бойынша бұл пәнде студенттер өзіндік жұмысты орындаулары керек. Єдістемелік нұсқауда өзіндік жұмысқа тапсырмалар келтірілген.

Пікір беруші: АЭжБИ-дің ИК кафедрасының доценті, т.ғ.к. Ю.В.Шевяков

Алматы энергетика және байланыс институтының 2004 ж. жоспары бойынша басылады.

	МАЗМҰНЫ	б
КІРІСПЕ		4
1 лабораториялық жұмыс. МӘЛІМЕТТЕР ҚОРЫНЫҢ ЖОБАСЫН ӨҢДЕУ, ҚҰРАМЫН ЖАСАУ		4
	1.1. Концептуалды жобалау	4
	1.2 Оқу процесс мәліметтерінің концептуалды үлгісі	5
	1.3 Концептуалды үлгіні реляциондық үлгіге түрлендіру	7
	1.4 Лабораториялық жұмысқа тапсырма	11
	1.4.1 Мәліметтер қорын жасау	11
	1.4.2 Кестелерді жасау	12
	1.4.3 Кесте құрамын өзгерту	14
	1.4.4 Кестелерді жою	15
	1.5 Бақылау сұрақтары	15
2 лабораториялық жұмыс. МӘЛІМЕТТЕРГЕ ӘРЕКЕТТЕРДІ ҚОЛДАНУ		15
	2.1 Мәліметтерге әрекеттерді қолдану бұйрықтары	15
	2.2 Лабораториялық жұмысқа тапсырма	17
	2.3 Бақылау сұрақтары	17
3 лабораториялық жұмыс МӘЛІМЕТТЕР ҚОРЫНА СҰРАНЫСТАР		18
	3.1 Кестелерден мәліметтерді алу	18
	3.2 Мәліметтерді таңдауға шарттарды орнату	20
	3.3 Мәліметтерді агрегатты функциялармен қорытындылау	23
	3.4 Лабораториялық жұмысқа тапсырма	24
	3.5 Бақылау сұрақтары	25
4 лабораториялық жұмыс КҮРДЕЛІ СҰРАНЫСТАРДЫ ЖАСАУ		25
	4.1 Сұраныстарда кестелерді ұйымадастыру	25
	4.2 Сұраныс ішіндегі сұраныстарды қолдану	26
	4.3 Елестетуді жасау және қолдану	30
	4.4 лабораториялық жұмысқа тапсырма	30
	4.5. Бақылау сұрақтары	31
5 лабораториялық жұмыс. САҚТАЛЫНҒАН ПРОЦЕДУРАЛАР МЕН ТРИГГЕРЛЕР		31
	5.1 Процедураларды қолдану	31
	5.2 Триггерлер	33
	5.3 Лаборатриялық жұмысқа тапсырма	34
	5.4 Бақылау сұрақтары	35
6 лабораториялық жұмыс КЛИЕНТТІК ҚОЛДАНБАЛЫ БАҒДАРЛАМАЛАРДЫ ӨҢДЕУ		35
	6.1 Мәліметтердге DELPHI қолданбалы бағдарламадан қол жеткізу	35
	6.2 «Education» мәліметтер қоры үшін қолданбалы бағдарлама жасау	36
	6.3 Лабораториялық жұмысқа тапсырма	40
	6.4 Бақылау сұрақтары	41
ӨЗІНДІК ЖҰМЫСҚА ТАПСЫРМА		41
ӘДЕБИЕТТЕР ТІЗІМІ		42

КІРІСПЕ

Қазіргі жағдайда басқарудың мәліметтік технологиялары реляционды мәліметтік қорларын басқаратын жүйелерде (РМҚБЖ) негізделген. Клиент-серверлік архитектурасында қолданылатын МҚБЖ-лер ішінде көп тараған жүйелері - Microsoft SQL Server, Oracle, Informix, Sybase SQL Server болып табылады. Бұл МҚБЖ-лер мәліметтік қорларының реляциондық SQL-серверлері болады. Клиент-серверлік архитектурасы бар мәліметтік қорларын басқаратын жүйелерінің өзінің клиенттік бағдарламасы болуы мүмкін. Сонымен бірге бұл мәліметтік қорының серверінің клиенті ретінде басқа МҚБЖ-лер қолдануы мүмкін. Клиенттердің сервермен байланысуы үшін арнайы бағдарламалар өңделген. Microsoft SQL Server жүйесі мәліметтер қорынын өндіріс деңгейінде жасап, қолдануға негізделген. Негізгі міндеті – үлкен корпоративті қорлармен жүмыс жасау. Керекті мәліметтерді қордан табу үшін, арнайы сұраныстар орнатылады. SQL Server-де универсалды сұраныстар тілі ретінде құрамдасқан сұраныстар тілі - SQL (Structured Query Language) қолданылады. Әр түрлі өңдеушілермен жасалған SQL тілінің бірнеше диалекттері бар. SQL-дің соңғы SQL Server 7.0 версиясында Transact SQL диалектісі қолдалынады. Бұл версия SQL 92 негізделген, ал SQL 92 – қазірдегі ANSI-мен орнатылған стандарт (ANSI – Америкадағы ұлттық стандарттар институты). Бүгінгі күнде дамыған интерфейсі бар және де оптималданған құрамы бар идеалды мәліметтер қорларын басқаратын жүйесі жоқ. Мысалы, MS Access клиенттік бағдарлама ретінде өте ыңғайлы, бірақ оны негізгі қор ретінде қолдануға болмайды. Ал, SQL Server жүйесінде пайдаланушымен интерфейсі жоқ. Бірақ, бұл жүйеде мәліметтерді сақтап, олармен жұмыс істеуге көп дамыған құралдары бар.

1 лабораториялық жұмыс. Мәліметтер қорының жобасын өңдеу, құрамын жасау

1.1 Концептуалды жобалау. Мәліметтерге қол жеткізу әдістері соңғы бірнеше он жылдардағы физикалық бағытталған әдістерден қазіргі кездегі мәліметтерді өңдеудің бірнеше түрлеріне дамыған. Қазіргі кездегі реляциондық «революциясының» маңызды аспектісі – мәліметтердің логикалық құрамын олардың компьютерлік құралдар талап ететін физикалық көрсетулерінен бөлу. Осы идея қабылданып, мәліметтер қорларының үш деңгейлі архитектурасы ретінде ұсынылды. Үш деңгейлі архитектура – концептуалды, сыртқы және ішкі деңгейлерден тұратын мәліметтер қорларының стандартты құрамы.

Концептуалды деңгейінде мәліметтер қорының концептуалды жобасы өңделеді.

Концептуалды жобалау пайдаланушының мәліметтік қажеттіліктерін анализдеп, оларға мәліметтердің қажетті элементтерін таңдаудан тұрады. Концептуалды жобалау нәтижесінде концептуалды схема пайда болады яғни мәліметтердің барлық элементтерінің тұтас логикалық бейнелеуі және олар арасындағы қатынастар. Мәліметтер қорындағы мәліметтерге пайдаланушылардың көз-қарастары сыртқы деңгейді құрастырады. Ішкі деңгей мәліметтер қорының физикалық түрін анықтайды. Бұл деңгейге пайдаланушының қатынасы жоқ. Концептуалды жобалау мәліметтер қорының концептуалды схемасын жасаудан тұрады. Бұл қадамда пайдаланушылардың мәліметттерге көз-қарастарының үлгілері жасалынып, олар концептуалды үлгіге түрленеді, сонан соң бұл үлгі мәліметтер қорын құрастыратын мәліметтердің барлық элеметтерінен тұрады. Концептуалды үлгінің басты элементтері объектілер мен қатынастар болып табылады. Пайдаланушылар үлгіленетін ортаның ішіндегі маңызды деп санайтын заттар объектілерді құрастырады. Заттардың бір типті жиынтығы объектілік жиынтық болады.

Екі объектілік жиынтықтар элементтерінің арасындағы байлансытар қатынас деп аталады. Қатынастың өзі объектілік жиынтық ретінде қарастырылса, ол құрамдасқан объектілік жиынтық болады. Бір объектілік жиынтық элементтерінің басқа объектілік жиынтығы элементтерімен байланыстарының максималды саны қатынастың қуаттылығы деп аталады. Реляциондық үлгілерге қарағанда концептуалды үлгілерді түсіну жеңілдеу, себебі олар заттарға кәдімгідей көз қарасты сақтайды. 1.2 Оқу процесс мәліметтерінің концептуалды үлгісі

Ағынды семестрдің оқу процесінің мәліметтерінен тұратын мәліметтер қорын жасау керек болсын. Ол мәліметтер, мысалы: топтардағы студенттердің тізімдері, оқылатын пәндердің тізімі, кафедралардың мұғалімдерінің тізімдері, әр тобында өтетін лекциялар, практикалық, лабораториялық, т.б. сабақтар туралы мәліметтер, өткізілген сабақтар бойынша емтихандар, сынақтар туралы мәліметтер. Мәліметтер қорын “Оқу процесі” деп атайық. Бір типті заттар бөлек объектілік жиынтықтарда сақталынатын болғандықтан, келесі объектілік жиынтықтарды құраcтыруымызға болады:

ТОБЫ, СТУДЕНТ, КАФЕДРА, МҰҒАЛІМ, ПӘНДЕР, САБАҚ_ТҮРЛЕРІ.

Бұл объектілік жиынтықтардың кей-бір атрибуттарын анықтайық:

ТОБЫ - Тобы_аты, Студенттер_саны, Курсы.

СТУДЕНТ - Студент_фамилиясы, Студент_аты, Студент_әкесінің_аты,

Студент_туған_күні, Студент_адресі.

КАФЕДРА - Кафедра_аты, Кафедра_телефоны, Меңгеруші_аты_жөні.

МҰҒАЛІМ - Мұғалім_фамилиясы, Мұғалім_аты, Мұғалім_әкесінің_аты,

Мұғалім_қызметі, Мұғалім_ғылым_дәрежесі.

ПӘНДЕР - Пән_ аты, Барлық_сағаты, Лекциялар_сағатттары, Практика_сағаттары, Лабораториялық_сағаттары.

САБАҚ_ТҮРЛЕРІ – Сабақ_түрі.

Мәліметтер қорына көпдеген сұрақтар қойылуы мүмкін. Мысалы: студент қайсы тобында оқиды, мұғалімдер қайсы кафедраларда жұмыс істейді, қай топтарда сабақ өткізеді, студенттер белгілі сабақ түрлерінен белгілі сабақтардан қандай баға алған, т.б. Мәліметтер қорына қойылатын мүмкін болатын сұрақтарға жауап алу үшін объектілік жиынтықтардың арасындағы байланыстарды қарастыру қажет. ТОБЫ мен СТУДЕНТ объектілер арасында “бір-көпке”деген қатынас бар, себебі бір тобында көп студенттер бар, ал бір студент тек қана бір тобы құрамына кіреді. Сол сияқты КАФЕДРА мен МҰҒАЛІМ объектілер “бір-көпке” қатынаста болады (бір кафедрада көп мұғалім бар, ал әр мұғалім тек қана бір қафедрада қызмет жасайды). Әр-бір пән бойынша әр түрлі топтарда әр түрлі мұғалімдермен сабақтардың көп түрлері өткізіледі. Сондықтан, ТОБЫ мен ПӘНДЕР, ТОБЫ мен МҰҒАЛІМ, ПӘНДЕР мен МҰҒАЛІМ, ПӘНДЕР мен САБАҚ_ТҮРЛЕРІ жиынтықтар араларында “көп-көпке”деген қатынастар бар. Топтар, пәндер және мұғалімдер арасындағы қатынастар 1-суретте келтірілген, суретте қатынастардың қуаттылықтары көрсетілген. Схемада объектілердің атрибуттары көрсетілмеген.

МҰҒАЛІМ

бөлінеді

оқиды

ПӘНДЕР

САБАҚ_ТҮРІ

^ТО^БЫ

ОҚУ

өткізеді

1 – сурет. ОҚУ құрамды объектілік жиынтық

Сабақ_сағаты

Бұл қатынастар құрамды объектілік жиынтықты құрастырады, оны ОҚУ деп атайық. Бұл жиынтықтың өзінің “Сабақ_сағаты” деген атрибуты бар (бұл атрибуттың мәні ПӘНДЕР объектілік жиынтығының Лекциялар_сағатттары, Практика_сағаттары, Лабораторялық_сағаттары атрибуттарының біреуінің мәніне тең). Мәліметтер қорында ОҚУ объектісінде көрсетілген барлық сабақтар түрлерінен семестр бойынша студенттердің бағалары туралы мәліметтерде болуы қажет. СТУДЕНТ пен ОҚУ объектілері арасында “көп-көпке” деген қатынас бар, себебі әр студент ОҚУ объектінде көрсетілген сабақтардың көп түрлеріне қатысады, ал сабақтың бір түрі көп студенттерге өткізіледі. Байланыстыратын объект ретінде ҮЛГЕРУ объектісін қолданамыз, бұл объектіде белгілі студенттің белгілі пәннен бағалары орнатылады. Сондықтан бұл объект СТУДЕНТ және ОҚУ объектілерімен байланысады. Бір студенттің бірнеше пәннен бағалары бар, бірақ әр баға белгілі студенттікі. Сонымен, ҮЛГЕРУ объектісі СТУДЕНТ объектісіне бағынады, ол СТУДЕНТ объектісімен “бір-көпке” деген қатынаста болады. ҮЛГЕРУ объектісі ОҚУ объектісінеде бағынады, оныменда “бір-көпке” қатынаста болады, себебі сабақтардың бір түрі бойынша көп студенттердің үлгерулері туралы мәліметтер бар, бырақ әр қайсысы белгілі сабақ түрінен анықталған. Бұл құрамды объектілік жиынтықтың өзінің атрибуттары бар: Тапсыру_датасы, Бақылау_түрі және Бағасы.

Мәліметтер қорының ақырғы схемасы 2-суретте келтірілген. Схемаға ТОБЫ мен СТУДЕНТ, КАФЕДРА мен МҰҒАЛІМ объектілер арасындағы қатынастарда қосылған. Объектілер атрибуттары схемада көрсетілмеген.

1.3 Концептуалды үлгіні реляциондық үлгіге түрлендіру

Концептуалды үлгі объектілер, атрибуттар, қатынастар және құрамды объектілерден тұрады. Олардың барлығын реляциондық кестелерге түрлендіру керек. Нәтижесінде алынған кестелер төртінші нормалды түрде болады яғни үлгіні әрі қарай нормаландырудың қажеті жоқ.

Объектілік жиынтықтар мен атрибуттарды түрлендіру. Объектілік жиынтықты реляциондық кестеге түрлендіруге болады. Объектілік жиынтықтың аты кесте аты болып, ал атрибуттары кестенің атрибуттары болады. Егер де атрибуттар жиынтықтары ішінде бір сыпырасын кестенің кілті ретінде пайдалануға болса, олар кестенің кілті бодып таңдалынады. Кері жағдайда, кестеге жаңа атрибут қосылады. Оның мәндері объектілік жиынтықтың элементтерін бір мағыналы анықтауы қажет, сонымен ол кестенің кілті бола алады.

ТОБЫ, КАФЕДРА, ПӘНДЕР объектілік жиынтықтарын сәйкес аттары бар реляциондық кестелерге түрлендірейік. Егер де объект элементтерінің аттары қайталанбайды деп есептесек, бұл кестелердің потенциалды кілттері ретінде Тобы_аты, Кафедра_аты, Пән_аты атрибуттары таңдалынуына болады. Бірақ біздер бұл кестелердің элементтерін бір мағыналы анықтау үшін кестелерге Тобы_нөмірі, Кафедра_нөмірі, Пән_нөмірі деген атрибуттарды еңгізіп, оны бастапқы кілт ретінде қолданамыз. Күнделікті мәліметтерді еңгізу жұмысында бұл кілтті пайдалану қарапайымдау болады.

ҮЛГЕРУ

ОҚУ

МҰҒАЛІМ

бөлінеді

оқиды

ПӘНДЕР

САБАҚ_ТҮРІ

^ТОП

өткізеді

КАФЕДРА

жұмыс_істейді

СТУДЕНТ

тапсырады

құрамында

Тапсыру_датасы

Бақылау_түрі

Бағасы

Сабақ_сағаты

ҮЛГЕРУ

2 – сурет. Оқу процесс мәліметтерінің концептуалды үлгісі

Сонымен, келесідей реляциондық кестелерді аламыз (кілттік өрістері курсивпен белгіленген): ТОБЫ (Тобы_нөмірі, Тобы_аты, Студент_саны, Курсы);

КАФЕДРА (Кафедра_нөмірі, Кафедра_аты, Кафедра_телефоны, Меңгеруші_аты_жөні);

ПӘНДЕР (Пән_нөмірі, Пән_аты, Барлық_сағаты, Лекциялар_сағатттары,

Практика_сағаттары, Лабораторялық_сағаттары).

САБАҚ_ТҮРІ объект жалғыз атрибуты бар келесі САБАҚ_ТҮРІ (Сабақ_түрі) кестеге түрленеді. СТУДЕНТ деген объектіде студентті табу үшін оның фамилиясын қолдануға болады. Бырақта, фамилиялары бірдей студенттер болуы мүмкін, сондықтан студент идентификаторы ретінде оның реттік нөмірін қолданамыз. Бұл кестелерде келесідей: “Сынақ_кітапша_нөмірі”, “Жеке_куәлік_нөмірі”, “РНН_нөмірі” сияқты идентификаторларды қолданбаңыз, себебі оларды еңгізу операцияларда қолдану ыңғайсыз, және олар өзгеруі мүмкін (мысалы жоғалғаннан кейін): СТУДЕНТ (Студент_нөмірі, Студент_фамилиясы, Студент_аты, Студент_әкесінің_аты, Студент_туған_күні, Студент_адресі).

МҰҒАЛІМ кестесінің құрамыда сол сияқты анықталады:

МҰҒАЛІМ (Мұғалім_нөмірі, Мұғалім_фамилиясы, Мұғалім_аты,

Мұғалім_әкесінің_аты, Мұғалім_қызметі, Мұғалім_ғылым_дәрежесі). Қатынастарды түрлендіру .Кез-келген “бір-көпке” деген қатынаста, қуаттылығы “көп” жағындағы объектіні бейнелейтін кестеге, басқа объектінің кілті сыртқы кілт ретінде еңгізіледі. ТОБЫ және СТУДЕНТ объектілер арасындағы байланыстар “бір-көпке” қатынастармен бейнеленеді, себебі бір топта көп студент, ал бір студент тек қана жалғыз топқа кіреді. Сонда қатынастарды түрлендіру ережесі бойынша, олар арасындағы байланыс тобының номері бойынша орнатылады. Бұл атрибут СТУДЕНТ кестесінің сыртқы кілті болады. Сонымен бірге, кестеге Студент_староста атрибутын қосамыз, бұл өрісте тобының старостасы болатын студенттің нөмірін енгіземіз, яғни кестенің өзінің кілттік өрістерінің мәндерін. Бұл атрибут СТУДЕНТ объектісін өз-өзімен байланыстырады, бұндай қатынастар рекурсивті қатынас деп аталады. Студент_староста атрибуты рекурсивті сыртқы кілт болады. Сонымен, СТУДЕНТ кестенің құрамы осындай болады:

СТУДЕНТ (Студент_нөмірі, Студент_фамилиясы, Студент_аты, Студент_әкесінің_аты, Студент_туған_күні, Студент_адресі, Тобы_нөмірі, Студент_староста). Тағы бір жағдайды айтып кеткен жөн. Бұл кестеде тобының нөмірінен және студенттің нөмірінен тұратын құрамды кілтті таңдауға болар еді. Осындай идентификатор студенттің тобын тікелей анықтауға мүмкіншілік берер еді. Бұл кезде старосталарды анықтау үшін бөлек кесте керек болатын еді. Біз қарапайым кілтті қолданайық дедік.

КАФЕДРА және МҰҒАЛІМ кестелер ”бір-көпке” деген қатынаста. Олар арасындағы байланыс басты КАФЕДРА объектісінің уникалды кілті – кафедра нөмірі бойынша орнатылады, ол бағынды объектіге енгізіледі:

МҰҒАЛІМ (Мұғалім_ нөмірі, Мұғалім_фамилиясы, Мұғалім_аты, Мұғалім_әкесінің_аты, Мұғалім_қызметі, Мұғалім_ғылым_дәрежесі, Кафедра_нөмірі).

ОҚУ объектісі «көп-көпке» деген қатынастарды анықтайды (1 сурет). Осындай қатынастарды түрлендіру үшін қиылысу кестесі орнатылады, мұндай кестеде бастапқы кестелердің тек қана кілттік өрістері қолданылады. Қиылысу кестенің өзінің кілттік емес атрибуттары болуы мүмкін.

ОҚУ кестенің атрибуттарын анықтайық: ОҚУ (Тобы_нөмірі, Пән_нөмірі, Мұғалім_нөмірі, Сабақ_түрі, Сабақ_сағаты).

Ескерту. Келешекте жобаны жасағанда, САБАҚ_ТҮРІ кестені жасамаймыз, себебі бұл кестенің жалғыз атрибуты ОҚУ кестесінің кілттік атрибуттар құрамына кіреді. Қарастырып отырған объектілердің ”көп-көпке” қатынасында ОҚУ кестесі байланыстыру объект болады. Әр топта оқылатын пәндерге сәйкес сабақтардың көп түрлері өткізіледі. Басқа жақтан қарағанда, сабақтың әр түрі белгілі топ үшін анықталған. Сондықтан, ТОБЫ және ОҚУ объектілер арасында ”бір-көпке” деген байланыс бар. Әрбір пән бойынша әртүрлі топтарда әртүрлі мұғалімдермен көпдеген сабақтар өткізіледі. Басқа жақтан қарағанда, әр сабақ белгілі пән бойынша өткізіледі, сондықтан ПӘНДЕР және ОҚУ объектілер арасында ”бір-көпке” деген байланыс бар. Сол сияқты МҰҒАЛІМ және ОҚУ объект арасындағы қатынастар түрі ”бір-көпке” болады.

ҮЛГЕРУ объектісінде студенттердің семестр бойынша әр пәннен қорытынды бағалары сақталады. Осындай бағалар бір жағынан студенттің идентификаторымен (Студент_номері), басқа жақтан – сабақтың (Тобы_нөмірі+Пән_нөмірі+Мұғалім_нөмірі+Сабақ_түрі) идентификаторымен анықталады. Осы идентификаторлардың қосындысы ҮЛГЕРУ объектінің уникалды идентификаторын құрастырады. Бұл объект белгілі студенттің белгілі пән бойынша бағаларын анықтайды. Әртүрлі пәндер бойынша әртүрлі бақылаулар өткізіледі (емтихан, сынап, т.б). Бақылау_түрі, Тапсыру_датасы, Бағасы атрибуттар осы кестенің кілттік емес атрибуттары болады:

ҮЛГЕРУ (Студент_нөмірі, Тобы_нөмірі,Пән_нөмірі, Мұғалім_нөмірі,

Сабақ_түрі, Бақылау_түрі, Тапсыру_датасы, Бағасы).

Сонымен, жобаланып отырған мәліметтер қорының реляциондық кестелерінің құрамы келесі болады (бастапқы кілттері курсивпен белгілінген): ТОБЫ (Тобы_нөмірі, Тобы_аты, Студент_саны, Курсы);

КАФЕДРА (Кафедра_нөмірі, Кафедра_аты, Кафедра_телефоны, Меңгеруші_аты_жөні);

ПӘНДЕР (Пән_нөмірі, Пән_ аты, Барлық_сағаты, Лекциялар_сағатттары, Практика_сағаттары, Лабораторялық_сағаттары).

СТУДЕНТ (Студент_нөмірі, Студент_фамилиясы, Студент_аты,

Студент_әкесінің_аты, Студент_туған_күні, Студент_адресі.

Тобы_нөмірі, Студент_староста).

МҰҒАЛІМ (Мұғалім_нөмірі, Мұғалім_фамилиясы, Мұғалім_аты,

Мұғалім_әкесінің_аты, Мұғалім_қызметі,

Мұғалім_ғылым_дәрежесі, Кафедра_нөмірі).

ОҚУ (Тобы_нөмірі, Пән_нөмірі, Мұғалім_нөмірі, Сабақ_түрі, Сабақ_сағаты). ҮЛГЕРУ (Студент_нөмірі, Тобы_нөмірі,Пән_нөмірі, Мұғалім_нөмірі,

Сабақ_түрі, Бақылау_түрі, Тапсыру_датасы, Бағасы).

1.4 Лабораториялық жұмысқа тапсырма

Жобаланған оқу процесінің үлгісін мәліметтер қорларын басқару MS SQL Server жүйесінің ортасында жасаңыз. Лабораториялық жұмыста қарастырып отырған мәліметтер қорының объектілеріне ағылшын тілде атаулар қолданылады. Бұл пайдаланатын бағдарламаның талабына сәйкес. Сондықтан төмендегі мысалдарда мәліметтер қоры “Education” деп аталады.

Кез-келген SQL Server-дің объектісін жасау үшін белгілі бұйрықты жасауда негізделген бір-неше тәсілдері бар. Transact SQL операторлары мен процедураларын еңгізіп тексеруге арналған SQL Server Query Analyser - SQL сұраныстарын анализдеу терезесі қолданылады. Бұл терезені бағдарламаның негізгі терезесінің Tools менюін қолданып ашуға болады.

Пайдаланушылардың мәліметтер қорларының орнатылуы SQL версиясынан және Program Files тобының орнатуынан тәуелді. Databases тізіміндегі кез-келген қордың қасиеттерін контексттік менюден Свойства (Properties) жолын таңдап пайдаланушының мәліметтер қорын орнату орнын анықтауға болады. Осы қасиеттер терезесінен транзакциялар журналының орнатылуында TransactionLog беттен қарап алыңыз. Мәліметтер қорының резервті копиясын жасау және қажет болса қалпына келтіру бұйрықтары да осы контексттік менюден таңдалынады: Все задачи (All Tasks) жолынан Backup Databases немесе Restore Databases бұйрықтары.

1.4.1 Мәліметтер қорын жасау

Мәліметтер қоры операциондық жүйенің бір немесе бернеше файлдарында орнатылады. Операциондық жүйенің бір файлында бірнеше мәліметтер қоры орнатылмайды. Бұл файлда кестелер мен индекстер сақталынады. SQL Server транзакция жасау алдында және жасалғаннан кейін мәліметтерді жазып отыратын жұмыс ортаны транзакциялар журналы деп атайды. Бұл мәліметтер орындалған транзакцияны жою немесе қажет болса мәліметтер қорын қалпына келтіру үшін қолданылады. 7.0 версияда транзакциялар журналы бұрынғы версиядай кестелермен бірге емес, бөлек файлда сақталынады. Transact SQL көмегімен мәліметтер қорын жасау үшін CREATE DATABASE бұйрығы қолданылады. CREATE DATABASE education

ON PRIMARY

(NAME = education_data, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\education_data.mdf', size = 4, maxsize =25, filegrowth = 1 mb)

LOG ON

(NAME = education_log, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\education_log.ldf', size = 4, maxsize = 20,

filegrowth =1 mb)

Ескерту: Мәліметтер қорымен транзакциялар журналының орнатылуы - 'С:\…’ – SQL версиясы мен Program Files тобының орнатуына қарай өзгеруі мүмкін. Мұнда:

education - Жасалынып отырған мәліметтер қорының аты.

ON - Мәліметтер қорының ақпараттары сақталынатын дискідегі

файлдардың тізімін анықтайды.

PRIMARY - Мәліметтер қорының және жүйелік кестелердің логикалық басы болатын файлды анықтайды. Мәліметтер қорында бастапқы

(PRIMARY ) файл жалғыз болады. Бұл параметр көрсетілмеген болса, тізімдегі бастапқы файл ретінде бірінші файл қолданылады LOG ON - Дискідегі транзакциялар журналы сақталынатын файлдардың тізімін анықтайды. Бұл параметр көрсетілмеген болса, транзакциялар журналының өлшемі мәліметтер файлының өлшемінің 25% болады.

education_ data – Файлға қатысу үшін SQL Server-дің қолданатын логикалық аты.

FILENAME – Операциондық жүйе файлының параметрлерін анықтайды (SQL Server орнатылған сервердегі файлдың атын, мәліметтер қорының бастапқы және соңғы өлшемін, мәліметтер қорының өлшемін өсіретін өсімшесі).

1.4.2 Кестелерді жасау

Ағынды мәліметтер қорын келесі бұйрықпен анықтаңыз:

USE education

Енді барлық қолданатын бұйрықтар осы мәліметтер қорында орындалады.

Кестені Transact-SQL тілінің CREATE TABLE операторы көмегімен жасауға болады. Біріншіден кестелердің тік жол мәліметтерінің типтерін анықтау қажет. Тік жол үшін тағы бір - NULL немесе NOT NULL сипаттамасын анықтауға болады. Егер де тік жол үшін NULL атрибуты анықталған болса, мәліметтерді енгізгенде, осы тік жол үшін мәндерді енгізбеуге болады. Кері жағдайда (NOT NULL) SQL Server бұл тік жолды бос қалдыруға рұқсат бермейді. ТОП кестесін жасау: CREATE TABLE Gruppa (

Grup_ID integer identity (1,1) not null PRIMARY KEY,

Grup_NAME char (9) not null,

Grup_KOLSTUD smallint not null,

Grup_COURSE int not null )

СТУДЕНТ кестесін жасау (мұнда кілттерді бейнелеудің басқа варианты келтірілген):

CREATE TABLE Students (

Stud_ID bigint not null ,

Stud_FAM char(20) not null,

Stud_IMA char(10) not null,

Stud_OTCH char(15) not null,

Stud_DATE datetime not null,

Stud_ADDRESS char(25) null,

Grup_ID integer not null FOREIGN KEY REFERENCES Gruppa (Grup_ID ),

Stud_STAR bigint not null ,

CONSTRAINT PK_Students PRIMARY KEY (Stud_ID),

CONSTRAINT FK_Students_Students FOREIGN KEY (Stud_STAR)

REFERENCES Students (Stud_ID)

)

КАФЕДРА кестесін жасау: CREATE TABLE Chair (

Chair_ID integer not null PRIMARY KEY ,

Chair_NAME char(20) not null,

Chair_PHONE char(10) null ,

Chair_CHIEF char(15) not null )

МҰҒАЛІМ кестесін жасау: CREATE TABLE Teacher (

Teach_ID bigint not null PRIMARY KEY ,

Teach_FAM char(20) not null,

Teach_IMA char(10) not null,

Teach_OTCH char(15) not null,

Teach_POSITION char(18) not null,

Teach_STEPEN char(12) null ,

Chair_ID integer not null FOREIGN KEY REFERENCES Chair (Chair_ID) )

ПӘНДЕР кестесін жасау: CREATE TABLE Subject (

Subj_ID integer not null PRIMARY KEY ,

Subj_NAME char(20) not null,

Total_Hours integer not null,

Lection_Hours integer not null,

Practice_Hours integer not null,

Labor_Hours integer not null ) ОҚУ кестесін жасау: CREATE TABLE Study (

Grup_ID integer not null FOREIGN KEY REFERENCES Gruppa (Grup_ID ),

Subj_ID integer not null FOREIGN KEY REFERENCES Subject (Subj_ID ),

Teach_ID bigint not null FOREIGN KEY REFERENCES Teacher (Teach_ID),

Form_Study char (20) not null,

Lesson_Hours integer not null,

CONSTRAINT PK_Study PRIMARY KEY (Grup_ID, Subj_ID, Teach_ID, Form_Study )

)

ҮЛГЕРУ кестесін жасау: CREATE TABLE Progress (

Stud_ID bigint not null FOREIGN KEY REFERENCES Students (Stud_ID),

Grup_ID int not null,

Subj_ID integer not null,

Teach_ID bigint not null,

Form_Study char (20) not null,

Form_Control char (20) not null,

Pr_DATE datetime null,

OCENKA integer CHECK ( OCENKA in (0,1,2,3,4,5,6,7,8,9)) DEFAULT(0),

CONSTRAINT FK_Progress_Study

FOREIGN KEY (Grup_ID, Subj_ID, Teach_ID, Form_Study)

REFERENCES Study (Grup_ID, Subj_ID, Teach_ID, Form_Study),

CONSTRAINT PK_Progress

PRIMARY KEY (Stud_ID, Grup_ID, Subj_ID, Teach_ID, Form_Study)

)

1.4.3 Кесте құрамын өзгерту

Кестені жасап болғаннан кейін оның құрамын ALTER TABLE бұйрығымен өзгертуге болады. Өрістерді қосу. Students кестесіне студентердің стипендиялары туралы мәліметтерді сақтайтын өрісті қосыңыз:

USE education

ALTER TABLE Students

ADD Stud_STIP smallmoney

Өрістерді жою. Еңгізілген өрісті кестеден жойыңыз:

ALTER TABLE Students

DROP COLUMN Stud_STIP

Шектеулерді қосу. Кестенің бірінші немесе сыртқы кілттері бейнеленбеген болса, оны да ALTER TABLE бұйрығымен орнатуға болады.

Мысалы Students кестесінде бірінші кілт анықталмаған дейік:

ALTER TABLE Students

ADD CONSTRAINT PK_Students PRIMARY KEY (Stud_ID) Study кестесінде сыртқы кілттің біреуін анықтау қажет болса: ALTER TABLE Study

ADD CONSTRAINT PK_Study_Gruppa FOREIGN KEY (Grup_ID) REFERENCES Gruppa (Grup_ID) 1.4.4 Кестелерді жою

Кестелерді жою үшін DROP бұйрығы негізделген. Мысалы:

DROP Students

DROP бұйрығын ұқыпты қолданыңыз!

1.5 Бақылау сұрақтары

1.5.1 Мәліметтердің концептуалды үлгісі дегеніміз не?

1.5.2 Мәліметтер қорының реляциондық схемасын қалай түсінесіз?

1.5.3 Келесі терминдердің мағынасын түсіндіріңіз: бастапқы кілт, сыртқы кілт, құрамды кілт, реляциондық кесте, нормаландыру.

1.5.3 SQL деген не?

1.5.5 Клиент-серверлік технологиялардың мәйнфреймді қолданатын технолгиясынан негізгі айырмашылығы.

1.5.6 Клиент-серверлік технологиясында персоналды компьютер клиент болама немесе сервер бола ма?

2 лабораториялық жұмыс. Мәліметтерге әрекеттерді қолдану

2.1 Мәліметтерге әрекеттерді қолдану бұйрықтары

Кестеге жазбаларды қосу. Мәліметтер қорының кестелеріне қолданатын әрекеттердің ішінде негізгісі - кестеге мәліметтерді қосу болып табылады.

Кестеге мәліметтерді қосу үшін INSERT инструкциясы қолданылады. Ең қарапайым түрде бұл инструкцияның келесі түрі қолданылады:

INSERT INTO кесте_аты

VALUES (мән,…, мән)

Бұл бұйрық ешқандай мәліметтерді шығармайды. Бірақ Query Analyzer бір жазбаның қосылғанын атап кетеді. Бұл бұйрықты қолдану алдында кесте жасалынып тұруы қажет, ал VALUES қызмет сөзінен кейінгі жақшадағы барлық мәндердің мәліметтер типтері сәйкес тік жолдың мәліметтерінің типтерімен бірдей болуы керек. Бірінші мән бірінші тік жолға, екінші – екіншіге, т.с. түседі. Мысалы, студенттердің кестесіне екі жазбаны қосу үшін келесіні қолданамыз:

INSERT INTO Students

VALUES (030101, 'Ахметов', 'Данияр', 'Муратович', 02/04/85,'Алматы', 1, 030101)

INSERT INTO Students

VALUES (030102,'Ким', 'Алла', 'Юрьевна',11/09/85б, 'Алматы',1, 030101)

Егер де бос мәнді (NULL) еңгізу қажет болса, оны мәндер тізіміне қосыңыз (кесте жасалғанда, өріс типі NULL деп сипатталыну қажет). Мысалы: INSERT INTO Students

VALUES (030103, 'Цой', 'Олег','Иванович',10/05/85, NULL,1,030101)

Кестелерден жазбаларды жою. Бұл үшін DELETE бұйрығы қолданылады:

DELETE [FROM] кесте_аты

WHERE тік_жол_аты = мән

FROM деген қызмет сөзді қолданбауға да болады. DELETE инструкциясы WHERE қызмет сөзсіз өте сирек қолданылады. Операцияны жасау алдында, инструкцияны дұрыс жазғаныңызға көз жеткізіңіз, себебі байқамай кестенің барлық жазбаларын жоғалтып алуыңыз мүмкін.

Студенттердің кестесінің барлық жазбаларын жою үшін келесіні енгізуге болады:

DELETE FROM students

Әдетте мұндай бұйрықтар жасалынбайды. Ө»ткені кестенің белгілі жолдарын жою қажет. Ол үшін сұрыптау шарттары қолданылады. Мысалы, студент туралы белгілі жолды жою үшін, келесіні еңгізуге болады:

DELETE FROM students

WHERE Stud_ID = 30103

Әрине, шартты бір неше жазба қанагаттандырса, олардың барлығы жойылады.

Файлдық мәліметтер қорларын басқару жүйесінен өзгеше, SQL Server жойылатын жазбаларды белгілемейді, бірден жояды, сондықтан, оларды қайта қайтару мүмкін емес. DELETE бұйрығын ұқыпты қолданыңыз.

Өрістің мәнін өзгерту. Кестенің тік жолдарындағы мәндерді өзгерту үшін UPDATE инструкциясы қолданылады. Бұл инструкцияның қарапайымдалған түрі мынадай:

UPDATE кесте_аты

SET бірінші_тік_ жол_аты = мән, …, n-ші_тік_ жол_аты = мән

WHERE тік _ жол_аты салыстыру_операторы мән

UPDATE бұйрығы кестедегі жазбалардың кейбір немесе барлық мәндерін өзгертуге мүмкіндік береді. Бұл бұйрықтың UPDATE қызмет сөзінен кейін кесте аты көрсетіледі, SET сөзінен кейін белгілі тік жол үшін орнатылатын өзгерістер аталады. Мысалы, барлық мұғалімдердің қызметтерін доцент деп өзгерту үшін, келесіні қолдануға болады:

UPDATE Teacher

SET Teach_POSITION = ‘доцент’

DELETE бұйрығындай, UPDATE бұйрығында өзгертетін жазбаларды табу үшін, шарттар қолдануға болады. Мысалы, нөмірі 30102-ге тең студенттің стипендиясының мәнін өзгерту үшін, келесіні жазамыз:

UPDATE Students

SET Stud_STIP = 2000

WHERE Stud_ID = 30102

Араларын үтірмен бөліп, SET сөйлемінде бірнеше тік жолдарды орнатуға болады.

2.2 Лабораториялық жұмысқа тапсырма

2.2.1 Мәліметтер қорының барлық кестелерін мәліметтермен толтырыңыз.

2.2.2 Келесі ережелерді сақтаңыз:

а) Біріншіден байланыстары ең аз кестелер толтырылады.

б) Категориялық бүтіндік ережесі: жатық жолдың ешқандай кілттік

атрибуты бос болмауы керек.

в) Сілтеме деңгейіндегі бүтіндік: әрбір сыртқы кілттің мәні не бос болуы, не басқа кестенің ағынды мәндерінің біреуіне тең болуы керек.

2.2.2 Students кестесіне Stud_STIP өрісін қосыңыз, бұл өрісте студенттердің стипендиялары туралы мәліметтер болады.

2.2.3 Students кестесіндегі Stud_STIP өрісін әртүрлі мәліметтермен толтырыңыз (UPDATE бұйрығын қолданыңыз).

2.2.3 Кестелердегі мәліметтерді өз қалауларыңыз бойынша өзгертіңіз.

2.2.4 2.3 Бақылау сұрақтары

2.3.1 Мәліметерге әрекеттерді қолданудың қандай бұйрықтарын білесіңдер.

2.3.2 Мәліметтердің бүтіндігінің анықтамасын беріңіз.

2.3.3 Сілтеме деңгейіндегі бүтіндіктің анықтамасын беріңіз.

2.3.4 Өрістің NOT NULL параметрі нені білдіреді?

2.3.5 Егерде өрістің параметрі NULL деп анықталса, бұл өріске міндетті түрде мәліметтер еңгізілуі керек пе?

2.3.6 Өрістің identity параметрі нені білдіреді? 3 лабораториялық жұмыс Мәліметтер қорына сұраныстар

3.1 Кестелерден мәліметтерді алу

Мәліметтер қорына қойылатын сұраныс мәліметтер диапазонын азайтып, оны берілген шарттарды қанағаттыратын тік және жатық жолдармен шектететін, сұрыптау операциясы болады.

Transact-SQL-де мәліметтерді сұрыптау үшін SELECT инструкциясы қолданылады. Әдетте, кестенің ізделінентін жалғызғана жатық немесе тік жол емес, бірақ барлық жолдары да емес. Сұраныс бойынша алынатын мәліметтердің көлемін азайту үшін SELECT бұйрығының күрделі инструкциялары қолданылады. Сұраныста кестелерді анықтау. Мәліметтерді сұрыптап алу үшін біріншіден керекті кестелерді атап кету керек. Ол үшін SELECT инструкциясында FROM сөйлемін қолдану крек.

Келесі SELECT инструкциясында “Students” кестесіндегі барлық мәліметтерді көрсету керек екендігін FROM сөйлемі орнатады:

SELECT *

FROM Students

SELECT инструкциясында қолданған жұлдызша (*) “Students” кестесіндегі барлық мәліметтерді қайтару керек деп түсіндіреді.

Тік жолдарды қолдану. SELECT инструкциясында мәліметтерді тігінінен немесе көлденен айырып шығаруға болады. Бірінші жағдайда қайтарылатын тік жолдар саны шектеледі, екінші жағдайда – сәйкес жатық жолдар.

Мәліметтер қорының кестелерінен қайтарылатын тік жолдар бір-бірінен үтірмен бөлініп тізім ретінде SELECT қызмет сөзінен кейін аталып кетеді: SELECT Stud_ID, Stud_FAM, Stud_IMA, Stud_ADDRESS

FROM Students

Бұл бұйрықта:

· SELECT – қызмет сөзі

· Stud_ID, Stud_FAM, Stud_IMA, Stud_ADDRESS - кестеден сұраныс бойынша таңдалынатын тік жолдардың тізімі. Бұл тізімде аталмаған басқа тік жолдар қарастырылмайды

· FROM – қызмет сөз, бұл сөзден кейін мәліметтердің көзі болатын кестелер аталып кетеді.

Таңдалынып алынған тік және жатық жолдар уақытша кестеге жиналады. Бұл кесте мәліметтер пайдаланушыға берілгенше өмір жасайды.

Кесте тік жолдарын бастапқы реті емес, басқа ретпен де көрсетуге болады:

SELECT Stud_ID, Stud_ADDRESS, Stud_FAM, Stud_IMA

FROM Students

Осындай мүмкіншілік реляционды мәліметтер қорларының сипаттарына сәйкес, себебі реляционды қорларда мәліметке қол жеткізу мәліметті сақтау әдісінен тәуелсіз. Кестенің бір тік жолын бірнеше жерде көрсетуге болады. Осындай мүмкіншілік кестені түсінуін жақсартады.

SELECT бұйрығы бойынша алынатын мәліметтер қайталанбауы үшін DISTINCT аргументі қолданылады. Мысалы, ең кемінде бір бағасы бар студенттердің тізімі керек болсын (бағалар әзір керек емес, тек қана студенттердің идентификаторлары):

SELECT Stud_ID

FROM Progress

Бұл бұйрық орындалса, идентификаторлары қайталануы мүмкін (кейбір студенттерде бірнеше баға бар). Қайталанбайтын тізімді алу үшін келесіні енгіземіз: SELECT DISTINCT Stud_ID

FROM Progress

SELECT бұйрығында DISTINCT тек қана бір рет көрсетіледі. Егерде бірнеше тік жол таңдалынса, DISTINCT таңдалынған өрістердің мәндері бірдей жазбаларды көрсетпейді. Кейбір мәндер бірдей, ал кейберуі - әртүрлі болған жазбалар қалады.

DISTINCT орнына ALL сөзін орнатуға болады, бұның әрекеті керісінше – жолдардың қайталануы сақталады (бұл жағдай ешқандай парамер көрсетілмегенге сәйкес).

Кесте жазбаларын шектеу. Кестелер өте үлкен болып кетуі мүмкін. Әдетте біздерге барлық жазбалар емес, белгілі жазбалар керек болады. Қандай жолдар таңдалынатынын критерийлерді орнатып анықтаймыз. Ол үшін SELECT бұйрығында WHERE қызмет сөзі қолданылады. Осы параметр көмегімен шарттар орнатылады. SELECT тек қана осы шарттарды қанағаттандыратын жолдарды көрсетеді. WHERE сөйлемінің синтаксисі:

WHERE тік_жол_аты салыстыру_операторы мән

WHERE сөйлемін қолданғанда SELECT бұйрығы нәтижелі жиынтықты азайтады. Ол жалғыз ғана жолды көрсетуі мүмкін немесе шарттарды қанағаттандыратын жолдар табылмаса, ештеңені көрсетпеуі мүмкін. Мысалы әкесінің аты 'Николаевич' болатын студенттердің тізімі ккрек болсын:

SELECT Stud_FAM, Stud_IMA

FROM Students

WHERE Stud_OTCH = ' Николаевич '

Егер де сұраныста WHERE сөзі болса, SQL Server кестенің әр жолын қарап, шарт орындалуын тексереді.

Ескерту. Символдық константалар (мысалы, 'Москва' сияқты) TransactSQL-де апострофпен ', немесе тырнақшамен " шектеледі.

WHERE сөйлемінде сандық өрісті пайдалануды көрейік. Стипендиялары 1200-ге тең студенттер тізімін алайық:

SELECT *

FROM Students

WHERE Stud_STIP = 1200

Мәліметтерді реттеу. Әдетте мәліметтерге реттеуді қолдану қажет. Реттеу ORDER BY қызмет сөзі көмегімен орнатылады (келісу бойынша реттеу өсу тәртібімен өткізіледі). ORDER BY сөйлемінің синтаксисі: ORDER BY тік_жол_аты | Бүтін_мән [ACS | DESC]

Мысалы, студенттер тізімін алфавит бойынша реттеп шығарайық;

SELECT Stud_ID, Stud_FAM

FROM Students

ORDER BY Stud_FAM

Егер де тік жол атынан кейін тағы бір тік жолдың атын орнатсақ, екінші тік жол бойынша бірінші тік жолда бірдей мәндері бар жатық жолдар реттеледі. ORDER BY сөзден кейін орнатылған тік жол орнына оның тізімдегі орын санын орнатуға болады (Бүтін_мән):

SELECT Grup_ID, Stud_ID, Stud_FAM

FROM Students

ORDER BY 1, 3 DESC

3.2 Мәліметтерді таңдауға шарттарды орнату

Күрделі таңдау шарттарды құрастыру үшін Transact-SQL-де қатынас, логикалық және арнайы операторлар бар.

Қатынас операторлары:

· = Тең

· > Үлкен

· < Кіші

· >= Үлкен немесе тең

· <= Кіші немесе тең

· <> Тең емес

· Мысалы, 1200-ден артық стипендиялары бар студенттер тізімін алайық:

· SELECT *

· FROM Students

· WHERE Stud_STIP>=1200

Логикалық операторлар:

- AND логикалық "ЖӘНЕ"

- - OR логикалық "ЯҒНИ"

- - NOT логикалық "ЖОҚ"

- AND операторы екі логикалық мәндерді салыстырады, егер де екеуі де ақиқат болса, TRUE мәнін қайтарады, басқа жағдайларда - FALSE мәні қайтарылады. Егер де аргументтердің біреуі TRUE болса, OR операторы TRUE мәнін қайтарады. Егер де оператор аргументі FALSE болса, NOT операторы TRUE мәнді қайтарады, кері жағдайда – керісінше.

- Логикалық операторлар SELECT бұйрығының мүмкіншіліктерін көбейтеді. Егер де сұраныс нәтижесінде берілетін жазбалар WHERE сөйлемде берілген шарттардың екеуінде қанағаттандыру керек болса, AND операторын (логикалық ЖӘНЕ) қолдану дұрыс:

- SELECT *

- FROM Students

- WHERE Stud_IMA = 'Алексей'

- AND

- Stud_ADDRESS = ‘Таугуль’

- Кестенің Stud_IMA тік жолында мәндері 'Алексей' болатын екі жатық жол болса, AND логикалық операторы Stud_ADDRESS тік жолында мәні ‘Таугуль’ болатын жолды таңдайды (яғни екі Алексейден Таугулде тұратын Алексейді).

- OR операторы көмегімен бір тік жолдан бірнеше мәндерді таңдауға болады:

- SELECT *

FROM Students

WHERE Stud_IMA = 'Алексей' OR

Stud_IMA = 'Николай' NOT операторы салыстыру операторларда тік жол атының алдында орнатылады:

SELECT *

FROM Students

WHERE NOT Stud_ADDRESS = ‘Таугуль’

Бұл сұраныс Students кестесінен Таугүлде тұрмайтын студенттер туралы мәліметтерді көрсетеді.

Өрнектерді топтастыру үшін жақшаларды қолдануға болады:

SELECT *

FROM Students

WHERE NOT (Stud_IMA = 'Алексей' OR

Stud_IMA = 'Николай')

IN, BETWEEN, LIKE, IS NULL арнайы операторлары

IN операторы өрістің мәндерінің тізімін белгілейді:

SELECT *

FROM Students

WHERE Stud_IMA IN ('Алексей' , 'Николай')

BETWEEN операторы IN операторына ұқсас. Бырақ ол рұқсат етілген тізімді белгілемейді, мәндер диапазонын белгілейді. Сұраныста BETWEEN сөзінен кейін бастапқы мән, содан кейін AND сөзі және де соңғы мәні көрсетіледі. Бірінші мән екінші мәннен кем болуы керек. Мысалы, келесі сұраныс бағалары 3 пен 5 арасындағы студенттердің тізімін береді:

SELECT *

FROM Progress

WHERE Ocenka BETWEEN 3 AND 5

LIKE операторы тек қана символдық өрістерге қолданылады. Бұл оператор шарттағы символдармен өрістің символдарының бөлігі сәйкес келетіндігін салыстырады. Шарт ретінде келесі арнайы символдар қолданылады:

а) астынан сызып қою символы _ - бұл белгі кез келген жалғыз символды орынын басады.

Мысалы, 'к_т' дегеніміз 'кот' және 'кит' деген сөздерді көрсету мүмкін, бырақ 'крот' дегенді көрсетпейді;

б) процент белгісі % - кез келген символдар тізбегін көрсетеді. Мысалы, 'компьютер' және 'омар' деген сөздерге '%м%р' деген символдар тізбегі сәйкес болады. Аттары 'О' әрібінен басталатын студенттер тізімін алу үшін, келесіні енгіземіз:

SELECT *

FROM Students

WHERE Stud_IMA LIKE 'О%'

Егер де мәндердің дәл символдарын білмесеңіз LIKE операторын қолдану ыңғайлы. Есіңізде бар символдар қатарын қолдануға болады.

NULL сөзі мәннің жоқтығын көрсетеді. Тік жолда NULL мәндері бар жазбаларды айыру үшін арнайы IS NULL операторын қолдану қажет. Мысалы, мәліметтер қорынан Stud_STIP тік жолында NULL мәндері бар жазбаларды табайық:

SELECT *

FROM Students

WHERE Stud_STIP IS NULL

3.3 Мәліметтерді агрегатты функциялармен қорытындылау

Агрегатты функциялар мәндердің жиынтығын бір мәнге келтіреді. SQL Server бірнеше агрегатты функцияларды қолдануға мүмкіншілік береді:

COUNT – сұраныс шартын қанағаттыратын жолдар санын табады

SUM – тік жолдың мәндерінің арифметикалық қосындысын есептейді

AVG – барлық мәндердің орта арифметикалық мәнін есептейді

MAX – таңдалынған мәндер ішінде максималдысын табады

MIN - таңдалынған мәндер ішінде минималдысын табады

SUM және AVG функциялары тек сандық өрістерге қолданылады. COUNT, MAX, MIN функцияларын символдық және сандық өрістерге қолдануға болады. MAX, MIN функцияларын символдық өрістерге қолданғанда, мәндер алфавит ретінде салыстырылады. Агрегаттық функциялар NULL мәндерді есепке алмайды.

Мысалы, студенттердің стипендияларының орта мәнін табу үшін, келесі сұранысты қолдануға болады: SELECT AVG (Stud_STIP)

FROM Students

COUNT функциясы басқаларынан өзгеше. Бұл функция берілген тік жолдағы мәндер санын яғни кестенің жатық жолдар санын есептейді. Мысалы, сабақтарды тапсырған студенттер санын есептейік: SELECT COUNT( DISTINCT Stud_ID)

FROM Progress

Бұл мысалда DISTINCT қызмет сөзі қолданылады: Progress кестесінің Stud_ID тік жолындағы мәндердің қайталанбайтын сандары есептелінеді. Егер де бұл сөз орнатылмаса, нәтиже өзгереді. Егер кестедегі жатық жолдардың жалпы мәнін есептеу керек болса, COUNT функциясын қолданғанда, өріс аты орнына жұлдызшаны орнатыңыз:

SELECT COUNT (*)

FROM Students

Агрегаттық функцияны қолданғанда мәндерді топтастыру үшін GROUP BY сөйлемін орнатады. Бұл мүмкіншілік агрегатты функциямен өрістерді бір SELECT сөйлемінде ұйымдастыруға мүмкіншілік береді. Мысалы, үлгеру ведомостан минималды бағалары бар студенттердің номерлерін анықтау қажет болсын: SELECT Stud_ID, MIN(ocenka )

FROM Progress

GROUP BY Stud_ID

Тік жол аттары пайдаланушыға түсінікті болу үшін, тік жолдар үшін псевдонимдарды қолдануға болады:

SELECT Stud_ID, MIN(ocenka) AS MIN_OCENKA

FROM Progress

GROUP BY Stud_ID GROUP BY агрегатты функциясы жазбалар топтарына қолданылады. Топты ұйымдастыру шарты - өрістің бірдей мәндері (қарастырып отырған мысалда - Stud_ID). Сұранысты өзгертейік:

SELECT Stud_ID, Pr_Date, MAX(ocenka )

FROM Progress

GROUP BY Stud_ID, Pr_DATE

Бұл сұраныс бойынша, студенттердің номері мен олардың әрбір датасында алған максималды бағалары табылады. Бағалар қойылмаған күндер көрсетілмейді.

HAVING қызмет сөзі SELECT операторында GROUP BY сөзбен бірге қолданылады. GROUP BY үшін HAVING қызметі ORDER BY үшін WHERE сөздің қызметі сияқты. Мысалы, әр студент үшін, оның 4-тен артық бағаларының максималдысын табуымыз керек болсын. Ол үшін, HAVING сөзін қолданамыз, бұл сөз сұраныстың шартын орнатады (бөлек жолдар үшін WHERE сөзі сияқты): SELECT Stud_ID, Pr_DATE, MAX (Ocenka )

FROM Progress

GROUP BY Stud_ID, Pr_DATE

HAVING MAX (Ocenka ) > 4

Сонымен, агрегатты функциялар мәліметтер қорынан тек қана белгілі мәндер табу емес, оларды қорытындылауға, анализдеуге мүмкіншілік береді.

3.4 Лабораториялық жұмысқа тапсырма

Тапсырманы жасау алдында 3.1-3.3 бөліктердегі келтірілген мысалдарды орындаңыз. Бөлек кестелерден келесі мәліметтерді табыңыз:

3.4.1 Алфавит ретінде мұғалімдер тізімі мен олардың қызметтерін.

3.4.2 Кафедралдың атын, меңгерушісінің аты-жөнімен.

3.4.3 Бірінші тобындағы фамилиялары қайталанбайтын студенттер тізімін (бұл тобында фамилиялары бірдей студенттер бар деп есептелінеді).

3.4.4 Стипендиясы 2000-нан артық студенттер тізімін.

3.4.5 Астана мен Қарағандыда тұратын студенттер тізімін.

3.4.6 Екінші тобындағы стипендия алмайтын студенттер тізімін.

3.4.7 Үшінші тобындағы фамилиялары А әрібінен басталатын студенттер тізімін.

3.4.8 1986 жылы туған студенттер тізімін.

3.4.9 Үшінші тобындағы студенттердің стипендиясының жалпы қосындысын есептеңіз.

3.4.10 Барлық пәндерден лекциялық сабақтардың орта мәнін есептеңіз.

3.4.11 Мәліметтер қорында неше студентер бар?

3.4.12 Пәндер туралы барлық мәліметтерді шығарыңыз.

3.4.13 Алматыда тұрмайтын студенттер тізімін.

3.4.14 Мамыр айында туған студенттер тізімін.

3.4.15 Үлгеру ведомостан минималды бағалары бар студенттер тізімін.

3.4.16 Үлгеру ведомостан максималды бағалары бар студенттер тізімін.

3.5 Бақылау сұрақтары

3.5.1 SELECT операторының құрамында міндетті түрде көрсетілетін сөздерін атаңыз.

3.5.2 WHERE қызмет сөзінің өрнегінде тырнақша барлық мәліметтерге орнатыла ма?

3.5.3 SELECT операторы SQL-дің қай бөлігіне қатынасады?

3.5.4 WHERE сөздің өрнегінде бір неше шартты орнатуға бола ма?

3.5.5 Тырнақшаны сандық өрістердің мәндеріне қолдануға бола ма?

3.5.6 COUNT функцияны қолданғанда, мәліметтер типі әсер ете ме?

3.5.7 Мәліметтерді тік жол бойынша топтастыру үшін, тік жол аты SELECT

сөздің тізімінде көрсетілуі керек пе?

3.5.8 SELECT сөйлемінде ORDER BY сөзін қолданғанда, GROUP BY сөзін қолдану қажет пе?

4 лабораториялық жұмыс Күрделі сұраныстарды жасау

4.1 Сұраныстарда кестелерді ұйымадастыру

Осыған дейін сұраныстар тек қана бір кестемен жұмыс жасаған. Бірақ SQL бір сұраныста бірнеше кестені қолдануға мүмкіндік береді. Осындай сұраныстарда әр тік жолдың аты мен бірге оларға сәйкес кестелердің аттары аталады. Мұндай жағдай тік жолды сұраныста толық анықтау деп аталады. Толық анықтаулар сұраныста аталған бірнеше кестеде орнатылған тік жолдар үшін керек. Бірақ әдетте барлық тік жолдар үшін толық анықтаулар қолданылады. Мысалы:

SELECT Teacher.Teach_FAM, Chair.Chair_NAME

FROM Teacher, Chair

WHERE Teacher.Chair_ID = Chair.Chair_ID

Бұл оператор мұғалімдердің фамилияларымен олардың жұмыс орнын көрсетеді. Мұғалім идентификаторы екі қолданылған кестеде бар, олар эквивалентті деп есептелінеді (оны теңдікпен орнатамыз). Келесі сұраныста Chair_ID өрісі екі кестеде бар, бірақ тек қана Teacher кестесінен алынады, себебі екінші кестедегі сәйкес өрістен бірдей мәліметтер:

SELECT Teacher.*, Chair.Chair_NAME

FROM Teacher, Chair

WHERE Teacher.Chair_ID = Chair.Chair_ID

Кесте аттары үшін псевдонимдарды қолдану. Клавиатурадан теру жұмысын азайту үшін жәнеде кестелерді рекурсивті тәртібі бойынша байланыстыру үшін, кестелерге псевдоним аттарды беруге болады. Мысалы: SELECT A.Teach_ID, B.Chair_NAME

FROM Teacher A,

Chair B

WHERE A.Chair_ID = B.Chair_ID

Керекті мәліметтер бір кестеде орнатылса, ал кестенің жазбаларын бір бірімен салыстыру қажет болса, онда кестелерге рекурсивті байланыстыруды қолдану ыңғайлы. Біздің мәліметтер қорымызда Students кестесінде студенттердің фамилияларымен олардың старосталарының номерлері орнатылған. Студенттер тізімін олардың старосталарының фамилияларымен бірге шығарайық:

SELECT A.Stud_FAM, B.Stud_FAM

FROM Students A,

Students B

WHERE A.Stud_Star = B.Stud_ID

Бірнеше кілттер бойынша байаныстыру. Біздерге студенттердің әртүрлі пәндерден алған бағалары керек болсын:

SELECT Students. Stud_FAM, Subject.Subj_NAME, Progress.Оcenka

FROM Students, Subject, Progress

WHERE Students.Stud_ID = Progress.Stud_ID AND

Subject.Subj_ID = Progress.Subj_ ID

SQL Server екі кестенің жатық жолдарының әрбір комбинацияларын тексеріп, WHERE сөйлемінде көрсетілген шартты қанағаттандыруын тексереді. Егер де шарт қанағаттандырылса, комбинация шығарылады. Мысалы, Сериков мұғаліміне жақсы бағамен тапсырған студенттер тізімі керек болсын. Ол үшін төрт кестені байланыстыру қажет:

SELECT Students.Stud_FAM, 'по предмету ', Subject.Subj_NAME,

'получили оценку ', Progress.OCENKA,

'у преподавателя ', Teacher.Teach_FAM

FROM Students, Subject, Progress, Teacher

WHERE Students.Stud_ID = Progress. Stud_ID

AND

Teacher.Teach_ID = Progress.Teach_ID AND

Subject.Subj_ID = Progress.Subj_ID AND

Progress.Оcenka <>2 AND

Teacher.Teach_FAM = 'Сериков '

4.2 Сұраныс ішіндегі сұраныстарды қолдану

Сұраныстар басқа сұраныстарды басқара алады. Ол үшін бір сұраныс шартының ішіне басқа сұранысты орнатып, ішкі сұраныс нәтижелерін шартта қолдануға болады.

Әдетте ішкі сұраныс сыртқы сұраныс шартында тексерілетін мәндерді құрастырады. Мысалы, біз студент фамилиясын – Сидоровты білеміз, бірақ оның нөмірін (Stud_ID) білмегенмен Progress кестесінен оның барлық бағаларын алғымыз келеді:

SELECT *

FROM Progress

WHERE Stud_ID = (

SELECT Stud_ID

FROM Students

WHERE Stud_FAM = 'Сидоров' )

Сыртқы (негізгі) сұраныс жасау алдында, WHERE сөйлем ішіндегі ішкі сұраныс орындалады. Ішкі сұраныста Students кестесі қарастырылып, одан Stud_FAM өрісі 'Сидоров' мәнге тең жатық жолдар алынып, алынған жолдардың Stud_ID өрістерінің мәндері алынады. Мысалы, жалғыз ғана жол Stud_ID = 30104 алынған болсын. Алынған мән негізгі сұраныстың шартына ішкі сұраныс орнына орнатылады, сонымен шарт келесідей болады:

WHERE Stud_ID = 301004

Салыстыру операциялар негізінде орнатылған шарттарда ішкі сұраныстарды қолданғанда, ішкі сұраныс жалғыз ғана мәнді қайтаратынына көз жеткізу керек. Егер де бірнеше жазбаны қайтаратын ішкі сұранысты қолдану қажет болса, IN операторы орнатылады. Мысалы, мәліметтер қорында Сидоров фамилиясы бар бірнеше студент болса, сұраныс келесідей болады:

SELECT *

FROM Progress

WHERE Stud_ID IN (

SELECT Stud_ID

FROM Students

WHERE Stud_FAM = 'Сидоров' )

Информатика пәнінен барлық бағаларды шығарайық:

SELECT *

FROM Progress

WHERE Subj _ID IN (

SELECT Subj_ID

FROM Subject

WHERE Subj_NAME = 'Информатика' )

Бұл нәтижені ұйымдастыруды қолданып алуға да болады:

SELECT Progress.*

FROM Subject, Progress

WHERE Subject.Subj_ID = Progress.Subj_ID AND

Subject.Subj_NAME = 'Информатика'

Келтірілген ішкі сұраныстарда тек қана жалғыз тік жолы қолданылады. SELECT * сияқты бұйрықтарды ішкі сұраныстарда қолдануға болмайды.

Ішкі сұраныстарды HAVING сөйлемінде қолдану болады. Бұл ішкі сұраныстар өзінің GROUP BY немесе HAVING сөйлемдерін қолдануы мүмкін. Мысалы:

SELECT Ocenka, COUNT (DISTINCT Stud_ID )

FROM Progress

GROUP BY Ocenka

HAVING Ocenka > (

SELECT AVG(Ocenka )

FROM Progress

WHERE Pr_DATE >’ 04/06/02’ )

Бұл бұйрық бойынша емтиханды 04.06.02 кейін тапсырған бағалары орта бағадан үлкен студенттер тізімі көрсетіледі.

Байланысқан ішкі сұраныстар. Ішкі сұраныстан сыртқы сұраныстағы кестені қолдануға болады. Мысалы, емтиханды 3 маусымнан кейін тапсырған студенттерді табу керек болсын: SELECT *

FROM Students C

WHERE '2004-06-03' IN (

SELECT Pr_DATE

FROM Progress O

WHERE O.Stud_ID = C.Stud_ID )

Осы нәтижені алу үшін ұйымдастыруды қолдануға да болады:

SELECT C.*

FROM Students C, Progress O

WHERE C.Stud_ID = O.Stud_ID AND

O. Pr_DATE = '2004.06.03'

Кестені өзі-өзімен салыстыру үшін байланысқан ішкі сұраныстарды қолдануға болады. Мысалы, бағалары орта мәннен артық студентерді табу үшін келесіні орнатамыз: SELECT *

FROM Progress O

WHERE Оcenka > (

SELECT AVG(ocenka )

FROM Progress O1

WHERE O1.Stud_ID = O.Stud_ID )

EXISTS операторын қолдану. EXISTS операторы ішкі сұранысты аргумент ретінде қолданып, ол жазбаларды тапса, оны ақиқат деп, кері жағдайда ақиқат емес деп есептейді. Мысалы, мәліметтер қорында стипендиялары 1200 тең студенттер бар екендігін тексерейік: SELECT Stud_ID , Stud_FAM, Stud_STIP

FROM Students

WHERE EXISTS (

SELECT *

FROM Students

WHERE Stud_STIP = 1200 )

Ішкі сұраныс стипендиялары 1200 тең студенттерді тауып, олар туралы барлық мәліметтерді шығарады. Сыртқы сұраныста EXISTS операторы ішкі сұраныс мәліметтерді тапты, сондықтан шарт ақиқат деп есептейді. Ішкі сұраныс бір рет орындалады, оның жалғыз мәні бар.

ANY, ALL операторларды қолдану. Емтихандарды тапсырған студенттерді іздеудің тағы бір амалын қарастырайық: SELECT *

FROM Students

WHERE Stud_ID = ANY (

SELECT Stud_ID

FROM Progress )

ANY операторы ішкі сұраныспен шығарылған барлық мәндерді алып, егер де олардың кез-келген мәні сыртқы сұраныстың ағынды жолындағы мәнге тең болса, оларды ақиқат деп есептейді. Келтірілген сұраныста IN операторында қолдануға болар еді.

Егер де ішкі сұраныспен таңдалынған мәндердің әр қайсысы сыртқы сұраныстың шартын қанағаттандырса, ALL операторы шартты ақиқат деп есептейді. Стипендиялары 1500-ден көп студентер тізімін шығарайық: SELECT *

FROM Students

WHERE Stud_STIP > ALL(

SELECT Stud_STIP

FROM Students

WHERE Stud_STIP = 1500 )

UNION бұйрығын пайдалану. UNION бұйрығы бірнеше сұраныстардың нәтижелерін біріктіреді және қайталанатын жолдарды жояды. Мысалы, төменде келтірілген сұраныс студенттер мен мұғалімдердің К және С әріптер арасында орналасқан фамилияларын шығарады:

SELECT Stud_FAM

FROM Students

WHERE Stud_FAM BETWEEN 'К' AND 'С'

UNION

SELECT Teach_FAM

FROM Teacher

WHERE Teach_FAM BETWEEN 'К' AND 'С'

UNION бұйрығын қолдану үшін екі талап бар:

- тік жолардың саны мен реті барлық сұраныста бірдей болуы керек;

- мәліметтер типтері сәйкес болуы керек.

- UNION қайталанатын жазбаларды көрсетпейді. Егер де сұраныстың барлық жатық жолдары керек болса UNION ALL бұйрықты қолдану керек: