I want to design a database schema for a movie and tv series database using the E-R model and design tables accordingly using the relationship model. The necessary information is given below :
Entities : Movie, Actors, TV series. A TV series or a movie must have an actor. An actor can act in both. Assume columns accordingly and mention primary keys, weak and strong entities.
This is the question I tried to solve. I have few doubt related to this.
In this, model will have above three tables with given attributes. It maintains the criteria that each tv series or movie must have an actor and an actor can act in both. But If a movie has three/four actor then we have to enter movieName,movieGenre for same movieID. It is repetition of information and I do not want that.
Here as we added ActorMovies and ActorTVSeries, we need not to repeat inoformation. But I do not know how to ensure every movie or tv series will have an actor as they are in different table. I also want to know weak and strong entities. Please help me.
You cannot enforce that in a relational database within the schema for the following reason:
As a way to create RELATIONAL databases, you can create CONSTRAINTS that make sure that a record is validated against another table. This requires that the value against which something is validated already exists. You can't link an actor to a movie that doesn't exist: Both the actor record AND the movie record have to exist for this to happen. So it's possible (and required) to create a movie without an actor existing for it.
Your solution is NOT in the schema, but in the triggers AND stored procedures - using a transaction. You would need to provide BOTH an actor AND a movie to a stored procedure and it will create BOTH entries in the tables. It will have to be very smart and NOT recreate an actor record if one exists. It will have to re-use a movie if already created.
This whole logic has to checked against UPDATEs and DELETEs to make sure every movie has at least one actor. This is through triggers.
I am a bit lazy and don't want to write the whole stored procedure for you at this point, if this answers your question. If you need help with the whole transaction, please let me know.
--- SUGGESTION ---
If this is a real project and not an exercise, I suggest the BUSINESS LOGIC (insure an actor exists) be enforced by your processing and display language. It's not a good idea to put business logic in the DB, very hard to maintain.
--- SIDE NOTE ---
You may want to add a genre table to validate the genres