To Previous Chapter To Table of Contents To Bottom of Page To Next Chapter

Chapter 6 - Database Design Using Entity-Relationship Models

  1. Transformation of Entity-Relationship Models into Relational Database Designs
    • REPRESENTING ENTITIES WITH THE RELATIONAL MODEL
      Represent each entity with a relation
      CUSTOMER entity contains
      CustNumber
      CustName
      Address
      City
      State
      ZipPhoneNumber

      CUSTOMER(CustNumber, CustName, Address, City, State, Zip, ContactName, PhoneNumber)

      • The Role of Normalization
        CUSTOMER(CustomerNumber, Address, Zip, ContactName)

        ZIP-TABLE(Zip, City, State)

        CONTACT(ContactName, PhoneNumber)

        Interrelational constraints:

        Zip in CUSTOMER must exist in ZIP-TABLE
        ContactName in CUSTOMER must=a ContactName in CONTACT

      • Representation of Weak Entities (entity depends for its existence on another entity)
        INVOICE-|-----1:N-----|-LINE-ITEM

        LINE-ITEM(LineNumber, Qty, ItemNumber, Description, Price, ExtPrice)

        LINE-ITEM(InvoiceNumber, LineNumber, Qty, ItemNumber, Description, Price, ExtPrice)

    • REPRESENTING HAS-A RELATIONSHIPS
      • Representing 1-1 Relationships: foreign key - key of one relation is stored in a second relation
        EMPLOYEE------1:1------AUTO

        EMPLOYEE(EmployeeNumber, EmployeeName, Phone, ...)
        AUTO(LicenseNumber, SerialNumber, Color, Make, Model, ..., EmployeeNumber)

      • Questionable 1:1 Relationships (1:1 and mandatory in both directions), ex.
        EMPLOYEE-|-----1:1-----|-JOB-EVALUATION

      • Representing 1-Many Relationships (parent - child relationship
        PROFESSOR-|-----1:N-----0-STUDENT

        DORMITORY-0-----1:N-----0-STUDENT

        CUSTOMER-|-----1:N-----0-APPOINTMENT

        PROFESSOR
        ProfessorNamePhoneDept
        \
         -
          \
           \
            \
             0
              ^
        STUDENT
        StudentNumberStudentNameCampusAddressProfessorName

      • Representing Many-Many Relationships
        STUDENT-0-----M:N-----0-CLASS

        requires an intersection relation

        STUDENT
        StudentNumberStudentName
        CLASS
        ClassNumberClassName
        \
         -
          \
           \
            \
             0
              ^        		       /
             -
            /
           /
          /
         0
        ^
        StudentNumberClassNumber

    • REPRESENTING RECURSIVE RELATIONSHIPS (relationshio among entities of the same class)
      SPONSOR
      PERSON------1:1------PERSON
      REFERRED-BY
      CUSTOMER------1:N------CUSTOMER
      TREATED-BY
      DOCTOR------M:N------DOCTOR

    • REPRESENTING TERNARY AND HIGHER-ORDER RELATIONSHIPS - use combination of binary relationships

    • REPRESENTING IS-A RELATIONSHIPS (SUBTYPES)
      • CLIENT (ClientNumber, ClientName, AmountDue)
      • INDIVIDUAL-CLIENT (ClientNumber, Address, SSN)
      • PARTNERSHIP-CLIENT (ClientNumber, ManagingPartnerName, Address, Tax-ID)
      • CORPORATE-CLIENT (ClientNumber,, ContactPerson, Phone, Tax-ID)

  2. Example Design (see pg. 153)

  3. Trees, Networks, and Bills of Materials
    • TREES
      tree or hierarchy have 1-many relationships where each node except the root has one-and-only-one parent

    • SIMPLE NETWORKS
      simple network have 1-many relationships with more than one parent (ex. STUDENT-MAJOR-ACTIVITY)

    • COMPLEX NETWORKS
      complex network have at least one many-to-many relationship (ex. INVOICE, LINE-ITEM, PARTS, SUPPLIERS)
    • BILLS OF MATERIALS (M-N relationships with recursion) (ex. PARTS that consititute products)

To Previous Chapter To Table of Contents To top of page To Next Chapter