Difference between revisions of "More details about the database."
From SQLZOO
(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> | |
<table border="1"> | <table border="1"> | ||
| Line 123: | Line 135: | ||
</table> | </table> | ||
| − | + | <h2>Example</h2> | |
| − | + | <table border="1"> | |
| − | <h2> | + | <tr><th>'''movieid'''</th><th>'''actorid'''</th><th>'''ord'''</th></tr> |
| − | < | + | <tr><td>10003</td><td>20</td><td>4</td></tr> |
| − | + | <tr><td>10004</td><td>50</td><td>1</td></tr> | |
| − | < | + | </table> |
| − | + | ||
| − | + | ||
| − | + | ||
| − | < | + | |
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
| id | title | yr | director | budget | gross |
|---|---|---|---|---|---|
| 10003 | "Crocodile" Dundee II | 1988 | 38 | 15800000 | 239606210 |
| 10004 | 'Til There Was You | 1997 | 49 | 10000000 |
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
| id | name |
|---|---|
| 20 | Paul Hogan |
| 50 | Jeanne 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
| movieid | actorid | ord |
|---|---|---|
| 10003 | 20 | 4 |
| 10004 | 50 | 1 |
