Write a report to describe the development of a database based on a on a specific case provided for this exercise.

Aim: The aim of the exercise is to enable students to investigate a business situation within the context of an organisation,design and implement a database to support the data needs of the organisation.

The report requirements and the marking criteria are included in the case study.

Case Study - Home Sweet Home Stores

Background

Home Sweet Home (HSH) is a chain of stores selling an extensive range of household goods from stores located throughout the UK. The company recognises the growing importance of e-commerce in the consumer market and in a bid to remain ahead of its competitors, HSH has decided to expand into this area and offer customers the opportunity to purchase items from a Web site. It should be possible for a customer to log onto the HSH Web site, select and purchase their required items and pay for them. The items would then be delivered to the customer at home.HSH aims to make shopping from its Web site easy for its customer by stocking the goods they require, offering them a wide range of alternatives and keeping then informed them about new products coming onto the market.

The company requires a database system to meet these aims. The database needs to store information about products in stock and products that will shortly be coming onto the market, to track the stock of products available and to record orders made by the company's customers.

Information Required

When a customer enters the HSH website, they are presented with a home page that has a number of options that the customer can select as illustrated in Figure 1. A customer can either browse the site by exploring the various product categories, e.g. kitchenware, cleaning products and storage solutions, or by examining the site's favourites and top selling products, or by selecting the product directly using the product reference code, or by use of a keyword search. Alternatively a customer can chose to directly place an order.

When browsing the site a customer is taken through a series of pages to select products using certain criteria. For example, when a customer selects one of the categories in the top left corner of the home page they are presented with a set of sub categories for that category. Figure 2 shows an example of this. When a customer clicks on one of the sub-categories they are shown individual products that fall under the chosen category. The first page displays just the picture and name of a product - see figure 3. If the customer clicks on a specific product's picture they can view full details of that product - its name, description, product reference number and price. This is shown in figure 4. It is at this point that the customer has the option to place the product in his shopping basket by entering the quantity required and then clicking on the ‘Add to Shopping Basket' button.

All products fall into one sub-category and but can qualify for listing under several categories. For example, a bread bin is in the sub-category of ‘In the Kitchen' but will qualify for both the 'kitchenware' and ‘storage solutions' categories. If a customer uses a keyword search they are taken directly to the individual product pages as shown in figure 3. The product reference search takes a customer direct to the description of the individual product as shown in figure 4.

If the customer does not want to browse they can go direct to the order form as shown in figure 5 where they enter the product reference number and quantity required and then click on the ‘Add to Shopping Basket' button.

A shopping basket contains items that a customer is thinking about purchasing. Once an item has been added to the basket, it can be changed in terms of the quantity required or it can be removed from the basket. An example of the shopping basket is shown in figure 6. The first time in a session that a customer decides to add an item to their shopping basket, or wants to view the basket for the first time, they need to enter their username and password to log on. If the customer is visiting the site for the first time, they will need to sign up for an account before they can use the shopping basket facility. This is achieved by completion of a registration form. An example of this process is available in figure 7.

When a customer actually wants to purchase the items in their shopping basket they click on the ‘purchase' button that takes them to the Basket Summary page and it is here that the order is created. Postage and packing costs are added, £2.95 for orders to the value of £39.99 inclusive, but free on orders of £40.00 and above. The customer also provides the address for the despatch of the goods. The default address is the customer's home address but the customer can add additional delivery addresses as required. These are stored and can be selected from a drop-down menu on the basket summary screen. If the customer requests next day delivery, a charge of £4.50 is levied. Again the shipping method can be selected from a drop-down menu on the page although the only options at present are ‘UK standard charge' and ‘Next day delivery. However it is anticipated that if the company expands and sells to overseas customers then there will be additional shipping methods. An example of the basket summary page is provided in figure 8.

The final step in the purchase process is for the customer to provide payment details. For security purposes details of a customer's credit card(s) are not stored. They must be entered each time a customer makes a purchase.

To manage the delivery of orders to clients, HSH has divided its operation into 10 geographical regions within the UK. Customers are supplied from their local distribution centre that maintains a stock of products listed on the web site.

If a customer makes an order and the distribution centre does not have enough of an item ordered in stock then it will deliver the items ordered that are available and hold those where a delivery of stock is awaited. This means that an order can be delivered in parts. It is therefore necessary to record whether an order item has been delivered or not and to help HSH track the progress of a customer's order, it would be useful to record the delivery date. If an item of stock is not available, an expected date should be recorded so that a customer can be informed of the possible delivery date. New products will be advertised on the Web site before they come into stock so that customers are aware of their arrival. In such cases the date the product is expected to be available must also be displayed.

Coursework Requirements

Conceptual and logical design tasks using UML Class diagram

Design a class diagram that will capture the data, associations and constraints capable of supporting the requirements outlined above. The figures provide some sample data to give you additional information on the kind of data you will need to store. You are not required to reproduce any particular formatting of the Web pages. The model should be capable of merely supporting the Web site - providing/recording data to/from the Web site. Specifically the system should be capable of helping OWW meets its aims described in the ‘Background'.

You need to prepare a class diagram with suitable set of attributes with their data types, indicate primary keys, foreign keys, named relationships, constraints and assumptions. You are NOT required to capture any operations in the class diagram or any data types for the class attributes.

Implementation Stage

The final stage will be to implement your design using Oracle. You will need to perform the following activities:
• Convert your Class diagram model into an SQL database.
• Populate your database with some sample data
• Test your database, and provide five sample queries you can with your database to retrieve some useful information.

Attachment:- Home Sweet Home case figures.pdf