Relational Model It is the most popular implementation model CS 2550 / Spring 2006 Simplest, most uniform data structures Most formal (algebra to describe operations) Principles of Database Systems Introduced in 1970 (by E. F. Codd) Everything from real world is represented by relations 02 – Relational Model (i.e. tables) Alexandros Labrinidis Each table has multiple rows and columns University of Pittsburgh Row in a table “binds” values together (row = tuple) 2 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Relations Relations are sets account-number branch-name balance account-number branch-name balance Attributes (=columns) A-101 Downtown 500 A-222 Redwood 700 A-102 Perryridge 400 A-102 Perryridge 400 account-number branch-name balance A-201 Brighton 900 A-305 Round Hill 350 Domain: set of permitted values A-101 Downtown 500 A-215 Miami 700 A-215 Miami 700 A-102 Perryridge 400 A-217 Brighton 750 A-201 Brighton 900 A-201 Brighton 900 tuple t A-222 Redwood 700 A-101 Downtown 500 A-215 Miami 700 t [account-number] = A-215 A-305 Round Hill 350 A-217 Brighton 750 A-217 Brighton 750 A-222 Redwood 700 Tuple order is not important A-305 Round Hill 350 The two relations are exactly the same The account relation 3 4 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 1
Relation Schema The Mathematical Concept of Relation Let D 1 , D 2 ,…, D n be domains (not necessarily distinct) A relation schema specifies: Name of relation the Cartesian product of these n sets Names of attributes of the relation D 1 x D 2 x … x D n The domain for each attribute is the set of all possible ordered n-tuples (v 1 , v 2 ,…, v n ) such that v 1 ∈ D 1 , v 2 ∈ D 2 , …, v n ∈ D n Database schema = set of relation schemas, constraints (i.e. the logical design) Example : let D 1 = {Nick, Susan} and D 2 = {BS, MS, PhD} D 1 x D 2 = {(Nick, BS), (Nick, MS), (Nick, PhD), Database instance = snapshot of the data in database (Susan,BS), (Susan, MS), (Susan, PhD)} Α relation is any subset of the Cartesian product R 1 = {(Nick,BS),(Nick, MS), (Susan, BS), (Susan, PhD)} R 2 = {} 5 6 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Relation Schema Examples Keys Same definitions from Entity-Relationship model Account-schema = (account-number, branch-name, balance) Superkey Branch-schema = (branch-name, branch-city, assets) Set of one or more attributes that, taken collectively, uniquely identify a tuple within the relation Customer-schema=(customer-name, customer-street, customer-city) E.g., {customer-name}, {customer-name, customer-city} For simplicity assume customer-name unique Candidate key Depositor-schema = (customer-name, account-number) Superkey for which no proper subset is superkey (i.e. minimal) E.g., {customer-name} Loan-schema = (loan-number, branch-name, amount) Primary key Borrower-schema = (customer-name, loan-number) Candidate key chosen by database designer as principal means of identifying tuples within relation 7 8 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 2
Foreign Keys Schema Diagram A relation r 1 may include among its attributes the depositor branch account primary key of another relation, r 2 customer-name branch-name account-number account-number branch-city branch-name This attribute is called foreign key from r 1 referencing r 2 assets balance r 1 is called the referencing relation customer r 2 is called the referenced relation borrower loan customer-name customer-name Example loan-number loan-number customer-street customer-city loan-schema includes “branch-name” which is a primary key for branch-name branch-schema amount therefore: branch-name is foreign key 9 10 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Roadmap Relational Algebra Relational Model Procedural Query Language Relational Schema Fundamental Operators Keys Schema Diagrams Unary Relational Algrebra Select Fundamental operators Project Rename Binary Union Set Difference Cartesian-Product 11 12 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 3
Select Operator Project Operator select operator selects tuples that satisfy given predicate project operator returns relation with attributes left out σ predicate (relation) Π attribute-list (relation) attribute-list relation � selection predicate: Example: comparisons: =, !=, <, <=, >, => � combinations: and loan-number loan-number branch-name branch-name amount amount customer-name account-number Π customer-name (depositor) � or customer-name L-11 L-11 Round Hill Round Hill 900 900 Hayes A-102 ¬ not Hayes L-14 L-14 Downtown Downtown 1500 1500 Johnson A-101 Example: Johnson loan-number L-15 L-15 branch-name Perryridge Perryridge amount 1500 1500 Johnson A-201 Jones L-16 L-16 L-14 Downtown Perryridge Perryridge 1500 1300 1300 Jones A-217 σ amount>1200 (loan) L-15 Perryridge 1500 Lindsay L-17 L-17 Downtown Downtown 1000 1000 Lindsay A-222 L-16 Perryridge 1300 Smith L-23 L-23 Redwood Redwood 2000 2000 Smith A-215 L-23 Redwood 2000 Turner L-93 L-93 Mianus Mianus 500 500 Turner A-305 13 14 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Composition of Relational Operators Union Operator Result of relational operator is a relation! Set operation: r U s Produces union of two sets Can arbitrary combine operators Relations are sets eliminate duplicate values For union operation to be valid between r and s: Example: loan-number branch-name branch-name amount 1. both relations must be of same arity Π branch-name ( σ amount>1200 (loan)) L-14 Downtown Downtown 1500 2. domains of corresponding attributes must match L-15 Perryridge Perryridge 1500 L-16 Redwood Perryridge 1300 Commutative operation L-23 Redwood 2000 r U s = s U r 15 16 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 4
Set Difference Operator Cartesian-Product Operator Cartesian product operator (x) allows us to combine Set operation: r – s information from any two relations Allows us to find tuples that are in relation r, but not in s Combine attribute-lists: Relation r, schema R =(A, B, C) Relation s, schema S =(C, D, E) r x s, schema =(r.A, r.B, r.C, s.C, s.D, s.E) =(A, B, r.C, s.C, D, E) Relations must have same arity and matching attribute Cardinality of relation r = number of tuples in r domains, as with the Union operator notation: cardinality(r) = |r| Cardinality of Cartesian product: Non-commutative operation |r x s| = |r| * |s| r – s != s – r 17 18 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Rename Operator Relational Algebra (Formal Definition) rename operator gives name to results A relational algebra expression is: A relation in the database ρ new-name (expression) A constant relation, e.g., {(A-101, Pgh, $20), (A-203, Oak, $5)} If we also want to rename attributes to A 1 , A 2 , …, A n E1 U E2 E1 – E2 ρ (A1, A2, …, An) (expression) E1 x E2 σ predicate (E1) Π attr-list (E1) ρ new-name (E1) where E1 and E2 are also relational-algebra expressions 19 20 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 Alexandros Labrinidis, Univ. of Pittsburgh CS 2550 / Spring 2006 5
Recommend
More recommend
