-
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)
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|