ER Diagram
HW-1
1. Draw an ER diagram for the given scenario
Scenario:
a. Suppose that you are designing a schema to record information about reality shows on TV. Your database needs to record the following information:
b. For each reality show, its name, genre, basic_info and participant’s name. Any reality show has at least two or more participants.
c. For each producer, the company name, company country. A show is produced by exactly one producer. And one producer produces exactly one show.
d. For each television, its name, start year, head office. A television may broadcast multiple shows. Each show is broadcasted by exactly one television.
e. For each user, his/her username, password, and age. A user may rate multiple shows, and a show may be rated by multiple users. Each rating has a score of 0 to 10.
f. Draw an entity relationship diagram for this database
2. Assume we have the following application that models soccer teams, the games they play, and the players in each team. In the design, we want to capture the following:
· We have a set of teams, each team has an ID (unique identifier), name, main stadium, and to which city this team belongs.
· Each team has many players, and each player belongs to one team. Each player has a number (unique identifier), name, DoB, start year, and shirt number that he uses.
· Teams play matches, in each match there is a host team and a guest team. The match takes place in the stadium of the host team.
· For each match we need to keep track of the following:
· The date on which the game is played
· The final result of the match o The players participated in the match. For each player, how many goals he scored, whether or not he took yellow card, and whether or not he took red card.
· During the match, one player may substitute another player. We want to capture this substitution and the time at which it took place.
· Each match has exactly three referees. For each referee we have an ID (unique identifier), name, DoB, years of experience. One referee is the main referee and the other two are assistant referee.
From above ERD diagram identify the following:
a. List all entities with their identifiers
b. List all weak entities
c. Identify recursive relation
d. Identify all multi-valued attributes
e. Identify an identifying relationship type
f. Identify One-to-one (1:1) relationship
g. Identify One-to-many (1:N)
h. Identify Many-to-many
i. Identify derived attribute(s)
j. Identify Composed attribute(s)