Database Definition and FMS Tool Management Case Study
This presentation is the property of its rightful owner.
Sponsored Links
1 / 46

Database Definition and FMS Tool Management Case Study PowerPoint PPT Presentation


  • 56 Views
  • Uploaded on
  • Presentation posted in: General

Database Definition and FMS Tool Management Case Study A Flexible manufacturing system consists of a number of machines or cells each of them capable of handling one tool magazine at a time. The cells use the tools to produce objects according to the instructions in part-programs.

Download Presentation

Database Definition and FMS Tool Management Case Study

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Database definition and fms tool management case study

Database Definition and FMS Tool Management Case Study

A Flexible manufacturing system consists of a number of machines or cells each of them capable of handling one tool magazine at a time.

The cells use the tools to produce objects according to the instructions in part-programs.

The contents of the tool magazines can be changed and they are interchangeable between machines.

Answers are required to questions like-

Which machine(s), or cell(s), have the appropriate tool mix in their tool magazines to process a given part program?

This question could be asked by an FMS production manager, or the FMS scheduling program itself, before finalising the sequence in which the certain part, or parts, will be processed in the system


Database definition and fms tool management case study

Option1- An approach to be avoided…..as a warning

First step is to set up some tables (in files) containing the information on about cells, magazines, and workparts.

The name of one table is 'machine’ which consists of the following fields

machine_id a 16 character long alphanumeric name, containing the machine identifier.

Max_power is the maximum machine power in kwatts and is a real number.

x_mintoz_max describe the motion range limits of the machine in mm.

Rot_index stores the size of increment the table is capable of rotating on the X,Y table of the machine.

poserr_x_y_z contains the positioning error regarding the relevant axis.

controller identifies the type of CNC control used.

magazine stores the magazine identifier currently being utilised on the cell.

It is assumed that each cell can only have one tool magazine for automated access at any one time.http://scm-intranet.tees.ac.uk/users/u0000667/cweb2003/slides/fmswide1.ppt


Database definition and fms tool management case study

The second file we need must contain information about the tools located in the magazines.

Thus there is a magazine table identifying twelve tool locations.

In this example there are only twelve locations in the tool magazines

In a real situation machines often have over fifty tools or even more

Only one simple code identifying a single tool in the magazine is used.


Database definition and fms tool management case study

So magazine has the fields-

magazine stores the magazine identifier.

T1 to T12 are 8 character fields which hold the tool identifiers of the tools in a particular magazine.

i.e. (magazine_Id, T1,T2,T3,T4,........T12)


Database definition and fms tool management case study

The third and last table we set up is called prog_tool.

It contains the tool codes used in different part programs.

Again for simplicity we use only six fields describing six tools, although in a real situation a part program could easily require the access of over twenty tools or more.

So prog_tool has the fields-

Prog_name is a 16 character field containing the part program name

T1toT6 are 8 character fields containing the codes of the tools used in the program.

i.e. (prog_name, T1, T2, T3, T4, T5, T6)

http://scm-intranet.tees.ac.uk/users/u0000667/cweb2003/slides/fmswide2.ppt


Database definition and fms tool management case study

  • So, there is-

  • A table telling us which magazine is on which cell

  • A table telling us which tools are in which magazines

  • And a table telling us which tools a part program requires


Database definition and fms tool management case study

  • We want the database to find the cell on which there is the appropriate magazine to execute a particular part program.

  • Produce an entity-relationship diagram based on these table definitions.

  • 2. Devise an query to do this based on these table definitions.

  • 3. Assume the database is loaded with data. Write down the steps required to

  • modify the database to accept magazines with twenty tools and part programs

  • that require twenty tools.


Database definition and fms tool management case study

  • You should have found that

  • The data model is a little bizarre-with multiple relationships between the entities

  • That the query is at least very difficult to produce

  • That the tables have to be significantly altered to accommodate the new requirements


Database definition and fms tool management case study

(machine_id, magazine_id etc)

Magazine

Cell

(magazine_id)

Mag_Tool

(mag_id, tool_id)

0..1

0..1

Holds

Tool

1..1

(tool_id)

1..1

Loaded_with

Has_mounted

0..*

0..*

(program_id)

Program

Prog_Tool

(prog_id,tool_id)

0..*

Used_on

1..1

1..1

Employed_by

0..*

Requires

0..*

1..1

Option 2

The data has not been modelled correctly-the data model should look like this-


Database definition and fms tool management case study

Create Procedure fmscase

/*(@parameter1 datatype = default value,

@parameter2 datatype OUTPUT)*/

As

create table magazine

(magazine_id varchar(5) not null,

constraint magazine_key primary key(magazine_id),

constraint mag_check check (magazine_id like 'mag_')

);


Database definition and fms tool management case study

create table machine

(machine_id varchar(5), max_power float not null,

x_min float, x_max float, y_min float, y_max float,

z_min float, z_max float, rotJndex float, poserrx float,

poserry float, poserrz float, controller varchar(10), magazine varchar(5),

constraint machine_key primary key (machine_id),

constraint holds foreign key (magazine ) references magazine(magazine_id) on update no action on delete no action,

constraint mc_id_check check (machine_id like 'cell[0-9]'),

constraint max_p_check check (max_power>0),

constraint x_mincheck check (x_min between -1500 and 1500),

constraint x_maxcheck check (x_max between -1500 and 1500),

constraint y_mincheck check (y_min between -1500 and 1500),

constraint y_maxcheck check (y_max between -1500 and 1500),

constraint z_mincheck check (z_min between -1500 and 1500),

constraint z_maxcheck check (z_max between -1500 and 1500),

constraint rotJndexcheck check (rotJndex between -1500 and 1500),

constraint poserrxcheck check (poserrx between -1500 and 1500),

constraint poserrycheck check (poserry between -1500 and 1500),

constraint poserrzcheck check (poserrz between -1500 and 1500),

constraint cont_check check (controller in ('cincinnati','hertel','sandvig')));


Database definition and fms tool management case study

Create table tool

( tool_id varchar(5) not null,

constraint tool_key primary key (tool_id),

constraint tool_id_check check (tool_id between 't0000' and 't9999')

);

create table program

(prog_name varchar(6) not null,

constraint program_key primary key (prog_name),

constraint program_check check (prog_name in ('mill1','bore1','drill1'))

);

Createtable mag_tool

(magazine_id varchar(5) not null,

tool_id varchar(5) not null,

constraint mag_tool_key primary key (magazine_id, tool_id),

constraint loaded_with foreign key (magazine_id) references magazine(magazine_id) on update cascade on delete cascade,

constraint used_on_2 foreign key (tool_id) references tool on update cascade on delete cascade);


Database definition and fms tool management case study

create table prog_tool

(prog_name varchar(6) not null,

tool_id varchar(5) not null,

constraint prog_tool_key primary key (prog_name, tool_id),

constraint requires foreign key (prog_name) references program on update cascade on delete cascade,

constraint employed_with foreign key (tool_id) references tool on update cascade on delete cascade);

return


Database definition and fms tool management case study

Create Procedure fmsinsert

/*(@parameter1 datatype = default value,

@parameter2 datatype OUTPUT)*/

As

/* set nocount on */

insert into program values ('bore1');

insert into program values ('drill1');

insert into program values ('mill1');


Database definition and fms tool management case study

insert into tool values('t1002');

insert into tool values('t1584');

insert into tool values('t2000');

insert into tool values('t2001');

insert into tool values('t2002');

insert into tool values('t2109');

insert into tool values('t2329');

insert into tool values('t2341');

insert into tool values('t3212');

insert into tool values('t3456');

insert into tool values('t3480');

insert into tool values('t4097');

insert into tool values('t4496');

insert into tool values('t4797');

insert into tool values('t5472');

insert into tool values('t5670');


Database definition and fms tool management case study

insert into tool values('t6652');

insert into tool values('t673 ');

insert into tool values('t6800');

insert into tool values('t6811');

insert into tool values('t7352');

insert into tool values('t7803');

insert into tool values('t8081');

insert into tool values('t8085');

insert into tool values('t8086');

insert into tool values('t8087');

insert into tool values('t8972');

insert into tool values('t9001');

insert into tool values('t9002');

insert into tool values('t9004');

insert into tool values('t9008');

insert into tool values('t9102');

insert into tool values('t1254');

insert into tool values('t2626');

insert into tool values('t7800');

insert into tool values('t002');

insert into tool values('t5555')


Database definition and fms tool management case study

insert into magazine values('mag0');

insert into magazine values('mag1');

insert into magazine values('mag2');


Database definition and fms tool management case study

insert into machine values (

'cell1',15.00,

0,650.0,

0,850.0,

150.0,950.0,

0.1,0.01,

0.01,0.015,

'cincinnati','mag0');

insert into machine values (

'cell2',22,

0,800.0,

0,1250.0,

0,1100,

0,0.018,

0.018,0.02,

'cincinnati','mag1');

insert into machine values (

'Cell3',15,

0,650.0,

0,850.0,

150.0,950.0,

0.05,0.005,

0.005,0.01,

'cincinnati','mag2');


Database definition and fms tool management case study

insert into mag_tool values('mag0','t9001');

insert into mag_tool values('mag0','t9004');

insert into mag_tool values('mag0','t9008');

insert into mag_tool values('mag0', 't9002');

insert into mag_tool values('mag0','t8081');

insert into mag_tool values('mag0','t9102');

insert into mag_tool values('mag0','t7352');

insert into mag_tool values('mag0','t673 ');

insert into mag_tool values('mag0','t3480');

insert into mag_tool values('mag0','t6811');

insert into mag_tool values('mag0','t2329');

insert into mag_tool values('mag0','t1002');

insert into mag_tool values('mag1' ,'t8085');

insert into mag_tool values('mag1' ,'t6800');

insert into mag_tool values('mag1' ,'t8087');

insert into mag_tool values('mag1','t8086');

/* insert into mag_tool values('mag1','t2626'); removed for query purposes*/


Database definition and fms tool management case study

insertinto mag_tool values('mag1','t1584');

insert into mag_tool values('mag1','t5472');

insert into mag_tool values('mag1','t5670');

insert into mag_tool values('mag1','t7803');

insert into mag_tool values('mag1','t4496');

insert into mag_tool values('mag1','t4097');

insert into mag_tool values('mag1' ,'t4797');

insert into mag_tool values('mag2','t2109');

insert into mag_tool values('mag2','t3456');

insert into mag_tool values('mag2','t6652');

insert into mag_tool values('mag2','tl254');

insert into mag_tool values('mag2', 't8972');

insert into mag_tool values('mag2','t3212');

insert into mag_tool values('mag2','t2341');

insert into mag_tool values('mag2','t2000');

insert into mag_tool values('mag2','t2001');

insert into mag_tool values('mag2','t2002');

insert into mag_tool values('mag2','t9001');

insert into mag_tool values('mag2','t9004');


Database definition and fms tool management case study

insert into prog_tool values('mill1','t9008');

insert into prog_tool values('mill1','t3480');

insert into prog_tool values('mill1','t9004');

insert into prog_tool values('mill1','t9002');

insert into prog_tool values('mill1','t7352');

insert into prog_tool values('bore1','t6800');

insert into prog_tool values('bore1','t2626');

insert into prog_tool values('bore1','t2329');

insert into prog_tool values('bore1','t1002');

insert into prog_tool values('bore1','t5555');

insert into prog_tool values('drill1','t8081');

insert into prog_tool values('drill1','t9102');

insert into prog_tool values('drill1','t7800');

return


Database definition and fms tool management case study

Magazine

Mag0

Mag1

Mag2

http://scm-intranet.tees.ac.uk/users/u0000667/cweb2003/slides/fmswide5.ppt

select * from magazine;

A list of all the magazines?'


Database definition and fms tool management case study

prog_name

bore1

drill1

mill1

select * from program;

A list of all the programs?'


Database definition and fms tool management case study

magazine

tool_id

mag0

t9001

mag0

T9004

mag0

T9008

mag0

T9002

mag0

T8081

mag0

T9102

mag0

T7352

mag0

T6739

mag0

T3480

mag0

T6811

mag0

T2329

mag0

T1002

Mag1

T8085

Mag1

T6800

Mag1

T8087

Mag1

T8086

Mag1

T2626

Mag1

T1584

Mag1

T5472

Mag1

T5670

Mag1

T7800

Mag1

T4496

Mag1

T4097

Mag1

T4797

Mag2

T2109

Mag2

T3456

Mag2

T6652

Mag2

T1254

Mag2

T8972

Mag2

T3212

Mag2

T2341

Mag2

T2000

Mag2

T2001

Mag2

T2002

Mag2

T9001

Mag2

T9004

select * from mag_tool;

'A list of all the tools on all the magazines?'


Database definition and fms tool management case study

Prog_name

tool_id

Mill1

t3480

Mill1

T9004

Mill1

T1002

Mill1

T7352

Bore1

T6800

Bore1

T2626

Bore1

T2329

Bore1

T1002

Bore1

T5555

Drill1

T8081

Drill1

T9102

Drill1

T7800

select * from prog_tool;

A list of all the tools used by the programs?


Database definition and fms tool management case study

Tool_id

T1002

T1254

T1584

T2000

T2001

T2002

T2109

T2329

T2341

T2626

T3212

T3456

T3480

T4097

T4496

T4797

T5472

T5670

T6652

T6739

T6800

T6811

T7532

T7800

T8081

T8085

T8086

T8087

T8972

T9001

T9002

T9004

T9008

T9102

select * from tool

A list of all the tools?'


Database definition and fms tool management case study

prog_name

prog_name

tool_id

tool_id

mill1

mill1

t3480

T1002

mill1

Bore1

T9004

T1002

mill1

Bore1

T2329

T1002

Bore1

mill1

T2626

T7352

Bore1

mill1

T6800

t3480

Bore1

Bore1

T2626

T5555

Bore1

Bore1

T6800

T2329

Bore1

mill1

T7352

T1002

Drill1

Bore1

T7800

T5555

Drill1

Drill1

T8081

T8081

Drill1

mill1

T9004

T9102

Drill1

Drill1

T7800

T9102

select * from prog_tool;

select * from prog_tool order by tool_id


Database definition and fms tool management case study

Machine_id

controller

magazine

Cell1

cincinnati

Mag0

Cell2

cincinnati

Mag1

Cell3

cincinnati

Mag2

select machine_id, controller,magazine from machine

What are the controllers and magazines on all the machines?


Database definition and fms tool management case study

Machine_id

controller

magazine

Cell2

cincinnati

mag1

Machine_id

magazine

Tool_id

Cell2

Mag1

T2626

select machine_id, controller,magazine from machine where max_power>16.0

What are the controllers and magazines on all the machines that have a maximum power rating greater than 16.0?'

select machine_id, mag_too, magazine_id, tool_id from machine,mag_tool where

machine.magazine=mag_tool.magazine_id and mag_tool.tool_id=’t2626'

Which machines have a magazine with tool ‘t2626’?'


Database definition and fms tool management case study

Prog_name

Bore1

select prog_name from prog_tool

where not exists

(select * from mag_tool where

progtool. tool_id=mag_tool.tool_id)

Does any program require a tool that is not in a magazine on the machines?'


Database definition and fms tool management case study

COUNT(TOOLID)

PROG NAME

5

bore1

3

drill1

4

mill1

select count( tool_id),prog_name from prog_tool group by prog_name;

*How many tools does each program need ?'


Database definition and fms tool management case study

count(tool_id)

prog_name

5

bore1

4

mill1

select count(tool_id),prog_name from prog_tool group by prog_name having

count(tool_id)>3

'Which programs require the use of more than three tools ?'

Could be written as a stored procedure using parameter thus-

create procedure min_no_of_tools(@c int) as

select count(tool_id),prog_name from prog_tool group by prog_name having

count(tool_id)>@c;

exec min_no_of_tools 2


Database definition and fms tool management case study

COUNT(TOOL ID)

PROG NAME

4

mill1

select count(tool_id),prog_name from prog_tool where prog_name<>’bore1’

group by prog_name having count(tool_id)>3

'Which programs need more than 3 tools except bore1 ?'


Database definition and fms tool management case study

avg(max_power)

17.333333

prog_name

bore1

drill1

mill1

MAX(MAX POWER)

22

sum(max_power)

52

select avg(maxpower) from machine;

select distinct prog_name from prog_tool;

select max(max_power) from machine;

select sum(max_power) from machine;


Database definition and fms tool management case study

Magazine_id

Mag0

select magazine_id from magazine where not exists

(select * from prog_tool where not exists

(select * from mag_tool where

progtool.tool_id=mag_tool. tool_id

and magazine.magazine_id=mag_tool.magazine_id)

and prog_name='mill1’)

Which magazine has all the tools for job mill1?'

Could be written as a stored procedure using parameter thus-

create procedure all_tools(@c varchar(6)) as

select magazine_id from magazine where not exists

(select * from prog_tool where not exists

(select * from mag_tool where

Prog_tool.tool_id=mag_tool. tool_id

and magazine.magazine_id=mag_tool.magazine_id)

and [email protected]);

exec all_tools ‘mill1’


Database definition and fms tool management case study

Machine_id

cell1

select machine_id from machine where magazine in (select magazine_id from magazine

where not exists

(select * from prog_tool where not exists

(select * from mag_tool where

Prog_tool tool_id=mag_tool. tool_id

and magazine.magazine_id=mag_tool.magazine_id)

and prog_name='mill1’))

Which machine has the magazine to do all of job mill1?'


Database definition and fms tool management case study

‘Executive’ Commands to Manipulate Schema and Data

exec fmscase /* executes schema */

exec fmsinsert /*insert example data */

exec dropfms /* removes schema */

exec fmsq1 /*example data manipulation */

exec fmsq2 /*example data manipulation */

exec storedprocedure1 'cell5',10,0.0

/*insert with parameters */


Database definition and fms tool management case study

Extracting Metadata

select * from machine

select * from information_schema.tables

select * from information_schema.columns

select * from information_schema.referential_constraints /* lists foreign key constraints */

select * from information_schema.table_constraints


Database definition and fms tool management case study

exec sp_tables

exec sp_stored_procedures

/* lists stored procedures */

exec sp_helptext stored_procedure1

/*lists text of stored procedures and other objects */

exec sp_helpconstraint prog_tool

/* lists text of constraints inc. foreign and primary key constraints */

exec sp_helptrigger machine

/* lists text of trigger */

exec sp_help machine_Trigger3

exec sp_helptext machine_Trigger3

/* lists trigger text */


Database definition and fms tool management case study

Stored Procedure-insert with parameters

Alter Procedure StoredProcedure1(@mcid varchar(5),@m_power int,@xmin float)

/*

(@parameter1 datatype = default value,

@parameter2 datatype OUTPUT)

*/

As

/* set nocount on */

insert into machine values (@mcid,

@m_power,

@xmin,

650.0,0,850.0,150.0,950.0,0.05,0.005,0.005,0.01,'cincinnati','mag2');

return

exec storedprocedure1 'cell5',10,0.0


Database definition and fms tool management case study

Insert Trigger-Correlating values in columns

alter trigger power_x_limit

on machine

for insert

as

declare @x int

select @x=max_power from inserted

if @x>10

begin

update machine set x_min=10 where max_power>10

end


Database definition and fms tool management case study

create trigger x_check

ON machine

FOR INSERT, UPDATE

AS

declare @xmi float

declare @xma float

select * from inserted

select @xmi=x_min,@xma=x_max frominserted

if @xmi>@xma

begin

RAISERROR (‘x_max must be greater than or equal to x_min', 16, 10)

rollback

end


Database definition and fms tool management case study

Consider that some tools cannot be removed from Magazines

createtable fixed_tools

(magazine_id varchar(5),

tool_id varchar(5),

constraint ft_key primary key (magazine_id, tool_id));

insert into fixed_tools values('mag0','t1002');

insert into fixed_tools values('mag0','t2329');

insert into fixed_tools values('mag0','t3480');

drop table fixed_tools


Database definition and fms tool management case study

create trigger tool_prev_del

on mag_tool

for delete,update

as

declare @tid varchar(5)

select @tid=tool_id fromdeleted

if @tid in (select tool_id from fixed_tools)

begin

raiserror(‘This tool cannot be removed from the magazine',16,1)

rollback

end


Database definition and fms tool management case study

Createprocedure tool_log_create

/*(@parameter1 datatype = default value,

@parameter2 datatype OUTPUT)*/

As

/* set nocount on */

createtable tool_log

(tool_replaced varchar(5),

new_tool varchar(5),

date_replaced datetime,

constraint toollogkey primary key (tool_replaced, date_replaced))

return


Database definition and fms tool management case study

To keep a log of tool changes-

createtrigger tool_Trigger1

On tool

For Update, Delete

As

declare @rep_date datetime

declare @rep_tid varchar(5)

declare @new_tid varchar(5)

select @rep_date=getdate()

If Update (tool_id)

begin

select @new_tid=tool_id from inserted

select @rep_tid=tool_id from deleted

insertinto tool_log values(@rep_tid, @new_tid, @rep_date)

EndElsebegin

select @rep_tid=tool_id from deleted

insert into tool_log values(@rep_tid,'none', @rep_date)

end


  • Login