Normalization

I am a bit confused with normalization, hopefully someone here can help me out.
I got the following unnormalized list:

UNNORMALIZED
R1 = (ward_no, ward_name, patient_no, first_name, last_name, drug_card_no (drug_code, drug_name, date_dispensed, dosage))
Now i want to normalize it to the 3NF.
This is what i have tried:

1NF
R11 = (ward_no, ward_name (patient_no, first_name, last_name, drug_card_no))
R12 = (drug_code, drug_name, date_dispensed, dosage)

2NF
R111 = (ward_no, ward_name)
R112 = (ward_no, patient_no, first_name, last_name, drug_card_no)
R12 = (drug_code, drug_name, date_dispensed, dosage)

3NF
R111 = (ward_no, ward_name)
R1121 = (ward_no, patient_no, first_name)
R1122 = (first_name, last_name, drug_card_no)
R12 = (drug_code, drug_name, date_dispensed, dosage)

But now am not sure if its right. I think i may be missing somethings. could someone advise?
Thanks1NF means no repeating groups

it looks like R11 repeats patients in a ward, so that fails 1NF

what are you using as your reference for normalization? a textbook or the internet? if a textbook, please give its title and author, if the internet, please give urls of the sites you're using|||Well im just using the lecture slide note we got in class.
now for that 1NF i know what you mean so i was thinking like this:
1NF
R11 = (ward_no, ward_name patient_no, first_name, last_name, drug_card_no)
R12 = (drug_code, drug_name, date_dispensed, dosage)

without that new group? anything else|||here are two good resources:
Relational Data Architecture (PDF) (http://www.oreilly.com/catalog/javadtabp/chapter/ch02.pdf)
3 Normal Forms Database Tutorial (http://www.phlonx.com/resources/nf3/)|||i understand the rules but then once i try doin on i jus get confused i u can see in the above.|||Can you give us a sample of your data. Then we can show you the transition to 1NF, 2NF, 3NF. The problem is that you have a represented your initial data in a column related format so we can't actually see where data repeats itself.

The two rules to follow for 1NF are :
1. A row of data cannot contain repeating groups of similar data (atomicity); and
2. Each row of data must have a unique identifier (or Primary Key).

You initial line actually looks like 1NF already to me, however it doesn't identify primary keys.|||ummm well this is what i was given:

R1 = (ward_no, ward_name, patient_no, first_name, last_name, drug_card_no (drug_code, drug_name, date_dispensed, dosage))

thats all i can give u sorry.

Normalisation vs If it works just do it!

Hi All,

As an accomplished web devver of many years using ASP and ASP.NET in conjunction with Access and SQL Server, I am a bit pedantic on the rules of good data structures.

Specifically the two main rules of data redundancy and normalisation.

The latter dictates at the lowest level that a data table should NOT contain a field that can be gleaned from one or a combination of others.

I have a problem with this now, I am building a betting system which will take the odds given, plus the stake placed and calculate the winnings or losses accordingly.

There is an added complication in that not all profit is calculated the same way, as a horse can also be 'placed' which does the same calculation as for profit, but then quarters it, so one single select statement won't do.

I could calculate this at data entry stage on a per entry basis and simply store in a Profit/Loss field and keep the value for each bet, however I know this is not the correct thing to do!

My other alternative [and the correct method] is to do this calculation at data request time, but that would involve the use of a cursor or loop in the SP.

I am aware of the huge resources a cursor can consume and I am not sure which is worse, using a cursor or ignoring the normalisation procedures.

So the question is this, what would you do here?

Since I may not be the same SQL Server expert as I am a programmer, is there an alternative way of reading all the bets and doing these calcs on SQL server and bang them back to ASP as a self contained recordset with all the profit/losses calculated for each bet?

Each bet as a unique EntryID and there is a field called Result which stores 'Win, Place or Loss' accordingly.

Thanks in advance of any help/opinions.

:)I'm a bit confused... When I use the term "place" to describe a horse racing bet, I mean that I expect (and am wagering that) the horse will finish second. While it is unusual to do, you can "box" a single horse, meaning that you expect them to place in the top 3, but you retrieve your original wager plus twenty percent of what the return would have been for a correct bet (win, place, or show), which sounds something like your description of a "place" bet.

If you can describe what you really want, I'm sure that someone here on the forum can show you how to code it!

-PatP|||Hi,

Thanks for the response, the actual calculations is not the issue e.g. the odds for a place bet, that is already known.

The problem concerns how to read through the table of placed bets while calculating the profit/loss as we iterate through each record.

The arithmetic is not the issue here, its the method of retrieval so that each record will have a field containing a profit or loss amount, put there by SQL as it reads through each item.

Thanks.|||Performance is the key in my work. If running the cursor is going to slow down the operation then having a little redundency isn't bad.|||My thought would be to do an UPDATE using the CASE to control which computation you use (main or place). Clean, fast, no cursor... What more could you ask?

-PatP

Noob having troble with group by etc.

Well i guess it is a bit more than just group by but here we go...

General description :( background info on the problem; may or may not be needed)

I am trying to get a list of active card holders for a particular month. A card holder is considered active if their status was active any time during that month. I have a card holder status in my CardHolder table but that will only give me their current status. I also have a CardHolderHistory table that saves any changes to a card holder including their status. Using the CardHolderHistory table i can find out who was active for any month in question by using the following algorithm:

A card holder is active if they meet one or both of the following conditions;

1) The last entry before the month in question has them as active in CardHolderHistory or

2) They were set to active any time during the month in question

The problem:

I create a temporary table and copy the card holders into the table with separate select into statements for each of the above conditions. For the first condition, I first gather all the CardHolderHistory entries before the month in question with the following select

select EmployerName, cardholderhistory.FirstName, cardholderhistory.Initial, cardholderhistory.LastName, ChangeDate, CardHolderHistory.CardState, FKEmployerID

into #MonthlyCards from cardholderhistory left join Employer on PKEmployerID = cardholderhistory.FKEmployerID

where changedate<@.datespecified

This gives me ALL of the CardHolderHistory before the month in question (@.dateSpecified).

It would look something like this

EmployerName First Initial Last Status ChangeDate EmployerID

-

.

.

.

Some Fake Company John P Doe A 2006-05-02 11:34:26.360 109

Some Fake Company John P Doe A 2006-03-28 11:14:10.520 109

Some Fake Company Jane T Doe S 2006-05-30 15:34:14.900 109

Some Fake Company Jane T Doe A 2006-03-28 12:20:03.670 109

Some Fake Company Jane T Doe A 2006-03-01 10:12:45.320 109

.

. (S = suspended; A = Active)

.

Now I need to make sure people are only listed once and remove everyone whose status = suspended for their last change date. That is my problem; I can’t figure out how to remove these people. The next step i took was to do a group by with EmployerName, FirstName, Initial, LastName, Status, Max(ChangeDate), FKEmployerID. This removes everyone listed multiple times except if their status changes. After the group by the list would be

EmployerName First Initial Last Status ChangeDate EmployerID

-

Some Fake Company John P Doe A 2006-05-02 11:34:26.360 109

Some Fake Company Jane T Doe S 2006-05-30 15:34:14.900 109

Some Fake Company Jane T Doe A 2006-03-28 12:20:03.670 109

Now I need to delete Jane completely, since her latest status is S. I could delete where Status = ‘S’ but that leaves her as active from 3/28. I can’t figure out how to completely remove Jane. The rest I can figure out I think. Please help me get rid of Jane for good, I don’t like her.

Thanks for any help,

Rico

Instead of grabbing too many records, how about grabbing only the records you want. In this case, construct a query that uses a full outer join of the two conditions you have stated. Now you will have only one record for the active members and you can sort out the values of the columns.

Hopefully, this code gives you something to work with. I have assumed that there is a CardHolderID that is unique to each CardHolder record.

Select

From(

select c1.CardHolderID,

c1.FirstName,

c1.Initial,

c1.LastName,

c1.ChangeDate,

C1.CardState,

FKEmployerID

From cardholderhistory c1

Where ChangeDate = (

Select Top 1

changeDate

From cardholderhistory c2

Where c1.CardHolderID = c2.CardHolderID

And c2.ChangeDate < @.DateSpecified

Order by changeDate desc )

And CardState = 'A'

) t1

Full Outer Join

(

select c3.CardHolderID,

c3.FirstName,

c3.Initial,

c3.LastName,

c3.ChangeDate,

C3.CardState,

FKEmployerID

From cardholderhistory c3

Where ChangeDate between @.monthStart and @.monthEnd

) t2

On t1.CardHolderID = t2.CardHolderID

Join Employer

On t1.FKEmployerID = Employer.PKEmployerID

|||

Anomolus,

Thank you very much for your reply. I have wasted an embarrassing number of hours trying to figure this out but with your help I was able to get what I needed. The code i used was

declare @.dateSpecified datetime
--@.employerID int
declare @.endDate datetime

set @.dateSpecified = '8/1/2006'
set @.endDate = dateadd(m, 1, @.dateSpecified)

select EmployerName, chh.FirstName, chh.Initial, chh.LastName, FKEmployerID
into #MonthlyCards
From CardHolderHistory chh left join
Employer on PKEmployerID = FKemployerID
Where ChangeDate = ( Select Top 1 ChangeDate
From CardHolderHistory chh2
Where chh.PKCardHolderID = chh2.PKCardHolderID
And chh2.ChangeDate < @.DateSpecified
Order by changeDate desc )
And (CardState = 'A' or CardState = '')
Order By EmployerName, LastName, FirstName, Initial

insert into #MonthlyCards([EmployerName],[FirstName],[Initial],[LastName],[FKEmployerID])
(select EmployerName, chh.FirstName, chh.Initial, chh.LastName, FKEmployerID
From CardHolderHistory chh left join
Employer on PKEmployerID = FKemployerID
where (changedate between dateadd(ms, (-5),@.dateSpecified) and @.enddate)
And (CardState = 'A' or CardState = ''))

Select * from #monthlycards
group by EmployerName, FirstName, Initial, LastName, FKEmployerID
--order by pkcardholderid

drop table #MonthlyCards

I was having a problem getting that first condition in the form of a select. I have few follow-up questions, now that i got it to work.

1) Why use the Full outer join? It seems to get a bunch of info that i don't need and has a bunch of nulls. Wouldn't the full outer join join the two conditions into the same record?

2) What if there are too ChangeDates with the same value? Will this cause errors or is it generally acceptable to rely on the millisecond precision of the datetime data type?

3) Is there anything i should do to optimize this code? My CardHolderHistory table has thousands of entries already and it will continue to get bigger.

Again, thanks Anomolus, and thank you to anyone that can help with these questions,

Rico

|||

1) The Full outer join allows you to get the list of customers in a single select. The customers will match at least one of the two conditions and if it matches both, then they are combined into a single record. You will get nulls when the customer only satisfies one condition. It occurs to me that all you really need out of this is the CustomerID. Isn't there a risk of getting old information if you grab the name from history as it could have been what was changed last?

2) This is really a business question since your rule says the last change. If it is possible for two changes to happen at the same time, then you ought to have another field that designates the version order in the history table.

|||

"Isn't there a risk of getting old information if you grab the name from history as it could have been what was changed last?"

Yes there is, thanks for catching that! I thouught that i would save some resources by using one less join but you are right... i had thought of that in my earlier failed attemps but looked over it in this version.

"If it is possible for two changes to happen at the same time"

I dont think this is possible, maybe you could help me find out. The ChangeDate value is filled in automatically (the default value is getdate())when a new CardHolderHistory record is created. It is not changes beyond that. If 2 different administrators used the website and tried to update card holders at the same and my SaveCardHolder stored procedure is called at the same time im guessing this could generate the same ChangeDate. Besides the fact that it is unlikly, can more than one record be saved at the same time? I insert into the CardHolderHistory table from Save CardHolder using transactions (roll back on error otherwise commit) if this makes a difference.

Thank you for your help! You have really saved me!

sql

non-sa users get access denied in OLE DB query

On SQL Server 2005, 64 bit, this query:
SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
MISDB2A.FASMSCAC_STD_CARRIER_CODE')
Gets this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
I works if it is run by the sa login, or by a login that has been granted
the sysadmin Server Role.
How can I permission non-sa logins to access the OLE DB provider?Ad-hoc remote queries are disabled by default in SQL 2005. Check the
"Surface Area Configuration for Features (Ad Hoc Remote Queries) - Database
Engine " topic in BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/fe754945-2cd2-4cc1-8ae1
-4de01907b382.htm).
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:0896B540-3262-42A8-8D4E-97D900C35A91@.microsoft.com...
> On SQL Server 2005, 64 bit, this query:
> SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
> SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
> MISDB2A.FASMSCAC_STD_CARRIER_CODE')
> Gets this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access
> this provider through a linked server.
> I works if it is run by the sa login, or by a login that has been granted
> the sysadmin Server Role.
> How can I permission non-sa logins to access the OLE DB provider?|||It has been turned on!
With it left off, EVERYBODY gets this mssage:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of
component 'Ad Hoc Distributed Queries' because this component is turned off
as part of the security configuration for this server. A system administrato
r
can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. Fo
r
more information about enabling 'Ad Hoc Distributed Queries', see "Surface
Area Configuration" in SQL Server Books Online.
But turned on, uness you have sysadmin privs, you get this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
So, with ad hoc turned on, how do you permission non-sa logins to access the
OLE DB provider?|||Stu (Stu@.discussions.microsoft.com) writes:
> But turned on, uness you have sysadmin privs, you get this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access this provider through a linked server.
> So, with ad hoc turned on, how do you permission non-sa logins to access
> the OLE DB provider?
Books Online gives us this little tidbit:
OPENROWSET can be used to access remote data from OLE DB data sources
only when the DisallowAdhocAccess registry option is explicitly set to
0 for the specified provider, and the Ad Hoc Distributed Queries
advanced configuration option is enabled. When these options are not
set, the default behavior does not allow for ad hoc access.
i search the registry for DisallowAdhocAccess and I found that under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\MSSQL.1\Providers
there is a whole list of providers. Of these SQLNLCI has DisallowAdhocAccess
set to 0, but the others not. I did a test with a plain-test user, and
it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
did not have DisallowAdhocAccess listed.
So that seems to be the key.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> i search the registry for DisallowAdhocAccess and I found that under
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
> Server\MSSQL.1\Providers
> there is a whole list of providers. Of these SQLNLCI has
> DisallowAdhocAccess
> set to 0, but the others not. I did a test with a plain-test user, and
> it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
> did not have DisallowAdhocAccess listed.
Yes, you nailed it. In BOL, it also stands "This option controls the ability
of non-administrators to run ad hoc queries. Administrators are not affected
by this option." In addition, there is a funny thing: you can set this
option to 1 through SSMS (Server Objects - Linked Servers - Providers -
properties of a specific provider). When you check the checkbox, SSMS
creates the registry key. However, you cannot set it to 0 with SSMS. When
you uncheck the checkbox, SSMS deletes the key. So the only option to set it
to 0 is with registry editor (ok, or with SMO, I guess). Not very
recommendable.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Yes, you nailed it. In BOL, it also stands "This option controls the
> ability of non-administrators to run ad hoc queries. Administrators are
> not affected by this option." In addition, there is a funny thing: you
> can set this option to 1 through SSMS (Server Objects - Linked Servers -
> Providers - properties of a specific provider). When you check the
> checkbox, SSMS creates the registry key. However, you cannot set it to 0
> with SSMS. When you uncheck the checkbox, SSMS deletes the key. So the
> only option to set it to 0 is with registry editor (ok, or with SMO, I
> guess).
Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
In my opinion this is a bug. Someone has apparently not understood how
this provider option works. I've submitted
https://connect.microsoft.com/SQLSe...=2574
94
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm working now.
Thanks guys, for your research and feedback!|||Voted:-)
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98D4B72B433ADYazorman@.127.0.0.1...
> Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
> In my opinion this is a bug. Someone has apparently not understood how
> this provider option works. I've submitted
> https://connect.microsoft.com/SQLSe...=25
7494
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

NON EMPTY issue

Apologies if this is a bit simple but it's doing my head in a bit

Why would the following MDX return 4 correct records

Code Snippet

SELECT { [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

non empty{ DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time Calcs].[YTD],[Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Unit of Measure].&[2] )

however adding another measure returns 50 additional spurious measures, all with blank rows

Code Snippet

SELECT NON EMPTY { [Measures].[Signed Leases], [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Time Calcs].[YTD], [Unit of Measure].&[2] )

The "signed leases" measure was the field added

Presumably you mean that adding the Signed Leases measure returns another 50 spurious rows, not measures? Is Signed Leases a calculated measure, and if so what is its definition? I'm guessing [Time Calcs].[YTD] is another calculated measure (and is probably the culprit) - what is its definition? Have you been using the Non_Empty_Behavior property anywhere?

Chris

|||

Chris,

Yes, you were correct in your assumption. TimeCalcs was OK, it was actually the fact that signed leases was calculated. Adding an iif statement to create a null sorted it