资料库系统专题.doc

資料庫系統專題 設計報告題目 : 職業工會會員管理系統教 授：黃三益 老師組 別：第六組學 生：蘇聖義 m9142321陳佳雯 m9222406035蔡明安 m9322406025黃正倫 m9322406044目 錄壹、功能說明2貳、ERD3參、關聯綱目4肆、SQL敘述_新增5伍、SQL敘述_查詢、統計15陸、開發工具20柒、安裝說明21捌、執行畫面26玖、心得報告41壹、 功能說明職業工會組織是台灣非常重要的人民組織，是依各行各業、屬性、願景相同者所組成的團體，本系統主要為協助會務人員處理日常事務的資訊系統。 A. 會員資料管理 - 可建立會員編號、會員姓名、公司名稱、通訊資料等資料。 - 提供各種查詢功能以供快速查詢會員資料或繳費紀錄。 B. 費用收支管理 - 各項費用收入資料建立。 - 各項費用支出資料建立。 - 各項費用收支資料查詢。 - 年度費用預算編列輸入。貳、 ERD參、 關聯綱目肆、 SQL敘述_新增用途SQL command新增會員資料(1)insert into member (member_no,member_name,member_tel,member_addr,ext_no,site_no,dept_no) values(M001,劉湘川,02-89123456,台北市萬華區,215,S001,1100);insert into member (member_no,member_name,member_tel,member_addr,ext_no,site_no,dept_no) values(M002,詹美鳳,089-324576,台東縣延平鄉,3606,S005,1400);insert into member (member_no,member_name,member_tel,member_addr,ext_no,site_no,dept_no) values(M003,蔡志展,089-247588,台東縣成功鎮,172,S005,1400);新增會員資料(2)insert into member (member_no,member_name,member_tel,member_addr,ext_no,site_no,dept_no) values(M004,張清榮,02-89314567,台北市松山區,225,S002,1100);insert into member (member_no,member_name,member_tel,member_addr,ext_no,site_no,dept_no) values(M005,蘇友泉,07-8023124,高雄市前鎮區,360,S004,1300);insert into member (member_no,member_name,member_tel,member_addr,ext_no,site_no,dept_no) values(M006,石萬壽,04-2195637,台中市西區,5223,S003,1200);新增會員資料(3)insert into member (member_no,member_name,member_tel,member_addr,ext_no,site_no,dept_no) values(M007,溫振華,07-8013257,高雄市小港區,610,S004,1300);insert into member (member_no,member_name,member_tel,member_addr,ext_no,site_no,dept_no) values(M008,黃森泉,02-22561147,台北市大同區,175,S001,1100);insert into member (member_no,member_name,member_tel,member_addr,ext_no,site_no,dept_no) values(M009,王柏山,04-2234567,台中市東區,2257,S003,1200);新增部門資料insert into dept (dept_no,dept_name,dept_addr,dept_tel,dept_type,boss) values(1100,台北總部,台北市中正區健康路100號,02-89191234,北,jack);insert into dept (dept_no,dept_name,dept_addr,dept_tel,dept_type,boss) values(1200,台中分部,台中市西屯區中華路300號,04-22356677,中,judy);insert into dept (dept_no,dept_name,dept_addr,dept_tel,dept_type,boss) values(1300,高雄分部,高雄市鼓山區致力路200號,07-5525678,南,peter);insert into dept (dept_no,dept_name,dept_addr,dept_tel,dept_type,boss) values(1400,台東分部,台東縣池上鄉公園路50號,089-315789,東,victor);新增公司資料insert into site (site_no,site_name,site_tel,site_addr,boss) values(S001,致得,02-89457788,台北市大同區50號5F,june);insert into site (site_no,site_name,site_tel,site_addr,boss) values(S002,明惠,02-89349852,台北市松山區40號4F,joan);insert into site (site_no,site_name,site_tel,site_addr,boss) values(S003,台新,04-22348789,台中市南區30號3F,lee);insert into site (site_no,site_name,site_tel,site_addr,boss) values(S004,品力,07-8013325,高雄市前鎮區20號2F,ricky);insert into site (site_no,site_name,site_tel,site_addr,boss) values(S005,全文,089-247666,台東縣成功鎮100號2F,ann);新增費用資料insert into expense (expense_no,expense_name,expense_type) values(A001,勞保費,I);insert into expense (expense_no,expense_name,expense_type) values(A002,健保費,I);insert into expense (expense_no,expense_name,expense_type) values(A003,會費,I);insert into expense (expense_no,expense_name,expense_type) values(K001,管理費,O);insert into expense (expense_no,expense_name,expense_type) values(K002,行政費,O);insert into expense (expense_no,expense_name,expense_type) values(K003,租金,O);insert into expense (expense_no,expense_name,expense_type) values(K004,其它,O);新增繳費資料(1)insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M001,2004/7/1 00:00:00,A003,3000.00);insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M001,2004/10/1 00:00:00,A001,1010.00);insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M001,2004/11/1 00:00:00,A001,1010.00);insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M001,2004/11/1 00:00:00,A002,1200.00);新增繳費資料(2)insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M002,2004/7/1 00:00:00,A003,3000.00);insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M002,2004/10/1 00:00:00,A001,1030.00);insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M002,2004/11/1 00:00:00,A001,1030.00);insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M002,2004/11/1 00:00:00,A002,1200.00);新增繳費資料(3)insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M003,2004/11/1 00:00:00,A001,1030.00);insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M003,2004/11/1 00:00:00,A002,1250.00);insert into incoming (member_no,trans_date,expense_no,trans_amt) values(M004,2004/7/1 00:00:00,A003,3000.00);新增支出資料(1)insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1100,2004/11/1 00:00:00,K001,5000.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1100,2004/11/1 00:00:00,K002,2000.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1100,2004/11/1 00:00:00,K004,500.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1200,2004/11/1 00:00:00,K001,4000.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1200,2004/11/1 00:00:00,K002,3000.00);新增支出資料(2)insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1200,2004/11/1 00:00:00,K003,7000.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1200,2004/11/1 00:00:00,K004,700.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1300,2004/11/1 00:00:00,K001,3000.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1300,2004/11/1 00:00:00,K002,3500.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1300,2004/11/1 00:00:00,K003,6000.00);新增支出資料(3)insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1300,2004/11/1 00:00:00,K004,650.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1400,2004/11/1 00:00:00,K001,3500.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1400,2004/11/1 00:00:00,K002,4000.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1400,2004/11/1 00:00:00,K003,5500.00);insert into outgoing (dept_no,trans_date,expense_no,trans_amt) values(1400,2004/11/1 00:00:00,K004,450.00);執行結果：A. 新增會員資料結果B. 新增部門資料結果C. 新增公司資料結果D. 新增費用資料結果E. 新增繳資料結果F. 新增支出資料結果G. 新增預算資料結果伍、 SQL敘述_查詢、統計A.用途SQL command查詢會員溫振華基本資料select * from member where member_name = 溫振華;執行結果：B.用途SQL command查詢各工會部門有多少人員SELECT dept.dept_no, dept.dept_name, count(member.member_no) FROM dept LEFT OUTER JOIN member ON dept.dept_no = member.dept_no group by dept.dept_no;執行結果：C.用途SQL command查詢各月份，健保費的總收入select substring(convert(char(6),trans_date,112),1,6) yyymm, sum(trans_amt) total_amt from incoming where expense_no = A001group by substring(convert(char(6),trans_date,112),1,6);執行結果：D.用途SQL command查詢2004年11月未繳健保費的會員select member_no, member_name from member where member_no not in (select distinct member_no from incoming where expense_no = A001 and substring(convert(char(6),trans_date,112),1,6) = 200411);執行結果：E.用途SQL command統計各部門每個月份的支出金額SELECT dept.dept_no, substring(convert(char(6),trans_date,112),1,6) yyyymm, sum(trans_amt) total_amt FROM dept LEFT OUTER JOIN outgoing ON dept.dept_no = outgoing.dept_no group by dept.dept_no, substring(convert(char(6),trans_date,112),1,6);執行結果：F.用途SQL command統計各部門每個月份的收入金額SELECT dept.dept_no, substring(convert(char(6),trans_date,112),1,6) yyyymm, sum(trans_amt) total_amt FROM dept LEFT OUTER JOIN member ON dept.dept_no = member.dept_no LEFT OUTER JOIN incoming ON member.member_no = incoming.member_no group by dept.dept_no, substring(convert(char(6),trans_date,112),1,6);執行結果：陸、 開