Creating/Normalizing a database

Sorry if this is the incorrect area to ask this question [for my solution].
I'm NOT a SQL programmer but a pretty good layman.
I'd like to create a test database. I've been wondering if my thinking is
correct in normalizing it.
Table Questions
QID [pk]
QText
Table Answers
AnsID [pk]
QID [fk]
Ans
Table CorrectAnswers
AnsID [pk]
QID [fk]
-- OR --
One [flat] table for it all. Obviously there will be nulls here, especially
for T/F.
Table Test
QID [pk]
QText
Answer0
Answer1
Answer2
Answer3
Answer4
Answer5
Answer6
Answer7
Answer8
Answer9
AnswerCorrect (should this contain the text of the correct answer or the
Field Name of the correct answer?)
Now... I have 100 questions and want to extract 25% randomly. I need a
randomizer for a for loop to not ONLY randomize the questions, but randomize
the non T/F answers if it is multiple choice. (which begs a question, should
I have a field that specifies it as a T/F [Yes/No] answer or multiple
choice?)
What would my SQL statement look like?
Anybody who can answer this will ultimately write my entire logic for my
task and I thank you.
Jon| I'd like to create a test database. I've been wondering if my thinking is
| correct in normalizing it.
|
| Table Questions
| QID [pk]
| QText
|
| Table Answers
| AnsID [pk]
| QID [fk]
| Ans
|
| Table CorrectAnswers
| AnsID [pk]
| QID [fk]
|
| -- OR --
| One [flat] table for it all. Obviously there will be nulls here,
especially
| for T/F.
|
| Table Test
| QID [pk]
| QText
| Answer0
| Answer1
| Answer2
| Answer3
| Answer4
| Answer5
| Answer6
| Answer7
| Answer8
| Answer9
| AnswerCorrect (should this contain the text of the correct answer or the
| Field Name of the correct answer?)
|
| Now... I have 100 questions and want to extract 25% randomly. I need a
| randomizer for a for loop to not ONLY randomize the questions, but
randomize
| the non T/F answers if it is multiple choice. (which begs a question,
should
| I have a field that specifies it as a T/F [Yes/No] answer or multiple
| choice?)
|
| What would my SQL statement look like?
|
| Anybody who can answer this will ultimately write my entire logic for my
| task and I thank you.
--
Hi Jon,
If you have one and only one correct answer for each question, then I would
design your tables as follows:
Tbl_Questions
Question_no (primary key)
Question_Text
Question_CorrectAnswer
Tbl_PossibleAnswers
Question_no (foreign key)
PossibleAnswer_no
PossibleAnswer_Description
There will be a one-to-many relationship between tbl_Questions and
tbl_PossibleAnswers.
Hope this helps,
--
Eric Cárdenas
SQL Server support

Creating stored procs with MSforeachdb

I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.
Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan
|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.
|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.
|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve
|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.
|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve
|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.
|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan
|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve

Creating stored procs with MSforeachdb

I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - (
1;Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve

Creating stored procs with MSforeachdb

I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 = 'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve