中山資管所 碩專班

1、中山資管所 碩專班資料庫系統(tǒng)專題 第五組 專案二鋼板加熱爐程控電腦指導(dǎo)教授：黃三益 博士組長：洪敏鴻 n954020009組員：黃俊源 n954020027組員：林忠誠 n964020001組員：孫盟格 n964020015目錄一. 資料和功能需求分析P.3二. 實體關(guān)係圖（ERD） P.4三. 關(guān)聯(lián)綱目P.5四. SQL敘述 P.7五. 網(wǎng)頁畫面HardCopyP.15六. 系統(tǒng)架構(gòu)圖P.24七. 系統(tǒng)所使用的工具P.25八. 心得P.26一、資料和功能需求分析鋼板加熱爐工場概述 本工場為XX鋼鐵股份有限公司鋼板加熱爐工場，主要是將鋼板送入加熱爐中，依據(jù)鋼板材質(zhì)特性指定加熱方式，可改變鋼板之

2、內(nèi)部結(jié)構(gòu)，進(jìn)而增進(jìn)鋼板強度。本次專案是建置鋼飯加熱爐工場程控電腦系統(tǒng)，增進(jìn)生產(chǎn)之自動化。程控應(yīng)用軟體系統(tǒng)可區(qū)分成下列各功能項目：1. 比對待入爐鋼板操作條件。2. 抓取進(jìn)(出)爐時間。3. 顯示、記錄、查詢各項生產(chǎn)資訊。4. 決定鋼板出爐時機，並提醒操作人員。5. 儲存、管理(編輯修改、刪除)待生產(chǎn)資訊。6. 建立各種升、降溫(控制)曲線，及空爐時之控溫。7. 操作警示。二、ERD鋼板加熱爐 :$ 程控電腦接收鋼板PDI資料，包含鋼板ID、產(chǎn)出日期、鋼板材質(zhì)、加熱樣板編號等。$ 操作人員包含操作人員ID、姓名、姓別、地址等。$ 分三班操作，包含班別、操作人員ID等。$ 加熱樣板包含加熱樣板編號

3、、初溫、初溫時間、中溫、中溫時間、末溫、末溫時間等。$ 操作人員建立鋼板入爐排程，包含班別、流水號、鋼板ID等。$ 鋼板加熱之後，回拋PDO資料，包含鋼板ID、產(chǎn)出日期、加熱日期、鋼板材質(zhì)、加熱樣板、加熱班別等。三、關(guān)聯(lián)綱目四、SQL敘述建立資料表:CREATE TABLE Shift( ShiftId VARCHAR2(16),Description VARCHAR2(32) ,CONSTRAINT Shift_PK PRIMARY KEY ( ShiftId )CREATE TABLE OPERATOR(OperatorId VARCHAR2(8),Name VARCHAR2(8),Sex

4、 CHAR(1),Address VARCHAR2(127),CONSTRAINT Operator_PK PRIMARY KEY ( OperatorId )CREATE TABLE ShiftMember(ShiftId VARCHAR2(16),OperatorId VARCHAR2(8) ,CONSTRAINT ShiftMember_PK PRIMARY KEY ( ShiftId ,OperatorId ),CONSTRAINT ShiftMember_ShiftId_FK FOREIGN KEY ( ShiftId ) REFERENCES Shift( ShiftId ),CO

5、NSTRAINT ShiftMember_OperatorId_FK FOREIGN KEY ( OperatorId ) REFERENCES OPERATOR( OperatorId )CREATE TABLE Recipe(RecipeId VARCHAR2(16),BegTemp NUMBER,BegDuration NUMBER,MidTemp NUMBER,MidDuration NUMBER,EndTemp NUMBER,EndDuration NUMBER,CONSTRAINT Recipe_PK PRIMARY KEY ( RecipeId )CREATE TABLE Pdi

6、(PlateId VARCHAR2(16),ProdDate DATE,Material VARCHAR2(32),RecipeId VARCHAR2(16),CONSTRAINT Pdi_PK PRIMARY KEY ( PlateId ),CONSTRAINT Pdi_RecipeId_FK FOREIGN KEY ( RecipeId ) REFERENCES Recipe( Recipeid )CREATE TABLE Schedule(ShiftId VARCHAR2(16),BatchId VARCHAR2(16),PlateId VARCHAR2(16),CONSTRAINT S

7、chedule_PK PRIMARY KEY ( ShiftId ,BatchId ),CONSTRAINT Schedule_ShiftId_FK FOREIGN KEY ( ShiftId ) REFERENCES Shift( ShiftId ),CONSTRAINT Schedule_PlateId_FK FOREIGN KEY ( PlateId ) REFERENCES Pdi( PlateId )CREATE TABLE Pdo(PlateId VARCHAR2(16),ProdDate DATE,ProcDate DATE,ProcShiftId VARCHAR2(16),Ma

8、terial VARCHAR2(32),RecipeId VARCHAR2(16),CONSTRAINT Pdo_PK PRIMARY KEY ( PlateId ),CONSTRAINT Pdo_PlateId_FK FOREIGN KEY ( PlateId ) REFERENCES Pdi( PlateId ),CONSTRAINT Pdo_RecipeId_FK FOREIGN KEY ( RecipeId ) REFERENCES Recipe( RecipeId )刪除資料表: DROP TABLE Pdo;DROP TABLE Schedule;DROP TABLE Pdi;DR

9、OP TABLE Recipe;DROP TABLE ShiftMember;DROP TABLE OPERATOR;DROP TABLE Shift;輸入資料:- INSERT Shift data-INSERT INTO Shift VALUES ( 'SPH-MO' ,'PlateHeatingSection-MorningShift' );INSERT INTO Shift VALUES ( 'SPH-MI' ,'PlateHeatingSection-MiddleShift' );INSERT INTO Shift VA

10、LUES ( 'SPH-NI' ,'PlateHeatingSection-NightShift' );- INSERT Operator data-INSERT INTO Operator VALUES ( 'SPH07001' ,'Op07001' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07002' ,&

11、#39;Op07002' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07003' ,'Op07003' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( '

12、SPH07004' ,'Op07004' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07005' ,'Op07005' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operato

13、r VALUES ( 'SPH07006' ,'Op07006' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07007' ,'Op07007' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INS

14、ERT INTO Operator VALUES ( 'SPH07008' ,'Op07008' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07009' ,'Op07009' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, T

15、aiwan' );INSERT INTO Operator VALUES ( 'SPH07010' ,'Op07010' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07011' ,'Op07011' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohs

16、iung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07012' ,'Op07012' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07013' ,'Op07013' ,'M' ,'No.70, Lianhai Rd., Gushan

17、 District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07014' ,'Op07014' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07015' ,'Op07015' ,'M' ,'No.70, Lia

18、nhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07016' ,'Op07016' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07017' ,'Op07017' ,'M'

19、,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07018' ,'Op07018' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07019' ,'Op07019

20、9; ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07020' ,'Op07020' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07021'

21、,'Op07021' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07022' ,'Op07022' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 

22、9;SPH07023' ,'Op07023' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07024' ,'Op07024' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Opera

23、tor VALUES ( 'SPH07025' ,'Op07025' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );INSERT INTO Operator VALUES ( 'SPH07026' ,'Op07026' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );I

24、NSERT INTO Operator VALUES ( 'SPH07027' ,'Op07027' ,'M' ,'No.70, Lianhai Rd., Gushan District, Kaohsiung City 804, Taiwan' );- INSERT ShiftMember-INSERT INTO ShiftMember VALUES ( 'SPH-MO' ,'SPH07001');INSERT INTO ShiftMember VALUES ( 'SPH-MI' ,

25、'SPH07002');INSERT INTO ShiftMember VALUES ( 'SPH-NI' ,'SPH07003');INSERT INTO ShiftMember VALUES ( 'SPH-MO' ,'SPH07004');INSERT INTO ShiftMember VALUES ( 'SPH-MI' ,'SPH07005');INSERT INTO ShiftMember VALUES ( 'SPH-NI' ,'SPH07006

26、9;);INSERT INTO ShiftMember VALUES ( 'SPH-MO' ,'SPH07007');INSERT INTO ShiftMember VALUES ( 'SPH-MI' ,'SPH07008');INSERT INTO ShiftMember VALUES ( 'SPH-NI' ,'SPH07009');INSERT INTO ShiftMember VALUES ( 'SPH-MO' ,'SPH07010');INSERT INTO

27、ShiftMember VALUES ( 'SPH-MI' ,'SPH07011');INSERT INTO ShiftMember VALUES ( 'SPH-NI' ,'SPH07012');INSERT INTO ShiftMember VALUES ( 'SPH-MO' ,'SPH07013');INSERT INTO ShiftMember VALUES ( 'SPH-MI' ,'SPH07014');INSERT INTO ShiftMember VALU

28、ES ( 'SPH-NI' ,'SPH07015');INSERT INTO ShiftMember VALUES ( 'SPH-MO' ,'SPH07016');INSERT INTO ShiftMember VALUES ( 'SPH-MI' ,'SPH07017');INSERT INTO ShiftMember VALUES ( 'SPH-NI' ,'SPH07018');INSERT INTO ShiftMember VALUES ( 'SPH-MO

29、' ,'SPH07019');INSERT INTO ShiftMember VALUES ( 'SPH-MI' ,'SPH07020');INSERT INTO ShiftMember VALUES ( 'SPH-NI' ,'SPH07021');INSERT INTO ShiftMember VALUES ( 'SPH-MO' ,'SPH07022');INSERT INTO ShiftMember VALUES ( 'SPH-MI' ,'SPH0

30、7023');INSERT INTO ShiftMember VALUES ( 'SPH-NI' ,'SPH07024');INSERT INTO ShiftMember VALUES ( 'SPH-MO' ,'SPH07025');INSERT INTO ShiftMember VALUES ( 'SPH-MI' ,'SPH07026');INSERT INTO ShiftMember VALUES ( 'SPH-NI' ,'SPH07027');- INS

31、ERT Recipe data-INSERT INTO Recipe VALUES ( 'ST-650' ,650 ,20 ,750 ,90 ,850 ,30 );INSERT INTO Recipe VALUES ( 'ST-750' ,750 ,20 ,850 ,90 ,950 ,30 );INSERT INTO Recipe VALUES ( 'ST-850' ,850 ,20 ,950 ,90 ,1050 ,30 );INSERT INTO Recipe VALUES ( 'ST-950' ,950 ,20 ,1050 ,

32、90 ,1150 ,30 );INSERT INTO Recipe VALUES ( 'ST-1050' ,1050 ,20 ,1150 ,90 ,1250 ,10 );INSERT INTO Recipe VALUES ( 'ST-1150' ,1150 ,20 ,1250 ,90 ,1350 ,10 );INSERT INTO Recipe VALUES ( 'ST-1250' ,1250 ,90 ,1350 ,30 ,1450 ,10 );- INSERT Pdi data-ALTER SESSION SET NLS_DATE_FORMAT

33、 = 'YYYYMMDD HH24MISS'INSERT INTO PDI VALUES ( 'SP-304-07-46-001','20071116 162000','304','ST-1050' );INSERT INTO PDI VALUES ( 'SP-304-07-46-002','20071116 163000','304','ST-1050' );INSERT INTO PDI VALUES ( 'SP-304-07-46-003

34、','20071116 164000','304','ST-1050' );INSERT INTO PDI VALUES ( 'SP-304-07-46-004','20071116 165000','304','ST-1050' );INSERT INTO PDI VALUES ( 'SP-304-07-46-005','20071116 170000','304','ST-1050' );INSERT INT

35、O PDI VALUES ( 'SP-304-07-46-006','20071116 171000','304','ST-1250' );- INSERT Schedule data-INSERT INTO Schedule VALUES ( 'SPH-NI' ,'20071117 010000' ,'SP-304-07-46-001' );INSERT INTO Schedule VALUES ( 'SPH-NI' ,'20071117 030000

36、9; ,'SP-304-07-46-002' );INSERT INTO Schedule VALUES ( 'SPH-NI' ,'20071117 130000' ,'SP-304-07-46-003' );INSERT INTO Schedule VALUES ( 'SPH-NI' ,'20071117 150000' ,'SP-304-07-46-004' );INSERT INTO Schedule VALUES ( 'SPH-NI' ,'200711

37、18 050000' ,'SP-304-07-46-005' ); INSERT INTO Schedule VALUES ( 'SPH-MO' ,'20071118 090000' ,'SP-304-07-46-006' ); - INSERT Pdo data-INSERT INTO Pdo VALUES ( 'SP-304-07-46-001','20071116 162000','20071117 012000','SPH-NI','304&#

38、39; ,'ST-1050' );INSERT INTO Pdo VALUES ( 'SP-304-07-46-002','20071116 162000','20071117 031000','SPH-NI','304' ,'ST-1050' );INSERT INTO Pdo VALUES ( 'SP-304-07-46-003','20071116 162000','20071117 132000','SPH-MO'

39、;,'304' ,'ST-1150' );INSERT INTO Pdo VALUES ( 'SP-304-07-46-004','20071116 162000','20071117 192000','SPH-MI','304' ,'ST-1050' );INSERT INTO Pdo VALUES ( 'SP-304-07-46-005','20071116 162000','20071118 032000',

40、9;SPH-NI','304' ,'ST-1050' );- COMMIT;-COMMIT;五、網(wǎng)頁畫面Hardcopy登入畫面使用者輸入使用者名稱及密碼，程控電腦會比對Operator資料表中的資料，若符合則允許使用者登入，若無符合之使用者資料則出現(xiàn)如下之訊息 :MENU使用者登入後出現(xiàn)畫面選單PDI進(jìn)入PDI畫面後，使用者可看到PDI資料，使用者點選任一筆PDI資料後，該筆PDI資料會顯示於下方的編輯區(qū)中，使用者可進(jìn)行新增/修改/刪除功能。PDO進(jìn)入PDO畫面後，使用者可看到PDO資料，使用者點選任一筆PDO資料後，該筆PDO資料會顯示於下方的編輯區(qū)

41、中，使用者可進(jìn)行新增/修改/刪除功能。Schedule進(jìn)入Schedule畫面後，使用者可看到Schudule資料，使用者點選任一筆Schedule資料後，該筆Schedule資料會顯示於下方的編輯區(qū)中，使用者可進(jìn)行新增/修改/刪除功能。Recipe 進(jìn)入Recipe畫面後，使用者可看到Recipe資料，使用者點選任一筆Recipe資料後，該筆Recipe資料會顯示於下方的編輯區(qū)中，使用者可進(jìn)行新增/修改/刪除功能。Operator進(jìn)入Operator畫面後，使用者可看到Operator資料，使用者點選任一筆Operator資料後，該筆Operator資料會顯示於下方的編輯區(qū)中，使用者可進(jìn)行新

42、增/修改/刪除功能。Shift進(jìn)入Shift畫面後，使用者可看到Shift資料，使用者點選任一筆Shift資料後，該筆Shift資料會顯示於下方的編輯區(qū)中，使用者可進(jìn)行新增/修改/刪除功能。ShiftMember進(jìn)入ShiftMember畫面後，使用者可看到ShiftMember資料，使用者點選任一筆ShiftMember資料後，該筆ShiftMember資料會顯示於下方的編輯區(qū)中，使用者可進(jìn)行新增/修改/刪除功能。六、系統(tǒng)架構(gòu)圖DATADBMSOracle 10gUSERDBMSSEVERClientWEBSEVERIISADMINISTRATORHTMLASP.NET網(wǎng)站架構(gòu)圖七、系統(tǒng)所使

43、用的工具：伺服器與語言:1. IIS6.02. Oracle 10g3. ASP.NET 2.0系統(tǒng)網(wǎng)址：系統(tǒng)的可執(zhí)行時間：執(zhí)行方式：使用一般瀏覽器即可八、心得：洪敏鴻：這次雖然在工作中忙碌的學(xué)習(xí)，但重溫了許多資料庫的知識，資料庫實在是一門實用價值很高的一門課，許多產(chǎn)業(yè)的系統(tǒng)實作上更是離不開資料庫的應(yīng)用，且當(dāng)前產(chǎn)業(yè)界所最常使用的資料庫也大多為關(guān)連式的資料庫。對於一個從事資訊領(lǐng)域的資訊人來說，只有不斷的學(xué)習(xí)與擴充才能維持自己不落後於科技的腳步。在過去許多工作經(jīng)驗的累積中，資料庫一直是我重要的工作內(nèi)容之一，但對於其核心的作業(yè)理論及最佳化理論的依據(jù)其實並未能有完全透徹的了解，但在老師完善的課程安排下，使我能透徹的了解資料庫背後許多更深入的細(xì)節(jié)，這讓我對於資料庫的建置有了更全面的思考，對於往後的工作管理及系統(tǒng)的規(guī)劃上我有了不同以往的想法，不再只是局限於程式碼對資料庫的控制，而是應(yīng)該先從資料庫本身的運作改善開始，謝謝老師這一學(xué)期的指導(dǎo)。孫盟格：這一學(xué)期從老師實務(wù)的課程教學(xué)方式，讓我真正體會到一門科目的學(xué)習(xí)及鑽研是需要投入大量時間及精神的。從老師的課程安排及充實的課程規(guī)劃，以及緊湊的學(xué)習(xí)進(jìn)度