Term Project Deliverable 2

Building on the ERD developed in deliverable 1. The group will continue to work on the project and turn in the following files (with new requirements highlighted in bold text):

a) A MS Word document that contains the same five sections of the group proposal (no individual proposal needed). Include my feedback for deliverable 1 and highlight any changes made in response to the feedback. In Particular, section 5 should be expanded to include a list of 10 specific functionalities that you plan to implement using queries in Access (to be done in deliverable 3). Build your queries with the following specific requirements (Note that you may have the one query that satisfies multiple requirements below):

1) The 10 functionalities should cover all entities in your ERD (with the exception that only one of the subtypes needs to be covered). In other words, there should be no entities that are not involved in any proposed functionality.

2) At least 5 functionalities should involve more than one entity (Example: show the total number of the products supplied by customer X) – this functionality involves at least the Product and Customer entities).

3) At least 2 functionalities should involve more than two entities (Example: Show the most profitable product purchased by customers from Florida – this functionality involves at least the Orderline, Order, and Customer entities).

4) At least 5 functionalities should involve the use of mathematical functions such as Sum, AVG, Min, Max.

5) At least 2 functionalities should involve the "Group By" feature (Example: Display the average prices of the products supplied by each vendor).

6) At least 1 functionality should involve the use of the Count feature (Example: Count the number of vendors who have supplied more than one product).

7) At least 1 functionality should involve the entity that exhibits the unary relationship (i.e. a self-join is needed).

8) At least 1 functionality should involve both the supertype and one of the subtypes.

After you list the 10 functionalities, indicate which of the above requirements each functionalitiy satisfies, e.g.: Find out the average number of products purchased by customers from Florida (#2, #4, #5). .

2) An Access file that contains all the tables outlined in the ERD with both entity integrity (through PK) and referential entity (through FK) properly enforced in the relationship diagram. Each table should have at least 1 test record. When you insert these test records, make sure they are consistent with the business rules and constraints you specified in the proposal. For example, if you specified the "Price" attribute to be numeric and not null, then the column should not contain either a string value or a null value. Similarly, if you specified that "Each vendor must supply at least one product", then you need to make sure that every vendor that appears in the Vendor table must show up at least once in the Product table.

3) The Visio file that contains the updated ERD (include the Visio file even if no changes are made).

Final submission for this assignment will include a Word Doc (.docx), a Access file (.aacdb), and a Visio diagram (.vsdx).

FinalCopies/~$liverable_1_LastName.docx

FinalCopies/Deliverable_1_LastName.docx

Student Enrollment Management System (SEMS) Documentation.

Introduction

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.

1 Project Proposal 1 1.1 1.1 Introduction 1 1.2 1.2 Problem Statement 2 1.3 1.3 Proposed Database Solution 2 1.4 1.4 Business Rules 3 1.5 1.5 Entity-Relationship Diagram (ERD) 3 2 Updated Group Proposal 5 2.1 Enhancements 5 2.2 Prototype of the Application 5 2.3 Final Functional Database Application 5 2.4 Database Development 5 2.5 Query Functionality 6

Project Proposal

Introduction

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 problem statement, proposed database solution, and an Entity-Relationship Diagram (ERD) for the system.

Problem Statement

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.

Proposed Database Solution

To solve the aforementioned issues, we propose the development of a comprehensive SEMS database application that includes the following classes/entities:

a. Student: To manage student information.

b. Subject: To represent the main subject areas, each containing multiple courses.

c. Instructor: To maintain information about instructors.

d. Assignment: To track instructor assignments to courses.

e. Enrollment: To record student enrollments in subjects.

f. Courses: To represent individual courses within a subject, including credit hours.

Student

· StudentID (Primary Key, Autoincrement, Integer)

· FirstName (Text)

· LastName (Text)

· DateOfBirth (Date)

· PhoneNumber (Text)

· Email (Text)

Instructor

· InstructorID (Primary Key, Autoincrement, Integer)

· FirstName (Text)

· LastName (Text)

· PhoneNumber (Text)

· Email (Text)

Course

· CourseID (Primary Key, Autoincrement, Integer)

· CourseTitle (Text)

· Description (Text)

· CreditHours (Integer)

· SubjectID (Foreign Key, Integer)

Enrollment

· EnrollmentID (Primary Key, Autoincrement, Integer)

· StudentID (Foreign Key, Integer)

· CourseID (Foreign Key, Integer)

· EnrollmentDate (Date)

Business Rules

Business Rule 1: Each student can enroll in multiple courses.

Business Rule 2: A course must have at least one enrolled student.

Business Rule 3: An instructor can teach multiple courses.

Business Rule 4: Enrollment dates must be within the academic calendar.

Entity-Relationship Diagram (ERD)

The ERD for SEMS is as follows:

Explanations of Relationships:

1. Student – Enrolment (One-to-Many):

· One student can have multiple enrollments.

· This relationship allows tracking of multiple enrollments by a single student.

2. Courses – Enrolments (One-to-Many):

· One course can have multiple student enrollments.

· This relationship allows you to associate multiple students with a single course.

3. Instructor – Assignment (One-to-Many):

· One instructor can have multiple teaching assignments.

· Instructors are assigned to courses.

4. Assignment – Courses (Many-to-One):

· Many assignments are associated with one course.

· This relationship links instructors' assignments to specific courses.

5. Courses – Subject (Many-to-One):

· Many courses belong to one subject.

· This relationship organizes courses into subject categories.

These relationships define how data is related and interconnected within the SEMS database. They facilitate the management of student enrolments, courses, instructors, assignments, and the organization of courses within subjects.

Top of Form

Updated Group Proposal

Enhancements

Based on feedback and further analysis, the project proposal has been enhanced with the following improvements:

· Refined problem statement.

· Improved database solution.

· Updated and refined ERD.

Prototype of the Application

The prototype of the SEMS application includes the following functionalities:

· Student registration with unique Student IDs.

· Course and subject management with instructor assignments.

· Student enrollment process with conflict resolution.

Final Functional Database Application

The Student Enrollment Management Functionalities:

· Users can register and log in.

· Students can enroll in courses.

· Instructors can assign courses to students.

· Generate reports on enrollment data.

· Manage student and instructor information.

Database Development

The final database application includes the following components:

· Development of the complete database structure.

· Creation of tables with actual student, subject, course, and instructor data.

Query Functionality

The SEMS provides a wide range of query functionalities, including:

· Student and instructor data retrieval.

· Scheduling conflict checks.

· Enrollment history reporting.

· Credit-hour tracking.

In conclusion, the proposed Student Enrollment Management System will address critical data management challenges in educational institutions. By efficiently managing student enrollments, courses, and instructors, the system will enhance the educational experience and administrative processes.

image1.png

FinalCopies/Deliverable_1_Liu_Group.vsdx

Student Table StudentID PK FK FirstName PK FK LastName PK FK Course Table CourseID PK FK SubjectID PK FK CourseTittle PK FK Enrollment Table EnrollmentID PK FK StudentID PK FK EnrollmentDate PK FK DateOfBirth PK FK NextOfKin PK FK Address PK FK EmergencyContact PK FK Relationship PK FK Description PK FK PhoneNumber PK FK Instructor Table InstructorID PK FK FirstName PK FK LastName PK FK Address PK FK Email PK FK PhoneNumber PK FK Assignment Table AssignmentID PK FK InstructorID PK FK CourseID PK FK Subject Table SubjectID PK FK Subject Tittle PK FK attribute name PK FK Credit Hours PK FK CourseID PK FK SubjectID PK FK Teaches M1 M2 M3 M4 Enrolls M1 M2 M3 M4 Enrolled M1 M2 M3 M4 M1 M2 M3 M4 Belongs To M1 M2 M3 M4

FinalCopies/LastName.accdb

AssignmentID	InstructorID	CourseID
1	1	1
2	2	2
3	3	3
4	4	4

CourseID	SubjectID	CourseTitle	Description	CreditHours
1	1	Computer Science 101	Introduction to Programming	3
2	2	History 101	World History I	4
3	3	Chemistry 101	General Chemistry	4
4	4	Finance 101	Financial Principles	3
5	1	Mathematics 101	College Algebra	3

EnrollmentID	StudentID	CourseID
1	1	1
2	2	2
3	3	3
4	4	4

InstructorID	FirstName	LastName	PhoneNumber	Email
1	Professor	Smith	(111) 222-3333	[email protected]
2	Dr.	Johnson	(222) 333-4444	[email protected]
3	Ms.	Brown	(333) 444-5555	[email protected]
4	Mr.	Garcia	(444) 555-6666	[email protected]

StudentID	FirstName	LastName	PhoneNumber	Email
1	John	Doe	(123) 456-7890	[email protected]
2	Jane	Smith	(987) 654-3210	[email protected]
3	Bob	Johnson	(555) 123-4567	[email protected]
4	Alice	Brown	(444) 789-1234	[email protected]

SubjectID	SubjectTitle
1	Computer Science
2	History Studies
3	Applied Chemistry
4	Finance
5	Music & Art

SELECT Instructor.*, Students.* FROM Students, Instructor INNER JOIN Assignment ON Instructor.InstructorID = Assignment.InstructorID;

IS 3063 Term Project Deliverable 1 Template

Group Number: 3

Your Name: Aman Ali

Your email: [email protected]

Group proposal section:

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

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.

• 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	Required/Optional	E.g.	Description	Null value
StdtID	INT(10)	Required	123	Student ID
FName	CHAR(10)	Required	John	Student’s first name
LName	CHAR(10)	Required	Martin	Student’s last name
DateOfBirth	DATE	Required	01/20/1989	Student’s date of birth
NextOfKin	CHAR(50)	Optional	Lucas B. Martin	Student’s closest living blood relative
EmergCont	INT(15)	Optional	+1(210)352-7846	Emergency contact
Address	CHAR(30)	Required	23 Street A	Student’s address
Relationship	CHAR(10)	Optional	Mother	Relation of emergency contact to student
PhoneNo	INT(15)	Required	+1(210)345-6732	Student’s phone number
Email	VARCHAR (200)	Required	[email protected]	Student’s email

Entity: SUBJECT

Attribute	Data Type	Required /Optional	E.g.	Description	Null Value
SubjID	INT(10)	Required	456	Subject ID
SubjTitle	CHAR(50)	Required	Biology	Title of the subject
AttrbName	CHAR(50)	Required	Science	Name of the attribute

Entity: INSTRUCTOR

Attribute	Data Type	Required /Optional	E.g.	Description	Null Value
InstID	INT(10)	Required	1234567890	Instructor ID
FName	CHAR(10)	Required	John	Instructor’s first name
LName	CHAR(10)	Required	Doe	Instructor’s last name
PhoneNo	INT(15)	Required	+1(210)345-6732	Instructor’s phone number
Email	VARCHAR (200)	Required	[email protected]	Instructor’s email
Address	VARCHAR(100)	Required	987 1st St	Instructor’s address

Entity: ASSIGNMENT

Attribute	Data Type	Required/Optional	E.g.	Description	Null Value
AssignmID	INT(10)	Required	1234567890	Assignment ID
InstID	INT(10)	Required	0987654321	Instructor ID
CourseID	INT(10)	Required	1234567890	Course ID

Entity: ENROLLMENT

Attribute	Data Type	Required/Optional	E.g.	Description	Null Value
EnrollmID	INT(10)	Required	1234567890	Student’s enrollment ID
StdtID	INT(10)	Required	0987654321	Student ID
CourseID	INT(10)	Required	1234567890	Course ID
EnrollmDate	DATE	Optional	12/30/1997	Student’s enrollment date

Entity: COURSE

Attribute	Data Type	Required /Optional	E.g.	Description	Null Value
CourseID	INT(10)	Required	1234567890	Course ID
SubjID	INT(10)	Required	0987654321	Subject ID of the course
CourseTitle	CHAR(50)	Required	History 101	Title of the course
Descr	VARCHAR (100)	Optional	This course is an introduction to history.	Description of the course
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-to-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-to-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-to-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.

FinalCopies/~$liverable_1_LastName.docx

FinalCopies/Deliverable_1_LastName.docx

Project Proposal

Introduction

Problem Statement

Proposed Database Solution

Business Rules

Entity-Relationship Diagram (ERD)

Updated Group Proposal

Enhancements

Prototype of the Application

Final Functional Database Application

Database Development

Query Functionality

image1.png

FinalCopies/Deliverable_1_Liu_Group.vsdx

FinalCopies/LastName.accdb

image1.png

Order a similar assignment, and have writers from our team of experts write it for you, guaranteeing you an A

Order a similar assignment, and have writers from our team of experts write it for you, guaranteeing you an A