1、Relational Model,Structure of Relational Databases Relational Algebra Fundamental Relational-Algebra-Operations Additional Relational-Algebra-Operations,Example of a Relation,Relational Database,A relational database consists of a collection of tables. Each table is assigned a unique name. A row in

2、a table represents a relationship among a set of values. A table is a collection of such relationship. Relational data model is the primary data model for commercial data-processing applications. Because of its simplicity Easy use for programmer Hard job for DBMS software.,Database is a collection o

3、f data,What is Data Model: To describe: Data, Data relalationship, Data Semantices, Data Constraints,Basic Structure,Formally, given sets D1, D2, . Dn a relation r is a subset of D1 x D2 x x Dn Thus a relation is a set of n-tuples (a1, a2, , an) where ai Di Example: if customer-name = Jones, Smith,

4、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 customer-city,Attribute Types,Each attribute of a

5、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 E.g. the value of an attribute can be an account number, but cannot be a set of account numbers Domain is said to be atom

6、ic if all its members are atomic 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,Relation Schema,A1, A2, , An are attributes

7、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),Relation Instance,The current values (relation instance) of a relation are specified by a table An element t of

8、r is a tuple, represented by a row in a table,Relations are Unordered,Order of tuples (row) is irrelevant (may be stored in an arbitrary order) Order of attributes (column) is irrelevant E.g. account relation with unordered tuples,Database,A database consists of multiple relations Database = Set of

9、relations Information about an enterprise is broken up into parts, with each relation storing one part of the information E.g.: account : stores information about accounts depositor : stores information about which customer owns which account customer : stores information about customers Storing all

10、 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) Normalization theory (Chapter 7) deals with how to design relatio

11、nal schemas,The customer Relation,The depositor Relation,Keys,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) by “possible r” we mean a relation r that could exist in the enterprise we are modeling. Example: customer-name, custome

12、r-street and customer-name are both superkeys of Customer, if no two customers can possibly have the same name. K is a candidate key if K is minimal Example: customer-name is a candidate key for Customer, since it is a superkey assuming no two customers can possibly have the same name), and no subse

13、t of it is a superkey.,Primary Key,It is possible to have more than one candidate key. E.g. customer-name and email-address are both unique, can serve as candidate key. Primary key: a candidate key chosen as the principal means of identifying tuples within a relation Should choose an attribute whose

14、 value never, or very rarely, changes. E.g. email address is unique, but may change,Primary key is chosen by human being, which one do I like?,Schema Diagram for the Banking Enterprise,Foreign Key: The attributes of a relation schema r1 is the primary key of another relation schema r2. The attribute

15、s is called a foreign key from r1, referencing r2. The attribute branch-name in Account is a foreign key referencing Branch. Only values occurring in the primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relation,Query Languages,Language in wh

16、ich user requests information from the database. Categories of languages Procedural: The user instructs the system to perform a sequences of operations on the database. non-procedural: The user describes the desired information without giving a specified procedure for obtaining that information. “Pu

17、re” languages: Relational Algebra Tuple Relational Calculus Domain Relational Calculus Pure languages form underlying basis of query languages that people use.,Relational Algebra,Procedural language Six basic operators select project union set difference Cartesian product rename The operators take t

18、wo or more relations as inputs and give a new relation as a result.,Select Operation Example,Relation r,A,B,C,D, , ,1 5 12 23,7 7 3 10,A=B D 5 (r),A,B,C,D, , ,1 23,7 10,Select Operation,Notation: p(r) p is called the selection predicate Defined as: p(r) = t | t r and p(t) Where p is a formula in pro

19、positional calculus consisting of terms connected by : (and), (or), (not) Each term is one of: op or where op is one of: =, , , . 1200 (loan) Find the loan number for each loan of an amount greater than $1200 loan-number (amount 1200 (loan),Example Queries,Find the names of all customers who have a

20、loan, an account, or both, from the bank customer-name (borrower) customer-name (depositor) Find the names of all customers who have a loan and an account at bank. customer-name (borrower) customer-name (depositor),Example Queries,Find the names of all customers who have a loan at the Perryridge bra

21、nch. customer-name (branch-name=“Perryridge” (borrower.loan-number = loan.loan-number(borrower x loan) Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank. customer-name (branch-name = “Perryridge” (borrower.loan-number = loan

22、.loan-number(borrower x loan) customer-name(depositor),Example Queries,Find the names of all customers who have a loan at the Perryridge branch. Query 1 customer-name(branch-name = “Perryridge” (borrower.loan-number = loan.loan-number(borrower x loan) Query 2 customer-name(loan.loan-number = borrowe

23、r.loan-number( (branch-name = “Perryridge”(loan) x borrower) ),Example Queries,Find the largest account balance Rename account relation as d The query is: balance(account) - account.balance (account.balance d.balance (account x rd (account),Formal Definition of Relational Algebra,A basic expression

24、in the relational algebra consists of either one of the following: A relation in the database A constant relation Let E1 and E2 be relational-algebra expressions; the following are all relational-algebra expressions: E1 E2 E1 - E2 E1 x E2 p (E1), P is a predicate on attributes in E1 s(E1), S is a li

25、st consisting of some of the attributes in E1 x (E1), x is the new name for the result of E1,Additional Operations,We define additional operations that do not add any power to the relational algebra, but that simplify common queries. Set intersection Natural join Division Assignment,Set-Intersection

26、 Operation,Notation: r s Defined as: r s = t | t r and t s Assume: r, s have the same arity attributes of r and s are compatible Note: r s = r - (r - s),Set-Intersection Operation - Example,Relation r, s: r s,A B, ,1 2 1,A B, ,2 3,r,s,A B, 2,Natural-Join Operation,Notation: r s Let r and s be relati

27、ons on schemas R and S respectively.The result is a relation on schema R S which is obtained by considering each pair of tuples tr from r and ts from s. If tr and ts have the same value on each of the attributes in R S, a tuple t is added to the result, where t has the same value as tr on r t has th

28、e same value as ts on s Example: R = (A, B, C, D) S = (E, B, D) Result schema = (A, B, C, D, E) r s is defined as: r.A, r.B, r.C, r.D, s.E (r.B = s.B r.D = s.D (r x s),Natural Join Operation Example,Relations r, s:,A,B, ,1 2 4 1 2,C,D, ,a a b a b,B,1 3 1 2 3,D,a a a b b,E, ,r,A,B, ,1 1 1 1 2,C,D, ,a

29、 a a a b,E, ,s,Division Operation,Suited to queries that include the phrase “for all”. Let r and s be relations on schemas R and S respectively where R = (A1, , Am, B1, , Bn) S = (B1, , Bn) The result of r s is a relation on schema R S = (A1, , Am) r s = t | t R-S(r) u s ( tu r ) Where tu means the

30、concatenation of tuples t and u to produce a single tuple,r s,Division Operation Example,Relations r, s:,r s:,A,B, ,1 2,A,B, ,1 2 3 1 1 1 3 4 6 1 2,r,s,Another Division Example,A,B, ,a a a a a a a a,C,D, ,a a b a b a b b,E,1 1 1 1 3 1 1 1,Relations r, s:,r s:,D,a b,E,1 1,A,B, ,a a,C, ,r,s,Division O

31、peration (Cont.),Property Let q = r s Then q is the largest relation satisfying q x s r Definition in terms of the basic algebra operation Let r(R) and s(S) be relations, and let S R r s = R-S (r) R-S ( (R-S (r) x s) R-S,S(r) To see why R-S,S(r) simply reorders attributes of r R-S(R-S (r) x s) R-S,S(r) gives those tuples t in