cli:franke:shared-db-info

Shared database

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

Name	Description	Attributes
Entity
		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

Table of Contents