Difference between revisions of "More details about the database."

From SQLZOO
Jump to: navigation, search
(Created page with "<h3>Movie Database</h3> <p>This database features two entities (movies and actors) in a many-to-many relation. Each entity has its own table. A third table, <i>casti...")
 
 
Line 55: Line 55:
 
     </tr>
 
     </tr>
 
   </table>
 
   </table>
 +
<h2>Example</h2>
 +
<table border="1">
 +
<tr><th>'''id'''</th><th>'''title'''</th><th>'''yr'''</th><th>'''director'''</th><th>'''budget'''</th><th>'''gross'''</th></tr>
 +
<tr><td>10003</td><td>"Crocodile" Dundee II</td><td>1988</td><td>38</td><td>15800000</td><td>239606210</td></tr>
 +
<tr><td>10004</td><td>'Til There Was You</td><td>1997</td><td>49</td><td>10000000</td><td></td></tr>
 +
</table>
  
 
   <h2>actor</h2>
 
   <h2>actor</h2>
Line 84: Line 90:
 
     </tr>
 
     </tr>
 
   </table>
 
   </table>
 +
<h2>Example</h2>
 +
<table border="1">
 +
<tr><th>'''id'''</th><th>'''name'''</th></tr>
 +
<tr><td>20</td><td>Paul Hogan</td></tr>
 +
<tr><td>50</td><td>Jeanne Tripplehorn</td></tr>
 +
</table>
  
  <h2>casting</h2>
+
<h2>casting</h2>
  
 
   <table border="1">
 
   <table border="1">
Line 123: Line 135:
 
</table>
 
</table>
  
<hr />
+
<h2>Example</h2>
 
+
<table border="1">
<h2>Footnotes</h2>
+
<tr><th>'''movieid'''</th><th>'''actorid'''</th><th>'''ord'''</th></tr>
<p>This data has been drawn from the [http://www.imdb.com/ Internet Movie Database] The cast lists have been truncated to exclude those actors with a single appearance.</p>
+
<tr><td>10003</td><td>20</td><td>4</td></tr>
 
+
<tr><td>10004</td><td>50</td><td>1</td></tr>
<p>New releases and English language films are predominant.</p>
+
</table>
 
+
<p>Often the the title is in the original language thus '''My Life as a Dog''' is '''Mitt liv som hund''' and '''The Good the Bad and the Ugly''' is '''Buono, il brutto, il cattivo, Il'''</p>
+
 
+
<p>Because of the actor culling the ord values may not be consecutive for a given film.</p>
+

Latest revision as of 13:15, 6 August 2012

Contents

Movie Database

This database features two entities (movies and actors) in a many-to-many relation. Each entity has its own table. A third table, casting , is used to link them. The relationship is many-to-many because each film features many actors and each actor has appeared in many films.

movie

Field name Type Notes
id INTEGER An arbitrary unique identifier
title CHAR(70) The name of the film - usually in the language of the first release.
yr DECIMAL(4) Year of first release.
director INT A reference to the actor table.
budget INTEGER How much the movie cost to make (in a variety of currencies unfortunately).
gross INTEGER How much the movie made at the box office.

Example

idtitleyrdirectorbudgetgross
10003"Crocodile" Dundee II19883815800000239606210
10004'Til There Was You19974910000000

actor

Field name Type Notes
id INTEGER An arbitrary unique identifier
name CHAR(36) The name of the actor (the term actor is used to refer to both male and female thesps.)

Example

idname
20Paul Hogan
50Jeanne Tripplehorn

casting

Field name Type Notes
movieid INTEGER A reference to the movie table.
actorid INTEGER A reference to the actor table.
ord INTEGER The ordinal position of the actor in the cast list. The

star of the movie will have ord value 1 the co-star will have

value 2, ...

Example

movieidactoridord
10003204
10004501
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense