1、作業一:USING DBMS(使用DB2 及SQL 基本練習)報告人：學生楊群期 學號：69421502課程:高等資料庫講師:楊維邦教授準備作業(下載DB2)DB2 V8.2.2Windows for 32bit system下載檔案n休息是為了走更長遠的路安裝DB2n程式解壓縮nSetupn安裝完成 (如右圖)建立資料庫建立TEST資料庫第一題：nwrite an appropriate set of SQL definitions for this database.(Fig 4.5)n操作方式有:nSQL指令n精靈或圖型介面工具工具/命令編輯區使用SQL指令建立TABLEcreate t
2、able 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#
3、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的資料值建立nInsert into s values(S1,Smith,20,London);nInsert into s values(S2,Jones,10,Paris);nInsert into s values(S3,Blake,30,Paris);nInsert i
4、nto s values(S4,Clark,20,London);nInsert into s values(S5,Adams,30,Athens);nInsert into p values(P1,Nut,Red,12,London);nInsert into p values(P2,Bolt,Green,17,Paris);nInsert into p values(P3,Screw,Blue,17,Rome);nInsert into p values(P4,Screw,Red,14,London);nInsert into p values(P5,Cam,Blue,12,Paris);
5、nInsert into p values(P6,Cog,Red,19,London);nInsert into J values(J1,Sorter,Paris);nInsert into J values(J2,Display,Rome);nInsert into J values(J3,OCR,Athens);nInsert into J values(J4,Console,Athens);nInsert into J values(J5,RAID,London);nInsert into J values(J6,EDS,Oslo);nInsert into J values(J7,Ta
6、pe,London);利用精靈或圖型介面工具建立資料完成Fig 4.5第二題：nwhat additional CREATE TABLE options dose that product support?n資料庫的種類很多,較常提及的有:nIBM公司 DB2n甲骨文公司 ORACLEn微軟公司 SQL SERVERn 其他如 MYSQL,SYBASE.n雖各家發展資料庫延伸功能各異,但基本上都會提供一些相同的功能CREATE TABLE SYNTAXn不論何種公司發展的資料庫,一般而言建立表格的基本指令格式區分以下幾類:n表格名稱n欄位描述n儲存參數設定n其他參數設定n限制條件整理自:儒林出
7、版精鍊Oracle 8/8i資料庫系統與應用(第5章)表格名稱n在同一資料庫中不得定義重覆名稱n表格名稱有長度的限制n不包含引號n不區分大小寫n必需以字母開頭n字元內容的限制(AZ,09,$,#,.,)欄位描述(欄位名/類型/長度)nCHAR(N)固定長字元串,N位元長nVARCHAR2(N)變動字元串,最長NnLONG(N)長變動字元,最長NnDATE日期/時間欄位nNUMBER(P,S)數字欄位,全部P,小數點後SnRAW OR LONG RAW二進位儲存nMLSLABEL 安全級別(ORACLE自設)儲存參數設定nSTORAGE 子句用來控制分配給表格的儲存空間大小nMINEXTENTS
8、參數 分配的區間,預設是1nMAXEXTENTS參數 擁有的最大區間數n儲存參數設定nTABLESPACE定義表格放在那個表空間中nPCTEFREE區塊內預留的空的空間百分比,預設是10%nPCTUSED區塊內已使用空間最小百分比,預設是40%nPARLLLEL加速對表格掃描可以使用的併行查詢行程個數nCHCHE指明該表格經常被存取nCLUSTER指明該表格放置在叢集中其他參數設定nINITRANS 指定表格的每一資料區塊分配的行程個數值nMAXTRANS指定可同時修改表格的資料區塊的最大行程數nRECOVERABLE指定表格可還原nUNRECOVERABLE指定表格不可還原nAS子查詢 將子
9、查詢回捲的記錄插入到所建立的表格中限制條件nCHECK 確保值在一定範圍內nDEFAULT 在欄位內放入預設值nNOT NULL 在欄位內保持一個值nPRIMARY KEY 不得為空值且該值是唯一值nFOREIGN KEY 該值依附另一表格的主鍵值nUNIQUE 確保該值對於表格是唯一的例如:MYSQL CREATE TABLE Syntax CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name (create_definition,.)column_definition type table_options select_statement option
10、 ref:http:/ that product support the information schema?If not,what does its catalog support look like?n schema以醫院醫療資訊系統(HIS)膳食系統為例nCatalog各層的資料定義稱為綱要(Schema)n外部層(External Level)：則代表使用者所看到的部份。n概念層(Conceptual Level)：為內部層與外部層之間的橋樑。此部份為資料庫管理師所看到的部份n內部層(Internal Level)：所代表的是實際儲存資料的結構。以醫院醫療資訊系統(HIS)膳食系統為
11、例 外部層(External Level)：n檔案名稱：nFOD102 膳食履歷資料檔nFOD107 膳食基本資料檔nFOD108 伙食身份及 請款單位代碼n可輸入病床號碼、病歷號碼、身份證號，將住院病患每日之膳食起伙止伙等資料，如日期、時間、原因等輸入後，再作新增或修改之動作。來查詢膳食基本資料檔。以醫院醫療資訊系統(HIS)膳食系統為例 概念層(Conceptual Level)系統/子系統代號：FOD版本：系統/子系統名稱：膳食管理系統日期：檔案名稱：膳食履歷資料檔檔案代號：FOD102檔案結構：記錄長度：主鍵名稱：CRTNO+SEQNO (Unique)副鍵名稱：1.CRTNO+IPD
12、NO 2.EFDTE+ENDTE整數 小數01 病歷號碼CRTNOX1004 序號SEQNO90405 起伙日期EFDTEX0706 起伙時間EFTMEX0407 起伙原因STANSX0208 伙食種類BOARDX0109 伙食內容1BRDNO1X0210 停伙日期ENDTEX0711 停伙時間ENTMEX0412 停伙原因ENANSX0213 備註REMRKX8014 建立人員UPOPX0615 建立日期UPDTEX0716 過帳日期TRDTEX0717 過帳時間TRTMEX0418 批價表單編號IPBNO904長度內 容 說 明序號中 文 名 稱英文名稱型態以醫院醫療資訊系統(HIS)膳食
13、系統為例內部層(Internal Level)系統目錄(System Catalog)n系統目錄存放著:系統中有那些資料表(Table)、各資料表中含有哪些綱要內容、資料的統計結果、各資料表的擁有者(Owner)資料，可供哪些使用者使用資料、各個視界(Views)等等。n“Data about data第四題:SQL操作nA:insert a new supplier S10 into table S.the name and city are Smith and New York,respectively;the status is not yet known.nB:change the c
14、olor of all red parts to orange.nC: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 orangeUPDATE PSET COLOR=OrangeWHERE P.COLOR=Red;C:delete all projects for whi
15、ch there are no shipments第五題nwrite a program with embedded SQL statements to list all.(Fig 4.5)nEmbeded SQL 是普通的程式語言中加入資料庫語言的一種複合式語言。為了區分 資料庫語言和程式語言的差別，在每個資料庫語言之前必須加一些關鍵字 EXEC SQL。因為此語言穿插著普通語言和資料庫的語言，所以必須經過前處理（precompiler process）把資料 把資料庫語言轉換成普通語言的程式碼，然後才可利用普通語言編譯器來編譯成可執行檔。實作#include#include#include
16、#include void main()EXEC SQL INCLUDE SQLCA;EXEC SQL 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);while(SQLCODE=0)printf(%2s%10s%6d%10sn,s,sname,status,city);EXEC SQL CLOSE CS;EXEC SQL CONNECT RESET;第六題:nLet tables PART and PART_STRUCTURE be defined as(Fig 4.6)PART_STRUCTUREMAJOR_P#MINOR_P#QTYP1P1P2P2P3P4P5P2P3P3P4P5P5P62413983
