数据库代写｜sql｜web｜Database Management Systems Instructor: Wang-Chien Lee

Microsoft Word – NittanyPath-v1.docx

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee

NittanyPath

NittanyPath is a startup project which aims to help Nittany State University (NSU) maintain their software system for course information management. John Smith, a stakeholder of the project, along with members of the Nittany 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 Nittany 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 NSU 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 NittanyPath 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 entity- relationship model to expressed the data and constraints identified. Furthermore, students shall perform a technology survey by researching on the current web/database application technologies,

1

SP2020 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, Python, 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 of 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, MS Word, or other document typesetting languages or tools. Your report should be converted into one PDF file for submission electronically on Canvas.

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, Python, 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).

2

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee

PHASE I – DATABASE DESIGN AND TECHOLOGY SURVEY Checklist

Tasks

Requirement Analysis Conceptual Database Design

Technology Survey
Logical Database Design and Normalization

Task 1: Requirement Analysis

As mentioned earlier, NittanyPath aims to replace the current course management system in Nittany 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 NittanyPath. The following is a general description of the expected system functionalities in NittanyPath. 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 (as well as illustrative figures), 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 NittanyPath system from the users’ perspective, then we describe some additional information in the system.

1. NittanyPath 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 three types of users, Students, Faculty members, Teaching Assistants, as well as a special staff called Administrator, who takes charge of managing all students, faculty members, teaching assistants and courses information. At the beginning of each semester, the Administrator will create all courses on NittanyPath. He maintains all students, faculty members and teaching assistants 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. For teaching assistants, the administrator maintains their departments. 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. Note that the administrator only provides necessary initial information for course configuration in NittanyPath and he does not use it

3

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee

after the semester begins. Therefore, do not worry about the design of administrator. The primary NittanyPath users whom you should be concerned about include only students, faculty members and teaching assistants. 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 Nittany State. It is important that detailed information of students is collected and stored within NittanyPath.

For students, NittanyPath is a system to enroll into courses and to view their course information, assignments scores and courses grades. A student can log into NittanyPath with her/his email address and her/his own initial password at first. After logging in, she/he can modify her/his personal information (except for his ID) and reset login password. In addition, the student should be able to browse/search the general course information (including the instructor’s name) for enrollment to courses. She/he can also find details regarding the courses that she/he takes (grades, instructor name and their details). A student cannot find specific information (e.g., assignments) of courses that she/he is not taking. For privacy concerns, students are only able to see their own grades.

For each course a student is taking, she/he can create posts in the course forum about questions or concerns about the course, which is visible to and can be commented by other students, teaching assistants and faculty members of that course. The students can also see and comment the posts (note that a student can comment only on the posts instead of other comments) created by other users.

Intuitively, for a student to be a valid user of the system, she/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. As for the course forum, each student can make multiple comments on a post in the forum.

b. Faculty Members: The same with students, faculty members log in the NittanyPath system with their email and initial login password. After a faculty logs in, she/he can modify her/his personal information and reset her/his login password. Besides, she/he can see the list of the courses and sections she/he teaches. For a course section she/he teaches, the faculty member can see the names (along with IDs) of students enrolled in the section. Also, the faculty member can create entries for homework assignments and exams. She/he will submit the scores of assignments and exams for students after grading. At the end of semester, she/he will submit final grades for students based on their academic performance. Besides, a faculty member can make announcements in a course she/he teaches, which can be seen by all the students, teaching assistants and all other faculty members of that course. In the course forum, a faculty member can see the posts and the comments created by other users and make comments. In addition, any post in the course forum can be deleted by the faculty members.

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

4

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee

sections of the same course in one semester, which should be recorded by the NittanyPath. However, only the courses (sections) being taught in the current semester can be managed by the faculty member.

c. Teaching Assistants: A teaching assistant is a student who can log in his account, modify her/his personal information and reset the password. After logging in the system, the teaching assistant can see the courses he is taking and the course he is teaching. Different from faculty members, however, the teaching assistant cannot create homework assignments or exams for a course but only can grade the homework assignments and exams for all the students. In addition, the teaching assistants may make announcements in a course she/he teaches, which can be seen by all the students, teaching assistants and faculty members of that course. In the course forum, the faculty members can see the posts and the comments created by the other users and make comments.

Intuitively, each teaching assistant can only teach one course section in each semester, and he may teach the same course in different semesters. Only the courses (sections) being taught in the current semester can be managed by the teaching assistant. A student cannot be the teaching assistant of the course he is enrolled in.

The above is stated from the user view of NittanyPath. The following parts describe the Departments, Courses and Sections which are to be managed in NittanyPath.

1. Departments: Like all universities, departments in Nittany State University are home of majored students and affiliated faculty members. Every faculty member belongs to exactly one department (no more, no less); 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.
2. Courses: Courses are the main area of focus which the NittanyPath project is targeting on. In Nittany State, a course can only be offered by one department. In addition, courses have pre-requisites (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 (you may use course information in LionPath of Penn State as a reference). Note that each course has its own dropping- course deadline, before which the students can drop the course.

4. Sections: a section is the lower-level unit of a course. One course can have multiple sections. Sections are denoted by section numbers. Note that each section can be taught by a teaching team of professors and teaching assistants. The teaching team consists of one or more professor and zero or more teaching assistant. 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., Nittany State University let professors (faculty members) to flexibly decide how many homework assignments and exams to give in their sections.

Task 2: Conceptual Database Design

5

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee

Based on the result of Task 1, you should present an entity-relationship (ER) 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 in detail. Note that the 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.

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, Python, 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 NittanyPath 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.

6

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee

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 (i.e., proposed by you, not stated in the above project description), 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 contents). Turnitin will be where you submit a .PDF version of your report. As stated in classes as well as 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.

7

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee

PHASE II – SYSTEM PROTOTYPE

The EECS students have sent in their design proposals to Mr. Smith, who with the assistance of Dr. Lee and his teaching team, has determined the best conceptual and logical database designs. Now, Mr. Smith wants to validate the idea by prototyping a web application based on the selected logical database design and see how it work. To make it easier to compare different implementations, Mr. Smith has extracted a dataset (in the form of CSV files) from the legacy system, which will be used by the students to populate the database and test the prototyped system functionality. For evaluation, the student implementations will be tested/evaluated based on the ability to execute designated tasks and the overall user experience from using the website.

The schema for NittanyPath has been provided in a separate document. You are expected to follow this schema for Phase 2. However, you can make changes to the schema as long as you use the data from the CSV files we provide.

Checklist ✓ Task

Database Population
Functionality Implementation Progress Review
Reflections and Final Deliverables

Project description

In Phase II, you will be provided with the following materials: i) a database schema design and ii) CSV files which contain the raw data (will be released later). You are supposed to reorganize the raw data in accordance with the schema and populate the database. In addition to this, you will implement the web application for NittanyPath and demonstrate the functionality of NittanyPath as detailed in Task 2 below.

Task 1: Database population

The first task is to create and populate the database. You will be provided with large data files where the columns contain values for various fields. However, please note that those are RAW data. In other words, do not simply treat those spreadsheets as ‘tables’ in the database since they may not comply to the integrity constraints. You are supposed to organize the raw values in the files based on the schema provided to create all of the necessary tables, define primary keys, foreign keys, and specify any integrity constraints as appropriate. In addition, define any views of the schema as you see appropriate. Again, please inspect all the columns and the corresponding

8

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee

data types in the CSV files before you proceed to populate the database. To support additional functionality you wish to add, you are encouraged to augment the schema and add your own data. You can use Python libraries like Pandas or other database facilities/tools to parse the CSV files and insert values to the table. You should provide (and maintain) one or more SQL scripts used in your data population, so that you can easily regenerate the database or restore the database states.

Task 2: Functionality implementation

You should prototype (i.e., implement) the system functionality as specified below and be able to demonstrate the following operations on your final system prototype. The following is a description of the expected functionality.

1. UserLogIn: This is for a user (a student, a faculty member or a teaching assistant) to log in. Note that the necessary users’ log-in information is pre-stored inside NittanyPath system by the administrator. The system recognizes the user by his username and password. Please be sure that the user’s password is hashed when you store it and it is not displayed while being entered.
2. CheckingInfo: This is for a student to get information of the courses he is taking, which includes the course description, professor contact information (email and office) and course grades. Students should also be able to see their personal information, but change password only.
3. CreatingPosts: This is for students, teaching assistants, or faculty members to create posts on topics being discussed in classes, assignments, projects and examinations. If one creates a post on any topic, any student, any teaching assistant, and any faculty member in the section can read and write comments to that post. Comments by any student, teaching assistant, or faculty member can be created by displaying the name of the stakeholders.
4. CreatingAssignments: This is for a faculty member to create assignments in the section she/he is teaching. The assignments include homeworks and exams. The assignments will be seen by the students after the faculty member creates them.
5. SubmittingScores: This is for a faculty member to enter the scores of a certain assignment for all students after grading. There should be a grades page for each assignment which includes a table whose columns are the student’s IDs and grades. The scores should be entered into the table. For uniformity, we assume all scores are out of 100.
6. DroppingCourses: This is for a student who wants to drop out a course before the late drop deadline. The system needs to block a request if the late drop deadline has passed. If a course is successfully dropped by the student, the posts created by the student, and comments made by the student should also be deleted.

In addition to the functionality listed above, you may choose to implement one or more of the functionalities below for extra credits.

9

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee Optional functionality to implement:

1. GradeBook: At the end of semester, NittanyPath will submit final letter grades for students based on their academic performance. Grades can be assigned with a 70-30 weight distribution for examinations and homeworks. You can make your own assumption regarding what final scores corresponds to which letter grade. Besides, students should be able to view the highest score, lowest score and average score in each assignment besides their own scores.
2. MakingAnnouncement: This is for a faculty member or a teaching assistant to make announcements to the students. There should be an announcement page which includes all the announcements previously made in each section.
3. Administration: This is for an administrator (i.e., a university employee with the ID admin@Nittanystate.edu) to manage the courses (add/remove), assign professors to courses, and enroll students to courses and sections. Ensure that no Professor is left unassigned and the students’ enrollment to the course does not exceed the course limit.

Please note that while the correctness of functionality above is critical, the design of a user- friendly web interface is also very important to the success of the prototype. As we expect this system to impress the members of the Nittany State Advisory Board and eventually be used by all the students, faculty members and teaching assistants of Nittany State University, please pay attention and effort to the web design of the prototype.

Task 3: Progress Review

We will have a midway project review to serve as the progress checkpoint before the final deadline. It will be held soon after spring break and you are expected to complete and showcase to the teaching team at least the following tasks:

• Database population
• UserLogIn You will be given 5 minutes to demonstrate the above. We will post the schedule for the progress review and signup sheet for your respective project demos at a later time. Please note that all files of your code have to be submitted on CANVAS in a zip file. You have to include a ReadMe file to describe your code functionality and control flow. Your code has to be properly documented. Task 4: Reflection At the end of project, it’s beneficial for you to flash back what you expected at the beginning of the semester regarding the project and reflect on whether you meet the goals you set back then. Make a comparison between what was expected, and what has been achieved. Discuss the lessons learned – for example, why some parts of the required functionality were not completed (if any), how you added some new features (and why, if any), how you would proceed with the project the same or differently in the way you do in the course, and your thoughts about the project and suggestions to Mr. Smith, etc. Please plan and include the reflections as part of your video presentation (see more details of the video presentation in the Deliverables section below).

10

SP2020 CMPSC431W Database Management Systems Instructor: Wang-Chien Lee

Final Deliverables

The final deliverables in Phase 2 include a video presentation and a demonstration of the NittanyPath system. In your video presentation (no more than 3 minutes), please discuss important design and implementation details you considered and make a reflection. Your video presentation should be submitted via Canvas. For help with making the video presentation, please have a look at: https://mediacommons.psu.edu/support/tutorials/

In the project demonstration, you are expected to show your prototype implementation of the NittanyPath system to the teaching team. We will use the website and check some/all of the compulsory tasks with data from the CSV files provided to you. You may also show the optional functionality for extra credit. Please be well-versed with your code as we may ask you to explain the web server code and the queries you wrote. Please note that all files of your code have to be submitted on CANVAS in a zip file. You have to include a ReadMe file to describe your code functionality and control flow. Your code has to be properly documented for full credits.

As stated in classes and the syllabus, academic integrity is the most important — there will be no tolerance and exceptions towards violation! An incomplete but attempted submission is always far better than committing an academic integrity violation.

Your extra efforts will be rewarded with extra credits!

11