Definition实在不知道怎么概括= =...

• Intention of a relation: relation definition and constrains
• Extention of a relation: the actual data, tuples

Relation properties

• Set Theory
  
  
  • Attributes has order (not necessary)
  • Value are used to identify tuples
  • Tuples dont have order
  • Tuples cant have duplication
• Attribute value are atomic
• Degree: How many attributes in the schema
• Cardinality: How many tuples in an instance

Constraints

• Let the DBMS to ensure the entry or modification on data are legal
• Give applications' bug no chance to ruin the data

Three Constraint Level

• Tuple-level
  
  
  • Domain restrictions (datatype)
  • Attribute comparisons (好像是指check)
• Relation-level
  
  
  • Special type of keys
    
    
    • Superkey: 能uniquely identifies a tuple的一个or一组attribute
    • Candidate key: Superkey的最小集, (最简单的一个or一组attribute)
    • Primary key: 被选中, 用来identity tuples的Candidate key
• Database-level
  
  
  • Referential integrity
    
    
    • Foreign key: 其他relation中的primary key
    • Referential integrity: 不允许出现和对应relation中的记录不符的情况
  • Inclusion dependencies: = =`不懂

Definitions

• Database: is a large and persistent collection of pieces of information organized in a way that facilitate efficient retrieval and modification.
• DBMS: is a program that manages details related to storage and access for a database.
• Schema: is a description of the data interface to the database.
• Instance: is a database that conforms to a given schema.
• Transaction: is an application-specified atomic and durable unit of work.
• View: is a relation in the external schema whose instance is determined by the instances of the relations in the conceptual schema.
• Trigger: is a procedure executed by the database in response to a change to the database instance

Three Level Schema Architecture

• External schema (view): visualized data
• Conceptual schema: logical structure
• Physical schema: file/storage devices

Data Independence

• Application access the data through an abstract data model provided by the DBMS, rather than direct access
• Physical: applications cant touch the storage stucture
• Logical: applications cant change the data organization

Four Transaction Properties

NoSQL据说就是要break these properties= =`IDK why...

• Atomic: once the transaction happens, it happened. No intermediate stage!
• Consistency: transaction follows the database consitences
• Isolated: concurrent transactions wont effect each other
• Durable: transaction results permanently

What DBMS do

• Remoce common code from applications
• Provide uniform access to data
• Guarantee data integrity
• manage concurrent access
• Protect against system failure
• Set access policies for data

SELECT

SELECT attribute-expression-list FROM relation-list [ WHERE condition ];

attribute-expression-list:

• [relation-name.]attribute
• [relation-name.]attribute [arithmatic computation] AS another name
  
  
  • i.e., E.Salary - 40000 AS SalaryDiff
• CASE WHEN ... THEN ...
  ELSE ... END
  
  
  • i.e., CASE WHEN E.Salary < 40000 THEN 0
    ELSE E.Salary - 40000 END

relation-list:

• list of table names
• seperate by comma

condition:

• arithmetic operation +, -, *, /
• comparisions =, <>, <, <=, >. >=
• logical connectives AND, OR, NOT
• attribute IN (Q)
• attribute NOT IN (Q)
• attribute op SOME (Q)
• attribute op ALL (Q)
• EXISTS (Q)
• NOT EXISTS (Q)
• IS [NOT] NULL

Read more...

我的QQ邮箱现在已经有3T容量了...
原来玩WordPress的时候其实就一直拿来做数据库备份, 现在容量变大几倍有余, 自然是备份的不二选择. 这可比FDC的大容量VPS划算多了, 特别是当你的网站并没有多大的时候. (书苑现在的数据库也都还没有1G...)

这个脚本来自小夜博客, 经我略加修改了一下.
下载: backup_to_mail.sh

很简单的一段bash script, 只需要自定义下mysql的用户名密码, 邮箱地址和要备份的目录.

然后进行下边的步骤:

首先要建立好放置备份文件的目录

mkdir /home/backup

然后安装发送邮件的程序

yum install sendmail mutt

把backup_to_mail.sh放到刚刚建立的目录中, 然后给脚本增加运行权限

chmod a+x /home/backup/backup_to_mail.sh

最后一步是设置定时运行, 输入

crontab -e

然后在vi编辑界面中加入下面这行文字

00 00 * * * /home/backup/backup_to_mail.sh

:x + enter就ok了.

其中00 00表示在每日00分, 00点的时候运行该脚本.

下边是脚本的内容, 以备附件失效和以后学习...

#!/bin/bash
MYSQL_USER=root                             #mysql username
MYSQL_PASS=***********                      #mysql password
MAIL_TO=*******@***.**                      #mailling to
WEB_DATA=/home/wwwroot/                     #to be backup dir

#define variables
DataBakName=Data_$(date +%Y%m%d).tar.gz
WebBakName=Web_$(date +%Y%m%d).tar.gz
OldData=Data_$(date -d -3day +%Y%m%d).tar.gz
OldWeb=Web_$(date -d -3day +%Y%m%d).tar.gz
DataSubject="Backup: gregwym.info database "$(date +%Y%m%d)
WebSubject="Backup: gregwym.info web dir "$(date +%Y%m%d)

#delete data from 3days earlier
rm -rf /home/backup/$OldData /home/backup/$OldWeb
cd /home/backup

#export database to compressed gz files
for db in `/usr/local/mysql/bin/mysql -u$MYSQL_USER -p$MYSQL_PASS -B -N -e 'SHOW DATABASES' | xargs`; do
    (/usr/local/mysql/bin/mysqldump --single-transaction -u$MYSQL_USER -p$MYSQL_PASS ${db} | gzip -9 - > ${db}.sql.gz)
done

#compress database files
tar zcf /home/backup/$DataBakName /home/backup/*.sql.gz
rm -rf /home/backup/*.sql.gz
#mail database
echo $DataSubject | mutt -a /home/backup/$DataBakName -s "${DataSubject}" $MAIL_TO

#compress backup dir
tar zcf /home/backup/$WebBakName $WEB_DATA
#mail dir
echo $WebSubject | mutt -a /home/backup/$WebBakName -s "${WebSubject}" $MAIL_TO

echo "bye"
#END

如果不希望备份网页文件, 或者希望数据库和网页分别备份的, 可以自行拆分脚本为两个文件.

如果只是想降低备份网页文件的频率, 可以将#mail dir部分替换为:

if [ $(date +%A) = "Sunday" ]; then 
    echo $WebSubject | mutt -a /home/backup/$WebBakName -s "${WebSubject}" $MAIL_TO 
fi