Question

The following relational schema is given for a Pokemon Database:

Pokemon (PID, PName)

Type (TID, TName)

Trainer (SSN, Name, Surname)

InType (PID, TID)

StrongAgainst (TID_Strong, TID_Weak)

Owns (SSN, PID, Nickname, Level, Height, Weight, Catch_Date)

SpecializedIn (SSN, TID)

In this database, Pokemon (Imaginary creatures, short for “Pocket Monsters”) are stored in the entity set Pokemon, with a unique PID and PName. Pokemon types are stored in the entity set Type, with a unique TID and TName. Pokemon trainers are stored in the entity set Trainer, including a unique SSN, Name and Surname for each trainer.

Relation InType is used for matching Pokemon with types. One Pokemon could be in many types, and there could be many Pokemon of a type. Each Pokemon should have at least one type. In this relation, PID is a foreign key to Pokemon entity set and TID is a foreign key to the Type entity set. Some types of Pokemon are strong against other types, relation StrongAgainst is used for indicating which type is strong against which other type. Both TID_Strong and TID_Weak are foreign keys to the entity set Type, TID_Strong being strong against TID_Weak. This is a many-to-many relation. Not every type is strong against some other type.

Relation Owns is used when a trainer owns a certain Pokemon. SSN is a foreign key to the Trainer entity set and PID is a foreign key to the Pokemon entity set. SSN, PID and Nickname contribute to the primary key of this table. (In Pokemon table we store different kinds of existing Pokemon, and when an instance of a specific kind is owned by a trainer, we store it using the relation Owns. For example, one trainer could own multiple instances of the same kind of Pokemon, with different Nicknames given to each instance). Level is a number that indicates the experience, hence the power of the Pokemon (Higher is stronger). Height is a number in centimeters, and Weight is a number in grams. Catch_Date indicates the start date of the partnership between a Pokemon and its trainer.

Some trainers are specialized in a certain type of Pokemon. A trainer could specialize in at most one type, and there could be many trainers specialized in a certain type. Relation SpecializedIn is used for this. SSN is a foreign key to the entity set Trainer, and TID is a foreign key to the entity set Type. SSN is the primary key of this table.

Questions are on the second page. 2

1) Draw the corresponding ER diagram, using Draw.IO. (10 points)

2) In the table SpecializedIn, only one of the foreign keys is used as a primary key; whereas in a different table such as InType, both foreign keys contribute to the primary key. What is the reason that there is only one primary key for the table SpecializedIn? Explain. (5 point)

Write the corresponding Relational Algebra expression for the remaining questions:

3) Find PName of Pokemon that are in type “Fire”. (5 points)

4) Find SSN and surname of the trainers whose name is “Gary”. (5 points)

5) Find type names that are weak against the type “Electricity”. (5 points)

6) Find name and surname of the trainers that does not own any Pokemon. (10 points)

7) Find PName and type (TName) of the Pokemon that are owned by the trainer whose name is “Drizzt” or “Bruenor”. (10 points)

8) Find name and surname of the trainers that own at least one Pokemon of each existing type. (There should be at least one Pokemon from each type that is owned by such trainers; for example, at least one “Fire”, at least one “Water”, and so on; for every type). (10 points)

9) Find SSN of the trainers that do not own any Pokemon of the type this trainer is specialized in. (Such trainers are specialized in a certain type, but they do not own any Pokemon that is in that certain type.) (10 points)

10) Find the heaviest Pokemon’s nickname that is owned by a trainer. (10 points)

11) Find name, weight and height of the Pokemon that was caught most recently, owned by the trainer with SSN “1837837”. (10 points)

12) Find name and level of the Pokemon that are owned by trainers that are specialized in a type that is strong against “Rock” or “Water”. (10 points)

Answer 1

NOTE: According to HomeworkLib guidelines, it is required to solve only four sub parts, if there are multiple sub parts in same question. Please ask rest of the sub parts in another section.