Vistec Semiconductor Sysetms

1. Vistec Semiconductor Sysetms Group 6 Ding Chen Yee Wan Cheung Roya Pakzad Zach Rabinovich Megan Whittey

2. 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

3. 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

4. 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

5. DP I Summary • We created: • Overall Project Summary • Textual summary of database requirements • Simplified EER diagram from information from client

6. 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.

7. 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

8. 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)

9. EER Diagram

10. Relational Schema

11. Relational Schema (cont’d)

12. Relationship View in Access

13. 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.

14. 1 FD 2FD Normalization 4. Component Transitive Dependency Violates 3 NF Normalization Component 1 Component 2 FD4 FD3

15. 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;

16. 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;

17. Query 2

18. 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;

19. Query 3

20. 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 SUM(ORDER.quantity) AS DemandTotal, ORDER.PID, ORDER.Date • From ORDER • GROUP BY ORDER.PID • ORDER BY ORDER.DATE;

21. Query 4

22. 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

23. Component & Feedback Reports

24. Component & Lithography Tables

25. Vistec Semiconductor Sysetms Questions and Answers