1 / 25

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

ethan
Download Presentation

Vistec Semiconductor Sysetms

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related