DBMS’10 Term Project 教師: 李強老師 助教: 林宣佑、袁國斌、劉佩琦 EMail:{a6771349,jordan7652,ayumi416}@dblab.csie.ncku.edu.tw

Term Project Goal 分組需求 due day 使用現成套裝的 DBMS軟體 (如 Microsoft SQL server, MySQL…等) 來建立一個系統資料庫 例如：人事薪資系統, 學生學籍系統…等), 然後使用者可以利用該資料庫來取得要求的查詢結果。 分組需求 三人一組(和paper報告相同) due day 2010 demo 時間表會在公布於課程網頁上。 注意：拒收遲交的專案。

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

Requirement (cont.) 須提供能夠讓使用者下達 SQL 指令的介面。 這個介面可直接利用DBMS軟體內部所提供的SQL指令查詢做處理。(Demo時不接受command line的方式。) 使用者下達 SQL 後，必需將符合 SQL 要求的結果顯示在畫面上。

Requirement (cont.) 在你的DBMS中，必需能下達下列的 SQL Query Modify the database 需有 CREATE, DROP table 和 INSERT, DELETE, UPDATE tuple 功能 Basic queries in SQL SELECT-FROM-WHERE Ordering of query results (ORDER BY & DESC/ASC) Set operations (UNION) Complex queries in SQL Nested queries (IN) Aggregate functions (COUNT, SUM, MAX, MIN, AVG)

Report 系統架構與環境 資料庫設計 系統使用說明 E-R schema diagram (詳細文字說明每個 table, attribute, relationship 的意義和關係) SQL syntax (create database, create table, primary key, foreign key…等，需加詳細註解) 系統使用說明

評分標準 Total (100%) = 系統建置與DEMO(70%) + document(30%) 系統建置與DEMO(70%) Database requirement (20%): 符合助教的規定 (介面，database requirement) DEMO (50%): 功能 Document (30%) 報告的架構完整度 (10%) 內容詳細度 (20%) 註：嚴禁抄襲，抄襲一律零分！

介面

Operation Requirements DBMS’10 Term Project Operation Requirements

Modify the database CREATE TABLE DROP TABLE INSERT a tuple DELETE a tuple UPDATE a tuple Example Suppose we want to create a temporary table that has the name, number of employees, and total salaries for each department. CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(10), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER); INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SQL)

Basic queries in SQL SELECT-FROM-WHERE 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 =, <, <=, >, >=, != Example Query 1: Retrieve the name and address of all employees who work for the ‘Research’ department. Q1: SELECT FNAME,LNAME,ADDRESS FROM EMPLOYEE,DEPARTMENT WHERE DNAME =‘Research’ AND DNUMBER = DNO

Basic queries in SQL (Cont.) DISTINCT：Eliminates the duplicate tuples SELECT DISTINCT <attribute list> FROM <table list> WHERE <condition> Example Query 2: Retrieve the all distinct salary values of employees. Q2: SELECT DISTINCT SALARY FROM EMPLOYEE

Basic queries in SQL (Cont.) UNION：Set operations SELECT <attribute list> FROM <table list> WHERE <condition> There is a union operation (UNION) operations Example Query 4: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. Q4: ( SELECT PNAME FROM PROJECT,DEPARTMENT,EMPLOYEE WHERE DNUM = DNUMBER AND MGRSSN = SSN AND LNAME =‘Smith’) UNION FROM PROJECT,WORKS_ON,EMPLOYEE WHERE PNUMBER = PNO AND ESSN = SSN AND LNAME =‘Smith’)

Complex Basic queries in SQL (Cont.) Aggregate functions Include COUNT, SUM, MAX, MIN, and AVG Example Query 6a: Find the maximum salary, the minimum salary, and the average salary among employees who work for the ‘Research’ department. Q6a: SELECT MAX(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

Complex Basic queries in SQL (Cont.) GROUP BY & HAVING：Grouping SELECT <attribute list> FROM <table list> WHERE <condition> GROUP BY <grouping attribute(s)> HAVING <group condition> Example Query 7: For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project. Q7: SELECT PNUMBER,PNAME,COUNT(*) FROM PROJECT,WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER,PNAME HAVING COUNT(*)>2

Demo 時間表 6/14(一) 18:00~20:00 6/15(二) 10:00~11:50 18:00~20:00 6/15(二) 10:00~11:50 18:00~20:00 6/17(四) 10:00~11:50 18:00~20:00 每組demo 10分鐘 如全部時段皆不行時,和助教另約時間