# 关系代数(Relational Aigebra)：

## Union

Duplicate tuples will not appear in the output as (D-3) both in R and S.
R and S must satisfy the following rules:

Rule 1: have the same degree Rule 2:domain of the ith attribute both of R and S must be same

## difference

semantics: R-S is the relation containing all tuples in R that do not appear in S.

## insersection

R intersection S returns all tuples that appears both in R and S

## cartesian product (卡迪尔积)（cross product）

Assuming R has n attributes and S has m attributes respectively,the cartesian product can be written as:

result of the above set operation is:

Duplicate tuples contain in ouput !!!

## select operation

This operation retrieves a subset of tuples in a relation that satisfy a select condition.

such as : AGE=19 is a condition and student is a relation.
And two and more condition :
The selection operation is commutative. The below are equivalent.

## projection operation

Now we can combine projection and selection.

# RA’s join

## Join

Return all tuples in RxS that satisfy the join condition. Derivation

example:

### equi join（=）

A join is called an equijoin if only equality operator is used in all join conditions.
< join condition> must always have = operation

### natural join（*）

if an attribute is common both in two relation,we can remove one in the join natural join omit the condition

## Outer Joins (Special Case of EquiJoin)

How to keep dangling tuples in the result of a join?

### left outer join

is similar to a natural join but keep all dangling tuples of R1.

### right outer join

is similar to a natural join but keep all dangling tuples of R2.

### full outer join

is similar to a natural join but keep all dangling tuples of both R1 and R2.

The advantages of outer join is to take the union of tuples from two relations that are not union compatible.

# Division

Precondition: in A/B, the attributes in B must be included in the schema for A. Also, the result has attributes A-B

Another example is: Suppose we want to find all the students who have selected all courses (See Figure 3.19 Course Table ) provided by the school.
We can think of it as three steps:
1.We can obtain the NO. of all courses provided by the school by:

2.We can also find all SSN, cno pairs for which the student has selected courses by:

3.Now we need to find all students who selected all the courses. The divide operation provides exactly those students:

# View

an interesting example

create table table1 (bm float )
insert into table1 values(5000)

create view TestViewCheckOption AS
select * from Table1 where Bm < 5003
with check option

update TestViewCheckOption set Bm = 5005
go

update error because 5005 can not appear in view with check option.

And a view can be changed by alter statement.

``````  ALTER VIEW view_name [(column_list)] [WITH ENCRYPTION]
AS select_statement
[WITH CHECK OPTION]
``````

# Specifying constraints

## not null constraint

For item must contain a not null value

## unique constraint

can not have same value.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

## primary key constraint

A primary key must contain unique values and cannot contain NULL values. must have and only one

## Foreign key constraint

two tables, one is referencing table, another is referenced table columns in referencing table must primary key or other candicate key in referenced table

## check constraint

limit the value range that can be placed in a column

## default constraint

insert a default value into column if no other value is specified

defalut can also use system function like getdate()

Add constraints to an existing table by using the ALTER TABLE statement.
drop it

# index

A database index enables the database application to find data quickly without having to scan the whole table.

# select statement

The SELECT clause lists what columns to return. The FROM clause which indicates the table(s) from which data is to be retrieved. The WHERE clause specifies which rows to retrieve. The GROUP BY clause groups rows sharing a property so that an aggregate function can be applied to each group. The WHERE clause is applied before the GROUP BY clause. The HAVING clause selects among the groups defined by the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate. The ORDER BY clause specifies an order in which to return the rows. Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.

## alias

shown with new name or new column

## distinct

show only distinct rows in column

## between or not between (range search)

get res of column in a range

## in or not in (set membership search)

specify multiple values in where clause

## like or not like (pattern match search)

use for inexact condition search

The % wildcard matches zero or more characters of any type. The _ wildcard matches exactly one character of any type.

To match strings that there are at least one character between ‘a’ and ‘c’ .
SELECT SNAME
FROM STUDENT
WHERE SNAME LIKE ‘a_%c’;

### escape character

If the search strings can include the wildcards(%,_) itself, we can use an escape character to represent the wildcards.
For example, to match the string ‘20%’, we can use :
Like ‘20#%’ ESCAPE ‘#’

sort

## aggregate function （聚合函数）

COUNT
(1) count number of rows of tables
(2) count distinct values for a given column

distinct is not an argument in the function,it use before function is
applied.

AVG
MAX MIN SUM

## group by clouse

group the result-set by one or more columns with function

## having clouse

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

# Multiple table (sql多表)

SQL allows us to query multiple tables in one SELECT-FROM-WHERE statement.

The SELECT and WHERE can refer to the attributes of any of the tables once we list each table in the FROM clause. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT. a table (base table, view, or joined table) can JOIN to itself in a self-join.

table student

table department

## outer join

Rows are returned even when there are no matches through the JOIN critieria.

### Right outer join

The RIGHT OUTER JOIN returns all rows from the second table.

Although 35 have no students we should list it too.

### Full outer join

A full outer join combines the effect of applying both left and right outer joins.

# Alias

Table names can be abbreviated in order to simplify what is typed in with the query.

## date format

date ------- to_char

select the name,job and date of hire of the employees in department 20(format the hiredate column using a picture MM/DD/YY).
Select ename “employee”,job, to_char(hiredate,’MM/DD/YY’) “HireDate”
From emp
Where deptno=20

Use a picture to format hiredate as DAY(day of the week),MONTH(name of the month),DD(day of the month) and YYYY(year).
Select to_char(hiredate,’DAY,MONTH,DD,YYYY’ ) “HireDate”
From EMP

Which employees were hired in March?
Select ename “Employee”,hiredate
From emp
Where to_char(hiredate,’MON’)=‘3月’

Show the weekday of the first day of the month in which each employee was hired.
Select ename “employee”,hiredate,to_char(trunc(hiredate,’month’),’day’)
From emp

## trunc

1 trunc用于对值的截断。用法有两种：sysdate=2010-10-20

trunc(NUMBER)截断数字:trunc(n1,n2),n2表示要截断到哪一位。也可为负数，如：
trunc(19.85)——>19; trunc(19.85,1)——>19.8; trunc(DATE)截断日期
截取今天：trunc(sysdate,‘dd’)或trunc(sysdate)——》2012-03-24
截取本周第一天：trunc(sysdate,‘d’)——》2012-3-18
截取本月第一天：trunc(sysdate,‘mm’)——》2012-3-1
截取本年第一天：trunc(sysdate,‘y’)——》2012-1-1

## last_day and next_day

last_day求一个日期所在月的最后一天
next_day(日期,‘sun|mon|…|fri|sat’)从日期后的下一天开始找，如果与相应的星期满足，则返回满足条件的日期。

SQL> select last_day(sysdate) from dual;

SQL> select next_day(sysdate,‘mon’) from dual;

SQL> select next_day(last_day(sysdate),‘fri’) from dual

# set operation

## union

union removes duplicate rows. union all keeps duplicate rows.

## comparison-operator

ALL – the comparison must be true for all returned values.
ANY – The comparison need only be true for one returned value.
IN may be used in place of = ANY.
NOT IN may be used in place of != ALL.

# Trivial FD (平凡函数依赖)：

such as {stu,num,id}
{stu,num} -> { stu } is a Trivial FD
{stu,num} -> { id } is not a Trivial FD

# 数据库函数依赖和范式：

1NF、2NF、3NF、BCNF

## attribute clouse （computing F+）

### super key and primary

AB is a superkey of R since (AB)+ = ABCDE. Since A+ = A, B+ = BD, neither A nor B is a superkey. Hence ，AB is a candidate key.

one

``````for each pair AiAj, i ！= j
if Ai or Aj is a candidate key
then AiAj is not a candidate key;
else compute (Ai Aj)+;
if (AiAj)+ = A1 A2 ... An
then (Ai Aj) is a candidate key;
``````

two

Relation schema: R = (A, B, C, D, E)
F = {A->BC, CD->E, A->D, B->D, E->A}
(1)Find A+, B+, BC+
(2)Find Candidate keys of R

hence candicate keys are ?

A?B?D?E? BC? CD?
Candidate keys : A 、E、 BC、 CD

ER图解析