This is an old revision of the document!
Shared production data
Database access: technical information
E-R diagram
Data dictionary
Entities
| Entity | ||||||
|---|---|---|---|---|---|---|
| Name | Description | Attributes | ||||
| Name | Description | Type | Identifier | |||
| prodline | Production resources list | codice | Production resource unique identifier | varchar | Yes | |
| descrizio | Production resource name | varchar | ||||
| codrep | Department unique identifier | varchar | ||||
| descrep | Department description | varchar | ||||
| codstab | Plant identifier | varchar | ||||
| descstab | Plant description | varchar | ||||
| unitprod | Unique unit of production, produced items | codice | Produced item unique identifier | varchar | YES | |
| codart | Product code | varchar | ||||
| dart | Product description | varchar | ||||
| procord | Work order (from ERP) | varchar | ||||
| qtaprev | Work order total quantity to be produced | double | ||||
| unita | Produced items for each work cycle (generally 1) | double | ||||
| tmcr | Time Epoch of the tracked production unit (seconds since 01/01/1970) | long | ||||
| datacr | Date of the tracked production unit (YYYYMMGG) | varchar | ||||
| oracr | Time of the tracked production unit (HH:MM:SS) | varchar | ||||
| causanc | Non compliant causes list | codice | Cause unique identifier | varchar | YES | |
| descrizio | Cause description | varchar | ||||
| causafm | Downtime causes list | codice | Downtime unique identifier | varchar | YES | |
| descrizio | Downtime description of the monitored resource | varchar | ||||
| tipofm | Downtime type: F = unplanned downtime, M = microstop, P = planned downtime | varchar | ||||
| setup | Downtime for setup reason (flag): 1 = setup kind, 0 = general downtime | integer | ||||
Relationships
| Relationship | |||||
|---|---|---|---|---|---|
| Name | Description | Components | Attributes | ||
| Name | Description | Type | |||
| regprod | Each row refers to a production event | prodline, unitprod | data | Event date (YYYYMMGG) | varchar |
| ora | Event time (HH:MM:SS) | varchar | |||
| tm | Event Time Epoch | long | |||
| qta | Number of produced items | double | |||
| regnc | Each row refers to a non compliant production event | prodline, unitprod, causanc | data | Event date (YYYYMMGG) | varchar |
| ora | Event time (HH:MM:SS) | varchar | |||
| tm | Event Time Epoch | long | |||
| qta | Number of non compliant produced items | double | |||
| regfm | Each row refers to a begin or an end of a downtime | prodline, causafm | data | Event date (YYYYMMGG) | varchar |
| ora | Event time (HH:MM:SS) | varchar | |||
| tm | Event Time Epoch | long | |||
| tmdiff | Time difference from end and begin of the downtime events | long | |||
| tipoevento | Downtime event type: INFM=downtime start, FIFM=donwtime end | long | |||
Logical scheme
| Table | Foreign keys |
|---|---|
| prodline(codice, descrizio, codrep, descrep, codstab, descstab) | |
| unitprod(codice, codart, dart, procord, qtaprev, unita, tmcr, datacr, oracr) | |
| causanc(codice, descrizio) | |
| causafm(codice, descrizio, tipofm, setup) | |
| regprod(codice, data, ora, tm, qta, codlot, codubi) | - codlot foreign key on unitprod.codice - codubi foreign key on prodline.codice |
| regnc(codice, data, ora, tm, qta, codlot, codubi, caudef) | - codlot foreign key on unitprod.codice - codubi foreign key on prodline.codice - caudef foreign key on causanc.codice |
| regfm(codice, data, ora, tm, tmdiff, tipoevento, codubi, caufm) | - codubi foreign key on prodline.codice - caufm foreign key on causafm.codice |
Quering samples
Produced items on June 18th 2018 with date, time, unique identifier, production resource description, department description, article code (model) and work order, sorted by event time epoch
SELECT DATA, ora, codlot, prodline.descrizio AS linea, prodline.descrep, unitprod.dart, unitprod.procord FROM regprod INNER JOIN prodline ON prodline.codice=regprod.codubi INNER JOIN unitprod ON unitprod.codice=regprod.codlot WHERE DATA='20180618' ORDER BY tm
Conteggio dei pezzi prodotti dopo il 17 Maggio (compreso) raggruppati per giorno, per prodotto e ordini di produzione, nella linea 04
SELECT DATA, unitprod.dart, unitprod.procord, SUM(qta) AS pezzi FROM regprod INNER JOIN unitprod ON unitprod.codice=regprod.codlot WHERE regprod.codubi='04' AND regprod.data>='20170517' GROUP BY DATA, dart, procord
Estrarre la lista delle fermate (inizio, fine) della linea 04 dalle ore 06:00 alle ore 14:00 del giorno 17 Maggio, con data, ora, differenza temporale tra due eventi in minuti, tipo di evento, descrizione della causa di fermo, tipo di fermo, setup (1,0)
SELECT DATA, ora, tmdiff/60 AS dtm, tipoevento, causafm.descrizio AS dfermo, causafm.tipofm, causafm.setup FROM regfm INNER JOIN causafm ON causafm.codice=regfm.caufm WHERE codubi='04' AND DATA='20170517' AND ora>='06:00' AND ora<='14:00' ORDER BY tm
