| Author |
 Topic  |
|
|
MrMokete
Starting Member
Botswana
6 Posts |
 Posted - 01/11/2013 : 03:42:20
|
i want to create a database with six related tables
1.Tests
2.Machines
3.Facilities
4.Reagents
5.Statistics
6.Testing
Machines 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
India
47173 Posts |
Posted - 01/11/2013 : 03:44:05
|
seems like an assignment
Read about CREATE DATABASE,CREATE TABLE statements in books online
for relationships you need to use FOREIGN KEY constraints
Start with logical model and then work on scripts
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
MrMokete
Starting Member
Botswana
6 Posts |
Posted - 01/11/2013 : 03:50:17
|
drop database quantification
/* Install quantification database */
go
create database quantification
go
checkpoint
go
use quantification
go
/* -- 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)
)
go
raiserror ('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)
)
go
raiserror ('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)
)
go
raiserror ('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)
)
go
raiserror ('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 machines
add constraint reagent_id foreign key (reagent_id) references reagents (reagent_id);
alter table tests
add constraint machine_id foreign key (machine_id) references machines (machine_id);
alter table reagents
add constraint test_id foreign key (test_id) references tests (test_id);
alter table tests
add constraint statistics_id foreign key (statistics_id) references statistic (statistics_id);
alter table facility
add constraint testing_id foreign key (testing_id) references testing (testing_id);
alter table statistic
add 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
India
47173 Posts |
Posted - 01/11/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
MrMokete
Starting Member
Botswana
6 Posts |
Posted - 01/11/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/11/2013 : 03:58:15
|
>> Machines is linked to Tests through types of tests that can run on that machine
That 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 table
create table Test_Machine
(
test_id
machine_id
)
pk both columns
fk 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
India
47173 Posts |
Posted - 01/11/2013 : 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 print
for inserting data, start with master tables (parent) and then insert into child tables with referenceids generated in parent
Will you be doing row by row or bulk insertion?
If former, use SCOPE_IDENTITY to retrieve the generated id
if latter use OUTPUT clause
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
MrMokete
Starting Member
Botswana
6 Posts |
Posted - 01/11/2013 : 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
Botswana
6 Posts |
Posted - 01/11/2013 : 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
India
47173 Posts |
Posted - 01/11/2013 : 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 like
DECLARE @ParentID int
INSERT INTO Parent (Col1,Col2,.. all except identity column)
VALUES(val1,val2,...)
SET @ParentID = SCOPE_IDENTITY()
INSERT INTO Child(ParentID,...)
VALUES(@ParentID,...)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 01/11/2013 04:08:48 |
|
|
|
MrMokete
Starting Member
Botswana
6 Posts |
Posted - 01/11/2013 : 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
India
47173 Posts |
Posted - 01/11/2013 : 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 already
see post on 01/11/2013 : 03:54:10
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/11/2013 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 01/11/2013 : 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 MVP
http://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.
|
|
|
| |
Topic |
|
Creating A Database with six tables
