Urgent Help!

[caniballism]

hi, am a newbie in database design and i really need some help.
am supposed to create a database for the following scenario.

"your organisation is a small firm that stocks equipment for hire to customers, you are required to help management create a database to simplify management of the business.
Equipments are classified under varoius categories, the known ones been 1) Gardening equipment 2) Building equipment 3) Power tools 4) Access equipment 5)Decorating equipment 6) miscellanous.
equipments are of different brands within each category. brands vary in their prices and are obtained from different suppliers, prices of equipments offered by the various suppliers as well as the delivery time for that equipment must be stored in the DB.

customers are grouped into two entities;
business customers- have a 24 hour support to replace any broken or faulty equipment that on hire, upon recieving a complaint an immediate delivery will be arranged to replace the faulty equipment provided it is in stock, otherwise, a refund will be administered.
business customers are given varoius membership categories for discount purposes. these are silver(10%), Gold(25%), Diamond(40%).

Private customers- do not have a 24hour support system, if in a similar situation, they must bring the faulty equipment in themselves and replace them with working ones or get a full refund if not in stock.
Hiring equipment for private customers is restricted to those who live within 5 miles raduis from their address. the distance is measured on a map and the information is recorded."

REquirements
the computer is to be used when a customer visits the company to hire equipments, at the time the following processing is necessary for each transaction.
-if not already known, the customers details are recorded
-the equipment to be hired, with the return date recorded.
- the equipment stock as recorded in the database is updated automatically.

when business customers call for support to replace any broken or faulty equipmenton hire, a log must be made in the dB and whether replacement equipment is arranged and delivered or a full refund is given.
When the equipment is returned, the following processing is required.
-The actual return date entered
-an invoice is printed which shows the item borrowed and calculates the total cost including VAT. the cost should be based on a rate for one day plus a rate for each additional day. the daily rate varies between weekdays and weekend days.
-the stock figures are adjusted
-business customers discounts will be calculated according to their membership category, while no discounts for private customers.

quite long.....................but thats the scenario, i will post some of my ideas, that i hope you can help me with.

[caniballism]

So far i have come up with the following table names and entities

Customer{customer id, customer fname, customer lname, customer address, customer tel no}

business customer{bcustomer id, customer id*, membership id*}

private customer{pcustomer id, customer id*, address distance}

membership{membership id, membership type, discount applicable}

category{category id, category name}

brand(brand id, brand name}

supplier{supplier id, supplier name, supplier address}

equipment{equipment id, category id*, brand id*, supplier id*, price, delivery time}

[almostbob]

Customer{customer id, customer fname, customer lname, customer address1, address2, addresscity, adress_postalcode, customer tel no, customer_category} /* invoices and statements are usually posted, plan for it even if the course notes dont require it */

customer_category{cust_type, discount} /* four possible entries
Private =>0
Bus_Silver => .1
Bus_Gold => .25
Bus_Diamond => .4 */

single lookup table from the customer id then can reference pricing and returns policies
Hope this assists your school work

[caniballism]

Thanks, i understand what you have explained.
am working on my ERD diagram, i will post it here when am done with it for your reviews

[caniballism]

i have created my logical relational schema as below.

Customer{customer_id, customer_fname, customer_lname, customer_address, customer tel no, customer_type*}

Customer category{customer type, discount}

category{category_id, category_name}

brand(brand id, brand name}

supplier{supplier id, supplier name, supplier address}

equipment{equipment id, category id*, brand id*, supplier id*, price, delivery time}

Transaction{transaction_id, equipment_id, customer_id, return_date, cancellation_reason, service_type//refund or replacement}

..........................................................................................................

i need to display the following applications from my schema

1. For a given particular equipment, show current stock and current items on hire with expected return dates. the name of the equipment should be captured at run-time from the user.

2.for a particular business customer, show the current items on hire with expected return dates plus any previous complaints that were made by that customer that involved a replacement or refund.

3.produce, for a given category, the total number of equipmets(i.e. items) under that category available in stock and the number currently on hire to customers.

[SethAngel]

Did you ever get and anwser for number 3? I have the same coursework and i'm stuck with number 3?