Transcript Vistec Semiconductor Sysetms

Vistec Semiconductor
Sysetms
Group 6
Ding Chen
Yee Wan Cheung
Roya Pakzad
Zach Rabinovich
Megan Whittey
Visctec Company Profile
Vistec produces all its
products in Germany
Products include inspection
systems, defect detection
and classification,
metrology systems for
mask and wafer
manufacturing
Vistec utilizes state of the
art electron beam
lithography and wafer
process control
Vistec Current Database System
Database used now is in Lotus Notes
No system in place to track "out of box" failures
of spare parts, it is important for the company to
know failure rates as well as who ordered, etc.
Currently within Logistics department there are
few tracking systems in place to monitor and
improve current performance
Need to develop ways to track ingoing and
outgoing parts to insure orderly acceptance and
rejection of various spares used by field service
organization to repair systems installed in field
Vistec Database Project Summary
Provide a working
database which
amends the flaws
in the current
database
Focus on the
OBF’s since it’s the
company’s priority
DP I Summary
We created:



Overall Project Summary
Textual summary of database requirements
Simplified EER diagram from information from
client
DP II Summary
We….
Revised simplified EER diagram
Relational Schema from EER diagram
5 interesting queries described and why they are
interesting to our client





1: Rank products by Instant Failure (OBF)
2: Helping managers set the warranty periods based on mean
time between failures
3: Rank all sales people by profits
4: Forecast the customer demands on each product type and
estimate the time required to produce products
5: Choose an optimal order quantity (EOQ) that will minimize the
storage and ordering costs.
DP III Summary
We…
Client Description
Revised simplified EER diagram
Revised relational design schema
Created and implemented tables in Microsoft
Access

Created relationships between the tables
5 interesting queries written in:


English
Relational algebra and/or SQL
EER Diagram Assumptions
Each product is checked by the company before they
send it out to the customers, so that the product is
either OBF (out of box failure) or not
Each customer only works with one sales rep.
Each sales rep. has at least one customer
Each factory has at least one factory worker
Each factory worker only works at one factory
Each stock stores at one factory
Each warranty status and OBF will only be checked by
one service engineer (i.e. no duplicate checking)
EER Diagram
Relational Schema
Relational Schema (cont’d)
Relationship View in Access
Normalization
1NF:
2.Customer (CID, Name, StreetAddress, PhoneNumber, City, State,
Zip)
Reason: The customer entity has no multivalued attributes assuming
there’s only one phone number for each customer.
2NF:
10.Order (OID, Date, Description, ESSN6a, CID2, PID1, Quantity)
Reason: No partial dependencies, everything is derived from the order
id.
Normalization
4. Component
CPID CType
Description
PID
Type
Price
1 FD
2FD
Transitive Dependency Violates 3 NF
Normalization
Component 1
CPID
cType Description PID
FD3
Component 2
PID
Type
FD4
Price
Query 1
Find out which product types have most failure by
ranking percentage failure for each product
SELECT COUNT(R. TagNumber)/SUM(o.quantity)*100
FROM
Returned_Produce R, OrderTo O
WHERE SUM(o.quantity) IN (SELECT SUM(O.quantity)
FROM ShipsTo S
GROUP BY S. PID)
GROUP BY R.PID
ORDER BY COUNT(R.TagNumber)/SUM (O.quantity)*100;
Query 2
Helping the company to set the warranty
periods based on mean time between failure
SELECT RProd.PID, AVG(DATEDIFF(“day”, ShipTo.Date,
RProd.Date_Returned)
From Returned_Product AS RProd, ShipTo
Where RProd.PID=ShipTo.PID, RProd.Type=ShipTO. Type,
RProd. CID=ShipTo.CID
GROUP BY ShipTo.PID;
Query 2
Query 3
Rank all salesperson by profits (subtract salary
from the salesperson’s total sales)
3.1 Total_Sales_Query
SELECT (p.Price*o.quantity)OrderTotal
FROM Product AS p, Order AS o
WHERE p.PID=o.PID AND o.Date LIKE ‘%2007’
GROUP BY o.OID;
3.2 Profit_per_SalesPerson_Query
SELECT SP.ESSN, (OT.OrderTotal*(1 - SP.Commision) – SP.Salary )Profits
FROM SalesPerson AS SP, Order_Total_Query AS OT
WHERE SP.ESSN=OT.ESSN
ORDER BY (OT.OrderTotal*(1 - SP.Commision) – SP.Salary )Profits;
Query 3
Query 4
Forecasting the customer demands on each product type based
on the historical demand data, and estimate the time require
producing the products by keep tract of the average time for
supplier to deliver the component when placed order.
4.1 Average time for supplier
Select AVG (DATEDIFF (“d”, ORDERFROM. Date_Received, ORDERFROM.
Date-Ordered))/*100
FROM
ORDERFROM
GROUP BY ORDERFROM. Type;
4.2 Demand_Query
Select
From
GROUP BY
ORDER BY
SUM(ORDER.quantity) AS DemandTotal, ORDER.PID, ORDER.Date
ORDER
ORDER.PID
ORDER.DATE;
Query 4
Query 5
Choose an optimal order quantity (EOQ) that will
minimize the storage and ordering costs.
OrderingCost_Query
SELECT C.Price AS OrderCost
FROM Component AS C;
HoldingCost_Query
SELECT StoredAt.HoldingCost AS HoldCost
FROM StoredAt;
GROUP BY StoredAt.PID;
EOQ
SELECT sqrt( 2*OC.OrderCost*D.DemandTotal/HC.HoldCost )
FROM OrderingCost_Query AS OC, HoldingCost_Query AS
HC,
Demand_Query AS D
Component & Feedback Reports
Component & Lithography Tables
Vistec Semiconductor
Sysetms
Questions
and
Answers