Problem
Consider a "Digital Games" database with the following relations and attributes:
Gamer: (UserID, Name, Age, Address, City, Favorite-Game, Rank)
Game: (GameID, Name, Company, Release-Year, Version)
Store: (StoreID, Name, Address, City)
WhereToFind: (GameID, StoreID, Price)
Category: (GameID, Category-Name)
The database has information about registered gamers as well as their favorite games and their ranks. For simplicity, let us assume that the database stores only one favorite game per gamer. This database also has information about famous digital games, the stores that sell them along with the price of each game. Each store sells many games, and copies of a single game can be sold in different stores. The whereToFind relation captures such a many-to-many relationship. GameID and ShopID in WhereToFind relation are foreign keys referencing the primary keys of the Game and Shop relations, respectively. FavoriteGame in the Gamers relation is a foreign key referencing the primary keys of the Game relation. Each game can fall within several categories (e.g., sports, action), stored in the Category relation. GameID in Category relation is a foreign key referencing the primary key of the Game.
From the relational schemas illustrated above, use the different relational algebra operators and operations (ρ, σ, π, ?, ?, -, ?) to build the following queries:
A. List gamers -by name- whose favorite games are either "Call of Duty" or "FIFA". Call of Duty and FIFA are names for games, not IDs.
B. For each possible game, list the name and category of the game as well as the name and address of the store selling it. Name the result as "Full_Info".
C. List stores -by name and address- that sell games from the "action" category, not from the "sports" category.
D. Display stores -by name- that sell a game named "NBA" with a price of less than 80$ (included). NBA is the name of the game, not the game's ID. Name the result as "WhereToFind_NBA"