Docstoc

Database Concepts

Document Sample
Database Concepts Powered By Docstoc
					IST 210
          Entity-Relationship Model and Diagrams
                            (continued)



                   Todd S. Bacastow
                         IST 210
                    Organization of data
IST 210
          Database Design

 Conceptual design        Entity-Relationship Model




                 Logical design
                                               Logical Schema




                             Physical design
          Critical Success Factors in
IST 210
          Database Design
             Work interactively with the users as much as
              possible
             Follow a methodology throughout the data
              modeling process
             Incorporate structural and integrity
              considerations into the data models
             Combine conceptualization, normalization, and
              validation techniques into the methodology
IST 210   Conceptual Design Phase
              TOP-DOWN           Identify Entities




                             Identify Relationships




                                 Identify Attributes




              BOTTOM-UP



                                 Identify Relationships




                                 Identify Dependencies

                          DATA

                                 Collect Data
IST 210       ERD as a “Semantic Model”
             Semantics: Relationships between symbols
              and their meanings.
                 A semantic model attempts to capture the
                  meaning of user's information and provide a
                  concise, high-level description of that information.
                 The information is represented by logical
                  associations (relations) between pairs of objects
                  and by the classification of objects into categories
                  (entities).
IST 210   Entity, Attribute, Relationship
              Name            Address


                                                               Numb

                     CLIENT                     PASSPORT

                                                                Exp


                                        •   A client must have a passport
                ITINERARY               •   Each client has only one passport
                                        •   A client may have one or more itinerates
                                        •   An itinerary belongs to one client
               Date       city
IST 210
           Basic relationships

          One-to-One     HUSBAND                    WIFE

                         • A husband has one wife
                         • A wife has one husband

          One-to-Many    TEAM                       PARTICIPANT

                         • A team has many participants
                         • Many participants belong to one team

          Many-to-Many   EMPLOYEE                          SKILL


                         • An employee has many skills
                         • Many employees have the same skill
IST 210
          Exercise
     Business rules: A university where a student may
      take many courses employs lecturers. Each lecturer
      teaches one or more courses but no course is taught
      by more than one lecturer. Each student has to
      complete two or three assignments for each course.
     Requirement: Draw an entity relationship (ER)
      diagram for the situation described.
IST 210
          Solution
IST 210
          Review
         Use ER Diagrams to help understand
             The essential information
             Visual guide to the organization
             Plan for how to proceed
             Improve database design
IST 210   Logical design phase

             Conceptual
             E-R Model




                          1. REFINE THE CONCEPTUAL MODEL


                      Refined Conceptual Model


                      2.APPLY THE RULES OF NORMALIZATION

                                                           Logical
                                                            Data
                                                            Model
IST 210
          The Next Step
                             Table1
                             Date           Name       Address                   Complaint
                                 9/9/2001   A Smith    Bantry, Cork              Order didn't arrive
                               11/10/2001   John Doe   New York, USA             Book didn't arrive
                               11/10/2001   John Doe   Fairview, Dublin          Cr. card processed twice
                                 5/1/2002   A Smith    Bantry, Cork              Wrong product delivered
                                8/17/2002   M Black    Canada
                               10/12/2002   F White    Toronto, Canada           Order took 4 weeks



          Going from this:                      Table2
                                                                                                                                                     To this:
                                               Date            Name        Address            Complaint
                                                  09/09/2001   A Smith     Bantry, Cork       Order didn't arrive
                                                  10/11/2001   John Doe    New York, USA      Book didn't arrive
                                                  10/11/2001   John Doe    Fairview, Dublin   Cr. card processed twice
                                                  01/05/2002   A Smith     Bantry, Cork       Wrong product delivered
                                                  17/08/2002   M Black     Canada
                                                  12/10/2002   F White     Toronto, Canada    Order took 4 weeks



                                                                    Table3
                                                                          ssss           ewrtg        rewby               Complaint
                                                                          ddd            rdf          Bantry, Cork        Order didn't arrive
                                                                          ddd            fr           New York, USA       Book didn't arrive
                                                                          ddd            htyu         Fairview, Dublin    Cr. card processed twice
                                                                          ddd            njkl         Bantry, Cork        Wrong product delivered
                                                                          ddd            ersw         Canada
                                                                          ww             gynk         Toronto, Canada     Order took 4 weeks



                                                                                         Table4
                                                                                              ssss            ewrtg       rewby                Complaint
                                                                                              ddd             rdf         Bantry, Cork         Order didn't arrive
                                                                                              ddd             fr          New York, USA        Book didn't arrive
                                                                                              ddd             htyu        Fairview, Dublin     Cr. card processed twice
                                                                                              ddd             njkl        Bantry, Cork         Wrong product delivered
                                                                                              ddd             ersw        Canada
                                                                                              ww              gynk        Toronto, Canada      Order took 4 weeks


                                                                                                                   Table5
                                                                                                                   ssss            ewrtg         rewby                Complaint
                                                                                                                   ddd             rdf           Bantry, Cork         Order didn't arrive
                                                                                                                   ddd             fr            New York, USA        Book didn't arrive
                                                                                                                   ddd             htyu          Fairview, Dublin     Cr. card processed twice
                                                                                                                   ddd             njkl          Bantry, Cork         Wrong product delivered
                                                                                                                   ddd             ersw          Canada
                                                                                                                   ww              gynk          Toronto, Canada      Order took 4 weeks
IST 210
              Logical Database Design
     Main objectives of relational databases
      is to ensure that each item of data is
      held only once within the database:
             Minimize the amount of storage space
             Simplify updating procedures
             Ensure that data is accurate
     Done by converting ERD into well-
      structured relational data model
IST 210
           Relational Data Model
          E-R Model helps to define the underlying tables
                                 CUSTOMER
            CUSTOMER             Cust_ID   Cust_Name              Cust_Address
                                 G01       Alpha Trading Co       34 Any St
                                 G02       Beta Company Ltd       42 Main Plaza
                                 G03       Gamma Providers Intl   Unit 7, Delta Ind. Estate

                receive
                                  INVOICE
                                  Inv_No        Cust_ID               Invoice Total
                                     490        G03                       43,564.19
                                     491        G01                          339.78
              INVOICE
                                     492        G03                        7,851.00
IST 210     Converting ERD to tables
   Convert each of the entities (E1 & E2) in to a table. The attributes of the entity
    become attributes of the table.
   Convert each relationship (R1) with a many-to-many cardinality in to a table. The
    primary keys of the entities (E1 & E2) linked by the relationship and attributes of
    the relationship A1 become attributes of the resulting table.
   Do not convert relationships (R1) with one-to-many or one-to-one cardinality in to
    a table.
         For a one-to-one relationship, add the attributes of the relationship (A1) and the primary
          key of either table (T1 or T2) into the other table (T1 or T2).
         For a one-to-many relationship, add the attributes of the relationship (A1) and the
          primary key to the other table.



               Entity 1                                               Entity 2
                 E1                   Relationship1
                                                                        E2
                                            R1
                                                             A1
IST 210
              Fields and Records

         Field: a single column of                                                    Field
                                        Key
          information in a table
             Appears as an attribute
              in the ERD
         Key: a field which            Cust_ID   Cust_Name              Cust_Address
          uniquely identifies each      G01       Alpha Trading Co       34 Any St
          record in a field             G02       Beta Company Ltd       42 Main Plaza
                                        G03       Gamma Providers Intl   Unit 7, Delta Ind. Estate
IST 210
              Fields and Records
                                                               Foreign Key
    Foreign Key: links tables
     by referencing a key in
     another table
                                            Inv_No   Cust_ID        Invoice Total
                                               490   G03                43,564.19
    Tuple: the full set of                    491   G01                   339.78
     information about one                     492   G03                 7,851.00

     occurrence of the entity
             All information included in
              a single row of the table                             Tuple
IST 210
          Review
             The key conversion tasks are:
                 Create entity tables
                 Resolve relationships
                 Resolve multi-valued and composite attributes
                 Insert primary and foreign keys
                 Normalize to Third Normal Form (we will talk
                  about this next time)
                 Integrity constraints
IST 210
          Review
             Relations
                 Just another name for a table
                 Every relation has a unique name
                 Every attribute value is atomic
                 Every row is unique
                 Attributes in tables have unique names
                 The order of the columns is irrelevant
                 The order of the rows is irrelevant

				
DOCUMENT INFO