1-тажриба машгулоти

Тajriba ishi №7
Mavzu: Ma`lumotlar bazasini yuritish dasturini yaratish.


Maqsad: Talabalarda tanlangan topshiriq mavzusi bo`yicha MB ni yuritish dasturini yaratish haqida bilim va malakalarini mustahkamlash.
Vazifa: Talabalar mustaqil ravishda quyidagilarni bajarishi, ular haqida malaka va ko`nikmaga ega bo`lishi lozim.
1. MB ni yaratish. (Oracle misolida).
Jihozlar: Kompyuter.
Uslubiy ko`rsatma:
SQL ni tasnifi bo`yicha ma`lumotlar ma`ruzada keltirib o`tilgan. Biz, quyida ularning eng muhimlarini yana qayta takroran bayon qilamiz.
Nomlar. MB dagi har bir ob`ektning takrorlanmas nomi bor. Nomlar SQL operatorlarida ishlatiladi va operator MB dagi qaysi ob`ekt ustida amallar bajarishi lozimligini ko`rsatadi. ANSI standartida jadvallar, ustunlar va foydalanuvchilar nomga ega bo`lishi aniqlangan. Ammo, ko`pgina SQL ni qo`llab quvvatlovchi MBBT larida “birlamchi kalit—ikkilamchi kalit” munosabatlari, protsedura, funktsiya, paket va trigger kabi ob`ektlar ham nomlanadi.
Jadval nomlari. Agarda operatorlarda jadval nomi ko`rsatilgan bo`lsa, SQL bu operatorlar orqali tuzilgan jadvalga murojaat qiladi.
Ustun nomlari. Agarda operatorga ustun nomi berilsa, SQL bu operatorda ko`rsatilgan jadvallarning qaysi birida ushbu ustun mavjudligini o`zi aniqlaydi. Ammo, agarda operatorda turli jadvallarning bir xil nomdagi ikkita ustuni ishlatilsa, u holda bu ustunlarni to`liq nomidan foydalanishimizga to`gri keladi. Ustunning to`liq nomi nuqta bilan ajratilgan ustunni o`zida saqlovchi jadval nomi va ustun nomi (oddiy nomi)dan iborat. Masalan, ATALABA jadvalining FAM ustuni to`liq nomi quyidagicha aniqlanadi: ATALABA. FAM.
Ma`lumotlar tipi. Axborotlarni relyatsion MBga o`tkazish uchun ANSI standartida ma`lumotlar tiplari aniqlanadi.
SQL qo`llaniladigan turli MBBT larida turli qo`shimcha tiplarni qo`llanilishi, dasturlarning bir MBBT dan boshqasiga ko`chirilishiga xalaqit beradi. SHuning uchun imkon darajasida standart tiplarni ishlatish muvofiq hisoblanadi.
‘Talabalar demografik ma`lumotlari’ MB va uning jadvallarini SQL tilida yaratish.
1. D:\TALABA\ katalogda ‘Talabalar demografik ma`lumotlari’ MB ni SQL tilida yaratish dasturi quyidagicha:
Create database ‘D:\Talaba\Talaba-demografik.gdb’
file ‘D:\Talaba\Talaba-demografik.gd1’ starting at page 1001
length 500
file ‘D:\Talaba\Talaba-demografik.gd2’.
2. ‘Talabalar demografik ma`lumotlari’ MB jadvallarini SQL tilida yaratish dasturi quyidagicha:
-- 1 Jins
CREATE TABLE sjins
(id CHAR(1) not null,
jins CHAR(7) not null,
CONSTRAINT P_sjins PRIMARY KEY (id));
-- 2 Til, millat
CREATE TABLE stil
(id INTEGER not null,
lkod CHAR(3),
lname VARCHAR2(10) not null,
mnom VARCHAR2(30),
CONSTRAINT P_stil PRIMARY KEY (id));
-- 3. YAshash holati
CREATE TABLE syhol
(id INTEGER not null,
tjoy VARCHAR2(20) not null,
CONSTRAINT P_ syhol PRIMARY KEY (id));
-- 4 Ijtimoiy tabaqalar
CREATE TABLE sijt
(id INTEGER not null,
ijtnom VARCHAR2(20) not null,
CONSTRAINT P_ sijt PRIMARY KEY (id));
-- 5 Oilaviyligi
CREATE TABLE soa
(id INTEGER not null,
oila VARCHAR2(15) not null,
CONSTRAINT P_ soa PRIMARY KEY (id));
-- 6 Harbiy xizmatga aloqadorlik turi
CREATE TABLE sharb
(id INTEGER not null,
hnom VARCHAR2(20) not null,
CONSTRAINT P_ sharb PRIMARY KEY (id));
-- 7 Nogironlik
CREATE TABLE snogiron
(id INTEGER not null,
nog VARCHAR2(40) not null,
CONSTRAINT P_ snogiron PRIMARY KEY (id));
-- 8 Etim
CREATE TABLE setim
(id INTEGER not null,
etim VARCHAR2(20) not null,
CONSTRAINT P_ setim PRIMARY KEY (id));
-- 9 Fakul’tet
CREATE TABLE sfak
(id INTEGER not null,
fnom VARCHAR2(40) not null,
CONSTRAINT P_ sfak PRIMARY KEY (id));
-- 10 Mutaxassislik
CREATE TABLE smut
(id INTEGER not null,
fnom INTEGER not null,
mnom VARCHAR2(50) not null,
CONSTRAINT P_smut PRIMARY KEY (id),
CONSTRAINT F_smut FOREIGN KEY (fnom) REFERENCES sfak(id));
-- 11 Guruh
CREATE TABLE sgur
(id INTEGER not null,
mnom INTEGER not null,
kurs INTEGER not null,
guruh VARCHAR2(10) not null,
CONSTRAINT P_sgur PRIMARY KEY (id),
CONSTRAINT F_sgur FOREIGN KEY (mnom) REFERENCES smut(id));
-- 12 Ta`lim bosqichi (O`qitish darajasi)
CREATE TABLE sudar
(id CHAR(1) not null,
udsh CHAR(2),
udnom VARCHAR2(20) not null,
uqy INTEGER DEFAULT 4 not null,
CONSTRAINT P_sudar PRIMARY KEY (id));
-- 13 O`qish shakli
CREATE TABLE suqsh
(id CHAR(1) not null,
unom VARCHAR2(20) not null,
uq CHAR(1) DEFAULT ‘Y’ not null,
CONSTRAINT P_suqsh PRIMARY KEY (id).
CONSTRAINT CH_uq CHECK (uq in ('N','Y'));
-- 14 Qarindosh
CREATE TABLE sqar
(id INTEGER not null,
qar VARCHAR2(10) not null,
CONSTRAINT P_ sqar PRIMARY KEY (id));
-- 1 Talaba
CREATE TABLE atalaba
(id INTEGER not null,
fam VARCHAR2(25) not null,
oism VARCHAR2(15),
oism VARCHAR2(20),
guruh INTEGER not null,
CONSTRAINT P_atalaba PRIMARY KEY (id),
CONSTRAINT F_atalaba-g FOREIGN KEY (guruh) REFERENCES sgur(id));
-- 2 Umumiy ma`lumot
CREATE TABLE aumt
(id INTEGER not null,
jins CHAR(1) not null,
tsan DATE not null,
mil INTEGER not null,
CONSTRAINT P_aumt PRIMARY KEY (id),
CONSTRAINT F_aumt FOREIGN KEY (id) REFERENCES atalaba(id)
ON DELETE CASCADE,
CONSTRAINT F_aumt-jins FOREIGN KEY (jins) REFERENCES sjins(id),
CONSTRAINT F_aumt-til FOREIGN KEY (millat) REFERENCES stil(id));
-- 3 SHaxsiy ma`lumot
CREATE TABLE ashmt
(id INTEGER not null,
ijt INTEGER not null,
oila INTEGER not null,
bola INTEGER DEFAULT 0 not null,
harb INTEGER not null,
nog CHAR(1) DEFAULT ‘N’ not null,
etim CHAR(1) DEFAULT ‘N’ not null,
CONSTRAINT P_ashmt PRIMARY KEY (id),
CONSTRAINT F_ashmt FOREIGN KEY (id) REFERENCES atalaba(id)
ON DELETE CASCADE,
CONSTRAINT F_ashmt-o FOREIGN KEY (oila) REFERENCES sijt(id),
CONSTRAINT F_ashmt-h FOREIGN KEY (harb) REFERENCES sharb(id),
CONSTRAINT CH_ashmt-n CHECK (nog in ('N','Y'),
CONSTRAINT CH_ashmt-e CHECK (etim in ('N','Y'));
-- 4 Muassasadagi ma`lumot
CREATE TABLE akm
(id INTEGER not null,
tb CHAR(1) not null,
ut INTEGER not null,
ukod INTEGER not null,
qyil DATE not null,
rd INTEGER,
ku CHAR(1) DEFAULT ‘Y’ not null,
ka CHAR(1) DEFAULT ‘Y’ not null,
CONSTRAINT P_akm PRIMARY KEY (id),
CONSTRAINT F_akm FOREIGN KEY (id) REFERENCES atalaba(id)
ON DELETE CASCADE,
CONSTRAINT F_akm-t FOREIGN KEY (tb) REFERENCES sudar(id),
CONSTRAINT F_akm-u FOREIGN KEY (ut) REFERENCES til(id),
CONSTRAINT F_akm-s FOREIGN KEY (ukod) REFERENCES suqsh(id),
CONSTRAINT CH_akm CHECK (ku in ('N','Y'),
CONSTRAINT CH_akm-k CHECK (ka in ('N','Y'));
-- 5 Pasport
CREATE TABLE apasp
(id INTEGER not null,
sp VARCHAR2(20) not null,
bs DATE not null,
as DATE not null,
ib VARCHAR2(30) not null,
in VARCHAR2(10),
manz VARCHAR2(50) not null,
CONSTRAINT P_apasp PRIMARY KEY (id),
CONSTRAINT F_apasp FOREIGN KEY (id) REFERENCES atalaba(id)
ON DELETE CASCADE));
-- 6 Telefon
CREATE TABLE atel
(id INTEGER not null,
tel VARCHAR2(30) not null,
CONSTRAINT P_atel PRIMARY KEY (id),
CONSTRAINT F_atel FOREIGN KEY (id) REFERENCES atalaba(id)
ON DELETE CASCADE));
-- 7 Manzil
CREATE TABLE amanz
(id INTEGER not null,
tjoy INTEGER not null,
manz VARCHAR2(50) not null,
CONSTRAINT P_amanz PRIMARY KEY (id),
CONSTRAINT F_amanz FOREIGN KEY (id) REFERENCES atalaba(id)
ON DELETE CASCADE,
CONSTRAINT F_amanz-t FOREIGN KEY (tjoy) REFERENCES syhol(id));
-- 8 Ota-ona
CREATE TABLE aqar
(id INTEGER not null,
tart INTEGER not null,
qar INTEGER not null,
fam VARCHAR2(50) not null,
ishj VARCHAR2(30),
lav VARCHAR2(20),
tel VARCHAR2(20),
manz VARCHAR2(50) not null,
CONSTRAINT P_aqar PRIMARY KEY (id,tart),
CONSTRAINT F_aqar FOREIGN KEY (id) REFERENCES atalaba(id)
ON DELETE CASCADE,
CONSTRAINT F_aqar-q FOREIGN KEY (qar) REFERENCES sqar(id));
-- 9 Etim
CREATE TABLE aetim
(id INTEGER not null,
etim INTEGER not null,
CONSTRAINT P_etim PRIMARY KEY (id),
CONSTRAINT F_etim FOREIGN KEY (id) REFERENCES atalaba(id)
ON DELETE CASCADE,
CONSTRAINT F_etim-e FOREIGN KEY (etim) REFERENCES setim(id));
-- 10 Nogiron
CREATE TABLE anogiron
(id INTEGER not null,
nogiron INTEGER not null,
CONSTRAINT P_anogiron PRIMARY KEY (id),
CONSTRAINT F_anogiron FOREIGN KEY (id) REFERENCES atalaba(id)
ON DELETE CASCADE,
CONSTRAINT F_an FOREIGN KEY (nogiron) REFERENCES snogiron (id));
Topshiriq
1. MB ni yarating. (Oracle misolida).
Xulosa: Bajargan ishlaringizni batafsil bayon qiluvchi xulosa yozing.