Words: 547
Equipment’s are of different brands within each category. Brands are varies in their prices and obtained from various suppliers. The known categories are: a) Gardening Equipment d) Decorating Equipment g) Heating and Lighting b) Building Equipment e) Car Maintenance h) Miscellaneous c) Access Equipment f) Power Tools Prices of equipment offered by the various suppliers as well as the delivery time for that equipment must be kept in the DB. The firm arranges for business customers only, a 24-hour support to replace any Rosen or faulty equipment that on hire.
Upon receiving a customer complain, an immediate delivery will be arranged to replace the faulty equipment provided that it is in stock. Otherwise, a refund for the full charged amount will be administered. Private customers though, in similar cases, must bring the faulty equipment(s) themselves and replace them with working ones or get a full refund if there isn’t any available in stock. Business customers are given various membership categories for discount purposes. These are silver, gold and diamond memberships.
Silver members get 10%; Gold members get 25% and Diamond members get 40%. Hiring equipment’s for private customers are restricted to those who live within 5 miles radius from their address. This distance from the private customer address is measured on map on the wall and the information stored on the customer record the first time the hire equipment’s. No such restriction exists for business customers. Requirements: The following sections specify the requirements of the system. The computer is to be used when a customer visits the company to hire equipment’s.
At this time the following processing is necessary for each transaction: ; If not already known (I. E. Not previously entered into the DB), the customer’s details are recorded. ; The equipment to be hired, with expected return date, is recorded. ; The equipment stock as recorded in the database is updated automatically. When business customers’ call for support to replace any broken or faulty equipment’s on 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 is 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 customer discounts will be calculated according to the membership category, while no discount is given to private customers. 2. 3. 4. Design and create a database to the above scenario.
State any assumptions made (if any). Map the diagram in to Relational Schema and Proof your answer is normalized up to BC. Create the Database using Myself. Final report should contain: 0 Assumptions 0 Entity Relationship Diagram 0 Relational Schema 0 Proof of Normalization 0 Myself commands which you have used to create the database Assignment Background A Database Management System (DB’S) is a set of programs that enables you to store, modify, and extract information from a database, it also provides users with tools to add, delete, access, modify, and analyze data stored in one location.