作業一 : USING DBMS ( 使用 DB2 及 SQL 基本練習 ) 報告人：學生楊群期 學號： 課程 : 高等資料庫 講師 : 楊維邦教授

準備作業 ( 下載 DB2)

DB2 V8.2.2

Windows for 32bit system

下載檔案 … 休息是為了走更長遠的路 ~

安裝 DB2 程式解壓縮 Setup 安裝完成 ( 如右圖 )

建立資料庫

建立 TEST 資料庫

第一題： write an appropriate set of SQL definitions for this database.(Fig 4.5) 操作方式有 : SQL 指令 精靈或圖型介面工具

工具 / 命令編輯區

使用 SQL 指令建立 TABLE create table P (P# char (5) not null, PNAME char (20), COLOR char (10), WEIGHT DECIMAL (6,1), CITY char (15), primary key (P#)); create table J (J# char (5) not null, JNAME char (20), CITY char (15), primary key (J#)); create table S (S# char (5) not null, SNAME char (20), STATUS smallint, CITY char (15), primary key (S#)); CREATE TABLE SPJ ( S# CHARACTER (5) NOT NULL, P# CHARACTER (5) NOT NULL, J# CHARACTER (5) NOT NULL, QTY INTEGER NOT NULL, PRIMARY KEY ( S#, P#, J#) ) ;

利用精靈或圖型介面工具 建立 TABLE

TABLE 建立完成

輸入資料至 TABLE

用 SQL 指令將各 TABLE 的資料值建立 Insert into s values('S1','Smith',20,'London'); Insert into s values('S2','Jones',10,'Paris'); Insert into s values('S3','Blake',30,'Paris'); Insert into s values('S4','Clark',20,'London'); Insert into s values('S5','Adams',30,'Athens'); Insert into p values('P1','Nut','Red',12,'London'); Insert into p values('P2','Bolt','Green',17,'Paris'); Insert into p values('P3','Screw','Blue',17,'Rome'); Insert into p values('P4','Screw','Red',14,'London'); Insert into p values('P5','Cam','Blue',12,'Paris'); Insert into p values('P6','Cog','Red',19,'London'); Insert into J values('J1','Sorter','Paris'); Insert into J values('J2','Display','Rome'); Insert into J values('J3','OCR','Athens'); Insert into J values('J4','Console','Athens'); Insert into J values('J5','RAID','London'); Insert into J values('J6','EDS','Oslo'); Insert into J values('J7','Tape','London');

利用精靈或圖型介面工具 建立資料

完成 Fig 4.5

第二題： what additional CREATE TABLE options dose that product support? 資料庫的種類很多, 較常提及的有 : IBM 公司 DB2 甲骨文公司 ORACLE 微軟公司 SQL SERVER 其他如 MYSQL,SYBASE ……. 雖各家發展資料庫延伸功能各異, 但基本 上都會提供一些相同的功能

CREATE TABLE SYNTAX 不論何種公司發展的資料庫, 一般而言建 立表格的基本指令格式區分以下幾類 : 表格名稱 欄位描述 儲存參數設定 其他參數設定 限制條件 整理自 : 儒林出版精鍊 Oracle 8/8i 資料庫系統與應用 ( 第 5 章 )

表格名稱 在同一資料庫中不得定義重覆名稱 表格名稱有長度的限制 不包含引號 不區分大小寫 必需以字母開頭 字元內容的限制

欄位描述 ( 欄位名 / 類型 / 長度 ) CHAR(N) 固定長字元串,N 位元長 VARCHAR2(N) 變動字元串, 最長 N LONG(N) 長變動字元, 最長 N DATE 日期 / 時間欄位 NUMBER(P,S) 數字欄位, 全部 P, 小數點後 S RAW OR LONG RAW 二進位儲存 MLSLABEL 安全級別 (ORACLE 自設 )

儲存參數設定 STORAGE 子句用來控制分配給表格的儲存空間大小 MINEXTENTS 參數 分配的區間, 預設是 1 MAXEXTENTS 參數 擁有的最大區間數 儲存參數設定 TABLESPACE 定義表格放在那個表空間中 PCTEFREE 區塊內預留的空的空間百分比, 預設是 10% PCTUSED 區塊內已使用空間最小百分比, 預設是 40% PARLLLEL 加速對表格掃描可以使用的併行查詢行程個數 CHCHE 指明該表格經常被存取 CLUSTER 指明該表格放置在叢集中

其他參數設定 INITRANS 指定表格的每一資料區塊分配 的行程個數值 MAXTRANS 指定可同時修改表格的資料 區塊的最大行程數 RECOVERABLE 指定表格可還原 UNRECOVERABLE 指定表格不可還原 AS 子查詢 將子查詢回捲的記錄插入到所 建立的表格中

限制條件 CHECK 確保值在一定範圍內 DEFAULT 在欄位內放入預設值 NOT NULL 在欄位內保持一個值 PRIMARY KEY 不得為空值且該值是唯一值 FOREIGN KEY 該值依附另一表格的主鍵值 UNIQUE 確保該值對於表格是唯一的

例如 :MYSQL CREATE TABLE Syntax CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [( create_definition,...)] [ column_definition ] [ type ] [ table_options ] [ select_statement ] option …… ref: table.html

第三題 : Dos that product support the information schema? If not, what does its catalog support look like? schema 以醫院醫療資訊系統 (HIS) 膳食系統為例 Catalog

各層的資料定義稱為 綱要 (Schema) 外部層 (External Level) ： 則代表使用者所看到的部 份。 概念層 (Conceptual Level) ： 為內部層與外部層之間的 橋樑。﹝此部份為資料庫管 理師所看到的部份﹞ 內部層 (Internal Level) ： 所代表的是實際儲存資料 的結構。

以醫院醫療資訊系統 (HIS) 膳食 系統為例 外部層 (External Level) ： 檔案名稱： FOD102 膳食履歷資料檔 FOD107 膳食基本資料檔 FOD108 伙食身份及 請款單位代碼 可輸入 病床號碼、病歷號碼、身份證號， 將住院病患每日之膳食起伙止伙等資料，如 日期、時間、原因等輸入後，再作新增或修 改之動作。來查詢膳食基本資料檔。

以醫院醫療資訊系統 (HIS) 膳食 系統為例 概念層 (Conceptual Level)

以醫院醫療資訊系統 (HIS) 膳食 系統為例 內部層 (Internal Level)

系統目錄 ( System Catalog ) 系統目錄存放著 : 系統中有那些資料表 (Table) 、各資料表中含有哪些綱要內容、 資料的統計結果、各資料表的擁有者 (Owner) 資料，可供哪些使用者使用資料、 各個視界 (Views) 等等。 “ Data about data"

第四題 :SQL 操作 A: insert a new supplier S10 into table S. the name and city are Smith and New York, respectively; the status is not yet known. B: change the color of all red parts to orange. C: delete all projects for which there are no shipments.

A:insert S10, Smith, New York into table S. INSERT INTO S (S#, SNAME, CITY) VALUES ('S10', 'Smith', 'New York');

B: change the color of all red parts to orange UPDATE P SET COLOR = 'Orange' WHERE P.COLOR = 'Red';

C: delete all projects for which there are no shipments

第五題 write a program with embedded SQL statements to list all …. (Fig 4.5) Embeded SQL 是普通的程式語言中加入資料庫語 言的一種複合式語言。為了區分 資料庫語言和程式 語言的差別，在每個資料庫語言之前必須加一些關 鍵字 EXEC SQL 。 因為此語言穿插著普通語言和資 料庫的語言，所以必須經過前處理（ precompiler process ）把資料 把資料庫語言轉換成普通語言的 程式碼，然後才可利用普通語言編譯器來編譯成可 執行檔。

實作 #include void main() { EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; char msgbuffer[500]; char dbname[7]="TEST"; /* Name of database */ char s[5]; char sname[20]; smallint status; char city[20]; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO :dbname; EXEC SQL DECLARE CS CURSOR FOR SELECT S#,SNAME,STATUS,CITY FROM S ORDER BY S#; printf("s# sname status city "); while (SQLCODE==0){ printf("%2s %10s %6d %10s\n",s,sname,status,city); } EXEC SQL CLOSE CS; EXEC SQL CONNECT RESET; }

第六題 : Let tables PART and PART_STRUCTURE be defined as … (Fig 4.6) PART_STRUCTURE MAJOR_P#MINOR_P#QTY P1 P2 P3 P4 P5 P2 P3 P4 P5 P