Erstelle entsprechend der gegebenen Relationen die nötigen Tabellen in einer Datenbank. Verwende dafür den Befehlt CREATE TABLE.

  • movie(mid, title, year)
  • actor(id, name, movie_id, role, order)
  • actress(id, name, movie_id, role, order)
  • producer(id, name, movie_id, role)
  • genre(id, movie_id, genre)

Die Datentypen sind wie folgt zu wählen:

  • Die Attribute id, year und order sind vom Typ INTEGER
  • Alle anderen Attribute sind vom Typ VARCHAR(127)

Definiere auch die Primärschlüssel für die einzelnen Tabellen.

Hinweise zu dem verwendeten IMDb-Datensatz:

  • Die Tabellen actor, actress, producer und genre enthalten eine Spalte mit dem Namen "id”. Hierbei handelt es sich um Zeilennummern und nicht um Werte, die etwa einen Film oder eine Person eindeutig identifizieren. Die Tabellen actor und actress speichern daher eher Rollen von Schauspielern in verschiedenen Filmen als Schauspieler selbst.
  • Nimm an, dass Schauspieler, Schauspielerinnen und Produzenten eindeutig über ihre Namen identifiziert werden können.
  • Nimm an, dass die "movie_id”-Attribute jeweils das "mid”-Attribut referenzieren. Eine Fremdschlüssel-Eigenschaft gilt allerdings nicht automatisch in dem zur Verfügung gestellten IMDb-Datensample. Es gibt also z.B. Schauspieler und Genres, deren zugehörige Filme NICHT existieren!

Nenne für jede der folgenden natürlichsprachlichen Fragen eine geeignete SQL-Anfrage und führe sie auf den Daten der IMDb aus. Gib auf deiner Abgabe die Anfrage und deren Ergebnis an.

  1. Wie viele Schauspielerinnen gibt es? (2P)

  2. Gib alle Producer aus, die keine zugehörigen Einträge in der Filmtabelle haben (nach Producernamen sortiert)! Jeder Producer soll dabei nur einmal ausgegeben werden. (3P)

  3. Gib die Titel aller Filmpaare aus, in denen mindestens ein gemeinsamer Schauspieler mitspielt! Sortiere das Ergebnis nach dem Titel des zweiten Films. (3P)

  4. Gib die Namen der Personen (Schauspieler und Produzenten) an, die an der Serie "Edge of Night, The" beteiligt waren und zwar einmal nach Mengen- und ein weiteres Mal nach Multimengensemantik. Hinweis: UNION benutzen. (4P)

  5. Erstelle eine Top-3 Liste der Filme mit den meisten Schauspielern und Schauspielerinnen! Sortiere entsprechend. Hinweis: Recherchiere hierzu die FETCH FIRST Klausel. (4P)

  6. Erstelle eine Top-3 Liste der Schauspieler und Schauspielerinnen mit den meisten Filmen! Sortiere entsprechend. (4P)

Solution
  • 1

    select count(distinct name) as "Number of Actresses"
    from actresses;
    
    Row Number of Actresses
    1 3331

    2

    select *
    from producers
    where movie_id not in (
        select mid as movie_id from movies
    )
    order by name;
    
    Row id name movie_id role
    1 34205 Bovaira, Fernando Vanilla Sky (2001) executive producer
    2 38488 Briskin, Frederick Adventures of Rin Tin Tin, The producer
    3 147431 Hofmann, Trish Running with Scissors (2002) executive producer
    4 149175 Horan, Ralph Michael (1996) executive producer
    5 177843 Kousakis, John Peter Feeling Called Glory, A (1999) co-executive producer
    6 211751 Matouk, Antonio Pablo y Carolina (1957) producer
    7 258028 Potamkin, Buzz Flintstones Christmas Carol, A (1994) (TV) executive producer
    8 279764 Ruiz Sandoval, Humberto Dempsey en México (1925) producer
    9 292571 Scorsese, Martin Kicked in the Head (1997) executive producer
    10 355663 Winter, Donna (II) Girl Talk (2001) producer

    Anmerkung: Diese Anfrage gibt Produzenten aus, die mindestens einen Film gedreht haben, der nicht in 'movie' enthalten ist. 'Bovaira, Fernando' hat zwar 'Vanilla Sky (2001)' gedreht, und dieser Film kommt nicht in 'movie' vor. Jedoch hat 'Bovaira, Fernando' noch einen weiteres Film gedreht (nämlich 'Caja 507, La (2002)'), der sehr wohl in 'movie' vorkommt. [-1 Punkt]

    3

    select distinct movie1.title as "Title 1", movie2.title as "Title 2"
    from movies as movie1
    inner join actors as actor1 on actor1.movie_id = movie1.mid
    inner join actors as actor2 on actor2.name = actor1.name
    inner join movies as movie2 on movie2.mid = actor2.movie_id
    where movie1.mid < movie2.mid
    order by movie2.title;
    
    Row Title 1 Title 2
    1 Amiche del cuore, Le Amore imperfetto, L'
    2 Air-Tight Back Stage
    3 Andy Clyde Gets Spring Chicken Boobs in Arms
    4 Big Idea, The Booty and the Beast
    5 Boobs in Arms Booty and the Beast
    6 Air-Tight Bromo and Juliet
    7 Beer Cry for Help: The Tracey Thurman Story, A
    8 4 aventures de Reinette et Mirabelle Dame de lieudit, La
    9 Adieu Don Juan
    10 Dernier mot, Le Don Juan
    11 Bare Ass Beach Eclipse
    12 Cafe Fantasy Eclipse
    13 Addicted to Love Eclipse
    14 Bikini Beach Eclipse
    15 Cry for Help: The Tracey Thurman Story, A Edge of Night, The
    321 Rainha Louca, A Último Vôo do Condor, O

    4

    select name
    from actors, movies
    where movies.mid = actors.movie_id and title = 'Edge of Night, The'
    
    union
    
    select name
    from producers, movies
    where movies.mid = producers.movie_id and title = 'Edge of Night, The'
    
    Row name
    1 Aames, Willie
    2 Addy, Wesley
    3 Albee, Denny
    4 Andrews, Mark (V)
    5 Arnold, Mark (I)
    6 Arnold, Victor (II)
    7 Augustine, Craig
    8 Bain, Conrad
    9 Baio, Joey
    10 Barrett, Leslie (I)
    11 Barrow, Bernard
    12 Beck, Jackson
    13 Bedford Lloyd, John
    14 Bell, Ralph
    15 Berger, Bill
    284
    select name
    from actors, movies
    where movies.mid = actors.movie_id and title = 'Edge of Night, The'
    
    union all
    
    select name
    from producers, movies
    where movies.mid = producers.movie_id and title = 'Edge of Night, The'
    
    Row name
    1 Aames, Willie
    2 Addy, Wesley
    3 Albee, Denny
    4 Andrews, Mark (V)
    5 Arnold, Victor (II)
    6 Arnold, Mark (I)
    7 Augustine, Craig
    8 Bain, Conrad
    9 Baio, Joey
    10 Barrett, Leslie (I)
    11 Barrow, Bernard
    12 Beck, Jackson
    13 Bedford Lloyd, John
    14 Bell, Ralph
    15 Berger, Bill
    286

    5

    select title as "Movie Title", count(*) as "Number of Actors"
    from (
        select * from actors
        union
        select * from actresses
        ) actorMaleFemale
        join movies on movies.mid = actorMaleFemale.movie_id
    group by movies.mid
    order by "Number of Actors" desc
    fetch first 3 rows only;
    
    Row Movie Title Number of Actors
    1 General Hospital 1089
    2 Edge of Night, The 465
    3 Meister Eder und sein Pumuckl 92

    6

    select name as "Name", count(*) as "Number of Movies"
    from (
            select distinct name, actorMaleFemale.movie_id
            from (
                select * from actors
                union
                select * from actresses
            ) actorMaleFemale
        )
    group by name
    order by "Number of Movies" desc
    fetch first 3 rows only
    
    Row Name Number of Movies
    1 Bayrhammer, Gustl 5
    2 Richardson, Jack (I) 5
    3 Gorss, Sol 4
  • URL:
  • Language: Deutsch
  • Subjects: Databases
  • Type: Name
  • Duration: 45min
  • Credits: 20
  • Difficulty: 0.6
  • Tags: sql query
  • Note:
    HPI, 2015-06-01, Datenbanksysteme 1, Aufgabe 3.1
  • Created By: adius
  • Created At:
    2015-07-26 22:37:34 UTC
  • Last Modified:
    2015-07-26 22:37:34 UTC