SQL Server Performance

Please I need your help

Discussion in 'SQL Server-Related Job Postings' started by JAMESZIGGSYO007, May 12, 2010.

  1. JAMESZIGGSYO007 New Member

    [Contact me for the database that is to be used to solve this little project]
    Business Scenario
    MumsNet was founded in 2003 and has grown over the years to become a UK’s leading specialist brand for mothers-to-be and mothers of babies and pre-school children.
    They offer a collection of maternity clothes, nursery equipment, accessories and toys for babies and toddlers, as well as baby clothes from newborn to 3 years.
    MumsNet markets and sells its products through its own stores and online via its own website MumsNet.com. The company launched its online store at the start of 2005 and has done significantly well as online sales have grown every year since then.
    The management team at MumsNet would like to introduce a new business intelligence platform to their organisation. They would like to start analysing the data collected from its online store as it is well structured and its analysis would provide an immediate benefit to their business.
    You are part of a team of database designers who have just been assigned to work on the new business intelligence platform for MumsNet.
    COMM033/7/Semester 2 2009/10 - Page 4 - Project Deliverable 1:Your team has been authorised to deliver a ‘proof of concept’ business intelligence platform using SQL Server 2008 (Enterprise Edition) to satisfy the following data requirements:
    ï‚· Number of cancelled orders
    ï‚· Percentage of orders cancelled by the customer
    ï‚· Sales value of cancelled orders
    ï‚· Number of unfulfilled basket orders due to out of stock item(s)
    ï‚· Percentage of all unfulfilled basket orders
    ï‚· Percentage of abandoned basket orders
    ï‚· Percentage of fulfilled orders
    ï‚· All percentage calculations are against total orders placed
    ï‚· All order calculations should be broken down, by product, by customer, by day
    ï‚· Sales and quantity sold by product, by customer, by day
    ï‚· Available stock quantity by product
    ï‚· Ordered stock quantity by product, by ordered day
    ï‚· Customer rolls into city, which rolls into region, which rolls into country
    ï‚· Product rolls into product group, which rolls into product category
    ï‚· Day rolls into month, which rolls into quarter, which rolls into year
    ï‚· All product calculations must be broken down to product variant level
    ï‚· Available stock calculations must reflect current, real-time, stock levels
    ï‚· Sales calculations have an acceptable latency of 24 hours
    ï‚· Use UK and Ireland specific data for all calculations
    ï‚· The time period for all calculations from 01/01/2005 until 31/12/2009
    ï‚· Produce monthly sales and quantity sold forecasts
    ï‚· Make product recommendations using market basket analysisCOMM033/7/Semester 2 2009/10 - Page
    5 - Project Deliverable 2:In addition to the data analysis requirements, the administrator who maintains the MumsNet.com database has requested that the following database issues are addressed prior to any new database development:
    ï‚· Table structures containing a high number of duplicate values make the product update process error prone, leading to poor quality data
    ï‚· Product variant stock quantities are being miscalculated by the online order processing application. To rectify this fault, the administrator manually runs stock update scripts on a daily basis.
    Your team has reviewed the MumsNet.com database tables, product data, and order processing stored procedures and has made the following observations:
    ï‚· The Catalogue table is in 1NF and needs to be normalised
    ï‚· The stored procedures responsible for the basket operations do not contain implementation code for error handling and concurrency control. There is no code to validate the existence of a basket or to check stock availability prior to the basket update operation
    ï‚· The basket order status can take one of the following values:eek:
    0: New order o
    1: Abandoned order o
    2: Unfulfilled order due to out of stock item(s) o
    3: Order cancelled by user o
    4: Fulfilled order o The basket order status needs to be updated in accordance to stock availability. ï‚· The data stored in the CustomerAddress table will need to be cleansed before it can be used for data analysis
    Based on your team’s observations, the following recommendations were made to the management team which have been approved for implementation:
    1. Normalise the Catalogue table and migrate all product data to the new structure. Enforce data integrity via the use of primary and foreign keys and implement indexes where necessary.
    2. Develop the following stored procedures to replace the faulty stored procedures responsible for the stock quantity miscalculations:
    a. prCreateBasket: responsible for the initialisation of a new basket order, based on the parameter values supplied by the caller application. The stored procedure must create a new order to store customer and address information in the BasketGroup table. The stored procedure parameters are:-OrderNumber: nvarchar(32)
    -OrderCreateDate: datetime
    -CustomerId: uniqueidentifier
    -AddressId: uniqueidentifierb. prCreateBasketItem: responsible for the creation of one item to an existing basket order, based on the parameter values supplied by the caller application. The stored procedure must create a new order line to store basket item order information in the BasketGroupLineItem table. The stored procedure parameters are:-OrderNumber nvarchar(32)
    -LineItemNumber nvarchar(32)
    -ProductGroupId int
    -ProductCode nvarchar(255)
    -VariantCode nvarchar(255)
    COMM033/7/Semester 2 2009/10 - Page 6 - -Quantity int
    -UnitPrice moneyc. prUpdateBasketOrder: responsible for the update of an existing order, based on the order status supplied by the caller application. The stored procedure must process the supplied basket order status and if appropriate, update the product variant stock quantity. The stored procedure parameters are:-OrderNumber: nvarchar(32)
    -BasketStatus: int3. Cleanse UK and Ireland specific customer address region and country data to prepare it for data analysis. For NULL and empty string values use the value ‘Unknown’.
    4. The implementation of the above improvements will need to be done before the business intelligence platform is developed to improve the data quality and the data analysis output.
    The MumsNet project manager has delivered the database backup file containing the product and sale orders data spanning from 01 January 2005 to 31 December 2009. The database backup file is located at
    COMM033/7/Semester 2 2009/10 - Page 7 - Project Deliverable 3:Produce an individual report (2000 words) based on the template below (diagrams may be shared between team members):
    - Project Structureo
    Project team Structure – Roles and Responsibilities o
    Project plan – Tasks undertaken and task duration o Focus on individual’s role and contribution to the project - Technical Solutiono
    Solution Overview 
    Summary of solution 
    High level diagram o
    Use of Technology  SQL Server 2008 Server Architecture  DBMS Engine
    ï‚· Analysis Services
     Development Tools
    Examine and compare against one alternative technology o
    Data Modelling 
    E-R Diagrams o
    Concurrency Control 
    Produce UML Sequence Diagram for basket update operation o
    Business Intelligence Architectures  OLAP Architectures  Unified Data Modellingo Data Views used  UDF/OLAP Architecture(s) used Data Mining Models  Data Mining Lifecycle
     Algorithms used Technology Assessment  Compare OLAP and UDM
    ï‚· Compare OLAP and Data Miningo
    Testing and verification of results 
    OLAP Cube verification techniques  Data Mining verification techniques - Project Reviewo
    Solution Review (lessons learned) o
    Self Assessment (lessons learned) o
    Recommendations/ Future Work
  2. FrankKalis Moderator

    Welcome to the forum!
    Apart from not even asking a precise question, do you really think someone else will do this homework for you?
  3. Madhivanan Moderator

    Did you copy it from MS word or something?
    It looks well formatted
  4. satya Moderator

    I;m based in UK, you an reach me on sqlmaster at sqlserver-qa dot net to discuss this further.

Share This Page