custom writing services

Need more information?: Chat with us

order download
Average score 4.96 out of 5 based on 673 opinions

Advanced Databases & the Web Online Sweet Shop Design Exercise

CI6230: Advanced Databases & the Web
Online Sweet Shop Design Exercise

1 Background
Online Sweet Shop (OSS) has been set up to sell sweets on-line. It is a UK based company and its market at present is restricted to England. All selling is done over the Internet, the company does not operate any retail outlets. The company would like to establish itself as a leading Internet retailer. It aims to make shopping for sweets from its web site easy for its customer by stocking the sweets they require, offering them a wide range of alternatives and keeping then informed about new sweets coming onto the market.

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

2 Information Required
OSS has divided its operation into 10 geographical regions within England. Each region has a warehouse that stores the sweets until they are required to be delivered to the local distribution centres located within that region. A distribution centre can request deliveries of sweets from its warehouse in two different ways. A standing order is used to request sweets required at regular intervals throughout the year. A special order is used to request goods that are in demand at certain times of the year. A standing order basically remains the same but can be revised to meet seasonal fluctuations in the demand for certain sweets. For example, chocolates, such as tins of quality streets, are in more demand in the Winter (Christmas) and Spring (Easter), chewy sweets, such as Haribos, tend to be more popular in the autumn (Halloween). Examples of a standing order and special order can be seen in Figures 1 and 2. Standing orders have set frequencies and delivery days but the delivery of a special order will be set by the warehouse once it receives the special order.

When a customer enters the OSS website, they are presented with OSS’s home page which has a number of options that the customer can select as illustrated in Figure 3. It offers the opportunity to view sweets from a series of different perspectives.
Figure 4 shows an example of sweets in the chocolate category.
Each sweet is allocated a unique sweet code. If a sweet is held in more than one size, a different code is used, one for each size of the sweet held. Each sweet also has a description, size, the number of sweets per multi packs and cost by both pack and individual sweet. Sweets also need to be classified by various categories to meet the searching requirements of the Web site as shown in Figure 3. OSS also use their Web pages to promote new sweets that will be available in the near future and to indicate sweets that are currently out of stock at the distribution centre but should be arriving shortly from the regional warehouse. The stock of each sweet at each distribution centre is recorded. If a sweet becomes out of stock then the date of the next delivery is recorded, if known.

OSS will often promote sweets by giving discounts to customers. If a sweet is on offer, the type of offer is recorded; for example, buy 3 for £2, 10% reduction, a price reduction, etc, along with the value of the discount where applicable, along with the dates during which the offer is valid. An offer can only apply to a single sweet.

If the customer is interested in a sweet, they can add it to their shopping list. This is done by clicking on the ADD TO SHOPPING LIST button against the sweet. A shopping list is where OSS’s customers can bookmark sweets they are interested in but have not yet decided to purchase. The shopping list enables customers to keep track of sweets in which they are interested, but have not yet decided to purchase. It also enables customers to flag new sweets that are not yet available in which they are interested. Unless deleted by a customer, sweets remain on a shopping list for 60 days before being automatically deleted. A customer’s shopping list is saved so that a customer can return to their list at any time.

A shopping basket contains items that a customer has decided to purchase. Items can be moved from the shopping list to the basket or added directly to the basket by selecting the ADD TO SHOPPING BASKET against the sweet. When an item is added to a shopping basket, the customer is asked to specify the amount required either as individual sweets or in packs. An example of a shopping basket can be seen in Figure 5. Only sweets that are available can be added to the Shopping Basket, new sweets that will be arriving shortly cannot be added. A shopping basket item can only be individual sweets or packs, the two cannot be mixed in the same item. Within an order a customer can purchase individual sweets and packs but as separate items.

The first time in a session that a customer decides to add an item to their shopping basket or list, or wants to view either of these for the first time in the session, they need to enter their username and password before they can proceed.

When a customer is happy with the items in the shopping basket, they click on the ‘Proceed to Checkout’ button where they are asked to enter their payment and delivery details. The customer’s address is held and is assumed to be the delivery address unless the customer specifies otherwise for that particular order. It is at this point that the database needs to check if the item requested is in stock at the customer’s nearest distribution centre and calculate an appropriate delivery date. If the item is not in stock and it is not known when new stock will arrive, then the item needs to be flagged as ‘awaiting new stock’. Items in an order may be delivered separately depending on the availability of stock. As a matter of policy, OSS prefers to deliver ordered items that are in stock as soon as possible rather than waiting for the whole order to be ready. Figure 6 shows an example of a customer order. A delivery charge of £5.00 is applied to any orders under £50. A charge of £10 is made for orders between £50 and £100. Delivery on orders over £100 is free.

Customers can keep track of their orders via the ‘View Orders’ option once they have logged in. Items are delivered at a time agreed by the customer as part of the entry of payment and delivery details.

3 Coursework 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 OSS meets its aims described in the ‘Background’.

You need to prepare a class diagram with constraints and assumptions. You are NOT required to capture any operations in the class diagram or any data types for the class attributes. Your model should be ready for submission onto studyspace by Friday, 30th October at 11:59pm.
This is worth 20% of the coursework mark. Following feedback, you can revise the class diagram and improve your mark. If you do not submit the class diagram at this stage, you will receive no marks at all for this element of the coursework.

Relational Design Stage
You will now undertake the first step in designing the database for OSS. This will involve the following activities:
• Revise your class diagram in the light of the feedback you have received.
• Convert the class diagram into a relational model.

Implementation Stage
The final stage will be to implement your design using Oracle, demonstrate it and write a final report. You will need to perform the following activities:
• Convert your relational model into an SQL database. (‘SQL Plus Tutorials’ can be found in in the Assignments folder on studyspace).
• Populate your database with some sample data
• Test your database. You will need to consider testing the database to ensure that the database meets the information requirements of the system. You need to create and run SQL queries that produce the information required.

4 Coursework Assessment
The coursework component of the module will be assessed in two stages. The first part will be a demonstration of your database; the second part will be the report.

The Demonstration
The demonstration will be assessed in your coursework pairs and is worth 30% of this assignment’s overall mark. The demonstrations are scheduled to take place in the workshop sessions on Friday, 11th December.. You will be expected to choose a timeslot for your demonstration.

You will be required to demonstrate that your database meets the information requirements of the OSS System and that your database is in a consistent state. Further details are available on the demo mark sheet that is available on StudySpace in the Assignments folder. A copy of this should be printed off and brought along to your demonstration with your names and ids filled in.

If you wish, there will be earlier opportunities to do your demonstration. You will need to book this by email with either Pushpa or myself. Demonstrations must be completed by the end of Friday, 11th December.

The Final Report
The design and implementation stages will be undertaken as a team exercise where you will be working in pairs. The final report will be an individual piece of work and will document the activities you have undertaken and is worth 70% of the final grade for this assignment. It requires 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 together with a one page discussion of the major design decisions taken in construction of the model

Chapter 3: The Relational Model and a one page discussion of major design decisions taken in conversion of the class diagram into a relational model

Chapter 4: Implementation. Provide a listing of the SQL table definitions and a one page discussion of major design decisions taken in conversion of the relational model into an Oracle database.

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

Further details are available on the report mark sheet that will be available on StudySpace in the Assignments folder. The report should be submitted onto studyspace by Friday, 8th January, 11:59pm.


  simple order process Pricing order download You have just landed to the most confidential, trustful essay writing service to order the paper from. We have a dedicated team of professional writers who will assist you with any kind of assignment that you have. Your work is handled confidentially with a specific writer who ensures you are satisfied. Be assured of quality, unique and price friendly task delivered in good time when you place an order at our website. banner We have a team of dedicated writers with degrees from all spheres of study. We readily accept all types of essays and we assure you of content that will meet your expectations. Our staff can be reached  via live chat, email or by phone at any given time with prompt response.  

Prices starting at:

High school
Undergrad.(yrs 1-2)
Undergrad.(yrs 3-4)
$10 / page
$10/ page
$12/ page
$15 / page
$17/ page

Note: The prices in the table above are applicable to orders completed within 14 days. Kindly see the full price table for more prices..

We urge you to provide as much information as possible to avoid many revisions. Set your deadline, choose your level, give payment information and relax while you track your work. We will deliver your paper on time. . 1 Are you in a quagmire and you are unable to complete your assignments? Do you doubt yourself on the quality of the essay you have written?  is here to assist you. We will offer you unmatched quality that is plagiarism free. Place an order at  for guaranteed  high grades. order download

we offer gifts to our paper help customersPayment Methods

you can request our services and pay via paypal

we offer gifts to our paper help customersOur Guarantees

  1. Zero plagiarism.
  2. 100% confidentiality.
  3. FREE Reference page
  4. High Quality papers
  5. 24/7 Customer support

Our Testimonials Testimonials

I ordered a 7 page college essay and was delivered on time. Thank you guys I will definitely order again!

, FL

Our statisticsOur Statistics

  • Orders Completed todate 65387

  • Number of writers 246

  • Satisfied customers 98.7%

  • Returning customers 92.3%

  • Orders placed today 204