Author |
Topic |
MrMokete
Starting Member
6 Posts |
Posted - 2013-01-11 : 03:42:20
|
i want to create a database with six related tables1.Tests2.Machines3.Facilities4.Reagents5.Statistics6.TestingMachines is linked to Tests through types of tests that can run on that machine. it is also linked to reagents through types of reagents that can used on that machine.Tests is linked to reagents through types of reagents that has to be used for that test.simply linked to statistics through the fact that we need to know how many tests have been carried out in a certain period.Facilities linked to machines because each facility has machines installed in that facility,it is also related to the Statistics through the fact that we have to know the number of tests performed during a certain time,also related to Testing because we have to know the types of testing area a certain facility has.please help with the design and sample data insertion query.BMokete |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 03:44:05
|
seems like an assignmentRead about CREATE DATABASE,CREATE TABLE statements in books onlinefor relationships you need to use FOREIGN KEY constraintsStart with logical model and then work on scripts------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
MrMokete
Starting Member
6 Posts |
Posted - 2013-01-11 : 03:50:17
|
drop database quantification/* Install quantification database */gocreate database quantificationgocheckpointgo use quantificationgo/* -- Creating Objects for quantification */raiserror ('Creating Testing Area...',0,1)create table testing([testing_id] int identity(10,1),[testing_name] varchar(100),[machine_id] varchar(100),constraint pktesting_id primary key clustered (testing_id))goraiserror ('Creating Reagents...',0,1)create table reagents([reagent_id] int identity(30,5),[reagent_code] varchar(20),[reagent_name] varchar(100),[pack_size] varchar(30),[cost] float,[test_id] int,[qty_test] int,[qty_day]int,[qty_control] int,constraint pkreagent_id primary key clustered (reagent_id))goraiserror ('Creating Machines...',0,1)create table machines([machine_id] int identity(20,3),[machine_name] varchar(100),[serial_no] varchar(20),[date_of_setup] datetime,[last_service] datetime,[next_service] datetime,[reagent_id] int,[test_id] int,constraint pkmachine_id primary key clustered (machine_id))goraiserror ('Creating Facilities...',0,1)create table facility([facility_id] int identity(40,2),[facility_name] varchar(100),[testing_id] int,[machine_id] int,constraint pkfacility_id primary key clustered (facility_id))go raiserror ('Creating Tests...',0,1)create table tests([test_id] int identity(50,6),[test_type] varchar(100),[machine_id] int,[reagent_id] int,[statistics_id] int,constraint pktest_id primary key clustered (test_id))goraiserror ('Creating Statistics...',0,1)create table statistic([statistics_id] int identity(60,8),[test_id] int,[facility_id] int,[statistics_year] datetime,[statistics_month] datetime,[number_of_valid_tests] int,[number_of_invalid_tests] int,[number_of_controls] int,[total_number_of_tests] int,constraint pkstatistics_id primary key clustered (statistics_id))go/* -- Creating foreign key constraints for quantification */raiserror ('Creating foreign key constraints...',0,1)alter table machinesadd constraint reagent_id foreign key (reagent_id) references reagents (reagent_id);alter table testsadd constraint machine_id foreign key (machine_id) references machines (machine_id);alter table reagentsadd constraint test_id foreign key (test_id) references tests (test_id);alter table testsadd constraint statistics_id foreign key (statistics_id) references statistic (statistics_id);alter table facilityadd constraint testing_id foreign key (testing_id) references testing (testing_id);alter table statisticadd constraint facility_id foreign key (facility_id) references facility (facility_id);what do you think of this script looking at the Post?? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 03:54:10
|
whats the purpose of those raiserror statements in between? why do you need to raise messages as an error? wont select be enough? also you may give some descriptive names for those fk constraints------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
MrMokete
Starting Member
6 Posts |
Posted - 2013-01-11 : 03:57:28
|
the raise error just show a message in the output window whether a table has been created or not..my problem is how do i go about inserting data into the tables considering the foreign keys involved.. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-11 : 03:58:15
|
>> Machines is linked to Tests through types of tests that can run on that machineThat implies that there are a number of tests that can be run on a machine and also a number of machines that can run a test.Means the link cannot be just an attribute on machine or test - it needs a conjoin tablecreate table Test_Machine(test_idmachine_id)pk both columnsfk for each to the relevent table.Same applies to reagents and others.Also you have artificial keys for all the tables and are using that for referential integrity (ok but some would disagree violently) - you probably also need a unique index on a natural key - machine name and probably serial no would probably both be unique.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 04:00:27
|
quote: Originally posted by MrMokete the raise error just show a message in the output window whether a table has been created or not..my problem is how do i go about inserting data into the tables considering the foreign keys involved..
for that use select or printfor inserting data, start with master tables (parent) and then insert into child tables with referenceids generated in parentWill you be doing row by row or bulk insertion?If former, use SCOPE_IDENTITY to retrieve the generated idif latter use OUTPUT clause------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
MrMokete
Starting Member
6 Posts |
Posted - 2013-01-11 : 04:04:29
|
thank you guys for your input..any chance of you giving a sample script of how to go about using SCOPE_IDENTITY to insert data because i will be inserting data row by wor. |
|
|
MrMokete
Starting Member
6 Posts |
Posted - 2013-01-11 : 04:06:09
|
if by any chance please help me by improving the script i posted..thank you for being so helpful.BMokete |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 04:08:30
|
quote: Originally posted by MrMokete thank you guys for your input..any chance of you giving a sample script of how to go about using SCOPE_IDENTITY to insert data because i will be inserting data row by wor.
something likeDECLARE @ParentID intINSERT INTO Parent (Col1,Col2,.. all except identity column) VALUES(val1,val2,...)SET @ParentID = SCOPE_IDENTITY()INSERT INTO Child(ParentID,...)VALUES(@ParentID,...) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
MrMokete
Starting Member
6 Posts |
Posted - 2013-01-11 : 04:18:29
|
thank you visakh16...according to you what are your thoughts about the script i posted??where do i have to really improve to make it better..this my first script in database design. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 04:20:22
|
quote: Originally posted by MrMokete thank you visakh16...according to you what are your thoughts about the script i posted??where do i have to really improve to make it better..this my first script in database design.
i suggested alreadysee post on 01/11/2013 : 03:54:10------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-11 : 04:46:24
|
Did you read my post?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-11 : 15:00:42
|
quote: Originally posted by visakh16 whats the purpose of those raiserror statements in between? why do you need to raise messages as an error? wont select be enough? also you may give some descriptive names for those fk constraints------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Raising an eror with severity of less than 11 is used for returning messages. I can't say I do it often, but I've seen it used reasonably commonly.I know that Visakh already knows this, but incase other don't.. from BOL:quote: RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the Transact-SQL PRINT statement does not. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.
|
|
|
|