1 / 44

Team #2 A DATABASE FOR LA GAUGE COMPANY

Team #2 A DATABASE FOR LA GAUGE COMPANY. Benn Ackley Ajay Bawa Clarence Cheung Steven Leonard Nhat Nguyen Edrick Soetanto. Today’s Agenda . Company Background Customer Request EER Diagram Relational Schema Normalization Analysis Forms and Reports Query implementation

jarah
Télécharger la présentation

Team #2 A DATABASE FOR LA GAUGE COMPANY

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. Team #2A DATABASE FOR LA GAUGE COMPANY Benn Ackley Ajay Bawa Clarence Cheung Steven Leonard Nhat Nguyen Edrick Soetanto

  2. Today’s Agenda • Company Background • Customer Request • EER Diagram • Relational Schema • Normalization Analysis • Forms and Reports • Query implementation • Future improvement

  3. Company Background • Location: Sun Valley, CA • Provides ultra precision machining of Beryllium Alloys and other exotic metals used in aerospace, defense, optics, and nuclear industries. • Major clients: • Boeing • Northrop Grumman • Raytheon

  4. Customer Request • Current database: Delmar- a 16-bit legacy database • Difficulty with current database: • Slow and unreliable • Many glitches • High maintenance cost • To create a database allowing: • LA Gauge engineers to effectively communicate with the floor machinists and keep track of the work flows. • Easy input of series of operations and specifications to machine a product. • Sales Associate to input customer and order data • Estimated data size: 5,000 – 10,000 records.

  5. EER Diagram

  6. Relational Schema • Customer (Customer_ID, Company, Lname, Fname, Email, Job_Title, Business_Phone, Street_Address, City, State, ZIP, Country, Web_Page, Primary_liaison7) • Order(Order_ID, Order_Date, Customer_ID1) • Product(Product_ID, Product_Name, Description, Product_Sales_Price, Amount_On_Hand, Order_ID2) • Purchased(Product_ID, Purchased_Cost, Purchased_Date, Supplier_ID5) • In-House(Product_ID, Manufacturing_Cost, Manufacturing_Date,) • Shipment(Shipment_ID, Tracking_Number, EID7a) • Ship_In(Shipment_ID, Supplier_ID5, Received_Date, Receiving_Employee7, RM_ID6) • Ship_Out(Shipment_ID, Customer_ID1, Shipped_Date, Shipping_Cost) • Supplier(Supplier_ID, Company, Lname, Fname, Email, Job_Title, Business_Phone, Business_Phone, Street_Address, City, State, Province, ZIP, Country, Web_Page, Account_ID23) • Raw_Material(RM_ID, RM_Name , Description, RM_Cost, Supplier_ID5, Shipment_ID4, Account_ID23, Amount_On_Hand, Units, Amount_Purchase) • Employee(Employee_ID, Lname, Fname, Email, Phone, Streeet_Address, City, State, ZIP, Country, Schedule, Working_Hour, Department_ID12, Starting_Date, Salary, Supervisor7) • Sales_Associate(Employee_ID, Commission) • Technician(Employee_ID) • Other(Employee_ID, Job_Duty) • Job(Job_ID, Job_Name, Job_Description, Tech_ID7b, Product_ID3b, Employee_Modify7b, Modifying_Date)

  7. Relational Schema • File(File_ID, File_Name, File_Description, Tech_ID7b, Modifier7b, Created_Date, Modifying_Date, Job_ID8) • Procedure(Procedure_ID, Job_ID8, Procedure_Name, Description, File_ID9) • Operation(Operation_ID, Procedure_ID, Job_ID8, Description, Operation_Name, WorkCenter_ID13 , Hardware_ID14) • Department(Department_ID, Department_Name, Department_Location, Department_Phone) • WorkCenter(WorkCenter_ID, WorkCenter_Name, Description, Location, WorkCenter_Capacity, Operating _Hours, Managing_Department12) • Hardware(Hardware_ID, Hardware_Name, Hardware_Description, Hardware_Usage_Period, Hardware_Size, Hardware_Weight, Hardware_Brand, WorkCenter_ID13, Operation_ID11) • Tool_Type(Hardware_ID, Replacement_Period) • Machine_Type(Hardware_ID, Machine_name, Description, Cost) • Tool_Token (Tool_Token_ID, Tool_Token_Name, Quantity, Hardware_ID14a) • Machine_Token(Machine_Token_ID, Machine_Token_Name, Quantity, Hardware_ID) • Product_components(Product_ID3, Component_ID3, Quantity) • Is_authorized_to_use(Hardware_ID14b, Tech_ID7b) • Request (Order_ID2, Product_ID3, quantity) • Is_Composed_Of (Product_ID3, RM_ID6) • LA_Gauge_Account (Payment_ID, Order_ID2, Customer_ID1, Amount, Due_Date, Date_Of_Payment, Penalty) • Machine_Status(Machine_ID14b, Inspection_Date, Status) • Product_Materials(Product_ID14b, RM_ID, Quantity)

  8. RELATIONAL SCHEMA: 1:1 RELATIONSHIP 8. Job(Job_ID, Job_Name, Job_Description, Product_ID3b, Employee_Modify7b, Modifying_Date)

  9. RELATIONAL SCHEMA: 1:N RELATIONSHIP Sales_Associate(Employee_ID, Commission) b. Technician(Employee_ID, Salary) c. Others (Employee_ID, Wage, Job_Duty) 7. Employee(Employee_ID, Lname, Fname, Email, Job_Title, Phone_Address, Streeet_Address, City, State, ZIP, Country, Schedule, Working_Hour, Department_ID12, Starting_Date, Supervisor7)

  10. MS Access Relationship View

  11. Normalization 1 (1NF) • In 1NF: • Procedure(Procedure_ID, Job_ID, Procedure_Name, Description, File_ID) Functional Dependencies: • FD1: {Procedure_ID}  {Procedure_Name} • FD2: {Procedure_ID, Job_ID}  {File_ID} • In 2NF: • Procedure1(Procedure_ID, Job_ID,File_ID) • Procedure2(Procedure_ID, Procedure_Name, Description) • The relations are in 2NF and satisfy both 3NF and BCNF requirements as well

  12. Normalization 2 (1NF) • In 1NF: Operation(Operation_ID, Procedure_ID, Job_ID, Description, Operation_Name, WorkCenter_ID, Hardware_ID) • Functional Dependencies: • FD1: Operation_ID  {Description, Operation_Name} • FD2: {Operation_ID, Procedure_ID, Job_ID}  {WorkCenter_ID, Hardware_ID} • FD3: Hardware_ID  WorkCenter_ID • In 2NF: • Operation1(Operation_ID, Procedure_ID, Job_ID, WorkCenter_ID, Hardware_ID) • Operation2(Operation_ID, Description, Operation_Name)

  13. Normalization 2 (1NF) • In 3NF: • Operation1A(Operation_ID, Procedure_ID, Job_ID, Hardware_ID) • Operation1B(Hardware_ID, Workcenter_ID) • The relations are in BCNF too

  14. Normalization 3 (2NF) • In 2NF: LA_Gauge_Account ( Account_ID, Amount, Date, Order_ID, Customer_ID) Functional Dependencies: • FD1: Account_ID  {Amount, Date, Order_ID, Customer_ID} • FD2: Order_ID  Customer_ID • In 3NF: • LA_Gauge_Account1(Account_ID, Amount, Date, Order_ID) • LA_Gauge_Account 2(Order_ID, CustomerID) • The relations are in BCNF too

  15. Normalization 4 (3NF) • In 3NF Department (Department_ID, Department_Name, Department_Location, Department_Phone) • Functional Dependencies: • FD1: Department_ID  {Department_Name, Department_Location, Department_Phone} • FD2: {Department_Name, Department_Location} Department_ID • FD3: {Department_Phone}  {Department_Location} • In BCNF: • Department1(Department_ID, Department_Name, Department_Phone) • Department2(Department_Phone, Department_Location)

  16. Normalization 5 (BCNF) • In BCNF Order(Order_ID, Order_Date, Customer_ID) Functional Dependencies: FD1: Order_ID  {Order_Date, Customer_ID}

  17. Switchboard

  18. Form: Add new customer

  19. Form: Add new job

  20. Report: Customer Payment Account

  21. Report: Employee salary

  22. QUERY 1: Optimal Order Quantity • Functions: • To indicate if any of the raw material is out of stock. • To compute the optimal order amount using the Economic Oder Quantity (EOQ) • Purposes: • Help the sales department order the out-of-stock items ASAP  minimize any possible delays & start the production process on time • Help decrease the storage cost and increase the company’s cash flow.

  23. MS Access Implementation • EOQ model assumptions: • Fixed cost is 100$ and holding cost is 10% material price • Deterministic demand Q*: optimal order quantity D: the product’s demand rate S: fixed cost per order H: annual holding cost per unit of product

  24. Implementation SELECT sub.*, SQR(2*sub.demand*100/(0.1*sub.rm_cost)) AS reorder_quantity FROM (SELECT rm.rm_id, rm.amount_on_hand, rm.rm_cost, (rm.amount_purchased-rm.amount_on_hand)/DATEDIFF('ww',s.received_date,DATE()) AS demand, s.received_date FROM Raw_Material AS rm LEFT JOIN Ship_In AS s ON s.rm_id=rm.rm_id) AS sub WHERE sub.amount_on_hand<=sub.demand;

  25. QUERY 2 : Machine’s usage time and maintenance schedule • Functions: • To sums up the total usage time of each machine starting from its first operation • To estimate the remaining service time (the survival period) of each machine using the survival analysis. • Purposes: • Help the maintenance team determines when a check-up or a replacement is necessary • Help the financial department and the executive committee in distributing future investment accordingly (since a machine may cost up to million dollars)

  26. Implementation Option Compare Database Private Sub Command0_Click() On Error GoTo Err_Command0_Click Dim mySQL As String mySQL = "SELECT [Inspection_date], [status] INTO m_status FROM machine_status WHERE [machine_id] = 1" DoCmd.RunSQLmySQL DoCmd.TransferTextacExportDelim, TableName:=“m_status", File:="C:\machine_status.csv", HasFieldNames:=True Shell("c:\run_cox_survival.R", 1) Exit_Command0_Click: Exit Sub Err_Command0_Click: MsgBoxErr.Description Resume Exit_Command0_Click End Sub

  27. Future Improvements • Integrate our database with a job scheduling package • Record job start and stop times to determine historic operation duration • Analyze whether SPT or Moore’s Algorithm is more efficient • Provide job tracking report for customers to view online • They would always know where their product was and who was working on it • Could also be used by management to snapshot the factory status • Iterate with test users to obtain feedback and refine the database • Users would identify additional functionality and workflows

  28. QUERY 3: Product’s profitability ranking • Functions: To rank each product based on its service category from the highest profitability to the lowest ones. • Purposes: • The marketing department could recruit more customers interested in purchasing the top profitable products. • The financial department can distribute more investment into advancing the current technology of that given services  improves the product’s quality and attracts more customers.

  29. SQL SELECT ptop.product_id, (ptop.product_sales_price - pc_sub.cost - pm_sub.cost) AS profit FROM (product AS ptop LEFT JOIN (SELECT p.product_id, SUM(p2.product_sales_price * pc.quantity) AS cost FROM ( (product p LEFT JOIN product_components pc ON pc.product_id = p.product_id ) LEFT JOIN product p2 ON pc.component_id = p2.product_id ) GROUP BY p.product_id ) AS pc_sub ON pc_sub.product_id = ptop.product_id) LEFT JOIN (SELECT p.product_id, SUM(rm.rm_cost * pm.quantity) AS cost FROM ( (product p LEFT JOIN product_materials pm ON pm.product_id = p.product_id ) LEFT JOIN raw_material rm ON rm.rm_id = pm.rm_id ) GROUP BY p.product_id ) AS pm_sub ON pm_sub.product_id = ptop.product_id ORDER BY ptop.product_sales_price - pc_sub.cost - pm_sub.cost DESC;

  30. Access Implementation:

  31. QUERY 4: Operating hours and scheduling • Functions: • To sort out all of the different works centers needed to perform a given job and rank them according to the job’s priority • Within one work center, lists out the operating hours of each machine along with the names of the technicians who are authorized to operate that type of machines. • Purposes: • To help the scheduling department ease down the difficulties in scheduling the work centers and the technicians’ working schedule • Increase productivity

  32. SQL (4a & 4b) 4a. SELECT p.procedure_name, p.description, f.file_name FROM [procedure] AS p LEFT JOIN file AS f ON f.File_id=p.File_ID WHERE p.job_id=job; 4b. SELECT o.operation_name, o.description, w.workcenter_name, h.hardware_name FROM (([procedure] AS p LEFT JOIN operation AS o ON o.procedure_id=p.procedure_id) LEFT JOIN workcenter AS w ON w.workcenter_id=o.workcenter_id) LEFT JOIN hardware AS h ON h.hardware_id=o.hardware_id WHERE p.job_id=job;

  33. SQL (4c & 4d) 4c. SELECT e.fname, e.lname, tech.cnt FROM employee AS e LEFT JOIN (SELECT a.tech_id, COUNT(*) AS cnt FROM ((job AS j LEFT JOIN [procedure] AS p ON p.job_id=j.job_id) LEFT JOIN operation AS o ON o.procedure_id=p.procedure_id) LEFT JOIN is_authorized_to_use AS a ON a.hardware_id=o.hardware_id WHERE j.job_id=job GROUP BY a.tech_id) AS tech ON tech.tech_id=e.employee_id WHERE tech.cnt>0 ORDER BY tech.cnt DESC; 4d. SELECT p.product_name, p.description, pc.quantity, p.product_sales_price FROM (job AS j LEFT JOIN product_components AS pc ON pc.product_id=j.product_id) LEFT JOIN product AS p ON p.product_id=pc.component_id WHERE j.job_id=job;

  34. Access Implementation: List out the work center & operation

  35. Access Implementation: List out the work center & operation

  36. Access Implementation: List out the procedures & files

  37. List out the employees authorized to use a certain hardware

  38. List out the employees authorized to use a certain hardware (cont)

  39. List out which job correspond to which product

  40. QUERY 5: Sales Associate’s effectiveness ranking • Function: To rank all of the sales associates (SA) according to their effectiveness index, which is calculated as the ratio of the profits made from all of the orders that employee makes during that year to their base salary. • Function: • Help the human resources department compute the employees’ year-end bonuses and reward the SAs with a high effectiveness index accordingly.

  41. SQL SELECT e.fname, e.lname, sub.total_sales / e.salary AS profitability FROM (SELECT c.primary_liason, SUM(o_amt.order_total) AS total_sales FROM ( ( [order] o LEFT JOIN ( SELECT r.order_id, SUM(r.quantity * p.product_sales_price) AS order_total FROM request r LEFT JOIN product p ON p.product_id = r.product_id GROUP BY r.order_id ) o_amt ON o.order_id = o_amt.order_id ) LEFT JOIN [customer] c ON o.customer_id = c.customer_id ) WHERE o.order_date >= DATEADD('yyyy', -3, DATE()) GROUP BY c.primary_liason ) AS sub LEFT JOIN employee AS e ON e.employee_id = sub.primary_liason ORDER BY sub.total_sales / e.salary DESC;

  42. Access Implementation:

  43. Question?

  44. Thank You

More Related