Parrot Palace works with TV and movie producers who need birds that can perform
ID: 3764701 • Letter: P
Question
Parrot Palace works with TV and movie producers who need birds that can perform special tricks, such as playing dead, reciting poetry, ladder climbs, and various other tricks. Parrot Palace has about 50 birds and a list of 20 tricks from which to choose. Each bird can perform one or more tricks, and many tricks can be performed by more than one bird. When a bird learns a new trick, the trainer assigns a skill level. Some customers insist on using birds that score a 10, which is the highest skill level. As an IT consultant, you have been asked to suggest 3NF table designs. You are fairly certain that a M:N relationship exists between birds and tricks.
Tasks
Draw an ERD for the Parrot Palace information system.
Indicate cardinality
Identify all fields you plan to include in the birds and tricks tables. For example, in the bird table, you might want species, size, age, name, and so on. In the tricks table, you might want the trick name and description. You will need to assign a primary key in each table. Hint: you might spot a situation that requires an associative entity that you can use as a pattern.
Create 3NF table designs.
Explanation / Answer
Answer:
According the question, there exist four tables.
First table contains the data regarding the Bird details.
Second table contains the data regarding the Trick details.
Third table contains the Customer details who want to purchase the birds depending on the trick scores.
Fourth table contains the M:N relationship between the Bird’s table and the Trick’s table.
Birds table: It contains the BirdId as the primary key, and includes Spices_Name, Size, Age, and SkillLevel.
The table is given as,
Bird(BirdId, Spice_Name, Size, Age, SkillLevel )
Trick table: It contains Trick_Num as the primary key, and includes Trick_Name and Description.
The table is given as,
BirdTrick(Trick_Num, Trick_Name, Description)
Customer table: To purchase the birds with good skills they have to provide their information to which customer the bird with which skills is being given away.
This table contains Customer_Id as the primary key.
The table is given as,
Customer(Customer_Id, CustomerName, Address, PhoneNum)
Bird skills table: Need to maintain a M:N relationship between the bird and tricks table. Because, when a bird learns a new skill the information has to be updated. So, the table is provided with BirdId and Trick_Num as primary key and is given as,
BirdSkills(BirdId, Trick_Num)