This repository has been archived by the owner on May 1, 2024. It is now read-only.
Tables
CREATE TABLE Student
(
id int NOT NULL IDENTITY (1, 1),
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
address varchar(200) NOT NULL,
email varchar(50) NOT NULL UNIQUE,
phone_number varchar(20) NOT NULL UNIQUE,
PRIMARY KEY (id),
)- id — An unique identifier of the student.
- first_name — A name of the student.
- last_name — A surname of the student.
- address — A home address of the student.
- email — An email address of the student. Must be unique.
- phone_number — A phone number of the student. Must be unique.
CREATE TABLE Teacher
(
id int NOT NULL IDENTITY (1, 1),
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
address varchar(200) NOT NULL,
email varchar(50) NOT NULL UNIQUE,
phone_number varchar(20) NOT NULL UNIQUE,
PRIMARY KEY (id),
)- id — An unique identifier of the teacher.
- first_name — A name of the teacher.
- last_name — A surname of the teacher.
- address — An address of the teacher.
- email — An email of the teacher.
- phone_number d A phone number of the teacher.
CREATE TABLE Language
(
id int NOT NULL IDENTITY (1, 1),
name varchar(50) NOT NULL UNIQUE,
PRIMARY KEY (id),
)- id — An unique identifier of the language.
- name — The name of the language. Must be unique.
CREATE TABLE Translator
(
id int NOT NULL IDENTITY (1, 1),
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
address varchar(200) NOT NULL,
email varchar(50) NOT NULL UNIQUE,
phone_number varchar(20) NOT NULL UNIQUE,
PRIMARY KEY (id),
)- id — An unique identifier of the translator.
- first_name — A name of the translator.
- last_name — A surname of the translator.
- address — An address of the translator.
- email — An email of the translator.
- phone_number — A phone number of the translator.
CREATE TABLE Webinar
(
id int NOT NULL IDENTITY (1, 1),
price int DEFAULT 0 NOT NULL,
datetime datetime NOT NULL,
url varchar(200) NOT NULL UNIQUE,
language varchar(50) DEFAULT 'Polish' NOT NULL,
translator_id int,
teacher_id int NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (translator_id) REFERENCES Translator (id) ON DELETE SET NULL,
FOREIGN KEY (teacher_id) REFERENCES Teacher (id) ON DELETE NO ACTION,
CONSTRAINT not_negative_webinar_price CHECK (price >= 0),
)
- id — An unique identifier of the webinar.
- price — A price (in Polish grosz) of the webinar. If the price is 0, then the webinar is free.
- datetime — A date of the webinar.
- url — An url to the webinar on the platform.
- language — A language in which the webinar will be held.
- translator_id — An unique identifier of the translator who will translate the webinar. If the webinar will be held in Polish, then this field is empty.
- teacher_id — An unique identifier of the teacher who will lead the webinar.
CREATE TABLE Course
(
id int NOT NULL IDENTITY (1, 1),
price int NOT NULL,
advance_price int NOT NULL,
subject varchar(100) NOT NULL,
language varchar(50) DEFAULT 'Polish' NOT NULL,
student_limit int NOT NULL,
PRIMARY KEY (id),
CONSTRAINT positive_course_price CHECK (price > 0),
CONSTRAINT not_negative_advance_price CHECK (advance_price >= 0),
CONSTRAINT positive_course_student_limit CHECK (student_limit > 0),
)- id — An unique identifier of the course.
- price — A price (in Polish grosz) of the course. Must be greater than 0.
- advance_price — An advance price (in Polish grosz) of the course. Must be greater or equal to 0.
- subject — A subject of the course.
- language — A language in which the course will be held.
- student_limit — A maximum number of students who can attend the course. Must be greater than 0.
CREATE TABLE Room
(
id int NOT NULL IDENTITY (1, 1),
number varchar(10) NOT NULL,
building varchar(50) NOT NULL,
PRIMARY KEY (id),
)- id — An unique identifier of the room.
- number — A number of the room.
- building — A building in which the room is located.
CREATE TABLE Module
(
id int NOT NULL IDENTITY (1, 1),
course_id int NOT NULL,
type varchar(15) NOT NULL,
room_id int,
teacher_id int NOT NULL,
start_date date NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (course_id) REFERENCES Course (id) ON DELETE CASCADE,
FOREIGN KEY (room_id) REFERENCES Room (id) ON DELETE NO ACTION,
FOREIGN KEY (teacher_id) REFERENCES Teacher (id) ON DELETE NO ACTION,
CONSTRAINT type_and_room CHECK (
type = 'hybrid' OR
type = 'in_person' AND room_id IS NOT NULL OR
type IN ('online_sync', 'online_async') AND room_id IS NULL
),
)- id — An unique identifier of the module.
- course_id — An unique identifier of the course to which the module belongs.
-
type — A type of the module. Can be one of the following
values:
hybrid,in_person,online_sync,online_async. - room_id — An unique identifier of the room in which the module will be held. If the module will be held online, then this field must be empty.
- teacher_id — An unique identifier of the teacher who will lead the module.
- start_date — A start date of the module.
CREATE TABLE Studies
(
id int NOT NULL IDENTITY (1, 1),
title varchar(100) NOT NULL,
syllabus varchar(5000),
registration_price int NOT NULL, --in Polish grosz
language varchar(50) DEFAULT 'Polish' NOT NULL,
student_limit int NOT NULL,
PRIMARY KEY (id),
CONSTRAINT non_negative_studies_registration_price CHECK (registration_price >= 0),
CONSTRAINT positive_studies_student_limit CHECK (student_limit > 0),
)- id — An unique identifier of the studies.
- syllabus — A syllabus of the studies.
- price — A price (in Polish grosz) of the studies. Must be greater than 0.
- advance_price — An advance price (in Polish grosz) of the studies. Must be greater or equal to 0.
-
language — A language in which the studies will be held. The default value is
Polish. - student_limit — A maximum number of students who can attend the studies. Must be greater than 0.
CREATE TABLE Semester
(
id int NOT NULL IDENTITY (1, 1),
number int NOT NULL,
studies_id int NOT NULL,
price int NOT NULL, --in Polish grosz
schedule_url varchar(200) NOT NULL,
start_date date NOT NULL,
end_date date NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (studies_id) REFERENCES Studies (id) ON DELETE NO ACTION,
CONSTRAINT number_between_1_and_12 CHECK (number > 0 AND number <= 12),
CONSTRAINT start_date_lower_end_date CHECK (start_date < end_date),
CONSTRAINT non_negative_semester_price CHECK (price >= 0),
UNIQUE (studies_id, number),
)- id — An unique identifier of the semester.
- number — A number of the semester. Must be greater than 0 and less or equal to 12.
- studies_id — An unique identifier of the studies to which the semester belongs.
- price — A price (in Polish grosz) of the semester. Must be greater than 0.
- schedule_url — An url to the schedule of the semester.
- start_date — A start date of the semester.
- end_date — An end date of the semester. Must be later than start_date.
- The combination of studies_id and number must be unique.
CREATE TABLE Subject
(
id int NOT NULL IDENTITY (1, 1),
name varchar(200) NOT NULL,
semester_id int NOT NULL,
teacher_id int,
PRIMARY KEY (id),
FOREIGN KEY (semester_id) REFERENCES Semester (id) ON DELETE CASCADE,
FOREIGN KEY (teacher_id) REFERENCES Teacher (id) ON DELETE SET NULL,
)- id — An unique identifier of the subject.
- name — A name of the subject.
- semester_id — An unique identifier of the semester to which the subject belongs.
- teacher_id — An unique identifier of the teacher who will lead the subject.
CREATE TABLE Internship
(
id int NOT NULL IDENTITY (1, 1),
studies_id int NOT NULL,
date date NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (studies_id) REFERENCES Studies (id) ON DELETE CASCADE,
)- id — An unique identifier of the internship.
- studies_id — An unique identifier of the studies to which the internship belongs.
- date — A date of the internship.
CREATE TABLE Meeting
(
id int NOT NULL IDENTITY (1, 1),
module_id int,
subject_id int,
url varchar(200),
datetime datetime NOT NULL,
length int DEFAULT 90 NOT NULL, --in minutes
type varchar(10) NOT NULL,
standalone_price int, --in Polish grosz
translator_id int,
substituting_teacher_id int DEFAULT NULL,
substituting_room_id int DEFAULT NULL,
student_limit int NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (module_id) REFERENCES Module (id) ON DELETE NO ACTION,
FOREIGN KEY (subject_id) REFERENCES Subject (id) ON DELETE NO ACTION,
FOREIGN KEY (translator_id) REFERENCES Translator (id) ON DELETE SET NULL,
FOREIGN KEY (substituting_teacher_id) REFERENCES Teacher (id) ON DELETE NO ACTION,
FOREIGN KEY (substituting_room_id) REFERENCES Room (id) ON DELETE NO ACTION,
CONSTRAINT polymorphic CHECK (
module_id IS NOT NULL AND subject_id IS NULL OR
module_id IS NULL AND subject_id IS NOT NULL
),
CONSTRAINT type_and_url CHECK (type = 'in_person' OR type IN ('online', 'video') AND url IS NOT NULL),
CONSTRAINT positive_student_limit CHECK (student_limit > 0),
CONSTRAINT positive_standalone_price CHECK (standalone_price is NULL OR standalone_price > 0),
)
- id — An unique identifier of the meeting.
- module_id — An unique identifier of the module to which the meeting belongs. If the meeting is not a part of the module, then this field must be empty.
- subject_id — An unique identifier of the subject to which the meeting belongs. If the meeting is not a part of the subject, then this field must be empty.
- url — An url to the meeting on the platform. If the meeting will be held in person, then this field must be empty.
- datetime — A date of the meeting.
- length — A length (in minutes) of the meeting.
-
type — A type of the meeting. Can be one of the following values:
in_person,online,video. - standalone_price — A price (in Polish grosz) of the meeting for students who are not enrolled in the course. If the meeting is free, then this field must be empty.
- translator_id — An unique identifier of the translator who will translate the meeting. If the meeting is not translated, then this field must be empty.
- substituting_teacher_id — An unique identifier of the teacher who will substitute the teacher who was supposed to lead the meeting. If the meeting will be led by the teacher who was supposed to lead it, then this field must be empty.
- student_limit — A maximum number of students who can attend the meeting. Must be greater than 0.
CREATE TABLE Basket
(
id int NOT NULL IDENTITY (1, 1),
student_id int NOT NULL,
payment_url varchar(200),
state varchar(15) NOT NULL,
create_date date NOT NULL,
payment_date date,
PRIMARY KEY (id),
FOREIGN KEY (student_id) REFERENCES Student (id) ON DELETE CASCADE,
CONSTRAINT state_enum CHECK (state IN ('open', 'pending_payment', 'success_payment', 'failed_payment')),
CONSTRAINT state_and_payment_url CHECK (state = 'open' OR payment_url IS NOT NULL ),
CONSTRAINT payment_date_greaterEq_create_date CHECK (payment_date IS NULL OR payment_date >= create_date),
)- id — An unique identifier of the basket.
- student_id — An unique identifier of the student who owns the basket.
- payment_url — An url to the payment gateway. If the basket is not paid, then this field must be empty.
-
state — A state of the basket. Can be one of the following
values:
open,pending_payment,success_payment,failed_payment. - create_date — A date when the basket was created.
- payment_date — A date when the basket was paid. If the basket is not paid, then this field must be empty. Must be later or equal to create_date.
CREATE TABLE Parameter
(
name varchar(50) NOT NULL,
value varchar(50) NOT NULL,
date date NOT NULL,
PRIMARY KEY (name, date),
)- name — A name of the parameter.
- value — A value of the parameter.
- date — A date when the parameter was set.
Parameters required by the application:
-
availability_period— A period (in days) in which the student can enroll in the course after the start date of the course. -
module_completion_threshold— A threshold (in percent) of the module completion. If the student completes the module with a grade greater or equal to the threshold, then the module is considered completed. -
internship_length— A length (in days) of the internship. -
internship_completion_threshold— A threshold (in percent) of the internship completion. If the student completes the internship with a grade greater or equal to the threshold, then the internship is considered completed. -
payment_deadline— A deadline (in days) for the student to pay for the basket after the basket was created.
A parameter with the latest date is considered as the current parameter.
CREATE TABLE StudentWebinar
(
student_id int NOT NULL,
webinar_id int NOT NULL,
payment_date date NOT NULL,
PRIMARY KEY (student_id, webinar_id),
FOREIGN KEY (student_id) REFERENCES Student (id) ON DELETE CASCADE,
FOREIGN KEY (webinar_id) REFERENCES Webinar (id) ON DELETE CASCADE,
)The table for many-to-many relationship between Student and Webinar.
- student_id — An unique identifier of the student who attended the webinar.
- webinar_id — An unique identifier of the webinar which the student attended.
- payment_date — A date when the student paid for the webinar.
CREATE TABLE StudentCourse
(
student_id int NOT NULL,
course_id int NOT NULL,
advance_payment_date date NOT NULL,
full_payment_date date,
credit_date date,
certificate_post_date date,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Student (id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES Course (id) ON DELETE CASCADE,
CONSTRAINT advance_payment_date_lowerEq_full_payment_date CHECK (advance_payment_date <= full_payment_date),
CONSTRAINT full_payment_date_lowerEq_credit_date CHECK (full_payment_date <= credit_date),
CONSTRAINT credit_date_lowerEq_certificate_post_date CHECK (credit_date <= certificate_post_date),
)The table for many-to-many relationship between Student and Course.
- student_id — An unique identifier of the student who attended the course.
- course_id — An unique identifier of the course which the student attended.
- advance_payment_date — A date when the student paid for the course in advance.
- full_payment_date — A date when the student paid for the course in full. If the student paid for the course in advance, then this field must be later or equal to advance_payment_date.
- credit_date — A date when the student received a credit for the course. If the student paid for the course in full, then this field must be later or equal to full_payment_date.
- certificate_post_date — A date when the student received a certificate for the course. If the student received a credit for the course, then this field must be later or equal to credit_date.
- The combination of student_id and course_id must be unique.
CREATE TABLE StudentSemester
(
student_id int NOT NULL,
semester_id int NOT NULL,
payment_date date NOT NULL,
PRIMARY KEY (student_id, semester_id),
FOREIGN KEY (student_id) REFERENCES Student (id) ON DELETE CASCADE,
FOREIGN KEY (semester_id) REFERENCES Semester (id) ON DELETE CASCADE,
)The table for many-to-many relationship between Student and Semester.
- student_id — An unique identifier of the student who attended the semester.
- semester_id — An unique identifier of the semester which the student attended.
- payment_date — A date when the student paid for the semester.
- The combination of student_id and semester_id must be unique.
CREATE TABLE StudentStudies
(
student_id int NOT NULL,
studies_id int NOT NULL,
registration_payment_date date NOT NULL,
certificate_post_date date,
PRIMARY KEY (student_id, studies_id),
FOREIGN KEY (student_id) REFERENCES Student (id) ON DELETE CASCADE,
FOREIGN KEY (studies_id) REFERENCES Studies (id) ON DELETE CASCADE,
CONSTRAINT registration_payment_date_lower_certificate_post_date CHECK (registration_payment_date < certificate_post_date),
)The table for many-to-many relationship between Student and Studies.
- student_id — An unique identifier of the student who attended the studies.
- studies_id — An unique identifier of the studies which the student attended.
- registration_payment_date — A date when the student paid for the studies.
- certificate_post_date — A date when the student received a certificate for the studies. If the student paid for the studies, then this field must be later or equal to registration_payment_date.
- The combination of student_id and studies_id must be unique.
CREATE TABLE StudentInternship
(
student_id int NOT NULL,
internship_id int NOT NULL,
attended_days int DEFAULT 0 NOT NULL,
exam_result int,
PRIMARY KEY (student_id, internship_id),
FOREIGN KEY (student_id) REFERENCES Student (id) ON DELETE CASCADE,
FOREIGN KEY (internship_id) REFERENCES Internship (id) ON DELETE CASCADE,
CONSTRAINT not_negative_attended_days CHECK (attended_days >= 0),
CONSTRAINT exam_result_between_0_and_100 CHECK (exam_result >= 0 AND exam_result <= 100),
)The table for many-to-many relationship between Student and Internship.
- student_id — An unique identifier of the student who attended the internship.
- internship_id — An unique identifier of the internship which the student attended.
- attended_days — A number of days which the student attended the internship.
- exam_result — A result of the exam which the student took after the internship.
- The combination of student_id and internship_id must be unique.
CREATE TABLE StudentMeeting
(
student_id int NOT NULL,
meeting_id int NOT NULL,
payment_date date,
PRIMARY KEY (student_id, meeting_id),
FOREIGN KEY (student_id) REFERENCES Student (id) ON DELETE CASCADE,
FOREIGN KEY (meeting_id) REFERENCES Meeting (id) ON DELETE CASCADE,
)The table for many-to-many relationship between Student and Meeting.
- student_id — An unique identifier of the student who attended the meeting.
- meeting_id — An unique identifier of the meeting which the student attended.
- payment_date — A date when the student paid for the meeting.
- The combination of student_id and meeting_id must be unique.
CREATE TABLE StudentMeetingAttendance
(
student_id int NOT NULL,
meeting_id int NOT NULL,
PRIMARY KEY (student_id, meeting_id),
FOREIGN KEY (student_id) REFERENCES Student (id) ON DELETE CASCADE,
FOREIGN KEY (meeting_id) REFERENCES Meeting (id) ON DELETE CASCADE,
)The table for many-to-many relationship between Student and Meeting.
- student_id — An unique identifier of the student who attended the meeting.
- meeting_id — An unique identifier of the meeting which the student attended.
- The combination of student_id and meeting_id must be unique.
CREATE TABLE TranslatorLanguage
(
translator_id int NOT NULL,
language_id int NOT NULL,
PRIMARY KEY (translator_id, language_id),
FOREIGN KEY (translator_id) REFERENCES Translator (id) ON DELETE CASCADE,
FOREIGN KEY (language_id) REFERENCES Language (id) ON DELETE CASCADE,
)- translator_id — An unique identifier of the translator who can speak the language.
- language_id — An unique identifier of the language.
CREATE TABLE BasketItem
(
basket_id int NOT NULL,
course_id int NULL,
meeting_id int NULL,
studies_id int NULL,
webinar_id int NULL,
UNIQUE (basket_id, course_id, meeting_id, studies_id, webinar_id),
FOREIGN KEY (basket_id) REFERENCES Basket (id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES Course (id),
FOREIGN KEY (meeting_id) REFERENCES Meeting (id),
FOREIGN KEY (studies_id) REFERENCES Studies (id),
FOREIGN KEY (webinar_id) REFERENCES Webinar (id),
CONSTRAINT polymorphism CHECK (
(course_id IS NULL AND meeting_id IS NULL AND studies_id IS NULL AND webinar_id IS NOT NULL) OR
(course_id IS NULL AND meeting_id IS NULL AND studies_id IS NOT NULL AND webinar_id IS NULL) OR
(course_id IS NULL AND meeting_id IS NOT NULL AND studies_id IS NULL AND webinar_id IS NULL) OR
(course_id IS NOT NULL AND meeting_id IS NULL AND studies_id IS NULL AND webinar_id IS NULL)
)
)The table for many-to-many relationship between Basket and Course, Meeting, Studies or Webinar (exclusively).
- basket_id — An unique identifier of the basket to which the item belongs.
- course_id — An unique identifier of the course which the item belongs. If the item is not a course, then this field must be empty.
- meeting_id — An unique identifier of the meeting which the item belongs. If the item is not a meeting, then this field must be empty.
- studies_id — An unique identifier of the studies which the item belongs. If the item is not a studies, then this field must be empty.
- webinar_id — An unique identifier of the webinar which the item belongs. If the item is not a webinar, then this field must be empty.
- The combination of basket_id and course_id or meeting_id or studies_id or webinar_id must be unique.