Introduction to MIS

Size: px

Start display at page:

Download "Introduction to MIS"

敷票全
9 years ago
Views:

1 MIS for Logistics EMBA J.-S. Chou, P.E., Ph.D.

2 Outline (I) Introduction to MIS: Managerial and Decision Support Systems Article Review: The KDD process for extracting useful knowledge from volumes of data Tips: Google Search A global trend: OSS Database Processing Examples of Database

3 Outline (II) Data warehouse Article Review: Data Mining vs. Statistics Data mining techniques Article Review: Digging for Nuggets of Wisdom Data mining software demonstration MS SQL Server 2005 SAS 9.1 Enterprise Miner

4 An IS Framework for Business Professionals

5 Components of an Information System Hardware Software Operating system software DBMS software Application programs and utility software Data Network People Procedures Hardware People Information Systems Resources & Procedures Networks Data Software

6 Contrasting Database and File Systems

7 Applications of Information Systems Marketing ( 市場行銷管理 ) Identify customers Determine what they want Planning products Advertising and promoting products Determine prices for products Sales ( 銷售物流管理 ) Contact customers Sell the product Take the order Follow-up on the sale 5 year sales forecast Manufacturing ( 製造業生產管理 ) Control Equipment and machinery Design new products When and quantity of products to produce New production facilities Generate the work order Human Resources ( 人力資源管理 ) Employee wages, salaries & benefits Long term labor requirements Tracking vacation, sick, Track employee skills Interview and review employees Purchasing ( 電子商務管理 ) Which vendors Quantity to purchase Coop, rebate tracking Handle delivery discrepancies Generate the purchase order Finance ( 財務金融管理 ) Financial Assets Investment management Banking Long term budgets Accounting ( 會計資訊系統 ) Accounts Receivable Disbursements Payroll Depreciation Earned Coop and Rebates

8 Knowledge Management Knowledge management (KM) is a process that helps organizations identify, select, organize, disseminate, and transfer important information and expertise that are part of the organization s memory. Structuring of knowledge enables effective and efficient problem solving dynamic learning strategic planning decision making. Knowledge management initiatives focus on identifying knowledge how it can be shared in a formal manner leveraging its value through reuse. Knowledge management can promote organizational learning help solve problems 8

9 Knowledge Knowledge is very distinct from data and information and provides a higher level of meaning about that data and information. The ability to act is an integral part of being knowledgeable. Data are a collection of: Facts Measurements Statistics Information is organized or processed data that are: Timely Accurate Knowledge is information that is: Contextual Relevant Actionable. Having knowledge implies that it can be exercised to solve a problem, whereas having information does not.

10 Knowledge Corporate Asset Knowledge has the following characteristics that differentiates it from an organization s other assets Extraordinary leverage and increasing returns. Knowledge is not subject to diminishing returns. When it is used, it is not consumed. Its consumers can add to it, thus increasing its value. Fragmentation, leakage, and the need to refresh. As knowledge grows, it branches and fragments. Knowledge is dynamic; it is information in action. Thus, an organization must continually refresh its knowledge base to maintain it as a source of competitive advantage. Uncertain value. It is difficult to estimate the impact of an investment in knowledge. There are too many intangible aspects. Uncertain value of sharing. Similarly, it is difficult to estimate the value of sharing the knowledge, or even who will benefit most. Rooted in time. Intellectual capital or intellectual assets

11 Knowledge Explicit knowledge Explicit knowledge has been codified (documented) in a form that can be distributed to others or transformed into a process or strategy without requiring interpersonal interaction. Explicit knowledge (or leaky knowledge) deals with objective, rational, and technical knowledge Data Policies Procedures Software Documents Products Strategies Goals Mission Core competencies The more that knowledge is made explicit, the more economically it can be transferred.

12 Knowledge Tacit knowledge Tacit knowledge is usually in the domain of subjective, cognitive, and experiential learning; it is highly personal and difficult to formalize. It is also referred to as embedded knowledge since it is usually either localized within the brain of an individual or embedded in the group interactions within a department or business unit. Tacit knowledge is the cumulative store of the corporate experiences Mental maps Insights Acumen Expertise Know-how Trade secrets Skill sets Learning of an organization The organizational culture Tacit knowledge is generally slow and costly to transfer and can be plagued by ambiguity.

13 Knowledge Knowledge Management Systems The goal of knowledge management is for an organization to be aware of individual and collective knowledge so that it may make the most effective use of the knowledge it has. Firms recognize the need to integrate both explicit and tacit knowledge into a formal information systems - Knowledge Management System (KMS) A functioning knowledge management system follows six steps in a cycle dynamically refining information over time 1. Create knowledge. 2. Capture knowledge. 3. Refine knowledge. 4. Store knowledge. 5. Manage knowledge. 6. Disseminate knowledge. As knowledge is disseminated, individuals develop, create, and identify new knowledge or update old knowledge, which they replenish into the system.

14 Knowledge Knowledge Management Systems Knowledge Management Cycle

15 Knowledge Management Systems Demonstration

16 Knowledge Management Information Technology Knowledge management is more than a technology or product, it is a methodology applied to business practices. However, information technology is crucial to the success of knowledge management systems. Components of Knowledge Management Systems: Communication technologies allow users to access needed knowledge and to communicate with each other. Collaboration technologies provide the means to perform group work. Storage and retrieval technologies (database management systems) to store and manage knowledge.

17 Knowledge Management Supporting Technologies Technologies enable advanced functionality in knowledge management systems and form the base for future innovations. Artificial Intelligence (AI methods: expert systems, neural networks, fuzzy logic, genetic algorithms, etc.) Assist in identifying expertise Elicit knowledge automatically and semi-automatically Provide interfacing through natural language processors Enable intelligent searches through intelligent agents. Intelligent agents are software systems that learn how users work and provide assistance in their daily tasks. Knowledge Discovery in Databases (KDD) is a process used to search for and extract useful information from volumes of documents and data. It includes tasks such as: knowledge extraction data archaeology data exploration data pattern processing data dredging information harvesting

18 Knowledge Management Supporting Technologies Continued Data mining the process of searching for previously unknown information or relationships in large databases, is ideal for extracting knowledge from databases, documents, , etc. Model warehouses & model marts extend the role of data mining and knowledge discovery by acting as repositories of knowledge created from prior knowledge-discovery operations Extensible Markup Language (XML) enables standardized representations of data structures, so that data can be processed appropriately by heterogeneous systems without caseby-case programming.

19 Knowledge Management IT Products Technology tools that support knowledge management are called knowware. Most knowledge management software packages include one or more of the following tools: collaborative computing tools knowledge servers enterprise knowledge portals electronic document management systems knowledge harvesting tools search engines knowledge management suites.

20 Knowledge Management IT Services Consulting Firms provide assistance in establishing knowledge management systems measuring their effectiveness Support for vertical market software Application service providers (ASPs) have evolved as a form of KMS outsourcing on the Web. Offering a complete knowledge management solution, including a KM suite and the consulting to set it up.

21 Knowledge Management Integration Knowledge management systems are enterprise-wide and must be integrated with other information systems in an organization. Decision Support Systems (DSS) Artificial Intelligence Customer Relationship Management Systems (CRM) Supply Chain Management Systems (SCM) Corporate Intranets Extranets

22 Knowledge Management Integration Knowledge management systems integration.

23 Business Intelligence Business intelligence (BI) is a broad category of applications and techniques for gathering, storing, analyzing and providing access to data. It help s enterprise users make better business and strategic decisions. Major applications include the activities of query and reporting, online analytical processing (OLAP), DSS, data mining, forecasting and statistical analysis. Business intelligence includes: outputs such as financial modeling and budgeting resource allocation coupons and sales promotions Seasonality trends Benchmarking (business performance) competitive intelligence. Starts with Knowledge Discovery

24 Business Intelligence Continued How It Works.

25 Knowledge Discovery Before information can be processed into BI it must be discovered or extracted from the data stores. The major objective of this knowledge discovery in databases (KDD) is to identify valid, novel, potentially useful, and understandable patterns in data. KDD supported by three technologies: massive data collection powerful multiprocessor computers data mining and other algorithms. KDD primarily employs three tools for information discovery: Traditional query languages (SQL, ) OLAP Data mining Discovering useful patterns

26 Knowledge Discovery Continued Discovering useful patterns

27 Paper Review The KDD process for Extracting Useful Knowledge from Volumes of Data, Usama Fayyad.

28 Google 兩名 Stanford CS PhD candidates: Larry Page and Sergey Brin 於 1998 年成立 Google 取自於 googol (10 的 100 次方 ), 代表著網路資訊浩蕩無涯之意目前有超過萬台伺服器以 T3 頻寬提供全世界網路使用者搜尋服務藉由網路使用者的集體智慧來決定網頁的重要性 e.g. PageRank Robot Capture Web Pages to Google Database Use Index for searching

29 資料庫之相關資料數, 由 Robot, Spider, Worm 定時在網路上漫遊所蒐集的網頁連結集合

30 Google s Measure of the importance of this page

31 PageRank Page A 的級別 = (1 - C) + C * (Page1 級別 /Page1 鏈出個數 + Page2 級別 /Page2 鏈出個數 + + PageN 級別 /PageN 鏈出個數 ) 連結指向 A 的網頁愈多,A 的級別愈高連結指向 A 的網頁, 其網頁級別 ( 分子 ) 愈高,A 的級別也愈高連結指向 A 的網頁, 其鏈出的個數 ( 分母 ) 愈多,A 的級別愈低利用此原理加上其它上百種因素來參與排序, 即為 Google 的搜尋結果內容關聯性更新度點閱率地區性語言別等

32 Google Search 查詢特定網站資料 E.g. cost management site: 查詢特定國家網站 E.g. site:tw(hk, cn, jp, etc.) 尋找特定文件檔案 E.g. filetyp:doc(pdf, doc, xls, swf, txt, rtf, etc.) knowledge management 尋找非文件類型 E.g. inurl:mp3 Canon

33 列出所有有設定連結到 PMI 網站的網頁 E.g. link: E.g. link: Google 帶動的產業 : E.g. Search Engine Marketing (SEM) / Search Engine Optimization Co. 提供關鍵字管理 : 16 萬 / 年 / 每關鍵字未來發展方向個人化搜尋語音搜尋以圖找圖或以 xx 找 xx, 而非僅文字找所有物件

34 Free Software/Open Source Software 自由軟體前所未有的賦予軟體使用者以下的四種自由 : 使用的自由 : 可以不受任何限制地來使用軟體研究的自由 : 可以研究軟體運作方式並使其適合個人需要散佈的自由 : 可以自由地複製此軟體並散佈給他人改良的自由 : 可以自行改良軟體並散佈改良後的版本以使全體社群受益與商業軟體之間的差異 : 自由軟體鼓勵使用者拷貝自由軟體允許使用者研究改良

35 各國採用自由軟體的重要歷程

36 Free Software vs. Windows Applications Application Free Software Windows Browser KKMAN, Mozilla firefox, Opera Internet Explorer Mail Thunderbird, evolution Outlook (Express) FTP gftp WSFTP, CuteFTP Net meeting gnomemeeting MS NetMeeting Messenger gaim MSN, Yahoo Messenger Word abiword Word PDF gpdf, xpdf Acrobat Reader Calculation gnumeric Excel Finance management GnuCash MS Money Presentation MagicPoint MS PowerPoint Office Openoffice, KOffice MS Office Project management egroupware Primavera, MS Project Burning software XcdRoast, K3b Nero, EasyCD Creator

37 Free Software vs. Windows Applications Application Free Software Windows Media player HelixPlayer, mplayer Music MikMod RealPlayer Phone Skype Skype Image ImageMagick, Gimp Photoshop Web server Apache MS IIS Scripting language for Web page PHP ASP Windows Media Player Database MySQL,postgresql Oracle, Microsoft SQL Server, MS Access Programming language Perl, Python Visual Basic Application Server Zope IBM Websphere Remote control Vnc UltraVNC, RealVNC Mail Server Postfix, Sendmail MS Exchange Server Proxy Squid MS Proxy Server DNS Bind MS DNS Server

38 以 OSS 架構的網路世界

39 Life Cycle of OSS (

40 資料來源 : 主計處多人用電腦作業系統設置概況單位 : 台 ;% 設置概況 93 年底 92 年底作業系統設置總台數設置率設置總台數設置率 Windows( 註 ) 177, , LINUX 14, , UNIX 13, , 其他 11, , 註 :Windows 包含 Windows NT Windows 2000 Windows 2003

41 92 年度電腦應用概況調查結果

42 Applications of OSS: e-economics RFID Digital family technology Server Large-scale system ERP system Database system Last mile problem: how about the service?

43 自由軟體發展趨勢

44 Database Processing Ref: Database Processing: Fundamentals, Design, and Implementation, 原著 :Kroenke. 審閱 : 范錚強, 校閱 : 鄧姚文, 譯者 : 李毅騁

45 為什麼使用資料庫? 資料庫的目的在於幫助人們保留事件的軌跡以清單紀錄資料的問題資料不一致資料隱私權 : 無法只分享部分資料, 分享清單的人可以看到清單上全部的資訊資料庫系統以資料表 (Table) 儲存資料, 每一個資料表都只有一個主題資料表之間以主鍵 (Primary Key) 和外部鍵 (Foreign Key) 維繫相互之間的關係

46 資料庫系統的組成元件圖 1-6 資料庫系統元件資料庫應用程式資料庫管理系統 (DBMS) 資料庫使用者

47 應用程式功能 : 建立並處理表單建立並傳送查詢建立並處理報表執行應用邏輯控制應用程式

48 資料庫管理系統 (DBMS) DBMS: 資料庫管理系統功能 : 建立資料庫資料表以及支援架構讀取與更新資料庫資料維護資料庫結構強化規範控制共時 (Concurrency) 問題提供安全性執行備份與復原實例 : Oracle, DB2, Microsoft Access, SQL Server, MySQL

49 資料庫資料庫是一組具有自我描述性質的相關記錄所形成的集合組成元件 : 使用者資料中繼資料 (Metadata): 關於資料庫結構的資料索引 (Index) 與相關結構預存程序 (Stored Procedure): 儲存在資料庫中的程式模組觸發機制 (Trigger): 於特定事件發生時執行的副程式應用程式中繼資料 : 描述應用程式元件的資料, 例如表單和報表

50 資料庫的類型個人資料庫一個使用者 ; < 10 MB 工作群組資料庫 < 25 使用者 ; < 100 MB 企業資料庫數百名使用者上千名使用者 >1 TB(2 12 ), 可能有多個資料庫

51 範例 : 企業組織的資料庫圖 1-15 企業組織的資料庫系統駕駛員歷史資料應用程式 Java 程式碼汽車資料庫汽車註冊應用程式 C# 程式碼駕駛員資料庫駕駛員換照應用程式 HTML 與 VBScript

52 建構一個資料庫系統 3 階段需求階段 : 建立資料模式資料模式是資料庫結構的邏輯表現設計階段 : 將資料模式轉換成資料表與關係建置階段 : 建立資料表關係以及限制條件撰寫預存程序與觸發機制將資料填入資料庫測試將來可以修改資料庫和應用程式 ( 經歷相同的 3 階段 ) 以滿足新的需求

53 範例 : 資料模式圖 1-18 Lakeview 租賃公司的個體關係圖

54 應用程式發展應用程式的發展和資料庫的發展並行

55 資料庫處理的歷史

56 早期資料庫的模式在 1960 年代中期以前, 只能使用磁帶進行循序檔案處理 1960 年代中期, 磁碟儲存裝置的出現, 發展出階層式資料庫與網路式資料庫 IBM 的 DL/I (Data Language One) CODAYSL 的 DBTG (Data Base Task Group) 模式現代 DBMS 的基礎

57 關聯式資料模式 E.F. Codd 於 1970 年發表關聯式資料模式 IBM 的 DB2 為第一款關聯式 DBMS 產品其他的關聯式 DBMS 發展於 1980 年代末期 DB2 Oracle Microsoft SQL Server 為現今最著名的關聯式 DBMS 產品

58 個人電腦 DBMS 微電腦的進展使得個人資料庫廣受歡迎圖形使用者介面 (Graphical User Interface,GUI) 讓資料庫更容易使用早期的 DBMS 產品, 例如 : dbase R:base Paradox

59 物件導向式 DBMS (OODBMS) 物件導向程式設計始於 1980 年代中期 OODBMS 的目標為 : 將物件導向程式的物件儲存於資料庫中, 不必再轉換成關聯式格式物件 - 關聯式 DBMS 產品如 Oracle 8i 和 9i 在同一個資料庫之中同時提供關聯式的觀點與物件式的觀點目前 OODBMS 尚未在商用領域獲得成功的主要原因為 : 將舊有的關聯式資料轉換成物件的成本太高

60 近代歷史 Microsoft Access Microsoft Office 套件與 Windows 整合簡單易用而且功能強大的個人 DBMS 網際網路資料庫, e.g. PHP + MySQL + APACHE XML 與資料庫的整合, e.g. IBM DB2 Viper

61 實體關係資料模型化 : 工具與技巧

62 三種結構模型外部結構 (External Schema) 又稱為使用者觀點 (User View) 呈現使用者看待資料庫的觀點概念結構 (Conceptual Schema) 一種邏輯觀點, 涵蓋資料庫中所有的資料及其相互關係的說明與資料儲存的方式無關一個概念結構通常包含多個不同的外部結構內部結構 (Internal Schema) 將概念結構實體儲存於特定產品中一個概念結構可以用多種不同的內部結構呈現

63 實體關係模型 (E-R Model) 實體關係模型是一套觀念與圖形符號, 可用來建立概念結構四種版本原始的實體關係模型由 Peter Chen 於 1976 年提出延伸的實體關係模型 (Extended E-R model) 為最廣泛被使用的版本資訊工程版 (IE) 由 James Martin 於 1990 年提出 IDEF1X 版由美國國家標準與技術學會 ( National Institute of Standards and Technology) 提出物件導向方法支持統一塑模語言 (Unified Modeling Language, UML)

64 延伸的 E-R 模型圖 2-15 實體關係模型範例

65 範例 :E-R 圖圖 2-10 在實體關係圖中顯示屬性

66 實體 (Entities) 使用者想要追蹤的東西, 能夠被識別實體類別 (Entity class) 一組相同類型的實體, 擁有相同的格式實體執行個體 (Entity instance) 任何一個特定的實體一個實體類別通常有很多個實體執行個體

67 範例 : 實體圖 2-5 一個實體範例 :CUSTOMER

68 屬性 (Attributes) 描述實體的特徵一個實體類別中的所有個體都具有相同的屬性複合屬性 (Composite attribute): 由多個屬性組成多重值屬性 (Multi-value attributes): 一個屬性擁有多個值

69 識別項 (Identifiers) 用來為每一個實體執行個體命名的屬性, 可用來識別各個執行個體識別項可以是單一屬性, 也可以由多個屬性組成識別項可以是唯一的, 也可以不是唯一的唯一識別項 (Unique identifier): 其值可識別出唯一的一個執行個體非唯一識別項 (Non-unique identifier): 其值可識別出一群執行個體複合識別項 (Composite identifiers): 由兩個以上的屬性組成

70 關係 (Relationship) 實體之間透過關係互相結合關係類別 (Relationship classes): 實體類別之間的關係關係執行個體 (Relationship instances): 實體執行個體之間的關係關係也有屬性一個關係類別可以和多個實體類別相關關係的自由度 (Degree of the relationship) 一個關係所涉及的實體類別個數

71 範例 : 關係的自由度圖 2-6 不同自由度的關係 :(a) 自由度 2 的關係 ;(b) 自由度 3 的關係自由度為 2 的關係最常見, 又稱為二元關係 (Binary Relationship)

72 二元關係 1:1 1:N N:M 圖 2-7 二元關係的三種類型 :(a)1:1 二元關係 ;(b)1:n 二元關係 ;(c)n:n 二元關係

73 遞迴關係存在於單一實體類別內的執行個體之間遞迴關係 (Recursive Relationship) 圖 2-9 遞迴關係 :(a)e-r 圖 ; (b) 樣本資料

75 Recursive relationships are common! In many manufacturing industries, incredibly detailed part tracking is mandatory. For example, the implementation of the recursive relationship PART contains PART is especially desirable in the aviation manufacturing businesses. Such businesses are required by federal law to maintain absolute parts tracing records. If a complex part fails, it must be possible to follow all the trails to all the component parts that may have been involved in the part s failure.

76 基數 (Cardinality) 最大基數 (Maximum cardinality) 一個關係涉及的最大實體數目最小基數 (Minimum cardinality) 一個關係涉及的最小實體數目圖 2-8 一個顯示最小基數的關係

77 弱實體 (Weak Entity) 弱實體必須在邏輯上依賴其他實體才能夠存在如果資料庫之中沒有依存對象 ( 強實體, Strong Entity), 弱實體便不存在 ID 相依實體 (ID-dependent entity): 一個實體的識別項包含在另一個實體的識別項中

78 範例 : 弱實體圖 2-11 弱實體 :(a) 沒有 ID 相關 ;(b) ID 相關

79 範例 : 弱實體圖 2-12 需求實體 (Required Entity)

80 值域 (Domain) 值域為屬性值的集合, 每一個值域有一個特定的名稱值域的表現方式 : 以清單列舉所有的值預先定義的資料特性, 例如 :75 個字之內的字串值域降低資料模型中的模糊性, 讓資料模型的定義更明確在實務上也相當有用兩種值域基礎值域 (Base Domain): 具有資料類型, 並以數值範圍或列舉清單方式標示型態值域 (Type Domain): 基礎值域的子集合或另一個型態值域的子集合

81 範例 : 值域階層圖 2-26 值域階層範例基礎值域型態值域

82 IDEF1X 標準 IDEF1X (Integrated Definition 1, Extended) 於 1993 成為美國國家標準定義有 : 實體關係屬性, 但意義上稍有不同修改了 E-R 圖的部分符號增加了值域 (domain) 的定義四種關係類型非定義的連結關係 (Non-Identifying Connection Relationship) 定義的連結關係 (Identifying Connection Relationship) 非特定關係 (Non-Specific Relationship) 分類關係 (Categorization Relationship) 支援 IDEF1X 的產品 : ERWin, Visio, Design/2000

83 A Comparison of ER Modeling Symbols

84 The Chen Representation of the Invoicing Problem

85 The Crow s Foot Representation of the Invoicing Problem

86 The Rein85 Representation of the Invoicing Problem

87 The IDEF1X Representation of the Invoicing Problem

88 UML 形式的 E-R 圖 UML 是一組結構與技術的集合, 用於模型化與設計物件導向應用程式 UML 的實體關係以及屬性在觀念上和延伸的 E-R 模型非常相似額外的 OOP 結構 : <Persistent> 標示該實體類別存在資料庫中 UML 的實體類別可以有屬性 UML 支援屬性與方法 (Method) 的可見度 (Visibility) UML 在實體類別中分區段標示其限制與方法物件導向記號目前的實用性仍有相當的限制

89 範例 : UML 圖 2-27(a) 1:1 關係的 UML 表示法

90 範例 : UML 圖 2-27(b) 1:N 關係的 UML 表示法

91 範例 : UML 圖 2-27(c) N:M 關係的 UML 表示法

92 UML: Weak Entities 弱實體的 UML 表示法

93 UML: Subtypes 圖 2-29 子類型的 UML 表示法

94 關聯 (Relation) 關聯式資料庫管理系統將資料以關聯的形式儲存於系統中一個關聯 (Relation) 是一個二維表格, 並具有下列特性 : 每一列 (Row) 包含一個實體 (Entity) 的資料每一欄 (Column) 紀錄一項實體的屬性 (Attribute) 表格中的每一格 (Cell) 皆為單一值同一欄的項目其類型相同每一欄都有一個唯一的名字欄的順序不重要列的順序不重要沒有任何兩列是完全相同的雖然表格未必關聯是, 但是表格和關聯這兩個名詞經常被混在一起使用表格 / 列 / 欄 (Table/Row/Column) = 檔案 / 紀錄 / 欄位 (File/Record/Field) = 關聯 / 列錄 / 屬性 (Relation/Tuple/Attribute)

95 鍵值類型一個鍵 (Key) 由關聯中的一個或多個欄位組成, 用以識別資料列唯一鍵 (Unique Key) 用來識別單一資料列 ; 非唯一鍵 (Non-unique Key ) 用來識別多個資料列複合鍵 (Composite Key) 為兩個以上的欄位組成的鍵每一個關聯都有一個唯一鍵, 稱為主鍵 (Primary Key); 若一關聯有多個唯一鍵, 則不作為主鍵的這些額外的唯一鍵稱為備選鍵 (Candidate Key) 主鍵的功能尚有在關係中代表表格安排表格的儲存產生索引

96 正規化 (Normalization) 正規化消除修改異常 (Modification Anomalies) 現象刪除異常 (Deletion Anomaly): 刪除單一資料列造成多個實體的資訊遺失插入異常 (Insertion Anomaly): 在另一個實體的資料尚未插入之前, 無法插入目前這個實體的資料將一個關聯適當地分割成為多個關聯, 以消除異常現象分割關聯可能產生參考完整限制 (Referential Integrity Constraint) 正規化的過程分成很多種正規化格式 (Normal Form)

97 A Dependency Diagram: First Normal Form (1NF)

98 Second Normal Form (2NF) Conversion Results

99 Third Normal Form (3NF) Conversion Results

100 The Completed Database

101 The Completed Database (continued)

102 反正規化 (De-normalized) 設計如果正規化的結果造成不自然不方便或效能無法被接受的情況, 則我們寧可不做正規化範例正規化關聯 CUSTOMER (CustNumber, CustName, Zip) CODES (Zip, City, State) 反正規化關聯 CUSTOMER (CustNumber, CustName, City, State, Zip)

103 SQL 簡介結構化查詢語言 (Structured Query Language) 簡稱 SQL SQL 是一套包含資料定義與資料處理功能的語言 SQL 的功用 : 可在 DBMS 的命令列上單獨使用可內嵌在觸發機制 (Trigger) 和預存程序 (Stored Procedure) 中使用可內嵌在程式語言中使用

104 SQL-92 IBM 在 1970 年代末期發展出 SQL SQL-92 於 1992 年由 ANSI 訂為美國國家標準 SQL3 增加物件導向但是未被業界接受資料定義語言 (Data Definition Language, 簡稱 DDL): 用以定義資料庫結構資料操作語言 (Data Manipulation Language, 簡稱 DML): 用以查詢與更新資料 SQL 敘述以分號 (;) 結尾

105 建立資料表使用 CREATE TABLE 陳述式建立資料表每一個欄位以三項屬性加以描述 : 欄位名稱 (Column Name) 資料型態(Data Type) 限制 ( 可有可無 ) 範例 CREATE TABLE PROJECT ( ProjectID Integer Primary Key, Name Char(25) Unique Not Null, Department VarChar(100) Null, MaxHours Numeric(6,1) Default 100);

106 資料型態 (Data Type) 標準資料型態 Char 適用於固定長度字串 VarChar 適用於可變長度字串相較於 Char,VarChar 需要額外的處理程序 Integer 適用於整數 ( 沒有小數點的數值 ) Numeric 適用於精確度要求較高的數值除了上述常用資料型態之外,SQL-92 標準還定義很多其他的資料型態

107 限制 (Constraint) 限制可以在 CREATE TABLE 之中加以定義, 也可以在完成資料表定義之後, 以 ALTER TABLE 陳述式修改資料表的限制定義五種限制 : PRIMARY KEY 定義主鍵, 不可為空值 UNIQUE 定義唯一的索引值, 可以有空值 NULL/NOT NULL 可以為空值 / 不可為空值 FOREIGN KEY 外部鍵 CHECK 額外的檢查條件

108 ALTER 陳述式在資料表已經建立之後, 以 ALTER 陳述式修改資料表的結構屬性限制範例 ALTER TABLE ASSIGNMENT ADD CONSTRAINT EmployeeFK FOREIGN KEY (EmployeeNum) REFERENCES EMPLOYEE (EmployeeNumber);

109 DROP 陳述式 DROP TABLE 陳述式可用來將資料表及其資料自資料庫之中刪除如果資料表之中有一些被其他資料表外部鍵參照的鍵值, 便不能刪除先以 ALTER TABLE DROP CONSTRAINT 將限制條件刪除, 然後再刪除資料表範例 : DROP TABLE CUSTOMER; ALTER TABLE ASSIGNMENT DROP CONSTRAINT ProjectFK;

110 SELECT 陳述式查詢資料 SELECT 可以被用來取得特定資料列與特定資料欄的資料值基本格式 : SELECT ( 欄位名稱清單或 *) FROM ( 資料表清單 ) [WHERE ( 限制條件 )];

111 WHERE 條件子句宣告成 Char 或 VarChar 的字串型態欄位, 須以單引號將數值括起來 ;Integer 和 Numeric 等數值欄位則不必使用單引號以 AND( 且 ) 和 OR( 或 ) 組合多項限制條件 IN 表示符合集合中的任何一個值 ;NOT IN 表示不符合集合中的任何值萬用字元必須與 LIKE 合用 : _ 對應任何一個字元, 且一定要有一個字元 % 對應任何數目 ( 包括零 ) 的任何字元 IS NULL 可測試某個欄位的值是否為空值

112 範例 : SELECT 陳述式 SELECT Name, Department, MaxHours FROM PROJECT;

113 範例 : SELECT DISTINCT SELECT DISTINCT Department FROM PROJECT; DISTINCT 令重複的項目只出現一次

114 範例 : SELECT 陳述式 SELECT * FROM PROJECT WHERE Department = Finance AND MaxHours > 100; * 表示列出所有的欄位 WHERE 子句限制只列出 Department 欄位的值等於 Finance 而且 MaxHours 欄位的值大於 100 的資料列

115 範例 : IN/NOT IN SELECT Name, Phone, Department FROM EMPLOYEE WHERE Department IN ( Accounting, Finance, Marketing ); SELECT Name, Phone, Department FROM EMPLOYEE WHERE Department NOT IN ( Accounting, Finance, Marketing );

116 範例 : BETWEEN SELECT Name, Department FROM EMPLOYEE WHERE EmployeeNumber BETWEEN 200 AND 500; WHERE 子句也可以寫成 WHERE EmployeeNumber >= 200 AND EmployeeNumber <= 500;

117 範例 : LIKE SELECT * FROM EMPLOYEE WHERE Phone LIKE 285- ; SELECT * FROM EMPLOYEE WHERE Phone LIKE 285% ;

118 範例 : IS NULL SELECT Name, Department FROM EMPLOYEE WHERE Phone IS NULL;

119 查詢結果排序在 SELECT 陳述式中加入 ORDER BY 定義排序方式 SELECT Name, Department FROM EMPLOYEE ORDER BY Department; 排序時可以使用多個欄位,ASC 表示從小到大遞增排序,DESC 表是從大到小遞減排序 SELECT Name, Department FROM EMPLOYEE ORDER BY Department DESC, Name ASC;

120 內建函數五個常用的內建函數 : COUNT 計算數量 SUM 計算加總值 AVG 計算平均值 MAX 取得最大值 MIN 取得最小值上列函數的結果都是單一數值不能在內建函數中混合多個欄位名稱不能在 WHERE 子句之中使用內建函數

121 範例 : 內建函數 SELECT COUNT (DISTINCT Department) FROM PROJECT; SELECT MIN(MaxHours), MAX(MaxHours), SUM(MaxHours) FROM PROJECT WHERE ProjectID < 1500;

122 內建函數與群組 GROUP BY 與內建函數合併使用 GROUP BY 以指定的欄位名稱作為分組的依據, 再對各組套用內建函數 WHERE 條件限制必須在 GROUP BY 之前以 HAVING 子句對 GROUP BY 加以限制範例 SELECT Department, Count(*) FROM EMPLOYEE WHERE EmployeeNumber < 600 GROUP BY Department HAVING COUNT(*) > 1;

123 查詢多個資料表以從屬查詢 (Subquery) 或聯結 (Join) 查詢多個資料表如果最後的查詢結果來自單一資料表, 可使用從屬查詢如果最後的查詢結果來自多個資料表, 則必須使用 Join Join 無法取代互相關聯的從屬查詢, 也不能取代使用 EXISTS 或 NOT EXISTS 子句的查詢

124 從屬查詢 (Subquery) 從屬查詢可以延伸很多層範例 SELECT DISTINCT Name FROM EMPLOYEE WHERE EmployeeNumber IN (SELECT EmployeeNum FROM ASSIGNMENT WHERE HoursWorked > 40 AND ProjectID IN (SELECT ProjectID FROM PROJECT WHERE Department = Accounting ));

125 Join 聯結基本概念 : 將兩個以上的關聯連接起來形成一個新的關聯聯結後的資料表和一般的資料表處理方式相同範例 SELECT PROJECT.Name, HoursWorked, EMPLOYEE.Name FROM PROJECT, ASSIGNMENT, EMPLOYEE WHERE PROJECT.ProjectID = ASSIGNMENT.ProjectID AND EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNum;

126 聯結語法的其他變化 SQL-92 以 JOIN 和 ON 這兩個關鍵字取代 WHERE 之中的聯結條件在列出資料表清單時, 可以加上別名 (Alias), 以簡化敘述範例 : 以 E 作為 EMPLOYEE 資料表的別名 SELECT P.Name, HoursWorked, E.Name FROM PROJECT P JOIN ASSIGNMENT A ON P.ProjectID = A.ProjectID JOIN EMPLOYEE E ON A.EmployeeNum = E.EmployeeNumber;

127 環外聯結 (Outer Join) 環外聯結可以確保查詢結果包含資料表的每一列資料左環外聯結 (LEFT OUTER JOIN): 查詢結果包含左邊資料表內的每一個資料列右環外聯結 (RIGHT OUTER JOIN): 查詢結果包含右邊資料表內的每一個資料列環外聯結可為巢狀

128 INSERT INTO 陳述式新增資料欄位名稱的順序和資料值的順序必須一致所有的非空值 (NOT NULL) 欄位都必須有值不可以指定替代鍵 (Surrogate Key) 的值可以用 SELECT 擷取大量的資料作為 INSERT INTO 的資料來源範例 : INSERT INTO PROJECT VALUES (1600, Q4 Tax Prep, Accounting, 100); INSERT INTO PROJECT (Name, ProjectID) VALUES ( Q1+ Tax Prep, 1700);

129 UPDATE 陳述式更新資料 UPDATE 只能更新現有的資料, 不能用來新增資料範例 : UPDATE EMPLOYEE SET Phone = WHERE Name = James Nestor ; UPDATE 可以一次更新多個欄位的值 UPDATE EMPLOYEE SET Phone = , Department = Production WHERE EmployeeNumber = 200;

130 DELETE FROM 陳述式 DELETE 將資料刪除範例 DELETE FROM PROJECT 刪除資料 WHERE Department = Accounting ; ON DELETE CASCADE 刪除所有符合參考完整性限制的相關資料列

131 SQL 檢視 (View) SQL 檢視是一種虛擬的資料表, 由其他的資料表或檢視表構成 SQL 檢視本身沒有資料, 其資料來自其他的資料表或檢視以 SELECT 陳述式定義 SQL 檢視 SQL92 的原始定義不允許在定義檢視的 SELECT 陳述式中使用 ORDER BY 但是 Oracle 和 MS SQL Server 都可以在檢視中使用 ORDER BY SQL 檢視是外部觀點的子集合

132 SQL 檢視用途隱藏欄位或資料列呈現計算欄位的結果隱藏複雜的 SQL 語法在應用程式與資料表之間提供一個中介層為資料表設定多組不同權限的處理方式設定不同的觸發機制

133 範例 : CREATE VIEW CREATE VIEW CustomerNameView AS SELECT Name AS CustomerName FROM CUSTOMER; SELECT * FROM CustomerNameView ORDER BY CustomerName; 建立檢視

134 在應用程式碼中嵌入 SQL SQL 可以被嵌在觸發機制 (trigger) 預存程序 (stored procedure) 以及程式碼之中問題 : 如何在 SQL 資料表欄位值與程式碼變數值之間轉換資料? 解決方案 : 物件導向程式語言 PL/SQL 問題 : SQL 語言與程式語言之間的情境差異 SQL 陳述式傳回一整個集合的資料列, 但是應用程式每次只能處理一個資料列解決方案 : 將 SQL 結果當成一個虛擬的檔案

135 觸發機制 (Trigger) 觸發機制是一種預存的程式,DBMS 在特定的事件發生時執行相關的觸發機制觸發機制有三種型態 : BEFORE INSTEAD OF 和 AFTER 每一種型態都可以用在 INSERT UPDATE DELETE 三種資料操作指令因此共計有九種觸發機制 Oracle 支援全部的 ( 九種 ) 觸發機制 MS SQL Server 只支援六種 ( 只有 INSTEAD OF 和 AFTER)

136 使用觸發機制當觸發機制被觸動時,DBMS 提供 : UPDATE: 更新前的舊資料值和更新後的新資料值 INSERT: 新增後的新資料值 DELETE: 刪除前的舊資料值上述資料的提供方式各家 DBMS 不同

137 預存程序 (Stored Procedure) 預存程序是儲存在資料庫中的一段事先經過編譯的程式碼 Oracle 的預存程序以 PL/SQL 或 Java 寫成 MS SQL Server 的預存程序以 TRANSACT-SQL 寫成預存程序可以接受輸入參數, 可以在執行完畢後傳回一個結果預存程序的呼叫方式 : 在程式語言內呼叫, 例如 :Java C# 在描述語言 (Scripting Language) 中呼叫, 例如 :JavaScript, VBScript 在 SQL 命令提示中執行, 例如 SQL Plus, Query Analyzer

138 預存程序的優點安全性高, 預存程序儲存在資料庫伺服器上, 客戶端看不到內容降低網路流量 DBMS 編譯預存程序可進行最佳化共享程式碼降低工作量標準化處理程序開發人員可以分工合作

139 在應用程式碼中使用 SQL SQL 可以嵌在應用程式之中使用必須執行數個 SQL 敘述才能構成一個外部觀點應用程式執行 SQL 敘述, 然後將結果顯示在表格控制項 (Grid Control) 中

140 在應用程式碼中使用 SQL( 續 ) 應用程式以表單處理使用者的輸入, 包括 : 使用下拉式選單和文字方塊供使用者輸入透過外部鍵產生相關聯的資料 SQL 嵌入程式碼的詳細情況因選用的程式語言和資料處理方式而異

141 Examples of DBMS In-class demonstration Tiny college..\..\nccu-mba-94-1-mis_graduate\ [Textbook 2] Database Systems_Rob-Instructor Resources 6th ed\2.3additional Faculty Files-Data Files Solun for Stu\Databases\Ch04\Ch04_College.mdb Medical system..\..\nccu-mba-94-1-mis_graduate\[assignments]\assgn_5_mis_erd_medical Clicnic & Norm with Sol.doc Sport scoring..\..\nccu-mba-94-1-mis_graduate\ [Textbook Stu\Databases\Ch04\Ch04_JCBC_version2.mdb 2] Database Systems_Rob-Instructor Resources 6th ed\2.3additional Faculty Files-Data Files Solun for Rental car..\..\nccu-mba-94-1-mis_graduate\[demo_access Auto Auctions Data]\Chap27Start.mdb Ticket Search..\..\NCCU-MBA-94-1-MIS_Graduate\[Group Project]\Public Transportation Ticket\CCUBA_MIS_PROJECT_Pub Trans Tickets.mdb..\..\NCCU-MBA-94-1-MIS_Graduate\[Group Project]\Public Transportation Ticket\project.ppt Article Order..\..\NCCU-MBA-94-1-MIS_Graduate\[Group Project]\MagTech\MIS-MagTech 電子期刊訂購系統 ( 建議使用華康唐風隸字體瀏覽 ).mdb..\..\nccu-mba-94-1-mis_graduate\[group Project]\MagTech\MIS-MagTech 電子期刊訂購系統.ppt

142 Reference Database Systems, Peter Rob (2004) Database Processing, Kroenke (2005), 審閱 : 范錚強, 校閱 : 鄧姚文, 譯者 : 李毅騁 Info Technology for Mgmt., Turban (2006) MIS, James A. O Brien (2005) Knowledge Management: Challenges, Solutions, and Technologies, Becerra-Fernandez (2004) OSS 入口網站 OSS 桌面應用增進計畫中文目錄網站資策會

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘 ITE 資訊專業人員鑑定資料庫系統開發與設計實務試卷編號 :IDS101 注意事項一本測驗為單面印刷試題, 共計十三頁第二至十三頁為四十道學科試題, 測驗時間 90 分鐘 : 每題 2.5 分, 總測驗時間為 90 分鐘二執行 CSF 測驗系統 -Client