Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Creating A Database with six tables

Author  Topic 

MrMokete
Starting Member

6 Posts

Posted - 2013-01-11 : 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

52326 Posts

Posted - 2013-01-11 : 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/

Go to Top of Page

MrMokete
Starting Member

6 Posts

Posted - 2013-01-11 : 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??
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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..
Go to Top of Page

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 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.
Go to Top of Page

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 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/

Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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 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/

Go to Top of Page

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.
Go to Top of Page

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 already

see post on 01/11/2013 : 03:54:10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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 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.

Go to Top of Page
   

- Advertisement -