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 | ||||
| qcausanc | Non compliant causes list | codice | Cause unique identifier | varchar | YES | |
| descrizio | Cause description | varchar | ||||
| qcausafm | 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 |
Sample queries
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 shareddata.regprod INNER JOIN shareddata.prodline ON prodline.codice=shareddata.regprod.codubi INNER JOIN shareddata.unitprod ON unitprod.codice=shareddata.regprod.codlot WHERE DATA='20180618' ORDER BY tm
Count produced items after June 18th 2018 (included) grouped by day, article code and work order on production resource 821102A (DMG LB)
SELECT DATA, unitprod.dart, unitprod.procord, SUM(qta) AS pezzi FROM shareddata.regprod INNER JOIN shareddata.unitprod ON shareddata.unitprod.codice=shareddata.regprod.codlot WHERE regprod.codubi='821102A' AND regprod.data>='20180618' GROUP BY DATA, dart, procord
Get the DMG LB (821102A) downtime events list from 06:00 to 14:00 of June 18th 2018 with date, time, duration in minutes, event type, downtime cause description, downtime kind, changeover time flag
SELECT DATA, ora, tmdiff/60 AS dtm, tipoevento, qcausafm.descrizio AS dfermo, qcausafm.tipofm, qcausafm.setup FROM shareddata.regfm INNER JOIN shareddata.qcausafm ON shareddata.qcausafm.codice=regfm.caufm WHERE codubi='821102A' AND DATA='20180618' AND ora>='06:00' AND ora<='14:00' ORDER BY tm