1 / 12

DBMS Project

DBMS Project. 教師 : 李強老師 助教 : 郭晉元 、黃品介 、廖恆慶 {cykuo,free999, kim_gtob}@dblab.csie.ncku.edu.tw. Term Project. Goal 使用 C 或 C++ 來建立一個系統資料庫 請同學自行建立一個資料庫 ( 資料庫建立方式以同學方便為主，但是要讓助教方便判別 SQL 輸入後的結果是否正確， ex: 用 .txt 檔建立，以 tab 鍵區隔每個 attribute) ，寫一支程式可以利用該資料庫以及使用者輸入的 SQL 指令來取得要求的查詢結果。 分組需求 一人一組 due day

fran
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++來建立一個系統資料庫 • 請同學自行建立一個資料庫(資料庫建立方式以同學方便為主，但是要讓助教方便判別SQL輸入後的結果是否正確，ex:用.txt檔建立，以tab鍵區隔每個attribute)，寫一支程式可以利用該資料庫以及使用者輸入的SQL指令來取得要求的查詢結果。 • 分組需求 • 一人一組 • due day 2012/6/10 (日)23:59 • demo 時間表會在公布於課程網頁上。

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

  4. Requirement • 底下將說明期末專案的需求，同學們所建立的資料庫要滿足底下的需求可以得到分數。 • Entity type 需求 • 最少 5 個 entity types (tables) • Attribute 需求 • 每個 table 至少有 5 個 attributes • 每個 table 都要有 key attribute • Relationship 需求 • 這些 entities 間需含有下列的兩種 relationship • Binary relationship, Ternary relationship • 需標示 relationship 間的 cardinality ratio • 註：未達到一個要求扣5分！

  5. Document • 系統架構與環境 • 資料庫設計 • E-R schema diagram (詳細文字說明每個 table, attribute, relationship 的意義和關係) • 系統使用說明 • 放一些操作截圖+說明

  6. 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

  7. 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

  8. 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’)

  9. 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

  10. 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

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

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

More Related