ЛЕКЦ 08: МЭДЭЭЛЛИЙН САНГИЙН ДИЗАЙН (Database Design & SQL)

Хичээлийн зорилго: Мэдээллийн сангийн суурь ойлголт, харилцааны мэдээллийн сан (RDBMS), хүснэгтийн дизайн, нормалчлал, SQL хэл, индекс, гүйлгээ (transaction), JPA/Hibernate-ийн суурь мэдлэгийг Java жишээнүүдтэйгээр эзэмшүүлэх.

Хамрах хүрээ: Мэдээллийн сангийн төрлүүд, ER диаграм, хүснэгтийн бүтэц, Primary Key, Foreign Key, нормалчлал (1NF-3NF), SQL (DDL, DML, DQL), JOIN, индекс, гүйлгээ (ACID), JPA Entity Mapping, харилцааны төрлүүд, N+1 асуудал, NoSQL ойлголт.

ХЭСЭГ 1: ОНОЛЫН СУУРЬ (Theory & Foundations)

1.1 Мэдээллийн сан гэж юу вэ?

Мэдээллийн сан (Database) — Зохион байгуулагдсан, хадгалагдсан, удирдагддаг өгөгдлийн цуглуулга. Програмын өгөгдлийг бүтэцтэй хэлбэрээр хадгалж, хайлт, шинэчлэлт, устгалт хийх боломжийг олгоно.

💡 Зүйрлэл: Номын сан шиг — номууд (өгөгдөл) ангилагдаж, тавиур дээр (хүснэгт) байрлаж, каталог (индекс)-аар хурдан олддог.

DBMS (Database Management System) — Мэдээллийн сангийн удирдлагын систем. Өгөгдөл хадгалах, хайх, өөрчлөх, аюулгүй байдлыг хангах програм хангамж.

1.2 Харилцааны мэдээллийн сан (RDBMS)

RDBMS (Relational Database Management System) — Өгөгдлийг хүснэгт (table) хэлбэрээр хадгалж, хүснэгтүүд хоорондоо харилцаа (relationship)-аар холбогддог.

Суурь нэр томьёо:

┌─────────────────────────────────────────────┐
│               students (хүснэгт)            │
├─────┬──────────┬─────────────────┬──────────┤
│ id  │ name     │ email           │ gpa      │  ← Баганууд (Columns)
├─────┼──────────┼─────────────────┼──────────┤
│ 1   │ Батболд  │ bat@example.com │ 3.75     │  ← Мөр (Row)
│ 2   │ Сараа    │ sar@example.com │ 3.90     │  ← Мөр (Row)
│ 3   │ Дорж     │ dor@example.com │ 3.20     │  ← Мөр (Row)
└─────┴──────────┴─────────────────┴──────────┘
  ↑ PK

1.3 Түлхүүрүүд (Keys)

1.3.1 Primary Key (PK) — Анхдагч түлхүүр

• Хүснэгтийн мөр бүрийг давтагдашгүй тодорхойлох
• NULL байж болохгүй
• Хүснэгтэд ганц PK байна
• Ихэвчлэн auto-increment тоо (1, 2, 3...) эсвэл UUID

CREATE TABLE students (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE
);

1.3.2 Foreign Key (FK) — Гадаад түлхүүр

• Өөр хүснэгтийн PK-г заах → Хүснэгт хоорондын ХОЛБООС
• Мэдээллийн бүрэн бүтэн байдал (Referential Integrity) хангах

CREATE TABLE enrollments (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrolled_at DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

students               enrollments              courses
┌─────┬─────────┐     ┌─────┬─────┬─────┐     ┌─────┬──────────┐
│ id  │ name    │     │ s_id│ c_id│ date │     │ id  │ name     │
├─────┼─────────┤     ├─────┼─────┼─────┤     ├─────┼──────────┤
│ 1   │ Батболд │◄────│ 1   │ 101 │ 9/1 │────►│ 101 │ Java     │
│ 2   │ Сараа   │◄────│ 2   │ 101 │ 9/1 │     │ 102 │ Python   │
│     │         │     │ 1   │ 102 │ 9/5 │────►│     │          │
└─────┴─────────┘     └─────┴─────┴─────┘     └─────┴──────────┘
  PK                    FK     FK                 PK

1.3.3 Бусад түлхүүрүүд

Зөвлөмж: Surrogate Key (auto-increment ID) ашиглах нь илүү уян хатан, бизнес дүрэм өөрчлөгдөхөд эвдрэхгүй.

1.4 Харилцааны төрлүүд (Relationships)

1.4.1 Нэг-Олон (One-to-Many / 1:N)

Хамгийн түгээмэл. Нэг тал → Олон тал.

Нэг тэнхим → Олон оюутан
┌─────────────┐        ┌──────────────┐
│ departments │        │   students   │
├─────────────┤        ├──────────────┤
│ id (PK)     │───┐    │ id (PK)      │
│ name        │   │    │ name         │
└─────────────┘   └───►│ dept_id (FK) │
                        └──────────────┘

CREATE TABLE departments (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE students (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    department_id BIGINT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

1.4.2 Олон-Олон (Many-to-Many / M:N)

Завсрын хүснэгт (Junction Table) шаардлагатай.

Олон оюутан ↔ Олон хичээл
┌──────────┐     ┌──────────────┐     ┌──────────┐
│ students │     │ enrollments  │     │ courses  │
├──────────┤     ├──────────────┤     ├──────────┤
│ id (PK)  │◄───│ student_id   │     │ id (PK)  │
│ name     │     │ course_id    │───►│ name     │
└──────────┘     │ grade        │     │ credits  │
                 └──────────────┘     └──────────┘

CREATE TABLE enrollments (
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    grade VARCHAR(2),
    enrolled_at DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

1.4.3 Нэг-Нэг (One-to-One / 1:1)

Нэг оюутан → Нэг профайл
┌──────────┐        ┌──────────────────┐
│ students │        │ student_profiles │
├──────────┤        ├──────────────────┤
│ id (PK)  │───┐    │ id (PK)          │
│ name     │   └───►│ student_id (FK)  │  ← UNIQUE
└──────────┘        │ bio              │
                    │ avatar_url       │
                    └──────────────────┘

CREATE TABLE student_profiles (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    student_id BIGINT NOT NULL UNIQUE,
    bio TEXT,
    avatar_url VARCHAR(500),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

1.5 ER диаграм (Entity-Relationship Diagram)

ER диаграм = Мэдээллийн сангийн бүтцийг зурагаар илэрхийлэх. Хүснэгтүүд, баганууд, харилцаануудыг харуулна.

┌──────────────┐       ┌───────────────┐       ┌──────────────┐
│  departments │       │   students    │       │   courses    │
├──────────────┤       ├───────────────┤       ├──────────────┤
│ * id         │       │ * id          │       │ * id         │
│   name       │───1:N─│   name        │──M:N──│   name       │
│   code       │       │   email       │       │   credits    │
└──────────────┘       │   gpa         │       │   code       │
                       │   dept_id (FK)│       └──────────────┘
                       │   status      │              │
                       └───────────────┘              │
                              │                       │
                              │ 1:1                   │
                       ┌──────▼────────┐       ┌──────▼───────┐
                       │student_profiles│       │ enrollments  │
                       ├───────────────┤       ├──────────────┤
                       │ * id          │       │ student_id   │
                       │   student_id  │       │ course_id    │
                       │   bio         │       │ grade        │
                       │   avatar_url  │       │ enrolled_at  │
                       └───────────────┘       └──────────────┘

Зурагласны ашиг тус:

• Хүснэгтүүдийн холбоосыг визуалаар харах
• Баг хамтран ажиллахад ойлголт нэгтгэх
• Дизайны алдааг эрт олох
• Шинэ хөгжүүлэгчид системийг хурдан ойлгох

Хэрэгслүүд: dbdiagram.io, Lucidchart, draw.io, IntelliJ Database Diagrams, DBeaver.

1.6 Нормалчлал (Normalization)

Нормалчлал = Давхардлыг арилгаж, өгөгдлийн бүрэн бүтэн байдлыг хангахын тулд хүснэгтийг зөв хуваах.

1.6.1 Нормалчлалгүй (Unnormalized)

┌─────┬─────────┬─────────────────────┬──────────────┐
│ id  │ name    │ courses             │ dept         │
├─────┼─────────┼─────────────────────┼──────────────┤
│ 1   │ Батболд │ Java, Python, DB    │ CS           │
│ 2   │ Сараа   │ Java, Web           │ CS           │
│ 3   │ Дорж    │ Math, Statistics    │ Mathematics  │
└─────┴─────────┴─────────────────────┴──────────────┘
⚠️ Асуудал: courses баганад олон утга, dept давхардаж байна.

1.6.2 Эхний нормал хэлбэр (1NF)

Дүрэм: Багана бүрт ганц утга (atomic). Олон утга → Тусдаа мөр.

┌─────┬─────────┬───────────┬──────┐
│ id  │ name    │ course    │ dept │
├─────┼─────────┼───────────┼──────┤
│ 1   │ Батболд │ Java      │ CS   │
│ 1   │ Батболд │ Python    │ CS   │
│ 1   │ Батболд │ DB        │ CS   │
│ 2   │ Сараа   │ Java      │ CS   │
│ 2   │ Сараа   │ Web       │ CS   │
└─────┴─────────┴───────────┴──────┘
✅ Багана бүрт ганц утга. ⚠️ Гэхдээ name, dept давхардаж байна.

1.6.3 Хоёрдугаар нормал хэлбэр (2NF)

Дүрэм: 1NF + PK-ийн бүх хэсэгт хамааралтай (Partial Dependency арилгах).

students              enrollments          courses
┌─────┬─────────┬──┐  ┌─────┬─────┐      ┌─────┬─────────┐
│ id  │ name    │dp│  │ s_id│ c_id│      │ id  │ name    │
├─────┼─────────┼──┤  ├─────┼─────┤      ├─────┼─────────┤
│ 1   │ Батболд │CS│  │ 1   │ 1   │      │ 1   │ Java    │
│ 2   │ Сараа   │CS│  │ 1   │ 2   │      │ 2   │ Python  │
└─────┴─────────┴──┘  │ 2   │ 1   │      │ 3   │ DB      │
                      └─────┴─────┘      └─────┴─────────┘
✅ Давхардал арилсан. Оюутны мэдээлэл нэг л газар.

1.6.4 Гуравдугаар нормал хэлбэр (3NF)

Дүрэм: 2NF + PK-аас шууд бус хамааралтай (Transitive Dependency) арилгах.

// ❌ 2NF: dept_name нь dept_id-аас хамааралтай (шууд бус)
students: id, name, dept_id, dept_name
         → dept_name нь id-аас шууд бус → dept_id-аар дамжуулж

// ✅ 3NF: dept_name тусдаа хүснэгтэд
students:    id, name, dept_id (FK)
departments: id, name

Нормалчлалын дүрэм (Товч):

Бодит байдал: Ихэвчлэн 3NF хүрэлцээтэй. Зарим тохиолдолд performance-ийн тулд denormalize (давхардуулах) хийж болно.

1.7 SQL хэл (Structured Query Language)

1.7.1 SQL-ийн төрлүүд

1.7.2 DDL — Хүснэгт үүсгэх, өөрчлөх

-- Хүснэгт үүсгэх
CREATE TABLE students (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    gpa DECIMAL(3,2) CHECK (gpa >= 0.00 AND gpa <= 4.00),
    status VARCHAR(20) DEFAULT 'ACTIVE',
    department_id BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Багана нэмэх
ALTER TABLE students ADD COLUMN phone VARCHAR(20);

-- Багана устгах
ALTER TABLE students DROP COLUMN phone;

-- Хүснэгт устгах (бүх өгөгдөлтэй)
DROP TABLE IF EXISTS students;

-- Бүх мөр устгах (бүтэц хадгалах)
TRUNCATE TABLE students;

1.7.3 DML — Өгөгдөл нэмэх, шинэчлэх, устгах

-- INSERT — Шинэ мөр нэмэх
INSERT INTO students (name, email, gpa, department_id)
VALUES ('Батболд', 'bat@example.com', 3.75, 1);

-- Олон мөр нэмэх
INSERT INTO students (name, email, gpa, department_id) VALUES
    ('Сараа', 'sar@example.com', 3.90, 1),
    ('Дорж', 'dor@example.com', 3.20, 2),
    ('Оюуна', 'oyu@example.com', 3.60, 1);

-- UPDATE — Мөр шинэчлэх
UPDATE students SET gpa = 3.80 WHERE id = 1;
UPDATE students SET status = 'GRADUATED' WHERE gpa >= 3.5;

-- DELETE — Мөр устгах
DELETE FROM students WHERE id = 1;
DELETE FROM students WHERE status = 'INACTIVE';

-- ⚠️ WHERE-гүй бол БҮГДИЙГ устгана!
-- DELETE FROM students;  ← Бүх оюутан устана! АЮУЛТАЙ!

1.7.4 DQL — Өгөгдөл хайх (SELECT)

-- Бүх оюутан
SELECT * FROM students;

-- Тодорхой баганууд
SELECT name, email, gpa FROM students;

-- Нөхцөлтэй (WHERE)
SELECT * FROM students WHERE gpa >= 3.5;
SELECT * FROM students WHERE status = 'ACTIVE' AND department_id = 1;
SELECT * FROM students WHERE name LIKE 'Бат%';
SELECT * FROM students WHERE gpa BETWEEN 3.0 AND 3.5;
SELECT * FROM students WHERE department_id IN (1, 2, 3);
SELECT * FROM students WHERE email IS NOT NULL;

-- Эрэмбэлэх (ORDER BY)
SELECT * FROM students ORDER BY gpa DESC;
SELECT * FROM students ORDER BY name ASC, gpa DESC;

-- Хязгаарлах (LIMIT + OFFSET)
SELECT * FROM students ORDER BY gpa DESC LIMIT 10;           -- Шилдэг 10
SELECT * FROM students ORDER BY id LIMIT 10 OFFSET 20;       -- 3-р хуудас

-- Агрегат функцүүд
SELECT COUNT(*) FROM students;                                -- Нийт тоо
SELECT AVG(gpa) FROM students;                                -- Дундаж GPA
SELECT MAX(gpa) FROM students;                                -- Хамгийн өндөр GPA
SELECT MIN(gpa) FROM students;                                -- Хамгийн бага GPA
SELECT SUM(gpa) FROM students;                                -- GPA нийлбэр

-- Бүлэглэх (GROUP BY)
SELECT department_id, COUNT(*) as student_count, AVG(gpa) as avg_gpa
FROM students
GROUP BY department_id;

-- Бүлэглэсний дараа шүүх (HAVING)
SELECT department_id, AVG(gpa) as avg_gpa
FROM students
GROUP BY department_id
HAVING AVG(gpa) > 3.5;

-- DISTINCT — Давтагдашгүй утгууд
SELECT DISTINCT status FROM students;
SELECT DISTINCT department_id FROM students;

1.7.5 JOIN — Хүснэгт холбох

-- INNER JOIN — Хоёр хүснэгтэд ХОЁУЛАНГД нь байгаа мөрүүд
SELECT s.name, d.name as department
FROM students s
INNER JOIN departments d ON s.department_id = d.id;

-- LEFT JOIN — Зүүн хүснэгтийн БҮХ мөр + баруунаас тохирох
SELECT s.name, d.name as department
FROM students s
LEFT JOIN departments d ON s.department_id = d.id;
-- Тэнхимгүй оюутнууд ч гарна (department = NULL)

-- RIGHT JOIN — Баруун хүснэгтийн БҮХ мөр
SELECT s.name, d.name as department
FROM students s
RIGHT JOIN departments d ON s.department_id = d.id;
-- Оюутангүй тэнхимүүд ч гарна

-- Олон хүснэгт холбох
SELECT s.name as student, c.name as course, e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
WHERE s.id = 1;

INNER JOIN:          LEFT JOIN:           RIGHT JOIN:
   A ∩ B               A (бүгд)             B (бүгд)
  ┌───┬───┐           ┌───┬───┐           ┌───┬───┐
  │   │███│           │███│███│           │   │███│
  │   │███│           │███│███│           │   │███│
  └───┴───┘           └───┴───┘           └───┴───┘
   Зөвхөн тохирох     Зүүн бүгд +         Баруун бүгд +
                       тохирох              тохирох

1.8 Индекс (Index)

Индекс гэж юу вэ?

Индекс = Номын сангийн каталог шиг — хайлтыг хурдасгах тусгай бүтэц. Индексгүй бол хүснэгтийн БҮХ мөрийг шалгана (Full Table Scan).

// Индексгүй: Бүх 1,000,000 мөр шалгана
SELECT * FROM students WHERE email = 'bat@example.com';
⏱️ Удаан (Full Scan)

// Индекстэй: B-Tree бүтцээр хурдан олно
CREATE INDEX idx_students_email ON students(email);
SELECT * FROM students WHERE email = 'bat@example.com';
⏱️ Хурдан (Index Scan)

Индексийн төрлүүд

-- Нэг баганат индекс
CREATE INDEX idx_students_email ON students(email);

-- Олон баганат (Composite) индекс
CREATE INDEX idx_students_dept_gpa ON students(department_id, gpa);

-- Partial индекс (зөвхөн идэвхтэй оюутнууд)
CREATE INDEX idx_active_students ON students(email)
WHERE status = 'ACTIVE';

-- UNIQUE индекс
CREATE UNIQUE INDEX idx_students_email_unique ON students(email);

Хэзээ индекс ашиглах вэ?

⚠️ Анхаарах: Индекс нь READ хурдасгана, гэхдээ WRITE (INSERT/UPDATE/DELETE) удаашруулна — индексийг ч шинэчлэх шаардлагатай учраас.

1.9 Гүйлгээ (Transaction) ба ACID

Transaction гэж юу вэ?

Transaction = Нэг логик нэгж болох олон үйлдлийн багц. Бүгд амжилттай бол COMMIT, нэг ч алдаа бол ROLLBACK.

-- Мөнгө шилжүүлэх (2 үйлдэл = 1 transaction)
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100000 WHERE id = 1;  -- Батаас -100,000
UPDATE accounts SET balance = balance + 100000 WHERE id = 2;  -- Дорж руу +100,000

-- Хоёулаа амжилттай бол:
COMMIT;

-- Аль нэг нь алдаатай бол:
ROLLBACK;  -- ХОЁУЛАНГ нь буцаах!

ACID зарчим

Isolation Level

PostgreSQL default: READ COMMITTED. MySQL default: REPEATABLE READ.

1.10 JPA / Hibernate Entity Mapping

1.10.1 Entity ба Table холбох

@Entity
@Table(name = "students")
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", nullable = false, length = 100)
    private String name;

    @Column(name = "email", nullable = false, unique = true)
    private String email;

    @Column(precision = 3, scale = 2)
    private BigDecimal gpa;

    @Enumerated(EnumType.STRING)
    @Column(length = 20)
    private StudentStatus status = StudentStatus.ACTIVE;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    private Department department;

    @OneToOne(mappedBy = "student", cascade = CascadeType.ALL)
    private StudentProfile profile;

    @ManyToMany
    @JoinTable(
        name = "enrollments",
        joinColumns = @JoinColumn(name = "student_id"),
        inverseJoinColumns = @JoinColumn(name = "course_id")
    )
    private Set<Course> courses = new HashSet<>();

    @Column(name = "created_at")
    private LocalDateTime createdAt = LocalDateTime.now();
}

1.10.2 Харилцааны Annotation

1.10.3 FetchType — LAZY vs EAGER

Зөвлөмж: Бараг бүх тохиолдолд LAZY ашиглах. EAGER = N+1 асуудал үүсэх эрсдэлтэй.

1.10.4 N+1 асуудал

// ❌ N+1 Problem
List<Student> students = studentRepository.findAll();  // 1 query
for (Student s : students) {
    System.out.println(s.getDepartment().getName());   // N query (оюутан бүрт 1)
}
// Нийт: 1 + N query (100 оюутан = 101 query!)

// ✅ JOIN FETCH-аар шийдэх
@Query("SELECT s FROM Student s JOIN FETCH s.department")
List<Student> findAllWithDepartment();  // 1 query (JOIN)

1.11 Өгөгдлийн төрлүүд (Data Types)

PostgreSQL-ийн үндсэн төрлүүд

⚠️ Мөнгөн дүнд DECIMAL ашиглах, FLOAT/DOUBLE биш! Float/Double = Бутархай тооны нарийвчлал алдагдна.

1.12 NoSQL ойлголт

SQL vs NoSQL

NoSQL-ийн төрлүүд

// SQL (RDBMS)                    // NoSQL (MongoDB)
┌──────────────┐                  {
│   students   │                    "_id": "abc123",
├──────────────┤                    "name": "Батболд",
│ id: 1        │                    "email": "bat@example.com",
│ name: Батболд│       ───►         "gpa": 3.75,
│ email: bat@  │                    "department": {
│ dept_id: 1   │                      "name": "CS",
└──────────────┘                      "code": "CSE"
┌──────────────┐                    },
│ departments  │                    "courses": [
├──────────────┤                      {"name": "Java", "grade": "A"},
│ id: 1        │                      {"name": "DB", "grade": "B+"}
│ name: CS     │                    ]
└──────────────┘                  }
2 хүснэгт + JOIN                 1 document (embedded)

Хэзээ SQL? Бүтэцтэй, харилцаатай өгөгдөл, ACID чухал (банк, ERP). Хэзээ NoSQL? Уян хатан бүтэц, том хэмжээ, хурдан бичих (лог, IoT, кэш).

1.13 Мэдээллийн сангийн дизайны шилдэг туршлагууд

ХЭСЭГ 2: ТҮЛХҮҮР ҮГ БА МЭРГЭЖЛИЙН НЭР ТОМЬЁО (Keywords & Glossary)