Database代写：CMPSC431WLionAuction

Database代写
发布日期:   2019-04-23

Requirement Analysis
Conceptual Database Design
Technology Survey
Logical Database Design and Schema Normalization
### Task 1: Requirement Analysis
LionAuction aims to become an auction-based online e-commerce platform where
university members and local business can buy and sell goods online. Thus, it
may help to explore some online e-commerce websites such as eBay, Amazon,
Walmart, Facebook market, etc. for reference to better understand the expected
(and alternative) functions and requirements for LionAuction. The following is
a general description of the expected functionality for the project,
reflecting Mr.
Dude’s non-technical point of view of the system. In this task, you should
envisage what the system should look like and how it may operate to enable the
campus auction business. You are required to analyze and describe in detail
the system requirements, including the system functions/operations, data
needed to support those system functions/operations, the business rules
(integrity constraints) of LionAuction to be imposed on the data, and
how/where they 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 LionAuction
from the users’ perspective, then add more information on the system.
1. Registered Users: To operate on LionAuction, a user needs to be registered in the system. A registered user is identified by a user ID (e.g., email address) and authenticated with a password. There are three different types of users who will use the system: Bidders, Sellers, and HelpDesk Staffs (HelpDesk in short). In the following, we describe what those three types of users are expected to do with the LionAuction system in their respective roles.
* a. Bidders: While the bidders are targeting on all members of LSU (i.e., students, faculty members, staffs, and so on), we will consider only students in this initial effort. Therefore, all LSU students are bidders by default. It is important to collect and maintain detailed information about bidders in LionAuction, including e-mail address, name, address (which consists of street, city, state, and zipcode), phone number, credit card info (including type of credit card, credit card number, expiration date), major, age, gender, annual income, and etc. Among them, the last four items are collected for marketing analysis, but we won’t tell the bidders that. A bidder may log into LionAuction with their email address (which serves as the user ID) and their existing password. After logging in, they can update their personal information (except for their ID) and reset login password. A bidder can also manage payment information, add/remove credit card number which is only viewable/accessible to herself/himself.
Additionally, a bidder may browse the product listings via a category
hierarchy (subcategories will be selectable in a list/menu) to find an
interested product and then start bidding the products. He may also send
question about a particular product listing to its sellers. In LionAuction,
products are organized based on a category hierarchy. For each product listed,
seller info and related product details are shown to the bidder. Products
which are already sold (auction successful) or terminated by the seller
(auction failed) should not be viewable/listed to buyers.
After a bidder wins an auction, he/she pays the winning price right away.
After the transaction is completed, the listing will be archived and no longer
listed. The bidder will be able to rate the seller after the transaction
completed. Note that ratings, meant to serve the community regarding the
reputation of sellers, is based on the 5-star rating system.
Some bidders may want to put their own items in auctions as well. To do so,
the buyer need to fill out an application form and submit it to the HelpDesk
for approval.
* b. Sellers: Similar to bidders, a seller also needs to log into LionAucton with her/his user ID (i.e., email address) and password. After the seller log in, she/he can update some account information and reset their login password. Sellers are either students of Lion State University or approved local business vendors. Different from bidders, bank routing number, bank account number, and a balance in LionAucton are mainitained for sellers. Additionally, for approved vendors, information such as business name, business address, and customer service phone number must be maintained. Note that LionAuction 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 made available by local venders for auctions in some promotional occasions. 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 need to enlist/publish the products they want to put up for auctions
on LionAuction by providing the product information, the category of product,
a reserve price (which is hidden from the bidders) and an auction stop time. A
reserve price is the minimum price a seller is willing to accept for the item.
At the end of the auction, if no bid is higher than the reserve price, the
seller is not obligated to sell the item.
For a listed product, a seller can view all the information for the product,
and the questions asked by bidders. Sellers can see the ratings on themselves
given by the bidders. Sellers can list products under existing categories for
sale. If some new products cannot find a fitting category in the category
hierarchy, the seller needs to submit a request to HelpDesk. Once approved,
the HelpDesk Staff will create a new category in the category hierarchy as
appropriate.
When an auction is ended (either successful or fail), the product will be
delisted (no more visible to the bidders) but the auction information
including the bids will be archived in the system. For a successful auction,
once the payment is completed by the bidder, the seller will receive the
payment in the account.
Intuitively, every product needs to have exactly one category, and every
product item is listed for auction by only one seller. For example, John and
Jane have a shared textbook. They cannot create two separate lists for the
same book. While sellers usually are not limited to selling only one product
at a time, we assume a product list contains only one product item for
simplicity. Except the aforementioned case where information about products
made by local business vendors information got deleted automatically when they
leave the market, LionAuction is responsible for maintaining historical
listings of sold products and bidding history for market research and
improvement purposes, regardless if their sellers are no longer listed in the
platform.
* c. HelpDesk: LionAuction will hire a team of IT staffs to manage the system and provide various services. For example, other users (bidders and sellers) may not change their user IDs. They need to submit a request to the HelpDesk team to make the change. Likewise, for a bidder to upgrade his role to a seller and for a seller to add a new category in the category hierarchy, these requests all need to go through HelpDesk. Additionally, they also perform some marketing analysis tasks, e.g., listing the number of auctions put forth by sellers in different majors.
The above descriptions are given by taking the views of different users of
LionAuction. In the following, we describe the Products, Bids, Categories,
which are important information to be managed in LionAuction.
* d. Products: Every product belongs to exactly one category. Every product item is listed by exactly one seller. For each auction, only one product is listed. If multiple items of the same product, e.g., two cow books, are listed, they are auctioned together. Products should contain some useful information that help the buyer to find the product (i.e., via browsing or search) before making a bid. Important information regarding a product like title, product detail, category and reserve 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 are unique. Their information will no longer be available if the business vendor leaves the market.
* e. Bidding: A bidder can place one or more bids on a product list as long as they are not the seller of the product. Each bid that is placed must be at least $1 higher than all previous bids and must be placed before the auction ends. At the end of the auction, the seller via LionAuction notifies all bidders the highest bid. With the notification, the bidder who won the auction can complete the payment transaction. An auction starts when the seller publishes the product for auction and ends exactly at the time the seller specified. Again, all historic auction information should be maintained no matter the transaction is successful or failed.
* f. Category hierarchy: A category is a generalization of a group of products. It is a higherlevel unit of products. Categories available in the platform are predefined by the HelpDesk team. As mentioned, for sellers to put up a new product of some non-existing category for auction, they can send a request to HelpDesk for approval. Once approved, it’s the job of HelpDesk 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 a category is unlimited. Therefore, the buyers can traverse the category hierarchy to narrow down the interested products in a specific category.
From the above description, you may tell that Mr. Dude is not a CS engineer.
The description mainly reflects some rough ideas he had in his mind. For
example, he didn’t even think about how a bidder may search available
products, in addition to browse the category hierarchy. Moreover, after
finding some interesting products, a bidder may also see what other products
are offered by the same seller. In addition, there are various potential ideas
to explore. 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 (for potential extra credit).
### 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 in this task.
### 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 (for application
development), programming languages, tools (e.g., IDE), 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 at least three trending alternatives in
to make a comparison with the software/language mentioned above. 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
database in LionAuction. 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.
### 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 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. The data
needed for the new functionality should also be specified, modeled, and
refined in Task 1, 2, and 4, like other data in the project. This extra credit
is graded on novelty of the new functionality as well as 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.
The length of the body of this report, excluding the cover page, table of
contents, list of figures and list of tables, is expected to be no less than
10 pages. However, based on our experience, 10 pages are usually not enough to
complete all the tasks in Phase 1 of the project with sufficient detail! There
is no upper limit on the number of pages in your document. You may use as many
pages as you like (hopefully not exceeding 50 pages) to produce a complete and
impressive report.
## PHASE II - SYSTEM PROTOTYPE
The PSU students have sent in their design proposals to Mr. Dude, 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. Dude 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. Dude has compiled 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 (UX) from using the website.
The relational schemas for LionAuction 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.
### Checklist
Task

Project Kickoff & Progress Review
Database Population
Functionality Implementation
Reflections and Group Representation
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 (released on Canvas
under /Modules/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 LionAuction and demonstrate the functionality of
LionAuction as detailed in Task 3 below.

Task 1: Project Kickstart and Progress Review

We will kickstart the implementation phase of the LionAuction project soon and
have a project review to serve as the checkpoint of your progress. You are
expected to complete and showcase to the teaching team at least the following
tasks:

  • Database population (Users table)
  • UserLogIn (Basic Authentication)
    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. 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 2: 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 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 the scripts (or
documentation of steps) used in your data population, so that you can easily
regenerate the database or restore the database states. There were some rare
cases of data loss or tables being corrupted during implementation. Data
loss/corruption cannot be used as an excuse for project incompletion or
deadline extension. You need to be prepared for such events.

Task 3: Functionality Implementation

You need to prototype (i.e., implement) the system functionality as specified
below and demonstrate how they operate on your final system prototype. The
following is a description of the expected functionality.

  1. UserLogIn (Complete): This is for a user (seller, bidder or helpdesk) to log in. Note that the log-in information of users is stored inside the LionAuction system (after data population). The system recognizes the user by his/her email and password. Please be sure that the user’s password is hashed when you store it and it is not displayed while being entered. Note that a user may have multiple roles (i.e., seller, bidder or helpdesk). Extending the basic version of the UserLogIn, the LionAuction system should facilitate its users to specify their roles in the login process and allow them to switch roles after login. Based on the specific role, a user will enter a welcome webpage, where allowable operations can be performed by the user. As part of UserLogIn, personal information of the user (e.g., a bidder has name, email ID, age, gender, major, billing address, which includes street, city, state and zipcode, last four digits of credit card number and so on, while a seller and a helpdesk staff have other information) can be displayed/checked based on your design. This welcome page should also provide entry points for other operations, e.g., a bidder may browse the categories to find an auction listing of interest; showing the status of those auctions the bidder has participated in so the bidder may continue to bid; and so on. Please consider possible scenarios in your design to provide a good user experience (UX).
  2. CategoryBrowsing: The auction listings available at LionAuction are categorized based on a predefined category hierarchy in the categories table. Each node (i.e., a category with a descriptive name) is associated with a set of auction listings which belong to the category and a number of subcategories, each of which includes its own auction listings. The root of the hierarchy, labeled as ‘All’, do not have any auction listing associated but a number of top-level subcategories. When a user clicks an auction listing, the auction details, such as auction title, product information, seller information, a product photo (optional), quantity, remaining number of bids, and so on, are displayed, along with a button to facilitate bidding. A user can traverse the category hierarchy by clicking on a subcategory to see the product items under this specific category. In each traversing step, you are required to dynamically query that database to find the next-level subcategories for display Note that the category hierarchy exists in the database logically. Retrieving the whole set of categories all together to build (hardcode) a category hierarchy in the application space is not preferred and will receive only a partial credit.
  3. AuctionListing: This is for a seller to publish/take off an auction listing in LionAuction. For publishing, detailed information of an auction listing (e.g., title, product details, category, and so on) should be entered before the listing is published. The published auction listing should be immediately available for display in its category when users browsing the category hierarchy. The seller can also make changes on an auction listing (active or inactive, but not sold listings). The seller may also take a published auction listing off the market, but useful information about this auction listing, including the remaining number of bids and reasons to take it off the market, should be maintained. Note that the provided schemas and datasets do not have information about remaining number of bids and reasons to take it off the LionAuction market, so you need to extend the database to support it for new auction listings. As part of this functionality, the auction listings of a seller should be properly grouped (as active, inactive and sold) and displayed to the seller for further actions, i.e., take off market and make changes.
  4. AuctionBidding: This is for a bidder to place a bid on an auction. As described previously, a new bid must be at least $1 higher than all previous bids and must be placed before the auction ends (controlled by Max_bids instead of an auction stop time, for ease of demonstration). After a bid is placed, the auction details (also see that in CategoryBrowsing), along with updated number of remaining bids, should be displayed. A bidder cannot bid again until another bid is placed by a competitor. Please consider the scenarios of bidding to provide a good user experience. Once the auction ends, all bidders should be notified with the bidding result. The notification of the winning bid (i.e., the final bid with a price greater than the reserve price) should lead the bidder enter the payment process, i.e., a new page where credit card information can be entered by or retrieved for the buyer. When the payment is submitted, the payment transaction is recorded in the database. Meanwhile, the status of the auction is changed to “Sold”, i.e., it is still maintained in the database instead of being deleted. However, this auction listing should not be available/found/displayed in LionAuction anymore.
    In addition to the functionality listed above, you may choose to implement one
    or more of the functionalities below for extra credits.

Optional functionality to implement:

  1. HelpDeskSupport: To support this functionality, sellers and bidders should have interfaces (i.e., webpages) to send their requests to the team of helpdesk staffs, initially assigned to a pseudo staff [email protected] . The types of requests, including change user ID, add a new category, and performing a market analysis. At the welcome page to a HelpDesk staff, information of unassigned requests and requests have been assigned to the staff should be displayed, so the staff can claim an unassigned request and perform an assigned requests. Due to limited time in the final review, you only need to implement the request for “adding a new category”. After the request is completed, the request status becomes “completed”, which should be reflected properly in the system.
  2. AccountRegisterUpdate: This allows a new user to create a new account and update personal information in the account, except user id which can only be changed by a
    HelpDesk staff.
  3. AuctionPromotion: This is for a bidder to quickly see/find an auction in promotion. A seller can promote an auction list by paying a promotion fee (i.e., 5% of the reserve price) in order to display it on the top of the category hierarchy, i.e., the All node/page. Note that a promoted auction will also be displayed when its associated category node is visited.
  4. Searching: A user can search the auction lists by entering some keywords. The search result is displayed to the user.
  5. Rating: After a bidder wins an auction and competes the payment, the bidder can rate the seller. The rating on the seller is recorded and used to calculate a new average rating of the seller. The new rating should be reflected as part of seller information when it is displayed in the system.
    Please note that while the correctness of functionality above is critical, the
    design of a userfriendly web interface and user experience is also very
    important to the success of the prototype. As we expect this system to impress
    Mr. Dude and potential investors, and provide excellent user experiences to
    its users, i.e., the students of Lion State University, please pay attention
    and effort to the web design of the prototype.
    Finally, risk management is critical to your project. To protect your
    implementation effort and product from disasters, please backup all the data,
    codes, scripts, and documentation in safe external storage.

Task 4: Group Representation - Reflection

At the end of project, the course members of CMPSC431W will form teams to
revisit the (different) decisions made by team members throughout different
stages of their projects. It’s beneficial for course members to flash back
what were expected at the beginning of the project and reflect on whether the
goals set back then have been met. 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. Dude, etc. Have a team
to make a group presentation will allow the group members to share thoughts
and exchange experiences learned from the project. The group members will
obtain in-depth insights to enrich the presentation through brainstorming,
discussion and collaboration and pave their roads toward bright futures.
Please plan and include the reflections as part of the group video
presentation (see more details of the video presentation in the Deliverables
section below).

Final Deliverables

The final deliverables in Phase 2 include a video presentation and a
demonstration of the LionAuction system.
In group video presentation (no more than 6 minutes), please discuss/review
different tasks you performed at different stages of the project, i.e., not
only the prototype implementation in Phase 2 but also the database design in
Phase 1 to make a reflection. Note that the video presentation is not a
recording of your project demonstration. Please exploit the opportunity
provided by the group work to revisit/compare various important design and
implementation decisions you considered to share thoughts, suggestions and
ideas for future. More information and guidance regarding the group work will
be discussed in classes and announced on Canvas. Your group video presentation
should be submitted via Canvas.
In the project demonstration, you are expected to show your prototype
implementation of the LionAuction system to the teaching team. We will test
the prototyped 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 credits. Please be well-versed with your code as we
may ask you to explain the web/application server code and the queries you
wrote. Please note that all files of your codes, scripts, databases and
documents, organized in a project directory ready for execution in PyCharm
Prof. or the IDE of your choice, have to be submitted on CANVAS as a zip file.
You have to include a ReadMe file as requested previously in Web Programming
Exercise. It should provide i) context of your code (i.e., background
information such as what they are used for); ii) features (i.e.,
functions/operations of the codes); iii) organization (i.e., how the files are
organized); iv) instructions (i.e., how to run the code, including how and
where to load the files into PyCharm Professional). Your codes have 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!

文章作者: SafePoker
文章链接: https://bestcstutor.github.io/2019/04/23/Database%E4%BB%A3%E5%86%99%EF%BC%9ACMPSC431WLionAuction/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 SafePoker !
Database代写
赏
  目录