Part 2 - 1 The Relational Database Model by suchenfz

VIEWS: 1 PAGES: 58

									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 tS}

• 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.

								
To top