Data Management & Governance Assignment - 'All About Furniture' DB Design Exercise

Background -

All About Furniture (AAF) 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, AAF 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 AAF Web site, select and purchase their required items and pay for them. The items would then be delivered to the customer at home. AAF 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 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 AAF 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 choose 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, AAF 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 AAF 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.

Requirements -

Conceptual Design Stage - 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 AAF meets its aims described in the 'Background'.

You need to prepare a class diagram with constraints and assumptions.

Relational Design Stage - Convert the class diagram into a relational model. The relational model should include all primary keys, foreign keys, datatypes and any other required constraints you have identified.

Implementation Stage - Implement your design using Oracle. You will need to perform the following activities:

• Convert your relational model into an SQL database.
• Populate your database with some sample data
• Write 5 queries that demonstrate that your database meets the data requirements of the system.

The Final Report - You are expected to submit a report that has the following chapters:

Chapter 1: Introduction - one page description of the aims and objectives of the report.

Chapter 2: The Class Diagram with constraints and assumptions.

Chapter 3: A discussion of the major design decisions taken in construction of the class diagram. Your discussion should justify the representation chosen to capture the data requirements. It should also include a discussion on design decisions taken in conversion of the class diagram into a relational model.

Chapter 3: The Relational Model.

Chapter 4: Implementation.

Chapter 5: Queries

Chapter 6: Conclusion. A critical evaluation of your final product and a review of the entire exercise.

Note - Also attached lecture notes you have to follow it so that solution is according to lecture notes.

Attachment:- Assignment Files.rar