代写Database、代做ER Model、代写SQL编程语言、代做SQL设计

日期：2019-11-23 10:44

Electronic Vendor Database

1 Project Timeline

The project has three deliverables;

_ ER Diagram: each group should develop an ER Model for the application

(“Electronic Vendor Database”). The ER Diagram can be included in the report as any

type of image file.

_ Relational Schema: the second deliverable is a translation of the ER Model into a

relational schema implemented as an SQL script. You can use Oracle or any other DB

(e.g., MySQL and PostgreSQL) for the project in which the submitted script should be

executable. Besides from defining tables and constraints, this script may include

views or indexes where appropriate. Please upload the script as a simple text file.

_ Application: the last deliverable is an Electronic Vendor Database Application that

uses the relational schema defined in the first two deliverables. This application can

be either a web or desktop application.

2 Overview

The application is an electronics vendor that operates both a website and a chain of

many physical stores. Examples include Best Buy and Circuit City. To find out more

about this application, think about any experiences you may have had making

purchases both online and in store, and browse their websites. In our hypothetical

company, it has been decided to redesign a major part of the database that

underlies company operations. Unfortunately, the manager assigned to solicit

database design proposals is unable to provide a very detailed specification at the

technical level. Fortunately, you are able to do that. Here are a few points to

consider:

_ There are many different products, grouped into a variety of (possibly overlapping)

categories. Groups can be formed by type of product (cameras, phones, etc.), by

manufacturer (Sony, Apple, etc.), or by other means (for example, a Gateway PC

might be packaged with a Sony monitor and an HP printer and marketed as a

package).

_ Some customers have a contract with the company and bill their purchases to an

account number. They are billed monthly. Other customers are infrequent

customers and pay with a credit or debit card. Card information may be stored for

online customers, but not for in-store customers.

_ Online sales must be sent to a shipper. The company needs to store the tracking

number for the shipping company so it can respond to customer inquiries.

_ Inventory must be accurate both in stores and in warehouses used to replenish

stores and to ship to online customers. When inventory is low, a reorder should be

sent to the manufacturer and listed in the database. When goods arrive, inventory

should be updated and reorders marked as having been filled.

_ Sales data are important for corporate planning. Marketers may want to look at

sales data by time period, product, product grouping, season, region (for stores), etc.

3 Application

The application should be developed for only one electronic vendor (e.g., Best Buy).

It should support the following actions:

_ Every customer can register an account with an email and will be assigned an ID (as

a frequent customer). If one customer purchases multiple products from the website

and physical stores, the same ID is used. Note that frequent customers can also

purchase as infrequent customers.

_ A frequent customer holding an account can register/modify/delete the

information for their account.

_ A frequent customer can complete his/her transaction online (including shipping),

and check his/her purchase history from both website and the physical stores, as

well as the stock information of different products available for purchase (both

online and physical stores).

_ Infrequent customers can complete their online transactions using their credit

cards. Before each purchase, the system checks that if the customer has enough

available credit for the purchase (assuming that the available credit is also

maintained by the electronic vendor). Note that the application does not necessarily

include the functions for the sales data analysis, but the backend database should

store such information for future decision support. If you would like to add more

features in the application, please feel free to do so.