Databases Model the Real World ER Relational Digging Deeper by oneforseven



                                                  Databases Model the Real World
ER & Relational: Digging                    •  Data Model: “language” of concepts to translate
        Deeper                                 real world into data
                                            •  Many models: Relational, E-R, O-O, Network,
                                               Hierarchical, etc.
     R &G - Chapters 2 & 3                  •  Relational
                                                  –  Rows & Columns
    Agile Web Development                         –  Keys & Foreign Keys to link Relations
        with Rails 3rd edition
           Chapters 18-19.3                sid          cid   grade         Students
                                                                            sid    name      login  age gpa
                                          53666   Carnatic101  C
                                                                           53666   Jones jones@cs   18 3.4
                                          53666   Reggae203    B
                                          53650   Topology112  A           53688   Smith smith@eecs 18 3.2
                                          53666   History105   B           53650   Smith smith@math 19 3.8

   Databases for Programmers                      Remember the Inequality!

•  Programmers think about objects
  –  Nested and interleaved
•  Often want to “persist” these things
•  Options
  –  encode opaquely and store
                                            •  If storing indefinitely…use a flexible
  –  translate to a structured form
     •  relational DB, XML file
  –  pros and cons?

   But YUCK!!                                     Object-Relational Mappings

•  How do I “relationalize” my objects?     •  Roughly:
•  Have to write a converter for each             –  Class ~ Entity Set
   class?                                         –  Instance ~ Entity
•  Think about when to save things into           –  Data member ~ Attribute
   the DB?                                        –  Reference ~ Foreign Key

•  Good news:
  –  Can all be automated
  –  With varying amounts of trouble


   Details, details                                             Ruby on Rails

•  We have to map this down to tables                        •  Ruby: an OO scripting language
•  Which table holds which class of object?                    –  and a pretty nice one, too
•  What about relationships?                                 •  Rails: a framework for web apps
•  Solution #1: Declarative Configuration                      –  “convention over configuration”
                                                                  •  great for standard web-app stuff!
  –  Write a description file (often in XML)
        •  E.g. Enterprise Java Beans (EJBs)
                                                               –  allows overriding as needed

•  Solution #2: Convention                                   •  Rails “Models”
  –  Agree to use some conventions                             –  Represent the data and business rules in
        •  E.g. Rails
                                                                  an application
                                                               –  Very ER-like

   Rails and ER                                                 Rails “Models” and “Associations”

•  Models                                                    app/models/department.rb 
  –  Employees                                               class Department < ActiveRecord::Base 
  –  Departments                                               has_many :employees # 1‐to‐n 
  –  Works_In?
        •  Depends on constraints

                            since                            app/models/employee.rb 
           name                             dname
  ssn               lot               did           budget   class Citizen < ActiveRecord::Base 
                                                               belongs_to :state # n‐to‐1 
        Employees          Works_In         Departments

   Rails “Models” and “Associations”                            Rails “Models” and “Associations”

app/models/engine.rb                                         app/models/parent.rb 
class Engine < ActiveRecord::Base                            Class Parent < ActiveRecord::Base 
  belongs_to :vehicle # 1‐to‐0 or 1‐to‐1                       # many‐to‐many 
end                                                            has_and_belongs_to_many :children 
Class Vehicle < ActiveRecord::Base                           app/models/child.rb 
  has_one :engine,   # 1‐to‐1                                Class Child < ActiveRecord::Base 
          :conditions => "id is not null”                      # many‐to‐many, full participation 
end                                                            has_and_belongs_to_many :parents, 
                                                                       :conditions => "id is not null”  


             A more complex example                                                                                                          Rails “Through Associations”

      •  Models                                                                          since                                           app/models/hiring.rb 
                                                                name                                         dname
           –  Employees                                ssn                   lot                    did                  budget          Class Hiring < ActiveRecord::Base 
           –  Departments                                                                                                                  belongs_to :employee # one‐to‐one 
                                                             Employees                Works_In               Departments
           –  Works_In?                                                                                                                  end 
                   •  Remove the key constraint
                   •  Where does “since” live?? Needs a model!                                                                           app/models/employee.rb 
                                                                                                                                         Class Employee < ActiveRecord::Base 
                                                                                                                                           has_many :hirings 
                           ssn                   lot                    did                    budget                                      has_many :departments, 
                                 Employees                   Hirings               Departments                                                      :through => hirings 

             Further Reading                                                                                                                 Aggregation                         ssn               lot


      •  Chapter 18 (through 18.3) in Agile Web
         Development with Rails (2nd edition)

                                                                                                                                                                      started_on            since
                                                                                                                                                                pid               pbudget          did              budget

                                                                                                                                                                      Projects           Sponsors          Departments

                                                                                                                                         Allows relationships with relationship sets.

             Aggregation vs. Ternary                                                                                                          Conceptual Design Using the ER Model
                                                                                         ssn               lot

                                                                                                                                      •  ER modeling can get tricky!
                                                                                                Monitors         until                •  Design choices:
                                                                                                                                          –  Entity or attribute?
                 ssn               lot
                                                                               started_on            since
                       Employees                                       pid                pbudget          did               budget       –  Entity or relationship?
                       Monitors          until
                                                                              Projects            Sponsors         Departments
                                                                                                                                          –  Relationships: Binary or ternary? Aggregation?
                                         since                                                                                        •  ER Model goals and limitations:
                                                                                                                                          –  Lots of semantics can (and should) be captured.
      Projects                             Departments
                                                                                                                                          –  Some constraints cannot be captured in ER.
                 pbudget             did
                                                                                                                                           •  We’ll refine things in our logical (relational) design


          Entity vs. Attribute                                                                          Entity vs. Attribute (Cont.)
                                                                                                                                                     from        to
     •  “Address”:                                                                                                          ssn
                                                                                                                                               lot                      did
                                                                                               •  Works_In2: employee                                                                  budget
         –  attribute of Employees?                                                               cannot work in a
                                                                                                                              Employees                Works_In2              Departments
         –  Entity of its own?                                                                    department for >1
     •  It depends! Semantics and usage.
         –  Several addresses per employee?                                                    •  Like multiple addresses
             •  must be an entity                                                                 per employee!
             •  atomic attribute types (no set-valued attributes!)                                                                      name                                    dname
                                                                                                                                  ssn                lot                 did            budget
         –  Care about structure? (city, street, etc.)
                                                                                                                                                            Works_In3           Departments
             •  must be an entity!                                                                                                  Employees

             •  atomic attribute types (no tuple-valued attributes!)
                                                                                                                                               from         Duration           to

          Entity vs. Relationship                                                                          Now you try it

•  Separate                                             since
                                                                                                 Try this at home - Courses database:
   discretionary budget                name                                   dname
                                                                                                 •  Courses, Students, Teachers
                               ssn                lot                 did             budget
   (dbudget) for each
   dept.                             Employees             Manages2           Departments
                                                                                                 •  Courses have ids, titles, credits, …
•  What if manager’s                                                                             •  Courses have multiple sections that have time/rm
   dbudget covers all                   name
                                                                                                    and exactly one teacher
   managed depts                 ssn               lot
                                                                                                 •  Must track students’ course schedules and
   –  Could repeat value
   –  But redundancy =
                                                                        did           budget        transcripts including grades, semester taken, etc.
      problems                                                                Departments        •  Must track which classes a professor has taught
•  Better design:                        is_manager             managed_by        since          •  Database should work over multiple semesters
                                              apptnum       Mgr_Appts

          E-R Diagram as Wallpaper                                                                         Converting ER to Relational

     •  Very common for them to be wall-sized                                                        •  Fairly analogous structure
                                                                                                     •  But many simple concepts in ER are
                                                                                                        subtle to specify in relations


             Logical DB Design: ER to Relational                                                              Relationship Sets to Tables
                                                        ssn                name        lot                                                     CREATE TABLE Works_In(
    •  Entity sets to tables.                                                                       •  In translating a many-to- ssn CHAR(1),
                                                        123-22-3666 Attishoo           48
                                                                                                       many relationship set to   did INTEGER,
                                                        231-31-5368 Smiley             22              a relation, attributes of  since DATE,
                                                                                                       the relation must         PRIMARY KEY (ssn, did),
                                                        131-24-3650 Smethurst 35
                                                                                                                                 FOREIGN KEY (ssn)
                                                                                                                                         REFERENCES Employees,
                                                                                                       1) Keys for each               FOREIGN KEY (did)
                                                                                                         participating entity set      REFERENCES Departments)
                                  CREATE TABLE Employees                                                 (as foreign keys). This set
                                    (ssn CHAR(11),                                                       of attributes forms a         ssn         did since
                                                                                                         superkey for the relation.
                                     name CHAR(20),                                                                                    123-22-3666 51 1/1/91
                                                                                                       2) All descriptive attributes.
                                     lot INTEGER,                                                                                                123-22-3666 56         3/3/93
                                     PRIMARY KEY (ssn))                                                                                          231-31-5368 51         2/2/92

          Review: Key Constraints                                                                          Translating ER with Key Constraints
                                                                                                                         name                          dname
•  Each dept has at                                                                                                ssn          lot              did           budget
   most one manager,                    name                                       dname
   according to the                                                                        budget
                                                                                                                     Employees        Manages      Departments
                                  ssn             lot                        did
   key constraint on
                                                                                                    •  Since each department has a unique manager, we
                                     Employees                  Manages        Departments             could instead combine Manages and Departments.
                                                                                                    CREATE TABLE Manages(    CREATE TABLE Dept_Mgr(
                                                                                                     ssn CHAR(11),            did INTEGER,
                                                                                                     did INTEGER,             dname CHAR(20),
                                                                                                     since DATE,          Vs. budget REAL,
                                                                         Translation to              PRIMARY KEY (did),       ssn CHAR(11),
                                                                         relational model?           FOREIGN KEY (ssn)        since DATE,
                                                                                                    REFERENCES Employees,     PRIMARY KEY (did),
    1-to-1      1-to Many         Many-to-1       Many-to-Many                                         FOREIGN KEY (did)      FOREIGN KEY (ssn)
                                                                                                    REFERENCES Departments)    REFERENCES Employees)

        Review: Participation Constraints                                                                    Participation Constraints in SQL
•  Does every department have a manager?
                                                                                                    •  We can capture participation constraints involving one entity
   –  If so, this is a participation constraint: the participation of
                                                                                                       set in a binary relationship, but little else (without resorting to
      Departments in Manages is said to be total (vs. partial).
                                                                                                       CHECK constraints which we’ll learn later).
       •  Every did value in Departments table must appear in a row of the
          Manages table (with a non-null ssn value!)
                                                                                                        CREATE TABLE Dept_Mgr(
                                                                                                           did INTEGER,
                name                                                dname                                  dname CHAR(20),
         ssn                lot                           did                 budget                       budget REAL,
                                                                                                           ssn CHAR(11) NOT NULL,
               Employees                Manages
                                                                                                           since DATE,
                                                                                                           PRIMARY KEY (did),
                                                                                                           FOREIGN KEY (ssn) REFERENCES
                                          since                                                               ON DELETE NO ACTION)


            Review: Weak Entities                                                    Translating Weak Entity Sets

                                                                                 •  Weak entity set and identifying relationship
•  A weak entity can be identified uniquely only by considering
   the primary key of another (owner) entity.                                       set are translated into a single table.
   –  Owner entity set and weak entity set must participate in a one-to-many        –  When the owner entity is deleted, all owned weak
      relationship set (1 owner, many weak entities).                                  entities must also be deleted.
   –  Weak entity set must have total participation in this identifying
      relationship set.                                                         CREATE TABLE Dep_Policy (
                                                                                   pname CHAR(20),
                                                                                   age INTEGER,
                name                                                               cost REAL,
      ssn                  lot
                                         cost       pname          age             ssn CHAR(11) NOT NULL,
                                                                                   PRIMARY KEY (pname, ssn),
                                                                                   FOREIGN KEY (ssn) REFERENCES Employees,
             Employees                  Policy            Dependents
                                                                                      ON DELETE CASCADE)

        Summary of Conceptual Design                                               Summary of ER (Cont.)
 •  Conceptual design follows requirements analysis,                           •  Several kinds of integrity constraints:
     –  Yields a high-level description of data to be stored                       –  key constraints
 •  ER model popular for conceptual design                                         –  participation constraints
     –  Constructs are expressive, close to the way people think
        about their applications.
                                                                               •  Some foreign key constraints are also implicit in
     –  Note: There are many variations on ER model
                                                                                  the definition of a relationship set.
         •  Both graphically and conceptually                                  •  Many other constraints (notably, functional
 •  Basic constructs: entities, relationships, and                                dependencies) cannot be expressed.
    attributes (of entities and relationships).                                •  Constraints play an important role in determining
 •  Some additional constructs: weak entities, ISA                                the best database design for an enterprise.
    hierarchies (see text if you’re curious), and

        Summary of ER (Cont.)
  •  ER design is subjective. There are often many
     ways to model a given scenario!
  •  Analyzing alternatives can be tricky, especially for
     a large enterprise. Common choices include:
     –  Entity vs. attribute, entity vs. relationship, binary or n-
        ary relationship, whether or not to use ISA hierarchies,
  •  Ensuring good database design: resulting
     relational schema should be analyzed and refined
     –  Functional Dependency information and normalization
        techniques are especially useful.


To top