Database( 第二學期 ) 期中報告三光眼鏡店管理系統專案

Database(第二學期)期中報告三光眼鏡店管理系統專案Database(第二學期)期中報告三光眼鏡店管理系統專案指導老師：汪維揚主任組長：1099137125 姜義發組員：1099137145 宋凱豐 1099137140 潘政宗 1099137134 葉庭宇 1099137121 司徒仲謙

ERD圖

資料成長率Growth Rate

檔案規格書(DD)

系統畫面(screen layout)SQLs 此為三光眼鏡內部系統的首頁，三光資訊、顧客驗光資料、送修服務紀錄、代購商品服務都可以在此連結。

顧客驗光相關資料

顧客資料輸入系統 顧客資料輸入SOLINSERT INTO customers VALUES (0000000001,'王曉明','0916395245','高雄市鳳山區XX里XX路XX巷X號','1991/5/3','Fa251468@yahooicom.tw');

驗光表 輸入系統驗光表輸入SQLINSERT INTO oplists VALUES (0000000001,'1.0','1.0','否','PD64');

查詢驗光紀錄 查詢驗光紀錄SQL Select Customer_name,Customer_Phone,Opr_time,Employee_Name,Op_Rvision,Op_Lvision,Op_ Astigmatism,Op_ Focal length From customers c,oprecordsopr,employeese,oplists o Where c.Customer_id=opr.Customer_id and e.Employee_id=opr.Employee_id and o.Op_id=opr.Op_id and Customer_name="王曉明" and Customer _Phone="0916596230" and to_char(Opr_time,'yyyy-mm-dd')=1991-02-15

顧客資料修改系統 顧客資料修改系統SQLUPDATE customers SET Customer_id ='000000000004' SET Customer _Name ='ㄆㄆㄆ' SET Customer _Phone ='09659445' SET Customer _Addr ='高雄市鳳山區XX里XX路XX巷X號' SET Customer _Brithday ='1991/5/6' SET Customer _Mail ='f49684FWE46@yahoo.com.tw' where Customer _Name ='王曉明' and Customer_phone ='0916596230'

送修服務紀錄

新增新客戶系統 新增客戶資料 SQL:INSERT INTO customersVALUES('0000000001', '吳小名', '0922456345', '高雄市三民區')

維修單 輸入系統 SQL:新增維修資料INSERT INTO maintens VALUES(' 2012/4/25' , '2012/4/30 ')新增維修服務員工INSERT INTO employees VALUES(' 派大星 ' , ' 0912634921')新增維修商品INSERT INTO maintenprs VALUES('白色鏡片' , ' $4500 ')新增維修廠商INSERT INTO maintenprs VALUES('想像力工廠' , ' 0912635921')

維修單查詢系統 SQL:查詢某顧客的維修紀錄Select Customer_id,Customer _Name,Customer _Phone,Customer _Address ,Me_Cd_Name,Me_Cd_Price,Me_Ft_Name,Me_Ft_PhoneFROM customers c, maintens m, maintenfas p, maintenprs pWHERE (m. Customer_id=c. Customer _id)AND(c. Customer _Name= '欲查詢之顧客名稱')AND(e.Employee_Phone='欲查詢之顧客手機號碼')AND(m.Employee_id= e.Employee_id) ORDER BY m. maintens_id查詢某員工的維修服務紀錄Select Employee_name,Employee_phone,Me_Cd_Name,Me_Cd_Price,Me_Ft_Name,Me_Ft_PhoneFROM customers c, maintens m, maintenfas p, maintenprs pWHERE (e. Employee_id=m. Employee_id)AND(e.Employee= '欲查詢之員工編號')ORDER BY m. maintens_id

代購紀錄

代購單

SQL: • 查詢某顧客的代購紀錄 • Select C.customer_name ,C.customer_phone , O.Order_id ,O.order_date,O.order_quentity ,O.order_price*O.order_quentity as total,O.len_id • From Orders O, Customers C • Where (O.customer_id = C.customer_id ) and (C.customer_name = ‘欲查詢之顧客名稱’ ) and (C.customer_phone = ‘欲查詢之顧客電話’)) • Order by O.order_id • 查詢某員工代購服務紀錄 • Select E.employee_id , E.employee_name , O.order_id,O.order_date • From Orders O , employees E • Where (O.employee_id = E.employee_id) and (employee_id = ‘欲查詢之員工編號’ ) • Order by O.order_id • 查詢鏡片供應商所提供的鏡片中，有關某個鏡片樣式且價格在限定範圍內之鏡片相關資料 • Select V.vender_id,V.vender_name,V.vender_phone,L.len_id,L.len_name,L.len_model, • V.price • From Venders V ,Lens L • Where ( V.len_id = L.len_id ) and ( L.len_model = ’樣式1’) • and (V.price between ‘價錢1’ and ‘價錢2’) • order by V.price • 代購單 • Insert into Orders • VALUES ( • (Select MAX(order_id)+1 From Order_id), • (Select V.price *Q*1.25 From Venders V Where (V.vender_name = ’供應商名稱’) and (V.vender_phone = ‘供應商電話號碼’) ), • (Select V.price *Q*0.625 From Venders V Where (V.vender_name = ’供應商名稱’) and (V.vender_phone = ‘供應商電話號碼’) ), • (Select sysdate From Deul), • ‘ ‘ , • Q, • e_id, • (Select customer_id From customers Where (customer_name = ‘欲訂購鏡片之顧客名稱’) and (customer_phone = ‘欲訂購鏡片之顧客電話號碼’)), • (Select len_id Form Lens Where (len_name = ’鏡片名稱’) and (len_model = ‘鏡片樣式’) )

報表(reports) 查詢驗光紀錄SQL(假設姓名輸入KEVN 手機0916484618 日期 1991/5/3) 查詢某顧客的維修紀錄(假設顧客名稱為王小名顧客電話為0922043241) 查詢某員工的維修紀錄(假設員工編號為 00004532) 查詢某顧客的代購紀錄(假設輸入顧客名稱為王大明顧客電話為012321312) 查詢某員工代購服務紀錄(假設輸入之員工編號為0000000010)

查詢鏡片供應商所提供的鏡片中，有關某個鏡片樣式且價格在限定範圍內之鏡片相關資料(假設樣式為黑中帶黑 價格介於0~499之間)

END我們的報告到此結束。謝謝大家

Database( 第二學期 ) 期中報告三光眼鏡店管理系統專案