CPS 430/542 Lecture notes: From ODL Designs to Relational Designs and the Object-relational model



Coverage: [FCDB] §§4.4-4.5 (pp. 155-173)


Overview (and recap)


Converting atomic attributes

    class Movie (extent Movies) {
       attribute string title;
       attribute integer year;
       attribute integer length;
       attribute enum Film {color,blackAndWhite} filmType;
    };
    
    ⇒
    
    Movies(title, year, length, filmType)
    


Issues

  • no keys in ODL
  • non-atomic attributes
  • methods
(most result from a richer type system)


Converting non-atomic attributes

    class Star (extent Stars) {
       attribute string name;
       attribute Struct Addr {string street, string city} address;
    };
    
    (simply flatten) ⇒
    
    Stars(name, street, city)
    


Converting set-valued attributes

    class Star (extent Stars) {
       attribute string name;
       attribute Set<Struct Addr {string street, string city}> address;
    };
    
    (use multiple tuples) ⇒
    
    Stars(name, street, city)
    
    however this conversion approach can lead to unnormalized relations, e.g.,
    class Star (extent Stars) {
       attribute string name;
       attribute Set<Struct Addr {string street, string city}> address;
       attribute Date birthdate;
    };
    
    ⇒
    
    Stars(name, street, city, birthdate)
    
    key: ???

    problems: ???

    one set-valued attribute and a single-valued attribute leads to _____???_____

    two set-valued attributes leads to _____???_____

    solution approaches:
    • normalize
    • introduce many-many relationships


Converting other type constructors

  • bags
  • lists
  • arrays
  • dictionaries


What about relationships?

    class Movie (extent Movies key title year) {
       attribute string title;
       attribute integer year;
       attribute integer length;
       attribute enum Film {color,blackAndWhite} filmType;
    
       relationship Set<Star> stars inverse Star::starredIn;
       relationship Studio ownedBy inverse Studio::owns;
    };
    
    class Studio (extent Studios key name) {
       attribute string name;
       attribute string address;
       relationship Set<Movie> owns inverse Movie::ownedBy;
    };
    
    ⇒
    
    Studios(name, address)
            ----
    StudioOf(title, year, studioName)
             -----  ----
    Movies(title, year, length, filmType)
           -----  ----
    
    // but remember we can combine StudioOf and Movies, why?
    
    // final relational database schema
    Studio(name, address)
           ----
    Movies(title, year, length, filmType, studioName)
           -----  ----
    
    What about stars relationship?
    Movies(title, year, length, filmType, studioName, starName)
    
    What if there is no key?
    Stars(cert#, name, street, city, birthdate)
    
    StarsIn(title, year, cert#)
    


The Object-Relational Model

extends relational model with
  • structured types for attributes (set of structs; essentially a ?el?ti??)
  • methods
  • identifiers for tuples
  • references


Nested Relations

    Stars(name, address(street, city), birthdate, movies(title, year, length))
    
    can lead to duplication galore!

    the nested relation analog of not being in BCNF!


References

    Movies(title, year, length)
    Stars(name, address(street, city), birthdate, movies({*Movies}))
    


Object-oriented model vs. object-relational model

  • object's value is a struct; a tuple is likewise a struct
  • methods: see SQL-99 standard which supports object-relational idea in the same manner as ODL
  • both have rich type systems
  • references and OIDs
    • pure OO: OIDs are hidden from the user
    • object-relational model allows references to be part of the schema
  • backwards compatibility


Converting ODL designs into object-relation designs

  • most non-atomic types in ODL have analogs in the object-relational model (e.g., SQL-99)
  • for those non-atomic types with no analog, use techniques presented here for converting from ODL designs to relational designs


References

    [FCDB] J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.

Return Home