CSE4_Project_-_SQL-PHP-Database

This project involves migrating a previously completed MS Access Database Project to phpMyAdmin and implementing SQL. The focus will be on transferring tables, relationships, and logical constructs.

This repository serves as Database Project in subject CSE4-M - CS Professional Elective 4

PROJECT OVERVIEW

This project is a School Management System that manages students, advisors, departments, and course records. This system provides different functionalities based on the account's role.

• Admin

  • View dashboard
  • Can create, read, update, and delete records
  • View Student, Advisor, Department, and Course records

• Registrar

  • View dashboard
  • Can create, read, and update records

• Advisor

  • View dashboard
  • Can only view the signed-in account information
  • View Department and available course records only

• Student

  • View dashboard
  • Can only view the signed-in account information
  • View Department and available course records only

This project also provides a search_row function to search for a search term.

USAGE

1. Clone the repository:

   git clone https://github.com/ChugxScript/CSE4_Project_-_SQL-PHP-Database.git

2. Install XAMPP.
3. (Optional) Install MySQL and MySQL Workbench and create a user account.
4. Start Apache and MySQL in XAMPP.
5. Log in to phpMyAdmin using localhost/phpmyadmin.
6. Install the PHP Server extension in VS Code.
7. In index.php, select PHP Server: Serve Project.

DATABASE SCHEMA AND TASK

• Task:

  • Design a database schema to store information about students, advisors, departments, and courses.
  • Establish relationships to represent academic advising.

• Solution Database Schema:

  • users

    • user_id (Primary Key)
    • password (varchar)
    • role (enum: 'admin', 'registrar', 'student', 'advisor')

  • student

    • student_id (Primary Key)
    • advisor_id (Foreign Key Reference to advisor_id in advisor table)
    • user_id (Foreign Key Reference to user_id in users table)
    • first_name (varchar)
    • last_name (varchar)
    • assigned_sex (enum: 'Male', 'Female')

  • advisor

    • advisor_id (Primary Key)
    • department_id (Foreign Key Reference to department_id in department table)
    • user_id (Foreign Key Reference to user_id in users table)
    • first_name (varchar)
    • last_name (varchar)
    • assigned_sex (enum: 'Male', 'Female')

  • department

    • department_id (Primary Key)
    • department_name (varchar)
    • course_id (Foreign Key Reference to course_id in course table)
    • location (varchar)

  • course

    • course_id (Primary Key)
    • course_name (varchar)
    • credits (int)

• SCREENSHOTS

• phpMyAdmin database tables

• advisor

  • 

• course

  • 

• department

  • 

• student

  • 

• users

  • 

• RELATIONSHIPS

• Web pages

Guest Dashboard

• Create

  • 

• Update

  • 

• Delete

  • 

• View

  • 

• Search function

  • 

• Admin (has all features)

  • Admin Dashboard (shows the current signed in user)

  • 

  • Student table

  • 

  • Advisor table

  • 

  • Department table

  • 

  • Course table

  • 

• Registrar (dont have delete feature)

  • Registrar Dashboard (shows the current signed in user)

  • 

  • Student table

  • 

  • Advisor table

  • 

  • Department table

  • 

  • Course table

  • 

• Student (has only department and courses tables and can only view rows)

  • Student Dashboard (shows the current signed in user)

  • 

  • Profile

  • 

  • Department table

  • 

  • Course table

  • 

• Advisor (has only department and courses tables and can only view rows)

  • Registrar Dashboard (shows the current signed in user)

  • 

  • Profile

  • 

  • Department table

  • 

  • Course table

  •