VIEWS: 1 PAGES: 58 POSTED ON: 9/30/2011 Public Domain
The Relational Database Model -Software School of Hunan University- 2006.09 Learning Objectives • Terminology of data model, relational model. • How tables are used to represent data. • Connection between mathematical relations and relations in the relational model. • Properties of database relations. • Concepts of primary, and foreign keys. • Meaning of integrity. • Relation Algebra Data Model • Integrated collection of concepts for describing data, relationships between data, and constraints on the data in an organization. • Data Model comprises: – a structural part; – a manipulative part; – possibly a set of integrity rules. • Purpose: – To represent data in an accurate and understandable way. Categories of data models Logic level: – Record-Based Data Models • Relational Data Model • Network Data Model • Hierarchical Data Model. – Object-Based Data Models • Entity-Relationship • Semantic data model • Functional data model • Object-Oriented data model. Physical level: – Physical Data Model Relational Model History • The relational model was proposed by E. F. Codd in 1970. • One of the first relational database systems, System R, developed at IBM led to several important breakthroughs: – the first version of SQL – various commercial products such as Oracle and DB2 – extensive research on concurrency control, transaction management, and query processing and optimization • Commercial implementations (RDBMSs) appeared in the late 1970s and early 1980s. Currently, the relational model is the foundation of the majority of commercial database management systems. Relational Model Definitions • A relation is a table with columns and rows. • An attribute is a named column of a relation. • A tuple is a row of a relation. • A domain（域） is a set of allowable values for one or more attributes. • The degree （度）of a relation is the number of attributes it contains. • The cardinality（基）of a relation is the number of tuples it contains. • A relational database is a collection of normalized relations with distinct relation names. • The intension(内涵） of a relation is the structure of the relation including its domains. • The extension （外延）of a relation is the set of tuples currently in the relation. Relation Example Relation Attributes PropertyForRent PropertyNo Address Type rooms rent OwnerNo PA14 16 Holhead House 6 650 C046 Cardinality = PL94 6 Argyll St Flat 4 400 C087 PG4 6 Lawrence St. Flat 3 350 C040 Tuples PG36 2 Manor Rd Flat 3 375 C093 PG21 18 Dale Rd House 5 600 C087 PG16 5 Novar Dr. Flat 4 450 C093 75 … … Degree = 6 Domain is currency Domain is integer: 1..100 Example of Domain Alternative Terminologies Relation Practice Questions Lease LeaseNo PropertyNo ClientNo rent Payment Deposit Paid RentStart Rentfinish Duration method 10024 PA14 CR62 650 Visa 1300 Y 1-Jun-01 31-May-02 12 10075 PL94 CR76 400 Cash 800 N 1-Aug-01 31-Jan-02 6 10012 PG21 CR74 600 cheque 1200 Y 1-Jul-01 30-Jun-02 12 1) What is the name of the relation? 2) What is the cardinality of the relation? 3) What is the degree of the relation? 4) What is the domain of RentStart? What is the domain of PaymentMethod? 5) What is larger the size of the intension or extension? Relational Model Formal Definition • The relational model may be visualized as tables and fields, but it is formally defined in terms of sets and set operations. • A relation schema R with attributes A =<A1, A2, …, An> is denoted R(A1, A2, …, An) where each Ai is an attribute name that ranges over a domain Di denoted dom(Ai). • Example: • Relation schema: PrivateOwner (OwnerNo, FirstName, LastName, Address, TelephoneNo) • R = PrivateOwner (relation name) • Set A = {OwnerNo, FirstName, LastName, Address, TelephoneNo} • dom(TelephoneNo) is set of all possible Telephone codes • dom(OwnerNo) is set of all possible strings that represent OwnerNo in the organization Relation Schemas and Instances • A relation schema is a definition of Named relation with a set of attribute and domain name pairs. – The relation schema is the intension(内涵） of the relation. • A relational database schema is a set of relation schemas , each with a distinct name • A relation instance denoted r(R) over a relation schema R(A1,A2, …, An) is a set of n-tuples <d1, d2, ..., dn> where each di is an element of dom(Ai) or is null. – The relation instance is the extension（外延） of the relation. – A value of null represents a missing or unknown value. Cartesian Product (review) • The Cartesian product written as D1 × D2 is a set operation that takes two sets D1 and D2 and returns the set of all ordered pairs such that the first element is a member of D1 and the second element is a member of D 2. • Example: – D1 = {1,2,3} – D2 = {A,B} – D1 × D2 = {(1,A), (2,A), (3,A), (1,B), (2,B), (3,B)} • Practice Questions: – 1) Compute D2 × D1. – 2) Compute D2 × D2. – 3) If |D| denotes the number of elements in set D, how many elements are there in D1 × D2 in general. – What is the cardinality of D1 × D2 × D1 × D1? Relation Instance • A relation instance r(R) can also be defined as a subset of the Cartesian product of the domains of all attributes in the relation schema. That is, • r(R) dom(A1) × dom(A2) × … × dom(An) • Example: • R = Person(id, firstName, lastName) • dom(id) = {1,2}, dom(firstName) = {Joe, Steve} • dom(lastName) = {Jones, Perry} • dom(id) × dom(firstName) × dom(lastName) = • { (1,Joe,Jones), (1,Joe,Perry), (1,Steve,Jones), (1,Steve,Perry), • (2,Joe,Jones), (2,Joe,Perry), (2,Steve,Jones), (2,Steve,Perry)} • Assume our DB stores people Joe Jones and Steve Perry, then • r(R) = { (1,Joe, Jones), (2,Steve,Perry)}. Properties of Relations • A relation has several properties: – 1) Each relation name is unique. No two relations have the same name. – 2) Each cell of the relation (value of a domain) contains exactly one atomic (single) value. – 3) Each attribute of a relation has a distinct name. – 4) The values of an attribute are all from the same domain. – 5) Each tuple is distinct. There are no duplicate tuples. This is because relations are sets. – 6) The order of attributes is not really important. • Note that this is different that a mathematical relation and our definitions which specify an ordered tuple. The reason is that the attribute names represent the domain and can be reordered. – 7) The order of tuples has no significance. Relational Keys • Keys are used to uniquely identify a tuple in a relation. – Note that keys apply to the relational schema not to the relational instance. That is, looking at the current instance cannot tell you for sure if the set of attributes is a key. • A superkey (超码) is a set of attributes that uniquely identifies a tuple in a relation. • A key (码) is a minimal set of attributes that uniquely identifies a tuple in a relation. • A candidate key (候选码) is one of the possible keys of a relation. • A primary key (主码) is the candidate key designated as the distinguishing key of a relation. • A foreign key (外码) is a set of attributes in one relation referring to the primary key of another relation. – Foreign keys allow referential integrity to be enforced. Example Relations • DreamHome Database: • Propertys have a unique number, address, type, and rent. • Clients have a unique number, name, and Address. • An client may view multiple properties and a property may Be viewed by multiple clients • Relations: • PropertyForRent (PropertyNo, Address, Type, Rooms) • Client (ClientNo, Name, Address) • Viewing (PropertyNo, ClientNo, DateViewed, Comments) • Underlined attributes denote keys. Example Relation Instances ProperityForRent Client Properity Address Type Rooms Client Name Address No No PA14 16 Holhead Flat 5 CR76 John 56 High St. PL94 6 Argyll St House 4 CR74 Mike 18 Tain St. PG21 18 Dale Rd Flat 3 CR62 Mary 5 Tarbot Rd. Viewing • Questions: Properit Clien ViewDate Comments • 1) Is name a key for Client? yNo tNo 1-Jun-01 • 2) Is viewdate a key for viewing? PA14 CR62 goods 1-Aug-01 • 3) List all the superkeys for viewing. PL94 CR74 preference 1-Jul-01 • 4) List the candidate keys for viewing. PG21 CR62 expensive Relational Integrity • Integrity rules are used to insure the data is accurate. • Constraints are rules or restrictions that apply to the database and limit the data values it may store. • Types of constraints: • 1) Domain constraint - Every value for an attribute must be an element of the attribute's domain or be null. – null represents a value that is currently unknown or not applicable. – null is not the same as zero or an empty string. • 2) Entity integrity constraint - In a base relation, no attribute of a primary key can be null. • 3) Referential integrity constraint - If a foreign key exists in a relation, then the foreign key value must match a primary key value of a tuple in the referenced relation or be null. Foreign Keys Example ProperityForRent Client Properity Address Type Room Client Name Address No s No PA14 16 Flat 5 CR76 John 56 High St. Holhead CR74 Mike 18 Tain St. PL94 6 Argyll St House 4 CR62 Mary 5 Tarbot PG21 18 Dale Rd Flat 3 Rd. Viewing Properit Clien ViewDate Comments yNo tNo PA14 CR62 1-Jun-01 goods PL94 CR74 1-Aug-01 preference PG21 CR62 1-Jul-01 expensive Integrity Questions ProperityForRent Client Properity Address Type Room No s Client Name Address Null 25Xishilu house 4 No PA14 16 Flat 5 CR76 John 56 High St. Holhead CR74 Mike 1-Oct-02 PL94 6 Argyll St House Tom Null Tom 56 High St. PG21 18 Dale Rd Flat 3 CR62 2004 5 Tarbot Rd. Viewing Properit Clien ViewDate Comments yNo tNo Question: PA14 CR62 1-Jun-01 goods 1) Find all violations of integrity PL94 CR74 1-Aug-01 preference constraints in these three relations. PL54 CR76 4-May-01 Null PA14 null 4-May-01 yes PG21 CR62 1-Jul-01 Expensive General Constraints • There are more general constraints that some DBMSs can enforce. These constraints are often called enterprise constraints or semantic integrity constraints. • Examples: – An staff cannot work on more than 2 projects. – An staff cannot make more money than their manager. – An staff must be assigned to one position. • Ensuring the database follows these constraints is usually achieved using triggers and assertions. Relational Algebra (代数） • A query language is used to update and retrieve data that is stored in a data model. • Relational algebra is a set of relational operations for retrieving data. • Just like algebra with numbers, relational algebra consists of operands (操作数）which are relations) and a set of operators（操作 符）. • Every relational operator takes as input one or more relations and produces a relation as output. – Closure property - input is relations, output is relations – Unary operations - operate on one relation – Binary operations - have two relations as input • A sequence of relational algebra operators is called a relational algebra expression. Relational Algebra Operators • Relational Operators: Selection σ Projection Π Cartesian product × Join ⋈ Union ∪ Difference - Intersection ∩ Division ÷ • Note that relational algebra is the foundation of ALL relational database systems. SQL gets translated into relational algebra. Selection Operation • The selection operation is a unary operation that takes in a relation as input and returns a new relation as output that contains a subset of the tuples of the input relation. That is, the output relation has the same number of columns as the input relation, but may have less tuples. • To determine which tuples are in the output, the selection operation has a specified condition, called a predicate, that tuples must satisfy to be in the output. • The predicate is similar to a condition in an if statement. Selection Operation Formal Definition • The selection operation on relation R with predicate F is denoted by σF(R). • σF(R) = {t | t∈R and F(t) is true} • Where R is a relation, t is a tuple variable, F is a formula (predicate) consisting of – operands that are constants or attributes – comparison operators: <, >, =, ≠, ≤, ≥ – logical operators: AND, OR, NOT PropertyForRent: Selection Example PropertyNo Street Type rooms rent OwnerNo PA14 16 Holhead House 6 650 C046 PL94 6 Argyll St Flat 4 400 C087 PG4 6 Lawrence St. Flat 3 350 C040 PG36 2 Manor Rd Flat 3 475 C093 PG21 18 Dale Rd House 5 600 C087 PG16 5 Novar Dr. Flat 4 450 C093 σType＝’House’(PropertyForRent) PropertyNo Street Type rooms rent OwnerNo PA14 16 Holhead House 6 650 C046 PG21 18 Dale Rd House 5 600 C087 σType＝’flat ‘ AND Rent<400 (PropertyForRent) PropertyNo Street Type rooms rent OwnerNo PG4 6 Lawrence St. Flat 3 350 C040 Selection Questions Viewing • Write the relational algebra Properity Client ViewDate Comments expression that: No No PA14 CR62 1-Jun-01 goods • 1) Returns all clients viewing PL94 CR74 1-Aug-01 preference property PA14. PL54 CR76 4-May-01 Null PA14 CR76 12-May-01 yes • 2) Returns all all clients viewing PA14 CR74 22-Jul-01 Too small during 1-May-01 and 30-Jul-01 PG21 CR62 1-Jul-01 Expensive • 3) Returns all property that have been viewed by client CR74. Projection Operation • The projection operation is a unary operation that takes in a relation as input and returns a new relation as output that contains a subset of the attributes of the input relation and all non-duplicate tuples. • The output relation has the same number of tuples as the input relation unless removing the attributes caused duplicates to be present. – Question: When are we guaranteed to never have duplicates when performing a projection operation? • Besides the relation, the projection operation takes as input the names of the attributes that are to be in the output relation. Projection Operation Formal Definition • The projection operation on relation R with output attributes A1,…,Am is denoted by ΠA1,…,Am(R). • ΠA1,…,Am(R) = { t [A1,…, Am] | t∈R } • Where R is a relation, t is a tuple variable • {A1,…,Am} is a subset of the attributes of R over which the projection will be performed. • Order of A1,…, Am is significant in the result. • Cardinality of ΠA1,…,Am(R) is not necessarily the same as R because of duplicate removal. Projection Example ΠClientNo, Comments(Viewing), ΠClientNo(Viewing) Viewing Properit Clien ViewDate Comments ClientNo Comments Clien yNo tNo tNo PA14 CR62 1-Jun-01 goods CR62 goods CR62 PL94 CR74 1-Aug-01 preference CR74 preference CR74 PL54 CR76 4-May-01 Null CR76 Null CR76 PA14 CR76 12-May-01 yes CR76 yes PA14 CR74 22-Jul-01 Too small CR74 Too small PG21 CR62 1-Jul-01 Expensive CR62 Expensive Union • Union is a binary operation that takes two relations R and S as input and produces an output relation that includes all tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated. • General form: • R ∪ S = {t | t∈R or t∈S} • where R, S are relations, t is a tuple variable. • R and S must be union-compatible. To be union- compatible means that the relations must have the same number of attributes with the same domains. ProperityNo(Viewing) ProperityNo(PropertyForRent) Viewing ProperityNo ClientNo ViewDate Comments PA08 CR62 1-Jun-01 goods ProperityNo PL94 CR74 1-Aug-01 preference PA08 PA14 CR76 12-May-01 yes PL94 PA14 CR74 22-Jul-01 Too small PA14 PropertyForRent PG4 PropertyNo Street Type rooms rent OwnerNo PG36 PA14 16 Holhead House 6 650 C046 PG21 PG16 PL94 6 Argyll St Flat 4 400 C087 PG4 6 Lawrence St. Flat 3 350 C040 PG36 2 Manor Rd Flat 3 375 C093 PG21 18 Dale Rd House 5 600 C087 PG16 5 Novar Dr. Flat 4 450 C093 Set Difference • Set difference is a binary operation that takes two relations R and S as input and produces an output relation that contains all the tuples of R that are not in S. General form: • R – S = {t |t∈R and tS} • where R and S are relations, t is a tuple variable. • Note that: R - S ≠S - R R and S must be union compatible. ProperityNo(Viewing) -ProperityNo(PropertyForRent) Viewing ProperityNo ClientNo ViewDate Comments PA08 CR62 1-Jun-01 goods ProperityNo PL94 CR74 1-Aug-01 preference PA08 PA14 CR76 12-May-01 yes PA14 CR74 22-Jul-01 Too small PropertyForRent PropertyNo Street Type rooms rent OwnerNo PA14 16 Holhead House 6 650 C046 PL94 6 Argyll St Flat 4 400 C087 PG4 6 Lawrence St. Flat 3 350 C040 PG36 2 Manor Rd Flat 3 375 C093 PG21 18 Dale Rd House 5 600 C087 PG16 5 Novar Dr. Flat 4 450 C093 Intersection • Intersection is a binary operation that takes two relations R and S as input and produces an output relation which contains all tuples that are in both R and S. General form: • R ∩ S = {t | t∈R and t∈S} • where R, S are relations, t is a tuple variable. • R and S must be union-compatible. • Note that R ∩ S = R - (R - S) = S - (S - R). ProperityNo(Viewing) ∩ProperityNo(PropertyForRent) Viewing ProperityNo ClientNo ViewDate Comments PA08 CR62 1-Jun-01 goods PL94 CR74 1-Aug-01 preference 12-May-01 ProperityNo PA14 CR76 yes 22-Jul-01 PL94 PA14 CR74 Too small PropertyForRent PA14 PropertyNo Street Type rooms rent OwnerNo PA14 16 Holhead House 6 650 C046 PL94 6 Argyll St Flat 4 400 C087 PG4 6 Lawrence St. Flat 3 350 C040 PG36 2 Manor Rd Flat 3 375 C093 PG21 18 Dale Rd House 5 600 C087 PG16 5 Novar Dr. Flat 4 450 C093 Cartesian Product • The Cartesian product of two relations R (of degree k1) and S (of degree k2) is: • R × S = {t | t [A1, …, Ak1]∈R and t [Ak1+1, …, Ak1+k2]∈S} • The result of R × S is a relation of degree (k1 + k2) and consists of all (k1 + k2)-tuples where each tuple is a concatenation of one tuple of R with one tuple of S. • The cardinality of R × S is |R| * |S|. • The Cartesian product is also known as cross product. Cartesian Product Example Staff Name StaffNo BrachNo Name StaffNo Brach Proper Street Type Mike S01 B01 No tyNo Tom S07 B01 Mike S01 B01 PA14 16 Holhead House Mary S15 B03 Mike S01 B01 PL94 6 Argyll St Flat Mike S01 B01 PG4 9 Lawrence St. Flat Tom S07 B01 PA14 16 Holhead House PropertyForRent Tom S07 B01 PL94 6 Argyll St Flat Prope Street Type rtyNo Tom S07 B01 PG4 9 Lawrence St. Flat PA14 16 Holhead House Mary S15 B03 PA14 16 Holhead House PL94 6 Argyll St Flat Mary S15 B03 PL94 6 Argyll St Flat PG4 6 Lawrence St. Flat Mary S15 B03 PG4 9Lawrence St. Flat θ-Join • Theta (θ) join is a derivative of the Cartesian product. Instead of taking all combinations of tuples from R and S, we only take a subset of those tuples that match a given condition F: • R ⋈FS = {t | t [A1, …, Ak1]∈R and t [Ak1+1, …, Ak1+k2]∈S and F(t) is true} • where • R, S are relations, t is a tuple variable • F(t) is a formula defined as that of selection. • Note that R ⋈FS = σF(R × S). θ-Join Example Staff Staff ⋈Type=‘Flat’ PropertyForRent Name StaffNo BrachNo Mike S01 B01 Name StaffNo Brach Property Street Type Tom S07 B01 No No Mary S15 B03 Mike S01 B01 PL94 6 Argyll St Flat Mike S01 B01 PG4 9 Lawrence St. Flat Tom S07 B01 PL94 6 Argyll St Flat PropertyForRent Tom S07 B01 PG4 9 Lawrence St. Flat Proper Street Type Mary S15 B03 PL94 6 Argyll St Flat tyNo House Mary S15 B03 PG4 9Lawrence St. Flat PA14 16 Holhead PL94 6 Argyll St Flat PG4 6 Lawrence St. Flat Types of Joins • The θ-Join is a general join in that it allows any expression in the condition F. However, there are more specialized joins that are frequently used. • A equijoin(等值连接) only contains the equality operator (=) in formula F. • e.g. Viewing ⋈ Viewing.PropertyNo = PropertyForRent.PropertyNo PropertyForRent • A natural join(自然连接) over two relations R and S denoted by R * S is the equijoin of R and S over a set of attributes common to both R and S. – It removes the “extra copies” of the join attributes. – Normally assumes the attributes have the same name in both relations. EquiJoin Example Staff Staff ⋈Staff.staffNo=PropertyforRent.StaffNo PropertyForRent Name StaffNo Branch Mike S01 B01 Name S.Staff BrachN P.Staff Propert Type Tom S07 B01 No o No yNo Mary S15 B03 Mike S01 B01 S01 PL94 Flat PropertyForRent Tom S07 B01 S07 PA08 Flat Propert StaffNo Type Tom S07 B01 S07 PG36 House yNo Mary S15 B03 S15 PA14 House PA14 S15 House Mary S15 B03 S15 PA74 Flat PL94 S01 Flat Mary S15 B03 S15 PG4 Flat PA74 S15 Flat PA08 S07 Flat PG36 S07 House PG4 S15 Flat NaturalJoin Example Staff Name StaffNo Branch Staff ⋈ PropertyForRent Mike S01 B01 Tom S07 B01 Name StaffNo BrachNo Property Type B03 No Mary S15 Mike S01 B01 PL94 Flat PropertyForRent Tom S07 B01 PA08 Flat Property StaffNo Type Tom S07 B01 PG36 House No Mary S15 B03 PA14 House PA14 S15 House Mary S15 B03 PA74 Flat PL94 S01 Flat Mary S15 B03 PG4 Flat PA74 S15 Flat PA08 S07 Flat PG36 S07 House PG4 S15 Flat Join Practice Questions Viewing Client Properity Client ViewDate Comments Client Name Address No No No PA08 CR62 1-Jun-01 goods CR76 John 56 High St. PL94 CR74 1-Aug-01 preference CR74 Mike 18 Tain St. PA14 CR74 22-Jul-01 Too small CR78 Tom 7 London St. PropertyForRent CR62 Mary 5 Tarbot Rd. PropertyNo Address Type PA14 16 Holhead House Compute the following joins: PL94 6 Argyll St Flat Client ⋈ Viewing ⋈ PropertyForRent PG4 6 Lawrence St. Flat PG36 2 Manor Rd Flat PG21 18 Dale Rd House PA08 5 Novar Dr. Flat Outer Joins • Outer joins are used in cases where performing a join "loses“ some tuples of the relations. These are called dangling tuples. • There are three types of outer joins: • 1) Left outer join - R ⋊ S - The output contains all tuples of R that match with tuples of S. If there is a tuple in R that matches with no tuple in S, the tuple is included in the final result and is padded with nulls for the attributes of S. • 2) Right outer join - R ⋉ S - The output contains all tuples of S that match with tuples of R. If there is a tuple in S that matches with no tuple in R, the tuple is included in the final result and is padded with nulls for the attributes of R. • 3) Full outer join - R ⋊ ⋉ S - All tuples of R and S are included in the result whether or not they have a matching tuple in the other relation. Left outer join Example Viewing PropertyForRent ⋊ Viewing Prope Client ViewDa Comments rityNo No te Prope Address Type Clien ViewD Com PA08 CR62 1-Jun-01 goods rtyNo tNo ate ments PL94 CR74 1-Aug-01 preference PA14 16 Holhead House Null Null Null PA08 CR74 22-Jul-01 Too small PL94 6 Argyll St Flat CR74 1-Aug- prefere 01 nce PropertyForRent PG4 6 Lawrence St. Flat Null Null Null PropertyNo Address Type PG36 2 Manor Rd Flat Null Null Null PA14 16 Holhead House PG21 18 Dale Rd House Null Null Null PL94 6 Argyll St Flat PA08 5 Novar Dr. Flat CR74 22-Jul- Too 01 small PG4 6 Lawrence St. Flat PA08 5 Novar Dr. Flat CR62 1-Jun- goods PG36 2 Manor Rd Flat 01 PG21 18 Dale Rd House PA08 5 Novar Dr. Flat SemiJoin • The Semijoin operation defines a relation that contain the tuples of R that participate in the join of R with S. • The simijoin operation performs a join of two relationa and then projects over the attributes of the first operand. • It is particularly useful for computing joins in distributed systems. • R⊲F S = ΠA（R ⋈FS）, A is the set of all attributes of R. Semijoin Example Viewing PropertyForRent ⊲ PropertyForRent.propertyNo = viewing.PropertyNo Viewing Proper Client ViewDat Comments ityNo No e PA08 CR62 1-Jun-01 goods Proper Address Type tyNo PL94 CR74 1-Aug-01 preference PL94 6 Argyll St Flat PA08 CR74 22-Jul-01 Too small PA08 5 Novar Dr. Flat PropertyForRent PropertyNo Address Type PA14 16 Holhead House PL94 6 Argyll St Flat PG4 6 Lawrence St. Flat PG36 2 Manor Rd Flat PG21 18 Dale Rd House PA08 5 Novar Dr. Flat Division Operator • For the division operation to be defined the set of attributes of S must be a subset of the attributes of R. • Let A is the set of attributes of R, B is the set of attributes of S, B⊆ A, C = A – B. • R ÷ S define a relation over C that consists of the set of tuples from R that match the combination of every tuple in S . Duplicate tuples are eliminated • Note that R ÷ S = ΠR-S(R) -ΠR-S ((ΠR-S (R)×S)-R). Division Example Viewing • Viewing ÷ PropertyForRent Proper Client ClientNo ityNo No CR56 PA14 CR36 PG4 CR76 PG4 CR56 Question: PA14 CR62 1) Can you give the relational algebra expression to PG36 CR56 find the property that are viewed on by all Clients? 2) If there are 6 properties in the database, and the PropertyForRent result of the query ΠClientNo,PropertyNo(Viewing ) ÷ PropertyNo Π PropertyNo(PropertyForRent) is 2 records,what is PG4 the minimum # of records that must be in the Viewing relation? PG36 Combining Operations • Relational algebra operations can be combined in one expression by nesting them: ΠAddress (σName＝’Mike’(Staff) ⋈ σType＝’House’(PropertyForRent)) Return the addresses of “House” properties that are overseen by staff „Mike'. • Operations also can be combined by using temporary relation variables to hold intermediate results. • We will use the assignment operator for indicating that the result of an operation is assigned to a temporary relation: HosuseProperty σType＝’House’(PropertyForRent) Rename Operation • Renaming can be applied when assigning a result: Result(StaffNum, FullName, Earning) Πstaffno,fName,salary (Staff) Operator Precedence • Just like mathematical operators, the relational operators have precedence. • The precedence of operators from highest to lowest is: unary operators: σ,Π Cartesian product and joins : ×, ⋈ intersection, division: ∩, ÷ union and set difference: ,－ • Parentheses can be used to changed the order of operations. Complete Set of Relational Algebra Operators • The five fundamental operations in relational algebra: σ,Π, ∪， －， × • Other operations call be derived from the five fundamental operations . Practice Questions • Relational database schema: – branch (bname, address, city, assets) – customer (cname, street, city) – deposit (accnum, cname, bname, balance) – borrow (accnum, cname, bname, amount) • 1) List the names of all branches of the bank. • 2) List the names of all deposit customers together with their account numbers. • 3) Find all cities where at least one customer lives. • 4) Find all cities with at least one branch. • 5) Find all cities with at least one branch or customer. • 6) Find all cities that have a branch but no customers who live in that city. • 7) Find the names of all branches with assets greater than $2,500. • 8) List the name and cities of all customers who have an account with balance greater than $2,000. Practice Questions (2) • Relational database schema: – branch (bname, address, city, assets) – customer (cname, street, city) – deposit (accnum, cname, bname, balance) – borrow (accnum, cname, bname, amount) • 9) List all the cities with at least one customer but without any bank branches. • 10) Find the name of all the customers who live in a city with no bank branches. • 11) Find all the cities that have both customers and bank branches. • 12) Find the name of customers who have deposits in every branch of the bank. • 13) Find the name and assets of all branches which have deposit customers living in Iowa City. • 14) Find all the customers who have both an account and a loan at the Coralville branch. • 15) Your own? Conclusion • The relational model represents data as relations which are sets of tuples. Each relational schema consists of a set of attribute names which represent a domain. • The relational model has several forms of constraints to guarantee data integrity including: domain, entity integrity and referential integrity constraints • Keys are used to uniquely identify tuples in relations. • Relational algebra is a set of operations for answering queries on data stored in the relational model. – The 5 basic relational operators are: {σ, Π, ×, ∪, -}. – By combining relational operators, queries can be answered over the base relations.