1 / 19

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.

Roberta
Download Presentation

Exam 2 Solution

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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%)

  3. 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”

  4. Week FD Diagram Critic Actor Review Role Award Overall Review Budget Movie Category Income

  5. 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

  6. 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

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

  8. 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

  9. 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

  10. 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

  11. 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"

  12. 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)

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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)

  19. 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)

More Related