数据库第二章ppt

8
2.1.1 Basic Structure (38)
• Formally, given sets D1, D2, …. Dn a relation r is a subset of D1 x D2 x … x Dn (Cartesian product) • Thus, a relation is a set of n-tuples (a1, a2, …, an) where each ai Di. • 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 x customer_street x 9 customer_city
15
2.1.3 Keys （码）(42)
• A superkey (超码）is a set of one or more attributes that allow us to identify a unique tuple in the relation.
– Example: {customer_name, customer_id},
Attribute Types
• Each attribute of a relation has a name • The set of allowed values for each attribute is called the domain of the attribute • Attribute values are (normally) required to be atomic; that is, indivisible. • Several attributes have the same domain. • The special value null is a member of every domain • The null value causes complications in the definition of many operations – We shall ignore the effect of null values in our main presentation and consider their effect later
20
Select Operation (选择运算) (P46)
Notation: p(r) p is called the selection predicate (选择谓词/条件). r is a relation name Defined as: 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: <attribute> op <attribute> or <constant> where op is one of: =, , >, . <. (比较运算符) • Example of selection: (Figure 2.6 & 2.9 ) • • • •
Example: customer (Customer_schema)
11
Relation Instance (40)
• 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.
12
Relations are Unordered
Order of tuples is irrelevant (tuples may be stored in
an arbitrary order) (figure 2-1, 2-2)
Example: account relation with unordered tuples.
• Six basic operators
– select: （选择） – project: （投影） – union: （并） – set difference: – （集合差） – Cartesian product: x （笛卡尔积） – rename: （更名）
• The operators take one or two relations as inputs and produce a new relation as a result.
account : stores information about accounts. depositor : stores information about which customer owns which account. customer : stores information about customers. Figure 2.1,2.3,2.4,2.5,2.6,2.7
{customer_name} and {customer_id} are superkeys of customer relation (P12-F1.2), if no two customers can possibly have the same name.
16

Keys (Cont.)
• If K is minimal superkey, K is named a candidate key (候选码).
branch_name=―Perryridge‖(loan)
21
Select Operation – Example
Relation r
A B C
10
Relation Schema (40)
• A1, A2, …, An are attribute names R = (A1, A2, …, An ) is a relation schema
Example:
Account_schema = (account_number, branch _street, balance) r(R) is a relation on the relation schema R
13
Database_ brief design process
• A database consists of multiple relations.
• Information about an enterprise is broken up into parts, with each relation storing one part of the information.
– E.g. customer_name and account_number attributes of depositor are foreign keys to customer and account respectively.
• Schema diagram: along with primary key and foreign key dependencies in a database schema.
18
Query Language（查询语言） (45)
• Language in which user requests information from the database. • Categories(种类) of languages
– Procedural – Non-procedural, or declarative
select: project: union: set difference: – Cartesian product: x rename:
2
Relation
3
Attribute
4
Domain
5
Tuple
6
“Relational Models” and “Tables”
7
Example of a Relation: account (P38)
attributes (or columns) customer_name customer_street customer_city
Jones Smith Curry Lindsay
Main North North Park customer
Harrison Rye Rye Pittsfield
tuples (or rows)
14
Database (Cont.)
• Storing all information as a single relation, such as
bank (account_number, balance, customer_name, ..)
results in – Repetition(重复) of information (e.g., two customers own an account) – the need for null values (e.g., represent a customer without an account) • Chapter 7 deals with how to design relational schemas.