数据库原理与应用（Principles of Database Systems）

引言

1.1What is DBMS? What is Database?

1.2 Files vs. Database

1.3 Why Use a DBMS

1.4 Why Study Database

1.5 Data, Data Model and Data Schema

数据模型

2.1 Hierarchical Data Model

2.2 Network Data Model

2.3 Relational Algebra

数据库系统的用户接口以及SQL语言User Interfaces and SQL Language

User Interface of DBMS

一、一个数据库系统都会提供一些用户接口去支持用户访问数据库

• 查询语言
• 访问和维护工具（GUI）
• APIs（应用程序访问数据库）：JDBC、ODBC
• 类库

Example of TQL & GQL

Relational Query Languages

Formal Relational Query Lauguage

SQL Language

Important terms and concepts

Example Instances

Basic SQL Query

Conceptual Evaluation Strategy

Simple Example

A Note on Range Variables

Expressions and Strings

• UNION(并集):Can be used to compute the union of any two union-compatible sets of tuples
• INTERSECT（交集）：Can be used to compute the intersection of any two union-compatible sets of tuples

Nested Queries

• 非关联嵌套：in

Nested Queries with Correlation

• Nested Queries with Correlation(关联嵌套): EXISTS is another set comparison operater, like IN;

More on set-Comparison Operators

1. More on Set-Comparison Operators: We’ve already seen IN, EXISTS, and UNIQUE. Can also use NOT IN, NOT EXISTS, and UNIQUE.
2. Also available: op ANY, op ALL, op IN <,>,=,<=,>=,!=

Rewriting INTERSECT Queries Using IN

1. Similarly, EXCEPT queries re-written using NOT IN

Division in SQL

3. Find sailors who’ve reseaved all boats（除法）
   SELECT S.name FROM Sailors S WHERE NOT EXISTS ((SELECT B.bid FROM Boats B) EXCEPT (SELECT R.bid FROM Reserves R WHERE R.sid=S.sid))
   用所有的船减去这个水手所预定的船，得到这个水手没有预定的船，如果不存在，得到这个水手预定了所有的船
4. 查找预定了所有船的水手

SELECT S.name 
    	FROM Sailors S 
    	WHERE NOT  EXISTS (SELECT B.bid 
    					 	  FROM Boats B 
    						  WHERE NOT EXISTS (SELECT R.bid
    											   FROM Reserves R
    											   WHERE R.bid=B.bid
    											    AND R.sid=S.sid))

Sailors S such that ...
			there is no boat B without ...
						a Reserves tuple showing S reserved 

Aggregate Operators

5. Aggregate Operators
   Significant extension of relational algebra.
   COUNT(*)
   COUNT([DISTINCT] A)
   SUM([DISTINCT] A)
   AVG([DISTINCT] A)
   MAX(A)
   MIN(A)

6. A is single column

7. The first query is illegal!
   (We’ll look into the reason a bit later, when we discuss GROUP BY)
   The third query is equivalent to the second query, and is allowed in the SQL/92 standard, but is not supported in some systems.

Motivation for Grouping

* So far, we've applied aggregate operators to all (qualifying) tuples. Sometimes, we want to apply them to each of several GOURPS of tuples.
* Consider: Find the age of the youngest sailor for each rating level.
	*. In general, 

Queries With GOURP BY and HAVING

	SELECT [DISTINCT] target-list
	FROM relation-list 
	WHERE qualification
	GOURP BY gouping-list
	HAVING group-qualification

• The target-list contains
  (i) attribute names
  (ii) terms with aggregate operation(eg. MIN(S.age))
  • The attribute list (i) must be a subset of grouping-list. Intuitively, each answer tuple corresponds to a gourp, and these attributes must have a single value per group.

Conceptual Evaluation

1. 对FROM子句里出现的relation-list做cross-product, 按WHERE子句里面的条件做筛选，把不满足条件的元组删除，得到符合条件的元组，按GROUP BY子句的gourping-list中属性值相等的原则，对经过筛选的元组做分组。
2. 按HAVING子句中的group-qualification对每个组作检查
3. 最后按照SELECT里面的要求做计算，每一个组得到一个元组
4. 在SELECT属性和HAVING属性的值必须在每个组里面是单一的，所以规定必须在GROUP BY子句初心，即是它的子集

Null Values(空值)

Field values in a tuple are sometimes unknown(e.g., a rateing has not been assigned) or inapplicable(e.g. no spouse's name)
	* SQL provides a special value null for such situations
The presence of null complicates many issue.
	* Special operators needed to check if value is /is not null
	* We need a 3-valued logic (true, false and nuknown)
	* Meaning of constructs must be defined carefully
	* New operators(in particular, outer joins) possible 

Some New Features of SQL

* CAST expression
* CASE expression
* Sub-query
* Outer Join
* Recursion

CAST Expression(类型转换)

CAST (Expression(NULL) AS Data-type)  

CASE Expression(编码)

Simple form:
	Officers(name, status, rank, title)
	
	SELECT name, CASE status 
						WHEN 1 THEN 'Active Duty'
						WHEN 2 THEN 'Reserve'
						WHEN 3 THEN 'Speicial Assignment'
						WHEN 4 THEN 'Retired'
						ELSE 'Unknown'
					END AS status
	FROM Officers;

Sub-query(子查询)

1. Embedded query & embedded query with correlation(关联查询与非关联查询)
2. The function of sub-queries have been enhanced in new SQL standard. Now they can be used in SELECT and FORM clause
      1. Scalar sub-query（标量子查询）
      2. Table expression（表表达式）
      3.  Common table expression（公共表表达式）

Scalar-query

The result of a sub-query is a single value. It can be used in the place where a value can occur.
Find the departments whose average bonus is higher than average salary(查找一个部门，它的平均奖金比平均工资高):

SELECT d.deptname, d.location 
	FROM dept AS d
	WHERE (SELECT avg(bonus)
			   FROM emp
			   WHERE deptno = d.deptno)
		      >(SELECT avg(salary)
			    FROM emp
			    WHERE deptno = d.deptno)

List the deptno, deptname, and the max salary of all departments located in New York ;

SELECT d.deptno, d.deptname, (SELECTMAX (salary)
							 FROM emp
							 WHERE deptno=d.deptno) AS maxpay
FROM dept AS d
WHERE d.location='New York';

Table Expression

The result of a sub-query is a table. It can be used in the place where a table can occur. 

SELECT startyear, avg(pay)
FROM (SELECT name, salary+bonus AS pay, 
			      year(startdate) AS startyear
	    FROM emp) AS emp2
GROUP BY startyear;

Find departments whose total payment is greater than 200000

SELECT deptno, totalpay 
	FROM (SELECT deptno, sum(salary)+sum(bonus) AS totalpay
			FROM emp
			GROUP BY deptno) AS payroll
	WHERE totalpay>200000;

Common Table Expression

In some complex query, a table expression may need occuring more than one time in the same SQL statements. Although it is permitted, the efficiency is low and there maybe inconsistency problem. 
在某些很复杂的查询语句中，一个表表表达式可能需要用到多次。
WITH clause can be used to define a common table expression. In fact, it defines a temporary view. 
Find the department who has the highest total payment:

Outer Join

Teacher(name, rank)姓名，职称
Course(subject, enrollment, quarter, teacher)
课程名字，选修人数，开课季度， 任课教师

WITH 
	innerjoin(name, rank, subject, enrollment) AS 
		(SELECT t.name ,t.rank, c.subject, c.enrollment) 
		 FROM teacher AS t, courses AS c
		 WHERE t.name=c.teacher AND c.quarter='Fall 96'), 
	teacher-only(name, rank) AS
		(SELECT name,rank
		 FROM teachers
		 EXCEPT ALL
		 SELECT name,rank
		 FROM innerjoin),
	course-only(subject, enrollment) AS
		(SELECT subject, enrollment 
		 FROM courses
		 EXCEPT ALL
		  SELECT subject, enrollment
		  FROM innerjoin)
SELECT name, rank, subject, enrollment
FROM innerjoin
UNION ALL
SELECT name, rank 
	CAST (NULL AS Varchar(20)) AS subject,
	CAST (NULL AS Integer) AS enrollment	
FROM teacher-only
UNION
SELECT CAST (NULL AS Varchar(20)) AS name, 
		CAST (NULL AS Varchar(20)) AS rank,
		subject, enrollment
FROM course-only;

Recursion(递归查询)

FedEmp(name, salary, manager)联邦雇员表
Find all employees under the management of Hoover and whose salary is more than 100000

WITH agent(name, salary) AS 
	((SELECT name,salary 
	  FROM FedEmp
	  WHERE manager='Hoover')//initial query
	UNION ALL
	 (SELECT f.name, f.salary
	  FROM agents AS a, FedEmp AS f
	  WHERE f.manager=a.name))//recursive query
SELECT name
FROM agents
WHERE salary>100000;//final query

Recursive Calculation

A classical “parts searching problem”
Componets(零件表)

Part	Subpart	QTY(quantity)
wing(机翼)	strut(机架)	5
wing	aileron(副翼)	1
wing	landing gear(登录装置)	1
wing	rivert(铆钉)	100
strut	rivert	10
aileron	hinge(铰链)	2
aileron	rivert	5
landing gear	hinge	3
landing gear	rivert	8
hinge	rivert	4

• Find how much rivets are used in one wing?
• A temporary view is defined to show the list of each subpart’s quantit used in a specified part;

WITH wingpart(subpart, qty) AS
	((SELECT subpart, qty
	   FROM components
	   WHERE part='wing')
	   UNION ALL
	   (SELECT c.subpart, w.qty*c.qty
	    FROM wingpart w, components c
	    WHERE w.subpart=c.part))
SELECT sum(qty) AS qty
FROM wingpart
WHERE subpart='rivert';

• Find all subparts and their total quantity needed to assemble

WITH wingpart (subpart, qty) AS
	((SELECT subpart, qty
	   FROM components
	   WHERE part='wing')
	 UNION ALL
	 (SELECT c.subpart, w.qty*c.qty
	  FROM wingpart w, components c
	  WHERE w.subpart=c.part))
SELECT subpart, sum(qty) AS qty 
FROM wingpart
GROUP BY subpart;

Recursive Search

• Typical airline route searching problem
• Find the lowest total cost route from SFO to JFK
  
  Flights

FlightNo	Origin	Destination	Cost
HY120	DFW	JFK	225
HY130	DFW	LAX	200
HY140	DFW	ORD	100
HY150	DFW	SFO	300
HY210	JFK	DFW	225
HY240	JFK	ORD	250
HY310	LAX	DFW	200
HY350	LAX	SFO	50
HY410	ORD	DFW	100
HY420	ORD	JFK	250
HY420	ORD	SFO	275
HY510	SFO	DFW	300
HY530	SFO	LAX	50
HY540	SFO	ORD	275

WITH trips(destination, route, nsegs, totalcost) AS
	((SELECT destination, CAST (destination AS varchar(20), 1, cost //initial query
	  FROM flights
	  WHERE origin='SFO')
	UNION ALL
	(SELECT f.destination,
			CAST(t.route ||','||f.destination AS varchar(20)
			t.nsegs+1, t.totalcost+f.cost         //recursive query
	  FROM trips t, flights f
	  WHERE t.destination=f.origin
	  		  AND f.destination<>'SFO'//stoping 
	  		  AND f.origin<>'JFK'
	  		  AND t.nsegs<=3))
SELECT route, totalcost 
FROM trips
WHERE destination='JFK' AND totalcost=
							(SELECT min(totalcost)
							 FROM trips
							 WHERE destination='JFK');

Data Manipulation Lauguage

• Insert
  Insert a tuple into a table

INSERT INTO EMPLOYEES VALUES ('Smith', 'John', '1980-06-10', 'Los Angles', 16, 45000)

• Delete
  Delete tuples fulfill qualifications

DELETE FROM Person WHERE Lastname='Rasmussen';

• Update
  Update the attributes’ value of tuple fulfill qualifications

UPDATE Person SET Address = 'Zhongshan 23' ,City= 'Nanjing' WHERE LastName = 'Wilson';

View in SQL

• General view
  • Virtual tables and derived from base tables
  • Logical data independence
  • Security of data
  • Update problems of view
• Temporary view and recursive query
  • WITH
  • RECURSIVE

Embedded SQL

• In order to access database in programs, and take further process to the query result, need to combine SQL and programming language(such as C/C++, etc)
• Problems should be solved:
  • How to accept SQL How to accept SQL statements in programming
    language
  • How to exchange data and messages between programming language and DBMS
  • The query result of DBMS is a set, how to transfer it to the variables in programming language
  • The data type of DBMS and programming language may not the same exactly

General Solutions

1. Embedded SQL

The most basic method. Through pre-compiling, transfer the embedded SQL statements to inner library functions call to access database
最早使用

2. Programming APIs

Offer a set of library functions or DLLs to programmer directly, linking with application program while compiling.
提供一组库函数，ODBC和JDBC

3. Class Library

Supported after emerging of OOP. Envelope the library functions to access database as a set of class, offering easier way to treat database in
programming language.
OOP面向对象出现后，封装了一些对数据库访问的一些类，提供了更简单的方式访问数据库

Usage of Embedded SQL(in C)

SQL statements can be used in C program directly:

• Begin with EXEC SQL, end with ‘;’
• Though host variables to transfer information between C and SQL. Host variables should be defined begin with EXEC SQL.
• In SQL statements, should add ‘:’ before host variables to distinguish with SQL’s own variable or attributes’ name.
• In host language (such as C), host variables are used as general variables.
• Can’t define host variables as Array or Structure.
• A special host variable, SQLCA (SQL Communication Area) EXEC SQL INCLUDE SQLCA
• Use SQLCA.SQLCODE to justify the state of result.
• Use indicator (short int) to treat NULL in host language.
• EXEC SQL开头，“;”结尾
• 通过宿主变量来在C代码和数据库系统之间传递信息，宿主变量是用EXEC SQL来定义的
• 在嵌入到C代码的SQL命令里面，我们可以用“:”来引用一个宿主变量的值
• 在C代码里面，宿主变量当成一个普通变量来使用
• 宿主变量不能定义成数组或者结构
• 特殊的宿主变量，SQLCA（SQL通信区）
  EXEC SQL INCLUDE SQLCA
• 用SQLCA.SQLCODE判断返回结果
• 在宿主语法中，用indicator(短整型)来表示NULL

Example of host variables defining

EXEC BEGIN DECLARE SECTION;
char SNO[7];
char GIVESNO[7];
char CNO[6];
char GIVENCNO[6];
float GRADE;
short GRADEI; /indicator of GRADE/
EXEC SQL END DECLARE SECTION;

Executable Statements

• CONNECT
  EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
• Execute DDL or DML Statements

 EXEC SQL INSERT INTO SC(SNO,CNO,GRADE) 
 				    VALUES(:SNO, :CNO, :GRADE);

• Execute Query Statements

    EXEC SQL SELECT GRADE
    					INTO :GRADE :GRADEI
    					FROM SC
    					WHERE SNO=:GIVENSNO AND
    								  CNO=:GIVENCNO;

• Because {SNO,CNO} is the key of SC, the result of
  this query has only one tuple. How to treat result if it
  has a set of tuples?

Cursor

• Define a cuser

 	EXEC SQL DECLARE <cursor name> CURSOR FOR
 	SELECT ...
 	FROM ...
 	WHERE ...

• EXEC SQL OPEN <cursor name>
  Some like open a file（看成一个文件）
• Fetch data from cursor

EXEC SQL FETCH <cursor name>
   		 INTO :hostvar1, :hostvar2, …;

• SQLCA.SQLCODE will return 100 when arriving the end of cursor
• CLOSE CURSOR <cursor name>

Dynamic SQL

Dynamic SQL is supported in SQL standard and most RDBMS pruducts

Dynamic SQL executed directly(可以直接运行的)

• Only used in the execution of non query SQL statements

EXEC SQL BEGIN DECLARE SECTION;
char sqlstring[200];//字符数组，动态拼一条语句
EXEC SQL END DECLARE SECTION;
char cond[150];//用户临时输入条件
strcpy( sqlstring, ”DELETE FROM STUDENT WHERE ”);//赋值
printf(“ Enter search condition :”);
scanf(“%s”, cond);	//输入条件
strcat( sqlstring, cond);//字符串拼接
EXEC SQL EXECUTE IMMEDIATE :sqlstring;//让数据库系统动态地立即执行SQL语句

Dynamic SQL with Dynamic parameters(带动态参数)

• Only used in the execution of non query SQL statements. Use place holder to realize dynamic parameter in SQL statement. Some like the macro processing method in C.
  用占位符在SQL语句中占位，运行时动态替换

EXEC SQL BEGIN DECLARE SECTION;
char sqlstring[200];
int birth_year;
EXEC SQL END DECLARE SECTION;
strcpy( sqlstring, ”DELETE FROM STUDENT WHERE
YEAR(BDATE) <= :y; ”);
printf(“ Enter birth year for delete :”);
scanf(“%d”, &birth_year);
EXEC SQL PREPARE PURGE FROM :sqlstring;
EXEC SQL EXECUTE PURGE USING :birth_year;

Dynamic SQL for query(动态查询语句)