itsdmd/cs207b

SchoolNexus - An all-in-one school management system for principals, teachers, and students.

SchoolNexus

An all-in-one school management system for principals, teachers, and students.

Overview

SchoolNexus is a web application that aims to simplify the management of schools. It provides a platform for principals, teachers, and students to interact with each other. The application is divided into two main parts: the API server (SchoolNexus-Server) and the web server (SchoolNexus-Web).

The API server is built with Node.js and Prisma, a modern database toolkit that makes it easier to work with databases. It provides a GraphQL API that allows clients to interact with the database. The database engine used is PostgreSQL, a powerful, open-source object-relational database system and is fully supported by Prisma.

The web server is built with ReactJS and Vite framework. It provides a user-friendly, easy to maintained interface using Bootstrap 5 for admins, principals, teachers, and students to access the allowed features provided by the server for each role.

Setup

# Install dependencies
cd SchoolNexus-Server
npm i
cd ../SchoolNexus-Web
npm i
cd ..

# Setup server
cd SchoolNexus-Server

Edit the .env file to match your database configuration, especially the DATABASE_URL variable to match PostgreSQL's connection string (example: DATABASE_URL=postgres://username:password@localhost:5432/schoolnexus)

Make sure you have PostgreSQL server installed and running before continue.

Tutorial for Windows: https://www.postgresqltutorial.com/postgresql-getting-started/install-postgresql/

Tutorial for MacOS: https://www.postgresqltutorial.com/install-postgresql-macos/

Tutorial for Linux: https://www.postgresqltutorial.com/install-postgresql-linux/

Note: MySQL cannot be used since Prisma does not support array of enums for this provider, which is needed for this project's database schema.

# Generate database schema
npm run dtb-migrate

# (Optional) Seed the database with dummy data for testing
npm run dtb-seed

# Start the API server
npm start

Then, open a new terminal and run the following commands:

cd ..
cd SchoolNexus-Web

# Start the web server
npm run dev

⚡ SchoolNexus Server

Managing database and handle API requests for SchoolNexus.

This project was scaffolded using Apollo GraphQL Server. PostgreSQL is used for the database and managed using Prisma.

Database

Initialize

Prequisites:

• A working PostgreSQL server (installation guide) with a database created.
• Edit DATABASE_URL in .env file to match your database server configurations.

To initialize Prisma, run the following command:

# Migrating (read below for further details)
npm run dtb-migrate

# Seeding (read below for further details)
npm run dtb-seed

# (Optional) Start Prisma Studio
npx prisma studio

If you don't use PostgreSQL (not recommended), please run the following command instead:

npx prisma init --datasource-provider <provider>

More details can be found in the Prisma documentation.

Migration

When you create a new database schema, make changes to an existing one or recently pulled new commits, a new migration file needs to be generated. To do so, run the following command:

npm run dtb-migrate

More details can be found in the Prisma documentation.

TL;DR: Migrate helps to keep track of changes to the database schema and saved in the prisma/migrations directory. When new breaking changes are made, delete the prisma/migrations directory and run dtb-migrate & dtb-seed again.

Seeding

Populate the database with dummy data set for development and testing by running the following command:

npm run dtb-seed

⚠️ WARNING: By default, this command will delete all existing data in the database and populate it with dummy data. Modify the lines at the bottom of SchoolNexus-Server/src/models/_seeder.js file as needed.

Seeding order

1. User
2. Relative
3. School
4. Room (dependencies: School.id)
5. UserSchoolAssignment (d: User.id; School.id)
6. Classs (d: School.id)
7. Subject
8. TeacherSubjectAssignment (d: Teacher.id; Subject.id)
9. UserClasssAssignment (d: User.id; Classs.id)
10. Semester
11. TimetableEntry (d: Semester.id; School.id; Classs.id)
12. TimetableEntryAttendence (d: TimetableEntry.id; User.id)
13. GradeType
14. StudentGrade (d: Student.id; Teacher.id; Semester.id; Subject.id; GradeType.id)
15. Meeting (d: School.id; Room.id; User.id)
16. MeetingAttendence (d: Meeting.id; User.id)

Classs is NOT a typo. It is the used to avoid conflict with the keyword class used in JS.

FAQ

Why PostgreSQL was chosen? Why not MySQL or MongoDB?

+ Pros

• It is more SQL-compliant, supports most standard SQL subqueries (eg. LIMIT, ALL, etc.) and clauses (INTERSECT, OUTER JOIN, etc.) that are not supported by MySQL. It's not a deal-breaker to not have them, but they can provide more flexibility and intuitiveness when writing more complex queries.
• It is an object-relational database, which means it supports more complex data types natively (eg. arrays, JSON, etc.) and allow propety inheritance. This is suitable for this project since most of the data are represented as objects.
• Data are structured and need integrity, therefore NoSQL databases like MongoDB are not suitable for this project.
• PostgreSQL is fully open-source and released under PostgreSQL License, making it completely free to use.
• Extensive support for all Prisma features related to relational database. See Prisma documentation for more details.

- Cons:

• It is more resource-intensive than MySQL since it needs to generate a new system process via memory allocation for every client connection established.
• It has no commercial support and rely fully on community or voluntary support, make it more difficult to maintain and find helps when needed.

Database visualization

Rendered ERD file

For an interactive diagram, please visit this link: dbdiagram.io

Why use Prisma?

Prisma is an open-source database toolkit that makes it easier to work with databases, including database migrations, schema management, and data access.

• Easily connect to and operate on database with Prisma Client
• Keep track of database schema changes and sync them with local database with Prisma Migrate
• More abstract and formulated way to perform CRUD operations with the use of object-relational mapping (ORM)

Why use GraphQL?

GraphQL is a query language for APIs and a runtime for fulfilling those queries with existing data. GraphQL gives clients the ability to ask for exactly what they need, makes it easier to evolve the API over time while maintaining speed, flexibility, and bandwidth usage.

Apollo helps to make the process of building GraphQL APIs easier and faster by providing a set of tools and best practices.

Are seeded data fully randomized?

No. The data are generated with some constraints to ensure data integrity.

For example, a student born in 2010 can only be a member of a 7th or 8th grade class as of 2023, and each class can only have a limited number of students with 1 form teacher.

To fine-tune the constraints and conditions, edit the SchoolNexus-Server/src/models/_seeder.js file as needed.

How are the passwords stored?

Passwords are hashed using bcrypt with a default salt of 10 rounds. The salt is automatically generated and stored in the hash itself, so there is no need to store it separately.

🌐 SchoolNexus Web Interface

Web interface for SchoolNexus.

This project was scaffolded using Vite. ReactJS is used for the frontend and Bootstrap 5 for styling.

FAQ

Why was React used? Why not Vue, Svelte, or plain HTML/CSS/JS?

React was chosen for its popularity, extensive community support, and the fact that it allows developers to create large web applications that can update data and content without reloading the page. It also allows for the creation of reusable UI components, which improves the extensibility and maintainability of the codebase. This will also make it easier to create a mobile app in the future using React Native.

Plain HTML/CSS/JS is a valid choice for simple websites or applications, but as the complexity of the project increases in the future, it can become challenging to manage and scale.

Why was Vite used? Why not Create-React-App, Next.js, or Gatsby?

Vite was chosen for its speed and simplicity. It is a build tool that aims to provide a faster and leaner development experience for modern web projects. Next.js and Gatsby are great for server-side rendering and static site generation respectively, but are not necessary for this project. CRA was also considered, but ultimately rejected due to its slower performance compared to Vite.

cs207b's Issues

Update database schema

• Remove UserSchoolAssignment
• Remove TimetableEntry.attendenceId
• Rename Quarter to Semester
• Remove FormTeacherAssignment, replace with Classs.formTeacherId

Server - Client communication

Use GraphQL for API

[func] Login

Create new user login session and redirect into home screen.

[ui::Admin] Delete user

The form should have:

• A text field to input userId
• A container that have entries for matched users, each entry display userId and a delete button

[ui::Prin] Create class

The form should have:

• Text input for class name
• Dropdown to select class' grade (1, 2, ..., 12)
• Dropdown to select form teacher

Use AsyncStorage fall-back for web

SecureStore is not supported by web browser. Use AsyncStorage as fall-back option.

Page redirection

Redirect between pages/views using react-router

[ui] Login screen

Top bar

• Back button
• User full name
• (?) Notification button
• Logout button

[ui::Admin] fix views/Admin/NewAccount.jsx

All fields of the form must be inside a <Form> tag, including submit button. See views/Login.jsx for example.

[ui] Timetable component

A timetable is a table with 7 columns for 7 days in a week, and 8 rows for 8 class periods.

The naming scheme for each cell: <day>-<period>, each index starts at 0. For example, the cell represents the first class period on Monday will have the class name 0-0; the third class period on Wednesday will be 2-2.

Each cell should have a placeholder text Subject.

The result table should look something like this:

	Mon	Tue	...
1	Subject	Subject	Subject
2	Subject	Subject	Subject
...	Subject	Subject	Subject

Bonus: The first row and first column should have bold text and colored background.

[api] Populate timetable with data from server

[func] Logout

Delete user login session and redirect to login screen.

[func::Admin] New User account

• [ui] Form
• [api] API

Database essential seeding

Functionality to populate dummy data to the database for testing.

[ui] Populate user functionality cards based on account type

Account type can be checked using localStorage.getItem("userAccountType")

• ADMIN
  • Create new user (/user/new)
  • Delete user (/user/del) #76
• PRINCIPAL
  • Create class (/class/new) #77
  • Assign teachers & students to classes (/class/assign)
  • Create timetable entry (/timetable/edit) #78
• TEACHER
  • View timetable (/timetable/view)
  • Create student grades (/grade/new)
• STUDENT
  • View timetable (/timetable/view)
  • View grade (/grade/view)

[func::Prin] Assign class to teachers and students

• [ui] Form
• [api] Query

[ui] Home screen

• Admin
• Principal
• Teacher
• Student

[ui::Teacher] Create grading for students

The form must have:

• Dropdown to choose class
• Dropdown to choose student
• Dropdown to choose type of grade
• Text input for grading value
• Submit button

📢 One branch per issue. Avoid pushing to main branch.

[api] Implement functionality for teacher's grading form

[ui] Settings screen

[ui::Prin] Create timetable entry

The form should have:

• 2 radio buttons to select between teacher and student
• A dropdown to select userId
• If selected teacher
  • A dropdown to select classsId
  • A dropdown to select dayOfWeek (Mon to Sat)
  • A dropdown to select timeSlot (1 to 8)
• If selected student
  • A dropdown to select classsId