Transcript Slide 1

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
Query implementation
Future improvement



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




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.
1.
2.
3.
4.
5.
6.
7.
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)
1.
2.
Purchased(Product_ID, Purchased_Cost, Purchased_Date, Supplier_ID5)
In-House(Product_ID, Manufacturing_Cost, Manufacturing_Date,)
1.
2.
Ship_In(Shipment_ID, Supplier_ID5, Received_Date, Receiving_Employee7, RM_ID6)
Ship_Out(Shipment_ID, Customer_ID1, Shipped_Date, Shipping_Cost)
Shipment(Shipment_ID, Tracking_Number, EID7a)
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)
a) Sales_Associate(Employee_ID, Commission)
b) Technician(Employee_ID)
c) Other(Employee_ID, Job_Duty)
8.
Job(Job_ID, Job_Name, Job_Description, Tech_ID7b, Product_ID3b,
Employee_Modify7b, Modifying_Date)
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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)
9.
Tool_Type(Hardware_ID, Replacement_Period)
10.
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: 1:1 RELATIONSHIP
8. Job(Job_ID, Job_Name, Job_Description, Product_ID3b,
Employee_Modify7b, Modifying_Date)

RELATIONAL 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_ID12, Starting_Date,
Supervisor7)
a. Sales_Associate(Employee_ID,
Commission)
b. Technician(Employee_ID, Salary)
c. Others (Employee_ID, Wage, Job_Duty)

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

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)

In 3NF:
◦ Operation1A(Operation_ID, Procedure_ID, Job_ID,
Hardware_ID)
◦ Operation1B(Hardware_ID, Workcenter_ID)

The relations are in BCNF too

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

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)

In BCNF
Order(Order_ID, Order_Date, Customer_ID)
Functional Dependencies:
FD1: Order_ID  {Order_Date, Customer_ID}
Switchboard
Form: Add new customer
Form: Add new job
Report: Customer Payment Account
Report: Employee salary

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.

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
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_purchasedrm.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;


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 checkup 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)
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.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 Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub


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


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.
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;

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
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;
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;
Access Implementation: List out the
work center & operation
Access Implementation: List out the
procedures & files
List out the employees authorized to
use a certain hardware
List out the employees authorized to
use a certain hardware (cont)
List out which job correspond to
which product
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.
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;