exam 2 solution
Download
Skip this Video
Download Presentation
Exam 2 Solution

Loading in 2 Seconds...

play fullscreen
1 / 19

Exam 2 Solution - PowerPoint PPT Presentation


  • 187 Views
  • Uploaded on

Exam 2 Solution . Sources used: http://en.wikipedia.org/wiki/Six_Degrees_of_Kevin_Bacon , http://college.yukondude.com/2003_09_comp210/ http://us.imdb.com/title/tt0388213/ http://academics.smcvt.edu/jellis-monaghan. Interview.

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 'Exam 2 Solution ' - Roberta


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
exam 2 solution

Exam 2 Solution

Sources used:

http://en.wikipedia.org/wiki/Six_Degrees_of_Kevin_Bacon,

http://college.yukondude.com/2003_09_comp210/

http://us.imdb.com/title/tt0388213/

http://academics.smcvt.edu/jellis-monaghan

interview
Interview
  • A new client comes begging for you to build him a database. He is movie fan and is too excited to sit down and write out statements describing the rules he wants, but you have been able to pry out of him that the following questions need to be answered by the system:
  • For any actor\'s name given, which movies has that actor appeared in?
  • Of those movies, what are the names of the other significant actors with roles?
  • What were the names of the characters played by those actors?
  • Which Academy Awards, if any, did the movies or the actors involved win?
  • The data on the producer, the company expenditure for the movie, the amount of revenue it collected on a given week of showing
  • The name of the critics and the ratings of an actor and a movie
  • After you accept his conditions, Your task is to turn 4 separate products of your design
  • The Domains and their format (25%)
  • The assumption (25%)
  • The FD diagram (25%)
  • The entities en 3 normal form (25%)
domains limited approach
Domains (limited approach)

1. Actor Name String ( 40 )Example : “Kevin Bacon”

2. Movie Title String (40) Example : “A few good men”

3. Critic Name String ( 40 )Example : “James Madison”

4. Category String ( 40 )Example : “Male supporting”

5. Role String ( 40 )Example : “Cap. Jack Ross”

6. Week int Example :1 (to represent first week of showing)

7. Budget FloatExample: $ 220,000,000

8. Income FloatExample: $ 20,000,000

9. Review int Example :6 (to represent a scale 1 to 10)

10. Overlall ReviewintExample :7 (to represent a scale 1 to 10)

11. Award String (10)Example : “Oscar”

fd diagram

Week

FD Diagram

Critic

Actor

Review

Role

Award

Overall

Review

Budget

Movie

Category

Income

entities
Entities

1. Reviews: (Movie, Role, Critic, Review)

2. Movies: ( Movie, OverallReview, Budget)

3. Players:(Movie, Role, Category, Actor, Award)

4. Profits: (Movie, Week, Income)

11 domains with 4 entities

assumptions
Assumptions
  • A movie title is unique, no new version are considered
  • Roles are performed individually
  • Categories are defined for roles in a movie
  • The movie has a single overall rating
  • The Role are also used as for non acting jobs (such as producer, director) or even the company therefore

Actor Name String ( 40 )Example : “Kevin Bacon”

is also used for director, producer, company

don t forget when modeling reality

Database

Enterprise

Num Fitting Cost

brass

hinge

tap

Day Num Status

Mon

Tue

Wed

Wrong

A good model generates

a lasting design

Don’t Forget when Modeling Reality

Relations and

information flows

Truth is the conformity that exists between the thing (reality of the enterprise) and the description of it (database)

Saint Thomas Aquinas (1224-1275)

domains extended
Domains (extended)

1. Actor Name String ( 40 ) Example : “Kevin Bacon”

2. Movie Title   String (40) Example : “A few good men”

3. Critic Name   String ( 40 ) Example : “James Madison”

4. Category   String ( 40 ) Example : “Male supporting”

5. Role    String ( 40 ) Example : “Cap. Jack Ross”

6. Week int  Example :1 (to represent first week of showing)

7. Budget   FloatExample: $ 220,000,000

8. Income   FloatExample: $ 20,000,000

9. Review int  Example :6 (to represent a scale 1 to 10)

10. Overlall Review  intExample :7 (to represent a scale 1 to 10)

11. Award String (10) Example : “Oscar”

12. Year int  Example : 1992

13. LocationString ( 40 ) Example : “New York City”

14. Version Integer  Example : 2 fisrt remake

homework 4 your task it to represent this normalization in er

Quantity

color

weight

color

weight

Manager

Description

t

fitting

Plant

Fitting

Quantity

P #

F #

Plant

Chen’s

Description

Manager

Codd’s

Quantity

Stock

weight

color

Manager

Description

m

t

fitting

Plant

P #

F #

Chen’s

Homework 4Your task it to represent this normalization in ER

now query the database

For any actor\'s name given, which movies has that actor appeared in?

  • Of those movies, what are the names of the other significant actors with roles?
  • What were the names of the characters played by those actors?
  • Which Academy Awards, if any, did the movies or the actors involved win?
  • And of course: for the given actor\'s name, by tracing through other actors
  • how many movies removed (degrees of separation) is that actor from Kevin Bacon
  • what is the path, or paths, leading between the two

Now query the database

data definition dd

sanchez=# create table movies(movie varchar(25), overallreview int, budget float,

primary key (movie) );

NOTICE: CREATE TABLE / PRIMARY KEY will create

implicit index "movies_pkey" for table "movies"

sanchez=# create table profits (movie varchar(25), week int, income float,

primary key (movie,week), foreign key (movie) references movies(movie));

NOTICE: CREATE TABLE / PRIMARY KEY will create

implicit index "profits_pkey" for table "profits"

CREATE TABLE

Data Definition (DD)

sanchez=# create table players(movie varchar(25), role varchar(15), category varchar(15),

actors varchar(25), award varchar(25), primary key (movie,role),

foreign key (movie) references movies(movie));

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit

index "players_pkey" for table "players"

CREATE TABLE

sanchez=# create table reviews(movie varchar(25), role varchar(25),

critic varchar(25), review int, primary key (movie,role,critic),

foreign key (movie) references movies(movie) );

NOTICE: CREATE TABLE / PRIMARY KEY

will create implicit index "reviews_pkey" for table "reviews"

data manipulation dm

sanchez=# insert into movies values (\'a few good men\', 8, 40);

sanchez=# insert into movies values (\'sleepers\', 7, 20);

sanchez=# insert into movies values (\'apollo13\', 6, 60);

sanchez=# insert into profits values (\'apollo13\', 1, 8);

sanchez=# insert into profits values (\'sleepers\', 1, 3);

sanchez=# insert into profits values (\'a few good men\', 1, 20);

sanchez=# insert into profits values (\'a few good men\', 2, 10);

sanchez=# insert into profits values (\'a few good men\', 3, 10);

sanchez=# insert into profits values (\'sleepers\', 3, 1);

sanchez=# insert into profits values (\'sleepers\', 2, 2);

sanchez=# insert into profits values (\'apollo13\', 2, 2);

sanchez=# insert into profits values (\'apollo13\', 3, 1);

sanchez=# insert into players values (\'apollo13\', \'Film Editing\',\'Best F Edit\',\'Mike Hill\',\'none\');

sanchez=# insert into players values (\'a few good men\', \'Nathan Jessep\',\'Sup Male\',\'Jack Nicholson\',\'Oscar\');

sanchez=# insert into players values (\'a few good men\', \'Jack Ross\',\'Sup Male\',\'Kevin Bacon\',\'none\');

sanchez=# insert into players values (\'a few good men\', \'JoAnn Calaway\',\'Best Female\',\'Demi Moore\',\'none\');

sanchez=# insert into players values (\'a few good men\', \'Director\',\'Direction\',\'Rob Reiner\',\'none\');

sanchez=# insert into players values (\'apollo13\', \'Jack Swigert\',\'Sup Male\',\'Kevin Bacon\',\'none\');

sanchez=# insert into players values (\'apollo13\', \'Film Editing\',\'Best F Edit\',\'Mike Hill\',\'none\');

sanchez=# insert into players values (\'sleepers\', \'Sean Nokes\',\'Sup Male\',\'Kevin Bacon\',\'none\');

sanchez=# insert into players values (\'sleepers\', \'MusicScore\',\'Best M Score\',\'John Williams\',\'oscar\');

Data Manipulation (DM)

data manipulation dm delete

sanchez=# \d players

Table "public.players"

Column | Type | Modifiers

----------+-----------------------+-----------

movie | character varying(25) | not null

role | character varying(15) | not null

category | character varying(15) |

actors | character varying(25) |

award | character varying(25) |

Indexes:

"players_pkey" PRIMARY KEY, btree (movie, role)

Foreign-key constraints:

"players_movie_fkey" FOREIGN KEY (movie) REFERENCES movies(movie)

sanchez=# select * from players

sanchez-# ;

movie | role | category | actors | award

----------+----------+-----------+--------+-------

apollo13 | Director | Direction | |

(1 row)

sanchez=# delete from players where movie =\'apollo13\';

DELETE 2

sanchez=# delete from players where movie =\'apollo13\';

DELETE 0

sanchez=# delete from players where movie =\'a few good men\';

DELETE 1

Data Manipulation (DM) Delete

data manipulation dm drop

sanchez=# \dt

List of relations

Schema | Name | Type | Owner

--------+-----------+-------+---------

public | movies | table | sanchez

public | parts | table | sanchez

public | players | table | sanchez

public | profits | table | sanchez

public | reviews | table | sanchez

public | sp | table | sanchez

public | suppliers | table | sanchez

(7 rows)

sanchez=# drop table parts;

DROP TABLE

sanchez=# \dt

List of relations

Schema | Name | Type | Owner

--------+-----------+-------+---------

public | movies | table | sanchez

public | players | table | sanchez

public | profits | table | sanchez

public | reviews | table | sanchez

public | sp | table | sanchez

public | suppliers | table | sanchez

(6 rows)

Data Manipulation (DM) Drop

data manipulation dm update

sanchez=# select * from parts;

pn | pname | color | weight | city

----+-------+-------+--------+--------

1 | Nut | red | 12 | London

2 | Bolt | green | 17 | Paris

3 | Screw | blue | 17 | Rome

4 | Screw | red | 14 | London

5 | Cam | blue | 12 | Paris

6 | Cog | red | 19 | London

(6 rows)

sanchez=# select * from parts;

pn | pname | color | weight | city

----+-------+---------+--------+--------

2 | Bolt | green | 17 | Paris

3 | Screw | blue | 17 | Rome

4 | Screw | red | 14 | London

5 | Cam | blue | 12 | Paris

6 | Cog | red | 19 | London

1 | Nut | magenta | 12 | London

(6 rows)

Data Manipulation (DM) Update

sanchez=# update parts set color=\'magenta\' where pn=1;

UPDATE 1

the kevin bacon game or 6 degrees of separation
The Kevin Bacon Gameor6 Degrees of separation

http://www.spub.ksu.edu/issues/v100/FA/n069/fea-making-bacon-fuqua.html

Kevin Bacon is not even among the top 1000 most connected actors in Hollywood (1222th).

Total number of linkable actors: 631275Weighted total of linkable actors: 1860181Average Bacon number: 2.947

Average Connery Number: 2.706

Data from The Oracle of Bacon at UVA

create views

sanchez=# \d myactors

View "public.myactors"

Column | Type | Modifiers

----------+-----------------------+-----------

actors | character varying(25) |

movie | character varying(25) |

role | character varying(25) |

category | character varying(15) |

award | character varying(25) |

critic | character varying(25) |

review | integer |

View definition:

SELECT players.actors, reviews.movie, reviews.role,

players.category, players.award, reviews.critic, reviews.review

FROM players, reviews

WHERE reviews.role::text = players.role::text AND

reviews.movie::text = players.movie::text;

Create Views

sanchez=# create view myactors as select actors, reviews.movie, reviews.role, players.category, players.award, reviews.critic , reviews.review from players, reviews where reviews.role=players.role and reviews.movie=players.movie;

CREATE VIEW

extended sql
Extended SQL

sanchez=# select actors, critic, review from

myactors where actors=\'Kevin Bacon\';

actors | critic | review

-------------+--------+--------

Kevin Bacon | abc | 10

Kevin Bacon | cbs | 9

Kevin Bacon | abc | 8

(3 rows)

Provide list of my actors

sanchez=# select * from myactors;

actors | movie | role | category | award | critic | review

-------------+----------------+------------+----------+-------+--------+--------

Kevin Bacon | a few good men | Jack Ross | Sup Male | none | cbs | 9

Kevin Bacon | a few good men | Jack Ross | Sup Male | none | abc | 10

Kevin Bacon | sleepers | Sean Nokes | Sup Male | none | abc | 8

(3 rows)

extended sql19
Extended SQL

Provide list of suppliers that are currently

providing something

Difference between in,any,some,all clauses

sanchez=# SELECT * FROM suppliers

WHERE sn IN (SELECT sn FROM sp);

sn | sname | status | city

----+-------+--------+--------

1 | Smith | 20 | London

2 | Jones | 10 | Paris

3 | Blake | 30 | Paris

4 | Clark | 20 | London

(4 rows)

sanchez=# select * from suppliers

where sn = any(select sn from sp);

sn | sname | status | city

----+-------+--------+--------

1 | Smith | 20 | London

2 | Jones | 10 | Paris

3 | Blake | 30 | Paris

4 | Clark | 20 | London

(4 rows)

ad