Student Course Management System Design Based on Client Requirements
Budget: $30 – $250 USD
Part-I
Project Title: Designing a Student Course Management System Based on Client Requirements
A. Scenario:
A university has hired a database consultant to design a Student Course Management System (SCMS). The transcript between university admin (client) and database consultant is as given below. In this project you are expected to perform the following tasks based on the transcript.
1. Identify the entities, attributes, relationships, and Design an ER diagram for the database.
2. Use SQL to define tables, constraints, and relationships.
3. Generate Sample Data and populate the database
4. Write queries to answer the client's reporting needs.
5. Implement views and indexes to optimize queries.
B. Client Meeting Transcript
Client (University Admin):
"We need a system to manage student enrolments, courses, and faculty assignments. Right now, everything is handled manually, which leads to errors and inefficiencies. We want a database solution that keeps track of students, courses, and the faculty who teach them."
Consultant (Students' Role):
"Can you describe the main entities involved in the system?"
Client:
"Sure. We have students who enroll in courses. Each course is taught by a faculty member. A student can take multiple courses, and each course can have many students. Faculty members teach multiple courses as well."
Consultant:
"What kind of information do you store about students, courses, and faculty?"
Client:
"For students, we need their ID, name, email, phone number, and address.
For courses, we need a course ID, course name, number of credits, and department.
For faculty, we need their ID, name, email, and department."
Consultant:
"How do you track student enrolments?"
Client:
"We need to record which students are enrolled in which courses, along with the semester and their final grade. We should also be able to retrieve a student's academic history."
Consultant:
"How do you assign faculty to courses?"
Client:
"Each semester, faculty members are assigned to specific courses. We need a way to track which faculty members are teaching which courses in a given semester."
Consultant:
"Do you need any special reports or queries?"
Client:
"Yes! We often need to check:
Which students are enrolled in a specific course?
(Query: Find students enrolled in a specific course)
What courses a student has taken and their grades?
(Query: List courses taken by a student along with grades)
Which faculty member is teaching a particular course?
(Query: Find faculty teaching a specific course)
The average grade for a course. (
Query: Calculate the average grade of a course. You can decide your own grading scheme)
A list of students who are about to graduate based on completed credits."
(Query: Find students who have completed at least 6 credits)
C. Some Direction/help for the Students:
i. Guiding Questions
a) What tables are required? Identify primary keys, foreign keys, and attributes.
b) What relationships exist? (One-to-many, many-to-many)
c) What constraints should be applied? (Unique, NOT NULL, foreign key constraints)
d) How can indexes help improve performance?
e) Which queries will be frequently used? (To design views for common reports)
ii. Sample SQL Schema
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(15),
Address VARCHAR(255)
);
Criteria
Rating
Rating Scale
Introduction (the introduction section clearly states the project purpose, significance, motivation, and problem statement).
10
Design and Functional modules:
Clear identification and planning of each functional module.
10
Method/Techniques (The project has utilized appropriate technique(s) in analyzing the process).
Students should cover the details of tools and techniques used in the project development.
10
Results/Analysis/Outcome (The results of the project are concise and well explanatory).
Students should attach the snapshot of each module prepared along with its description.
10
Conclusion (The findings are clearly stated, consistent with the results, and mentions future research idea).
10
References and citation (The reference are well in line with the project; the number of references is enough to justify the outcome; the references are up-to-date; the references were properly cited in the manuscript and the citation format adhere to the approved style).
Project formatting, Language, and Structure (The project has adhered to the standard and approved template; the language is properly formatted to a very good readable level)
Project Title: Designing a Student Course Management System Based on Client Requirements
A. Scenario:
A university has hired a database consultant to design a Student Course Management System (SCMS). The transcript between university admin (client) and database consultant is as given below. In this project you are expected to perform the following tasks based on the transcript.
1. Identify the entities, attributes, relationships, and Design an ER diagram for the database.
2. Use SQL to define tables, constraints, and relationships.
3. Generate Sample Data and populate the database
4. Write queries to answer the client's reporting needs.
5. Implement views and indexes to optimize queries.
B. Client Meeting Transcript
Client (University Admin):
"We need a system to manage student enrolments, courses, and faculty assignments. Right now, everything is handled manually, which leads to errors and inefficiencies. We want a database solution that keeps track of students, courses, and the faculty who teach them."
Consultant (Students' Role):
"Can you describe the main entities involved in the system?"
Client:
"Sure. We have students who enroll in courses. Each course is taught by a faculty member. A student can take multiple courses, and each course can have many students. Faculty members teach multiple courses as well."
Consultant:
"What kind of information do you store about students, courses, and faculty?"
Client:
"For students, we need their ID, name, email, phone number, and address.
For courses, we need a course ID, course name, number of credits, and department.
For faculty, we need their ID, name, email, and department."
Consultant:
"How do you track student enrolments?"
Client:
"We need to record which students are enrolled in which courses, along with the semester and their final grade. We should also be able to retrieve a student's academic history."
Consultant:
"How do you assign faculty to courses?"
Client:
"Each semester, faculty members are assigned to specific courses. We need a way to track which faculty members are teaching which courses in a given semester."
Consultant:
"Do you need any special reports or queries?"
Client:
"Yes! We often need to check:
Which students are enrolled in a specific course?
(Query: Find students enrolled in a specific course)
What courses a student has taken and their grades?
(Query: List courses taken by a student along with grades)
Which faculty member is teaching a particular course?
(Query: Find faculty teaching a specific course)
The average grade for a course. (
Query: Calculate the average grade of a course. You can decide your own grading scheme)
A list of students who are about to graduate based on completed credits."
(Query: Find students who have completed at least 6 credits)
C. Some Direction/help for the Students:
i. Guiding Questions
a) What tables are required? Identify primary keys, foreign keys, and attributes.
b) What relationships exist? (One-to-many, many-to-many)
c) What constraints should be applied? (Unique, NOT NULL, foreign key constraints)
d) How can indexes help improve performance?
e) Which queries will be frequently used? (To design views for common reports)
ii. Sample SQL Schema
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(15),
Address VARCHAR(255)
);
Criteria
Rating
Rating Scale
Introduction (the introduction section clearly states the project purpose, significance, motivation, and problem statement).
10
Design and Functional modules:
Clear identification and planning of each functional module.
10
Method/Techniques (The project has utilized appropriate technique(s) in analyzing the process).
Students should cover the details of tools and techniques used in the project development.
10
Results/Analysis/Outcome (The results of the project are concise and well explanatory).
Students should attach the snapshot of each module prepared along with its description.
10
Conclusion (The findings are clearly stated, consistent with the results, and mentions future research idea).
10
References and citation (The reference are well in line with the project; the number of references is enough to justify the outcome; the references are up-to-date; the references were properly cited in the manuscript and the citation format adhere to the approved style).
Project formatting, Language, and Structure (The project has adhered to the standard and approved template; the language is properly formatted to a very good readable level)