Create All Necessary Database Specifications (Table Names and Field Names)

question 39

Essay

Create all necessary database specifications (table names and field names) for ELM Corporation's sales / collection process based on the following narrative. Indicate primary keys by underlining and foreign keys with brackets. ELM Corporation sells durable medical equipment, such as wheelchairs and hospital beds; each salesperson at ELM specializes in a specific type of medical equipment. When a prospective client comes into their store, a greeter does a preliminary needs assessment to determine what type(s) of equipment the client requires. Based on that preliminary assessment, the greeter will match the client with a member of the sales staff. The staff specialist does a more detailed needs assessment, the results of which go into a computerized database along with the client's name, address, phone number and other related information. After the needs assessment, the staff specialist determines the client's budget, as well as whether the client has insurance coverage for the desired equipment; clients without insurance coverage receive a 10% discount on all equipment purchased up to $1,000, and a 20% discount for any sales over $1,000. The staff specialist makes recommendations based on the client's needs and budget. If the client chooses to purchase from ELM, the store fills out any necessary insurance paperwork and collects the client's portion of the bill. For example, if an insured client purchases a wheelchair with a price of $2,000 and the insurance covers 80%, ELM collects $400 from the client at the time of the sale. ELM accepts cash, checks and major credit cards from clients. Insurance payments go directly to ELM and normally arrive within 60 days of the sale. If the client needs more than one type of durable medical equipment, the first staff specialist introduces the client to a subsequent staff specialist, who repeats the process until all the client's equipment needs are met.

---

Definitions:

Equity Investment

An investment in shares of a company, providing ownership interest to the investor.

Debt Investment

A type of investment where investors lend money to an entity (corporate or governmental) that borrows the funds for a defined period at a variable or fixed interest rate.

Semiannually

Occurring or happening twice a year; every six months.

Face Value

The nominal value printed on a bond or share, distinct from its market value.