Deliverable 3

IS 3063 Term Project Deliverable 2

Group Number: 3

Your Name: Aman Ali

Your email: [email protected]

Professor’s Feedback: “Excellent work up on the Term project. This work will be reflected in the other deliverables. One note, your attributes need a correlation to the entity, i.e.: CUST_FName for yours STUD_StdtID or STUDStdtID.”

Group proposal section:

1. Description of the business context and related data management problem(s)

In today's dynamic educational landscape, efficient data management is paramount to providing a seamless learning experience. Educational institutions face numerous challenges in managing student enrollments, courses, and instructors, which can lead to administrative inefficiencies and data inaccuracies. The "Student Enrollment Management System" project aims to address these challenges by introducing a comprehensive database application. This proposal outlines the business context, identifies the problems that this system will solve, describes the entities and their attributes, defines critical business rules, presents a tentative Entity-Relationship Diagram (ERD), and provides an overview of the application's functionality.

The Student Enrollment Management System (SEMS) is a database application developed to streamline and enhance the student enrollment process at XYZ University. This documentation outlines the project proposal, including the problem statement, proposed database solution, and an Entity-Relationship Diagram (ERD) for the system.

The manual student enrollment process at XYZ University is error-prone, leads to scheduling conflicts, and causes delays in providing essential services. These challenges necessitate the development of a robust SEMS to address the following issues:

• Lack of a centralized system for student registration. • Scheduling conflicts due to manual enrollment. • Inaccurate course management leading to credit-hour discrepancies. • Limited reporting capabilities for administrators

2. The entities and the attributes

Entity: STUDENT

Attribute Data Type Require d/Option

al E.g. Description Null

value

STUD_StdtID INT(10) Required 123 Student ID STUD_FName CHAR(10) Required John Student’s first name STUD_LName CHAR(10) Required Martin Student’s last name

STUD_DateOfBirth DATE Required 01/20/1989 Student’s date of birth

STUD_NextOfKin CHAR(50) Optional Lucas B. Martin Student’s closest living blood relative

STUD_EmergCont INT(15) Optional +1(210)352-7846 Emergency contact STUD_Address CHAR(30) Required 23 Street A Student’s address

STUD_Relations CHAR(10) Optional Mother Relation of emergency contact to student

STUD_PhoneNo INT(15) Required +1(210)345-6732 Student’s phone number

STUD_Email VARCHAR (200) Required [email protected] Student’s email

Entity: SUBJECT

Attribute Data Type Required /Optional E.g. Description Null

Value SUBJ_SubjID INT(10) Required 456 Subject ID SUBJ_SubjTitle CHAR(50) Required Biology Title of the subject SUBJ_AttrbName CHAR(50) Required Science Name of the attribute

Entity: INSTRUCTOR

Attribute Data Type Required /Optional E.g. Description Null

Value INSTR_InstrID INT(10) Required 1234567890 Instructor ID INSTR_FName CHAR(10) Required John Instructor’s first name INSTR_LName CHAR(10) Required Doe Instructor’s last name

INSTR_PhoneNo INT(15) Required +1(210)345-6732 Instructor’s phone number

INSTR_Email VARCHAR Required [email protected] Instructor’s email

(200)

INSTR_Address VARCHAR (100) Required 987 1st St Instructor’s address

Entity: ASSIGNMENT

Attribute Data Type

Required/ Optional E.g. Description Null

Value ASGMT_AsgmtID INT(10) Required 1234567890 Assignment ID ASGMT_InstrID INT(10) Required 0987654321 Instructor ID ASGMT_CourseID INT(10) Required 1234567890 Course ID

Entity: ENROLLMENT

Attribute Data Type

Required /Optional E.g. Description Null

Value ENR_EnrollmID INT(10) Required 1234567890 Student’s enrollment ID ENR_StdtID INT(10) Required 0987654321 Student ID ENR_SubjID INT(10) Required 456 Subject ID ENR_CourseID INT(10) Required 1234567890 Course ID ENR_EnrollmDate DATE Optional 12/30/1997 Student’s enrollment date

Entity: COURSE

Attribute Data Type Required /Optional E.g. Description Null

Value COUR_CourseID INT(10) Required 1234567890 Course ID

COUR_SubjID INT(10) Required 0987654321 Subject ID of the course

COUR_CourseTitle CHAR(50) Required History 101 Title of the course

COUR_CourseDescr VARCHAR (100) Optional

This course is an introduction to

history.

Description of the course

COUR_CredHrs INT(4) Required 3 Measures the time needed for learning in a course

3. Business rules that define all the relationships among entities, the constraints, and the attribute domains (if any).

Relationships:

1. Student – Enrollment (One or many):

● One student can have one or many enrollments

● This relationship allows tracking of multiple enrollments by a single student

2. Enrollment – Student (One & only one):

● One enrollment can only be assigned to one student

3. Enrollment – Course (One or many):

● One enrollment can have one or many courses at the same time

4. Enrollment – Subject (One or many ):

● One enrollment can have one or many subjects

5. Subject – Enrollment (Zero or many)

● One subject can have zero or many enrollments

6. Subject – Course (Zero or many):

● One subject can be assigned to zero or many courses

7. Course – Subject (One & only one):

● One course can be assigned to one and only one subject

● This relationship organizes courses into subject categories.

8. Course – Enrollment (Zero or many):

● One course can have zero or many student enrollments

9. Course – Assignment (Zero or many):

● One course can have zero or many assignments

10.Assignment – Course (One-to-one):

● One assignment belongs to one course

11.Assignment – Instructor (One or many):

● One assignment can have one or many instructors assigned to it

12.Instructor – Assignment (One or many):

● One instructor can have zero or many assignments

4. ERD

5. A summary of the functionality of the application (i.e. what the users can do with this application, future extension/integration with other systems etc.).

Functionality of SEMS Application:

Student Enrollment:

● Users (administrators and staff) can enroll students into courses.

● SEMS will ensure that there are no scheduling conflicts during enrollment.

● Data accuracy is maintained during the enrollment process.

Subject and Course Management:

● Users can create and manage subjects and courses.

● Courses are categorized under subjects, allowing for easy organization.

● Descriptive information about courses is available, including titles, descriptions, and credit hours.

Instructor Management:

● Instructors can be added, and their details are recorded in the system.

● Instructors are associated with specific courses and assignments.

Assignment Management:

● Users can create assignments for courses.

● Assignments are associated with specific courses and instructors.

● SEMS ensures that assignments are correctly linked to courses.

Enrollment Tracking:

● SEMS maintains a record of student enrollments in courses.

● Enrollment dates are recorded for reference.

● The system allows administrators to access enrollment history.

User Communication:

● SEMS supports communication with students and instructors via email.

● Users can send notifications and updates regarding enrollment and assignments.

Reporting and Analytics:

● The application offers reporting capabilities for administrators.

● Users can generate reports on enrollment data, course statistics, and more.

● Analytics tools help identify trends and insights for decision-making.

Future Extensions and Integrations:

Integration with Learning Management Systems (LMS):

● SEMS can be integrated with an existing LMS for a seamless learning experience.

● This integration would allow students and instructors to access course materials, assignments, and grades within the LMS.

Student Portal:

● Develop a student portal where students can log in to view their course schedules, assignments, and grades.

● Provide a self-service option for students to manage their enrollment.

Mobile Application:

● Create a mobile app version of SEMS for on-the-go access to enrollment and assignment information.

● Enhance user convenience and accessibility.

Automated Notifications:

● Implement automated email and SMS notifications for enrollment confirmations, assignment due dates, and other important events.

Data Analytics and Predictive Insights:

● Utilize data analytics and machine learning to provide predictive insights, such as course recommendations based on a student's academic history and performance.

Integration with Financial Systems:

● Integrate SEMS with the university's financial systems to manage tuition payments and fee tracking.

Security Enhancements:

● Strengthen data security to protect sensitive student and instructor information.

● Implement user authentication and authorization mechanisms.

10+ Specific Functionalities:

1. Student Admission Rate: #4

● Determines the percentage of applicants accepted into the university (Involves STUDENT and ENROLLMENT entities).

● It applies the acceptance rate formula: acceptance rate = number of applicants accepted / total number of applicants * 100

2. Curriculum Planning: #2

● Determines the courses and instructors that will be teaching each class (Involves COURSE and INSTRUCTOR entities).

3. Grade Analytics: #4

● Calculates the overall course grade, which helps students and instructors to gain insights into academic progress and areas for improvement. (Involves COURSE and STUDENT entities).

● It is based on a predefined grading formula using the ‘AVG’ function.

4. Grade Prediction and Student Progress Analysis: #4

● Using course grades, assignment scores, and enrollment history, it is possible to predict future course grades based on historical performance (Involves COURSE, ASSIGNMENT, and ENROLLMENT entities).

● Utilizes regression analysis to develop prediction models.

5. Average Credit Hours: #4/5

● Calculates and displays the average credit hours for each course.

● Utilizing the ‘AVG’ function and ‘GROUP BY’, it finds the average credit hours for a course and groups it by course title.

6. Number of Assignments: #2/4/5/6

● Retrieves the number of assignments for each course.

● Using the ‘COUNT’ function to count occurrences of assignments in each course then ‘LEFT JOIN’ to ensure all courses are included in the results. Finally, use ‘GROUP BY’ to organize by course title.

7. Highest Assignments: #2/4/5

● Identifies the instructor with the highest number of assignments.

● Uses ‘COUNT’ and ‘LEFT JOIN’ to ensure all instructors are included and results are ordered descendingly to which ‘LIMIT 1’ is applied to retrieve only the top result.

8. Enhanced Functionality Covering All ERD Entities: #1

● STUD_Student Profile Management: Students can manage their profiles, including updating personal details like address and emergency contacts (involves STUDENT entity).

● COUR_Course Feedback System: Collect and manage feedback for each course, aiding in quality improvement (involves COURSE entity).

● INSTR_Instructor Performance Evaluation: Facilitate evaluations of instructors by students and peers (involves INSTRUCTOR entity).

● SUBJ_Subject Overview Display: Display details and statistics about each subject offered (involves SUBJECT entity).

● ASGMT_Assignment Review Process: Enable a system for reviewing and grading assignments (involves ASSIGNMENT entity).

● ENR_Enrollment Analytics Dashboard: Provide analytics on enrollments, such as trends and patterns (involves ENROLLMENT entity).

9. Multi-Entity Involvement Functionality: #2

● Course-Instructor Allocation: Allocate instructors to courses based on expertise and availability (involves COURSE and INSTRUCTOR entities).

● Subject-Course Enrollment Analysis: Analyze enrollment patterns for courses under each subject (involves SUBJECT and COURSE entities).

10. Functionality Involving Multiple Entities: #3

● Student Course Selection Advisor: Assist students in selecting courses based on past performance and subject interests (involves STUDENT, COURSE, and SUBJECT entities).

● Assignment Submission and Feedback System: Manage assignment submissions and provide feedback, linking students, courses, and instructors (involves STUDENT, COURSE, and INSTRUCTOR entities).

11. Mathematical Function Utilization: #4

● COUR_Average Class Size Calculation: Calculate the average number of students per class (uses AVG function).

● STUD_Student Performance Index: Compute a performance index for students based on grades across courses (uses AVG and SUM functions).

12. 'Group By' Feature Usage: #5

● INSTR_Instructor Workload Report: Group courses by instructor to analyze workload distribution (uses Group By feature).

● ENR_Enrollment Trends by Course: Display enrollment trends grouped by courses over different semesters (uses Group By feature).

13. 'Count' Feature Implementation: #6 ● Course Popularity Index: Count the number of enrollments per course to

determine popularity (uses COUNT feature).

14. Unary Relationship Involvement: #7

● COUR_Prerequisite Course Mapping: Map courses to their prerequisites within the same entity (requires self-join).

15. Supertype and Subtype Involvement: #8

● SUBJ_Subject-Course Correlation Analysis: Analyze the correlation between subjects (supertype) and their respective courses (subtype) based on student performance and enrollment trends.

● STUD_Student Admission Rate Calculation: Determines the acceptance rate of applicants (applies a formula using the COUNT function).

● COUR_Average Credit Hours Calculation: Calculates and displays average credit hours for each course (uses AVG function and GROUP BY).

● ASGMT_Assignment Count per Course: Retrieves the number of assignments for each course (uses COUNT and GROUP BY functions).

● INSTR_Highest Assignment Load Identification: Identifies the instructor with the highest number of assignments (uses COUNT, LEFT JOIN, and LIMIT functions).

By implementing these features and considering future extensions and integrations, SEMS can evolve into a comprehensive tool that not only streamlines enrollment processes but also enhances the overall learning experience for students and instructors at XYZ University.