DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: DVD Project

  1. #1
    Join Date
    Oct 2002
    Location
    india
    Posts
    1

    DVD Project

    Hi Gurus,

    I need a very big favour from you all..It will be greatfull,if you could help me within 2 days...

    I have a small project,which i have given the spec below.I need table design with constraint validation with PK & FK.

    And below that i have specified the requirement ie,simple sqls statements..

    I would appriciate all if you could give me answers asap.its quite very urgent.

    thanks in advance.

    Nithya

    Data to be Modelled:

    DVD Information:
    Unique ID

    DVD title (64 chars max)

    Movie ID the DVD contains

    Rating(string 12 char max.. eg "PG" or "MA-15+")

    Region Code (integer with range 0-5, 0 for un-encoded)

    Video Format (string 4:3, 16:9)

    Audio tracks (Sound Format which is a string <32 ----characters :mono,stereo,dolby 5.1

    sound string <32 characters: movie, musical score,commentary )

    Special features (commentary, pictures, screen notes, games, DVD ROM
    material, "making of" features)

    DVD release date

    Movie Information:

    Unique ID

    Title (string less than 64 chars)

    Year Created (integer value)

    Country of origin (country names <64 characters)

    Running Time (in minutes)

    Director (stores a cast member unique ID)

    Producer (stores a cast member unique ID)

    Genre(s) : action, adult, adventure, animation, classic, comedy,
    crime,cult, documentary, drama, family, fantasy, horror, historical, musical,mystery, romance, sci-fi, short, sport, thriller, television series, war,western

    Cast list

    o Unique cast member ID
    o role: actor/actress, director, producer, writer etc (can have more than one role e.g. actor and director, director and writer)
    o character name (if actor/actress)

    Brief plot outline (variable length text up to 150 characters)
    Synopsis (detailed plot outline) which is variable length text up to 1000 characters

    Famous quotes from the movie (quote consists of character name <32chars,quote text no more than 100 chars)

    Trivia pieces (can have multiple "snippets" of trivia per movie) eachcan be a maximum of 300 characters



    People (cast and crew) Biographies:

    First, middle and Last names

    Date of birth

    Searches:

    The following searches are to be created as SQL statements


    1) Movie Title search to return (possibly multiple rows) internal movie ID,Title, Year Created, Country of origin, Running Time (in minutes),Director,Producer (given a string containing the movie title)

    2) Cast and crew search for a movie given the internal movie unique ID(returns
    cast member ID, their role description , character name if an actor)Search for (possibly multiple) cast and crew members with a name matching given first, middle and last name (should return their internal ID, all name information and date of birth)

    3)Search for actor/actress (given their internal ID) to retrieve their bio

    4)Search for DVD titles by genre (list of internal DVD id, DVD title,movie
    internal ID, movie title, year of DVD release, year of movie creation)

    5)Search for all DVDs which have at least 3 special features ordered by the number of special features (to return DVD unique ID and number of special features)

    6)Search for actors who have acted in films of a certain genre between two given years (the genre is to be given in a string, the two years are given as numbers, should return a list of unique internal actor ids)

    7)Character name search to return list of actors with the movies they played the character in (should return actor unique id, actor first, middle and last name, movie unique internal id, movie title)

    8)Search for directors who have directed films of a certain given genre more than once, ordered by the number of times they have directed films of the genre (takes in a genre as a string and returns a list of internal unique ids to identify each director)

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    laff

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width