DziennikElektronicznyBazyDanych

Progress - school ediary

Some humble guy once said:

Science is the power key

That is why we want to make the learning experience more attractive for students and help teachers organize their work. [in progress]

Authors

Ewelina Flisak
Piotr Socała
Arkadiusz Tyrański

Dev stack

Front: React
Database: Postgresql
Backend: NodeJs + Express.js
Endpoint testing: Postman

Repositories

Run application

open (or install if you don't have) pgAdmin 4, create database called 'schoolDiaryV5' for user 'postgres'
open query console, copy and paste whole database script and execute all queries
if you want to use testing data then copy and paste whole testing data
create directory for whole Progress application (ex. C:/progress-app) and open the git bash console inside this directory
clone backend repository by writing following command in your git bash console:

git clone https://github.com/DziennikElektronicznyBazyDanych/backend backend-app

open directory 'backend-app' with VS Code
open terminal (Ctrl + `) and type following command to install all dependencies:

yarn install

create file called '.env' inside 'backend-app' directory, write following and replace <password> with your password to postgress

DB_PASSWORD=<password>

run server with following command:

yarn start

get back to your progress directory (ex. C:/progress-app), again open the git bash console inside this directory
clone frontend repository by writing following command in your git bash console:

git clone https://github.com/DziennikElektronicznyBazyDanych/frontend frontend-app

open directory 'frontend-app' with VS Code
open terminal (Ctrl + `) and type following command to install all dependencies:

yarn install

run client app with following command:

yarn start

Client application views

Login screen

Register screen

Student home page

Student profile

Student schedule

Student grades

Student tests

Student attendance summary

Teacher profile

Teacher home page

Teacher schedule

Teacher lesson panel

Teacher add grade modal

Database views, procedures and functions

Views

view_all_students_attendance

view_all_students_presence

view_all_students_absence

view_all_classes_schedules

view_all_teachers_courses

view_all_lesson_instances

view_all_students_overall_average_grades

view_all_students_average_grades_per_course

Procedures

proc_create_lesson(
    leading_teacher_id int, 
    class_id int, 
    course_id int, 
    day_id int,
    classroom_id int, 
    start_time time, 
    end_time time, 
    year varchar(4), 
    semester bit
)

proc_add_classroom(
    room_name varchar(4), 
    size smallint
)

proc_add_test(
    lesson_instance_id int, 
    material_scope varchar(200), 
    test_weight int
)

proc_generate_lessons_instances(
    date_from DATE, 
    date_to DATE, 
    semester BIT(1),
	day_id INTEGER,
	lesson_id INTEGER,
	teacher_id INTEGER,
	classroom_id INTEGER
)

proc_create_class(
	class_teacher INTEGER,
	class_name VARCHAR(2)
)

proc_create_student()

proc_create_teacher()

proc_add_student_to_class(
	student_id INTEGER, 
	class_id INTEGER
)

proc_create_course(
	course_name VARCHAR(50)
)

proc_edit_classroom(
    IN classroom_id integer, 
    IN room_name character varying, 
    IN size integer
)

proc_edit_latecoming(
    IN lessoninstanceid integer, 
    IN studentid integer, 
    IN waslate boolean
)

proc_edit_test(
    IN testid integer, 
    IN lessoninstanceid integer, 
    IN materialscope character varying, 
    IN testweight integer
)

proc_excuse_absence(
    IN lesson_instance_id integer, 
    IN student_id integer, 
    IN excuse_contents character varying
)

proc_promotestudents()

Functions

func_lesson_student_attendance(
    student_id int,
    lesson_instance_id int
) 
RETURNS (
    was_present bool
)

func_add_absence(
    in_lesson_instance_id int,
    in_student_id int
) 
RETURNS (
    success int
)

func_add_attendance(
    in_lesson_instance_id int,
    in_student_id int
) 
RETURNS (
    success int
)

func_edit_grade(
    grade_id int,
    lesson_instance_id int,
    grade_value int,
    grade_type int,
    weight int,
    description varchar(40)
) 
RETURNS (
    success int
)

func_delete_grade(
    grade_id int
) 
RETURNS (
    success int
)

func_add_grade(
    lesson_instance_id int,
    student_id int,
    grade int,
    grade_type int,
    weight int,
    description varchar(40)
) 
RETURNS (
    success int
)

func_expell_student(
    student_id int
) 
RETURNS (
    success int
)

func_register(
    in_first_name VARCHAR(20), 
    in_last_name VARCHAR(50),
    in_sex BIT(1), 
    in_birth_date DATE, 
    in_city VARCHAR(20), 
    in_postal_code VARCHAR(6), 
    in_email VARCHAR(50), 
    in_password VARCHAR(50),
    in_type VARCHAR(1)
)
RETURNS (
    result int
)

func_get_enrolled_courses(
    in_student_id int
)
RETURNS (
    course_id   int,
    course_name varchar(50)
)

func_get_test_for_class(
    in_class_id int
)
RETURNS (
    test_id int,
    material_scope varchar(200),
    test_weight int,
    teacher_id int,
    first_name varchar(20),
    last_name varchar(50),
    is_cancelled boolean,
    lesson_date date
)

func_login(
    in_email VARCHAR(50),
    in_password VARCHAR(100)
)
RETURNS (
    person_id int,
    first_name varchar(20),
    last_name varchar(50),
    sex bit,
    birth_date date,
    city varchar(20),
    postal_code varchar(6),
    email varchar(50),
    start_date date,
    end_date date,
    class_id int,
    class_name varchar(2),
    account_type varchar(1)
)

func_class_schedule_between(
    in_class_id int,
    in_year varchar(4),
    in_semester bit,
    in_date_from date,
    in_date_to date
)
RETURNS (
    lesson_instance_id int,
    lesson_id int,
    teacher_id int,
    first_name varchar(20),
    last_name varchar(50),
    course_id int,
    course_name varchar(50),
    day_id int,
    day_name varchar(12),
    classroom_id int,
    room_name varchar(4),
    start_time time,
    end_time time,
    year varchar(4),
    semester bit,
    is_cancelled boolean,
    lesson_date date
)

func_student_absence_at_day(
    student_id int, 
    date date
)
RETURNS (
    lesson_instance_id int, 
    lesson_id int, 
    course_name varchar(50)
)

func_student_grades_per_course(
    in_student_id int,
    in_year varchar(4)
)
RETURNS (
    grade_id int,
    grade smallint,
    lesson_instance_id int,
    lesson_date date,
    lesson_id int,
    course_id int,
    course_name varchar(50),
    grade_name varchar(30),
    weight smallint,
    description varchar(40)
)

func_student_grades_from_course(
    student_id int, 
    course_id int
)
RETURNS (
    grade_id int, 
    grade smallint, 
    lesson_instance_id int, 
    lesson_date date, 
    lesson_id int,
    grade_name varchar(30), 
    weight smallint, 
    description varchar(40)
)

func_student_attendance_all(
    in_student_id INT,
    year varchar(4),
    semester bit
) 
RETURNS (
    success int,
    presence int,
    absence int
)

func_student_attendance_at_day(
    in_student_id INTEGER,
    in_date DATE
)
RETURNS (
    student_id INTEGER, 
    first_name VARCHAR(20), 
    last_name VARCHAR(50), 
    lesson_instance_id INTEGER,  
	lesson_date DATE, 
    lesson_id INTEGER, 
    course_name VARCHAR(50), 
    was_present INTEGER
)

func_student_presence_all(
    in_student_id INTEGER
)
RETURNS (
    student_id INTEGER, 
    first_name VARCHAR(20), 
    last_name VARCHAR(50), 
    lesson_instance_id INTEGER,  
	lesson_date DATE, 
    lesson_id INTEGER, 
    course_name VARCHAR(50), 
    was_present INTEGER
)

func_student_presence_at_day(
    in_student_id INTEGER,
    in_date DATE
)
RETURNS (
    student_id INTEGER, 
    first_name VARCHAR(20), 
    last_name VARCHAR(50), 
    lesson_instance_id INTEGER,  
	lesson_date DATE, 
    lesson_id INTEGER, 
    course_name VARCHAR(50), 
    was_present INTEGER
)

func_student_absence_all(
    in_student_id INTEGER
)
RETURNS (
    student_id INTEGER, 
    first_name VARCHAR(20), 
    last_name VARCHAR(50), 
    lesson_instance_id INTEGER,  
	lesson_date DATE, 
    lesson_id INTEGER, 
    course_name VARCHAR(50), 
    was_present INTEGER
)

is_first_name_correct(
    first_name VARCHAR(20)
) 
RETURNS boolean

is_only_letters(
    name VARCHAR(20)
) 
RETURNS boolean

is_postal_code(
    name VARCHAR(6)
) 
RETURNS boolean

email_exists(
    e_email VARCHAR(50)
)
RETURNS boolean

func_class_announced_tests(
    class_id integer
) 
RETURNS (
    test_id integer, 
    lesson_instance_id integer, 
    material_scope character varying, 
    test_weight integer
)

func_class_list(
    class_id integer
)
RETURNS (
    student_id integer, 
    first_name character varying, 
    last_name character varying, 
    email character varying, 
    birth_date date, 
    city character varying, 
    postal_code character varying, 
    start_studying date, 
    end_studying date
)

func_class_schedule(
    class_id int, 
    year_param character varying, 
    semester_param bit
)
RETURNS (
    lesson_id integer, 
    teacher_id integer, 
    teacher_name character varying, 
    teacher_last_name character varying, 
    course_id integer, 
    course_name character varying, 
    day_name character varying, 
    classroom_id integer, 
    room_name character varying, 
    start_time time without time zone, 
    end_time time without time zone, 
    year character varying, semester bit
)

func_teacher_courses(
    teacher_id integer
) 
RETURNS (
    lesson_id integer, 
    course_id integer, 
    course_name character varying, 
    class_id integer, 
    class_name character varying
)

func_teacher_schedule_between(
    int_teacher_id int,
    in_year varchar(4),
    in_semester bit,
    in_date_from date,
    in_date_to date
) 
RETURNS (
    lesson_instance_id int,
    lesson_id int,
    class_id int,
    class_name varchar(2),
    day_id int,
    day_name varchar(12),
    classroom_id int,
    classroom_name varchar(4),
    start_time time,
    end_time time,
    course_id int,
    course_name varchar(50),
    is_cancelled boolean,
    lesson_date date
)

API Endpoints

Autentykacja

✔️ auth/login

metoda: POST
body:
- email::varchar(50),
- password::varchar(50)
output:

{
	person_id: number,
	first_name: string,
	last_name: string,
	sex: string,
	birth_date: date,
	city: string,
	postal_code: string,
	email: string,
	start_date: string,
	end_date: string,
	class_id: number,
	class_name: string,
	account_type: string,
}

przykładowe wykorzystanie funkcji z bazy danych:

SELECT * FROM func_login('[email protected]', 'KarolinaJakubowska834'); 	# logowanie studenta
SELECT * FROM func_login('ł[email protected]', 'ŁucjaGrabowska159');		# logowanie nauczyciela

te dane nie powinny zadziałać

SELECT * FROM func_login_student('[email protected]', 'Alamakota1234');

✔️ auth/register

metoda: POST
body:
- first_name::varchar(20),
- last_name::varchar(50),
- sex::bit,
- birth_date::date,
- city::varchar(20),
- postal_code::varchar(6),
- email::varchar(50),
- password::varchar(50),
- type: varchar(1),
output:

{	 
	success: boolean,
	message: string,
}

przykładowe wykorzystanie funkcji z bazy danych:

-- dodanie studenta
SELECT * FROM func_register('Zenon', 'Krasicki', 0::bit, '2000-04-15', 'Kraków', '31-852', '[email protected]', 'zenKras1234', 'S');

-- dodanie nauczyciela
SELECT * FROM func_register('Paulina', 'Sowa', 1::bit, '1980-09-02', 'Kraków', '31-551', '[email protected]', 'paulSow1234', 'T');

Student

✔️ student/schedule

metoda: POST
body:
- class_id::int,
- year::varchar(4),
- semester::bit,
- date_from::date,
- date_to::date
output:

[
	{
		lesson_instance_id: number,
		lesson_id: number,
		leading_teacher_id: number,
		first_name: string,
		last_name: string,
		day_id: number,
		day_name: string,
		classroom_id: number,
		classroom_name: string
		start_time: string,
		end_time: string,
		course_id: number,
		course_name: string,
		is_cancelled: boolean,
		lesson_date: string,
	}
]

przykładowe wykorzystanie funkcji z bazy danych:

SELECT * FROM func_class_schedule_between(1,'2022',0::bit, '2022-09-05', '2022-09-09');

✔️ student/tests

metoda: POST
body:
- class_id::int,
- year::varchar(4),
output:

[
  {
    test_id: number,
    material_scope: string,
    test_weight: number,
    teacher_id: number,
    first_name: string,
    last_name: string,
    is_cancelled: boolean,
    lesson_date: string
  }
]

przykładowe wykorzystanie z bazy danych:

SELECT * FROM func_get_test_for_class(1);

✔️ grades

metoda: POST
body:
- student_id::int,
- year::varchar(4)
output:

[
  {
    course_id: number,
    course_name: string,
    grades: [
    	grade_id: number,
      lesson_instance_id: number,
      grade: number,
      grade_type: string,
      weight: number,
      description: string
    ]
  }
]

przykładowe wykorzystanie funkcji z bazy danych:

SELECT * FROM func_get_enrolled_courses(20);
SELECT * FROM func_student_grades_per_course(20, '2022');

Pierwsza funkcja jest potrzebna żeby pobrać listę przedmiotów, na które dany student jest zapisany, druga po to, żeby pobrać oceny.

✔️ student/attendance

metoda: POST
body:
- student_id::int,
- year::varchar(4)
- semester::bit
output:

 {
    status: string,
    presence: number, 
    absence: number
 }

przykładowe wykorzystanie funkcji z bazy danych:

SELECT * FROM func_student_attendance_all(16::int, '2022'::varchar(4), 0::bit);

Nauczyciel

✔️ teacher/schedule

metoda: POST
body:
- teacher_id::int,
- year::varchar(4),
- semester::bit,
- date_from::date,
- date_to::date
output:

[
	{
		lesson_instance_id: number,
		lesson_id: number,
		class_id: number,
		day_id: number,
		day_name: string,
		classroom_id: number,
		classroom_name: string
		start_time: string,
		end_time: string,
		course_id: number,
		course_name: string,
		is_cancelled: boolean,
		lesson_date: string,
	}
]

przykładowe wykorzystanie funkcji z bazy danych:

SELECT * FROM func_teacher_schedule_between(1, '2022', 0::bit, '2022-09-05', '2022-09-09');

✔️ student/expell/:student_id

metoda: GET
output:

 {
    	status: string,
 }

przykładowe wykorzystanie funkcji z bazy danych:

SELECT * FROM func_expell_student(42);

✔️ teacher/schedule/lesson

metoda: POST
body:
- class_id::int,
- course_id::int,
- lesson_instance_id::int,
output:

{
	status: string,
	students: [
		student_id: number,
		first_name: string,
		last_name: string,
		attendance: string,
		grades: [
			grade_id: number,
			grade: number,
			grade_name: string,
			weight: number,
			lesson_date: string,
			lesson_id: number,
			description: string,
		]
	]
}

przykładowe wykorzystanie funkcji z bazy danych:

// Tutaj dostaniemy listę uczniów z danej klasy
SELECT * FROM func_class_list(1); 

// Tą funkcją pobieramy wszystkie oceny dla studenta o student_id=21 z kursu o course_id=1
SELECT * FROM func_student_grades_from_course(21, 1);

// Ta funkcja zwróci boola czy student o student_id=17 był obecny lub nie,
// albo NULL'a, jak nie ma nic wpisanego na lesson_instance od lesson_instance_id=10
SELECT * FROM func_lesson_student_attendance(17, 10);

✔️ grades/add

metoda: POST
body:
- lesson_instance_id::int,
- student_id::int,
- grade::int,
- grade_type::int,
- weight::int,
- description::varchar(40)
output:

{
	success: boolean,
	message: string,
}

przykładowe wykorzystanie funkcji z bazy:

SELECT * FROM func_add_grade(30, 21, 4, 3, 2, 'Odpowiedź z równań kwadratowych');

✔️ grades/remove

metoda: POST
body:
- grade_id::int,
output:

{
	// -1 = >Grade of id % does not exist
	success: boolean,
	message: string,
}

przykładowe wykorzystanie funkcji z bazy:

SELECT * FROM func_delete_grade(91);

✔️ grades/edit

metoda: POST
body:
- grade_id::int,
- lesson_instance_id::int,
- grade_value::int,
- grade_type::int,
- weight::int,
- description::varchar(40)
output:

{
	success: boolean,
	message: string,
}

przykładowe wykorzystanie funkcji z bazy danych:

SELECT * FROM func_edit_grade(92, 30, 2, 3, 2, 'Odpowiedź z równań kwadratowych');

✔️ student/attendance/add

metoda: POST
body:
- lesson_instance_id::int,
- student_id::int
- wasPresent::bool
output:

{
	success: boolean,
	message: string,
}

przykładowe wykorzystanie funkcji z bazy danych:

// Tej funkcji użyjemy zawsze, dodaje do tabeli attendance
SELECT * FROM func_add_attendance(7, 11);

// Jeśli uczeń nie będzie obecny, dodajemy go do nieobecnych
SELECT * FROM func_add_absence(7, 11);

Progress - school ediary

Authors

Dev stack

Repositories

Run application

Client application views

Login screen

Register screen

Student home page

Student profile

Student schedule

Student grades

Student tests

Student attendance summary

Teacher profile

Teacher home page

Teacher schedule

Teacher lesson panel

Teacher add grade modal

Database views, procedures and functions

Views

Procedures

Functions

API Endpoints

Autentykacja

✔️ auth/login

✔️ auth/register

Student

✔️ student/schedule

✔️ student/tests

✔️ grades

✔️ student/attendance

Nauczyciel

✔️ teacher/schedule

✔️ student/expell/:student_id

✔️ teacher/schedule/lesson

✔️ grades/add

✔️ grades/remove

✔️ grades/edit

✔️ student/attendance/add

Popular repositories Loading

Repositories

People

Top languages

Most used topics