SP2019 CMPSC431


SP2019 CMPSC431W Database Management Systems
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:

  1. Requirement Analysis, Conceptual Database Design, Technology Survey, Logical
    Database Design and Normalization.
  2. 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
    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.
    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
    Requirement Analysis
    Conceptual Database Design
    Technology Survey
    Logical database design and
    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
    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.
  3. 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
    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
    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
    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.
  4. 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.
  5. 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).
  6. 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
    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
    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
    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.
    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.
阅读 105
1 声望
0 粉丝
0 条评论

1 声望
0 粉丝