frank lehner u zurich proposal to use the atlas sct database for run iib why to switch now? u...

Frank LehnerU Zurich

DDProposal to use the

Atlas SCT database for Run IIb

Why to switch now? existing database (db) at UIC incomplete and

unlikely to be completed a true db design (i.e. entity-relationship) is not

existing and was never designed we want structured queries in order to retrieve

information on objects for assemblies this will be almost impossible to implement in the

existing db real concern that this db project is for an

undergrad student at UIC way too work we have to pull the break now, otherwise we will

not be able to have a good db during production testing and assembly




Why Atlas SCT db? solid and mature db design (v5.9) Atlas db tasks are very similar to ours their db design goes back to 1998 it is developed by programmers with close contact to SCT

people Our approach

adopt Atlas design by requesting the designer file from them

get help from D0 professionals: Stu, Taka, Stan for implementation

Need contact person within Run IIb, who knows details of the detector: Eckhard v. Toerne (?)

strip the design file to the absolute necessary structures use possibly MISWEB as Web interface for data entry and

retrieval tools The web-interfaces will be the most work to do




Atlas db system architecture: Relational db based on ORACLE 8 Db follows client-server model:

ORACLE server in geneva OMNIS client applications on PCs Web client to allow access to db from any web

browser Java client to allow upload of special formatted

data like labview program output etc.




The Atlas SCT db tracks components in four ways: Item

An item record holds basic information of all SCT components e.g. Serial number, item type, manufacturer, current

location, passed test? etc. Test

Test records are created whenever items are tested certain test parameters are automatically checked against

predefined tolerances Assembly

Assembly record links component item to assembly item There are automatic bookkeeping procedures, which keep the

item’s location and status up to date, when it is assembled Shipment/Tracking

Shipment records are created whenever an item is shipped/moved from one institute to another




Data structures data are organized in SQL tables, the contained data can

be manipulated by operations Several “classes” of tables:

Class 1 (in yellow): – description of item types, tests, assemblies, institutes, etc– read access by everyone– update access limited to few experts

Class 2: (in blue)– records of items, tests, assemblies, shipments, etc– each institute updates only entries it ‘owns’

Class 3: (in green)– Is a history structure for assemblies, if a deletion is made




Atlas SCT database works with a unique serial number for all items

This serial number is an intrinsic number within the db and does not coincide with any other serial numbers we have

part identification within the db all items in db have internal nine-digit decimal

serial number for unique identification the first two digits are a numeric code which are

assigned to each institute involved then, there is a six digit sequential number

automatically generated by the db, when the item is registered in the db for the first time

one other digit is reserved for other purposes




Close-up of the Atlas entity-relationship




The item tables are the following: Item_category: Item type category like

sensor, hybrid etc Item_description: Item type description

like L0-sensor, L1-sensor etc Items: this are the details of each item Item_comments: comments on item Item_images: optional gif or jpg images Stocks: stock levels of items




Of course, we are building assemblies: 20-20 stereos, 10-10 axials etc.

There are two Assembly tables: Assm_descr and Assm_items

together with the items and items_description tables they store a general description of each type of assembly, as well as records of which component items are mounted in any particular assembly

An item is ‘assembled’ in the databse when an assembly record is created linking it to a position on an assembly item. Deleting the record, ‘disassembles’ the item

Automatic procedures in ORACLE (ORACLE triggers) keep track of the location and status of assembled and disassembled items




Tests There are tables for a generic test information:

Each type of a test on an item or assembly has a short test description with a web pointer to a full specification

Every test has a test record with general details and pas/fail results. A sensor visual inspection would be recorded just in a normal test record

Raw data like C-V, I-V on sensors can be recorded in a spreadsheet (or XML-format) in a raw data record. The entire raw data record can be easily retrieved for display and analysis, but the db ignores its content

List of strip defects can be recorded as defects records associated with a test

Comments and web links can be attached Tolerance limits and specs can be associated with any

test parameter




There are also special test information tab les

The structures needed to hold parameters specific to a particular test are separated into special tables, which can be added as new tests are defined

Each special test record is associated with a generic test record, and thus with comments, web links or a list of defects




Shipments: Shipment tables keep track of items:

Ship: contains details of shipment: Carrier, data, sender etc

Ship_items: map items into shipment

Misc: Location table: institutions MFRS table: Manufactures/vendors Persons: Databse users




Futher information: SCT db welcome page:

http://melb.unige.ch:3143/ http://www.hep.man.ac.uk/groups/atlas/SC

Tdatabase/Database.html Atlas note ATL-INDET-2002-015

Next steps: Request designer file from Geneva Strip tables to a minimum necessary

amount use sensors to begin with

frank lehner u zurich proposal to use the atlas sct database for run iib why to switch now? u...

Documents