DBMS’10 Term Project 教師: 李強老師 助教: 林宣佑、袁國斌、劉佩琦

Presentation on theme: "DBMS’10 Term Project 教師: 李強老師 助教: 林宣佑、袁國斌、劉佩琦"— Presentation transcript:

1 DBMS’10 Term Project 教師: 李強老師 助教: 林宣佑、袁國斌、劉佩琦

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

3 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

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

5 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)

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

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

8 介面

9 Operation Requirements
DBMS’10 Term Project Operation Requirements

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

11 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

12 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

13 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’)

14 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

15 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

16 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分鐘 如全部時段皆不行時,和助教另約時間