Movies - PowerPoint PPT Presentation

Slide1 l.jpg
Download
1 / 16

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

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

Download Presentation

Movies

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 l.jpg

filmType

title

year

length

toStar

Movies

Voices

isa

isa

weapon

Cartoons

MurderMystery

Our Movie Example


Slide2 l.jpg

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 l.jpg

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 l.jpg

  • 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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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)

);


  • Login