Question

was born in has Country (.Code) CountryName has "Acting !" Movie Title Actor (.Name) Movie D acted in Year (CE) was released in is highly rated assigned was born in MovieRating (.Code) {G', 'PG', 'PG-13', 'R', 'NC-17'} Year (CE) AussieActor* AussieActor NumberOfAwards (.Nr) Numberpfawards ... in ... won ... *Each Aussie Actor is an Actor who was born in Country 'AU'.

how can I convert the diagram into relation database schema?

I wonder how to connect between each schema, and I want to make relation schema of nested and external constraints which is not sure.

I think the table is country, actor, acting(nested), movie, aussieactor. Am I right?

Answer 1

Yes you are right about the entities present in the above diagram.

(i) To convert into relational schema first you have to map entities and they are country, actor, movie, aussieactor. For acting it can be identified as a relationship which hav attributes .

(ii) Next we have to identify the attributes of each entity. For example movie entity have the attributes- Movie_title, year, rating.

(iii) The relationships with attributes will be mapped. For example here the relationship is acting.

(iv) Next task is to map the weak entity, but here is no such entity.

1. Country:

Country(Country_code, Country_name)

2. Movie:

Movie(Movie_id, Movie_name, Release_year, Movie_Rating, Acting_id)

3.Actor:

Actor(Actor_id, Actor_name, Acting_id, Country_code)

4.AussieActor:

AussieActor(Actor_id, Actor_name, Born_date, No_of_awards, Acting_id)

5. Acting:

Acting(Acting_id, Movie_id, Actor_id)

As there are very limited information, we have to add primary keys in the entities uniquely. For example Movie_id is added in the Movie entity to uniquely identify a particular row. The relationship acting stores the information of actors and movies. Instead of storing all the records it just simply stores the primary key of the belonging entitties which are Actor_id and Movie_id respectively. Another entity AussieActor are the actors who were born in the Country AU, so there is no need to provide the country_id field in the entity.