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
Showing posts with label programmer. Show all posts
Showing posts with label programmer. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
creating users in sql 2000
i recently noticed that the programmer has been adding the users of our applications to the actual users under our database. When i inquired as to why, he said it was the only way they could access the data. I also noticed that the users all have been set to owners. I spoke with another friend and he said as far as he knew there wasn't any real reason why the application users would have to be set up in the actual database but just in a table/field, but that they really don't need to be owners. This app is designed to have thousands and thousands of users, and it seem like this is going to be difficult to manage.
Any thoughts?There seems to many ways you can implement security, Windows Authentication, SQL Server mode, Application Roles, individual user accounts, single application accounts.
To shorten the response I will just say how we implemented security. Like you we have about 2000 users that access the database via one of many applications. We elected to create a single user account for each application (example: Payroll - INetPayroll). Each enduser would enter there logon information into a logon screen (.ASP) and be validated against a security table that listed every user and there password. Once validated the application would then connect to the database using it's own SQL Server account (INetPayroll). This way we limited the number of user accounts that had to be maintained in the database, security would only be granted to a few user accounts, we implemented GROUPs and added users (INetPayroll) to the GROUPs, there by only needing to grant permission to the GROUP only. We couldn't use NT authentication because we deal with NOVELL users and all applications are via Intranet and connections are made with the IIS user account.
By making every user the owner you have opened up a can of worms. This is the lazy mans way of doing it. If a user has MS Access on their desktop or MS Query, they could connect to the database directly and access to database. As owners they can now modify any data directly, drop create objects, not good.
In our implementation user don't really have a SQL Server account so they can not access the database directly.
You may want to look at Application Roles.|||and as a follow on to achorozy, I NEVER allow programmers direct access to production dbs & tables. If they can't get to what they want via an application & stored procedures they are out of luck. That always has been a tuff argument to make to managment and there will always be an exception but this is one area I won't back down on. Security can never be taken to seriously.|||I don't want to side track here, but Paul brings up a good point. In our environment all data access, manipulation is done through stored procedures and not direct SQL statements. Were I work only the DBAs create the stored procedures and database objects, the developers write the VB/ASP code, design the screens, etc.
I wish I could be a little survey here and find out how many people that subscribe to this forum are in fact DBAs at there site or have a DBA at there site. Are any of you out there developers/programmers thrown into the DBA role? Do you want to take on the responsiblities of a DBA or are you just doing enough to get by, to get the system up and running?
Just some thought I have.sql
Any thoughts?There seems to many ways you can implement security, Windows Authentication, SQL Server mode, Application Roles, individual user accounts, single application accounts.
To shorten the response I will just say how we implemented security. Like you we have about 2000 users that access the database via one of many applications. We elected to create a single user account for each application (example: Payroll - INetPayroll). Each enduser would enter there logon information into a logon screen (.ASP) and be validated against a security table that listed every user and there password. Once validated the application would then connect to the database using it's own SQL Server account (INetPayroll). This way we limited the number of user accounts that had to be maintained in the database, security would only be granted to a few user accounts, we implemented GROUPs and added users (INetPayroll) to the GROUPs, there by only needing to grant permission to the GROUP only. We couldn't use NT authentication because we deal with NOVELL users and all applications are via Intranet and connections are made with the IIS user account.
By making every user the owner you have opened up a can of worms. This is the lazy mans way of doing it. If a user has MS Access on their desktop or MS Query, they could connect to the database directly and access to database. As owners they can now modify any data directly, drop create objects, not good.
In our implementation user don't really have a SQL Server account so they can not access the database directly.
You may want to look at Application Roles.|||and as a follow on to achorozy, I NEVER allow programmers direct access to production dbs & tables. If they can't get to what they want via an application & stored procedures they are out of luck. That always has been a tuff argument to make to managment and there will always be an exception but this is one area I won't back down on. Security can never be taken to seriously.|||I don't want to side track here, but Paul brings up a good point. In our environment all data access, manipulation is done through stored procedures and not direct SQL statements. Were I work only the DBAs create the stored procedures and database objects, the developers write the VB/ASP code, design the screens, etc.
I wish I could be a little survey here and find out how many people that subscribe to this forum are in fact DBAs at there site or have a DBA at there site. Are any of you out there developers/programmers thrown into the DBA role? Do you want to take on the responsiblities of a DBA or are you just doing enough to get by, to get the system up and running?
Just some thought I have.sql
Tuesday, February 14, 2012
Creating DB schema from a script or stored procedure?
Hi there,
I am a fairly experienced programmer, but new to SQL Server - I understand basic DB theory well enough, but don't have much practical experience with using SQL Server.
I'm working on a project at the moment, where, as part of the spec, users can create 'systems' in the database. For example, in a parts database for a pumping station, there may be 10,000 parts. Rather than have one huge database for, say, 10 pumping stations, we would prefer to have 10 smaller databases, each dedicated to its own system. The schemas would be identical.
I think one approach to this would be have an empty database in SQL server (with the correct tables/schemas/relationships etc) and then copy that within SQL server, with a new name (the system name), probably using a stored procedure.
My question: Is this possible, is there already a stored procedure in SQL Server (2000) to do this, or do I have to write one? Writing a SP to physically create the database from scratch would be a nightmare, I'm hoping there is a simple 'copy_db to new_db' type stored procedure. Maybe there is a program can read a DB and create a script to re-create the DB under a new name?
Any information greatly appreciated.
Mark Wills.In my experience the multi-database approach is never worth the considerable administrative requirements to support it. Unless you anticipate the database size to increase way beyond the ability of a single database server to support it, you should serious rethink your plan and consider adding an additional field or table to your schema to make it scalable.|||I am in agreement with Blindman but if you persist with this scheme you can create your initial database by scripting it out or using the Enterprise Mangeler (whoops) Manager (EM).
Once you have your template defined, in the EM you can right click on your database and go to ALL TASKS and choose Generate SQL Scripts. You can make the EM script out all or some of your objects. then I guess you can wrap all of that code in a SP.
There is another way. If all of your DBs are going to have the same structure and objects on this instance of SQL server I believe you can alter your model db and everytime you issue CREATE DATABASE, you should get all of the objects you created in model. I have never used this second method so I would read up on it first before trying.|||Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.|||Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.
1.5 TERA BYTES? :eek: Holy cow, thats one hell of an MP3 collection :)
Ok guys, thanks for the information. I'll do it like you say. As I say, whilst I'm vaguely familar with DB theory, SQL Server is a whole new ballgame for me - it's a bit daunting, so I'm sure I'll be back with many questions! Thanks for taking the time to reply.
Mark.
I am a fairly experienced programmer, but new to SQL Server - I understand basic DB theory well enough, but don't have much practical experience with using SQL Server.
I'm working on a project at the moment, where, as part of the spec, users can create 'systems' in the database. For example, in a parts database for a pumping station, there may be 10,000 parts. Rather than have one huge database for, say, 10 pumping stations, we would prefer to have 10 smaller databases, each dedicated to its own system. The schemas would be identical.
I think one approach to this would be have an empty database in SQL server (with the correct tables/schemas/relationships etc) and then copy that within SQL server, with a new name (the system name), probably using a stored procedure.
My question: Is this possible, is there already a stored procedure in SQL Server (2000) to do this, or do I have to write one? Writing a SP to physically create the database from scratch would be a nightmare, I'm hoping there is a simple 'copy_db to new_db' type stored procedure. Maybe there is a program can read a DB and create a script to re-create the DB under a new name?
Any information greatly appreciated.
Mark Wills.In my experience the multi-database approach is never worth the considerable administrative requirements to support it. Unless you anticipate the database size to increase way beyond the ability of a single database server to support it, you should serious rethink your plan and consider adding an additional field or table to your schema to make it scalable.|||I am in agreement with Blindman but if you persist with this scheme you can create your initial database by scripting it out or using the Enterprise Mangeler (whoops) Manager (EM).
Once you have your template defined, in the EM you can right click on your database and go to ALL TASKS and choose Generate SQL Scripts. You can make the EM script out all or some of your objects. then I guess you can wrap all of that code in a SP.
There is another way. If all of your DBs are going to have the same structure and objects on this instance of SQL server I believe you can alter your model db and everytime you issue CREATE DATABASE, you should get all of the objects you created in model. I have never used this second method so I would read up on it first before trying.|||Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.|||Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.
1.5 TERA BYTES? :eek: Holy cow, thats one hell of an MP3 collection :)
Ok guys, thanks for the information. I'll do it like you say. As I say, whilst I'm vaguely familar with DB theory, SQL Server is a whole new ballgame for me - it's a bit daunting, so I'm sure I'll be back with many questions! Thanks for taking the time to reply.
Mark.
Subscribe to:
Posts (Atom)