Логички и физички дизајн - Креирање база податоци (со SQL DDL)

Релациско мапирање

Сите објекти се креирани во шемата project на доделената проектна база.

Ознаки

• Примарните клучеви се означени со bold и underline PK
• Надворешните клучеви се означени со FK* за кои во заграда () е напишана табелата која ја референцираат.
• Задолжителните атрибути се болдирани.

Табели

• users (id, name, embg, surname, index, bday, email, password, role, quota, enrollment_year, created_at)
• high_school (id, user_id*(users), gpa, type)
• contact (id, user_id*(users), city, municipality, address, number, microsoft_email)
• major (id, name)
• active_semesters (id, year, type)
• subjects (id, name, code, awarded_credits, dependency_credit)
• enrolled_semesters (id, user_id*(users), quota, major_id*(major), note, student_comment, created_at, last_change, completed, semester_id*(active_semesters))
• semesters_subjects (id, enrolled_semesters_id*(enrolled_semesters), subjects_id*(subjects), professor_id*(users), signature)
• passed_subjects (id, enrolled_id*(semesters_subjects), grade, date_passed)
• documents (id, type, body, cost)
• user_documents (user_id*(users), document_id*(documents))
• token (id, user_id*(users), token, expires_at, is_valid)
• payment (id, user_id*(users), enrollment_id*(enrolled_semesters), amount)
• major_subjects (major_id*(major), subject_id*(subjects), mandatory_semester)
• dependency_subject (subject_id*(subjects), dependency_id*(subjects))
• professour_subjects (prof_id*(users), active_semester_id*(active_semesters), subject_id*(subjects))

Дополнителни ограничувања

• UNIQUE (user_id, semester_id) во enrolled_semesters
• UNIQUE (enrolled_semesters_id, subjects_id) во semesters_subjects
• UNIQUE на passed_subjects.enrolled_id (врска 1:1 со semesters_subjects)
• UNIQUE на high_school.user_id и contact.user_id (врски 1:1 со users)
• UNIQUE (year, type) во active_semesters
• UNIQUE на users.email, subjects.name и subjects.code
• CHECK (subject_id <> dependency_id) во dependency_subject

DDL скрипта за бришење на табелите и креирање на табелите

schema_creation.sql​

Скриптата ја брише и повторно ја креира целата шема project, па работи и врз празна база и врз база во која објектите веќе постојат.

DML скрипта за полнење на табелите со податоци

data_load.sql​

Скриптата прво ги празни сите табели (TRUNCATE ... RESTART IDENTITY CASCADE), па работи и врз празни табели и врз табели кои веќе содржат податоци.

dbdiagram.io (DBML) код

iknow.dbml​

Релациски дијаграм

Користење на вештачка интелигенција

RelationalDesignAIUsage

Историјат

Верзија 1 - Почетна верзија

Верзија 2

• Додадени се табелите high_school и contact поврзани 1:1 со users.
• Додадена е табелата token за чување на токени за автентикација.

Верзија 3

• Раздвоени се табелите enrollment_info и enrolled_semesters.
• Додадена е табелата Payment со надворешни клучеви кон users и enrolled_semesters.

Верзија 4

• Додадена е табелата dependency_subject за моделирање на предуслови меѓу предмети.
• Додадена е табелата professour_subjects (професор ↔ предмет ↔ активен семестар).

Верзија 5

• Отстранета е табелата enrollment_info; нејзините атрибути quota и enrollment_year се преместени во табелата users.
• Отстранет е надворешниот клуч user_id од semesters_subjects – студентот се добива преку enrolled_semesters.user_id.
• Отстранет е атрибутот ukim од payment и akreditacija од major_subjects.
• Табелите high_school и contact добиваат сопствен id како примарен клуч, а user_id останува UNIQUE NOT NULL надворешен клуч (1:1).
• Преименувани колони заради усогласување со ЕР дијаграмот: year_ → year, note_ → note, passed_subjects.passed_id → id.
• Додадени се ограничувања за интегритет: UNIQUE (user_id, semester_id) во enrolled_semesters, UNIQUE на passed_subjects.enrolled_id (1:1 со semesters_subjects), UNIQUE (year, type) во active_semesters и CHECK (subject_id <> dependency_id) во dependency_subject.

Верзија 6

• Сите објекти се преместени во шемата project, согласно барањата на фазата.
• Скриптите се преименувани во schema_creation.sql и data_load.sql.
• data_load.sql сега прво ги празни табелите, па може да се стартува повеќепати.
• Исправен е називот на атрибутот manditory_semester → mandatory_semester во major_subjects.
• Примарните клучеви се дефинирани како GENERATED BY DEFAULT AS IDENTITY, за да апликацијата може да внесува редови без сама да пресметува id.
• Проширени се тест податоците: 20 предмети, 5 активни семестри и 4 професори, со покриеност на сите парови (семестар, предмет) во professour_subjects.

Статус

Во тек