資料庫系統簡介  資料庫系統 (Database System) 分成兩個部份： 資料庫 (Database) 是儲存資料的地方。 資料庫管理系統 (DataBase Management System, DBMS) 則是指管理資料庫的軟體, 它們負責使用 者與資料庫之間的溝通, 如存取資料庫中的資料、 以及管理資料庫的各項事務等。

關聯式資料庫的內部結構  關聯式資料庫最大的特色是將資料分類儲存 在資料表 (Table) 中。

資料庫管理系統的基本功能  資料定義  資料處理  資料安全  資料備份

資料庫系統的使用者  資料庫設計者 (Database Designer)  資料庫管理者 (DataBase Administrator, DBA)  應用程式設計者 (Application Designer)  一般使用者 (End user)

資料庫規劃  簡易的規劃流程  收集資料項並轉換成欄位  認識關聯、 Primary Key 與 Foreign Key  資料的完整性  資料表的關聯種類  資料庫的正規化分析

簡易的規劃流程  第一階段：收集完整且必要的資料項, 並轉 換成資料表的欄位形式。  第二階段：將收集的欄位做適當分類後, 歸 入不同的資料表中, 並建立資料表間的關聯。

收集資料項並轉換成欄位  收集必要且完整的資料項

收集資料項並轉換成欄位  轉換成資料表的欄位

收集資料項並轉換成欄位  轉換成資料表的欄位

關聯  下圖的訂單資料表與客戶資料表, 因為客戶 編號欄位而產生關聯：

分割資料表並建立關聯的優點  節省儲存空間  減少輸入錯誤  方便資料修改

節省儲存空間  當書籍資料表需要使用到作者名稱或分類時, 就可以 經由關聯, 到作者資料表與分類資料表中選取：

減少輸入錯誤

Primary key  Primary key 是用來辨識記錄的欄位, 具有唯一 性, 且不允許重複。

Foreign key  在關聯式資料庫中, 資料表之間的關係是藉 由 Foreign key 來建立的：

資料的完整性  實體完整性 (Entity Integrity)  區域完整性 (Domain Integrity)  參考完整性 (Referential Integrity)  使用者定義的完整性 (User-defined Integrity)

資料表的關聯種類  一對一關聯 (one-to-one)  一對多關聯 (one-to-many)  多對多關聯 (many-to-many)

一對一關聯 (one-to-one)

一對多關聯 (one-to-many)

多對多關聯 (many to many)

資料庫的正規化分析  正規化就是要讓資料庫中重複的資料減到最 少, 讓我們能夠快速地找到所要的資料, 以提 高關聯式資料庫的效能。

資料庫的正規化之前  資料表必須先設好 Primary key  確定資料表中所有欄位的值是不可分割的

資料庫的正規化  規則 1 ：除去同類型的欄位  規則 2 ：非 Primary key 的欄位需與整個 Primary key 有直接相關性  規則 3 ：非 Primary key 的欄位間不應有從 屬關係

規則 1 ： 除去同類型的欄位  規則 1 稱為第一階正規化 (1st Normal Form ； 1NF) － 其目的在除去同類型的欄位, 亦即同 類型的欄位不能重複：

規則 2 ：非 Primary key 的欄位需與整個 Primary key 有直接 相關性  規則 2 稱為第二階正規化 (2nd Normal Form ； 2NF) － 目的在除去僅與部份 Primary key 相依的欄位。

規則 3 ：非 Primary key 的欄位間不應有從屬 關係  規則 3 稱為第三階正規化 (3rd Normal Form ； 3NF) － 目的在除去所有非 Primary key 的欄 位間的相依性。

正規化的另類思考  不必要的分割  人工的分割

不必要的分割  正規化的工作有時不必做得非常徹底, 例如 郵遞區號與縣市、區若分割後, 每次查詢都 要多一道還原手續, 實無必要：

人工的分割  有時為了增加資料處理的效率, 我們會將已 經符合 3NF 的資料表再做分割。

認識 SQL 語言與資料型別

 SQL 語言的興起與語法標準  SQL 語言與傳統程式語言的差別  關鍵字、子句與敘述  SQL 語言的功能分類  資料型別  欄位的 NULL 值與 DEFAULT 值  識別名稱 (Identifier)

SQL 語言的興起與語法標準  SQL 語言是在 1970 年代晚期, 由 IBM 公司在美 國加州聖荷西的研究單位所發展出來的一套程 式語言, 當時是使用於 DB2 關聯式資料庫系統。  為了避免各產品之間的 SQL 語法不相容, 因此 由 ANSI (American National Standards Institute, 美國國國家標準局 ) 制定 SQL-92, 定義出 SQL 的關鍵字與語法標準, 以提高各家產品在 SQL 語法上的相容性。

SQL 語言與傳統程式語言的差別  SQL 語言寫成的程式必須應用在資料庫管理 系統中, 本身並不能獨立執行, 為非程序性 (non-procedural) 語言：

結構化查詢語言 SQL  假設要在訂單資料庫中建立一個客戶資料表, 可以執行以下的 SQL 敘述：

結構化查詢語言 SQL  建立出來的資料表：

關鍵字、子句與敘述  SQL 語法的基礎是子句 (clause), 子句中會包 括一些關鍵字 (keyword) 。一組可產生存取 資料庫結果的子句集合則稱為敘述 (statement) 。

SQL 語言的功能分類  資料定義語言 (Data Definition Language, DDL)

SQL 語言的功能分類  資料處理語言 (Data Manipulation Language, DML)

SQL 語言的功能分類  資料控制語言 (Data Control Language, DCL) 一般是指專門用來設定資料庫物件使用權限 的敘述。

資料型別  整數  精確位數  近似浮點數值  日期時間  字串  Unicode 字串  二元碼字串  貨幣  標記  其它

識別名稱的表示法  識別名稱的可用字元：

用 CREATE TABLE 敘述 建立資料表  CREATE TABLE 敘述的語法  設定資料表名稱  定義欄位屬性  設定欄位的條件約束  設定資料表條件約束  指定檔案群組  建立計算欄位

CREATE TABLE 敘述的語法

NULL 值

DEFAULT 值  建立一個訂單資料表, 其中有 3 個欄位設有 DEFAUL 值：

DEFAULT 值  分別加入 3 筆記錄到訂單資料表中：

DEFAULT 值  將訂單資料表中的記錄顯示出來：

NULL 、 NOT NULL 條件約束

新增記錄 － INSERT 敘述  基本用法  INSERT/SELECT

基本用法

 沒有被指定資料的欄位, MS SQL Server 處 理的方式：

INSERT/SELECT

簡易查詢 － SELECT 敘述

 基本用法  多資料表的查詢  設定資料表及欄位的別名

基本用法

多資料表的查詢

設定資料表及欄位的別名

用查詢結果建立新資料表 － SELECT INTO

更新記錄 － UPDATE 敘述

 基本應用  引用其它資料表的值來更新

基本應用

引用其它資料表的值來更新

刪除記錄 － DELETE  DELETE 敘述

DELETE 敘述

 在 DELETE 敘述中加上 FROM 子句, 還可以 引用其他資料表的值來做為刪除的條件：

查詢資料 － 善用 SELECT 敘述

 SELECT 敘述的基本結構  SELECT 子句  FROM 子句  WHERE 子句  GROUP BY 子句  HAVING 子句  ORDER BY 子句

SELECT 敘述的基本結構

SELECT 子句

 指定欄位名稱  是否顯示重複記錄： ALL 與 DISTINCT  TOP n 與 TOPnPERCENT  查詢具備 IDENTITY 或 ROWGUID 屬性的 欄位  設定與使用欄位別名

指定欄位名稱  *  column_name  expression

指定欄位名稱

是否顯示重複記錄： ALL 與 DISTINCT

TOP n 與 TOPnPERCENT

設定與使用欄位別名

FROM 子句

 定義資料表別名  JOIN 的基本原理  JOIN 的類型： INNER 、 LEFT 、 RIGHT 、 FULL 和 CROSS  Self-Joins ：自己 JOIN 自己

定義資料表別名

JOIN 的基本原理

JOIN 的類型  [INNER] JOIN  LEFT [OUTER] JOIN  RIGHT [OUTER] JOIN  FULL [OUTER] JOIN  CROSS JOIN

JOIN 的類型

Self-Joins ：自己 JOIN 自己

WHERE 子句

GROUP BY 子句

GROUP BY 基本用法

基本用法

HAVING 子句

ORDER BY 子句

子查詢 Subquery  子查詢的語法與範例  子查詢的類型與處理方式  獨立子查詢與關聯子查詢

子查詢的語法與範例  子查詢的語法和 SELECT 敘述一樣, 但有限 制： 整個子查詢敘述需用小括弧 ( ) 括住。 子查詢中不能使用 COMPUTE 、 INTO 子句。 若子查詢中有用到 SELECT TOP n …, 才可設定 ORDER BY 子句來排序。

子查詢的語法與範例

處理子查詢傳回結果的方法  方法一：直接取值  方法二：比對清單  方法三：測試存在

子查詢的類型與處理方法  3 種處理方法的適用時機如下表所示：

子查詢的類型與處理方法

直接取值的子查詢

比對清單的子查詢  IN ： IN 運算子可用來判斷給定的值是否在 指定的子查詢中。

比對清單的子查詢  ALL ： ALL 運算子表示在查詢中的結果必須 滿足子查詢中的所有結果。

比對清單的子查詢  ANY 、 SOME ： ANY 運算子表示查詢結果只要滿 足子查詢中任一個值即可； SOME 是 SQL-92 標準 的用法, 意思與 ANY 相同。

測試存在子查詢

相同功能的不同查詢方式

獨立子查詢

關聯子查詢

檢查是否為 NULL 值

ISNULL( ) 函數  ISNULL ( ) 函數可來用替換 NULL 值, 其語 法如下：

NULL 值的運算

建立檢視表

檢視表的用途  產品售價和製造商的資料表結構如下：

檢視表的用途

檢視表與資料表的差異

使用檢視表的優點  增加可讀性  資料安全及保密  降低查詢的複雜度  方便程式維護

用 CREATE VIEW 敘述 建立檢視表  指定檢視表的欄位別名  為 CREATE VIEW 敘述加密  結構描述繫結 WITH SCHEMABINDING  檢查檢視表的資料變動

用 CREATE VIEW 敘述 建立檢視表

 CREATE VIEW 敘述串的 select_ statement 不可以使用 INTO 、 ORDER BY 、 COMPUTE 或 COMPUTE BY 子句：

用 CREATE VIEW 敘述 建立檢視表  要排序, 可以等到實際使用檢視表做查詢時 再設定：

指定檢視表的欄位別名

用 DROP VIEW 敘述刪除檢視表

Rules for Using Indexes 1. Use on larger tables 2. Index the primary key of each table 3. Index search fields (fields frequently in WHERE clause) 4. Fields in SQL ORDER BY and GROUP BY commands 5. When there are >100 values but not when there are <30 values

Rules for Using Indexes 6. DBMS may have limit on number of indexes per table and number of bytes per indexed field(s) 7. Null values will not be referenced from an index 8. Use indexes heavily for non-volatile databases; limit the use of indexes for volatile databases Why? Because modifications (e.g. inserts, deletes) require updates to occur in index files

Query Optimization  Parallel Query Processing  Override Automatic Query Optimization  Data Block Size -- Performance tradeoffs: Block contention Random vs. sequential row access speed Row size Overhead  Balancing I/O Across Disk Controllers

Query Optimization  Wise use of indexes  Compatible data types  Simple queries  Avoid query nesting  Temporary tables for query groups  Select only needed columns  No sort without index

Database Application Development

Writing Applications with SQL  SQL is not a general purpose programming language. + Tailored for data retrieval and manipulation + Relatively easy to optimize and parallelize -Can ’ t write entire apps in SQL alone Options: Make the query language “ turing complete ” Avoids the “ impedance mismatch ” but, loses advantages of relational lang simplicity Allow SQL to be embedded in regular programming languages. Q: What needs to be solved to make the latter approach work?

Embedded SQL  DBMS vendors usually provide “ host language bindings ” E.g. for C or COBOL Allow SQL statements to be called from within a program Typically you preprocess your programs Preprocessor generates calls to a proprietary DB connectivity library  General pattern One call to connect to the right database (login, etc.) SQL statements can refer to host variables from the language  Typically vendor-specific We won ’ t look at any in detail, we ’ ll look at standard stuff  Problem SQL relations are (multi-)sets, no a priori bound on the number of records. No such data structure in C. SQL supports a mechanism called a cursor to handle this.

Database APIs: alternative to embedding  Rather than modify compiler, add a library with database calls (API) special procedures/objects passes SQL strings from language, presents result sets in a language-friendly way ODBC a C/C++ standard started on Windows JDBC a Java equivalent Most scripting languages have similar things  E.g. For Perl there is DBI, “ oraPerl ”, other packages  Mostly DBMS-neutral at least try to hide distinctions across different DBMSs

Architecture  A lookup service maps “ data source names ” ( “ DSNs ” ) to drivers Typically handled by OS  Based on the DSN used, a “ driver ” is linked into the app at runtime  The driver traps calls, translates them into DBMS- specific code  Database can be across a network  ODBC is standard, so the same program can be used (in theory) to access multiple database systems  Data source may not even be an SQL database! Application ODBC driver Data Source

ODBC/JDBC  Various vendors provide drivers MS bundles a bunch into Windows Vendors like DataDirect and OpenLink sell drivers for multiple OSes  Drivers for various data sources Relational DBMSs (Oracle, DB2, SQL Server, Informix, etc.) “ Desktop ” DBMSs (Access, Dbase, Paradox, FoxPro, etc.) Spreadsheets (MS Excel, Lotus 1-2-3, etc.) Delimited text files (.CSV,.TXT, etc.)  You can use JDBC/ODBC clients over many data sources E.g. MS Query comes with many versions of MS Office (msqry32.exe)  Can write your own Java or C++ programs against xDBC

JDBC  Part of Java, very easy to use  Java comes with a JDBC-to-ODBC bridge So JDBC code can talk to any ODBC data source E.g. look in your Windows Control Panel for ODBC drivers!  JDBC tutorial online DBCTutorial/

JDBC Basics: Connections  A Connection is an object representing a login to a database // GET CONNECTION Connection con; try { con = DriverManager.getConnection( "jdbc:odbc:sailorsDB", userName,password); } catch(Exception e){ System.out.println(e); }  Eventually you close the connection // CLOSE CONNECTION try { con.close(); } catch (Exception e) { System.out.println(e); }

JDBC Basics: Statements  You need a Statement object for each SQL statement // CREATE STATEMENT Statement stmt; try { stmt = con.createStatement(); } catch (Exception e){ System.out.println(e); } Soon we ’ ll say stmt.executeQuery( “ select …” );

CreateStatement cursor behavior  Two optional args to createStatement: createStatement(ResultSet., ResultSet. ) Corresponds to SQL cursor features  is one of TYPE_FORWARD_ONLY: can ’ t move cursor backward TYPE_SCROLL_INSENSITIVE: can move backward, but doesn ’ t show results of any updates TYPE_SCROLL_SENSITIVE: can move backward, will show updates from this statement  is one of CONCUR_READ_ONLY: this statement doesn ’ t allow updates CONCUR_UPDATABLE: this statement allows updates  Defaults: TYPE_FORWARD_ONLY and CONCUR_READ_ONLY

JDBC Basics: ResultSet  A ResultSet object serves as a cursor for the statement ’ s results (stmt.executeQuery()) // EXECUTE QUERY ResultSet results; try { results = stmt.executeQuery( "select * from Sailors") } catch (Exception e){ System.out.println(e); }  Obvious handy methods: results.next() advances cursor to next tuple  Returns “ false ” when the cursor slides off the table (beginning or end) “ scrollable ” cursors:  results.previous(), results.relative(int), results.absolute(int), results.first(), results.last(), results.beforeFirst(), results.afterLast()

ResultSet Metadata  Can find out stuff about the ResultSet schema via ResultSetMetaData ResultSetMetaData rsmd = results.getMetaData(); int numCols = rsmd.getColumnCount(); int i, rowcount = 0; // get column header info for (i=1; i <= numCols; i++){ if (i > 1) buf.append(","); buf.append (rsmd.getColumnLabel(i) ); } buf.append("\n");  Other ResultSetMetaData methods: getColumnType(i), isNullable(i), etc.

Getting Values in Current of Cursor  getString // break it off at 100 rows max while (results.next() && rowcount < 100){ // Loop through each column, getting the // column data and displaying for (i=1; i <= numCols; i++) { if (i > 1) buf.append(","); buf.append (results.getString(i) ); } buf.append("\n"); rowcount++; }  Similarly, getFloat, getInt, etc.

Updating Current of Cursor  Update fields in current of cursor: result.next(); result.updateInt("Rating", 10);  Also updateString, updateFloat, etc.  Or can always submit a full SQL UPDATE statement Via executeQuery()  The original statement must have been CONCUR_UPDATABLE in either case!

Cleaning up Neatly try { // CLOSE RESULT SET results.close(); // CLOSE STATEMENT stmt.close(); // CLOSE CONNECTION con.close(); } catch (Exception e) { System.out.println(e); }

Putting it Together (w/o try/catch) Connection con = DriverManager.getConnection("jdbc:odbc:weblog",us erName,password); Statement stmt = con.createStatement(); ResultSet results = stmt.executeQuery("select * from Sailors") ResultSetMetaData rsmd = results.getMetaData(); int numCols = rsmd.getColumnCount(), i; StringBuffer buf = new StringBuffer(); while (results.next() && rowcount < 100){ for (i=1; i <= numCols; i++) { if (i > 1) buf.append(","); buf.append(results.getString(i)); } buf.append("\n"); } results.close(); stmt.close(); con.close();