数据库ch2-relationalmodel

1、chapter2 relational model2contents structure of relational databases fundamental relational-algebra-operations additional relational-algebra-operations extended relational-algebra-operations null values modification of the databasechapter2 relational model3why study widely used simple data structure

2、chapter2 relational model4structure of relational databaserelations represents an entity set or a relationship seta row of a relation represents a entity or a relationshipchapter2 relational model5basic structure each row of a relation can be referred to as tuple each column of a relation has a name

3、 named columns of a relation are referred to as attributes the set of allowed values for each attribute is called the domain of the attributechapter2 relational model6basic structure domain- legal type and range of values of an attributedenoted by dom(ai) attribute: age domain: 0-100 attribute: empn

4、ame domain: 50 alphabetic chars attribute: salary domain: non-negative integerchapter2 relational model7 has 3 attributes customer_name, customer_street, customer_cityd1: the set of all possible customer namesd2: the set of all possible customer streets d3: the set of all possible customer city tupl

5、echapter2 relational model8cartesian productrelation formally, given sets d1, d2, . dn a relation r is a subset of d1 x d2 x x dnthus, a relation is a set of n-tuples (a1, a2, , an) where each ai dichapter2 relational model9例如：例如：d1=man=王兵，李平，张英王兵，李平，张英， d2=woman=丁梅，吴芳 d3=child=王一，李一，李二q笛卡尔积可以表示为一个二

6、维表，笛卡尔积可以表示为一个二维表，表中的每一行对应一个元组，每一表中的每一行对应一个元组，每一列对应一个域列对应一个域chapter2 relational model10man woman child王兵王兵 丁梅丁梅 王一王一王兵王兵 丁梅丁梅 李一李一王兵王兵 丁梅丁梅 李二李二王兵王兵 吴芳吴芳 王一王一王兵王兵 吴芳吴芳 李一李一王兵王兵 吴芳吴芳 李二李二李平李平 丁梅丁梅 王一王一李平李平 丁梅丁梅 李一李一李平李平 丁梅丁梅 李二李二李平李平 吴芳吴芳 王一王一李平李平 吴芳吴芳 李一李一李平李平 吴芳吴芳 李二李二man woman child张英张英 丁梅丁梅 王一王一张

7、英张英 丁梅丁梅 李一李一张英张英 丁梅丁梅 李二李二张英张英 吴芳吴芳 王一王一张英张英 吴芳吴芳 李一李一张英张英 吴芳吴芳 李二李二续左表chapter2 relational model11 例如：假设王兵的妻子是丁梅，他们的孩子是王一，李平的妻子是吴芳，他们的孩子是李一和李二，则取笛卡尔积的一个子集构造一个关系familyman woman child王兵 丁梅 王一李平 吴芳 李一李平 吴芳 李二familychapter2 relational model12relation schema a1, a2, , an are attribute names r = (a1, a2,

8、 , an ) is a relation schema r(r) denotes a relation r on the relation schema r) the current values (relation instance) of a relation are specified by a tablechapter2 relational model13 attribute values are (normally) required to be atomic; that is, indivisible the value of a tuple on an attribute c

9、an be an account number, but cannot be a set of account numbers the special value null is a member of every domaincont.basic structurechapter2 relational model14basic structure a special null value is used to represent values that are: not applicable (phone number for a client that has no phone) mis

10、sing values (there is a phone number but we do not know it yet) not known (we do not know whether there is a phone number or not)chapter2 relational model15relations are unordered order of tuples is irrelevant (tuples may be stored in an arbitrary order)account relation with unordered tupleschapter2

11、 relational model16relational database a database consists of multiple relations information about an enterprise is broken up into parts, with each relation storing one part of the informationaccount : stores information about accountsdepositor : stores information about which customer owns which ac

12、countcustomer : stores information about customerschapter2 relational model17cont.relational database storing all information as a single relation repetition of information the need for null values normalization theory (chapter 7) deals with how to design relational schemasif two customers own an ac

13、count (what gets repeated?)to represent a customer without an accountbank(account_number, balance, customer_name, .)result inchapter2 relational model18keys k is a set of attributes, let k r k is a superkey of r if values for k are sufficient to identify a unique tuple of each possible relation r(r)

14、 chapter2 relational model19if no two customers can possibly have the same name，customer_name, customer_street and customer_name are both superkeys of customerchapter2 relational model20cont.keys superkey k is a candidate key if k is minimal minimal means no subset of it is superkeycustomer_name is

15、a candidate key for customer, since it is a superkey and no subset of it is a superkey.chapter2 relational model21 primary key: a candidate key chosen as the principal means of identifying tuples within a relation should choose an attribute whose value never, or very rarely, changes.cont.keysemail a

16、ddress is unique, but may change,is not suitable for primary keychapter2 relational model22foreign keys a relation r1 may have an attribute that corresponds to the primary key of another relation r2. the attribute is called a foreign key r1 is referencing relation r2 is referenced relationcustomer_n

17、ame and account_number attributes of depositor are foreign keys to customer and account respectively. depositor is referencing relation, customer and account are referenced relationchapter2 relational model23primary keyforeign key, corresponds to the primary key of relation customerforeign key, corr

18、esponds to the primary key of relation accountreferencing relation referenced relation referenced relation chapter2 relational model24referencing constraint only values occurring in the primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relatio

19、nis subset ofchapter2 relational model25schema diagramchapter2 relational model26relation integrity(完整性） entity integrity the values of primary key are not null referential integrity user-defined integritychapter2 relational model27query languages language in which user requests information from the

20、 database. categories of languages procedural non-procedural, or declarativechapter2 relational model28relational algebra consists of a set of operators take one or two relations as inputs and produce a new relation as a result six basic operators select: project: union: set difference: cartesian pr

21、oduct: x rename: chapter2 relational model29select operation select tuples that satisfy a given predicate（谓词）abcbcd123710n relation ra=b d 5 (r)chapter2 relational model30cont.select operation p(r) = t | t r and p(t)where p is a formula in propositional calculus （命题演算）consisting of term

22、s connected by : (and), (or), (not)，each term is one of: op or where op is one of: =, , , . 1200 (loan)loan_number (amount 1200 (loan)customer_name (borrower) customer_name (depositor)chapter2 relational model47 find the names of all customers who have a loan at the perryridge branchlquery 1 custome

23、r_name (branch_name = “perryridge” ( borrower.loan_number = loan.loan_number (borrower x loan)lquery 2 customer_name(loan.loan_number = borrower.loan_number (branch_name = “perryridge” (loan) x borrower)cont.chapter2 relational model48cont. find the names of all customers who have a loan at the perr

24、yridge branch but do not have an account at any branch of the bankcustomer_name (branch_name = “perryridge” (borrower.loan_number = loan.loan_number(borrower x loan) customer_name(depositor)chapter2 relational model49 find the largest account balance strategy: find those balances that are not the la

25、rgest rename account relation as d so that we can compare each account balance with all others use set difference to find those account balances that were not found in the earlier step. the query is:cont.balance(account) - account.balance (account.balance d.balance (account x d (account)chapter2 rel

26、ational model50additional operationswe define additional operations that do not add any power to the relational algebra, but that simplify common queriesset intersection natural join division assignment chapter2 relational model51set-intersection operation notation: r s defined as: assume: r, s have

27、 the same arity attributes of r and s are compatible note: r s = r (r s)r s = t | t r and t s chapter2 relational model52a b121a b23a b2nsnrnr schapter2 relational model53join generating all possible combinations of tuples is not usually meaningful. join is a cartesian product followed by a selectio

28、n -joinchapter2 relational model54an introduction to database systemjoinrelation r, s ：chapter2 relational model55an introduction to database systemjoin一般连接 r s的结果如下： cechapter2 relational model56an introduction to database systemequijoin equijoin（等值连接） r s 的结果如下：r.b=s.b chapter2 relational model57a

29、n introduction to database systemnature join 自然连接 r s的结果如下： bchapter2 relational model58natural-join operation notation: r s let r and s be relations on schemas r and s respectively. then, r s is a relation on schema r s obtained as follows: consider each pair of tuples tr from r and ts from s. if t

30、r and ts have the same value on each of the attributes in r s, add a tuple t to the resultchapter2 relational model59abc d12412aababbde13123aaabba b c d e11112aaaabnr snsnrnatual-join connects two relations by: equating attributes of the same name and projecting out one copy of each pair of equated

31、attributes.chapter2 relational model60natural-join operationaccount customer() account.account_number=customer.accoont_number() customer_name, account.account_number, balancefind the names of all customers who have an account at the bank, along with the account number and the balance chapter2 relati

32、onal model61natural-join operation(account customer) customer_name, account.account_number, balancefind the names of all customers who have an account at the bank, along with the account number and the balance chapter2 relational model62nfind the name of all customers who have a loan at the bank and

33、 the loan amountcustomer_name, loan_number, amount (borrower loan) branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city)account (account_number, branch_name, balance)loan (loan_number, branch_name, amount)depositor (customer_name, account_number)borrower

34、(customer_name, loan_number)chapter2 relational model63nfind the names of all customers who have a loan and an account at bankcustomer_name (borrower) customer_name (depositor)customer_name (borrower depositor)chapter2 relational model64divisionlet a have two fields x and ylet b have one field ya/b

35、contains all x tuples, such that for every y tuple in b there is a xy tuple in axys1p1s1p2s1p3s1p4s2p1s2p2s3p2s4p2s4p4ap4p2ybs4s1xa/b/=chapter2 relational model65division operation notation: let r and s be relations on schemas r and s respectively, where r = (a1, , am , b1, , bn ) s = (b1, , bn) the

36、 result of r s is a relation on schemar s = (a1, , am)r s = t | t r-s (r) u s ( tu r ) where tu means the concatenation(连结) of tuples t and u to produce a single tupler s chapter2 relational model66ab12311134612ab12nr snsnrchapter2 relational model67abc deaaaaaaaaaabababb11113111deab11abcaanr snsnrc

37、hapter2 relational model68xys1p1s1p2s1p3s1p4s2p1s2p2s3p2s4p2s4p4p4p2yabdivision using basic operationschapter2 relational model69division using basic operations *cont.let a and b be relations, and let b a compute all possible combinations of the first column of a and b. then remove those rows that e

38、xist in a keep only the first column of the result. these are the disqualified values x( (x(a)b) a) a/b is the first column of a except the disqualified values a/b = x(a) x(x(a)b) a)chapter2 relational model70division (cont.)p4s4p2s4p2s3p2s2p1s2p4s1p3s1p2s1p1s1yx x(a) = x() =xs1s2s3s4 x(a) b =xs1s2s

39、3s4 p4p2y=xys1p2s1p4s2p2s2p4s3p2s3p4s4p2s4p4chapter2 relational model71division (cont.)( x(a) b) - a = xys1p1s1p2s1p3s1p4s2p1s2p2s3p2s4p2s4p4 =xys1p2s1p4s2p2s2p4s3p2s3p4s4p2s4p4xys2p4s3p4 x(a) x( x(a) b) a) =xs1s2s3s4 =xs2s3xs1s4example divisionfind the employment numbers of the pilots who can fly a

40、ll md planescan_fly / model_no(maker=mdplane)emp_no1003emp_nomodel_no1001b7271001b7471001dc101002a3201002a3401002b7571002dc91003a3101003dc9makermodel_noairbusa310airbusa320airbusa330airbusa340boeingb727boeingb747boeingb757mddc10mddc91003dc10cont.nfind all customers who have an account from at least

41、the “downtown” and the “uptown” branches.customer_name (branch_name = “downtown” (depositor account ) customer_name (branch_name = “uptown” (depositor account)lquery 1lquery 2 customer_name, branch_name (depositor account) temp(branch_name) (“downtown” ), (“uptown” )cont.nfind all customers who have

42、 an account at all branches located in brooklyn citycustomer_name, branch_name (depositor account) branch_name (branch_city = “brooklyn” (branch)chapter2 relational model75assignment operation the assignment operation () provides a convenient way to express complex queries. write query as a sequenti

43、al program consisting of a series of assignments followed by an expression whose value is displayed as a result of the query. assignment must always be made to a temporary relation variable.chapter2 relational model76assignment operation write r s as temp1 r-s (r ) temp2 r-s (temp1 x s ) r-s,s (r )r

44、esult = temp1 temp2 the result to the right of the is assigned to the relation variable on the left of the . may use variable in subsequent expressionscont.chapter2 relational model77extended operations generalized projection (广义投影) aggregate functions outer joinchapter2 relational model78generalize

45、d projection extends the projection operation by allowing arithmetic functions to be used in the projection list e is any relational-algebra expression each of f1, f2, , fn are arithmetic expressions involving constants and attributes in the schema of e.)( ,.,21enfffchapter2 relational model79genera

46、lized projectioncustomer_name, limit credit_balance (credit_info)given relation credit_info(customer_name, limit, credit_balance), find how much more each person can spend:chapter2 relational model80aggregate（聚集） functions aggregate function takes a collection of values and returns a single value as

47、 a result avg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of valueschapter2 relational model81aggregate operations aggregate operation in relational algebra)()(,),(),(2211ennafafafabc77310sum(c )27ng sum(c) (r)nrchapter2 relational model82)()(,),(),(,221121ennna

48、fafafgggf1(a1), f2(a2), , fn(an)nrelation egroupingg1, g2, , gnaggregate operationschapter2 relational model83branch_nameaccount_numberbalanceperryridgeperryridgebrightonbrightonredwooda-102a-201a-217a-215a-222400900750750700branch_namesum(balance)perryridgebrightonredwooranch_name g su

49、m(balance) (account)chapter2 relational model84aggregate operations result of aggregation does not have a name can use rename operation to give it a name for convenience, we permit renaming as part of aggregate operationcont.branch_name g sum(balance) (account)branch_name g sum(balance) as sum-balan

50、ce (account)chapter2 relational model85nrelation borrowercustomer_nameloan_numberjonessmithhayesl-170l-230l-155loan_numberamountbranch_name300040001700l-170l-230l-260downtownredwoodperryridgenrelation loanchapter2 relational model86outer join an extension of the join operation that avoids loss of in

51、formation computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join uses null values: null signifies that the value is unknown or does not exist all comparisons involving null are (roughly speaking) false by definition.chapter

52、2 relational model87nrelation borrowercustomer_nameloan_numberjonessmithhayesl-170l-230l-155loan_numberamountbranch_name300040001700l-170l-230l-260downtownredwoodperryridgenrelation loanchapter2 relational model88loan_numberamountcustomer_namebranch_namel-170l-23030004000jonessmithdowntownredwoodloa

53、n_numberamountcustomer_namebranch_namejonessmithnulll-170l-230l-260300040001700downtownredwoodperryridgen left outer join loan borrowern join loan borrowerchapter2 relational model89n right outer join loan borrowerloan_numberamountcustomer_namebranch_namel-170l-230lulljonessmithhayesdow

54、ntownredwoodnulln full outer join loan borrowerloan_numberamountcustomer_namebranch_namel-170l-230l-260l-155300040001700nulljonessmithnullhayesdowntownredwoodperryridgenullchapter2 relational model90null values it is possible for tuples to have a null value, denoted by null, for some of their attrib

55、utes null signifies an unknown value or that a value does not exist.chapter2 relational model91null values the result of any arithmetic expression involving null is null comparisons with null values return the special truth value: unknown aggregate functions simply ignore null values (as in sql)chap

56、ter2 relational model92 three-valued logic using the truth value unknown: or: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown and: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown not: (not unknown) = unknown in sql “p i

57、s unknown” evaluates to true if predicate p evaluates to unknowncont.null valueschapter2 relational model93cont.null values result of select predicate is treated as false if it evaluates to unknown p(r) for duplicate elimination and grouping, null is treated like any other value, and two nulls are a

58、ssumed to be the same chapter2 relational model94exercisechapter2 relational model95modification of the database the content of the database may be modified using the following operations: deletion insertion updating all these operations are expressed using the assignment operatorchapter2 relational

59、 model96deletion a deletion is expressed in relational algebra by:r r ewhere r is a relation and e is a relational algebra query the selected tuples are removed from the database. can delete only whole tuples; cannot delete values on only particular attributeschapter2 relational model97ndelete all loan records with amount in the range of 0 to 50ndelete all account records in the perryridge branchaccount account branch_name = “perryridge” (account )loan loan amount