000. Sxemalar va tayyorlov

Masalalar to'rtta namuna sxema ustida tuzilgan. Har bir sxema alohida PostgreSQL schema'si bo'lib, 5–8 ta jadvaldan iborat va tashqi kalitlar (FOREIGN KEY) bilan bog'langan. Ma'lumotlar deterministik: har safar bir xil qatorlar hosil bo'ladi.

PostgreSQL'ni ishga tushirish

Eng oson yo'li — Docker. Quyidagi buyruq PostgreSQL 17 konteynerini ishga tushiradi:

docker run -d --name pgmasala -e POSTGRES_PASSWORD=pg -p 5433:5432 postgres:17

Konteyner ishga tushgach, masala nomli ma'lumotlar bazasini yarating:

docker exec pgmasala psql -U postgres -c "CREATE DATABASE masala"

Konteyner mavjud bo'lsa, uni docker start pgmasala bilan yoqing. Bazaga ulanish:

docker exec -it pgmasala psql -U postgres -d masala

Sxemani yuklash

Quyida to'rtta sxemaning to'liq matni berilgan. Kerakli sxemani psql ichiga nusxalab joylashtiring. Har bir fayl boshida schema o'chirilib, qayta yaratiladi, shuning uchun uni istalgancha marta yuklash mumkin.

Masalani yechishdan oldin kerakli sxemani tanlang:

SET search_path = dokon;

Info

O'zgartiruvchi masalalarni (INSERT, UPDATE, DELETE, DDL) tranzaksiya ichida sinang va oxirida ROLLBACK qiling. Shunda sxema asl holatida qoladi.

Onlayn do'kon (dokon)

Jadvallar: kategoriya, mijoz, mahsulot, buyurtma, buyurtma_element, tolov.

DROP SCHEMA IF EXISTS dokon CASCADE; CREATE SCHEMA dokon; SET search_path=dokon;
CREATE TABLE kategoriya(id int PRIMARY KEY, nom text NOT NULL);
INSERT INTO kategoriya VALUES (1,'Elektronika'),(2,'Kiyim'),(3,'Oziq-ovqat'),(4,'Kitob'),(5,'Uy-ro''zg''or');
CREATE TABLE mijoz(id int PRIMARY KEY, ism text NOT NULL, shahar text NOT NULL, royxat_sana date NOT NULL, email text, tugilgan_yil int);
INSERT INTO mijoz SELECT i,
 (ARRAY['Ali','Vali','Sardor','Dilnoza','Madina','Jasur','Bekzod','Nargiza','Otabek','Zilola'])[i%10+1]||' '||(ARRAY['Karimov','Rahimova','Tursunov','Aliyev','Yusupova'])[i%5+1],
 (ARRAY['Toshkent','Samarqand','Buxoro','Andijon','Namangan','Fargona'])[i%6+1],
 DATE '2023-01-01'+ (i*13), CASE WHEN i%7=0 THEN NULL ELSE 'user'||i||'@mail.uz' END, 1970+(i*7)%35
FROM generate_series(1,30) i;
CREATE TABLE mahsulot(id int PRIMARY KEY, nom text NOT NULL, kategoriya_id int REFERENCES kategoriya(id), narx numeric(10,2) NOT NULL, qoldiq int NOT NULL);
INSERT INTO mahsulot SELECT i,
 (ARRAY['Telefon','Noutbuk','Futbolka','Jinsi','Non','Sut','Roman','Lugat','Choynak','Stul'])[(i-1)%10+1]||' '||i,
 ((i-1)%5)+1, 5000+ (i*7919)%995000/100*100, (i*17)%60
FROM generate_series(1,25) i;
CREATE TABLE buyurtma(id int PRIMARY KEY, mijoz_id int REFERENCES mijoz(id), sana date NOT NULL, holat text NOT NULL);
INSERT INTO buyurtma SELECT i, (i*7)%30+1, DATE '2024-01-01'+(i*5), (ARRAY['yangi','yetkazildi','bekor','yetkazildi'])[i%4+1] FROM generate_series(1,60) i;
CREATE TABLE buyurtma_element(id int PRIMARY KEY, buyurtma_id int REFERENCES buyurtma(id), mahsulot_id int REFERENCES mahsulot(id), miqdor int NOT NULL);
INSERT INTO buyurtma_element SELECT i, (i-1)/2+1, (i*11)%25+1, i%4+1 FROM generate_series(1,100) i;
CREATE TABLE tolov(id int PRIMARY KEY, buyurtma_id int REFERENCES buyurtma(id), summa numeric(12,2) NOT NULL, usul text NOT NULL);
INSERT INTO tolov SELECT i, i, 10000+(i*3571)%90000, (ARRAY['naqd','karta','click'])[i%3+1] FROM generate_series(1,45) i;

Maktab (maktab)

Jadvallar: oqituvchi, fan, sinf, oquvchi, baho, davomat.

DROP SCHEMA IF EXISTS maktab CASCADE; CREATE SCHEMA maktab; SET search_path=maktab;
CREATE TABLE oqituvchi(id int PRIMARY KEY, ism text NOT NULL, maosh numeric(10,2), ish_boshlagan date);
INSERT INTO oqituvchi SELECT i,(ARRAY['Anvar','Gulnora','Shuhrat','Malika','Rustam','Feruza'])[i%6+1]||' '||(ARRAY['Qodirov','Saidova','Nazarov'])[i%3+1], 3000000+i*250000, DATE '2005-09-01'+i*400 FROM generate_series(1,12) i;
CREATE TABLE fan(id int PRIMARY KEY, nom text NOT NULL, oqituvchi_id int REFERENCES oqituvchi(id));
INSERT INTO fan VALUES (1,'Matematika',1),(2,'Fizika',2),(3,'Kimyo',3),(4,'Ona tili',4),(5,'Ingliz tili',5),(6,'Tarix',6),(7,'Informatika',7),(8,'Biologiya',8);
CREATE TABLE sinf(id int PRIMARY KEY, nom text NOT NULL, rahbar_id int REFERENCES oqituvchi(id));
INSERT INTO sinf VALUES (1,'9-A',1),(2,'9-B',4),(3,'10-A',2),(4,'10-B',9),(5,'11-A',5);
CREATE TABLE oquvchi(id int PRIMARY KEY, ism text NOT NULL, sinf_id int REFERENCES sinf(id), tugilgan date NOT NULL, jins char(1) NOT NULL, telefon text);
INSERT INTO oquvchi SELECT i,(ARRAY['Aziz','Madina','Sherzod','Nilufar','Doniyor','Sevara','Timur','Laylo'])[i%8+1]||' '||(ARRAY['Ergashev','Karimova','Ismoilov','Hasanova'])[i%4+1], i%5+1, DATE '2008-01-15'+(i*37)%1000, CASE WHEN i%2=0 THEN 'E' ELSE 'A' END, CASE WHEN i%6=0 THEN NULL ELSE '+99890'||(1000000+i*12345)::text END FROM generate_series(1,40) i;
CREATE TABLE baho(id int PRIMARY KEY, oquvchi_id int REFERENCES oquvchi(id), fan_id int REFERENCES fan(id), ball int CHECK(ball BETWEEN 1 AND 5), sana date NOT NULL);
INSERT INTO baho SELECT i,(i*3)%40+1,(i*5)%8+1,(i*7)%5+1, DATE '2024-09-02'+(i*3)%120 FROM generate_series(1,150) i;
CREATE TABLE davomat(id int PRIMARY KEY, oquvchi_id int REFERENCES oquvchi(id), sana date NOT NULL, holat text NOT NULL);
INSERT INTO davomat SELECT i,(i*7)%40+1, DATE '2024-10-01'+(i%20), (ARRAY['keldi','keldi','kelmadi','kasal'])[i%4+1] FROM generate_series(1,80) i;

Bank (bank)

Jadvallar: filial, mijoz, hisob, tranzaksiya, qarz

DROP SCHEMA IF EXISTS bank CASCADE; CREATE SCHEMA bank; SET search_path=bank;
CREATE TABLE filial(id int PRIMARY KEY, nom text NOT NULL, shahar text NOT NULL);
INSERT INTO filial VALUES (1,'Markaziy','Toshkent'),(2,'Chilonzor','Toshkent'),(3,'Registon','Samarqand'),(4,'Buxoro filiali','Buxoro'),(5,'Andijon filiali','Andijon');
CREATE TABLE mijoz(id int PRIMARY KEY, ism text NOT NULL, tugilgan date, telefon text, filial_id int REFERENCES filial(id));
INSERT INTO mijoz SELECT i,(ARRAY['Jahongir','Shahzoda','Akmal','Dildora','Hamid','Kamola'])[i%6+1]||' '||(ARRAY['Boboyev','Normatova','Sobirov','Orifova'])[i%4+1], DATE '1960-01-01'+(i*397)%12000, CASE WHEN i%8=0 THEN NULL ELSE '+99893'||(2000000+i*7777)::text END, i%5+1 FROM generate_series(1,30) i;
CREATE TABLE hisob(id int PRIMARY KEY, mijoz_id int REFERENCES mijoz(id), turi text NOT NULL, balans numeric(14,2) NOT NULL, ochilgan date NOT NULL, valyuta char(3) NOT NULL DEFAULT 'UZS');
INSERT INTO hisob SELECT i,(i*3)%30+1,(ARRAY['jamgarma','joriy','depozit'])[i%3+1], (i*1234567)%50000000, DATE '2020-01-01'+i*30, CASE WHEN i%10=0 THEN 'USD' ELSE 'UZS' END FROM generate_series(1,40) i;
CREATE TABLE tranzaksiya(id int PRIMARY KEY, hisob_id int REFERENCES hisob(id), turi text NOT NULL, summa numeric(14,2) NOT NULL, sana timestamp NOT NULL);
INSERT INTO tranzaksiya SELECT i,(i*7)%40+1,(ARRAY['kirim','chiqim','kirim','otkazma'])[i%4+1], 50000+(i*98765)%5000000, TIMESTAMP '2024-01-01 09:00'+ (i*17||' hours')::interval FROM generate_series(1,100) i;
CREATE TABLE qarz(id int PRIMARY KEY, mijoz_id int REFERENCES mijoz(id), summa numeric(14,2) NOT NULL, muddat_oy int NOT NULL, holat text NOT NULL);
INSERT INTO qarz SELECT i,(i*4)%30+1, 5000000+(i*3000000)%60000000, 6*(i%6+1), (ARRAY['faol','yopilgan','kechikkan'])[i%3+1] FROM generate_series(1,20) i;

Kutubxona (kutubxona)

Jadvallar: janr, muallif, kitob, azo, ijara.

DROP SCHEMA IF EXISTS kutubxona CASCADE; CREATE SCHEMA kutubxona; SET search_path=kutubxona;
CREATE TABLE janr(id int PRIMARY KEY, nom text NOT NULL);
INSERT INTO janr VALUES (1,'Roman'),(2,'Detektiv'),(3,'Ilmiy'),(4,'She''riyat'),(5,'Tarix');
CREATE TABLE muallif(id int PRIMARY KEY, ism text NOT NULL, tugilgan_yil int, davlat text);
INSERT INTO muallif VALUES (1,'Abdulla Qodiriy',1894,'Ozbekiston'),(2,'Cholpon',1897,'Ozbekiston'),(3,'Oybek',1905,'Ozbekiston'),(4,'Tohir Malik',1946,'Ozbekiston'),(5,'Lev Tolstoy',1828,'Rossiya'),(6,'Agatha Christie',1890,'Angliya'),(7,'Pirimqul Qodirov',1928,'Ozbekiston'),(8,'Erkin Vohidov',1936,'Ozbekiston');
CREATE TABLE kitob(id int PRIMARY KEY, nom text NOT NULL, muallif_id int REFERENCES muallif(id), janr_id int REFERENCES janr(id), yil int, nusxa int NOT NULL);
INSERT INTO kitob SELECT i,'Kitob '||i||' '||(ARRAY['sirlari','yoli','kuni','dunyosi'])[i%4+1], i%8+1, i%5+1, 1950+(i*11)%73, i%5+1 FROM generate_series(1,40) i;
CREATE TABLE azo(id int PRIMARY KEY, ism text NOT NULL, royxat_sana date NOT NULL, email text);
INSERT INTO azo SELECT i,(ARRAY['Bahrom','Zarina','Eldor','Munisa','Ulugbek'])[i%5+1]||' '||(ARRAY['Aminov','Sodiqova','Yoqubov'])[i%3+1], DATE '2022-01-01'+i*20, CASE WHEN i%9=0 THEN NULL ELSE 'azo'||i||'@kutub.uz' END FROM generate_series(1,25) i;
CREATE TABLE ijara(id int PRIMARY KEY, kitob_id int REFERENCES kitob(id), azo_id int REFERENCES azo(id), olingan date NOT NULL, qaytarilgan date);
INSERT INTO ijara SELECT i,(i*7)%40+1,(i*3)%25+1, DATE '2024-01-01'+i*4, CASE WHEN i%5=0 THEN NULL ELSE DATE '2024-01-01'+i*4+5+i%12 END FROM generate_series(1,70) i;

Keyingi qadam

Sxemalarni yuklab bo'lgach, SELECT, alias va ifodalar mavzusidan boshlang.