Question

1a. Construct (draw) an E-R diagram representing the conceptual design of the database. Be sure to identify primary keys, relationship cardinalities, etc. State any assumptions you make.

1b. Convert your E-R diagram to the relational schema. Identify the primary keys of ever relation.

Consider the following application: An electronics vendor operates both a Web site 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 of a store like Best Buy (you can browse their website).

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 not very computer literate and 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 different products, grouped into a variety of (possibly overlapping)

categories. Groupings can be done --> 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.

Answer 1

HERE IS THE ER IN CONCEPTUAL DESIGN.

DONT DISCOURAGE THE WORK IF YOU HAVE ANY DOUBT PLEASE COMMENT I WILL CLARIFY IT FOR SURE