Relational Model PDF

RELATIONAL MODEL  Structure of Relational Databases  Relational Algebra Parul Saxena, MITS  Extended Relational-Algebra- Operations  Modification of the Database 1 EXAMPLE OF A RELATI...

RELATIONAL MODEL  Structure of Relational Databases  Relational Algebra Parul Saxena, MITS  Extended Relational-Algebra- Operations  Modification of the Database 1 EXAMPLE OF A RELATION Parul Saxena, MITS 2 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 Parul Saxena, MITS 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 3 ATTRIBUTE TYPES  Each attribute of a relation has a name  The set of allowed values for each attribute is Parul Saxena, MITS called the domain of the attribute. Example: Gender (M/F)  Attribute values are (normally) required to be atomic, that is, indivisible  E.g. multivalued attribute values are not atomic  E.g. composite attribute values are not atomic  The special value null is a member of every domain 4 RELATION SCHEMA  A1, A2, …, An are attributes  R = (A1, A2, …, An ) is a relation schema Parul Saxena, MITS E.g. Customer-schema = (customer-name, customer-street, customer-city) 5 RELATION INSTANCE  The current values (relation instance) of a relation are specified by a table  An element t of r is a tuple, represented by a row in a table Parul Saxena, MITS attributes (or columns) customer-name customer-street customer-city Jones Main Harrison Smith North Rye tuples Curry North Rye (or rows) Lindsay Park Pittsfield customer 6  RELATIONS ARE UNORDERED Order of tuples is irrelevant (tuples may be stored in an arbitrary order) E.g. account relation with unordered tuples Parul Saxena, MITS 7 DATABASE  A database consists of multiple 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 Parul Saxena, MITS depositor : 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 deals with how to design relational schemas 8 THE CUSTOMER RELATION Parul Saxena, MITS 9 THE DEPOSITOR RELATION Parul Saxena, MITS 10 E-R DIAGRAM FOR THE BANKING ENTERPRISE Parul Saxena, MITS 11 SCHEMA DIAGRAM FOR THE BANKING ENTERPRISE Parul Saxena, MITS 12 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) Parul Saxena, MITS  by “possible r” we mean a relation r that could exist in the enterprise we are modeling.  Example: {customer-name, customer-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 subset of it is a superkey. 13  CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple Parul Saxena, MITS candidate keys but only a single primary key.  Properties of Candidate key:  It must contain unique values  Candidate key may have multiple attributes  Must not contain null values  It should contain minimum fields to ensure uniqueness  Uniquely identify each record in a table 14 HOW CANDIDATE KEY IS DIFFERENT FROM SUPER KEY?  Candidate keys are selected from the set of super keys, the only thing we take care while selecting candidate key is: It should not have any redundant attribute. That’s the reason they are also termed as minimal super key.  Table: Employee Parul Saxena, MITS  Emp_SSN Emp_Number Emp_Name --------- ---------- -------- 123456789 226 Steve 999999321 227 Ajeet 888997212 228 Chaitanya 777778888 229 Robert  Super keys: The above table has following super keys. All of the following sets of super key are able to uniquely identify a row of the employee table.  {Emp_SSN}  {Emp_Number}  {Emp_SSN, Emp_Number}  {Emp_SSN, Emp_Name} 15  {Emp_SSN, Emp_Number, Emp_Name}  {Emp_Number, Emp_Name}  Candidate Keys: A candidate key is a minimal super key with no redundant attributes. The following two set of super keys are chosen from the above sets. Parul Saxena, MITS  {Emp_SSN}  {Emp_Number}  Primary key: A Primary key is selected from a set of candidate keys. This is done by database admin or database designer. We can say that either {Emp_SSN} or {Emp_Number} can be chosen as a primary key for the table Employee. 16 SUPER KEY VS CANDIDATE KEY  1. First you have to understand that all the candidate keys are super keys. This is because the candidate keys are chosen out of the super keys. Parul Saxena, MITS 2. How we choose candidate keys from the set of super keys? We look for those keys from which we cannot remove any fields. In the above example, we have not chosen {Emp_SSN, Emp_Name} as candidate key because {Emp_SSN} alone can identify a unique row in the table and Emp_Name is redundant. 17 DETERMINING KEYS FROM E-R SETS  Strong entity set. The primary key of the entity set becomes the primary key of the relation. Parul Saxena, MITS  Weak entity set. The primary key of the relation consists of the union of the primary key of the strong entity set and the discriminator of the weak entity set.  Relationship set. The union of the primary keys of the related entity sets becomes a super key of the relation. 18 RELATIONAL ALGEBRA  Procedural language  Six basic operators Parul Saxena, MITS  select  project  union  set difference  Cartesian product  rename  The operators take one or more relations as inputs and give a new relation as a result. 19 SELECT OPERATION – EXAMPLE Relation r A B C D Parul Saxena, MITS α α 1 7 α β 5 7 β β 12 3 β β 23 10 σA=B ^ D > 5 (r) A B C D α α 1 7 20 β β 23 10 20 SELECT OPERATION  Notation:  p(r)  p is called the selection predicate  Defined as: Parul Saxena, MITS p(r) = {t | t  r and p(t)} Where p is a formula in propositional calculus consisting of terms connected by :  (and),  (or),  (not) Each term is one of: op or where op is one of: =, , >, . 1200 (loan) Parul Saxena, MITS Find the loan number for each loan of an amount greater than 1200 loan-number (amount > 1200 (loan)) 35 EXAMPLE QUERIES  Find the names of all customers who have a loan, an account, or both, from the bank Parul Saxena, MITS  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) 36 EXAMPLE QUERIES  Find the names of all customers who have a loan at the Perryridge branch. customer-name (branch-name=“Perryridge” AND borrower.loan-number = loan.loan-number(borrower x loan)) Parul Saxena, MITS 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” AND borrower.loan-number = loan.loan-number(borrower x loan)) – customer-name(depositor) 37 EXAMPLE QUERIES Find the largest account balance  Rename account relation as d  The query is: Parul Saxena, MITS balance(account) - account.balance (account.balance < d.balance (account x d (account))) 38 FORMAL DEFINITION  A basic expression in the relational algebra consists of either one of the following:  A relation in the database Parul Saxena, MITS  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 39   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. Parul Saxena, MITS  Set intersection  Natural join  Division  Assignment 40 SET-INTERSECTION OPERATION  Notation: r  s  Defined as: Parul Saxena, MITS  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) 41 SET-INTERSECTION OPERATION - EXAMPLE A B A B  Relation r, s:  1  2  2  3  1 Parul Saxena, MITS r s A B  rs  2 42 NATURAL-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: Parul Saxena, MITS  Consider 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, add a tuple t to the result, where  t has the same value as tr on r  t has the 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: 43 A, r.B, C, r.D, E (r.B = s.B  r.D = s.D (r x s)) NATURAL JOIN OPERATION – EXAMPLE  Relations r, s: A B C D B D E  1  a 1 a  Parul Saxena, MITS  2  a 3 a   4  b 1 a   1  a 2 b   2  b 3 b  r s r s A B C D E  1  a   1  a   1  a   1  a  44  2  b  DIVISION OPERATION rs  Suited to queries that include the phrase “for all”. Parul Saxena, MITS  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 ) } 45 DIVISION OPERATION – EXAMPLE Relations r, s: A B B  1 1  2 2 Parul Saxena, MITS  3  1 s  1  1  3  4  6  1  2 r  s: A r   46 ANOTHER DIVISION EXAMPLE Relations r, s: A B C D E D E  a  a 1 a 1  a  a 1 b 1 Parul Saxena, MITS  a  b 1 s  a  a 1  a  b 3  a  a 1  a  b 1  a  b 1 r r  s: A B C  a   a  47 ASSIGNMENT OPERATION  The assignment operation () provides a convenient way to express complex queries.  Write query as a sequential 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. Parul Saxena, MITS  Example: Write r  s as  To retrieve the first name, last name & salary of all employees who work in dept. no. 5 FNAME,LNAME,SAL( dno=5(EMP)) temp1   dno=5(EMP) result  FNAME,LNAME,SAL (temp1)  The result to the right of the  is assigned to the relation variable on the left of the .  May use variable in subsequent expressions. 48 EXAMPLE QUERIES  Find all customers who have an account from at least the “Downtown” and the Uptown” branches. Query 1 CN(BN=“Downtown”(depositor account))  Parul Saxena, MITS CN(BN=“Uptown”(depositor account)) where CN denotes customer-name and BN denotes branch-name. 49 account (account-number, branch-name, balance) branch (branch-name, branch-city, assets) Parul Saxena, MITS Depositor (customer-name, account-number) 50 EXAMPLE QUERIES  Find all customers who have an account at all branches located in Brooklyn city. customer-name, branch-name (depositor account) Parul Saxena, MITS  branch-name (branch-city = “Brooklyn” (branch)) 51  EXTENDED RELATIONAL-ALGEBRA-OPERATIONS  Generalized Projection  Outer Join Parul Saxena, MITS  Aggregate Functions 52 GENERALIZED PROJECTION  Extends the projection operation by allowing arithmetic functions to be used in the projection list. Parul Saxena, MITS  F1, F2, …, Fn(E)  E is any relational-algebra expression  Each of F1, F2, …, Fn are arithmetic expressions involving constants and attributes in the schema of E.  Given relation credit-info(customer-name, limit, credit-balance), find how much more each person can spend: customer-name, limit – credit-balance (credit-info) 53  AGGREGATE FUNCTIONS AND OPERATIONS  Aggregation function takes a collection of values and returns a single value as a result. avg: average value Parul Saxena, MITS min: minimum value max: maximum value sum: sum of values count: number of values  Aggregate operation in relational algebra (g– calligraphic G) G1, G2, …, Gn g F1( A1), F2( A2),…, Fn( An) (E)  E is any relational-algebra expression  G1, G2 …, Gn is a list of attributes on which to group (can be empty)  Each Fi is an aggregate function  Each Ai is an attribute name 54 AGGREGATE OPERATION – EXAMPLE  Relation r: A B C   7 Parul Saxena, MITS   7   3   10 sum(c) g sum(c) (r) 27 55 AGGREGATE OPERATION – EXAMPLE  Relation account grouped by branch-name: branch-name account-number balance Parul Saxena, MITS Perryridge A-102 400 Perryridge A-201 900 Brighton A-217 750 Brighton A-215 750 Redwood A-222 700 branch-name g sum(balance) (account) branch-name sum(balance) Perryridge 1300 Brighton 1500 56 Redwood 700 AGGREGATE FUNCTIONS (CONT.)  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 Parul Saxena, MITS  operation branch-name g sum(balance) as sum-balance (account) branch-name sum-balance Perryridge 1300 Brighton 1500 57 Redwood 700 OUTER JOIN  An extension of the join operation that avoids loss of information. Parul Saxena, MITS  Computes the join and then adds tuples form one relation that do 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. 58 OUTER JOIN – EXAMPLE  Relation loan loan-number branch-name amount L-170 Downtown 3000 Parul Saxena, MITS L-230 Redwood 4000 L-260 Perryridge 1700 Relation borrower customer-name loan-number Jones L-170 Smith L-230 Hayes L-155 59 OUTER JOIN – EXAMPLE  Inner Join loan Borrower loan-number branch-name amount customer-name Parul Saxena, MITS L-170 Downtown 3000 Jones L-230 Redwood 4000 Smith Left Outer Join loan Borrower loan-number branch-name amount customer-name L-170 Downtown 3000 Jones L-230 Redwood 4000 Smith L-260 Perryridge 1700 null 60 OUTER JOIN – EXAMPLE  Right Outer Join loan borrower loan-number branch-name amount customer-name Parul Saxena, MITS L-170 Downtown 3000 Jones L-230 Redwood 4000 Smith L-155 null null Hayes Full Outer Join loan borrower loan-number branch-name amount customer-name L-170 Downtown 3000 Jones L-230 Redwood 4000 Smith L-260 Perryridge 1700 null L-155 null null Hayes 61 NULL VALUES  It is possible for tuples to have a null value, denoted by null, for some of their attributes Parul Saxena, MITS  null signifies an unknown value or that a value does not exist.  The result of any arithmetic expression involving null is null.  Aggregate functions simply ignore null values  For duplicate elimination and grouping, null is treated like any other value, and two nulls are assumed to be the same 62 MODIFICATION OF THE DATABASE  The content of the database may be modified using the following operations: Parul Saxena, MITS  Deletion  Insertion  Updating  All these operations are expressed using the assignment operator. 63 DELETION  A delete request is expressed similarly to a query, except instead of displaying tuples to the user, the selected tuples are removed from the Parul Saxena, MITS database.  Can delete only whole tuples; cannot delete values on only particular attributes  A deletion is expressed in relational algebra by: rr–E where r is a relation and E is a relational algebra query. 64 DELETION EXAMPLES  Delete all account records in the Perryridge branch. account  account – branch-name = “Perryridge” (account) Parul Saxena, MITS Delete all loan records with amount in the range of 0 to 50 loan  loan – amount >=0 and amount 10000 (account))  AN, BN, BAL *.05 (BAL