Docstoc

Constraints

Document Sample
Constraints Powered By Docstoc
					From E/R Diagrams to Relations
           The Relational Data Model
Database
                    Relational               Physical
Model
                    Schema                   storage
(E/R)




                                           Complex
Diagrams (E/R)   Tables:                   file organization
                   row names: attributes   and index
                   rows: tuples            structures.
                         Terminology
Attribute names


         Title          Year   Length         FilmType


         Star Wars      1997     124           color

         Mighty Ducks   1991     104            color

         Wayne’s World 1992       95           color

         ...            ...      ...            ...

tuples                                  components of tuples
                     More Terminology

Every attribute has an atomic type.

Relation Schema: relation name + attribute names + attribute types

Relation instance: a set of tuples. Only one copy of any tuple!

Database Schema: a set of relation schemas.

Database instance: a relation instance for every relation in the schema.
From E/R Diagrams to Relations
• Entity sets become relations with the
  same set of attributes.

• Relationships become relations whose
  attributes are only:
  – The keys of the connected entity sets.
  – Attributes of the relationship itself.
Example: Entity Sets to Relations
   title            year    Relation schema:
                            Movies(title, year, length, filmtype )
           Movies




  length        filmType
                             A relation instance:

title                year        length         filmtype
Star Wars            1977        124            Color
Might Ducks          1991        104            Color
Wayne’s World        1992        95             Color
     E/R Relationships to Relations
         (Renaming Attributes)
• We rename attributes when:
  one entity set is involved several times in a relationship
  - or -
  the same attribute name appears in the keys of different
  entity sets participating in a relationship,
  - or -
  we like to avoid confusion and to be clearer in meaning
          Example (with Renaming)
      name      addr                 name        manf

          Drinkers       Likes          Beers
husband
          1     2
                          Favorite
          Buddies                       Likes(drinker, beer)
                       wife             Favorite(drinker, beer)
                                        Buddies(name1, name2)
          Married
                                            Married(husband, wife)
           Example (with Renaming)
2. The relationship Stars-In between entity sets Movies and Stars is
   represented by a relation with schema:
       Stars-In(title, year, starName)
   1. A sample instance is:                                We rename
                                                          here for clarity.

        title            year            starName
       Star Wars         1977            Carrie Fisher
       Star Wars         1977            Mark Hamill
       Star Wars         1977            Harrison Ford
       Mighty Ducks      1991            Emilio Estevez
       Wayne’s World       1992          Dana Carvey
       Wayne’s World       1992          Mike Meyers
            Combining Relations
• OK to combine the relation for an entity-set E with the
  relation R for a many-one relationship from E to another
  entity set.

• Example:
   – Drinkers(name, addr) and Favorite(drinker, beer)
     combine to make Drinkers(name, addr, favBeer).

• However, when there are many drinkers that do not have a
  favorite beer, then we don’t combine the Drinkers and
  Favorite relations.
Risk with Many-Many Relationships

• Combining Drinkers with Likes would
  be a mistake. Why?
• It leads to redundancy, as:

       name    addr       beer
       Sally 123 Maple   Bud
       Sally 123 Maple   Miller



         Redundancy
    Handling Weak Entity Sets
• Relation for a weak entity set must
  include attributes for its complete key
  (including those belonging to other entity
  sets), as well as its own, nonkey
  attributes.

• A supporting (double-diamond)
  relationship is redundant and yields no
  relation.
                        Example
            name                      name


billTo     Logins         At         Hosts     location


         Hosts(hostName, location)
         Logins(loginName, hostName, billTo)
         At(loginName, hostName, hostName2)

                          Must be the same
 At becomes part of
 Logins
Our Movie Example (with ISA)

               length       title        year    filmType
to Stars
                                    Movies


      Voices
                      isa                       isa           weapon


                                                      Murder-
           Cartoons                                   Mysteries
  Subclass Structures to Relations
Two different approaches
• OO Approach
   – An object belongs to exactly one class.
   – An object inherits properties from all its super-classes but it is
     not a member of them.


• E/R Approach
   – An “object” can be represented by entities belonging to several
     entity sets that are related by isa relationships.
   – Thus, the linked entities together represent the object and give
     that object its properties (attributes and relationships).
                       OO approach
• Every subclass has its own relation.
• All the properties of that subclass, including all its
  inherited properties, are represented in this
  relation.


• Example: For our example the relational database schema
  would be:
   – Movies( title, year, length, filmType )
   – Cartoons( title, year, length, filmType )
   – MurderMysteries( title, year, length, filmType, weapon)
   – Cartoon-MurderMysteries( title, year, length, filmType, weapon)
• Can we merge Cartoons with Movies?
   – If we do, we lose information about which moves are
     cartoons.


• For the relationship Voices, we create:
   – Voices( title, year, starName )


• Is it necessary to create two relations one connecting
  cartoons with stars, and one connecting cartoon-
  murder-mysteries with stars?
   – Not, really. We can use the same relation (table).
                    E/R Approach
• We will have the following relations:
  – Movies(title, year, length, filmType).
  – MurderMystery(title, year, weapon).
  – Cartoons(title, year).
  – Voices(title, year, name).
                    E/R approach (II)
• Remark:
   – There is no relation corresponding to the class Cartoon-
     MurderMystery.
   – For a movie that is both, we obtain:
       • its voices from the Voices relation,
       • its weapon from the MurderMystery relation,
       • and all other information from the Movies relation.



• The relation Cartoons has a schema that is a subset of
  the schema for the relation Voices. Should we eliminate
  the relation Cartoons?
• However there may be silent cartoons in our database.
  Those cartoons would have no voices and we would lose
  them.
       Comparison of Approaches
OO translation drawback:
• Too many tables! Why?
  – In the OO approach if we have a root and n children we need 2^n
    different tables!!!




E/R translation drawback:
• We may have to look in several relations to
  gather information about a single object.
  – For example, if we want the length and weapon used
    for a murder mystery film, we have to look at Movies
    and MurderMysteries relations.
     Comparison of Approaches
           (Continued)
OO translation advantage:
• The OO translation keeps all properties of an object
  together in one relation.

E/R translation advantage:
• The E/R translation allows us to find in one relation
  tuples from all classes in the hierarchy.
                          Examples
• What movies of 1999 were longer than 150 minutes?
  – Can be answered directly in the E/R approach.
  – In the OO approach we have to examine all the relations.

• What weapons were used in cartoons of over 150 minutes in length?
  – More difficult in the E/R approach.
      • We should access Movies to find those of over 150 mins.
      • Then, we have to access Cartoons to see if they are cartoons.
      • Then we should access MurderMysteries to find the weapon.
  – In OO approach we need only access the Cartoon-MyrderMysteries
    table.
   Null Values to Combine Relations
• If we are allowed to use NULL in tuples, we can handle a hierarchy of
  classes with a single relation.
    – This relation has attributes for all the properties possessed by objects in
      any of the classes of the hierarchy.
    – An object is represented by a single tuple. This tuple has NULL in each
      attribute corresponding to a property that does not belong to the object’s
      class.
• If we apply this approach to the Movie hierarchy, we would create a
  single relation whose schema is:
    – Movie(title, year, length, filmType, studioName, starName, voice, weapon)
    – A movie like “Who Framed Roger Rabbit?”, being both a cartoon and a
      murdermystery, would be represented by several tuples that had no
      NULL’s.
    – The Little Mermaid, being a cartoon but not a murder-mystery, would have
      NULL in the weapon component.
• This approach allows us to find all the information about an object in
  one relation. Drawback?
                     Create Table
CREATE TABLE Movies (      • CHAR(n) allocates a fixed space,
   title CHAR(40),           and if the string that we store is
   year INT,                 shorter than n, then it is padded
   length INT,               with blanks.
   type CHAR(2)
                           • Differently, VARCHAR(n) denotes a
);
                             string of up to n characters.
CREATE TABLE Studios (
   name CHAR(20),             – ORACLE uses also VARCHAR2(n),
                                which is semantically the same as
   address VARCHAR(255),
                                VARCHAR(n), which is deprecated.
   noOfEmp INT
                              – VARCHAR(n) or VARCHAR2(n) allow
);
                                for compression in order to save
CREATE TABLE Stars (            space.
   name CHAR(30),
   address VARCHAR(255),   • Use CHAR(n) for frequently used
                             fields, and use VARCHAR(n)
   gender CHAR(1)
                             otherwise.
);
            Insert, Update, Delete,
INSERT INTO Movies(title, year, length, type)
VALUES('Godzilla', 1998, 120, 'C');


INSERT INTO Movies
VALUES('Godzilla', 1998, 120, 'C');


UPDATE Movies
SET title = 'Godzilla 2'
WHERE title = 'Godzilla' AND year=1998;


DELETE FROM Movies WHERE title='Godzilla 2';
             Declaring primary keys
DROP TABLE Movies;

CREATE TABLE Movies (
   title CHAR(40) PRIMARY KEY,
   year INT,
   length INT,
   type CHAR(2)
);


DROP TABLE Movies;


CREATE TABLE Movies (
   title CHAR(40),
   year INT,
   length INT,
   type CHAR(2),
   PRIMARY KEY (title, year)
);
                    Altering, Dropping
ALTER TABLE Stars ADD phone CHAR(16);


ALTER TABLE Stars DROP COLUMN phone;


ALTER TABLE Stars MODIFY phone CHAR(26);


DROP TABLE Stars;
DROP TABLE Movies;
DROP TABLE Studios;
             Declaring foreign keys
CREATE TABLE Studios (
   name CHAR(20) PRIMARY KEY,
   address VARCHAR(255),
   noOfEmp INT
);



CREATE TABLE Movies (
   title CHAR(40) PRIMARY KEY,
   year INT,
   length INT,
   type CHAR(2),
   studioName CHAR(20),
   FOREIGN KEY (studioName) REFERENCES Studios(name)
);

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:8/18/2012
language:English
pages:28