Дизайн бази даних SISFOMA (Система інформації для студентів) з використанням MySQL - javascript.org.ua

Логотип MySQL (джерело: https://id.wikipedia.org/wiki/MySQL)

Реляційна база даних — це набір інформації, що організує дані у заздалегідь визначені зв'язки, де дані зберігаються в одній або кількох таблицях (або "реляціях"), стовпцях і рядках, що дозволяє легко побачити та зрозуміти зв'язки між різними структурами даних. Реляція — це логічний зв'язок між різними таблицями, що створюється на основі взаємодії між ними.

Що таке реляційна база даних?

У цій статті я розгляну проектування та впровадження бази даних за допомогою MySQL для потреб Системи Інформації для студентів.

Кейс

Університет “Джая Селамая” створює систему інформації для студентів, оскільки вона необхідна для звітності до DIKTI щодо діяльності студентських організацій та досягнень студентів, як у академічній, так і в позаакадемічній сферах.

Кожен студент має право брати участь у позакласних заходах, наприклад, у студентських організаціях (Himpunan, BEM, або BLM) та Студентських Клубах (UKM) відповідно до своїх інтересів, наприклад, скаутинг, хор, духовні гуртки, пекінг, театр, природолюбителі тощо. Участь студентів у цих заходах регулюється університетом, і кожен студент зобов'язаний брати участь мінімум в одному позакласному заході в кампусі для розвитку своїх інтересів та здібностей.

Окрім цього, університет потребує дані про досягнення студентів, що фіксуються в реальному часі. Студенти можуть повідомляти про свої досягнення, як у академічній, так і позаакадемічній діяльності в будь-який час. Визнання досягнень студентів відбувається на рівнях від місцевого до міжнародного. Дані студентів, які повинні бути збережені, включають NPM, ім'я, програму навчання, факультет, електронну пошту та номер телефону. Окрім даних про студентів, ця система також потребує дані про UKM та досягнення.

Створення ERD

Базу даних SISFOMA спочатку проектують за допомогою Діаграми сутностей і зв'язків (ERD). Це проектування має на меті візуалізувати дані та зв'язки між сутностями перед їх впровадженням у реляційну базу даних. ERD полегшує розуміння структури даних та взаємодії компонентів у системі інформації для студентів Університету “Джая Селамая”.

ERD Системи Інформації для Студентів (SISFOMA)

Діаграма сутностей і зв'язків (ERD), як показано на зображенні вище, пояснює, що між сутностями існують зв'язки, а також що кожна сутність має атрибути або вміст, пов'язаний із іншими сутностями.

Сутності

SISFOMA має 3 основні сутності (Студент, Досягнення, Позакласні заходи) і 2 допоміжні сутності (UKM і Організація), які залежать від основних сутностей (Позакласні заходи).

Сутність Студент. Сутність студент зберігає особисті дані студента. Вона має 6 атрибутів: NPM, Ім'я, ПрограмаНавчання, Факультет, Електроннапошта, Номер_телефону, де NPM є основним ключем.
Сутність Досягнення. Сутність досягнень зберігає дані про досягнення студента. Вона має 4 атрибути: IdДосягнення, НазваДосягнення, ТипДосягнення, Рівень, ДатаДосягнення, Організатор, де Id_Досягнення є основним ключем.
Сутність Позакласні заходи. Сутність позакласних заходів зберігає дані про студентські клуби (UKM) та організації.
Сутність має 4 атрибути: IdЕкстракурикулум, НазваЕкстракурикулум, Тип, РікЗаснування, де IdЕкстракурикулум є основним ключем.
Сутність UKM. Ця сутність є допоміжною та залежить від сутності Екстракурикулум, зберігаючи додатковий атрибут: Категорія.
Сутність Організація. Ця сутність також є допоміжною і залежить від сутності Екстракурикулум, зберігаючи додаткові атрибути: Університет, Факультет та Програма_Навчання.

Кардинальність

Кардинальність у діаграмі сутностей та зв'язків (ERD) показує відносини між двома сутностями, а саме, скільки сутностей у одній таблиці може бути пов'язано з сутністю в іншій таблиці.

Студент — Екстракурикулум: Багато до Багатьох. Один студент може брати участь у багатьох екстракурикулумах, і кожен екстракурикулум може мати багато студентів.
Студент — Досягнення: Багато до Багатьох. Один студент може мати багато досягнень, і одне досягнення може бути досягнуте багатьма студентами. Наприклад, досягнення UKM може бути досягнуте членами, що беруть участь у ньому.
Екстракурикулум — Досягнення: Один до Багатьох. Один екстракурикулум може привести до багатьох досягнень, але одне досягнення може бути отримано лише одним екстракурикулумом. Наприклад, UKM Падуан Суара може досягти багатьох перемог у галузі хорового співу, але титул Переможець 1 місця в Падуан Суара може бути лише у UKM Падуан Суара, а не в інших UKM.
Екстракурикулум — UKM: Один до Багатьох.
Екстракурикулум — Організація: Один до Багатьох.

Взаємозв'язки

Для реалізації відносин між сутностями в ERD використовуються сутності-взаємозв'язки (які в майбутньому стануть таблицями зв'язку), що слугують мостом між сутностями.

partisipasi_prestasi : Зв'язує студентів, досягнення та екстракурикулуми. Показує, що студент може мати досягнення, пов'язані як з екстракурикулумами, так і незалежні досягнення. Ця сутність містить 3 зовнішні ключі: NPM (від Студента), IdЕкстракурикулум (від Екстракурикулуму), IdДосягнення (від Досягнення).
ikut_ekskul: Зв'язує студентів з екстракурикулумами, в яких вони беруть участь. Показує участь студента в екстракурикулумі. Ця сутність містить 2 зовнішні ключі: NPM (від Студента), IdЕкстракурикулум (від Екстракурикулуму), а також 3 додаткові атрибути: ДатаПриєднання, Посада та Статус.
terdiri_dari: Зв'язує основну сутність (Екстракурикулум) з допоміжними сутностями, що залежить від цієї сутності (UKM та організація). Показує, що екстракурикулум має 2 типи: UKM та Організація. Обидва ці типи в майбутньому зливаються в одну основну таблицю — таблицю Екстракурикулуму.

Структура таблиць

Таблиця Студент

CREATE TABLE Mahasiswa (  
 NPM VARCHAR(13) PRIMARY KEY,  
 Nama VARCHAR(255) NOT NULL,  
 Program_Studi VARCHAR(100) NOT NULL,  
 Fakultas VARCHAR(100) NOT NULL,  
 Email VARCHAR(255) NOT NULL,  
 No_HP VARCHAR(15) NOT NULL  
);

Таблиця Досягнення

CREATE TABLE Prestasi (  
 Id_Prestasi INT AUTO_INCREMENT PRIMARY KEY,  
 Nama_Prestasi VARCHAR(255) NOT NULL,  
 Jenis_Prestasi ENUM('Akademik', 'Non-Akademik') NOT NULL,  
 Tingkat ENUM('Kabupaten', 'Provinsi', 'Nasional', 'Internasional') NOT NULL,  
 Tgl_Pencapaian DATE NOT NULL,  
 Penyelenggara VARCHAR(100)  
);

Таблиця Екстракурикулум. Ця таблиця створюється шляхом об'єднання сутності Екстракурикулум та допоміжних сутностей, що з нею пов'язані, — UKM та Організація як одна сутність Екстракурикулум.
Тому атрибут Категорія буде заповнений лише у випадку, якщо Тип = 'UKM', а атрибути Університет, Факультет та Програма_Навчання будуть заповнені тільки в разі, якщо Тип = 'Організація'.

CREATE TABLE Ekstrakurikuler (  
 Id_Ekskul INT AUTO_INCREMENT PRIMARY KEY,  
 Nama_Ekskul VARCHAR(100) NOT NULL,  
 Tipe ENUM('UKM', 'Organisasi') NOT NULL,  
 Tahun_Berdiri YEAR NOT NULL,  
 Kategori VARCHAR(100),  
 Universitas VARCHAR(100),  
 Fakultas VARCHAR(100),  
 Program_Studi VARCHAR(100)  
);

Таблиця ikut_ekskul

CREATE TABLE ikut_ekskul (  
 Id_Ekskul INT,  
 NPM VARCHAR(13),  
 Tgl_Bergabung DATE,  
 Posisi VARCHAR(50) NOT NULL,  
 Status ENUM('Aktif', 'Non-Aktif') NOT NULL  
);

Таблиця partisipasi_prestasi. Досягнення, яке було здобуте, може бути пов'язане з UKM, в якому бере участь студент (тільки для UKM, оскільки Організація не бере участі в жодних змаганнях), або бути незалежним (Id_Ekskul = NULL)

CREATE TABLE partisipasi_prestasi (  
 NPM VARCHAR(13),  
 Id_Prestasi INT,  
 Id_Ekskul INT,  
 foreign key (NPM) references Mahasiswa(NPM),  
 foreign key (Id_Prestasi) references Prestasi(Id_Prestasi),  
 foreign key (Id_Ekskul) references Ekstrakurikuler(Id_Ekskul)  
);

Реалізація бази даних

Початкове заповнення даних

Для внесення даних використовуйте команду INSERT.

Дані Студентів

INSERT INTO Mahasiswa (NPM, Nama, Program_Studi, Fakultas, Email, No_HP) VALUES  
('2201234567890', 'Анджі Віджая', 'Інформатика', 'Інформатика', '[email protected]', '081234567890'),  
('2201234567891', 'Буді Сантосо', 'Техніка Індустрії', 'Техніка', '[email protected]', '081234567891'),  
('2201234567892', 'Чітра Айю', 'Психологія', 'Психологія', '[email protected]', '081234567892'),  
('2201234567893', 'Діан ПраТама', 'Інформатика', 'Інформатика', '[email protected]', '081234567893'),  
('2201234567894', 'Ека Путрі', 'Філологія Англійська', 'Філологія', '[email protected]', '081234567894');

Дані Екстракурикулуму

INSERT INTO Ekstrakurikuler (Nama_Ekskul, Tahun_Berdiri, Tipe, Universitas, Fakultas, Program_Studi) VALUES  
('Paguyuban Rakyat Seni Jaya', 2010, 'UKM', NULL, NULL, NULL),  
('Fotografi Jaya', 2008, 'UKM', NULL, NULL, NULL),  
('Jurnalistik Jaya', 2010, 'UKM', NULL, NULL, NULL),  
('BEM Universitas', 2005, 'Organisasi', 'Universitas Jaya Selamanya', NULL, NULL),  
('BEM Fakultas Teknik', 2010, 'Organisasi', 'Universitas Jaya Selamanya', 'Fakultas Teknik', NULL),  
('BEM Fakultas Ilmu Komputer', 2012, 'Organisasi', 'Universitas Jaya Selamanya', 'Fakultas Ilmu Komputer', NULL),  
('HIMA Teknik Industri', 2012, 'Organisasi', 'Universitas Jaya Selamanya', 'Fakultas Teknik', 'Teknik Industri'),  
('HIMA Informatika', 2013, 'Organisasi', 'Universitas Jaya Selamanya', 'Fakultas Ilmu Komputer', 'Informatika');

Дані ikut_ekskul

INSERT INTO Ikut_Ekskul (Id_Ekskul, NPM, Tgl_Bergabung, Posisi, Status) VALUES  
(1, '2201234567890', '2021-09-01', 'Голова', 'Активний'),  
(1, '2201234567894', '2021-09-01', 'Член', 'Активний'),  
(2, '2201234567891', '2022-01-15', 'Член', 'Активний'),  
(3, '2201234567892', '2023-03-20', 'Член', 'Неактивний'),  
(5, '2201234567891', '2021-09-02', 'Член', 'Активний'),  
(4, '2201234567893', '2022-06-10', 'Секретар', 'Активний'),  
(3, '2201234567893', '2022-06-19', 'Касир', 'Неактивний'),  
(4, '2201234567894', '2023-07-25', 'Член', 'Активний'),  
(6, '2201234567893', '2022-06-10', 'Секретар', 'Активний'),  
(7, '2201234567891', '2023-03-20', 'Член', 'Неактивний'),  
(8, '2201234567890', '2023-07-30', 'Член', 'Неактивний');

Дані Досягнень

INSERT INTO Prestasi (Nama_Prestasi, Jenis_Prestasi, Tingkat, Tgl_Pencapaian, Penyelenggara) VALUES  
('Переможець 1 місця в Падуан Суара Студентів', 'Неакадемічне', 'Національний', '2023-06-15', 'UNAIR Choir'),  
('Переможець 3 місця UI/UX', 'Академічне', 'Національний', '2023-02-10', 'HIMATIKA UI'),  
('Переможець 3 місця в Національному Есе', 'Академічне', 'Національний', '2023-02-10', 'Команда Письменників Індії');
('Переможець у конкурсі на найкращу статтю', 'Неакадемічне', 'Обласний', '2023-08-05', 'Управління району'),  
('Золота медаль у мікрофотографії', 'Неакадемічне', 'Міжнародний', '2021-05-03', 'Чиказький університет');

Дані partisipasi_prestasi

INSERT INTO Partisipasi_Prestasi (NPM, Id_Prestasi, Id_Ekskul) VALUES  
('2201234567890', 1, 1),  
('2201234567894', 1, 1),  
('2201234567890', 2, NULL),  
('2201234567894', 3, NULL),  
('2201234567892', 4, 3),  
('2201234567893', 4, 3),  
('2201234567891', 5, 2);

SQL запит SELECT для перегляду даних

Давайте спробуємо витягнути дані з моделі даних, яку ми створили.
Використовуйте запит SELECT

Вивести студентів, які беруть участь в екстракуррикулумах

SELECT m.NPM, m.Nama AS Nama_Mahasiswa, e.Nama_Ekskul, ie.Tgl_Bergabung, ie.Posisi, ie.Status  
FROM Mahasiswa m  
JOIN Ikut_Ekskul ie ON m.NPM = ie.NPM  
JOIN Ekstrakurikuler e ON ie.Id_Ekskul = e.Id_Ekskul;

Вивести всі досягнення студентів

SELECT m.Nama AS Nama_Mahasiswa, p.Nama_Prestasi, p.Jenis_Prestasi, p.Tingkat, e.Nama_Ekskul  
FROM Mahasiswa m  
JOIN partisipasi_prestasi pp ON m.NPM = pp.NPM  
JOIN Prestasi p ON pp.Id_Prestasi = p.Id_Prestasi  
LEFT JOIN Ekstrakurikler e ON pp.Id_Ekskul = e.Id_Ekskul;

Вивести всі екстракуррикулуми з їх типом та кількістю учасників

SELECT e.Nama_Ekskul, e.Tipe, COUNT(ie.NPM) AS Jumlah_Anggota  
FROM Ekstrakurikuler e   
LEFT JOIN ikut_ekskul ie on e.Id_Ekskul = ie.Id_Ekskul  
GROUP BY e.Id_Ekskul;

Вивести кількість досягнень кожного студента

SELECT m.Nama, COUNT(pp.Id_Prestasi) as Jumlah_Prestasi  
FROM Mahasiswa m  
LEFT JOIN partisipasi_prestasi pp ON m.NPM = pp.NPM  
GROUP BY m.NPM;

Висновок

SISFOMA забезпечує реальні переваги у вигляді підвищення ефективності управління даними, покращення точності та зручності доступу до інформації. Крім того, розробку цієї системи можна спрямувати на інтеграцію функціоналу входу на основі ролей для розмежування прав доступу, розробку інформаційної панелі для статистики діяльності, а також автоматичних повідомлень для нагадувань або оновлень даних. Завдяки надійній системній основі SISFOMA не лише підтримує управління даними студентів, але й надає гнучкість для подальшого розвитку відповідно до потреб університету.

Зв'яжіться зі мною на LinkedIn:

https://www.linkedin.com/in/mutiarautami/

Перекладено з: Desain Basis Data SISFOMA (Sistem Informasi Kemahasiswaan) dengan MySQL