Créer une présentation
Télécharger la présentation

Télécharger la présentation
## Introduction to Database

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Introduction to Database**CHAPTER 2 RELATIONAL MODEL 2.1 Structure of Relational Databases 2.2 Fundamental Relational-Algebra Operations 2.3 Additional Relational-Algebra Operations 2.4 Extended Relational-Algebra Operations 2.5 Null Values 2.6 Modification of the Database**PART 1: Relational Databases**• Relational Database: a shared repository of data that perceived by the users as a collection of tables. • To make database available to users: • Requests for data by • SQL (Chapter 3, 4) • QBE (Chapter 5) • Datalog (Chapter 5) • Data Integrity: protect data from damage by unintentional (Chapter 8) • Data Security: protect data from damage by intentional (Chapter 8) • Database Design (Chapter 7) • Design of database schema, tables • Normalization: Normal forms • Tradeoff: Possibility of inconsistency vs. efficiency 容器**account**2.1 Structure of Relational Databases • Relational Database: a collection of tables • Table has a unique name • A row (tuple) in a table: a relationship of a set of values • Table: mathematical concept of relation • Relational Model: proposed by Codd, 1970, ref. p.1108: Bibliography [Codd 1970]E. F. Codd, "A Relational Model for Large Shared Data banks," CACM Vol. 13, No.6,(1970), pp. 377-387**2.1.1 Basic Structure**• Relation: • Formally, given sets D1, D2, …. Dn • D1 x D2 x … x Dn = {(a1, a2, …, an) | where each ai Di} • 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 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 customer-city**account**Example: The account Relation, Fig. 2.1 • D1 = { } • D2 = { } • D3 = { } • D1 x D2 x D3= • account =**account**Attribute Types • Each attribute of a relation has a name • The set of allowedvalues for each attribute is called the domain of the attribute • Attribute values are (normally) required to be atomic, that is, indivisible • E.g. multivalued attribute values are not atomic, (A-201, A-217) • E.g. composite attribute values are not atomic, BirthDate: (5, 17, 1950) • 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**attributes**(or columns) customer-name customer-street customer-city Jones Smith Curry Lindsay Main North North Park Harrison Rye Rye Pittsfield tuples (or rows) customer 2.1.2 Database Schema • Attributes:SupposeA1, A2, …, Anareattributes • Relation schema:R = (A1, A2, …, An) is a relation schema e.g. Customer-schema = (customer-name, customer-street, customer-city) • Relation:r(R) is a relation on the relation schema R e.g. customer(Customer-schema) customer**attributes**(or columns) customer-name customer-street customer-city Jones Smith Curry Lindsay Main North North Park Harrison Rye Rye Pittsfield tuples (or rows) Relation Instance • Relation Instance: The current values (relation instance) of a relation are specified by a table • Tuple: An element t of r is a tuple, represented by a row in a table customer**account**Relations are Unordered Order of tuples is irrelevant (tuples may be stored in an arbitrary order) e.g. The account relation with unordered tuples (unordered tuples) (ordered by account-number)**bank**… Database • Database: A database consists of multiple relations (ref. p. 1-??) • Information about an enterprise is broken up into parts, with each relation storing one part of the information E.g.: account: stores information about accountsdepositor: stores information about which customer owns which account customer: stores information about customers • 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) • Normalization theory (Chapter 7) deals with how to design relational schemas**Example: Banking Database**• Banking Database: consists 6 relations: • branch (branch-name, branch-city, assets) • customer (customer-name, customer-street, customer-only) • account (account-number, branch-name, balance) • loan (loan-number, branch-name, amount) • depositor (customer-name, account-number) • borrower (customer-name, loan-number)**6. loan**3. depositor 5. account 存款帳 4. borrower 存款戶 貸款帳 貸款戶 Example: Banking Database 1. branch 2. customer 客戶(存款戶,貸款戶) 分公司**2.1.3 Keys**• Let K R = (A1, A2, …, An), set of attributes of relation r(R) • Superkey: K is a superkeyof R if values for K are sufficient to identify a unique tuple of each possible relation r(R) • Example: {customer-name, customer-street} and {customer-name} are both superkeys of Customer, if no two customers can possibly have the same name. • Candidate key:K is a candidate key if K is minimal e.g: {customer-name} is a candidate key for Customer, since it is a superkey and no subset of it is a superkey. • Primary Key • Foreign key customer**Schema Diagram, Fig. 2.8**Primary Key and foreign key can be depicted by schema diagram • Schema Diagram for the Banking Enterprise, Fig. 2.8**2.1.4 Query Languages**• Query Language: user requests information from the database. • Categories of languages • procedural • non-procedural • SQL (ch. 3, ch. 4) • QBE (Section 5.3) • “Pure” languages: • Relational Algebra (Section 2.2, 2.3, 2.4) • Tuple Relational Calculus (Section 5.1) • Domain Relational Calculus (Section 5.2) • Pure languages form underlying basis of query languages that people use (e.g. SQL).**Introduction to Database**CHAPTER 3 RELATIONAL MODEL 2.1 Structure of Relational Databases 2.2 Fundamental Relational-Algebra Operations 2.3 Additional Relational-Algebra Operations 2.4 Extended Relational-Algebra Operations 2.5 Null Values 2.6 Modification of the Database**Relational-Algebra Operations**• Procedural language • The operators take one or more relations as inputs and give a new relation as a result. • Fundamental Relational-Algebra Operations • Select • Project • Union • Set difference • Cartesian product • Rename • Additional Relational-Algebra Operations • Intersection • Natural Join • Division • Assignment**Select ()**Project () Product (x) a b c x y a a b b c c x y x y x y Difference () Union ( 2.2 Fundamental Relational-Algebra Operations • Select • Project • Union • Set difference • Cartesian product • Rename**Relationr**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 2.2.1 Select Operation: Example**Select Operation**• Notation: p(r) • p is called the selection predicate • Defined as: p(r) = {t | t rand 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:branch-name=“Perryridge”(account)**Example Queries 1: Select**• Find all loans of over $1200 amount > 1200 (loan) 5. loan**A**B C 10 20 30 40 1 1 1 2 2.2.2 Project Operation: Example • Relationr: A C A C A,C(r) 1 1 1 2 1 1 2 =**Project Operation**• Notation: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 accountaccount-number, balance (account)**Example Queries 2: Project**• List all all loan numbers and the amount of the loans loan-number, amount (loan) Fig. 2.10**Example Queries 3: Project**• Can use =, =, < > <=, >=, and, or, not … • Find all loans of over $1200 made by the Perryridge branch 梨崗山 amount > 1200 ^ branch-name=“Perryridge” (loan) loan Fig. 2.9**A**B C D E 1 1 1 1 2 2 2 2 10 10 20 10 10 10 20 10 a a b b a a b b A B r 1 2 C D E a a b b 10 10 20 10 A B C D E 10 20 20 a a b 1 2 2 2.2.3 Composition of Operations • We can build expressions by using multiple operations • Example: expression: A=C(r x s) op1:r x s op2: A=C(r x s) s**Example Queries 4: Select/Project**Find the loan number for each loan of an amount greater than $1200 loan-number (amount> 1200 (loan)) 5. loan**2. customer**Example Queries 5: Composition • Find those customers who live in Harrison customer-name (customer-city=“Harrison” (customer))**2.2.4 Union Operation: Example**• Relations r, s: A B A B 1 2 1 2 3 s r r s: A B 1 2 1 3**Union Operation**• Notation: r s • Defined as: r s = {t | t r or t s} • For r s to be valid. 1. r,s must have the same arity (same number of attributes, same heading) 2. The attribute domains must be compatible (e.g., 2nd column of r deals with the same type of values as does the 2nd column of s) • E.g. to find all customers with either an account or a loancustomer-name (depositor) customer-name (borrower)**Example Queries 6: Union/Intersection**• Find the names of all customers who have a 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. Fig. 2.11 customer-name (borrower) customer-name (depositor)**2.2.5 Set-Difference Operation: Example**• Relations r, s: A B A B 1 2 1 2 3 s r r – s: A B 1 1**Set Difference Operation**• Notation r – s • Defined as: r – s = {t | t rand t s} • Set differences must be taken between compatible relations. • r and s must have the same arity • attribute domains of r and s must be compatible**4. depositor**6. borrower Example Queries 7: Set Difference • Find the names of all customers who have an account, but not a loan customer-name (depositor) - customer-name (borrower) Fig. 2.12**Product (x)**a b c x y a a b b c c x y x y x y A B C D E rxs: 1 1 1 1 2 2 2 2 10 10 20 10 10 10 20 10 a a b b a a b b 2.2.6 Cartesian-Product Operation: Example A B C D E s r Relations r, s: 1 2 10 10 20 10 a a b b**Product (x)**a b c x y a a b b c c x y x y x y Cartesian-Product Operation • Notation r x s • Defined as: r x s = {t q | t r and q s} • Assume that attributes of r(R) and s(S) are disjoint. (That is, R S = ). • If attributes of r(R) and s(S) are not disjoint, then renaming must be used.**5. loan**6. borrower Example Queries 8: Cartesian-Product borrower x loan**Queries 8:borrower loan (Fig. 2.13)**8 x 7 = 56 tuples**Queries 8.1:Find the names of all customers who have a loan**at the Perryridge branch. branch-name = “Perryridge”(borrower loan) 8 x 2 = 16 tuples (Fig. 2.14)** branch-name = “Perryridge”(borrower loan),(Fig.**2.14) 8 x 2 = 16 tuples**Queries 8.2:(borrower.loan-number = loan.loan-number**(borrower x loan)) 8 x 1 = 8 tuples**Queries 8.3:(branch-name=“Perryridge”**(borrower.loan-number = loan.loan-number (borrower x loan))) 2 tuples**5. loan**6. borrower Example Queries 8.4: Cartesian-Product • Query: Find the names of all customers who have a loan at the Perryridge branch. customer-name (branch-name=“Perryridge” (borrower.loan-number = loan.loan-number (borrower x loan))) (Fig. 2.15)**Example Queries 9**Query: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.loan-number(borrower x loan))) – customer-name (depositor)**Example Queries 10: Comparison**• Query:“Find the names of all customers who have a loan at the Perryridge branch. • Query 1customer-name (branch-name = “Perryridge” ( borrower.loan-number = loan.loan-number (borrower x loan))) 8 x 7 = 56 tuples • Query 2 customer-name (loan.loan-number = borrower.loan-number ( (branch-name = “Perryridge” (loan)) x borrower)) 2 x 8 = 16 tuples Which one is better?**2.2.7 Rename Operation**• Rename Operation: Allows us to name, and therefore to refer to, the results of relational-algebra expressions. • E.g.1: x (E) returns the expression E under the name X • E.g. 2. If a relational-algebra expression E has arity n, then x (A1, A2, …, An) (E) returns the result of expression E under the name X, and with the attributes renamed to A1, A2, …., An. • E.g. 3. x (r) ? • SQL: Rename r As x**3. Account = d**3. account Example Queries 11: Rename, p.53 Query: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)))**3. Account = d**3. account Example Queries 11: Rename (cont.) account.balance(account.balance < d.balance(account x rd (account))) (Fig. 2.17) (Fig. 2.16)**2. customer**Fig. 2.18 Query: “Find the names of all customers who live on the same street and in the same city as Smith” Example Queries 12: Rename Algebra: p. 54**2.2.8 Formal Definition**• A basic expression 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 list consisting of some of the attributes in E1 • x(E1), x is the new name for the result of E1