I need your help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I need your help

Author

Topic

JAMESZIGGSYO007
Starting Member

1 Post

Posted - 2010-05-12 : 07:43:44

Good Afternoon,
My name is James and it's my first time using MS SQL. It seems pretty difficult. Well, i have a question to ask and i hope i can get an answer, thanks in advance.

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.

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 analysis

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:
o 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: uniqueidentifier
b. 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)
-Quantity int
-UnitPrice money
c. 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: int

3. 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.

Project Deliverable 3:
Produce an individual report (2000 words) based on the template below (diagrams may be shared between team members):
- Project Structure
o 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 Solution
o 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 Modelling
o 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 Mining
o Testing and verification of results
? OLAP Cube verification techniques
? Data Mining verification techniques

- Project Review
o Solution Review (lessons learned)
o Self Assessment (lessons learned)
o Recommendations/ Future Work

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-12 : 07:47:30

Lol.

You should maybe read your textbooks and go to some of the tutorials.

We won't give you homework / test answers.

If you make a stab at a design / solution we will tell you what is right or wrong with it though.

Good Luck.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 07:54:56

Well, i have a question to ask and i hope i can get an answer
Sure, what is your question?

No, you're never too old to Yak'n'Roll if you're too young to die.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-12 : 08:12:33

If I do your homework, do I get the qualification?

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-12 : 08:13:13

quote:
Originally posted by webfred

Well, i have a question to ask and i hope i can get an answer
Sure, what is your question?

I suspect it was an implied 'do all my work for me'.

--
Gail Shaw
SQL Server MVP

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 08:18:44

quote:
Originally posted by GilaMonster

If I do your homework, do I get the qualification?

--
Gail Shaw
SQL Server MVP

Is there any qualification in relation to SQL that is not already your's?

No, you're never too old to Yak'n'Roll if you're too young to die.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-12 : 08:25:37

quote:
Originally posted by webfred

quote:
Originally posted by GilaMonster

If I do your homework, do I get the qualification?

--
Gail Shaw
SQL Server MVP

Is there any qualification in relation to SQL that is not already your's?

MCM

and most of the BI certs. Need um, want um, don't really have time to study at the moment.

--
Gail Shaw
SQL Server MVP

Surrey_Uni_Hackers
Starting Member

1 Post

Posted - 2010-05-18 : 09:28:37

Dear Student,
It was unpleasant to discover that a student of our University is trying to avoid doing his coursework. This action is opposite with the legislation of the University. We know who you are, and you will be reported to the department.
Surrey Uni Plagiarism Detection Team

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-05-18 : 09:32:15

quote:
Originally posted by GilaMonster

If I do your homework, do I get the qualification?

--
Gail Shaw
SQL Server MVP

you seem to be repeating yourself today Gail

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-18 : 09:34:25

quote:
Originally posted by DonAtWork

quote:
Originally posted by GilaMonster

If I do your homework, do I get the qualification?

--
Gail Shaw
SQL Server MVP

you seem to be repeating yourself today Gail

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

That was actually a week back

. The Uni Plagiarism Detection Team (?) re-opened the thread.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-05-18 : 11:27:55

i see. Well, +1 for me then! (2 actually)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources