Final Presentation

1. Mechanical Engineering Machine shop Final Presentation Group 7 Ricardo Ambriz, Ryan Cabrera, Jin Jang, JiYang Liu, Justin Nakamura, YanWen Wang, Justin Wu

2. Overview • Client Description • EER Diagram/Previous DP Review Results • Relations • Normalization • Queries and Implementation • Sample Forms

3. Students and Faculties Machine Shop • Largest Manufacturing machine shop on campus • Prepare students for physical design and prototype courses • Work station of competitive teams such as Berkeley Solar Car Team and Civil Engineering Bridge Building Team, etc. • Also provides the facility for certain paid projects.

4. What’s Expected From Us • Needs: • Assign schedules to students for different training phase on different date and time • Assign and update schedules for the many student organizations that use the machine shop for projects • Keep in check with the depreciation of the machines and tools they have in the shop • Record all the repair history about the machine and tools • Record the inflow and outflow of the materials • Wants: • Find an optimal solution so that a maximum of 200 students can be trained • Prevent random students from signing up and taking up time slots • Ensure both administrators and students can access the information, but have different securities so that students can only access limited information • Enable students access of the training schedule and sign ups online

7. Relations 1. PERSON(PID, Fname, MI, LName, BDATE, Email, Phone#, Address) 1a) ALUMNI(AlumID1­, Grad_date, Grad_major) 1b) STUDENT(StudentID1­­, SID, Major, Minor, Class, Basic_Training_Done) 1c) STAFF(StaffID1,Job_Title, Salary) 1d) PROF(ProfessorID1, Department, Specialization,Tenure) 2. AUTHORIZED_FOR(Pid1, MachineID5) 3. MAINTENANCE(MachineID5, TID47, StaffID1c, Description, Cost) 4. PROJECT(ProjectID, Project_Name, Description, ProfessorID1d, ProjectTeamID4,MachineListID17) 4a) PAID(ProjectID3, Amount) 4b) CLASS(ProjectID3,CID, Semester, Year) // CID is like CCN from telebears 4c) ORG(ProjectID3, Organization_Name) 5. PROJECTTEAM (ProjectTeamID, Team_Member1, Team_Name, Sponsoring_Department, Team_Leader_PID1, Team_Leader_Contact#)

8. Relations 6. MACHINE(MachineID, Manufacturer, Purchase_Date, Machine_Type, Price, Width, Length, Height) 7. MACHINEPART(MachineID6, MachinePartID, Name, Type, Model, Size, Manufacturer) 8. DRILL_PRESS(MachineID6, Condition, Variable_speeds, Table_functionality, Quill_Travel, Depth-stops, Horsepower, Size/Center_drilling_capacity) 9. ENGINE_LATHE(MachineID6,Condition, Accessory, RPM) 10. MILLING(MachineID6, Type, Category, Electronic_Layout_System, Self-contained_electronic_drive, Coolant_system, Variable_spindle_speeds) 11. PEDESTAL_GRINDER(MachineID6,Category, Accessory, RPM) 12. BELT_SANDER(MachineID6,Power, Variable_Speed, Release_Lever_Type, Tracking_Control, Dust_Collection, Noise_Level) 13. RADIAL_DP(MachineID6, Condition, Variable_speeds, Table_functionality, Quill_Travel, Depth-stops, Horsepower, Size/Center_drilling_capacity) 14. VERTICAL_BANDSAW(MachineID6,Bandsaw_Speed, Range) 15. TOOL_SHARPENER(MachineID6, RPM) 16. HORZ_BANDSAW(MachineID6,Automatic_Feed_System, Hydraulic_Control_System) 17. MACHINELIST(MachineListID, MachineID6)

9. Relations 18. BASICTRAINING(BasicTrainID,TID20, Phase, MachineListID17, StaffID1c) 19. BASICENROLLMENT(SID1b, BasicTrainID18) 20. ADVANCEDTRAINING(AdvancedTrainID, TID20, Description, MachineListID17, StaffID1c) 21. ADVANCEDENROLLMENT(PID1,AdvancedTrainID18) 22. TIME_SLOT(TID,YEAR,MONTH,DAY,HOUR:MINUTE) 23. MACHINE_LOG(TID22, Login, Logout, PID1, ProjectID4, TrainID18, Minutes_past_hour)

11. Normalization First Normal Form: PERSON(PID, Fname, MI, LName, BDATE, Email, Phone#, Address) Relation PERSON is not in 1NF because Email, Phone#, Address (Permanent and Local) could all be multi-valued attributes. To normalize to 1NF: PERSONINFO(PID, Fname, MI, Lname, BDATE) PEmails(PID, Email) PPhones(PID, Phone#) PAddresses(PID, Address)

12. Normalization Second Normal Form: MACHINE(MachineID, Manufacturer, Purchase_Date)  MACHINEPART(MachineID6, MachinePartID, Name, Type, Model, Size, Manufacturer) Consider the weak entity MACHINEPART: It is in 2NF because it is in 1NF and every non-prime attribute is fully dependent on the Primary Key, no non-prime attribute can be determined form a subset of the PK.

13. Normalization Third Normal Form: PROJECTTEAM (ProjectTeamID,Team_Member1, Team_Name, Sponsoring_Department, Team_Leader_PID1, Team_Leader_Contact#) Consider the relation PROJECTTEAM in 1NF: PROJECTTEAM (ProjectTeamID, Team_Name, Sponsoring_Department, Team_Leader_PID1, Team_Leader_Contact#) PTMEMBERS(ProjectTeamID, PID) The relation PROJECTTEAM violates 3NF, to normalize: PROJECTTEAM (ProjectTeamID, Team_Name, Sponsoring_Department, Team_Leader_PID1) MAINCONTACT (Team_Leader_PID1, Team_Leader_Contact#)

14. Query 1-To Determine Monthly Demand Di SELECT [Time Slot].Month, Count([Advanced Training Enrollment].AdvancedTrainID)*2 AS CountOfAdvancedTrainIDFROM [Time Slot], [Advanced Training Enrollment], Advanced_TrainingWHERE (((Advanced_Training.TID)=[Time Slot].[TID]) AND ((Advanced_Training.AdvancedTrainID)=[Advanced Training Enrollment].[AdvancedTrainID]))GROUP BY [Time Slot].Month; SELECT [Time Slot].Month, Count([Basic Training Enrollment].BasicTrainID) AS CountOfBasicTrainID123FROM [Time Slot], [Basic Training Enrollment], [Basic Training]WHERE ((([Basic Training].TID)=[Time Slot].[TID]) AND (([Basic Training].BasicTrainID)=[Basic Training Enrollment].[BasicTrainID])) AND ([Basic Training].[Phase] = 1 OR [Basic Training].[Phase] = 2 OR [Basic Training].[Phase] = 3)GROUP BY [Time Slot].Month; SELECT [Time Slot].Month, Count([Basic Training Enrollment].BasicTrainID)*2 AS CountOfBasicTrainID4FROM [Time Slot], [Basic Training Enrollment], [Basic Training]WHERE ((([Basic Training].TID)=[Time Slot].[TID]) AND (([Basic Training].BasicTrainID)=[Basic Training Enrollment].[BasicTrainID])) AND [Basic Training].[Phase] = 4GROUP BY [Time Slot].Month;

15. Query1 • Advanced_Sessions_Hours + CountOfBasicTrainID123 + CountOfBasicTrainID4 = Di , (i in months) • Take data from several years to find correlation between number of students and Di • Forecast next year’s number of students to find Di for next year. N­f: Number of Full-Time Instructors in Month i NT: Number of Temporary Instructors in Month i FF , FT: Initial Hiring Cost for Full-Time, Temporary Staff PF , PT: Pay per Hour for Full-Time, Temporary Staff HF , HT: Hours per Month worked for Full-Time, Temporary Staff Minimize Cost: PFHF(NF1+…+NF12) + PTHT(NT1+…+NT12) + FF(NewF1+…+NewF12) + FT(NewT1+…+NewT12) Subject to Required_Work {i = 1, 2,…,12}: HFNFi + HTNTi >= Di Subject to New_Hires {i = 2,3,…,12}: NFi = NF(i – 1) +NewFi Subject to Minimum_F: NFi >= 1 Subject to Minimum_T: NTi >= 0

16. Query 1

17. Query 2 Find the average machine shop demand for each hour SELECT [Time Slot].Hour, Count(Person.PID) AS TotalPeople, Max([Time Slot].TID) AS MAXH, [TotalPeople]/(([MAXH]+(24-[Time Slot].[Hour]))/24) AS AvgMachineDemand FROM [Time Slot] INNER JOIN (Person INNER JOIN [Machine Log] ON Person.PID = [Machine Log].PID) ON [Time Slot].TID = [Machine Log].TID GROUP BY [Time Slot].Hour;

18. Query 2

19. Query 2

20. Query 2

21. Query 3 Order students by how many basic training phases they are enrolled for now. Then predict, based on current enrollment, each student's expected schedule time for their phase 4 (last phase) enrollment to complete the basic training. CREATE TABLE [PHASE] AS Select s.SID, s.Fname, s.Lname, count(*) as num_phase_enrolled From STUDENT s, BASICENROLLMENT be, Where s.SID=be.SID Group by s.SID Order by count(*);

22. Query 3 For num_phase_enrolled = 1, not enough information to calculate the expected phase 4 enrollment schedule. For num_phase_enrolled = 4, phase 4 enrollment is already scheduled. For num_phase_enrolled = 2 and 3, do the following query to calculate the expected phase 4 enrollment schedule. CREATE VIEW [EXP_TID1] AS Select ph.SID, ph.Fname, ph.Lname, (bt2.TID - bt1.TID)*3+bt.TID as exp_TID From PHASE ph, BASICENROLLMENT be, BASICTRAINING bt1, BASICTRAINING bt2, TIME_SLOT ts Where ph.phase_done = 2 AND ph.SID = be.SID AND be.basictrainID = bt1. basictrainID AND be.basictrainID = bt2. basictrainID AND bt1.TID < bt2.TID;

23. Query 3 CREATE VIEW [EXP_TID2] AS Select ph.SID, ph.Fname, ph.Lname, (bt3.TID - bt1.TID)*3/2+bt.TID as exp_TID From PHASE ph, BASICENROLLMENT be, BASICTRAINING bt1, BASICTRAINING bt2, BASICTRAINING bt3, TIME_SLOT ts Where ph.phase_done = 3 AND ph.SID = be.SID AND be.basictrainID = bt1. basictrainID AND be.basictrainID = bt2. basictrainID AND be.basictrainID = bt3. basictrainID AND bt1.TID < bt2.TID AND bt2.TID < bt3.TID;

24. Query 3 CREATE VIEW [EXP_TID] AS Select * From EXP_TID1, EXP_TID2; Select *, ts.year, ts.month, ts.day, ts.hour From EXP_TID et, TIME_SLOT ts Where et.exp_TID = ts.TID;

25. Query 4 For each machine, estimate the number of usage hours remaining until the next maintenance service should be performed. Create view [result1] as Select m.machinetype, mt.tid From machine m, maintenance mt Where m.machineid = mt.machineid Create view [result2] as Select m.machinetype, avg(r1.tid-r2.tid) as AvgMaint From result1 r1, result1 r2 Where r1.tid>r2.tid Group by m.machinetype

26. Query 4 Select ml.machineid, sum(ml.logout – ml.login) as NumHoursUsed, r.AvgMaint as AvgMaint, (AvgMaint – NumHoursUsed) as HoursRemaining From machinelog ml, result2 r, machine m Where r.machinetype = m.machinetype AND ml.machineid = m.machineid Group by ml.machineid

27. Sample Forms

28. Sample Forms

29. Sample Forms

30. ME MACHINE SHOP Q&A