Relation - 國立臺灣大學資訊工程學系

資料庫系統簡介
 資料庫系統 (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
INSERT/SELECT
簡易查詢－ SELECT 敘述
簡易查詢－ SELECT 敘述
 基本用法
 多資料表的查詢
 設定資料表及欄位的別名
基本用法
多資料表的查詢
多資料表的查詢
設定資料表及欄位的別名
用查詢結果建立新資料表
－ SELECT INTO
用查詢結果建立新資料表
－ SELECT INTO
更新記錄－ UPDATE 敘述
更新記錄－ UPDATE 敘述
 基本應用
 引用其它資料表的值來更新
基本應用
基本應用
引用其它資料表的值來更新
刪除記錄－ DELETE
 DELETE 敘述
DELETE 敘述
DELETE 敘述
DELETE 敘述
 在 DELETE 敘述中加上 FROM 子句, 還可以
引用其他資料表的值來做為刪除的條件：
查詢資料－
善用 SELECT 敘述







SELECT 敘述的基本結構
SELECT 子句
FROM 子句
WHERE 子句
GROUP BY 子句
HAVING 子句
ORDER BY 子句
SELECT 敘述的基本結構
SELECT 子句
SELECT 子句




指定欄位名稱
是否顯示重複記錄：ALL 與 DISTINCT
TOP n 與 TOPnPERCENT
查詢具備 IDENTITY 或 ROWGUID 屬性的
欄位
 設定與使用欄位別名
指定欄位名稱
*
 column_name
 expression
指定欄位名稱
指定欄位名稱
是否顯示重複記錄：
ALL 與 DISTINCT
TOP n 與 TOPnPERCENT
設定與使用欄位別名
FROM 子句
FROM 子句
 定義資料表別名
 JOIN 的基本原理
 JOIN 的類型：INNER、LEFT、RIGHT、
FULL 和 CROSS
 Self-Joins：自己 JOIN 自己
定義資料表別名
JOIN 的基本原理
JOIN 的基本原理
JOIN 的基本原理
JOIN 的類型





[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
CROSS JOIN
JOIN 的類型
JOIN 的類型
JOIN 的類型
Self-Joins：自己 JOIN 自己
Self-Joins：自己 JOIN 自己
WHERE 子句
GROUP BY 子句
GROUP BY 基本用法
基本用法
基本用法
HAVING 子句
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 值
檢查是否為 NULL 值
ISNULL( ) 函數
 ISNULL ( ) 函數可來用替換 NULL 值, 其語
法如下：
NULL 值的運算
建立檢視表
檢視表的用途
 產品售價和製造商的資料表結構如下：
檢視表的用途
檢視表的用途
檢視表的用途
檢視表與資料表的差異
使用檢視表的優點




增加可讀性
資料安全及保密
降低查詢的複雜度
方便程式維護
用 CREATE VIEW 敘述
建立檢視表




指定檢視表的欄位別名
為 CREATE VIEW 敘述加密
結構描述繫結 WITH SCHEMABINDING
檢查檢視表的資料變動
用 CREATE VIEW 敘述
建立檢視表
用 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
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
Application
ODBC driver
Data Source
 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 DBMSspecific code
 Database can be across a network
 ODBC is standard, so the same program can be
used (in theory) to access multiple database
systems
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
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
 http://developer.java.sun.com/developer/Books/J
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)
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.<TYPE>,
ResultSet.<CONCUR>)
 Corresponds to SQL cursor features
 <TYPE> 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
 <CONCUR> is one of
 CONCUR_READ_ONLY: this statement doesn’t allow
updates
 CONCUR_UPDATABLE: this statement allows
updates
 Defaults:
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
results.getMetaData();
rsmd.getColumnCount();
ResultSetMetaData rsmd =
int numCols =
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();

Download Report