How to Translate ER diagram into Relational Schema by g4039193

VIEWS: 16,039 PAGES: 42

									How to Translate ER diagram
into Relational Schema
   We will follow the following multi-step process
     Strong Entity Types

     Weak Entity Types

     Multivalued attributes

     Binary Relationships

        M:N

        1:N

        1:1

     n-ary relationships

                       Chapter 9, part 1
Strong Entity Types
 1. For each regular (strong) entity type in
  the ER schema, create a relation that
  includes all the simple attributes.
 2. Include only the simple component
  attributes of a composite attribute.
 3.Choose one of the key attributes of entity
  type as primary key for table.

                   Chapter 9, part 1
EMPLOYEE

SSN   Bdate   Sex   Address Salary            Fname    Minit Lname



                                                 Simple components
                          Chapter 9, part 1      Of composite attribute
 PROJECT

PNumber PName Location

       Only one of key attributes chosen as a primary key
                              Chapter 9, part 1
  DEPARTMENT

DNumber    DName

   We will see later how we are treating multivalued
   Attribute Locations
                   Chapter 9, part 1
      4. If the chosen key is composite, the set of
       simple attributes that form it will together
       form the primary key of the table.
  Example:

AreaCode        7digitNumber

           Phone#
                             CUSTOMER
        CUSTOMER
                         AreaCode 7digitNumber   Name


Name
                          Chapter 9, part 1
Weak Entity Types
 1. For each weak entity type, create a table
 2. Include all simple attributes (or simple
  components of composite attributes) of the
  entity type as attributes of the table (this is
  exactly what we did for strong entity types)




                    Chapter 9, part 1
DEPENDENT

Name   Sex   BirthDate              Relationship




                Chapter 9, part 1
 2. In addition, include as foreign key
  attributes the primary key attribute(s) of the
  table that correspond to the owner entity
  type(s)
 3. The primary key of the table is the
  combination of the primary key(s) of the
  owner(s) and the partial key of the weak
  entity type
                    Chapter 9, part 1
   Final table DEPENDENT…


DEPENDENT

Dependent_Name BirthDate     Relationship   ESSN   Sex




                             Foreign key of EMPLOYEE

                       Chapter 9, part 1
Multivalued Attributes
 1. For each multivalued attribute A, create a
  new table
 2. This table will include
    Attribute corresponding to A,

    As a foreign key, the primary key
     attribute of the table that represents the
     entity type (or relationship type)e that has
     A as an attribute.

                    Chapter 9, part 1
DEPT_LOCATIONS

 DNUMBER      DLocation


        Foreign key pointing to Dnumber which is
        Primary key in DEPARTMENT
                 Chapter 9, part 1
   3.The primary key of the table is the
    combination of all its attributes

    DEPT_LOCATIONS

     DNUMBER       DLocation




                     Chapter 9, part 1
 4. If the multivalued attribute is
  composite, we include its simple
  components
 5. In some cases when a multivalued
  attribute is composite, only some of the
  component attributes are required in the key
  of the table

                   Chapter 9, part 1
                                     VIN             Color

                                             Car

                       Name
                                            DRIVER


DRIVER                 LicenseNumber

LicenseNumber   Name

CAR

LicenseNumber   VIN        Color
                        Chapter 9, part 1
M:N Relationship Types
   1. For each binary M:N relationship type, create a
    new table
   2. Include as foreign key attributes in this table the
    primary keys of the tables that represent the
    participating entity types. Their combination will
    form the primary key
   3. Also include any simple attributes (or simple
    components of composite attributes) of the M:N
    relationship type
                         Chapter 9, part 1
 SSN                                        Regular     Overtime

        EMPLOYEE                               Hours
                    M
                             WorksOn
                                                 N


                                             PROJECT



                                                      PName
WORKS_ON
  SSN       Pname       HRegular HOvertime
                        Chapter 9, part 1
1:N Relationship Types
 1. For each regular binary 1:N relationship
  type R, identify the table that represents the
  participating entity type at the N-side of the
  relationship type
 2. Include as foreign key in this table the
  primary key of the table that represents the
  other entity type participating in the
  relationship
                    Chapter 9, part 1
          SSN

                       EMPLOYEE
                                                N
                                                           WorksFor

                                                               1                                 DName
Table corresponding to the other
entity type participating in the relationship          DEPARTMENT
       DEPARTMENT
                                                                 Foreign key to the table representing the other
    DNumber               DName                                  entity type participating in the relationship

         EMPLOYEE

    SSN Bdate           Sex        Address        Salary     Fname         Minit       Lname       DNO

    Table representing entity type on N side of      Chapter 9, part 1
    The relationship
   3. Include any simple attributes (or simple
    components of composite attributes) of the
    1:N relationship type as attributes of the
    table




                     Chapter 9, part 1
                                            TeacherName
             TEACHER

                 1                                    ClassName
                          Teaches
                                     N          CLASS
            NeedsProjector


CLASS
ClassName      TeacherName               NeedsProjector


        Foreign key to TEACHER               Relationship attribute
                        Chapter 9, part 1
1:N Relationship Types -
Alternative Representation
   If the participation at N side is partial and
    number of participating entities is small
      Represent 1:N relationship using a new

       table to avoid many null values in foreign
       keys




                     Chapter 9, part 1
Name                                           SSN
             CITIZEN

                 N

               Congressperson
                                                      StateName
                                           1
       ElectionDate                                  STATE




                       Chapter 9, part 1
    Using foreign key in CITIZEN
    table           Schema
CITIZEN

SSN               Name                  StateName                    ElectionDate

CITIZEN                                                Instance
            SSN                  Name                    StateName                ElectionDate
111222333            Pavel Mun
321321123            Naomie Cenet
555444666            Dragoljub Pokrajac
666555444            Marwan Rosamny
999888777            Michael Castle             Delaware                11/1993
645645645            Chaka Fattah               Pennsylvania            11/1994
664466445            Kevin Markus




                                          Chapter 9, part 1
Using a new Table CONGRESSPERSON
                                                           Schema
 CONGRESSPERSON
      SSN           StateName                          ElectionDate

                                                          Instance
 CONGRESSPERSON
              SSN           StateName                     ElectionDate
  999888777         Delaware                    11/1993
  645645645         Pennsylvania                11/1994




       Obviously, we save a lot of space

                                        Chapter 9, part 1
 1:1 Relationship Types
    The representation of these relationship
     types depends on the participation of the
     entity types
                ENTITY                       ENTITY
                TYPE 1                       TYPE 2

Case 1         TOTAL                         PARTIAL
               PARTIAL                       TOTAL
Case 2         TOTAL                         TOTAL
Case 3         PARTIAL                       PARTIAL

                         Chapter 9, part 1
    Case 1: One entity type has total, and
    another has partial participation
 1. Identify tables S and T that correspond to the
  entity types participating in the considered
  relationship
 2. Assume S is the table with total participation.
  Include as foreign key in S the primary key of T.
 3. Include all the simple attributes (or simple
  components of composite attributes) of the 1:1
  relationship type as attributes of S.

                          Chapter 9, part 1
        Name                                                       SSN
                           EMPLOYEE

                                  (0,1)

                                  MANAGES
                                                                                   DNO
                                                                 (1,1)
                   StartDate                                             DEPARTMENT
Each department has manager.DEPARTMENT has total participation
Not each employee is manager.EMPLOYEE has partial participation

      DEPARTMENT                                                                  DName

   DNumber             DName          MGRSSN              StartDate
                                                                           Relationship attribute


                                             Chapter 9, part 1     Foreign key to employee
       Case 2: Both entity types have
       total participation
   First possibility-the approach similar to case 1
   1. Identify tables S and T that correspond to the entity
    types participating in the relationship
   2. Choose the table which primary key has larger size
    (say S) Include as foreign key in S the primary key of
    T.
   3. Include all the simple attributes (or simple
    components of composite attributes) of the 1:1
    relationship type as attributes of S.

                              Chapter 9, part 1
  BuildingCode                                          RoomCode
                    ROOM

                           (1,1)

                          ASSIGNED TO
                                                                          SSN
                                                        (1,1)
ROOM                                                            PROFESSOR
BuildingCode   RoomCode       SSN

PROFESSOR
                                                                          Name
    SSN         Name
                                            Note: SSN contains 9 bytes, BuldingCode+
                                            RoomCode contains 25 bytes.
                                            Hence, we chose SSN to replicate
                                    Chapter 9, part 1
Case 2: Second Possibility
 1. Merge two entity types and the
  relationship into a single table
 2. Retain as a primary key the smaller of the
  primary keys of merged entity types




                   Chapter 9, part 1
    BuildingCode                                                         RoomCode
                                ROOM

                                         (1,1)

                                     ASSIGNED TO
                                                                                        SSN
                                                                         (1,1)
ROOM_ PROFESSOR                                                                    PROFESSOR
 BuildingCode          RoomCode                SSN                  Name

                                                                                        Name

In this case, we retain only one of primary keys (usually, smaller, SSN in this case)


                                                     Chapter 9, part 1
      Case 3: Both entity types have
      partial participation
   Here we have two sub-cases:
     Case 3a: Only a few entities from each type

      actually participate in the relationship
     Case 3b: The participation is substantial for at

      least entities from one entity type



                          Chapter 9, part 1
Case 3a: Only a few entities from
each type actually participate
       To avoid a lot of null values, we will apply the
        approach used for M:N relationship type
          1. For each binary 1:1 relationship type from
           Case 3a, create a new table
          2. Include as foreign key attributes in this table
           the primary keys of the tables that represent the
           participating entity types. Their combination
           will form the primary key
          3. Also include any simple attributes (or simple
           components of composite attributes) of the
           considered relationship type
                             Chapter 9, part 1
Extra-Credit
   Give example for this case…




                    Chapter 9, part 1
Case 3b: Significant fraction from one
entity type participates
       This approach is similar to the approach in Case 1
          1. Identify tables S and T that correspond to the
           entity types participating in the relationship
          2. Choose the table corresponding to entities
           which participate more frequently (say S)
           Include as foreign key in S the primary key of
           T.
          3. Include all the simple attributes (or simple
           components of composite attributes) of the 1:1
           relationship type as attributes of S.
                            Chapter 9, part 1
 Manufacturer                                             ServerCode
                       UNIX_SERVER

                             (0,1)

                          ASSIGNED TO
                                                                        ID
                                                           (0,1)
One server can be assigned to one professor.                       PROFESSOR
Professor can have only one server assigned.

Typically, almost all servers are assigned and there is
                                                                       Name
smaller number of servers than the number of professors

(Out of 5 unix servers, 4 are assigned
There are 20 professors, of which 4 are assigned unix servers)
                                      Chapter 9, part 1
                               UNIX_SERVER                                PROFESSOR
                          Server   Manufactu             ID        Name           ID
CORRECT                    Code      rer
REPRESENTATION           001       Sun               1
                                                              S. Chang       1
                                                              S. Bang        2
OF THIS                  002       IBM               2
                                                              K.C. Wang      3
                         003       HP                3
RELATIONSHIP             004       Dell              4
                                                              H. Hong        4
                                                              P. Chack       5
                         005       NEC
                                                              L. Mack        6
                                                              W.W. Web       7
                                                              L.L. Lang      8
                                                              D.D. Dream     9
Schema                         Instance                       F.F. Fack      10
                                                              H.G. Wels      11
                                                              M.M. Tivi      12
 UNIX_SERVER                                                  B.B. King      13
                                                              C. Madonna     14
  ServerCode   Manufacturer         ID                        B. Spears      15
                                                              B.H.Grr        16
 PROFESSOR                                                    N.S. Blak      17

                                                              H.R. Hrrr      18
     ID           Name
                                                              Z.Z. Top       19
                                                              B.C. Dop       20
                                     Chapter 9, part 1
                                 UNIX_SERVER                            PROFESSOR
                            Server   Manufactu                  Name         ID    Server
INCORRECT                    Code      rer                                          Code
REPRESENTATION             001       Sun
                                                           S. Chang     1         001
OF THIS                    002       IBM
                                                           S. Bang      2         002
                           003       HP
RELATIONSHIP               004       Dell
                                                           K.C. Wang    3         003
                                                           H. Hong      4         004
                           005       NEC
                                                           P. Chack     5
Problem: a lot of null                                     L. Mack      6
Values in foreign key field                                W.W. Web     7
                                                           L.L. Lang    8
 Schema                          Instance                  D.D. Dream   9
                                                           F.F. Fack    10
                                                           H.G. Wels    11
   UNIX_SERVER                                             M.M. Tivi    12
                                                           B.B. King    13
    ServerCode   Manufacturer
                                                           C. Madonna   14
                                                           B. Spears    15
   PROFESSOR                                               B.H.Grr      16
                                                           N.S. Blak    17
        ID          Name          ServerCode
                                                           H.R. Hrrr    18
                                                           Z.Z. Top     19
                                       Chapter 9, part 1
                                                           B.C. Dop     20
        Representation of n-ary
        Relationship Types
   Approach similar as for M:N binary relationship types
     1. For each n-ary relationship type, where n > 2, create
      a new table
     2. Include as foreign key attributes in this table the
      primary keys of the tables that represent the
      participating entity types. Their combination will form
      the primary key.
            However, if the cardinality constraints on any of the participating entity
             types is 1, then the primary key of S should not include the foreign key
             corresponding to that entity type
       3. Include any simple attributes (or simple components
        of composite attributes) of the considered relationship
        type                   Chapter 9, part 1
    Example: Ternary Relationship

              PART                                       SUPPLIER
                         (0,1)
   PartID                                              (0,M)    SNO
                                 Supply

                    Quantity                            Date
                                       (0,P)

Tuple of supplier and project       PROJECT
Uniquely determines ID of delivered
part                                                   PNO
                                   Chapter 9, part 1
     Table that Represents the Ternary
     Relationship
SUPPLY

   SNO               PNO                   PartID                Quantity              Date




                    Foreign keys to
                    Participating tables

                                                                   Relationship attributee
                                                                   become attributes of the
                                                                   table
   Only SNO and PNO
   Are members of primary key since
   The cardinality of PART is 1
                                             Chapter 9, part 1

								
To top