slide1
Download
Skip this Video
Download Presentation
Movies

Loading in 2 Seconds...

play fullscreen
1 / 16

Movies - PowerPoint PPT Presentation


  • 294 Views
  • Uploaded on

filmType title year length toStar Movies Voices isa isa weapon Cartoons MurderMystery Our Movie Example Subclass Structures to Relations There are two different approaches to organize relations that represent a hierarchy of classes regarding OO and E/R models. In OO

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Movies' - bernad


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1
filmType

title

year

length

toStar

Movies

Voices

isa

isa

weapon

Cartoons

MurderMystery

Our Movie Example

slide2
Subclass Structures to Relations
  • There are two different approaches to organize relations that represent a hierarchy of classes regarding OO and E/R models.
  • In OO
    • An object belongs to exactlyone class.
    • An object inherits properties from all its super-classes but it is not a member of them.
  • In E/R model
    • An “object” can be represented by entities belonging to several entity sets that are related by isarelationships.
    • Thus, the linked entities together represent the object and give that object its properties (attributes and relationships).
slide3
OO approach
  • OO way:
    • 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 examplethe 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)
slide4
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.
slide5
E/R way
  • The entity set Movieswill be represented by the relation:
    • Movies(title, year, length, filmType).
  • The entity set MurderMysterywill be represented by the relation:
    • MurderMystery(title, year, weapon).
  • The entity set Cartoonswill be represented by the relation:
    • Cartoons(title, year).
      • This relation has no attribute other than the key for movies.
  • The relationship Voiceswill be represented by the relation:
    • Voices(title, year, name).
      • The last attribute is the key for Starsand the first two form the key for Cartoons.
slide6
E/R way (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 Cartoonshas 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.
slide7
Comparison of Approaches

OO translation drawback:

  • Should we want to find an object, it forces us to search several relations.
    • For example, if we want to find “Robin Hood”, we should search four different relations, until we find the relation for the class the movie is in.

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 all the relations.
slide8
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.
  • In simple words:
    • To find the value of a general (of all classes) attribute is easier in E/R translation. We just look at the relation of the super-class.
    • To find the value of a specific (not of all classes) attribute along with the value of a general one, is easier in the OO translation. In the E/R translation we have to join the relation of the super-class with the relation for the child.
slide9
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 Cartoon-MyrderMysteries table.
  • However, we might want to use not too many tables. In the OO approach if we have a root and n children we need 2^n different tables!!!
slide10
Null Values to Combine Relations
  • If we are allowed to use NULL as a value 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.
slide11
Null Values to Combine Relations (Continued)
  • It allows us to find all the information about an object in one relation.
  • However, an approach using NULL’s is invalid for the relational model, although most of its commercial implementations support NULL’s.
slide12
Create Table

CREATE TABLE Movies (

title CHAR(40),

year INT,

length INT,

type CHAR(2)

);

CREATE TABLE Studios (

name CHAR(20),

address VARCHAR(255),

noOfEmp INT

);

CREATE TABLE Stars (

name CHAR(30),

address VARCHAR(255),

gender CHAR(1)

);

  • CHAR(n) allocates a fixed space, and if the string that we store is shorter than n, then it is padded with blanks.
  • Differently, VARCHAR(n) denotes a string of up to n characters.
    • ORACLE uses also VARCHAR2(n), which is semantically the same as VARCHAR(n), which is deprecated.
    • VARCHAR(n) or VARCHAR2(n) allow for compression in order to save space.
  • Use CHAR(n) for frequently used fields, and use VARCHAR(n) otherwise.
slide13
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';

slide14
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)

);

slide15
Altering, Dropping, Defaults, Indexes

ALTER TABLE Stars ADD phone CHAR(16);

ALTER TABLE Stars DROP COLUMN gender;

ALTER TABLE Stars MODIFY phone CHAR(26);

DROP TABLE Stars;

DROP TABLE Movies;

DROP TABLE Studios;

CREATE TABLE Stars (

name CHAR(30),

address VARCHAR(255),

gender CHAR(1) DEFAULT 'N'

);

slide16
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)

);

ad