DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Assignment
Course : Database Management Systems
Course Code : CSE 2221
Topic : Online Course Enrollment System
Sub. Date : March 19, 2025
Submitted by:
Name : Mohammad Manjur Morshed
ID : 1603110201176
Semester : 4th
Section :A
Dept. : CSE
Submitted to:
Mohammad Hasan
Assistant Professor
Department of Computer Science and Engineering
1. Introduction
1.1 Background & Context
Educational institutions are rapidly adopting online systems to manage academic processes.
Course enrollment, a vital step in a student's journey, is often hindered by outdated, manual
methods that lead to inefficiencies and errors. An online course enrollment system, powered
by a relational database, streamlines registration, enhances data integrity, and improves
accessibility.
1.2 Problem Statement
Manual enrollment systems result in slow processing, duplicate records, and limited scalability.
To overcome these challenges, a robust and automated course enrollment system is needed to
ensure accurate, efficient, and scalable operations.
1.3 Objectives & Scope
This project aims to design an optimized course enrollment system that:
• Automates student registration to reduce errors.
• Ensures data integrity through proper normalization and constraints.
• Supports scalability for future growth.
• Offers a user-friendly experience for students, instructors, and administrators.
1.4 Methodology Overview
The approach involves:
• Analyzing stakeholder requirements.
• Designing an optimized ER model.
• Normalizing database tables to eliminate redundancy.
• Implementing a relational schema with appropriate constraints and indexing.
• Evaluating performance to ensure efficient data retrieval and scalability.
2. Problem Analysis & Requirement Gathering
A well-structured course enrollment system optimizes academic operations, reduces
administrative workload, and enhances the student experience. To achieve this, we must
identify key stakeholders, define core functionalities, and address potential challenges.
2.1 Stakeholders & Their Needs
1. Students
• Browse, enroll in, and withdraw from courses seamlessly.
• View course details, schedules, and instructor information.
• Track enrollment history and academic progress.
• Receive real-time notifications on enrollment status and schedule changes.
2. Instructors
• Manage assigned courses, schedules, and student enrollments.
P a g e 2 | 10
• Handle waitlists and update course materials in real-time.
• Access analytics on student engagement and academic compliance.
3. Administration
• Oversee course offerings and the enrollment lifecycle.
• Assign instructors, resolve schedule conflicts, and manage capacities.
• Generate reports for academic planning and ensure compliance.
2.2 Core Functionalities
1. Course Management
• Create, update, and deactivate courses dynamically.
• Assign instructors and enforce prerequisite validation.
• Manage waitlists and course capacities efficiently.
2. Student Enrollment & Registration
• Prevent schedule conflicts and credit limit violations.
• Provide real-time enrollment updates and waitlist movement.
• Enable drop, swap, and track historical enrollment records.
3. Instructor & Teaching Management
• Centralized course assignment dashboard.
• Access student enrollment data for grading and attendance.
• Upload course materials and schedule office hours.
2.3 Key Challenges & Considerations
1. Enrollment Conflicts & Capacity Management
• Automated detection of schedule conflicts and alternative course suggestions.
• Enforce course capacity limits and waitlist prioritization.
2. Data Integrity & Security
• Prevent duplicate records and maintain referential integrity.
• Implement encryption, role-based authentication, and audit logs.
3. Scalability & Performance
• Optimize database indexing and implement asynchronous processing.
• API-driven architecture for future expansion and integration.
By addressing these factors, the system ensures a seamless, secure, and scalable enrollment
experience.
3. Database Design & ER Diagram
A robust database for a course enrollment system ensures data integrity, minimizes
redundancy, and scales with academic records. The design includes core entities—Students,
Courses, Instructors, Enrollments, Departments, and Class Schedule—along with clearly
defined relationships.
P a g e 3 | 10
3.1 Entities & Attributes
Students -> student_id (PK), name (or first_name/last_name), email (unique), phone,
social_media (optional), address (including district, sub-district, postal_code, area),
admission_year, status (Active, Graduated, Dropped, etc.), department_id (FK)
Courses -> course_id (PK), title, credits, description (or course_description), instructor_id
(FK), department_id (FK), schedule (Optional), duration (Optional)
Instructors -> instructor_id (PK), name (or first_name/last_name), specialization,
contact_info (or separate phone and email, with optional social_media), address (including
district, sub-district, postal_code, area), office_hours, department_id (FK)
Enrollments -> enrollment_id (PK) (or enroll_id), student_id (FK), course_id (FK), semester,
session, section (if applicable), enrollment_status (or enroll_status), timestamp (or enroll_date)
Departments -> department_id (PK) (or dept_id), department_name (or dept_name), location
(Optional), contact_details (or phone and email), department_head (FK to Instructors,
Optional)
Class Schedule -> schedule_id (PK), course_id (FK), instructor_id (FK), time_slot, room_no,
section, day
3.2 Relationships & Cardinality
• Students ↔ Courses (via Enrollments):
o Many-to-Many: A student can enroll in multiple courses, and a course can have
many students (with Enrollments as the join table).
• Courses ↔ Instructors:
o One-to-Many: Each course is taught by one instructor; one instructor can teach
several courses.
• Departments ↔ Students, Instructors, Courses:
o One-to-Many: Each department manages multiple students, instructors, and
courses.
The ER diagram visually represents these entities with primary and foreign keys and the above
relationships (See figure 1).
3.3 Enhanced ERD (EERD) Concepts
To refine the design further:
• Supertype/Subtype (Generalization/Specialization):
o Person (Supertype):
▪ Students: With subtypes such as Undergraduate and Graduate.
▪ Instructors: With subtypes like Full-time and Guest.
• Aggregation:
o Class Schedule: Aggregates both Course and Instructor for managing time slots
and room assignments.
• Weak Entities:
P a g e 4 | 10
o Enrollments: Considered a weak entity that relies on a composite key (e.g.,
student_id and course_id) to prevent duplication.
• Course Types:
o Courses are further classified as Core or Elective.
ER Diagram:
Figure 1: An ER Diagram of OCES
4. Database Normalization Process & Integrity Constraints
Step 1: First Normal Form (1NF): Ensure each attribute holds an atomic value.
Decompose Composite Attributes:
▪ In the Person table, split the composite name into first_name and last_name.
▪ Decompose contact_info into a separate Person_Contact table (with phone, email, and
social_media).
▪ Split the composite address into a Person_Address table (with district, sub_district,
postal_code, and area).
Department Table: Similarly, separate contact_info into a Department_Contact table.
Resulting Schemas:
✓ Person(person_id, first_name, last_name, dept_id, person_type)
✓ Person_Contact(person_id, phone, email, social_media)
P a g e 5 | 10
✓ Person_Address(person_id, district, sub_district, postal_code, area)
✓ Students(person_id, admission_year, status, student_type)
✓ Graduate(person_id, research_topic, publication_count, supervisor)
✓ Undergraduate(person_id, running_semester, credits_completed, advisor)
✓ Instructors(person_id, office_hours, speciality, instructor_type)
✓ Fulltime(person_id, salary, benefits, tenure_status)
✓ Guest(person_id, hourly_rate, contract_duration, visiting_days)
✓ Course(course_id, dept_id, instructor_id, title, credits, description, course_type)
✓ Core_Course(course_id, prerequisite, mandatory_status)
✓ Elective_Course(course_id, elective_type, restriction)
✓ Department(dept_id, dept_name, location, dept_head)
✓ Department_Contact(dept_id, phone, email)
✓ Enrollments(enroll_id, student_id, course_id, semester, session, section, enroll_status,
enroll_date)
✓ Class_Schedule(schedule_id, course_id, instructor_id, time_slot, room_no, section, day)
Step 2: Second Normal Form (2NF): Eliminate partial dependencies so that every non-
key attribute is fully dependent on the entire primary key.
Analysis:
o All tables now have single-attribute primary keys (or have been decomposed to avoid
partial dependency in composite keys).
o For instance, Enrollments uses enroll_id as a unique identifier ensuring that every
attribute is fully dependent on this key.
Conclusion:
o Each table’s non-key attributes are fully functionally dependent on its primary key.
Therefore, all tables satisfy 2NF.
Step 3: Third Normal Form (3NF): Remove transitive dependencies by ensuring non-
key attributes depend only on the primary key.
Verification:
o In Person, attributes like first_name, last_name, dept_id, and person_type depend
solely on person_id.
o Department and other tables similarly have attributes that depend directly on their
primary keys.
o Any potential transitive dependency (e.g., additional details of dept_head) is assumed
to be managed as either a simple attribute or through a proper foreign key reference.
Conclusion:
o No table contains transitive dependencies, so the design complies with 3NF.
Final 3NF Schemas with Constraints & Integrity Rules
• Primary Keys (PK):
o e.g., person_id for Person, course_id for Course, dept_id for Department, etc.
• Foreign Keys (FK):
P a g e 6 | 10
o Person: dept_id references Department(dept_id)
o Enrollments: student_id references Students(person_id); course_id references
Course(course_id)
o Department_Contact: dept_id references Department(dept_id)
• Optional Check/Unique Constraints:
o Person: person_type is limited to values like ('Student', 'Instructor').
o Instructors: instructor_type might be constrained to ('Fulltime', 'Guest').
o Course: course_type can be enforced as either 'Core' or 'Elective'.
o Enrollments: enroll_status may be restricted to defined statuses (e.g., 'active',
'completed').
o Unique Constraints: For attributes such as email in Person_Contact and
Department_Contact, ensuring uniqueness.
This streamlined schema ensures data integrity, consistency, and scalability while meeting the
business rules and operational requirements.
EER Diagram:
Figure 2: An EER Diagram of OCES
P a g e 7 | 10
6. Implementation Plan
A structured and efficient database implementation ensures smooth data management and
optimal system performance. This phase consists of three key steps: schema creation, data
population, and query development, each playing a crucial role in ensuring data integrity,
scalability, and optimized performance.
6.1 SQL Schema Creation (DDL)
The database schema is designed following normalization principles to minimize redundancy
and maintain data consistency. Tables are created with appropriate constraints, including
primary keys, foreign keys, unique constraints, and check constraints, ensuring referential
integrity across different entities. Additionally, indexing strategies are applied to improve
query performance, especially for frequently accessed data such as enrollments and course
schedules.
The relationships between departments, students, instructors, and courses are carefully
structured, enabling seamless data retrieval and management. By defining foreign key
constraints, the system prevents unauthorized modifications or deletions that could
compromise data accuracy.
6.2 Data Manipulation (DML)
To validate the schema and test system functionality, sample data is inserted into all tables.
The data is structured logically, ensuring a smooth insertion sequence that prevents foreign
key constraint violations.
Various test cases are considered, including different student enrollment scenarios, instructor-
course assignments, and department structures. This allows the system to be tested under
realistic conditions, ensuring that it can handle actual academic operations effectively.
Additionally, automated data generation scripts may be employed to create large datasets
for scalability testing, verifying system performance under increased loads.
6.3 Query Development
A set of optimized queries is developed to facilitate essential database operations and retrieve
meaningful insights. Some key queries include:
• Retrieving Student Enrollments: This query extracts data on student enrollments,
including student details, enrolled courses, and enrollment status for a specific
semester.
• Listing Instructor Assignments: It fetches details of instructors along with their
assigned courses, allowing administrators to monitor teaching assignments and balance
workloads effectively.
• Displaying Course Offerings: This query presents a structured view of all available
courses, including course titles, credit hours, department affiliations, and assigned
instructors.
• Checking Course Availability: It allows students to determine open slots in desired
courses before enrolling, ensuring efficient course registration.
P a g e 8 | 10
• Identifying Underutilized Resources: This query helps administrators detect under-
enrolled courses or departments with an excess of teaching staff, enabling strategic
resource allocation.
Relational Schema Diagram:
Figure 3: A relational schema diagram of OCES
7. Key Considerations & Solutions
Developing a robust and scalable database system requires careful consideration of advanced
design principles and efficient problem-solving strategies. The implementation of
normalization, ER/EER modeling, and supertype/subtype hierarchies ensures data integrity,
consistency, and scalability. By enforcing strict constraints such as unique 0,keys
n
and check
constraints, the system effectively prevents data anomalies. Additionally, associative tables
P a g e 9 | 10
like Enrollments help manage complex many-to-many relationships, reducing redundancy and
maintaining referential integrity.
Several challenges arise during database implementation, requiring strategic solutions to
ensure smooth performance and security. Concurrency management is crucial in handling
simultaneous transactions, which can be addressed using locking mechanisms and transaction
isolation levels to prevent conflicts. Scalability concerns are managed through proper indexing,
denormalization (where necessary), and query optimization to maintain performance even as
data volume increases. Security measures, such as role-based access control, encryption
techniques, and regular audits, safeguard sensitive student and course data from unauthorized
access. Furthermore, query performance is enhanced by utilizing optimized indexes, caching
strategies, and query tuning techniques, ensuring efficient and fast data retrieval.
8. Future Enhancements & Conclusion
As the system evolves, several enhancements can be introduced to improve functionality and
user experience. One such improvement is automated course scheduling, which dynamically
adjusts course schedules based on student demand and instructor availability, reducing
scheduling conflicts and optimizing resource allocation. Analytics and reporting tools can be
integrated to provide insights into enrollment trends, student performance, and course
popularity, aiding data-driven decision-making. Additionally, the incorporation of AI-based
recommendation systems can personalize course suggestions for students, leveraging historical
data to recommend optimal learning paths and improve student outcomes.
In conclusion, the database design follows robust normalization and ER/EER modeling
techniques, ensuring efficient data organization, consistency, and scalability. The
implementation of standard constraints and well-structured schema design guarantees data
integrity while allowing flexibility for future growth and enhancements. With a well-planned
architecture and continuous improvements, the system is poised to meet evolving academic
and administrative needs effectively.
P a g e 10 | 10
