Benn Ackley Ajay Bawa Clarence Cheung Steven Leonard Nhat Nguyen Edrick Soetanto Company Background Customer Request EER Diagram Relational Schema Normalization Analysis Forms and Reports ID: 792141
Download The PPT/PDF document "Team #2 A DATABASE FOR LA GAUGE COMPANY" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Team #2A DATABASE FOR LA GAUGE COMPANY
Benn Ackley
Ajay Bawa
Clarence Cheung
Steven Leonard
Nhat Nguyen
Edrick Soetanto
Slide2Company BackgroundCustomer RequestEER Diagram
Relational Schema
Normalization AnalysisForms and ReportsQuery implementationFuture improvement
Today’s Agenda
Slide3Location: 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
Company Background
Slide4Current database: Delmar- a 16-bit legacy database
Difficulty with current database:
Slow and unreliableMany glitchesHigh 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.
Customer Request
Slide5EER Diagram
Slide6Relational Schema
Customer (
Customer_ID, Company, Lname, Fname, Email, Job_Title, Business_Phone, Street_Address, City, State, ZIP, Country, Web_Page, Primary_liaison
7
)Order(Order_ID, Order_Date, Customer_ID
1)Product(
Product_ID
, Product_Name, Description, Product_Sales_Price, Amount_On_Hand, Order_ID
2
)
Purchased(Product_ID, Purchased_Cost, Purchased_Date, Supplier_ID
5
)
In-House(Product_ID, Manufacturing_Cost, Manufacturing_Date,)
Shipment(
Shipment_ID
, Tracking_Number, EID
7a
)
Ship_In(
Shipment_ID,
Supplier_ID
5
, Received_Date, Receiving_Employee
7
, RM_ID
6
)
Ship_Out(
Shipment_ID
, Customer_ID
1
, 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_ID
23
)
Raw_Material(
RM_ID
, RM_Name , Description, RM_Cost, Supplier_ID
5
, Shipment_ID
4
, Account_ID
23
, Amount_On_Hand, Units, Amount_Purchase
)
Employee(
Employee_ID
, Lname, Fname, Email, Phone, Streeet_Address, City, State, ZIP, Country, Schedule, Working_Hour, Department_ID
12
, Starting_Date, Salary, Supervisor
7
)
Sales_Associate(
Employee_ID
, Commission)
Technician(
Employee_ID
)
Other(
Employee_ID
, Job_Duty)
Job(
Job_ID
, Job_Name, Job_Description, Tech_ID
7b
, Product_ID
3b
, Employee_Modify
7b
, Modifying_Date)
Slide7File(File_ID, File_Name, File_Description, Tech_ID
7b
, Modifier7b, Created_Date, Modifying_Date, Job_ID8)
Procedure(
Procedure_ID, Job_ID8, Procedure_Name, Description, File_ID
9)Operation(
Operation_ID, Procedure_ID, Job_ID
8
,
Description, Operation_Name, WorkCenter_ID
13
, Hardware_ID
14
)
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)
Relational Schema
Slide8RELATIONAL SCHEMA:
1:1 RELATIONSHIP
8. Job(
Job_ID
, Job_Name, Job_Description,
Product_ID
3
b
,
Employee_Modify
7b
, Modifying_Date)
Slide9RELATIONAL SCHEMA: 1:N RELATIONSHIP
7.
Employee
(
Employee_ID
, Lname, Fname, Email, Job_Title, Phone_Address, Streeet_Address, City, State, ZIP, Country, Schedule, Working_Hour, Department_ID
12
, Starting_Date, Supervisor
7
)
Sales_Associate(
Employee_ID
, Commission)
b. Technician(
Employee_ID, Salary)c. Others (Employee_ID, Wage, Job_Duty)
Slide10MS Access Relationship View
Slide11In 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 wellNormalization 1 (1NF)
Slide12In 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)
Normalization 2 (1NF)
Slide13In 3NF:Operation1A(Operation_ID,
Procedure_ID
, Job_ID, Hardware_ID)
Operation1B(
Hardware_ID,
Workcenter_ID)The relations are in BCNF too
Normalization 2 (1NF)
Slide14In 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_IDIn 3NF:
LA_Gauge_Account1(
Account_ID,
Amount, Date, Order_ID)
LA_Gauge_Account 2(
Order_ID
, CustomerID)
The relations are in BCNF too
Normalization 3 (2NF)
Slide15In 3NFDepartment (
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)
Normalization 4 (3NF)
Slide16In BCNFOrder(Order_ID, Order_Date, Customer_ID)
Functional Dependencies:
FD1: Order_ID {Order_Date, Customer_ID}
Normalization 5 (BCNF)
Slide17Switchboard
Slide18Form: Add new customer
Slide19Form: Add new job
Slide20Report: Customer Payment Account
Slide21Report: Employee salary
Slide22Functions: 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.
QUERY 1: Optimal Order Quantity
Slide23EOQ model assumptions:Fixed cost is 100$ and holding cost is 10% material priceDeterministic demand
Q*: optimal order quantity
D: the product’s demand rate
S: fixed cost per order
H: annual holding cost per unit of product
MS Access Implementation
Slide24SELECT 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 subWHERE sub.amount_on_hand<=sub.demand; Implementation
Slide25Functions: To sums up the total usage time of each machine starting from its first operationTo 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)
QUERY 2 : Machine’s usage time and maintenance schedule
Slide26Option 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.RunSQL
mySQL DoCmd.TransferText acExportDelim, TableName:=“m_status", File:="C:\machine_status.csv", HasFieldNames:=True Shell("c:\run_cox_survival.R", 1)Exit_Command0_Click: Exit SubErr_Command0_Click: MsgBox Err.Description Resume Exit_Command0_Click End SubImplementation
Slide27Integrate our database with a job scheduling packageRecord job start and stop times to determine historic operation durationAnalyze 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 itCould 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
Future Improvements
Slide28Functions: 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.
QUERY 3: Product’s profitability ranking
Slide29SELECT ptop.product_id, (ptop.product_sales_price - pc_sub.cost - pm_sub.cost) AS profitFROM (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;
SQL
Slide30Access Implementation:
Slide31Functions: 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
QUERY 4: Operating hours and scheduling
Slide324a. 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;
SQL (4a & 4b)
Slide334c.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>0ORDER BY tech.cnt DESC;
4d.
SELECT p.product_name, p.description, pc.quantity, p.product_sales_priceFROM (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;
SQL (4c & 4d)
Slide34Access Implementation: List out the work center & operation
Slide35Access Implementation: List out the work center & operation
Slide36Access Implementation: List out the procedures & files
Slide37List out the employees authorized to use a certain hardware
Slide38List out the employees authorized to use a certain hardware (cont)
Slide39List out which job correspond to which product
Slide40Function: 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.
QUERY 5: Sales Associate’s effectiveness ranking
Slide41SELECT e.fname, e.lname, sub.total_sales / e.salary AS profitabilityFROM (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;
SQL
Slide42Access Implementation:
Slide43Question?
Slide44Thank You