作业一USINGDBMS使用DB2及SQL基本练习课件

1、作業一: USING DBMS(使用DB2 及SQL 基本練習)報告人：學生楊群期 學號：69421502課程:高等資料庫講師:楊維邦教授準備作業 (下載DB2)DB2 V8.2.2Windows for 32bit system下載檔案休息是為了走更長遠的路安裝DB2程式解壓縮Setup安裝完成 (如右圖)建立資料庫建立TEST資料庫第一題：write an appropriate set of SQL definitions for this database.(Fig 4.5)操作方式有:SQL指令精靈或圖型介面工具工具/命令編輯區使用SQL指令建立TABLEcreate table P

2、 (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 ke

3、y (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#) ) ;利用精靈或圖型介面工具建立TABLETABLE建立完成輸入資料至TABLE用SQL指令將各TABLE的資料值建立Insert into s values(S1,Smith,20,London);Insert into s values(S2,Jones,10,Paris);I

4、nsert 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);In

5、sert 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

6、,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不論何種公司發展的資料庫,一般而言建立表格的基本指令格式區分以下幾類:表格名稱欄位描述儲存參

7、數設定其他參數設定限制條件整理自:儒林出版精鍊Oracle 8/8i資料庫系統與應用(第5章)表格名稱在同一資料庫中不得定義重覆名稱表格名稱有長度的限制不包含引號不區分大小寫必需以字母開頭字元內容的限制(AZ,09,$,#,.,)欄位描述(欄位名/類型/長度)CHAR(N)固定長字元串,N位元長VARCHAR2(N)變動字元串,最長NLONG(N)長變動字元,最長NDATE日期/時間欄位NUMBER(P,S)數字欄位,全部P,小數點後SRAW OR LONG RAW二進位儲存MLSLABEL 安全級別(ORACLE自設)儲存參數設定STORAGE 子句用來控制分配給表格的儲存空間大小MINEX

8、TENTS參數 分配的區間,預設是1MAXEXTENTS參數 擁有的最大區間數儲存參數設定TABLESPACE定義表格放在那個表空間中PCTEFREE區塊內預留的空的空間百分比,預設是10%PCTUSED區塊內已使用空間最小百分比,預設是40%PARLLLEL加速對表格掃描可以使用的併行查詢行程個數CHCHE指明該表格經常被存取CLUSTER指明該表格放置在叢集中其他參數設定INITRANS 指定表格的每一資料區塊分配的行程個數值MAXTRANS指定可同時修改表格的資料區塊的最大行程數RECOVERABLE指定表格可還原UNRECOVERABLE指定表格不可還原AS子查詢 將子查詢回捲的記錄插

9、入到所建立的表格中限制條件CHECK 確保值在一定範圍內DEFAULT 在欄位內放入預設值NOT NULL 在欄位內保持一個值PRIMARY KEY 不得為空值且該值是唯一值FOREIGN KEY 該值依附另一表格的主鍵值UNIQUE 確保該值對於表格是唯一的例如:MYSQL CREATE TABLE SyntaxCREATE TEMPORARY TABLE IF NOT EXISTS tbl_name (create_definition,.) column_definition type table_options select_statement option ref:/doc/refm

10、an/5.0/en/create-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)：所代表的是實際儲存資料的結構

11、。以醫院醫療資訊系統(HIS)膳食系統為例 外部層(External Level)：檔案名稱：FOD102 膳食履歷資料檔FOD107 膳食基本資料檔FOD108 伙食身份及 請款單位代碼可輸入病床號碼、病歷號碼、身份證號，將住院病患每日之膳食起伙止伙等資料，如日期、時間、原因等輸入後，再作新增或修改之動作。來查詢膳食基本資料檔。以醫院醫療資訊系統(HIS)膳食系統為例 概念層(Conceptual Level)以醫院醫療資訊系統(HIS)膳食系統為例內部層(Internal Level)系統目錄 ( System Catalog )系統目錄存放著:系統中有那些資料表(Table)、各資料表中

12、含有哪些綱要內容、資料的統計結果、各資料表的擁有者(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

13、 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 orangeUPDATE PSET COLOR = OrangeWHERE P.COLOR = Red;C: delete all projects for which there are no shipments第五題write a program with embedd

14、ed SQL statements to list all. (Fig 4.5)Embeded SQL 是普通的程式語言中加入資料庫語言的一種複合式語言。為了區分 資料庫語言和程式語言的差別，在每個資料庫語言之前必須加一些關鍵字 EXEC SQL 。 因為此語言穿插著普通語言和資料庫的語言，所以必須經過前處理（precompiler process）把資料 把資料庫語言轉換成普通語言的程式碼，然後才可利用普通語言編譯器來編譯成可執行檔。實作#include #include #include #include void main() EXEC SQL INCLUDE SQLCA; EXEC S

15、QL BEGIN DECLARE SECTION; char msgbuffer500; char dbname7=TEST; /* Name of database */ char s5; char sname20; smallint status; char city20; 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 );whi