1 / 17

DBMS Project

DBMS Project. 教師 : 李強老師 助教 : 郭晉元 、黃品介 、廖恆慶 {cykuo,free999, kim_gtob}@dblab.csie.ncku.edu.tw. Term Project. Goal 使用 C 或 C++ 來建立一個系統資料庫 請同學從課程網站提供的資料庫 (2011 中華職棒 ) ，寫一支程式可以利用該資料庫以及使用者輸入的 SQL 指令來取得要求的查詢結果。 分組需求 一人一組 due day 2012/6/10 ( 日 ) 23:59 demo 時間表會在公布於課程網頁上。. 評分標準.

kerry
Download Presentation

DBMS Project

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DBMS Project 教師: 李強老師 助教:郭晉元、黃品介、廖恆慶 {cykuo,free999, kim_gtob}@dblab.csie.ncku.edu.tw

  2. Term Project • Goal • 使用C或C++來建立一個系統資料庫 • 請同學從課程網站提供的資料庫(2011中華職棒) ，寫一支程式可以利用該資料庫以及使用者輸入的SQL指令來取得要求的查詢結果。 • 分組需求 • 一人一組 • due day 2012/6/10 (日)23:59 • demo 時間表會在公布於課程網頁上。

  3. 評分標準 • Total (100%) = DEMO(70%) + document(30%) • DEMO(70%) • 根據輸出結果是否正確評分(70%) • 輸入輸出請簡單明瞭，也要方便助教確認是否正確 • Document (30%) • 報告的架構完整度 (10%) • 內容詳細度 (20%) • 註：嚴禁抄襲，抄襲一律零分！

  4. Database • 2011中華職棒資料庫內含5個Table ，包含：BAT(打者)、PITCH(投手)、PLAYER(球員)、TEAM(球隊)、FIELD(球場)。 檔名分別為：BAT.txt , PITCH.txt , PLAYER.txt , TEAM.txt , FIELD.txt。 • 每個Table的attribute名稱、attribute value 皆為Tab鍵區隔。

  5. Table BAT • TABLE BAT是打者的打擊數據，包含13個attribute：B_name 打者的名字、 B_team 打者所屬的隊伍、GB 打者的出賽場數、AB 打者的打數、RBI 打者的打點數、R 打者的得分數、H 打者的安打數、HR 打者的全壘打數、BB 打者的四死球數、SO 打者的三振數、SB 打者的盜壘數、AVG 打者的打擊率、 B_habbit 球員的打擊慣用手。

  6. Table PITCH • TABLE PITCH是投手的投球成績，包含9個attribute：P_name 投手的名字、 P_team 投手所屬的隊伍。、GP 投手的出賽場數、W 投手的勝場數、L 投手的敗場數、SV 投手的救援成功數、IP 投手的投球局數、ERA 投手的自責分率、 P_habbit球員的投球慣用手

  7. Table PLAYER • TABLEPLAYER是球員的基本資料資料庫，包含6個attribute：PL_name球員的名字、 P_position 球員的守備位置、PL_number 球員的背號、PL_team 球員所屬的隊伍、PL_pitchhab 球員的投球慣用手、 PL_bathab 球員的打擊慣用手。

  8. Table TEAM • TABLETEAM是球隊的基本資料，包含6個attribute：T_name 球隊的名稱、T_group 球隊的公司、T_date 球隊的創立日期、T_cham 球隊的總冠軍數、T_field 球隊的主場球場、T_con 主場球場的容量。

  9. Table FIELD • TABLEFIELD是球場的基本資料，包含5個attribute：F_name 球場的名稱、F_con 球場的最大觀眾容量、F_location 球場的位置、F_year 球場的建立年分、F_material 球場的內野材質。

  10. Document • 系統架構與環境 • 系統使用說明 • 放一些操作截圖+說明

  11. Basic queries in SQL • SELECT-FROM-WHERE (20%) SELECT <attribute list> FROM <table list> WHERE <condition> • Number of <attribute list> ranging from 1 to N. • Number of <table list> ranging from 1 to N. • Number of <condition> ranging from 0 to N. • Logical comparison operators are =, <, <=, >, >=, != Query 1: Retrieve the name and address of all employees who work forthe ‘Research’ department. Q1:SELECTFNAME,LNAME,ADDRESS FROM EMPLOYEE,DEPARTMENT WHEREDNAME =‘Research’ AND DNUMBER = DNO Example

  12. Basic queries in SQL (Cont.) • DISTINCT (10%)：Eliminates the duplicate tuples SELECT DISTINCT <attribute list> FROM <table list> WHERE <condition> Example Query 2-1: Retrieve the all distinct salary values of employees. Q2-1:SELECTDISTINCTSALARY FROMEMPLOYEE

  13. Basic queries in SQL (Cont.) • UNION (10%)：Set operations SELECT <attribute list> FROM <table list> WHERE <condition> • There is a union operation (UNION) operations Example Query 3:Male a list of all project numbers for projects that involve an employee whose last name is ‘Smith’ as a worker or as a manager of the department that controls the project. Q3: ( SELECT PNAME FROM PROJECT,DEPARTMENT,EMPLOYEE WHERE DNUM = DNUMBER AND MGRSSN = SSN AND LNAME =‘Smith’) UNION ( SELECT PNAME FROM PROJECT,WORKS_ON,EMPLOYEE WHERE PNUMBER = PNO AND ESSN = SSN AND LNAME =‘Smith’)

  14. Complex Basic queries in SQL (Cont.) • Aggregate functions (20%) • Include COUNT (4%), SUM (4%), MAX(4%), MIN(4%), and AVG(4%) Example • Query 6a: Find the maximum salary, the minimum salary, and the average salary among employees who work for the ‘Research’ department. • Q6a: • SELECTMAX(SALARY),MIN(SALARY),AVG(SALARY) • FROM EMPLOYEE,DEPARTMENT • WHERE DNO=DNUMBER AND DNAME=‘Research’ • Queries 6b: Retrieve the total number of employees in the company • Q6b: • SELECT COUNT(*) • FROM EMPLOYEE

  15. Complex Basic queries in SQL (Cont.) • ORDER BY (10%) ：Sort SELECT <attribute list> FROM <table list> WHERE <condition> ORDERBY <attribute list> Example • Query 8: Retrieve a list of employees and the projects each works in, ordered by the employee’s department, and within each department ordered alphabetically by employee last name. • Q8: • SELECT DNAME,LNAME,FNAME,PNAME • FROM DEPARTMENT,EMPLOYEE,WORKS_ON,PROJECT • WHERE DNUMBER = DNO AND SSN = ESSN AND • PNO = PNUMBER • ORDER BYDNAME,LNAME

  16. 檔案上傳 • 請同學將所有檔案 ( 執行的時候需要用的任何檔案 )，壓縮成 學號_project.rar (或是.zip) 例如：F74974039_project.rar • 壓縮檔請上傳至FTP： IP:140.116.247.193 帳號：dbms 密碼：dbms2012

  17. Demo • 每組demo10分鐘 • 作業系統限定windows 編譯環境限定Dev C, VS 無法滿足上述需求者請自備筆電 • 建議自備測資(SQL指令)，當然助教也會請你打出助教要的指令。

More Related