SP2019 CMPSC431
【SP2019 CMPSC431】SP2019 CMPSC431W Database Management Systems
1
CanvasPath
CanvasPath is a startup project which aims to help Lion State University maintain their software
system for course information management. John Smith, a stakeholder of the project, along with
members of the Lion State Advisory Board have been examining the current course management
system and realized that there is a serious need to migrate the legacy system into one with a new
design and up-to-date technology. The Advisory Board of Lion State (which luckily has some
members graduated from Penn State University) unanimously identified Canvas as the primary
reference for improving their system. Furthermore, to gain insights from Canvas users, the board
suggest Mr. Smith to invite EECS students of Penn State University, who are known for their
creativity and robust technical skills, to be involved in the project.
Mr. Smith, who is not a technical person, reached out to Dr. Lee and his team for assistance to
carry out a feasibility study. After extensive discussions, they agreed that this study with guidance
from Dr. Lee and his teaching team, can be performed by having students in CMPSC431W to
systematically design and implement prototypes for validation of feasibility. The goal is not to
produce a complete implementation of the system, as it would require too much time and resources
for the students. Instead, students will focus on i) the process of the database design that may pose
a significant risk due to uncertainty and the lack of understanding in requirements, and ii) the
prototyping of certain system functionality as a proof of concept. It is anticipated that based on
carefully examination on the tested prototypes, valuable insights and lessons may be obtained.
Indeed, this design-prototype approach of feasibility study is often adopted when the requirements
are not well understood. Moreover, a successful prototype can potentially serve as the foundation
for the future production system in operation, and an impressive demonstration of the prototyped
system, functions, and unique features will attract appreciation of the PSU talents by the members
of the LSU Advisory Board who are mostly executives in S&P 500 companies.
Dr. Smith and his associates have tried to communicate this project abstractly and expects students
to figure out and fill in all the missing details. Basically, you (students in CMPSC431W) will
design a database-backed web application to manage course-related information, including
courses, students, faculty members, and more. Students (you) will also implement a prototype to
demonstrate system functions and your design. As parts of the prototype, you will need to
implement a number of programs that access data in the designed database in order to support the
functions of the system. The project consists of two phases:
- Requirement Analysis, Conceptual Database Design, Technology Survey, Logical
Database Design and Normalization. - Protype Implementation
The first phase is to, based on the provided project description, analyze the requirements of
CanvasPath in order to specify its system functionality and to identify data needed for the system
functions as well as business rules (integrity constraints) to be imposed upon the needed data. In
addition, it also requires you to come up with a conceptual database design by using the entityrelationship
model to expressed the data and constraints identified. Furthermore, students shall
perform a technology survey by researching on the current web/database application technologies,
SP2019 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee
2
including web frameworks, programming languages, development tools, and database
management system, for comparison with those suggested by the CMPSC431W teaching team,
i.e., Flask Web Framework, Pythan, PyCharm IDE, and SQLite. Additionally, logical database
design and schema normalization is to be performed in the first phase. Each student will submit a
well-documented report for the tasks in Phase 1. After this detailed design process, in Phase 2,
students will populate the database and implement the system functions.
OVERALL REQUIREMENTS
Before we jump into the description for each phase, let’s talk about what you are expected to fulfill
while working on the project.
Document formatting
We will provide a document template (as .docx file) that you are more than welcome to directly
follow. However, you are encouraged to create a template of your own, as long as it maintains a
professional image for your cover page, and includes all the components from our provided
template. You can also write your documents in LaTeX or other document typesetting languages
or tools. Your report should be converted into one PDF file for submission electronically.
Source Control
It is required that every student maintains a GitHub account and repository for the project. It is an
excellent practice to maintain version control for projects of any size. Therefore, a GitHub
classroom for CMPSC431W has been created for students to enroll. Note that the status shown on
your repositories, e.g., how frequent the repository is committed may serve as an indicator for
assessing your project effort and performance. In addition, you are required to maintain good
documentation for your codes.
Project Management
The success of this project heavily relies on your own personal effort and commitment. As denoted
on the Syllabus and Course Schedule, the project is segmented into two phases. Thus, there will
be no intermediate submissions. Students are expected to set goals and maintain individual
milestones, documented as part of the Phase 1 report. For those who do not have prior background
on web programming, it is highly recommended to start reading documentation and tutorials
during Phase I.
Since there are numerous web application tools and frameworks, it is impossible for the teaching
staff to provide guidance on every combination of tools. While students have a choice to use the
recommended platform and tools (i.e., Flask Web Framework, Pythan, PyCharm IDE, and SQLite)
which the teaching staff are more than happy to provide guidance, you may choose to adopt
platforms, tools, programming languages and database management systems of your preference
(but you are on your own in this case).
SP2019 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee
3
PHASE I – DATABASE DESIGN AND TECH SURVEY
Checklist
Tasks
Requirement Analysis
Conceptual Database Design
Technology Survey
Logical database design and
normalization
Task 1: Requirement Analysis
As mentioned earlier, CanvasPath aims to replace the current course management system in Lion
State University. Thus, it may help to explore the current Canvas (and other course-related)
websites in PSU as a reference to better understand the expected functions and the requirements
for CanvasPath. The following is a general description of the expected system functionalities in
CanvasPath. In this task, you need to specify in detail the system requirements, including system
functions, the types of data that must be stored, the business rules (integrity constraints) to be
imposed on the data, and how it will be stored and accessed in support of the expected system
functionality.
Note: For Task 1, you are expected to elaborate the system functionality in your own words and
identify (also write down) the data needed to support the functionality as well as the integrity
constraints to be imposed on the data.
In the following, we first describe the CanvasPath system from the users’ perspective, then we
describe some additional information in the system. - CanvasPath Users: All software and computer applications are to be used. Whether it be an
embedded system which runs autonomously or a service/product which humans interact with,
a good design needs to understand the purposes/goals of the systems and who the end users
are. For this project, there are primarily two types of users, Students and Faculty members,
as well as a special staff called Administrator, who takes charge of managing all students,
faculty members, and courses information. At the beginning of each semester, the
Administrator will create all courses on CanvasPath. He maintains all students and faculty
members information such as the name, ID, age, gender, email address, home address, which
includes street, city, state and zipcode, and initial login password. For students, the
administrator also maintains their majors. For faculty members, the administrator also
maintains their office addresses (different faculty members have different offices), titles and
their departments as well. Then for each section of a course, he grants the faculty member who
teaches the section the permission to manage the section. At the beginning of semester, the
students enroll their courses but there is no opportunity to for them to add or drop course after
the semester begins. Note that the administrator only provides necessary initial information for
course configuration in CanvasPath and he does not use it after the semester begins. Therefore,
SP2019 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee
4
do not worry about the design of administrator. The primary CanvasPath users whom you
should be concerned about include only students and faculty members. We assume all users
can log in with the email ID and the login password.
a. Students: We need to be able to maintain information about students enrolled at Penn
State. It is important that detailed information of students is collected and stored within
CanvasPath.
For students, CanvasPath is a system to enroll into courses and to view their course
information, assignments scores and courses grades. A student can log into CanvasPath
with his email address and his own initial password at first. After logging in, he can modify
his personal information (except for his ID) and reset login password. In addition, he should
be able to browse/search the general course information (including instructor’s name) for
enrollment to courses. He can also find details regarding the courses that he takes (grades,
instructor name and their details). A student cannot find specific information (e.g.,
assignments) of courses that he is not taking. For privacy concerns, students are only able
to see their own grades. Furthermore, for a student taking a Capstone section of a course
(Capstone section and project are to be introduced later), the system should also provide
the student with contact information of their mentor/sponsor and the team members of his
Capstone project.
Intuitively, for a student to be a valid user of the system, he must take at least one class,
i.e. be enrolled to a course. In addition, a student can enroll to only one section of a course.
Again, he can only view the specific information of courses he is enrolled.
b. Faculty Members: As the same with students, faculty members can log in with their email
and initial login password. After a faculty logs in, he can modify his personal information
and reset his login password. Besides, he can see the list of the courses and sections he
teaches. For a course section he teaches, he can see the names (along with IDs) of students
enrolled in the section. If a section is not a Capstone section, the faculty can create entries
for homework assignments and exams. He will submit the scores of assignments and exams
for students after grading. At the end of semester, he will submit final grades for students
based on their academic performance.
If a section is a Capstone section, the faculty member can create entries for homework
assignments and some projects (which replace exams in regular sections). For each project,
he collects the information of all teams (e.g., team name and information of team
members). On CanvasPath,he assesses the performance of teams to assign grades for each
team (i.e., he gives the same score to students in the same team). The faculty member can
also see name and contact information of sponsors who mentor Capstone projects in the
section. More information about Capstone section are to be explained later.
Intuitively, all course sections need to be taught by at least one faculty member. A faculty
member may teach the same course in different semesters and he may teach multiple
sections of the same course in one semester, which should be recorded by the CanvasPath.
SP2019 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee
5
However, only the courses (sections) being taught in the current semester can be managed
by the faculty member.
The above is stated from the user view of CanvasPath. The following parts describe the
Departments, Courses and Sections which are to be managed in CanvasPath. - Departments: Like all universities, departments in Lion State University are home of majored
students and affiliated faculty members. Every faculty member belongs to only one department
and all students must major in at least one department. For instance, a student may major in
both of the Mathematics department and the Computer Science department (CMPSC), while
Prof. Lee is a member of the CMPSC faculty. - Courses: Courses are the main area of focus which the CanvasPath project is targeting on. In
Lion State, a course can only be offered by one department. In addition, courses have prerequisites
(one course can have multiple pre-requisites). In addition to pre-requisites, course
information includes the course name, the unique number abbreviation, the department
offering the course, and the course sections which the students actually enroll in (you may use
course information in LionPath of Penn State as a reference). - Sections: a section is the lower-level unit of a course. One course can have multiple sections.
Sections are denoted by section numbers and each section is always taught by at least one
faculty/professor. Note that each section can be taught by a team of professors (i.e., faculty
members). In such cases, professors teaching a course join some teams and each team takes in
charge of a course section. Note that while the same course has different sections, each section
has its own capacity limit, exams, homework assignments, i.e., Lion State University let
professors (faculty members) to flexibly decide how many homework assignments and exams
to give in their sections.
a. Capstone section: Some sections of a course are designated by a special type as
CAPSTONE section. The only difference between a CAPSTONE section and a regular
section is that in lieu of exams, students are to form teams to work on Capstone projects
which are mentored by some sponsors. Mentors/sponsors are professors who are faculty
members of any department in Lion State University. Every Capstone project has exactly
one sponsor who may mentor multiple projects. The same project may be performed by
different project teams independently but they are all mentored by the same sponsor. Every
Capstone project team, working only on one project, must have more than one student. As
mentioned before, all students in the same project team receive the same grade, which is to
be assigned by the instructor (i.e., the faculty member who teaches the section).
Task 2: Conceptual Database Design
Based on the result of Task 1, you should present an entity-relationship diagram describing your
conceptual database design. Also, your report should include a narrative description on all aspects
of the diagram in detail. In addition to an overall ER-diagram of your conceptual design, parts of
the ER diagram such as entities, relationships and integrity constraints are expected to be
explained. Note that conceptual database design is application-oriented. Please do not assume the
underlying database system (just yet) and thus do not map your design into relations.
SP2019 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee
6
Task 3: Technology Survey
An aspect of working in the IT industry is to always keep your knowledge updated with the current
market trends and technology innovations. Thus, for the project, it’s important for you to have a
good knowledge of the current web programming and database technology. A technology survey
is meant for you to research a breadth of various web programming frameworks, programming
languages, tools, and database management systems as well as the trends in those technological
areas. Beside taking into consideration the web programming stack suggested by the
CMPSC431W teaching team, i.e. Flask, Pythan, PyCharm IDE, SQLite, you have to come up with
trending alternatives to make a comparison. It is expected for you to write persuasively about
which tools you think as the best fit for the project. Please consider as many aspects as possible
regarding the roles of those technologies in your project, and to justify your own recommendation
by discussing the reasons, pros and cons of your choices. Also discuss the impact and relevance
of those technology trends to the computer science fields and a broader segment of society or
business/industry.
Task 4: Logical database design and Normalization
Based on the Requirement Analysis and Conceptual Database (ER) design you developed in Task
1-2, you will finalize the relational schema for the CanvasPath database. You should produce a
refined schema that reduces data redundancy to an acceptable level (i.e., the final schemas need to
be at least in the 3rd Normal Form) while not unduly affecting performance. Your schemas should
also support the enforcement of most, if not all, of the integrity constraints that you identify in this
phase as well as those newly identified or added after relational schemas are generated from the
ER diagram. In this task, we assume two functional dependencies have been identified: a) zip
codes in address determines state and cities; b) student email address which determines the
login password. You should present the specific details of how the process of schema generation
and normalization is done, i.e., you are expected to apply both of translating the ER diagram to
relations and then perform schema normalization.
SP2019 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee
7
Grading
Different from exams, the project provides a way for students to show their commitment and effort
in learning and practicing the knowledge learned from CMPSC431W. While the course project is
work intensive, students who show their strong motivation and effort through the high quality of
their reports will be rewarded. The project report, fulfilling the Writing requirement of the Penn
State curriculum, is graded in terms of correctness, completeness, presentation, and clarity. Again,
your effort will be reflected in your report and rewarded. Please be reminded that the project is
work intensive -- start early and prioritize your time.
Extra credit of up to 10% will be rewarded for reports who include at least 1 new functionality
expressed explicitly and clearly within the report. This is graded on novelty of the new
functionality and clarity the student is able to present in various tasks of Phase 1.
Submission
As mentioned above, a template will be provided as a .docx which serves as a mandatory style
guideline. The deliverable is a report in PDF containing the requirement analysis, conceptual
database design, technology survey, and logical database design and normalization. The
document must have page numbers, section numbers and a table of contents. Figures should be
used for illustration of your design. In addition, the document should also include a project plan
(including schedule, deliverables, and milestones) as an appendix in the project report. It is
expected that the report be at least 10 pages (not including the cover page and table of content).
Turnitin will be where you submit a .PDF version of your report. As stated in the syllabus,
academic integrity is the most important and there will be no tolerance and exceptions towards
violation. An incomplete but attempted submission is always far better than committing an
academic integrity violation.
Phase II description and guidelines will be announced and posted on Canvas later.
推荐阅读
- 宽容谁
- 我要做大厨
- 增长黑客的海盗法则
- 画画吗（）
- 2019-02-13——今天谈梦想（）
- 远去的风筝
- 三十年后的广场舞大爷
- 叙述作文
- 20190302|20190302 复盘翻盘
- 学无止境，人生还很长