Shared database

Database access technical data

Server IP	regional server IP
Port	5432
Database type	PostgreSQL
Access mode	ODBC
ODBC Driver	32bit, 64bit
Database Name	andon_PLANT_CODE
Username	frankerp
Password	f17!

E-R database schema

Data dictionary

Entities

Entity
Name	Description	Attributes
Name	Description	Type	Identifier
prodline	Production lines	key	Line unique key	varchar	SI
descrizio	Line description	varchar
codrep	Department code	varchar
descrep	Department name	varchar
codstab	Plant code	varchar
descstab	Plant description	varchar
unitprod	Unique production id, produces items	codice	Production unit unique code	varchar	YES
codart	Article code	varchar
dart	Article descrption	varchar
procord	Production Order (ERP)	varchar
qtaprev	Number of items to be produced per production order	double
unita	Number of pieces per unit production (usually 1)	double
tmcr	Production unit creation Epoch Time (number of seconds from Jan 1, 1970)	long
datacr	Creation date in YYYYMMDD format (database date)	varchar
oracr	Creatione time formatted as HH:MM:SS (HH=00..24)	varchar
causanc	Non-conformity causes list	codice	Cause unique code	varchar	SI
descrizio	Non-conformity cause description	varchar
causafm	Line stop causes list	codice	Stop cause unique code	varchar	SI
descrizio	Line stop cause description	varchar
tipofm	Type of stop: F = downtime, M = micro-stop, P = programmed pause	varchar
setup	Referred to tooling stops: 1 = stop is a tooling, 0 = stop is not a tooling	integer

Relations

Relations
Name	Description	Components	Attributes
Name	Description	Type
regprod	Each row represents a production event	prodline, unitprod	data	Event date formatted as YYYYMMDD	varchar
ora	Event time formatted as HH:MM:SS	varchar
tm	Event Epoch time (number of seconds elapsed from Jan 1, 1970)	long
qta	Number of produced pieces in production units	double
regnc	Each row represents a non-compliant event	prodline, unitprod, causanc	data	Event date formatted as YYYYMMDD	varchar
ora	Event type formatted as HH:MM:SS	varchar
tm	Event Time Epoch (number of seconds from Jan 1, 1970)	long
qta	Number of scrapt items (production units)	double
regfm	Every row represents a begin or end of a line stop	prodline, causafm	data	Event date formatted as YYYYMMDD	varchar
ora	Event type formatted as HH:MM:SS	varchar
tm	Event Time Epoch (number of seconds from Jan 1, 1970)	long
tmdiff	Time difference between 2 consecutive events, being them start or stop doesn't matter	long
tipoevento	INFM=line stop begins, FIFM=line stop ends	long

Logical Schema

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

Example Queries

Produced pieces on Sep 18, 2017 with date, time, unique identifier, line description, department description, article description and production order, ordered by production time

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='20170918' ORDER BY tm

Produced pieces after Sep 17 (included) grouped by day, product and production order on line 151301

SELECT DATA, unitprod.dart, unitprod.procord, SUM(qta) AS pezzi FROM regprod INNER JOIN unitprod ON unitprod.codice=regprod.codlot WHERE regprod.codubi='151301' AND regprod.data>='20170917' GROUP BY DATA, dart, procord

List of line 151301 stop (begins and ends) from 06:00 to 14:00 of Sep 17, with date, time, run/stop time in minutes, type of stop, stop cause description, stop type, 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='151301' AND DATA='20170917' AND ora>='06:00' AND ora<='14:00' ORDER BY tm