Spring 2022 CMPSC431W Database Management Systems
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Spring 2022 CMPSC431W Database Management Systems
NittanyMarket is a new platform under planning which aims to provide members of Nittany State University (NSU) to sell and buy used or unused goods online. The original objective of the platform is to allow departments and units of NSU to handle the removal of surplus equipment owned by NSU in environmentally responsible ways by reselling them to students, faculty, and staff in discounted prices. Due to the lack of a safe and public platform for selling and buying items on campus, the Nittany State Advisory Board charges Allen Walker, the manager of Nittany Surplus – a unit of NSU, to conduct a feasibility study.
Soon after reporting the findings of a preliminary investigation, Mr. Walker and Nittany State Advisory Board have realized the value and potential of such a platform, which is not only beneficial to the members of the university but also to the local economy. After extensive discussions, Allen Walker is now charged as the stakeholder of a project, codenamed as Nittany Market, to develop a software system for selling and buying items on campus. Students are permitted to textbooks, electronics, event tickets, clothes, shoes/sneakers, and other approved/appropriate items. Furthermore, the system will be open to local business. NSU has been committed to maintain and increase relations with local business such as Mike and Adidos (M&A), a local footwear maker, and many other categories. Thus, approved local vendors can also sell products on NittanyMarket at prices lower than retail market prices. Currently, the system is planned to support simple buy and sell operations, but the ideas of auction and bidding are brewing (for future extension).
The Advisory Board of Nittany State suggested Mr. Walker to invite EECS students at Penn State University, who are known for their creativity and robust technical skills, to be involved in the project. Mr. Walker, who is not a technical person, reached out to Professor Lee and his team for assistance to carry out a feasibility study and proposals.
After discussing the requirements and manpower needed, Mr. Walker agreed and trusted Professor Lee and his teaching team to advise and support students in CMPSC431W to systematically design and implement prototypes of NittanyMarket to validate feasibility of his ideas presented to the advisory board. 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 careful examination of the tested prototypes, valuable insights and lessons may be obtained. Widely accepted, 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 ready system. Furthermore, an impressive demonstration of the prototyped system, functions, and unique features will surely demonstrate the strong creative and technical ability of PSU students, reaffirming the strong trust between PSU and the Nittany State Advisory Board, which consists of mostly executives in S&P 500 companies.
Mr. Walker and his associates have tried to communicate this project abstractly and expect students to figure out and fill in all the missing details. You (students in CMPSC431W) will design a database-backed web application to buy/sell products on campus by members of the university and local community. You will also implement a prototype to demonstrate system functions and your design. As parts of the prototype, you will need to implement several programs to access data from the designed database to support the functionality ofthe system. The project consists oftwo phases:
1. Requirement Analysis, Conceptual Database Design, Technology Survey, Logical Database Design and Normalization.
2. Protype Implementation
Mr. Walker and Professor Lee ask you (students in CMPSC431W) to envision what NittanyMarket will be. Based on the provided project description, you need to analyze the requirements of NittanyMarket to determine the system’s functionality and to identify the data needed for the system’s functions. Furthermore, business rules (integrity constraints) need to be determined to be imposed upon the needed data. Based on your requirement analysis, you are also required to come up with a conceptual database design by using the entity-relationship model to express the data and constraints identified. Students shall also perform a technology survey by researching on the current web/database application technologies, 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 (or Spider) IDE, and SQLite. Logical database design and schema normalization are also 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 of each phase, let’s talk about what you (students in CMPSC431W) are expected to fulfill while working on the project.
This is an INDIVIDUAL Project! Not a team project. Design and implementation should be done individually based on each student’s OWN ideas!
Document Report and Formatting
The project report should be written in your own words. Reusing (copying) parts or all of the project description directly not only receives no credit but also violate academic integrity in terms of Plagiarism!
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.
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 except for a progress review before the final demo. 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 or Spider IDE (to be determined), 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).
PHASE I – DATABASE DESIGN AND TECHOLOGY SURVEY
Conceptual Database Design
Logical Database Design and
Task 1: Requirement Analysis
NittanyMarket aims to become a modern and safe online platform where university members and local business can buy and sell goods online. Thus, it may help to explore some online shopping websites such as Facebook market, Amazon, eBay, etc. for reference to better understand the expected (and alternative) functions and requirements for NittanyMarket. The following is a general description of the expected system functionalities in the project. In this task, you are required to analyze in detail the system requirements including the system functions, types of data that must be stored, the business rules (integrity constraints) of NittanyMarket to be imposed on the data, and how it will be stored and accessed to support the system functionality. Please note that in this task, you are expected to elaborate upon the system functionality in your own words with figures and identify (write down) the data needed to support each functionality as well as the integrity constraints to be imposed on the data. We first describe NittanyMarket from the users’ perspective, then some additional information on the system.
1. NittanyMarket Users: 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, Buyers and Sellers, as well as a special staff called Administrator, who takes charge of managing all types of users, product information, and system maintenance. The Administrator does not create product listings but manages the products that are selling, regulates the transactions among students, and publishes needed notification. Admin also maintains all students ’ information such as the name, age, gender, email address, home and billing address, which includes street, city, state and zipcode, credit card numbers, and login password. For student users, the administrator also maintains their account type. All students are buyers by default when they log in for the first time and are eligible to upgrade to sellers only when the first application is submitted and approved by the admin. For sellers, they need to fill in what type of product they are going to sell and are only permitted to sell the products they have applied for. The sellers can see the buyer’s information only after they placed an order. After an order is placed and labelled “received” by the buyer, the selling amount of that order will be added to the seller’s account balance. At this Phase, the primary NittanyMarket users whom you should be concerned about include only buyers and seller. We assume all users can log in with
the email address as the ID and the login password. Please note that, while the above description of users is based only on students, the possible users indeed include faculty, staff, departments, and local business vendors. You will need to fill the gap to describe these roles in your own view (i.e., you need to analyze and describe them in the requirement analysis part and the remaining tasks of the report!) The following are what we envision how the users would operate the NittanyMarket system in the roles of a buyer or a seller, respectively.
a. Buyers: Currently the buyers are limited to members ofNittany State (i.e., students, faculty and so on). You may optionally include local people for system extension in consideration of opening up NittanyMarket to the local public in the future. It is important that detailed information of buyers (including age, gender and so on) is collected and stored within NittanyMarket as they may be useful information for marketing analysis.
A buyer can browse the product listings, purchase products, manage personal account information, and ask questions to sellers. Buyers can log into NittanyMarket with their email address (which serves as the user ID) and their own initial password at first. After logging in, they can modify their personal information (except for their ID) and reset login password. Buyers can also manage payment information, add/remove credit card number which is only viewable/accessible to the correct buyer.
In NittanyMarket, goods (also called products) are organized based on a category hierarchy. Buyers should be able to browse products available for purchase in the category hierarchy. For each product listed, the following information is provided: price, seller info, and related details. In addition to browsing all products, the buyer can browse by category which is selectable in a list/menu. Products which are already sold out or deleted by the seller should not be viewable/listed to buyers.
When a buyer places an order, the buyer pays the required price right away. After the transaction is completed, the order is complete and the listing will be archived and no longer listed.
Buyers can, but are not obligated, to give reviews and ratings (as part of a review). Buyers can only review products which they have purchased before. In addition, buyers may rate the seller as well. Note that ratings are meant to serve the community regarding the reputation of sellers. Buyer’s who have purchased products from a specific seller can give a numerical rating out of 5 (i.e., based on the 5 * rating system).
Some buyers may want to sell their own items as well. To do so, the buyer has access to fill out an application form and be approved by the administrator.
b. Sellers: Similar to buyers, sellers also log into NittanyMarket with their email and initial login password. After sellers log in, they can modify their information and reset their login password. Sellers are either members of Nittany State or an approved local business vendor. For approved vendors, information such as business name, business address, and customer service phone number must be captured. Note that NittanyMarket provides an opportunity for the local venders to market their special products, which are unique and different from big brand-name products. Thus, these products will only be available for
sale by their makers (i.e., local business venders). If a local business seller decides to leave the market, the information of its products will be automatically removed and no longer available in the market.
Sellers can automatically publish products (goods) sold by them to be listed. For a listed product, a seller can view all the information for the product, and the questions asked by buyers previously. Sellers can see reviews made by buyers as well as the ratings on themselves given by the buyers. Sellers can list goods under existing categories for sale. An application is needed for a new category not existing in the category hierarchy to be created; and the application is to be approved by the admin. For goods to be published by a seller, some required information, e.g., category, needs to be filled in so that the listing can be “published” , and goods for sale can be found via the category hierarchy. Such information is highlighted below. For an existing product listed, the seller can edit the price, details, titles, but the category cannot be changed. Once orders placed by buyers are in the complete status, sellers should receive the payments. After a product is sold (or out of stock), its listing should be invisible from the buyers. If there are still more products in stock, the listing should reflect the decrease in quantity available.
Intuitively, every product needs to have exactly one category, and every product item is listed and sold by only one seller. For example, John and Jane have a shared textbook. They cannot create two separate lists to sell the same book. Sellers are not limited to selling only one product at a time, sellers can list any number of products in any approved category at the same time. Except the aforementioned case where information about products made by local business vendors information got deleted automatically when they leave the market, NittanyMarket is responsible for maintaining historical listings of sold products for market research and improvement purposes, regardless if their sellers are no longer listed in the platform.
The above is from the user view of NittanyMarket. The following parts describe the Products, Orders, Categories, which are to be managed in NittanyMarket.
2. Products: Every product belongs to exactly one category. Every product item is listed by exactly one seller. Multiple product items for the same seller are listed together corresponding to the seller. For example, a seller can list a smartphone and a textbook at the same time. Products should contain some useful information that help the buyer to find the product (i.e., via browsing or search) and make the purchase. Important information regarding a product like title, product detail, category and price are required. However, additional information which you (CMPSC431W Students) believe to be important can be included, as creativity is always highly appreciated and encouraged! Finally, as mentioned previously, products by local business is unique. Their information will no longer be available if the business vendor leaves the market.
Clarification: The above describes the concept of product items, which are not easy to identify in purchase operations. For example, Both Seller A and Seller B have a Cowbook
to sell. Using ISBN of the Cowbook cannot uniquely identify these two Cowbook items. Instead of modeling the concept of product items, you are suggested to model the concept “Product Listing”, i.e., listing of product items. To make it easier, the teaching team proposed to make the following assumption about "Listing", which has approved by Mr. Walker after a good discussion.
Assumption: The sellers create listings of the product items they want to list for sale. Each listing may contain only ONE product but any number of the product items, e.g., a listing may contain 2 copies of the Cowbook. If there are two items of different products to be listed, two listings (one for each product) should be created, e.g., two separate listings should be created for the Cowbook (of the DB course) and the Dragonbook (of the OS course), respectively. A new listing_ID is generated for each new listing (i.e., listings are uniquely identified by listing_ID).
Based on the above assumption, the two Cowbooks owned by Seller A and Seller B respectively can be easily identified by two different listings. In other words, the purchase of a particular product item is operated upon its listing. If you have different views or ideas regarding the above-mentioned issue, please feel free to pursue your own views/ideas but be sure to specify your assumptions (if you make any) and provide a clear explanation.
3. Orders: An order is placed when the buyer decides on the product and pays the listed price. The order reflects the time and the amount of the purchase transaction when it happens. By looking at a transaction, the seller knows that a product was sold to a buyer. Likewise, a buyer may find seller information form the transaction record. If a buyer is not satisfied or a seller decides to revoke a transaction and after mutual agreement, both the seller and buyer may submit a request to the admin for cancelation, and the funds should be returned to the buyer. Note that all historic order information should be maintained even though the purchase is canceled.
4. Category hierarchy: A category is a generalization of a group of products. It is a higher- level unit of products. Categories available in the platform are predefined by the admin based on his knowledge and experiences. As mentioned, for sellers to sell a new product of some non-existing category, they can send a request to the admin for approval. Therefore, once approved, it’s the admin’s job to create a new category for the item in the category hierarchy. Note that, in the category hierarchy, one category may have multiple subcategories, except the leaves (i.e., the lowest level) of the category hierarchy. The number of products within the category is unlimited. Therefore, the buyers can traverse the category hierarchy to narrow down the products in a specific category.
From the above description, you may tell that Mr. Walker, while known for his excellent management of Nittany Surplus, is not an IT expert. The description mainly reflects some rough ideas he had in his mind. For example, he didn’t even think about how a buyer may search available products, in addition to browse the category hierarchy. Moreover, after finding some
interesting products, a buyer may also see what other products are offered by the same seller. In addition, while he mentioned about facilitating reviews and ratings in NittanyMarket, the ideas are rather vague. To get the project moving forward (and to educate him, your customer), you may fill in the missing information, make suggestions, and inject your own ideas with clear elaboration and justification in your Phase 1 report.
Task 2: Conceptual Database Design
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 (or Spider) 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 NittanyMarket 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 . Your report should also include a narrative description with illustration of the translation (mapping) and normalization.
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 which 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.
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.
PHASE II – SYSTEM PROTOTYPE
The PSU students have sent in their design proposals to Mr. Walker, who with the assistance of Professor Lee and his teaching team has determined the best conceptual and logical database designs which match what they had in mind. Now, Mr. Walker 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. Walker has extracted a dataset (in the form of CSV files) based on a reduced set of relational schemas, 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 the designated tasks and the overall user experience from using the website.
The relational schemas for NittanyMarket have been provided in a separate document. You are expected to follow these relational schemas for Phase 2. However, you can make changes to the schemas as long as you use the data from the CSV files we provide. You may also extend the schemas to create new tables for new features you would like to implement. In that case, you will need to prepare data for the corresponding tables for testing and final demonstration.
Reflections and Final Deliverables
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 (released on Canvas under /Home/Project/). You are supposed to populate the data in accordance with the schema into the database. In addition to this, you will implement the web application for NittanyMarket and demonstrate the functionality of NittanyMarket 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, 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 data types in the CSV files before you proceed to populate the database. To support additionalfunctionality 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.