Database design for course selection and course grading system

. In the context of global education, universities and institutions emphasize the importance of proficient academic information management systems. As traditional approaches to course data management become obsolete, there is a growing need to leverage digital transformation for academic management. This paper proposes a novel course database design that centralizes information about subjects, courses, staff, students, buildings, and grades. The database is designed to facilitate the overall academic progress of students by ensuring efficient record keeping, retrieval and updating. The architecture simplifies administrative tasks, underscores the importance of databases in modern educational institutions by providing in-depth student data to support personalized learning, enhance communication among stakeholders, and aid academic research. The conclusion of the experimental test is that a database designer should always ensure they pay attention to avoiding data redundancy and ensuring data diversity when processing data.


Introduction
Nowadays, universities and educational institutions around the world are increasingly recognizing the importance of building an efficient academic information management system.The essence of modern education should revolve around acquiring knowledge, managing knowledge and disseminating it [1].With the continuous development of teaching methods, course management has become a challenging and complex task [2,3,4].Then the design of course database in this article plays a key role in this aspect.The entire database includes information on subjects, courses, staff and students, all of which contribute to a student's overall academic growth.However, an ongoing challenge is to systematically record, update and retrieve large amounts of data in an efficient and user-friendly manner.With technology integrated into nearly every aspect of education, traditional methods of managing course data have become redundant, if not outright obsolete.Educational institutions, from elementary schools to elite universities, are struggling not only to keep pace with digital transformation, but to harness its power to improve academic management.
The value of educational databases lies in efficient management: the database simplifies administrative tasks for educational institutions.They help to systematically manage and organize large amounts of data, making it easier to access, modify, and update.Personalized Learning: With access to detailed student data, educators can design a more personalized learning experience to meet the needs of individual students.Communication and Collaboration: Databases facilitate better communication between educators, and students.Teachers can provide feedback, and parents can also use feedback to monitor children's progress.Research and Development: Academic databases facilitate scholarly research by providing access to prior research, ensuring that knowledge is not developed in isolation.
This article details the relationship between courses, students, employees, teaching buildings, and grades, and discusses methods for comprehensively storing student information and their corresponding grades.It emphasizes the importance of educational databases for the modern management of educational institutions, highlighting their role in aligning with societal digital transformation trends.

Database Requirements
• Requirements for students: Students should be able to browse the list of available courses and select the ones they are interested in.When choosing courses, they should also be able to view the start and end dates for each course.The course selection system should offer search and filter functions, making it easier for students to find courses based on keywords, credits, timings, and other criteria [2].Detailed information about each course, including the instructor, schedule, and course description, should be accessible to students.Finally, students should have the capability to add chosen courses to their personal course selection list.

• Requirements for Class:
Students can view various details of their courses at any time to decide which courses they will choose to take each semester.
• Requirements for student_Grade: Each student's grade for each class will be calculated based on their attendance, homework, and test scores, and the final calculated grade will be their final grade after the class ends.
• Requirements for Department: Every student should choose their own major.Each major corresponds to a department, which has a designated person in charge and a specific teaching building.
• Requirements for Attendance/Assignments/Exam: Each class will record the attendance score of each student, and also record the attendance status of each student, such as presence, lateness, and absence, and each of their grades will be recorded.Each class will record the assignments' scores of each student, and the final assignments will have a percentage of the total grade.Each class will record Exam scores of each student.For example, some classes have 3 exams, namely exam 1, exam 2, and exam 3.And some courses only have 2 exams, one is midterm exam and the other is final exam.

Database functions
In response to the above requirements, the following features have been designed for this article • Update and modify student details, such as date of birth, address, email, phone number, and other related information.• Add, search, or drop a class, as well as check its start time, end date, corresponding section, and the designated staff.• View student grades for each class and access detailed breakdowns, including attendance' scores, assignments' scores, and Exam' scores.• Verify the department chosen by each student and the address of that department.
• Integrate data from courses, student grades, attendance, homework, and exams, allowing a comprehensive view of each student's performance in every class."a) As seen in the relationship between "student" and "department", students are from different departments; each student belongs to only one department, and each department has one head and a specific location on campus (Figure 1).b) As seen in the relationship between "student" and "course", each student can select more than one course, and each course has a specific name and credit value.c) As seen in the relationship between "course" and "enrollment", every course has an enrollment, and students can choose different sections of a single course.They have the option to both enroll in and drop courses.All the data can be recorded in the system.d) As seen in the relationship between "course" and "classroom", every course is assigned a designated classroom located in a specific building with limited capacity.e) As seen in the relationship between "course" and "faculty", each course is taught by multiple faculty members, including professors, associate professors, TA, etc.The information to be held on each faculty member includes their name, title, email, and phone number.
f) As seen in the relationship between "student" and "enrollment", one student can have many enrollments since a single student can enroll in multiple courses or sections.g) As seen in the relationship between "student_grade", "attendance", "assignments", and "exam", we can see that the relationship between "student_grade" and other 3 entities is 1 to many(1..*), since each student's grade is recorded and contains their attendance, assignments and exam scores.

Entities, attributes and its relational model
When designing the EduHub database, we selected the following 10 entities and their corresponding attributes, as shown in table 1. Next, the relationship between them will be explained in detail in the next section, such as what is a one to one relationship and what is a one to many relationship, and in this ER diagram as shown below that most of the relationships are one to many [4].

Normalization
When we were designing the EduHub database, and make sure we were creating good entities and its attributes, we noticed that we need to do normalization for "course" entity and "student_grade" entity as seen in Figure 2 and Figure 3. Normalization in database design is a systematic approach to organizing data to reduce redundancy and ensure data integrity [5].It should be used when aiming to achieve a clear and efficient database structure, especially when avoiding data anomalies and ensuring referential integrity.
The reason why we decided to make normalization for these two entities is because avg_grade as attributes in "course" entity would determine "grdScale_Course".For example, if the avg_Grade of a course is 93%, it indicates that the grdScale_Course is A-as a letter grade.
And we made normalization for "student_grade" is because grdValue would determine grdScale_student.For example, if a student's final grade (grdValue) is 96%, it would indicate that he gets an A as his letter grade (grdScale_Student).

Database Functions
Here is a list of some of the functions used and the goals achieved in the database of this course selection and grade management system.1.Data Storage: Databases provide a systematic and organized way to store large amounts of data, making it easily accessible and manageable [6].A lot of information is stored in each chart in the database.For example, in the student chart, the following information is stored, student ID, date of birth (in the form of year-month-date), address, phone number, and email address.In fact, according to design requirements, the type and diversity of information can be changed.One of the most basic functions of a database is to store a large amount of data.2. Data Retrieval: One of the primary functions of a database is to allow the retrieval of data for various applications and queries [7].For example, in the student chart, the student's ID, date of birth, phone number and address, and email address are stored.When we want to query the name of a student, we can query by the student's name, but if some students have the same name, then we need to query by studentID.Querying with studentID is the most accurate, because studentID is unique and cannot be repeated.3. Data Update and Deletion: Databases allow for the modification and deletion of data, ensuring that the information stored remains current and relevant [8][9].For example, in the student table, if we want to change the email of the student Mike, we need to enter the student table, and then use some sql codes to operate.

SQL command for creating database
In this section, this work lists databases as an example to show how to create the database in MySQL platform.
CREATE  [10].The 'Primary KEY' constraint uniquely identifies each record in a database table which means that every "deptID' must be unique and cannot be 'NULL'.And 'VARCHAR' stands for variable character, and it can store up to 50 characters in this case.Select * from department, this sql statement is used to retrieve all the records from the "Department" table.
"Select" is a way of saying select all columns, so it will return every column for every row in the table.In this case, "c.crsID" is used to retrieve the "crsID" columns from the "course" table.Here, the "Course" table has been aliased as "c" for brevity and to avoid ambiguity."From Course C" is used to specify the primary table from which you are selecting data.In this example, it is the "Course" table."JOIN Classroom cls" uses "JOIN" operation, it is instructing the database to combine rows from the "Course" table and the "Classroom" table.And "Classroom" table is given an alias "cls"."On c.clsrmID=cls.clsrmID" is used to specify the condition for the it tells the database how to combine the rows from the "Course" and "Classroom" table.(Table5) If we want to list the information of the faculty of each course, it should list the name of faculty, the title of faculty, the email of faculty and phone number as well (Table 6).SELECT c.crsID, c.crsName, f.facID, f.facName, f.facTitle, f.facEmail, f.facPhoneNbrIn summary, effective database queries enable educational institutions to efficiently retrieve and filter critical data.As demonstrated, the aliasing technique helps simplify complex queries, ensuring clarity and avoiding ambiguity.The first query illustrates the advantage of the JOIN operation in combining information from the Courses and Classes tables, providing a clear view of where each course is taught.Finally, the WHERE clause feature is evident in the third query, which enables institutions to set data retrieval criteria, such as identifying high-performing courses based on grade point average.Such targeted queries enable educational entities to make data-driven decisions that leverage optimal resource allocation and enhanced academic management.As the world of education continues to evolve, mastering these database operations remains critical to managing and utilizing the vast amounts of data available.As a database designer, we should be careful and cautious to avoid data duplication and redundancy, while ensuring data integrity.

Conclusion
This article mainly discusses the role of a database designer.When designing and generating a database, it is crucial to consider every detail and ensure the correct selection of entities and attributes.This not only determines the structure and efficiency of the database but also impacts the speed of future data queries and operations.Properly defining entities and attributes helps reduce data redundancy and duplication, enhancing the performance and maintainability of the database.Moreover, maintaining data diversity ensures that the database can meet various query needs, rendering the data completer and more accurate.
The article primarily designs a database system for course selection and grade management.This database stores vast amounts of data, including students, courses, staff, teaching buildings, attendance, assignments, exams, and student grades.Its purpose is to collect and tally the scores of every student for each subject.In practice, it can help professors understand the overall performance of their students in their courses, thereby enhancing the quality of teaching.Alternatively, it allows students to clearly understand how their final scores are derived and what they need to do to improve their grades.
In the future, professors can access real-time learning data of students, such as online exam scores and assignment completion rates, allowing them to provide immediate feedback and evaluation.However, with the increase in data volume and growing awareness of personal privacy, database designers must pay greater attention to data security and privacy protection to ensure that the information of students and professors isn't misused or leaked.

Table 1 .
Entities and the corresponding attributes.

TABLE Department
Commands for creating "department" table As shown above, the command "CREATE TABLE DEPARTMENT" is used to create a new table in the database named "Department"."DeptName" defines a column named "DeptID' with data type 'INT' as integer

Table 5 .
The outputs for courses and their classroom.