Invoice Rental

Document Sample
Invoice Rental Powered By Docstoc
					       CS411
  Database Systems

02: The Entity-Relationship Model

       Kazuhiro Minami
Steps in building a DB application

 Pick application
                           Conceptual design
     domain


                                         What data do I
                                          need for my
                                           application
                     How can I              domain?
                    describe that
                        data?
         Steps in building a DB
              application
      Pick application
                         Conceptual design
          domain




                               ER diagram
     SQL &
Java/C++/etc +
 user interface


       Implement         Convert ER diagram
   application code &    to the data model of
     user interface      your DBMS product
       The ER model is very simple
                                                 name
          name     category
price
                              makes     Company
         Product

                                                stockprice

            buys                      employs

                          Person



         address               name      ssn
  •4
Entity
  – real-world object distinguishable from other
    objects
  – described by its attributes
Attribute
  – Has an atomic domain: string, integers, date,
    …
Entity set: all have the same set of attributes

  price      name   category
                                 Company           name



                                   
          Product
                       boardOfDirectors     stockprice
               Relationships
If A, B are sets, then a relation R is a subset of
   A x B.
                                     1             a
A = {1, 2, 3} B = {a, b, c, d}   A   2             b
    R = {(1,a), (1,c), (3,b)}        3
                                                   c
                                               B   d
makes is a subset of Product x Company:

                     makes               Company
     Product
        name      category
                                                name

price
                             makes     Company
        Product

                                               stockprice

           buys                      employs

                         Person



        address               name      ssn
    Exercises 4.1.1 and 4.1.2
Design a database for a bank, including information about
customers and their accounts. Information about a customer
includes their name, address, phone, and Social Security
number. Accounts have numbers, types (e.g., saving, checking)
and balances. Also record the customer(s) who own an account.

1) Draw the E/R diagram for this database.
2) Change your diagram so an account can have only one
   customer.
3) Further change your diagram so a customer can have only
   one account
4) Change your original diagram in (1) so that a customer have
   a set of addresses.
Constraint = assertion about the
 DB that must always be true
Key: social security number uniquely identifies a person.

Single-value constraint: a person can have only one father.

Referential integrity: if a person works for a company,
                       the company must also be in the DB.

Domain constraint: peoples’ ages are between 0 and 150.

General constraint: all others
                    (at most 45 students in this room)
 Constraints are very important
• Help us to come up
  with efficient
  storage, query
  processing, etc.
• Help us keep
  garbage out of the
  DB
  – Garbage in,
    garbage out!
   Referential integrity: if you refer
   to something, it actually exists
• More or less built into the
  ER model
• But NOT automatic in other
  models
   – Examples:
      • Erbana, IL 61801
      • Brittany Speers
• The DB equivalent of a
  dangling pointer

Garbage in, garbage out!
Underline the key for each entity set
                               name     category

                                            multi-attribute keys
                      price                      are okay!

                              Product
Multiple “candidate
 keys”? Pick just
one to be the key.                                 Is this a good
                              Person
                                                        key?


          address               name                  ssn
 Sometimes your entity might not
      seem to have a key
Weak entity set: some or all of its key attributes
 come from other classes to which it is related.




        Team              affiliation     )   University


sport           record                        name
 We can show the cardinality of
        a relationship
               E         F
               1         a
               2         b
               3         c
one-one                  d


               1          a
               2          b
many-one       3          c
                          d



                   1         a
                   2         b
                   3         c
many-many                    d




                       makes     Company
     Product
 How do we model an n-way
       relationship?
    Product


                     Purchase                  Store




                      Person

Can still model as a mathematical set (how?)
  What do arrows mean in n-way
         relationships?
                        Invoice
      VideoStore

                    Rental        Movie


                   Person
If I know the store, person, and invoice, then
   there is only one possible movie.
 “VideoStore, Invoice, and Person determines
 Movie”
What if there are several arrows?
                     Invoice
   VideoStore

                 Rental        Movie


                Person



store, person, invoice determines movie;
store, invoice, movie determines person
How do I say “invoice determines
            store”?
No good way; best approximation:
                          Invoice
       VideoStore

                    Rental          Movie


                    Person


Why is this incomplete?
  What if we need an entity set
   twice in one relationship?
                                        the “role”
Product


                    Purchase                   Store


            salesperson   buyer


          Person               Person
  What if we need an entity set
   twice in one relationship?
Product


                    Purchase             Store

          salesperson            buyer



                        Person
Some versions of the ER model
allow attributes on relationships
                       date
Product


            Purchase          Store




             Person
              You can “upgrade” a
           relationship to be an entity
                       set
   date
             ProductOf      Product



Purchase
              StoreOf        Store




              BuyerOf        Person
  We can represent subclasses

      name              Customer Contact            email

           address
                        isa
                                     isa
               Institutional
                                              Individual
Revenues
                     isa
                               isa
     Commercial                            Educational

                                                     Age Group
                Subclasses

• Subclass = special case = fewer entities =
  more properties.
• Example: Ales are a kind of beer.
  – Not every beer is an ale, but some are.
  – Let us suppose that in addition to all the
    properties (attributes and relationships) of
    beers, ales also have the attribute color.
        Example

name    Beers     manufacturer



         isa


color    Ales
ER subclasses are different from
  object oriented subclasses

 • In the object-oriented world, objects are in
   one class only.
   – Subclasses inherit properties from
     superclasses.
 • In contrast, E/R entities have components
   in all subclasses to which they belong.
   – Matters when we convert to relations.
        Example

name    Beers   manf


                  Pete’s Ale
         isa


color    Ales
ER Design Principle #1: Model
    your domain faithfully

Product       Purchase   Person




Country      President   Person




Instructor    Teaches    Course
 Principle #2: Avoid redundancy

• Don’t say the same thing in two different
  ways.
• Redundancy wastes space and (more
  importantly) encourages inconsistency
  – The two instances of the same fact may
    become inconsistent if we change one and
    forget to change the other, related version.
                       Good

    name                    name        addr


    Beers        ManufBy         Manufacturers




This design gives the address of each
manufacturer exactly once.
                        Bad
    name                      name        addr


    Beers         ManufBy         Manufacturers


    manf


This design states the manufacturer of a beer
twice: as an attribute and as a related entity.
                        Bad
      name       manf      manfAddr


                Beers




This design repeats the manufacturer’s address
once for each beer; loses the address if there are
temporarily no beers for a manufacturer.
                     Good
    name                    name        addr


    Beers         ManfBy         Manufacturers



Manufacturers deserves to be an entity set
because of the nonkey attribute addr.

Beers deserves to be an entity set because it is
the “many” of the many-one relationship ManfBy.
   Principle #3: Don’t overuse
            entity sets
An entity set should satisfy at least one of the
   following conditions:
  – It is more than the name of something; it
    has at least one non-key attribute.
                or
  – It is the “many” in a many-one or many-
    many relationship.
                     Good
          name          manufacturer


                Beers




No need to make the manufacturer an entity set,
because we only remember its name.
                       Bad
    name                          name


    Beers         ManfBy          Manufacturer




Since the manufacturer is nothing but a name,
and is not at the “many” end of any relationship,
it should not be an entity set.
    Principle #4: Don’t Overuse
         Weak Entity Sets
• Beginning database designers often make
  most entity sets weak, supported by all other
  entity sets to which they are linked.
• Instead, we create unique IDs for entity sets.
  – Social-security numbers, driver’s license
    numbers, automobile VINs, …
• Only use weak entity sets when necessary.
  – Example: unique player numbers across all
    football teams in the world.

				
DOCUMENT INFO
Description: Invoice Rental document sample