北邮数据库课程讲义chapter2

minimal superkey
Customer-id is candidate key of customer account-number is candidate key of account
Although several candidate keys may exist, one
of the candidate keys is selected to be the primary key（主键、关键字）.
Example: {customer-name, customer-street} and {customer-name} are both superkeys of Customer,
Candidate Key
A candidate
DataBase System Concepts
key（候选键）of an entity set is a
p(r) = {t | t r and p(t)}
Where p is a formula in propositional calculus consisting of terms connected by : (and), (or), (not) Each term is one of : G <attribute>op <attribute> or <constant> where op is one of: =, , >, . <. •Example of selection: branch-name=“Perryridge”(account)
Relation Schema
has a name;
DataBase System Concepts
•A1, A2, …, An are attributes, each attribute of a relation
The set of allowed values for each attribute is called the domain of the attribute,D1,D2,…Dn:
Relation Instance
DataBase System Concepts
• The current values (relation instance) of a relation are specified by a table • An element t of r is a tuple, represented by a row in a table
Integrity Constraint of Primary Key:( 实体完整性约束）
Each specified column of primary key is assumed to be NOT NULL. Referential Integrity constraint： （参照完整性约束） The FK-values in any tuple of relation r1 are either NULL or must appear as the PK-values of a tuple of relation r2.
{{A1}D1, {A2}D2,… ,{An}Dn}
•R = (A1, A2, …, An ) is a relation schema
E.g. Customer-schema = (customer-name, customer-street, customer-city) r(R) is a relation on the relation schema R E.g. customer (Customer-schema) R (A1, A2, …, An ) /R(U)
attributes
customer-name customer-street customer-city
Column
Jones Smith Curry Lindsay
Main North North Park
customer
Harrison Rye Rye Pittsfield
Tuples or Row Record
Database Schema
DataBase System Concepts
•Database Schema: logical design of the database; •Database instance:
snapshot of the data in the database at a given instant in time;
Example: Employee(no,name,sex, age, deptno) dept( deptno, name , address)
Schema diagram
DataBase System Concepts
Integrity Constraint
DataBase System Concepts
The difference here are :
assign attribute name to every domain; finite set;
Relational Data Structure
DataBase System Concepts
Example: if customer-name = {Jones, Smith, Curry, Lindsay} customer-street = {Main, North, Park} customer-city = {Harrison, Rye, Pittsfield} Then r = { (Jones, Main, Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield),…} is a relation over customer-name × customer-street × customer-city
•Relation schema;
•Relation instance;
SuperKey
R(U) is a relation schema , K U;
DataBase System Concepts
K is a superkey(超键） for R:
for any r, no two distinct tuples have the same values on K. That is ,if t1 and t2 are in r and t1t2, then t1[k] t2[k]; values for K are sufficient to identify a unique tuple of each possible relation r(R) by “possible r” ;
Foreign Key
DataBase System Concepts
For R1,R2, R1 includes the Primary key X of R2, X is called a Foreign Key（外键） referencing R2;
R1 is called the referencing relation of the foreign key dependence; R2 is called the referenced relation of the foreign key ;
Project（投影）
Cartesian product（笛卡尔乘积）
Rename（更名）
Select Operation
•Notation:
DataBase System Concepts
p(r)
p is called the selection predicate
• Defined as:
Select Operation Example
• Relation r
A B C D
DataBase System Concepts




1
5 12
7
7 3


23 10
• A=B ^ D > 5


23 10
Project Operation
• Notation:
DataBase System Concepts
A1, A2, …, Ak (r)
where A1, A2 are attribute names and r is a relation name. • The result is defined as the relation of k columns obtained by erasing the columns that are not listed • Duplicate rows removed from result, since relations are sets • E.g. To eliminate the branch-name attribute of account account-number, balance (account)
Example: student( no, id, name,sex ,age) {no} and {id} are both candidate keys of student, {no} can be selected as primary key;
DataBase System Concepts
the Relational Algebra
Six basic operators
Select(选择）
DataBase System Concepts
The operators take one/two or more relations as inputs Union（并） and give a new relation as set difference（差） a result.
Extended Relational-Algebra-Operations
Null Values
Modification of the Database
Relational Data Structure
DataBase System Concepts
Mathematics define a relation to be a subset of a Cartesian product of a list of Domains:
Two Properties of Candidate Key: Uniqueness（唯一性）: No legal value of R ever contains two distinct tuples with the same value for K; Irreducibility（最小性）: No proper subset of K has the uniqueness property.
Chapter 2
Relational
Model
DataBase System Concepts
Relational
Model
DataBase System Concepts
Relational Data Structure Fundamental Relational-Algebra-Operations Additional Relational-Algebra-Operations
DataBase System Concepts
NO
7001
7331 7004
Name
zhang
Li Wang
Sex
F
M F
Age
19
19 20
Class Dept
9901
9902 0006
4
4 5
• Order of tuples is irrelevant (tuples may be stored in an arbitrary order); • Order of attributes is irrelevant; • For all relations , the domains of all attributes be atomic; • Attributes names must be different; • Several attributes can have the same domain; •Tuples are not duplicate.
D1 x D2 x … x Dn
Formally, given sets D1, D2, …. Dn a relation r is a subset of D1 × D2 × … × Dn Thus a relation is a set of n-tuples (a1, a2, …, an) where ai Di