SQL Alternative to cursor: lara169

I'm so sorry but I am not be able to see the aforementioned attachments
"lara169" wrote:

> Hi,
> Pls see the attachments.
> This Question was asked in one of the top indian universities(I've copied
it
> exactly the same way as it was on the Question paper). I got this from one
> of my friends.
> Can any one help me in designing the database in SQL server 2000 and findi
ng
> the payout for each ids?(the second part)
> Regads Lara................
>
>
>
>This was the exact Question...
Based on this Can u suggest the best way of DB design.. I think its just a
binary tree implementation
regards Lara
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:9980E6CD-CEFC-4A72-8E97-CC911E396C51@.microsoft.com...
> I'm so sorry but I am not be able to see the aforementioned attachments
> "lara169" wrote:
>
copied it
one
finding|||lara169 skrev:

> This was the exact Question...
> Based on this Can u suggest the best way of DB design.. I think its just a
> binary tree implementation
>
> regards Lara
>
No we cannot suggest much, as you seem to have some attachments
somewhere that are not visible in your posts (using for example Google
or an ordinary news reader - correct me if I'm wrong).
Either post a(n) URL for that/those attachment(s), or explain better in
a posting (text only).
/impslayer, aka Birger Johansson|||It is very hard to debug code when you do not let us see it. No
attachments, no URLs, real code please.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
If this is a tree problem, you can also get a copy of TREES &
HIERARCHIES IN SQL. Binary trees are pretty easy to model.

"lara169" <lara169@.gmail.com> wrote in message
news:%23Ir3arOEGHA.4000@.TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Sorry for posting the question again. Since I send the question as
> attachments, some people were unable to read it. Also i missed to attach
> the
> latest word file of the Question
>
> This Question was asked in one of the top indian universities(I've copied
> itexactly the same way as it was on the Question paper except the initial
> first part, which i got from my friend.).It seems to be an interesting
> Question.
>
> Can any one help me in designing the database in SQL server 2000 and
> finding
> the payout for each ids?(the second part)
> Regads Lara................
>
You still included attachments and many people can't or won't download them
or your post.
Posting exam questions verbatim is at the very least likely to be a breach
of copyright. Also, I for one don't want to help with what may be cheating,
especially since you haven't posted any evidence of your own attempts to
answer the problem. Why not make a start, then post a version of the problem
with some DDL, sample data and show us how far you have got.
David Portas
SQL Server MVP
--Hi,
Actually I typed question in a word doc and tried to attatch it
yesterday.But the problem with that was, i didn't attach the latest copy.
that's why the forst session was missing. Today what i did was i copied the
contents and paste it here.. and posted. At that moment from my outlook, it
was showing all the images, now only i noticed that, these images are
attached.
I'll send the post again with the design i am having...
Question...
This Question is related to Network marketing of the company named 'XYZ'.
With a purchase of any Product from XYZ , you will become an Independent
Distributor.
The conditions to become the IDs are
1. There should be a sponsor for each Independent distributor (Id).
2. A ID can have only two children (Like a binary tree).
3. and each ID except the ROOT ID, has a parent.
Usually the sponsor initiates the registration for its child IDs, So he can
deside which id will be the parent for this new IDs.
Once an ID is successfully registered, he/she can buy some business voulmes
( either PBV/BV/GBV). The different plans available are given below...
Binary Business plan. (W

ly Matching Bonus)
Joining starts from 50 PBV.
50 PBV -WITH SINLGE ID -WEEKLY SEALING - 40000/-
75 PBV -WITH SINLE ID -WEEKLY SEALING - 50000/-
100 PBV -WITH SINGLE ID -WEEKLY SELING - 70000/-
INAGURAL / PRELAUNCHING OFFER ONLY FOR 3 MONTHS from the date of
Commencement.
ONE PERSON CAN JOIN WITH
1O PBV -WITH SINGLE JOINING WEEKLY SEALING - 30000/-
20 PBV - WITH SINGLE JOINING WEEKLY SEALING - 30000/-
30 PBV - WITH SINGLE JOINING WEEKLY SEALING - 30000/-
40 PBV - WITH SINGLE JOINING WEEKLY SEALING - 30000/-
ANY ONE CAN UPGRADE THEIR SEELING LIMIT TO 70000/- PER WEEK BY PUCHASING A
PRODUCT TO REACH 100PBV,(Personnel Business Volume).
( Eg: if one man joined with 10PBV he can buy 90PBV product at a time and
upgrade to 100PBV. And sealing limit to 70000/- per w

.
PAY OUT OR MATCHING BONUS
1. Binary Income:-
first set matching bonus:-
Binary income starts Mr. "X" generate minimum two sales (one on your left
and one on your right side) The Ratio is 100BV :100BV, followed by a tail of
100BV.,
Or by the accumulated GBV, L: R ratio for the first payout is 100BV:200BV or
200BV:100BV. There by giving your First matching bonus. 1000/-
For the First Payout Mr. "X" will get an additional benefit called direct
referral commission (this is only for the first payout) from the first level
of Mr. "X" the BV will be multiplied by Rs.2.50/-
100BV
e.g. 1. See the following binary tree structure
(X)
(A) (B)
(C)
All having 100 BVs each
Mr. "X" will get binary income Rs. 1000/-
And Referral commission 100BV+100BV
100BV 100BV
= 200BV x Rs.2.50 = 500/-
i.e. the first payout of Mr. X will be Rs. 1500/-
100BV
The Second set onwards matching bonus will be given for minimum matching of
50GBV:50GBV
50GBV:50GBV =Re.500/-
75GBV:75GBV =Rs.750/-
100GBV:100GBV =Re.1000/-
If 10GBV:10GBV=100/-
20 GBV: 20GBV=200/-
The payment will be released only when the min matching bonus amount become
Rs.500/- . (Up to that the amount will be accumulated.)
PBV - Personnel Business Volume.
BV - Business Volume
GBV - Group Business Volume
W

ly Matching Bonus - statement as per the e.g. 1.
Total GBV L : R = 200GBV : 100GBV
Matching GBV L:R =100GBV : 100GBV
Cary forwarded BV L:R = 100GBV : 0 GBV
Matching Bonus Rs. 1000/-
Direct Referral Bonus from the first level
Referral GBV = 100+100= 200GBV
Referral Bonus = 200x Rs .2.50/- = 500/-
Total Pay out for this w

= 1000+500=1500/-
Deductions Tds = 5.1 %
Charity = 1.0 %
Handling Charges = 1.0 %
Deduction for repurchase (RSV) =10.0%
Total Deduction = 17.1%

I. SPONSORSHIP BONUS.
(SB)(spill ) ( Bi-Monthly)
A direct Sale other than the initial two direct sales in the first level and
second level is called a spill and it fetches you Rs.2.50/- of every sale
of 1GBV in its business group (or down line) there after till unlimited
depth with out balancing. If a person is not qualifies his first matching
Bonus is not eligible for Sponsorship bonus (SB).
The CAP is Rs.2,00,000/- per month maximum.
Sponsorship bonus will get only one person in the group who is the last
direct up Spill ID. Sponsor.
(X)
(A)
(B)
(C) (D)
(E) (F)
(G) (H)
(I)
I think the pic is not good
This is what is there in the picture
X is having Children A, B
A is having children C, D
D E, F
E G,H
H has only one child I
If Mr. "X", is directly sponsoring a Spill Mr. "D" under Mr. "A"
. , Mr . "X" will Get a SB @. Rs. 2.50/-each 1 GBV under Mr. "D". From
the group of "E" and From the Group of "F".
If Mr. "A" is directly sponsoring Mr. "E" to the down line of Mr. "D".
Mr. A is going to get a SB @. Rs.2.50/- each 1 GBV from All the Group of Mr.
E's Both left and Right. i.e. from the group of Mr. "G" and Mr. "H".
And Mr. "X" will not get Any SB from the group of Mr. E. But Mr. "X" will
get SB from Mr. F. Down line ( Right side ) of Mr. "D".
This SB will be given from unlimited depth and a Maximum CAP of
Rs.2,00,000/- per Month.
________________________________________
____________________
This is my database design..
CREATE TABLE [dbo].[User](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[FullName] [varchar](96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BVs] [int] NULL CONSTRAINT [DF_User_BVs] DEFAULT
((0)),--stores the total no of BVs
[IsLeaf] [bit] NOT NULL CONSTRAINT [DF_User_IsLeaf] DEFAULT ((0)),
CONSTRAINT [aaaaaUser_PK] PRIMARY KEY CLUSTERED
( [UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserBVs](
[UserID] [int] NOT NULL,
[BVs] [int] NOT NULL,
[AddedOn] [smalldatetime] NOT NULL CONSTRAINT [DF_UserBVs_AddedOn]
DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BTree](
[ParentID] [int] NOT NULL,
[SiblingID] [int] NOT NULL,
[ChildPosition] [bit] NULL CONSTRAINT [DF_BTree_ChildPosition]
DEFAULT (0),
[SponsorID] [int] NULL
CONSTRAINT [PK_BTree] PRIMARY KEY CLUSTERED
(
[ParentID] ASC,
[SiblingID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Here is my logic
Whenever a user is added an entry is made to User table and BTree Table (
BTree table is used to store the table structure); and whever he purchases
some BVs, the User.Bvs field is updated and also a new entry is entered on
'UserBvs' table.
Can anyone suggests the cons of this design related to the Question given
above .........|||Hi,
Actually I typed question in a word doc and tried to attatch it
yesterday.But the problem with that was, i didn't attach the latest copy.
that's why the forst session was missing. Today what i did was i copied the
contents and paste it here.. and posted. At that moment from my outlook, it
was showing all the images, now only i noticed that, these images are
attached.
I'll send the post again with the design i am having...
Question...
This Question is related to Network marketing of the company named 'XYZ'.
With a purchase of any Product from XYZ , you will become an Independent
Distributor.
The conditions to become the IDs are
1. There should be a sponsor for each Independent distributor (Id).
2. A ID can have only two children (Like a binary tree).
3. and each ID except the ROOT ID, has a parent.
Usually the sponsor initiates the registration for its child IDs, So he can
deside which id will be the parent for this new IDs.
Once an ID is successfully registered, he/she can buy some business voulmes
( either PBV/BV/GBV). The different plans available are given below...
Binary Business plan. (W

Monday, March 19, 2012

An interesting Question on DBDesign

An interesting Question on DBDesign

SQL Alternative to cursor

Blog Archive

About Me