-
July 20th, 2011, 08:14 AM
#1
How to create ER diagram for this?
Consider the following entities
1) Entity name: Movie
Fields:
Title
MovieID (primary key)
ReleaseDate
ProductionCost
ProdCompName (foregin key for entity Production Company)
2) Entity name: Production Company
Fields:
ProdCompName (primary key)
ContactTelNo
3) Entity name: Actor
ActorName (primary key)
YearBorn
One movie is produced by a single production company. One production company can produce one or more movies.
One actor can act in any number of movies. A movie can have any number of actors in it.
I have made the ER diagram for the above. Since the relation between Actor and Movie is many-to-many, I also made an entity called Movie_Actor for the relation between them. Its fields are
MovieID
ActorName
Fee
Primary key is the composite key made of MovieID and ActorName
But I have problem in the following
Actors and Production companies can get Oscar awards. An actor can have none, 1 or more oscars. Similarly a production company can have none, 1, or more oscars.
How to show this in ER diagram?
What will be fields of Oscar entity?
What will be its primary key?
Should there be a single Oscar entity or should OscarForActor and OscarForProduction be two separate entities?
Thank you
Last edited by visharad; July 20th, 2011 at 10:52 AM.
-
July 21st, 2011, 02:38 AM
#2
Re: How to design relational database for this?
First, I would use numerical ID for every entity, and that ID would be the first field in each table:
1) Entity name: Movie
MovieID (primary key)
Title
ReleaseDate
ProductionCost
ProdCompID (foregin key for entity Production Company)
2) Entity name: Production Company
ProdCompID (primary key)
ProdCompName
ContactTelNo
3) Entity name: Actor
ActorID (primary key)
ActorName
YearBorn
Then I would create an entity for Oscars:
4) Entity name: Oscar
OscarID (primary key)
OscarName
OscarType (A=for Actor, P=for Production Company, ...)
DestinationID (ActorID or ProductionID)
-
July 22nd, 2011, 09:24 AM
#3
Re: How to design relational database for this?
Thank you.
I am giving some examples of Oscar award and the ER diagram must work for these examples.
Nicholas Cage won two Oscars for his performance in the movie “National Treasure”.
Sam Bale, Morgan Freeman, and Michael Caine acted in "Batman". They won one oscar each for that movie.
Paramount Pictures won one Oscar for outstanding production of “Last Airbender".
------------------------------------------------------------------------------------------------------------------
As you are suggesting, there is a single entity for Oscar and OscarType specifies whether it is for actor or production company. If I have to make relation diagram for this in MS Access 2010, do I link DestinationID to both ActorID and ProductionID?
-
July 23rd, 2011, 04:15 PM
#4
Re: How to design relational database for this?
This is one example of Oscar awards, which is to be supported by the ER diagram.
Oscar prizes could be given to outstanding production company for the movies they produced, or to stars for outstanding performance in the movies. Some companies or individual could win more than one Oscar for the movie.
Nicholas Cage has won two Oscars for his performance in “National Treasure” and the stars of “Batman” has won one Oscar each. Paramount Pictures has won one Oscar for outstanding production of the “Last Airbender” and so has Summit Entertainment for “Eclipse”.
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
|